Université 8 Mai 1945- Guelma- Algérie
Computer Science Department
Course: Introduction to Databases
2nd year engineers
Course outline
1. Chapiter I: Databases presentation (DB)
2. Chapiter II: The Relationnal Model
3. Chapiter III: The Relationnal Algebra
Assessment
1. Final Exam: 60 %
2. Continuous assessment (TD, micro): 40 %
3. Credit : 3, Coefficient : 3
Dr. KHEBIZI Ali
Email: [Link]@[Link] / [Link]@[Link]
1
Université 8 Mai 1945- Guelma- Algérie
Course: Introduction To
Databases
Chapter I: Databases Presentation
2
Chapitre I: Plan du cours
1. Introduction
2. File Notion (interests and limits)
3. Introduction to the notion of Database (DB)
4. DB management Systems (DBMS)
5. Types of Data Models
(hierarchical, network, E/A, relational)
6. Conclusion
3
1. Introduction : Observations
- Explosion of the of the data size (data mass)
-Proliferation of connected devices (IOT)
- Smart sensors,
- Social networks,
- Online transactions,
The world generates enormous amounts of
information every day.
Phenomenal explosion in the volume of data:
Big Data: Variety of formats, sources and a high
speed of data production.
4
1. Introduction : How to manage this data?
This abundance of data offers unprecedented
opportunities
Information systems (IS)
- Acquisition: Data collection
- Storage: Adequate supports
- Processing: Applying management rules
- Diffusion: transmit information to stackeholders
Both in the business field and in scientific research
- Analysis (Data analysis, Data science)
- Decision-making (Decision support systems)
5
1. Introduction : How to manage this data?
This mass of data poses challenges in terms of :
• Storage,
• Processing,
• Privacy protection…
Requiring the constant evolution of
technologies to benefit from the new digital
era.
6
1. Introduction: Scientific Vs Management Computing
1. Scientific Computing 2. Management Computing:
payroll, accounting, education,
stock
Algorithms Data Types Algorithms Data Types
Complexes Simples Very Simples Complexes
-Operating System -Predefined - Add/delete a -Files
-Google Indexing (integers, reals, ) record -Meaning,
-Simulation -Structured (vector, (customer, -Links
-Data Analysis
matrix, record…) product…) -Inconsistencies,
-Matrix Calculation
-Dynamic (Linked -Search -Redundancies
lists, stacks, -Sort -Constraints…
queues, trees…) -Edit
7
2. File Notion
2.1 Definition: At the Logical (application) level:
A file is a set of data (records) recorded under the same name and
stored in secondary memory (SM) (HDD, USB key,…).
2 types of files
1. Data files : Sequences of records with a specific access mode
(sequential, indexed, etc.)
2. Processing files (programs): a set of instructions allowing the
manipulation of data in files (e.g., PHP, C++, Python,… etc)
Example C file to create a data file
#include <stdio.h>
// Structure representing a record
struct Record {
int code;
char Name[40];
char Firstname[40];
8
};
2. File Notion
int main() { // Open the file in writing mode
FILE *fichier = fopen("[Link]", "w");
if (fichier == NULL)
{ fprintf (stderr, " Error opening file for writing.\n");
return 1; // Error Code }
// Create some records
struct Record record1 = {1, « Selmi", « Rachid"};
struct Record record2 = {2, « Briki", « Selloua"};
// Write records to file
fprintf(file, "%d %s %s\n", [Link], [Link], [Link]
fprintf(file, "%d %s %s\n", [Link], [Link], [Link]
// Close file fclose (fichier); return 0;
// End of the program without errors
} 9
2. File Notion
2.2 Definition: At the physical (system) level:
A file is a set of blocks, organized in a certain way in Secondary
Memory (SM).
The data (records or commands) of the file are stored inside the blocks.
Data storage and manipulation using files structures
10
2. File Notion
2.3 File characteristics
To be able to manipulate a file correctly, we must therefore
know its structure and organization.
Some of this information (characteristics) is saved in SM and
loaded in CM when the file is manipulated.
Among the common characteristics, we find:
- Creation date and the last access date of the file,
- The number of used blocks,
- The number of records,
- The access rights to the file (case of multi-user systems)
- The identity of the file owner (case of multi-user systems)
11
2. File Notion
2.3 File characteristics
Access methods: Consists of defining:
- A way of organizing the blocks of a file on SM
- The placement of records inside the blocks
- The implementation of access operations (search, insertion, deletion,...).
12
2. File
File Notion
Notion(Benefits)
(benifits)
2.4 Interest in using files
The data mass is distributed among different files.
a. Flexibility : Differents data formats are managed (texts, images,
videos and other formats).
b. Sharing and collaboration: Files can be shared among company
members.
c. Integration with applications: Files can be used with a variety of
software applications easily integrating with other systems
and processes within the enterprise.
d. Security: Files can be protected by security mechanisms
(passwords, access permissions and encryption systems) to ensure
the confidentiality of sensitive data.
e. Backup and Recovery
f. Data analysis: statistics, trends, Decision making
13
2. File Notion (benifits)
2.4 Interest in using files
The data mass is distributed among different files.
These files are accessible to different management applications.
e-Commerce Customer File
Product File
Accounting
Supplier File
HRM
Employer File
Stock Stores File
The File Management System (FMS): is a software (module of the
operating system) which is responsible for managing the storage and
manipulation of files (Read, write, access mode…etc). 14
2. File Notion (Drawbacks)
2.5 Limits of using files
a. FMSs are limited when they face to managing a large mass
of data containing links between them.
Customer file Product file
Store file Supplier file
b. Information redundancy problems :
* Loss of memory space (**),
* Increased access time (***) for search, update tasks,
* Inconsistency of information (*****) Anomalies ! ! !
Example: Address for: Student scolarship (department),
Resident (residene), Reader (library), Player (hobbies club)
The information inconsistency problem requires a multiplicity
of updates. 15
2. File Notion (Drawbacks)
2.5 Limits of using files
c. The application is dependent on the data storage mode.
The file user must know :
- The file access mode (sequential, indexed or other );
- The physical structure of records;
- The localisation of used files.
d. A specific program for any new need is necessary.
Example: We want to estimate the sales evolution over the 2 past months following
a 10% increase in item prices.
e. Lack of security: In case of multiple access to files by several
programmers, it is not possible to ensure the security and integrity of
the manipulated data.
f. Unstructured data: Lack of rigourous data organisation, search and
retrieval issues, scalability issues,
g. Concurrency Problem: If multiple users access the same files at the
same time, then concurrency problems arise
16
leading to data inconsistency.
2. File Notion (Drawbacks)
2.5Limits
2.5 Les limites de l’utilisation
of using files des fichiers
Example: T1 and T2 are two parallel transactions.
-Transaction 1 : T1 (User 1)
- Read (balance) (500).
- Transaction T2 ( user 2)
- withdraw 50 DA from an account : balance = balance - 50
In the meantime, transaction T2 begins.
Transaction T2 :
- Read account balance (500)
- balance = balance - 100.
T2 updates the balance (500 - 100 = 400).
Back to transaction T1:
T1, not knowing about T2's change, decides to make a withdrawal
of 50 DA from the account.
T1 updates the balance (500 - 50 = 450).
If both transactions T1 and T2 are executed in parallel, there is an
inconsistency problem? Which is the right balance 400 or 450 ? 17
3. Introduction to the concept of DB
3.1 DB definitions
Application 1
Application 2 DBMS
Database
Application 3 DBMS: (DB)
…....
DataBase
Management
Application n System
Definition 1: A database is a set of structured information
stored on a persistent medium.
Definition 2: a Database is collection of data that is:
- Structured independently of a particular application,
- Accessible by multiple users.
- Minimal redundancy,
- Coherent, 18
3. Introduction to the concept of DB
3.1 DB definitions
Example of a database:
School database: modules, students, teachers, rooms, exams, etc.
Company database: Customers, suppliers, products, etc.
Hospital database: Patients, doctors, rooms, services, etc.
Airline database: Planes, flights, pilots, airports,..etc.
Hotel, Travel agency, bank, …..
19
3. Introduction to the concept of DB
3.2 Abstraction levels of DB: To simplify the users' vision, and ensure
abstraction of data stored on disks, three levels of data description have
been defined:
External level
Conceptual level
Physical level
20
3. Introduction to the concept of DB
3.2 Abstraction levels of DB: To simplify the users' vision, and ensure
abstraction of data stored on disks, three levels of data description have
been defined:
Three levels of DBMS description
(ANSI/SPARC):
1. The external level
- Level relative to users
- How they see the data
2. Conceptual level
- Intermediate Level
- "Abstract" representation of the entire
DB
3. Internal Level
- Physical memory level
- How data is actually stored
21
3. Introduction to the concept of DB
3.2 Abstraction levels of DB:
a. External Level: (or views): describes only the part of the data
that is of interest to a user or group of users.
b. Conceptual Level (Logical): Allows to describe all the company
data, their links and their constraints.
The data is considered semantically.
c. Physical level (internal): Allows the management of data storage
on physical media, storage structures (files) and access mode
(management of indexes, keys, etc.).
- Mode of recording on the physical media (disk);
- Data structures are described in more details;
- Access paths for the DB.
22
3. Introduction to the concept of DB
3.2 Abstraction levels of DB:
Example: Customers file containing multiple records.
Declaring a record in C:
struct Client {
int code;
char 1st Name [30];
char Last name [30];
char Address [50];
real Total-purchase [20]
};
a. External Level (Views): The record is characterized and
described by the users' views.
View #1: All customers living in city X;
View #2: All customers with the same last name;
View #3: All customers whose total purchases are greater than 1000;
23
3. Introduction to the concept of DB
3.2 Abstraction levels of DB:
b. Conceptual (logical) level: The record is described by its meaningful
content and relationships between properties.
Customer
Code Total-Purchase
1st Name
Last Name Address
c. Physical (internal) level: The record is described as a block of
consecutive memory locations (words or bytes).
Code 1st Name Last Address Total Pointers
Name Purchase
100 Mahmoudi Saad 12, Rue de la liberté Alger 500
200 Soltani Karima 33, bd des martyrs –23000- 2000
300 Masmoudia Walid Cité des amandiers, B5, N° 5000
8 Guelma
24
4. Databases Management systems (DBMS)
4.1 Definition: A Database Management System (DBMS) is a set
of programs for creating, managing and manipulating data.
It allows :
➢ Managing a set of files (database).
➢ Allows users to extract or store data.
25
4. Databases Management systems (DBMS)
4.2 DBMS Functions
The DBMS allows the following functions to be performed:
a) Create the DB (Data Description Language : DDL)
b) Update the DB structure (DDL)
c) Insert Data in the DB (Data Manipulation Language : DML)
d) Manipulate Data in search of information (query, search) (DML)
e) Visualize data (DML)
f) Administer DB (DB control): integrity constraints, logging,
transactions (Data Constraints Language : DCL)
g) DB security: access rights, confidentiality, recovery after failure..
Examples of DBMS:
▪ PC : Access, Foxpro, Paradox …
▪ Main frames: Oracle, DB2, Sybase, SQL Server, …
▪ Freewares et Sharewares : MySQL, MSQL, … 26
5. Types of Data models
5.1 Concept of Data model
⚫ The result of database design is a data description.
⚫ Data description is done using a data model.
⚫ The data model is an intellectual tool for understanding
the logical organization of data.
⚫ It is a set of concepts and rules for constructing a
representation of reality using data types.
Data model concept: allow the DB designer to build an
organizational representation of the company’s data.
27
Concepts Rules Formalism
28
6. Relational Model
2nd Generation (80s)
5. Entity-Association Model
2nd Generation (80s)
Types of data models
4. Object Models
5. Types of Data modeles
5.2 Different types of data models
3. Semantic Models
2. Network Model
1st Generation (70s)
1. Hierarchical Model
1st Generation (70s)
5. Types of Data modeles
5.2 Different types of data models
a. The Hierarchical model: TREE (60- early 70s)
Data is organized into a tree-like structure with parent-child
relationships. The hierarchical database (HDB) is composed of a
collection of trees.
➢ A virtual root that serves as an initial structure holds pointers to
sub-trees.
➢ Each child node has a single parent (Root).
➢ Relationships are represented as links between nodes.
29
5. Types of Data modeles
5.2 Different types of data models
a. The Hierarchical model: TREE (60- early 70s)
30
5. Types of Data modeles
5.2 Different types of data models
a. The Hierarchical model: Tree (60- early 70s)
Parent-child
relationship
31
5. Types of Data modeles
5.2 Different types of data models
b. Network Model: GRAPH (60- early 70)
➢ It extends the hierarchical model by allowing many-to-many
relationships between nodes.
(i.e. many-to-many relationships between related records).
➢ A record can be a member or child in multiple sets.
➢ This allows complex relationships to be translated.
A B C A
Parent-Child
Relationship
A child can have
several parents
32
5. Types of Data modeles
5.2 Different types of data models
b. Network Model: ou GRAPH (60- early 70)
33
5. Types of Data modeles
5.2 Different types of data models
c. The semantic model:
➢ Captures the meaning and relationships between data elements.
➢ Provides a more complete and meaningful representation of the
data.
➢ It emphasizes the relationships between data and their real-world
meanings.
34
5. Types of Data modeles
5.2 Different types of data models
d. The object model
➢ It performs a junction between object-oriented programming
concepts (Object, classes, attributes, methods, Encapsulation,
Polymorhism, inheritance), and database management area (entities,
attributes, relations…).
➢ This model is particularly useful for applications that deal with
complex, interrelated data.
35
5. Types of Data modeles
5.2 Different types of data models
d. The Entity/Relationship model
➢ Higher level of abstraction,
➢ Set of entities (abstract or concrete objects),
➢ Semantic associations or links between entities.
MAGASIN
EMPLOYE PRODUIT
Numéro-Magasin
Matricule N° Produit
Désignation
Nom Désignation
Superficie
Grade Couleur
Localisation
Salaire Prix-unitaire
Spécialité
EMPLOYE
PRODUIT
Matricule ( 0,1) MAGASIN
( 1,N)
STOCKER Code Produit
Nom Numéro Magasin
(0, 1)
( 1,N) Libellé
Travailler Désignation
Grade
Prix unitaire
Salaire Superficie
Stock de sécurité
Spécialité Localisation
36
5. Types of Data modeles
5.2 Different types of data models
d. The Entity/Relationship model
5 concepts
[Link] 2. Entity 3. Relationship 4. Identifier of
(property) Management Semantic link an entity /
Smallest piece object between 2 or more relationship
of information (concrete or entities A particular
abstract) property
5. Cardinalities)
(Min, Max)
Min : 0, 1
Max: 1, N
(0,1)-(0,N)
(1,1)-(1,N)
37
5. Types of Data modeles
5.2 Different types of data models
d. The Entity/Relationship model
Hospitalisation
Hospit.
38
5. Types of Data modeles
5.2 Different types of data models
e. The Relationnal model: Introduced by E. F. Codd in the 1970s.
➢ Data is organized in the form of tables, called relations.
➢ Each table is composed of rows and columns,
➢ Each row represents a record and each column represents an
attribute.
Car (Reg-number, Brand, Color, Power)
Attributes
Car Reg-Number Brand Color Power
100-123-24 Renault Bleue 7 Ch
200-311-23 Renault Blanche 5 Ch
Tuples
Relation 199-120-16 Peugeot Noir 6 Ch
or Records
Name 007-121-14 Fiat Blanche 5 Ch
39
6. Conclusion
1. Storing and manipulating data with FMS are too limited.
2. Interest in organizing, storing and manipulating data in
business in order to facilitate decision-making.
3. Different data models have been proposed.
4. How to represent data with minimum redundancy
and avoid inconsistencies ?
5. A database is a set of structured and integrated information.
6. A DBMS is software that ensures the creation and
manipulation of data in a database.
7. Relational databases represent data in the DB
form of tables. 40