Le modèle relationnel 8Les
structures de données
8Les
règles d’intégrité structurelle
8L’algèbre
relationnelle
Le modèle relationnel Inventé par E. F. Codd en 1970
Fondé sur la théorie mathématique des ensembles et sur la notion de base qui lui est rattachée : la relation.
2
Objectifs d’un modèle relationnel: Rappel Proposer une indépendance des données et
des traitements. Permettre de gérer les problèmes de
. Utiliser des structures de données simples et issues de la vie courante (tables). Proposer des langages de manipulation de données non procéduraux Permettre des vues utilisateurs différentes des relations implantées
3
Le modèle relationnel : Les 10 concepts clés (1/2) La définition du domaine d’un
attribut
Le concept de relation Les attributs Les tuples Le schéma d’une relation 4
Le modèle relationnel : Les 10 concepts clés (2/2) La clé La contrainte référentielle Les valeurs nulles La contrainte d’entité La contrainte de domaine 5
Les structures de données : Domaine d’un attribut Définition : ensemble des valeurs prises
par un attribut n oma ne se
n so :
• en extension ex. : couleur_yeux = {bleu, vert, marron, noir}
8
• en compréhension ex. : âge_enfants = {0, 15}
8
6
Définition du produit cartésien: Rappel Soient deux ensembles O et P, on appelle Produit cartésien de O et P, l'ensemble not × , es coup es x,y o x∈ et y∈P. Étudiants={ A , T , M } Professeurs = { L , Y } Étudiants ×Professeurs={ (A,L) , (A,Y) , (T,L) , (T,Y) , (M,L) , (M,Y) } 7
Les structures de données : la relation Définition : Sous-ensemble du produit cartésien d’un ensemble d’attributs Nom de la relation Attributs , champs
PRODUIT _ Occurrences , Tuples, Enregistrements
24 141 67
_ Chaise Table L it
_ 63 27 12
Caractérisée par : ♦ Cardinalité de la relation (nombre de lignes) ♦ Unicité du nom des attributs ♦ Indifférence de l’ordre des lignes et des colonnes ♦ Nombre de colonnes = degré
8
Les structures de données : Schéma de relations Le schéma de relation représente la structure invariante d’une relation. 4
Il est constitué du nom de la relation suivi de la liste des
4
PRODUIT (N°PRODUIT : entier, NOM : chaîne, QTE EN STOCK : entier>0)
Nom
Attributs (clé)
relation
(souligné)
Attributs non clé 9
Règles d’intégrité structurelle Assertions qui doivent être vérifiées par les
données contenues dans la base. • Règles inhérentes au modèle relationnel
10
Règles d’intégrité structurelle du modèle relationnel Unicité
de clé
Contrainte de référence Contrainte d’entité Contrainte de domaine 11
Clé d’une relation Une relation étant un ensemble de
tuples, il ne peut y avoir deux tuples identiques dans une relation.
Clé : ensemble minimal d’attributs dont la
connaissance des valeurs permet d’identifier un tuple unique 12
Contrainte référentielle(1/2)
Contrainte d’intégrité portant sur une relation R1, consistant à imposer que la valeur d’un groupe d’attributs apparaisse comme valeur de clé dans une autre relation R2. Elles définissent des liens obligatoires entre relations. 8Garantissent le succès des opérations de mise à jour. 8
13
Contrainte référentielle(2/2)
Les contraintes de référence sont liées à la notion de clés étrangères : 8Lors de l’insertion d’un tuple dans une relation soumise à une contrainte référentielle (« relations référençantes ») , il faut vérifier que les valeurs de clés étrangères existent dans les relations référencées. Lors de la suppression d’un tuple dans une relation référencée, il faut vérifier qu’aucun tuple de la relation référençante ne fasse référence au tuple que l’on souhaite supprimer. – soit le SGBD refuse la suppression 14 – soit il la répercute en cascade.
8
Contrainte d’entité Lorsque
la valeur d’un attribut est inconnue, on utilise une valeur conventionnelle appelée valeur nulle
La
contrainte d’entité impose que toute relation possède une clé primaire et que tout attribut participant à cette clé primaire soit non null. 15
Contrainte de domaine Les valeurs d’un attribut doivent vérifier
16
Algèbre relationnelle : Définition Collection
d’opérations formelles qui agissent sur des relations et produisent une .
Dans
la plupart des systèmes relationnels, la réponse à une requête s’obtient par l’utilisation d’un ou plusieurs opérateurs relationnels. 17
Algèbre relationnelle : Opérateurs relationnels • Opérateurs unaires : Sélection 8Projection 8Complément • Opérateurs binaires : 8Union 8Intersection 8Différence 8Les produits et Jointures 8Division 8
Théorie des ensembles
18
Algèbre relationnelle : Sélection (ou restriction) (1/3) Elimination des occurrences de la relation qui ne satisfont pas à une condition donnée. Ex. : Considérons la relation Commande N° COMMANDE 29 30 52
DATE Octobre Novembre Décembre
MONTANT 2024 1610 512
La sélection permet de répondre à la question : Donnez les commandes passées après le mois d’octobre N° COMMANDE 30 52
DATE Novembre Décembre
MONTANT 1610 512
La condition peut contenir plusieurs critères 19
Algèbre relationnelle : Sélection (ou restriction) (2/3)
Opération sur une relation R1 produisant une relation R2 de même schéma, mais comportant les seuls tuples qui vérifient la condition précisée en argument. Notation : σcondition ( R1) R2=selection ( R1,condition)
20
Algèbre relationnelle : Sélection (ou restriction) (3/3) Opérateurs de comparaison =, ≠, <, >, ≤, ≥, BETWEEN, IN, LIKE Combinaison de plusieurs conditions
reliées par des opérateurs logiques : AND, OR, NOT
Valeurs NULL 21
Algèbre relationnelle : Projection (1/2) Consiste à supprimer des attributs d’une relation Soit la relation ETUDIANT : Num_étu
Nom_étu
521
Nom1
569 451
Nom3 Nom1
La projection sur nom_étu, nom_départ donne : Nom_étu Nom1 Nom2 Nom3
Nom_départ Informatique Mathématique Informatique
Nom_départ Informatique a ma que Informatique Informatique
Adr_départ Fes Meknes Rabat Rabat
La projection sur nom_départ , adr_départ donne : Nom_départ
Adr_départ
Informatique Mathématique Informatique
Fes Meknes Rabat 22
Algèbre relationnelle : Projection (2/2) Opération sur une relation R1
consistant à composer une relation R2 en enlevant à la relation initiale tous les attributs non ment onn s en op ran es.
Notation: π A1, A2, …, An ( R1)
R2=projection( R1;A1,A2,…,An) 23
Algèbre relationnelle : Complément (1/2) Consiste à construire la relation qui contient toutes les occurrences qui n’existent pas (c’est la relation qui exprime le FAUX) Soit la relation R :
Professeur
Elève
Prof1
Nom2
Prof1
Nom1
Prof2
Nom2
Prof2
Nom1
Prof2
Nom4
Prof3
Nom1
Prof3
Nom4
Le complément de R sera : Professeur
Elève
Prof1 Prof3
Nom4 Nom2
24
Algèbre relationnelle : Complément (2/2) Ensemble des tuples du produit cartésien
des domaines des attributs d’une relation n’a artenant as à cette relation Notation: Not ( R1)
R2=Comp ( R1)
25
Algèbre relationnelle : Union (1/2) Permet de fusionner deux relations en une seule. Cette opération n’est possible que sur des relations ayant les mêmes attributs. Soit la relation OUVRIER Soit la relation CADRE _ 15 17 56
_ Nom1 Nom3 Nom2
_ 3 21
_ Nom5 Nom6
L’union permet de construire la relation EMPLOYE Num_empl 3 21 15 17 56
Nom_empl Nom5 Nom6 Nom1 Nom3 Nom2
26
Algèbre relationnelle : Union (2/2) Opération portant sur deux relations de
même schéma R1 et R2, consistant à R3 ayant pour tuples ceux appartenant à R1 ou (inclusif) R2. Notation: R1∪ R2
R3=Union (R1,R2) 27
Algèbre relationnelle : Intersection (1/2) Permet de fournir des occurrences présentes dans l’une et l’autre des relations. Cette opération n’est possible que sur des relations ayant les mêmes attributs. Soit la relation INGENIEUR Soit la relation CHEF DE SERVICE Num_empl 21 15 56
Nom_empl om Nom6 Nom1 Nom2
Num_empl 3 15 28
Nom_em l Nom5 Nom1 Nom4
L’intersection permet de construire la relation suivante Num_empl 3 15
Nom_empl Nom5 Nom1 28
Algèbre relationnelle : Intersection (2/2) Opération portant sur deux relations de
même schéma R1 et R2 consistant à construire une relation de même schéma R3 a ant our tu les ceux a artenant à la fois à R1 et R2. Notation: R1 ∩ R2
R3=Intersection (R1,R2) 29
Algèbre relationnelle : Différence (1/2) Permet d’obtenir les occurrences de la relation 1 qui n’appartiennent pas à la relation 2. Les deux relations doivent avoir les mêmes attributs. Cette opération n’est pas commutative. Soit la relation INSCRITS Nom_étu Nom2 Nom5 Nom2 Nom2 Nom5
Soit la relation RECUS
Nom_UV Maths Maths Physique Chimie Chimie
Nom_étu Nom2 Nom5 Nom2
Nom_UV Maths Maths Chimie
La différence permet de construire la relation suivante Nom_étu
Nom2 Nom5
Nom_UV
Physique Chimie
30
Algèbre relationnelle : Différence (2/2) Opération portant sur deux relations de
même schéma R1 et R2, consistant à construire une relation de même schéma R3 ayant pour tuples ceux appartenant à R1 et n’appartenant pas à R2. Notation : R1 – R2
R3=Difference(R1,R2) 31
Algèbre relationnelle : Produit cartésien (1/2) Le produit cartésien se construit en combinant toutes les possibilités. Soit la relation LIVRE Soit la relation EDITION Titre X Y
Couleur Rouge Blanc Vert
Auteur Nom2 Nom1
Edition Luxe Broché Cartonné
Le produit cartésien permet de construire la relation suivante Titre X X X Y Y Y
Auteur Nom2 Nom2 Nom2 Nom1 Nom1 Nom1
Couleur Rouge Blanc Vert Rouge Blanc Vert
Edition Luxe Broché Cartonné Luxe Broché Cartonné 32
Algèbre relationnelle : Produit cartésien (2/2) Opération portant sur deux relation R1 et
R2, consistant à construire une relation R3
ayant pour schéma la concaténation de toutes les combinaisons des tuples des relations opérandes Notation: R1 × R2
R3=Produit (R1,R2) 33
Algèbre relationnelle : Thêta-produit (thêta-jointure) (1/4) Le thêta-produit consiste en un produit cartésien doublé d’une sélection. On ne retient que les occurrences qui vérifient une condition logique. Thêta prend les valeurs : <, <=, >, >=, != ou <> Soit la relation EMPLOYE Nom_emp E1 E2 E3
Salaire_emp 10000 6000
Soit la relation CHEF Nom_chef Nom2 Nom1
Salaire_chef 12000
Le thêta-produit permet de répondre à la question : Donnez le nom des employés qui gagnent plus qu’un chef de service
On effectue d’abord un produit cartésien puis une sélection dont la condition est salaire_emp > salaire_chef 34
Algèbre relationnelle : Thêta-produit (thêta-jointure) (2/4) Produit cartésien Nom_emp E1 E1 E2 E2 E3 E3
Salaire_emp 20000 20000 10000 10000 6000 6000
Nom_chef Nom2 Nom1 Nom2 Nom1 Nom2 Nom1
Salaire_chef 25000 12000 25000 12000 25000 12000
Sélection Nom_emp E1
Salaire_emp 20000
Nom_chef Nom1
Salaire_chef 12000
35
Algèbre relationnelle : Thêta-produit (thêta-jointure) (3/4) Opérande consistant à rapprocher selon une
condition les tuples de deux relations R1 et R2 afin de former une relation R3 qui ’ obtenus en concaténant un tuple de R1 et un de R2 vérifiant la condition de rapprochement.
36
Algèbre relationnelle : Thêta-produit (thêta-jointure) (4/4) Notation: R1 θ θ condition R2
R3= θ-jointure( R1,R2; condition)
37
Algèbre relationnelle : Jointure naturelle (équijointure) (1/4) La jointure naturelle permet de réaliser une liaison logique entre deux tables. La condition de sélection est l’égalité entre les deux clés des deux relations. C’est un thêta-produit qui prend la valeur " = " entre des attributs identiques. Soit la relation SERVICE
Soit la relation EMPLOYE Num_emp 02 10 72 62
Nom_emp Nom2 Nom1 Nom2 Nom4
Num_service S1 S8 S6 S1
Num_service S1 S6 S8 S4
Nom_service Informatique Mathématiques Sociologie Anglais
38
Algèbre relationnelle : Jointure naturelle (équijointure) (2/4) La jointure naturelle permet de répondre à la question : Donnez le nom des employés et le nom de leur service. Num_emp 02 10 72 62
Nom_emp Nom2 Nom1 Nom2 Nom4
Num_service
Nom_service
S1 S8 S6 S1
Informatique Sociologie Mathématiques Informatique
Rq : Le service S4 Anglais qui n’a pas « d’associé », n’est pas présent dans la jointure. La jointure naturelle est l’une des opérations fondamentales de l’algèbre relationnelle. 39
Algèbre relationnelle : Jointure naturelle (équijointure) (3/4) Opérande consistant à rapprocher les tuples
de deux relations R1 et R2 afin de former une troisième relation R3 dont les attributs sont l’union des attributs de R1 et R2, et dont les tuples sont obtenus en composant un tuple de R1 et un tuple de R2 ayant mêmes valeurs pour les attributs de même nom. 40
Algèbre relationnelle : Jointure naturelle (équijointure) (4/4) Notation: R1 θ condition R2
R3=jointure(R1, R2; condition)
41
Algèbre relationnelle : Jointure externe (1/3) Il s’agit d’une jointure naturelle qui permet de faire figurer les occurrences qui n’ont pas « d’associé » dans l’autre relation. On leur associe alors la valeur nulle (symbole ). Soit la relation SERVICE
Soit la relation EMPLOYE _ 02 10 72 62 25
_
_ emp1 emp2 emp1 emp3 emp4
S1 S8 S6 S1 S5
Num_service S1 S6 S8 S4 S2
Num_bâtiment B8 B9 B3 B3 B1
42
Algèbre relationnelle : Jointure externe (2/3) La jointure extérieure conduit à la relation : Num_emp
Nom_emp
02 10 72 62 25
emp1 emp2 emp1 emp3 emp4
Num_service S1 S8 S6 S1 S5 S4 S2
Num_bâtiment B8 B3 B9 B8 B3 B1
43
Algèbre relationnelle : Jointure externe (3/3) R3 contient les tuples issus de la jointure
de R1 et R2 ainsi que ceux de R1 et R2 ne participant pas à la jointure, avec des renseignés.
44
Algèbre relationnelle : Jointure externe gauche (1/3) Permet de faire apparaître en totalité les occurrences d’une des deux relations. On définira une semi-jointure gauche ou une semi-jointure droite. Exem le de semi- ointure droite : Soit la relation SERVICE
Soit la relation EMPLOYE Num_emp 02 10 72 62 25
Nom_emp emp1 emp2 emp1 emp3 emp4
Num_service S1 S8 S6 S1 S5
Num_service S1 S6 S8 S4 S2
Num_bâtiment B8 B9 B3 B3 B1 45
Algèbre relationnelle : Jointure externe gauche (2/3) Le résultat de la semi-jointure gauche donne le résultat suivant : Num_emp 02 10 72 62 25
Nom_emp emp1 emp2 emp1 emp3 emp4
Num_service S1 S8 S6 S1 S5
Num_bâtiment B8 B3 B9 B8 _
46
Algèbre relationnelle : Jointure externe gauche (3/3) Opération portant sur R1 et R2 donnant en
résultat les tuples de R1 qui participent à la jointure des deux relations
47
Algèbre relationnelle : Auto-jointure (1/2) C’est une jointure naturelle dans laquelle
les deux relations initiales sont R1 et R1. Soit la relation Enseignant Num_ens 12 56 27 43 51
Nom_ens emp1 emp2 emp1 emp3 emp4
Grade Assistant MDC Assistant MDC MDC
Salaire 9 500 10 500 11 500 12 000 12 500
48
Algèbre relationnelle : Auto-jointure (2/2) L’auto-jointure permet de répondre à
la
question suivante : « Donnez le nom des ass stants qu gagnent p us qu un
»
Relation résultat Nom_ens emp1 49
Algèbre relationnelle : Division (1/3)
Permet d’obtenir les occurrences de R1 qui sont associées à toutes les occurrences de R2. Une relation est donc divisée par une autre relation contenant exclusivement des attributs de la première relation
Soit la relation suivante Nom_étu Etu1 Etu2 Etu3 Etu4 Etu1 Etu2 Etu3
Nom_prof Prof3 Prof1 Prof3 Prof4 Prof1 Prof3 Prof4
Soit la relation suivante Nom_étu Etu1 Etu2
50
Algèbre relationnelle : Division (2/3) La division permet de répondre à la question suivante : Donnez le nom des profs qui enseignent conjointement aux élèves figurant dans la seconde relation.
La relation résultat est :
Nom_prof Prof3 Prof1
51
Algèbre relationnelle : Division (3/3) Notation
R1 ÷ R2
52
Fonctions de calcul (1/2) Expression arithmétique construite à partir
d’attributs d’une relation, de constantes, d’opérateurs arithmétiques et de fonctions p ra eurs ar m ques +
-
/
*
Fonctions
8
» » » »
somme moyenne maximum, minimum compte 53
Fonctions de calcul (2/2) Ces fonctions sont liées à une opération
• Par exemple, avec une projection on a : π A1, A2*A4-10
( R1)
54
Les agrégats (1/2) Partitionnement horizontal d’une
relation en fonction des valeurs d’un groupe d’attributs, suivi d’un regroupement des d’une fonction de calcul.
55
