Chapter 6: Introduction to Data Base Management Systems PDF

Chapter 6 Introduction to Data Base Management Systems Dr. Lakehal Abderrahim Data management Data (File) Storing in Access SS...

Chapter 6 Introduction to Data Base Management Systems Dr. Lakehal Abderrahim Data management Data (File) Storing in Access SS Scattered Direct Contiguous Sequential (index) Variable Fixed size Table Tree size Liste / Table , Ordered / Unordered. M-ary B-ary Classic file processing approach Data Base Management Systems (DBMS) Overview of the classic file processing approach ❑ The classic approach involves creating an application program for a specific type of information: ❑ A clinic needs to store a significant volume of information: ▪ ▪ ▪ ❑ This information is found in various file processing systems: ▪ Patient management system. ▪ Material and personnel resource management system. ▪ Patient appointment management. ▪ Patient billing.. Overview of the Classic File Processing Approach: Disadvantages A) Organization, Storage, and Ease of Use Use case 1. Determine the system to consult (Patient management, Doctor management, etc.) 2. Identify the appropriate person: Patient Management→ Receptionists. Appointement management → Personal Assistants. Patient Billing → Financial Team. Problems Time loss Some information is often stored in multiple locations (e.g., Patient List and Doctor List in the Patient Appointment Management System). Data duplication and wasted storage space in file volume. Overview of the Classic File Processing Approach: Disadvantages B) Programming and development Development of applications and programs for each type of processing system and each operation. Code developed by different programmers is written in different programming languages. Problems Programming access operations involves very high complexity. It cannot quickly respond to information requests from multiple files, with high costs for modifications (due to multiple systems). Maintenance is difficult in the absence of the original developer. There is a possibility of having files in different formats. It is highly challenging to write a general program for accessing all the information. Overview of the Classic File Processing Approach: Disadvantages B) Programming and development Step Description Resource Identification The user or application identifies the resources (files) to be accessed (creates if files don't exist). File Opening Files are opened using operating system commands or function calls. Access Control Authorization checks are performed to determine if the user has the required permissions. File Operations Reading, writing, deleting, etc., based on the needs of the user or application. File Closing Closing files to release the associated system resources. Error Management Handling potential errors throughout the file access process. Resource Release Releasing all system resources when file access is no longer needed. Classic file processing approach Overview of the Classic File Processing Approach: Disadvantages C) Multiple Updates (Transactions) Concurrent processing, which is used to modify a given value from multiple tasks (functions) in real-time, can generate errors. Ex: Simultaneous updates of a bank account. Balance: 40 000.00 DA Tache 1: Deposit 30 000.00 DA Tache 2: Extract 50 000.00 DA Possibles scenarios : T1 T2 IF Task 1 before Task 2 : 40 000.00 DA → 70 000.00DA → 20 000.00 DA T2 T1 IF Task 2 before Task 1 : 40 000.00 DA → - 10 000.00DA → 20 000.00 DA Requires a supervisory program to manage transactions: Challenging. Overview of the Classic File Processing Approach: Disadvantages D) Security and Data Integrity ❑ Data security and unauthorized access are not guaranteed. ▪ Personal Assistants should not have access to the billing system. ▪ Financial staff should not have access to patients' personal information. ❑ Data Integrity: Ensuring that the data is accurate and adheres to defined constraints. ▪ Example 01: A patient's identifier is unique across all systems. ▪ Example 02: The amount to be paid should always be greater than 0. ▪ Example 03: Individuals under 17 should always be admitted to the pediatric department. → Difficulty in enforcing constraints in traditional file processing systems. Overview of the Classic File Processing Approach: Disadvantages D) Security and Data Integrity Disadvantage Description Repeated storage of the same information in multiple locations, resulting in wasted space and Data Redundancy potential coherence problems. Data Inconsistency Difficulty in maintaining data consistency due to redundancy and manual file management. Difficulty in Updating and Maintenance Structural modifications often require manual updates in multiple locations, which can be tedious. Access Difficulty Sequential data access makes search and filtering operations inefficient and difficult to implement. Offers limited security dependent on the operating system and file permissions, less sophisticated Limited Security than database management system access controls. Complexity in sharing data between different applications, without integrated mechanisms to ensure Difficulty in Data Sharing the consistency of shared data. Degraded Performance Performance can degrade with an increase in data volume due to sequential search and redundancy. Lack of sophisticated mechanisms for transaction management, making it difficult to ensure data Difficulty in Transaction Management consistency during multiple operations. Less flexible in terms of modifying data structure, making adaptation to changing user needs Lack of Flexibility challenging. Solution : Data Base Management Systems (DBMS) DBMS Complexity of Atomicity of Protection storage and Direct access Data banks Transactions and assured processing hidden through data base Data Integrity from the user Unique data Database Management System(DBMS) :Definitions DBMS (Database Management System): A system that manages a database shared by multiple users simultaneously through a set of programs that: ❑ Ensure the management of a set of files (database). ❑ Offer users the ability to retrieve or store data through predefined programs in the DBMS (search, insertion, deletion, etc.). Thus, the DBMS allows for: ▪ Creating ▪ Updating ▪ Querying (responding to user-defined "questions" or searches) ▪ Visualizing ▪ Managing... a database Database Management System(DBMS) : Commercialized DBMS Type of Database Examples Relational MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite, IBM Db2, MariaDB NoSQL MongoDB, Cassandra, Redis, CouchDB, Neo4j, Amazon DynamoDB Object-Oriented db4o, ObjectDB Cloud-Based Amazon Aurora, Microsoft Azure SQL Database, Google Cloud Spanner Files between the classic approach and DBMS Separation of Data and Programs File Database (The data in the database is described (The data in the files is described outside the programs, within the database within the programs) itself) (File 1) File description (1) Description Unique Used by (File 2) Permanent File description (2) Programmes Programmes  Database Management System(DBMS) : Objectives and Advantages A) Data Description: (Creation of a database) The data is described independently of the applications using a data definition language. Data Definition Language (DLL) B) Data Manipulation Query and update data without specifying or programming the access algorithm. → Say WHAT without saying HOW → declarative query language (Queries) Example: Who are the patients with a status = 0 (Waiting for diagnosis)? → Using a data manipulation language Data Manipulation Language (DML) C) Data Control Integrity → verification of integrity constraints Confidentiality → Access control, authorization Using a data control language Data Control Language (DCL) Database Management System(DBMS) : Objectives and Advantages D) Sharing and availability of the system ❑ A database is shared among multiple users simultaneously ⇒ Control of concurrent access (transaction) ⇒ The execution of a transaction must maintain the consistency of the database ❑ A DBMS must be available for every usage request, ensuring meticulous management of system failures. E) Access Performance Direct Access: Indexing (hashing, B-tree, and m-ary trees, etc.) (Most commonly used) Sequential Access: Used in cases of complete database scanning (very rare)  Database Modeling Process Real world 01- Requirements Gathering and Analysis 02- Conceptual design (Entity/Relation) 03- Model Implementation (Database Structure: Tables/Trees/Graphs) 04-Physical conception  Database Modeling Process 01- Requirements Gathering and Analysis: Requirements gathering and analysis in database modeling involve understanding business needs, identifying stakeholders, and designing an appropriate data structure. Business Requirements: Define what the organization expects from the information system to achieve its operational goals. User Requirements Gathering Problem Analysis: Asking questions like: What information do we want to store?  Database Modeling Process 02- Conceptual Design: Creation of the conceptual schema based on the Entity-Relationship (ER) model. ❑ Detailed Description of User Requirements: ▪ Data Types: Definition of the types of data to be stored. ▪ Relationships: Specification of how entities are connected. ▪ Constraints: Identification of rules governing the data. ❑ It is easy for users to understand. ❑ There is no implementation; it is just a graphical representation  Database Modeling Process 03- Implementation of the models Determine the structure of the database: ❑ Relational. ❑ Network. ❑ Hierarchical. The choice of the DBMS determines the internal (physical) structure of the database. Transformation of the conceptual schema from the high-level data model (Entity-Relationship) to the implementation data model (Relational, Network, or Hierarchical). 04- Physical conception Specification of internal storage structures, file organization, and access management in the database (Chapters 2-5). Data Base Management Systems (DBMS): Architecture user 1 user 2 user 3 user 4 user 5 user 6 View 1 View 2 External level Logical scheme Conceptual level (Data dictionary) Physical scheme (Data structure) Internal level  Data Base Management Systems (DBMS) : External level This is the level with which end users interact. External views are created at this level to represent the specific parts of the data that a user or an application needs Example Patient Department (External View 1): The employees of the patient department need access to information about patients, such as medical records, appointments, etc. Objective: Provide a specific view to enable the patient department staff to manage appointments, review patients' medical history, and handle payment statuses. Medical Department (External View 2): Doctors and nurses need access to medical records, test results, prescriptions, doctors' consultation schedules, etc. Objective: Provide a specific view to enable medical staff to monitor treatments, prescribe medications, and access medical examination results. Data Base Management Systems (DBMS) : Conceptual level ❑ Define the overall structure of the database, including entities, relationships, integrity constraints, and business rules. ❑ Conceptual schemas are created at this level to describe the structure of the database Conceptual Entities: Patient, Doctor, Appointment, Invoice, Prescription, etc. - Each entity is characterized by a unique key for each new element. Primary key Unique Starnger keys Relationships: A patient can have multiple appointments, a doctor can have multiple patients, etc. The relationship between the entities is established by foreign keys and/or relationship entities. Integrity Constraints: An appointment cannot exist without an associated patient, etc.  Data Base Management Systems (DBMS) : Conceptual level Example of Relational model Entity Patient Column row ID_Patient Name_Surname Department Age Gender Status P001 Mohamed Benabdellah Cardiologie 50 Homme 0 P002 Fatima Zahra Benslimane Pédiatrie 8 Femme 2 n-uplet P003 Ahmed Bouzid Ophtalmologie 70 Homme 0 P004 Sarah Benouari Chirurgie 35 Femme 1 tuple Clé Rows: primaire (unique) Correspond to a record/item (in a file) Contains all attributes Identified by a key  Data Base Management Systems (DBMS) : Conceptual level Example of Relational model Entity Invoice: ID_Invoice Amount_to_Pay ID_Patient Fact_001 1000 D.A P001 Fact_002 500 D.A P002 Fact_003 2000 D.A P005 Fact_004 3000 D.A P005 Data Base Management Systems (DBMS) : Conceptual level To find the amount to be paid by a patient "Fatima Zahra Benslimane," the system first searches for the primary key (ID_Patient) of Fatima in the Patient table. Then, it uses the foreign key ID_Patient from the Invoice table to find the corresponding row. ID_Patient Name_Surname Department Age Gender Status P001 Mohamed Benabdellah Cardiologie 50 Homme 0 P002 Fatima Zahra Benslimane Pédiatrie 8 Femme 2 P003 Ahmed Bouzid Ophtalmologie 70 Homme 0 P004 Sarah Benouari Chirurgie 35 Femme 1 Fatima Zahra Benslimane → P002 → P002 → 500 D.A ID_Invoice Amount_to_Pay ID_Patient Fact_001 1000 P001 Fact_002 500 P002 Fact_003 2000 P005 Fact_004 3000 P005  Data Base Management Systems (DBMS) : Internal level ▪ Internal schema: Details how the entities and relationships from the conceptual level are actually stored in the database (M.S). (Chapters 2- 5) ❑ Tables: The internal schema details the structure of the database tables. ❑ Relations: It specifies how the relationships between tables are implemented at the physical level. For example, the link between the "Invoice" table and the "Patients" table is made using the foreign key "ID_patient“. ❑ Data Types: The internal schema defines the data types associated with each column (integer, string, date, etc.). ❑ Integrity Constraints: It outlines the integrity constraints at the physical level, such as primary and foreign keys, to ensure data consistency. ID_Invoice Amount_to_Pay ID_Patient Struct Invoice{ rec 01 Fact_001 1000 P001 ID_Invoice: String; // unique value rec 02 Fact_002 500 P002 Amount_to_Pay: real; rec 03 Fact_003 2000 P005 rec 04 Fact_004 3000 ID_Patient: String; } P005 Bloc i rec 01 rec 02 rec 03 rec 04 Data Base Management Systems (DBMS) : Internal level Indexes: Specify how the data is indexed to speed up searches. Index on the Patient ID Number: To speed up searches based on the patient's identification number, an index can be created on this column. This reduces the time required to find specific records associated with a patient. Other Indexes: Depending on the needs, other indexes can be created on columns frequently used in queries, such as appointment dates, doctor's name, etc. Data Base Management Systems (DBMS) : Internal level Optimization: Includes details on how the database is optimized for performance, such as table partitioning, the use of caches, etc. Table Partitioning: If the database becomes large, table partitioning can be used to physically divide the data into manageable subsets, thereby improving query performance. Use of Caches: Caching frequently used query results can reduce access time to this data, thus enhancing overall performance. Query Optimization: Techniques such as analyzing query execution plans can be used to optimize query performance. Transaction Management: Optimization may also include transaction management strategies to ensure data consistency while maintaining good performance. Database Management Systems (DBMS): Database modeling Data modeling allows for the correct description of a database's architecture, including the data, their relationships, semantics, and constraints. DB model Entity/Relation model Relational Data base Hierarchical structure Network Database Management Systems (DBMS): Database modeling Characteristic Relational Model Hierarchical Model Network Model Entity-Relationship Model Data Structure Tables Trees Graphs Entities and Relationships Parent-Child (One-to-One, Relationship Between Data Use of foreign keys Connections between nodes Relational One-to-Many) Complexity of Relationships Simple to Complex Simple to Complex Complex Simple to Complex Flexibility Medium to High Limited High Medium to High Integrity and validation Data Integrity Relational integrity constraints Depends on implementation Depends on implementation constraints Query Language SQL Depends on implementation Depends on implementation SQL Relational database LDAP (Lightweight Directory IMS (Information Management Implementation Example management systems (MySQL, Oracle, MySQL, PostgreSQL Access Protocol) System) PostgreSQL, etc.) Database Management Systems (DBMS): Entity/Relationship model ER (Entity-Relationship) : A formalism adopted by ISO to describe the conceptual aspect of data using entities and relationships. ER Entity Propriety Identifier Association Cardinality Database Management Systems (DBMS): Entity/Relationship model Entity Representation of a material object (Patient, Doctor, Organizers) or an immaterial object (Consultation, Invoice). Entity_Name Liste of properties Database Management Systems (DBMS): Entity/Relationship model PROPRIETIES Elementary data related to an entity We only consider the properties that are relevant to a particular context. The properties of an entity are also called attributes or characteristics of that entity. Patient ID_Patient Name_Surname Age Gender Status Database Management Systems (DBMS): Entity/Relationship model THE IDENTIFIER A property or group of properties used to identify an entity. The identifier of an entity is chosen by the analyst in such a way that two occurrences of this entity cannot have the same identifier. For example, the identifier of the patient will be the identifier of the Patient entity. Patient ID_Patient Name_Surname Age Gender Status Database Management Systems (DBMS): Entity/Relationship model ASSOCIATIONS ❑ Representation of a link between two or more entities ❑ An association can have specific properties Patient Doctor ID_Patient ID_Doctor Name_Surname Consultation Name_Surname Age Specialty Gender Prescription Age Status Rank Database Management Systems (DBMS): Entity/Relationship model CARDINALITIES The cardinality of an association for an entity consists of a minimum bound and a maximum bound: Minimum: The minimum number of times an occurrence of the entity participates in the occurrences of the association, typically 0 or 1. Maximum: The maximum number of times an occurrence of the entity participates in the occurrences of the association, typically 1 or n. Doctor Patient ❑ A doctor can consult a maximum of n ID_Doctor ID_Patient Consult patients, whereas a patient can be Name_Surname 1,n 1,m Name_Surname consulted by a maximum of m doctors. Specialty Age Prescription Age Gender Rank Status By noting only the maximum cardinalities, three types of links can be distinguished: ❑ Minimum cardinalities are necessary to express integrity constraints. Functional link (1:n): One instance of A can only be associated with a single instance of B. ❑ Maximum cardinalities are necessary to design Hierarchical link (n:1): One instance of A can be associated with multiple instances of B. the database schema Network link (n:m): One instance of A can be associated with multiple instances of B, and vice versa Database Management Systems (DBMS): Entity/Relationship model Doctor Patient ID_Doctor ID_Patient 1,n Consult 1,m Name_Surname Name_Surname Specialty Age Age Prescription Gender n Rank Status 1 1 Pay Associate 1 m Invoice Ddepartment ID_Invoice ID_Department Amount_to_Pay n Attribute Statu_F Name_Dep Capacity Database Management Systems (DBMS): RELATIONAL SCHEMA The relational model is a logical schema represented by RELATIONS. THE RELATIONAL SCHEMA ❑ The relational schema is the set of RELATIONS that describes the entity/association model. ❑ The relations represent entities (such as patients, doctors) or the associations between these entities (e.g., consultation, payment) Database Management Systems (DBMS): RELATIONAL SCHEMA Transition from an E-A conceptual schema to a relational schema ❑ An entity is represented by the relation: entity_name (list of the entity's attributes) ❑ A M:N association is represented by the relation: association_name (list of identifiers of the participating entities, list of the association's attributes) Doctor Patient ID_Doctor ID_Patient Name_Surname 1,n Consulter 1,m Name_Surname Specialty Prescription Age Age Gender Rank Status Database Management Systems (DBMS): RELATIONAL SCHEMA Doctor(ID_Doctor,Name_Surname,Speciality,Age,Rank) Patient(ID_Patient,Name_Surname,Age,gender,Status) ID_Patient Name_Surname Age Sexe Statut ID_Médecin Nom_Prenom Spécialité Age Grade Cardio_M_1 S.Bounab Cardiologie 50 Assistant P001 M. Benabdellah 50 Homme 0 Pedia_S_2 F.Slimane Pédiatrie 35 Praticien Hospitalier P002 FZ.Benslimane 8 Femme 2 Ophta_L_7 K.Boutib Ophtalmologie 70 Professeur P003 A.Bouzid 70 Homme 0 Cardio_M_1 L.Nouari Chirurgie 45 Attaché des Hôpitaux P004 S.Benouari 35 Femme 1 Consultaion(ID_Médecin, ID_Patient, Prescription) ID_Doctor ID_Patient Prescription Cardio_M_1 P001 ************* Pedia_S_2 P002 ************* Ophta_L_7 P003 ************* Cardio_M_1 P004 ************* Database Management Systems (DBMS): RELATIONAL SCHEMA Transition from an E-A conceptual schema to a relational schema For other types of associations (1:n), the corresponding foreign keys are added in the relations of the concerned entities (the entity with a single occurrence) Patient ID_Patient Ddepartment Name_Surname 1 ID_Department Age Attribute Name_Dep Gender n Capacity Status Patient(ID_Patient, Name_Surname,Age,gender,Status, ID_Departement) ID_Patient Name_Surname Age Sexe Status ID_Department P001 M. Benabdellah 50 Homme 0 Cardio_00C P002 FZ.Benslimane 8 Femme 2 Pedia_00D P003 A.Bouzid 70 Homme 0 Ortho_00T P004 S.Benouari 35 Femme 1 Geneco_00B Database Management Systems (DBMS): RELATIONAL SCHEMA Passage d'un schéma conceptuel E-A à un schéma relationnel The same conversion as (1:N) is followed, or the 1:1 cardinality is represented in the relational model by a single table Patient ID_Patient Invoice 1 Name_Surname ID_Invoice Age Payer Amount_to_Pay Gender Statu_F 1 Status Patient(ID_Patient,Nom_Prenom,Age,Sexe,Statut, ID_Facture, Amount_to_Pay, Statut_F) Amount_to_Pay Statut_F ID_Patient Name_Surname Age Sexe Status ID_Invoice P001 M. Benabdellah 50 Homme 0 Fact_001 1000 Payer P002 FZ.Benslimane 8 Femme 2 Fact_002 500 Non_Payer P003 A.Bouzid 70 Homme 0 Fact_003 2000 Payer P004 S.Benouari 35 Femme 1 Fact_004 3000 Non_Payer Unique key A primary key is a unique key, but the reverse is not necessarily true. Database Management Systems (DBMS): RELATIONAL SCHEMA (Advantages) FEATURE DESCRIPTION SIMPLICITY OF PRESENTATION Representation in the form of tables Relational algebra RELATIONAL OPERATIONS Assertion languages Access optimization PHYSICAL INDEPENDENCE Access strategy determined by the system LOGICAL INDEPENDENCE Concept of VIEWS MAINTAINING INTEGRITY Integrity constraints defined at the schema level  Global structure of a DBMS (users) Operators: Access the database in their daily functions: ❑ Search. ❑ Update (Add, delete). ❑ Generate reports. Administrator: ❑ Manages resources (DB, DBMS, and software). ❑ Authorizes access and manages usage. ❑ Responsible for security and improving system response time. In large organizations, they are assisted by a team.  Global structure of a DBMS (users) Database Designer(s): ❑ The designer and the administrator can be the same person. ❑ Responsible for identifying the data to be stored. ❑ Chooses the structures to represent the data. ❑ Must have a good understanding of user queries. ❑ Manages the Data Definition Language (DDL) module. Systems Analyst: ❑ Determines user needs and develops specifications. ❑ Manages the Data Manipulation Language (DML) module. Application Programmers: ❑ Implements the specifications into a program using a language programing language (JAVA, C++, etc). Database Management Systems (DBMS): Operation Invisibles Task MM SS users Controler Optimiser Executor Human Integrity control Scheduling Execution of the plan Program Authorization Optimization Access methods control Definition of an Concurrency control Data Base Request control execution plan Atomicity of (Query) transactions Workflow of DBMS