Database & Database Management Systems PPT

Abdallah EL Asmar  Why databases Definitions : DB, Data, DBMS, Metadata Data model DBMS Features DBMS Languages Abdallah EL Asmar 31/03/2025 2 Why databases  Example ▪ « XYZ », a company specialized in the sale of househol...

Abdallah EL Asmar  Why databases Definitions : DB, Data, DBMS, Metadata Data model DBMS Features DBMS Languages Abdallah EL Asmar 31/03/2025 2 Why databases  Example ▪ « XYZ », a company specialized in the sale of household appliances, wants to acquire a computer system to manage its sales and the work of its employees. ▪ So, the objective is to have a computer system allowing to ▪ employees of the Sales department:  To record sales  To check the availability of an item  … ▪ managers:  To control the work of employees  To control the stock  … ▪ Other departments may use the system (for example, the accounting department) ▪.. ▪ Other treatments, not initially planned, can be added to the system. Abdallah EL Asmar 31/03/2025 3 Why databases  Example ▪ Types of information to be treated: ▪ Item “Tel888” (Toshiba Television) is no longer available. ▪ The seller “Sami” sold two “Bosch” washing machines today. ▪ The seller “Fadi” sold a “Samsun” refrigerator today. ▪ The stock quantity of item “Ref567” (Samsung Refrigerator) is 4. ▪ …. ◦ How should company information be represented and recorded ? ◦ How can this information be used ? Abdallah EL Asmar 31/03/2025 4 Why databases  Example Items ItemCode ItemName Supplier Stock-Qt price Ref462 Refrigerator Samsung 4 650 Wash345 Washing M. Bosch 6 220 Wash452 Washing M. Samsung 3 255 TV888 TV Toshiba 8 95 Sales Salesman Salesma Item- Sale-Qt Salesma Salesman Phone n-ID Code n-ID -name 10 Wash345 2 10 Sami 666888 29 Ref462 1 29 Fadi 444555 10 TV888 2 Abdallah EL Asmar 31/03/2025 5 Why databases  Example Possible treatments  Top Selling items  The total sale amount of each salesman  Increase by 5% the price of all items from the manufacturer «Samsung »  Needs ◦ Store large volumes of information ◦ Storage in a predefined structure ◦ Ensure effective and standard treatments (consultation, updating, …) ◦ Sharing information by multiple users ◦ Ensuring consistency Abdallah EL Asmar 31/03/2025 6 Definitions  Database A structured set of data, modeling a real world, recorded on computer media to satisfy several users simultaneously. Data Facts of knowledge that can be recorded and have implicit meaning. Real world Organization (company, hospital, university, …) or a part of an organization whose data is stored in a database. Abdallah EL Asmar 31/03/2025 7 Definitions  Database A structured set of data, modeling a real world, recorded on computer media to satisfy several users simultaneously. Abdallah EL Asmar 31/03/2025 8 Definitions  DataBase Management System (DBMS) A DBMS is a software that supports the structuring, storage and maintenance of a database. ▪ It is the only interface between computer scientists and data (definition of schemas, programming of applications), as well as between users and data (consultation and updating). ▪ Among the most famous software: Oracle, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, Firebird, Sybase, Access….. Abdallah EL Asmar 31/03/2025 9  Definitions  Database System The DBMS system as well as data itself. Sometimes applications are also included.  Metabase ▪ Collection of Metadata that describe the database Abdallah EL Asmar 31/03/2025 10 Data Model  Data model ◦ Set of concepts, offered by DBMS, to describe a DB; a model must describe:  The data  The links between the data  The semantic of data ◦ In general, a set of operations is associated to manipulate the data Brief history  Hierarchical Model: implemented by joint efforts of IBM and North American Rockwell in 1965. The result is the family of systems IMS.  Network Model: the first system was implemented by Honeywell in 1964-65 (IDS System).  Relational Model: proposed in 1970 by E.F. Codd (IBM), the first system marketed in 1982.  Object-Oriented Model : (1990) Several models are proposed; they are the models of OOP languages such as C++ and Smalltalk.  Relational-Object Model: Most used currently.  NoSQL families: : (2009) This is not a NoSQL model, but several NoSQL families which developed in parallel and which meet different needs. Abdallah EL Asmar 31/03/2025 11 DBMS Features  Database definition ◦ Define a DB in terms of data types, structures and constraints.  Database manipulation ◦ Querying the database ◦ Updating data ( Insertion , Modification , Deletion )  Integrity management and control  Definition of integrity constraints  Integrity constraint Property must always be verified by data ◦ Database updates (insertion, deletion, modification) must respect integrity constraints. Abdallah EL Asmar 31/03/2025 12 DBMS Features  Privacy Management ◦ Database shared by several users Make certain data accessible only to authorized persons ◦Defining access rights : defining which information should be protected from which people  Concurrent access management ◦ Simultaneous access to data by multiple users  detect possible conflicts  Treat the conflicts  Operational safety ◦ Ensure system restart in the case of a software or hardware incident  Put the DB in a consistent state Abdallah EL Asmar 31/03/2025 13 DBMS Languages Database languages are specialized languages used to interact with a database. They allow users to perform different tasks such as defining, controlling, and manipulating the data. There are categorized into the following four main types:  DDL (Data Definition Language)  DCL (Data Control Language)  DML (Data Manipulation Language)  TCL (Transaction Control Language) Abdallah EL Asmar 31/03/2025 14 DBMS Languages ▪ DDL (Data Definition Language) It is used to create and modify the structure of the database objects. ▪ DCL (Data Control Language) It is used to control the access permissions of users to the database. DCL commands help grant or revoke privileges to users, determining who can perform actions like reading or modifying data. ▪ DML (Data Manipulation Language) It is used to manage and manipulate data within a database. DML commands can perform various operations such as inserting, updating, selecting, and deleting data. ▪ TCL (Transaction Control Language) The TCL commands are used to manage and control transactions in a database, grouping them into logical units. These commands help ensure the integrity of data and consistency during complex operations. Abdallah EL Asmar 31/03/2025 15