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:

▪ <ID_Patient, Last_Name_First_Name, Department, Age, Gender, Status>

▪ <ID_Doctor, Last_Name_First_Name, Specialty, Department, Age, Rank>

▪ <ID_Invoice, ID_Patient, Amount_to_Pay>

❑ 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.

<ID_Patient, Name_Surname, Department, Age, Gender, Status>

Primary
key <ID_Doctor, Name_Surname, Specialty, Department, Age, Rank, Consultation_Schedule>

<ID_Invoice, Amount_to_Pay, ID_Patient>

Unique Starnger keys
<ID_Appointment, ID_Patient, ID_Doctor, Date, Time, Location>

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 <ID_Patient, Name_Surname, Department, Age, Gender, Status>

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>

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

<ID_Patient, Name_Surname, Department, Age, Gender, Status>

• 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.

<ID_Patient, Nom_Prenom, Age, Sexe, Status>

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 [Link] Cardiologie 50 Assistant P001 M. Benabdellah 50 Homme 0

Pedia_S_2 [Link] Pédiatrie 35 Praticien Hospitalier P002 [Link] 8 Femme 2

Ophta_L_7 [Link] Ophtalmologie 70 Professeur
P003 [Link] 70 Homme 0
Cardio_M_1 [Link] Chirurgie 45 Attaché des Hôpitaux P004 [Link] 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 [Link] 8 Femme 2 Pedia_00D
P003 [Link] 70 Homme 0 Ortho_00T
P004 [Link] 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 [Link] 8 Femme 2 Fact_002 500 Non_Payer
P003 [Link] 70 Homme 0 Fact_003 2000 Payer
P004 [Link] 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