View
5.430
Download
2
Category
Preview:
DESCRIPTION
Avant d'aborder le SQL
Citation preview
Le modèle relationnel
8Les structures de données8Les règles d’intégrité structurelle8L’algèbre relationnelle
Le modèle relationnel
� Inventé par E. F. Codd en 1970
� Fondé sur la théorie mathématique des
2
� Fondé sur la théorie mathématique des ensembles et sur la notion de base qui lui est rattachée : la relation.
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 cohérence et de redondance des données.
3
cohérence et de redondance des données.� 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
Le modèle relationnel :Les 10 concepts clés (1/2)
� La définition du domaine d’un attribut
� Le concept de relation
4
� Les attributs
� Les tuples
� Le schéma d’une relation
Le modèle relationnel :Les 10 concepts clés (2/2)
� La clé
� La contrainte référentielle
5
� Les valeurs nulles
� La contrainte d’entité
� La contrainte de domaine
Les structures de données : Domaine d’un attribut
� Définition : ensemble des valeurs prises par un attribut
� Un domaine se définit soit :
6
� Un domaine se définit soit :• en extension
8ex. : couleur_yeux = {bleu, vert, marron, noir}
• en compréhension8ex. : âge_enfants = {0, 15}
Définition du produit cartésien:Rappel
Soient deux ensembles O et P, on appelle Produit cartésiende O et P, l'ensemble noté O×P , des couples (x,y) où x∈O et
7
noté O×P , des couples (x,y) où x∈O et y∈P.
Étudiants={ A , T , M }
Professeurs = { L , Y }
Étudiants ×Professeurs={ (A,L) , (A,Y) , (T,L) , (T,Y) , (M,L) , (M,Y) }
Les structures de données : la relation
Nom de la relation
PRODUIT
Attributs , champs
N um _prod N om _prod Q té_stock
Définition : Sous-ensemble du produit cartésien d’un ensemble d’attributs
8
Occurrences , Tuples, Enregistrements
♦ 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é
N um _prod N om _prod Q té_stock
2414167
C haiseTab leL it
632712
Caractérisée par :
Les structures de données : Schéma de relations
4 Le schéma de relation représente la structure invariante d’une relation.
4Il est constitué du nom de la relation suivi de la liste des attributs et de leurs domaines associés
9
attributs et de leurs domaines associés
PRODUIT (N°PRODUIT : entier, NOM : chaîne, QTE EN STOCK : entier>0)
Nom
relation
Attributs (clé)
(souligné)Attributs non clé
Règles d’intégrité structurelle
� Assertions qui doivent être vérifiées par les données contenues dans la base.
10
• Règles inhérentes au modèle relationnel
Règles d’intégrité structurelle du modèle relationnel
�Unicité de clé
�Contrainte de référence
11
�Contrainte de référence
�Contrainte d’entité
�Contrainte de domaine
Clé d’une relation
� Une relation étant un ensemble de tuples, il ne peut y avoir deux tuples identiques dans une relation.
12
� Clé : ensemble minimal d’attributs dont la connaissance des valeurs permet d’identifier un tuple unique
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.
13
8Elles définissent des liens obligatoires entre relations.
8Garantissent le succès des opérations de mise à jour.
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.
14
clés étrangères existent dans les relations référencées.
8Lors 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
– soit il la répercute en cascade.
Contrainte d’entité
� Lorsque la valeur d’un attribut est inconnue,on utilise une valeur conventionnelle appeléevaleur nulle
15
� La contrainte d’entité impose que touterelation possède une clé primaire et que toutattribut participant à cette clé primaire soitnon null.
Contrainte de domaine
� Les valeurs d’un attribut doivent vérifier une assertion logique
16
une assertion logique
Algèbre relationnelle :Définition
� Collection d’opérations formelles qui
agissent sur des relations et produisent une
relationenrésultat.
17
relationenrésultat.
� 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.
Algèbre relationnelle : Opérateurs relationnels
• Opérateurs unaires:8Sélection8Projection8Complément
• Opérateurs binaires :
18
• Opérateurs binaires :8Union8Intersection8Différence8Les produits et Jointures
8Division
Théorie des ensembles
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 DATE MONTANT
28 Octobre 1986
19
28293052
OctobreOctobreNovembreDécembre
198620241610512
La sélection permet de répondre à la question : Donnez lescommandes passées après le mois d’octobre
N° COMMANDE DATE MONTANT
3052
NovembreDécembre
1610512
La condition peut contenir plusieurs critères
Algèbre relationnelle : Sélection (ou restriction) (2/3)
� Opération sur une relation R1produisant une relation R2de même schéma, mais comportant les seuls tuples qui vérifient la condition précisée en argument.
20
� Notation : σcondition (R1)
R2=selection (R1,condition)
Algèbre relationnelle : Sélection (ou restriction) (3/3)
� Opérateurs de comparaison=, ≠, <, >, ≤, ≥, BETWEEN, IN, LIKE
21
� Combinaison de plusieurs conditions reliées par des opérateurs logiques :
AND, OR, NOT
� Valeurs NULL
Algèbre relationnelle : Projection (1/2)
Consiste à supprimer des attributs d’une relation
Soit la relation ETUDIANT :
Num_étu Nom_étu Nom_départ Adr_départ
521632
Nom1Nom2
InformatiqueMathématique
FesMeknes
22
632569451
Nom2Nom3Nom1
MathématiqueInformatiqueInformatique
MeknesRabatRabat
La projection sur nom_étu, nom_départdonne :
Nom_étu Nom_départ
Nom1Nom2Nom3
InformatiqueMathématiqueInformatique
La projection sur nom_départ, adr_départdonne :
Nom_départ Adr_départ
InformatiqueMathématiqueInformatique
FesMeknesRabat
Algèbre relationnelle : Projection (2/2)
� Opération sur une relation R1consistant à composer une relation R2 en enlevant à la relation initiale tous les attributs non mentionnés en opérandes.
23
mentionnés en opérandes.
� Notation: π A1, A2, …, An (R1)
R2=projection(R1;A1,A2,…,An)
Algèbre relationnelle : Complément (1/2)
Consiste à construire la relation qui contient toutes les occurrencesqui n’existent pas (c’est la relation qui exprime le FAUX)
Soit la relation R :
Professeur Elève
Prof1 Nom2Le complément de R sera :
24
Prof1Prof2Prof2Prof2Prof3Prof3
Nom2Nom1Nom2Nom1Nom4Nom1Nom4
Professeur Elève
Prof1Prof3
Nom4Nom2
Algèbre relationnelle : Complément (2/2)
� Ensemble des tuples du produit cartésien des domaines des attributs d’une relation n’appartenant pas à cette relation
25
n’appartenant pas à cette relation
� Notation: Not (R1)
R2=Comp (R1)
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 lesmêmes attributs.
Soit la relation OUVRIERNum_empl Nom_empl Num_empl Nom_empl
Soit la relation CADRE
26
Num_empl Nom_empl
151756
Nom1Nom3Nom2
Num_empl Nom_empl
321
Nom5Nom6
L’union permet de construire la relation EMPLOYE
Num_empl Nom_empl
321151756
Nom5Nom6Nom1Nom3Nom2
Algèbre relationnelle : Union (2/2)
� Opération portant sur deux relations de même schéma R1et R2, consistant à construire une relation de même schéma
27
construire une relation de même schéma R3ayant pour tuples ceux appartenant à R1ou (inclusif) R2.
� Notation: R1∪R2
R3=Union (R1,R2)
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
Num_empl Nom_empl
Soit la relation CHEF DE SERVICENum_empl Nom_empl
3 Nom5
28
Num_empl Nom_empl
31528
Nom5Nom1Nom4
Num_empl Nom_empl
315
Nom5Nom1
L’intersection permet de construire la relation suivante
3211556
Nom5Nom6Nom1Nom2
Algèbre relationnelle : Intersection (2/2)
� Opération portant sur deux relations de même schémaR1etR2consistant à construire une relation de même schéma R3 ayant pour tuples ceux appartenant à la
29
R3 ayant pour tuples ceux appartenant à la fois à R1et R2.
� Notation: R1∩ R2
R3=Intersection (R1,R2)
Algèbre relationnelle : Différence (1/2)
Permet d’obtenir les occurrences de la relation 1 qui n’appartiennentpas à la relation 2. Les deux relations doivent avoir les mêmesattributs. Cette opération n’est pas commutative.
Soit la relation INSCRITS Soit la relation RECUS
30
Nom_étu Nom_UV
Nom2Nom5Nom2
MathsMathsChimie
Nom_étu Nom_UV
Nom2Nom5
PhysiqueChimie
La différence permet de construire la relation suivante
Nom_étu Nom_UV
Nom2Nom5Nom2Nom2Nom5
MathsMathsPhysiqueChimieChimie
Algèbre relationnelle : Différence (2/2)
� Opération portant sur deux relations de même schéma R1et R2, consistant à construire une relation de même schéma R3 ayant pour tuples ceux appartenant à R1
31
R3 ayant pour tuples ceux appartenant à R1et n’appartenant pas à R2.
� Notation : R1– R2
R3=Difference(R1,R2)
Algèbre relationnelle : Produit cartésien (1/2)
Le produit cartésien se construit en combinant toutes les possibilités.
Couleur Edition
RougeBlanc
LuxeBroché
Titre Auteur
XY
Nom2Nom1
Soit la relation LIVRE Soit la relation EDITION
32
BlancVert Cartonné
Le produit cartésien permet de construire la relation suivante
Y
Titre Auteur Couleur Edition
XXXYYY
Nom2Nom2Nom2Nom1Nom1Nom1
RougeBlancVertRougeBlancVert
LuxeBrochéCartonnéLuxeBrochéCartonné
Algèbre relationnelle : Produit cartésien (2/2)
� Opération portant sur deux relationR1et R2, consistant à construire une relation R3 ayant pour schéma la concaténation de ceux des relations opérandes et pour tuples
33
ceux des relations opérandes et pour tuples toutes les combinaisons des tuples des relations opérandes
� Notation:R1× R2
R3=Produit (R1,R2)
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 <>
Nom_chef Salaire_chef
Nom2 25000
Nom_emp Salaire_emp
E1 20000
Soit la relation EMPLOYE Soit la relation CHEF
34
Nom2Nom1
2500012000
E1E2E3
20000100006000
Le thêta-produit permet de répondre à la question : Donnez le nomdes employés qui gagnent plus qu’un chef de service
On effectue d’abord un produit cartésien puis une sélection dontla condition est salaire_emp > salaire_chef
Algèbre relationnelle : Thêta-produit (thêta-jointure) (2/4)
E1E1E2E2
Nom2Nom1Nom2Nom1
25000120002500012000
Produit cartésien
Nom_emp Salaire_emp Nom_chef Salaire_chef
20000200001000010000
35
E3E3
Nom1Nom2Nom1
120002500012000
1000060006000
Sélection
E1 Nom1 12000
Nom_emp Salaire_emp Nom_chef Salaire_chef
20000
Algèbre relationnelle : Thêta-produit (thêta-jointure) (3/4)
� Opérande consistant à rapprocher selon une condition les tuples de deux relations R1et R2afin de former une relation R3qui contient l’ensemble de tous les tuples
36
contient l’ensemble de tous les tuples obtenus en concaténant un tuple de R1et un de R2vérifiant la condition de rapprochement.
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.
38
02107262
S1S8S6S1
Num_emp Nom_emp Num_service
Nom2Nom1Nom2Nom4
Soit la relation EMPLOYE Soit la relation SERVICE
Num_service Nom_service
S1S6S8S4
InformatiqueMathématiquesSociologieAnglais
Algèbre relationnelle : Jointure naturelle (équijointure) (2/4)
La jointure naturelle permet de répondre à la question : Donnez lenom des employés et le nom de leur service.
021072
S1S8
InformatiqueSociologie
Num_emp Nom_emp Num_service Nom_service
Nom2Nom1
39
7262
S6S1
SociologieMathématiquesInformatique
Nom2Nom4
Rq : Le service S4 Anglais qui n’a pas « d’associé », n’est pasprésent dans la jointure.
La jointure naturelle est l’une des opérations fondamentales del’algèbre relationnelle.
Algèbre relationnelle : Jointure naturelle (équijointure) (3/4)
� Opérande consistant à rapprocher les tuples de deux relations R1et R2afin de former une troisième relation R3 dont les attributs
40
une troisième relation R3 dont les attributs sont l’union des attributs de R1et R2, et dont les tuples sont obtenus en composant un tuple de R1et un tuple de R2ayant mêmes valeurs pour les attributs de même nom.
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. Onleur associe alors la valeur nulle (symbole ).
Num_emp Nom_emp Num_service
Soit la relation EMPLOYE Soit la relation SERVICE
42
0210726225
S1S8S6S1S5
Num_emp Nom_emp Num_service
emp1emp2 emp1emp3emp4
Num_service Num_bâtiment
S1S6S8S4S2
B8B9B3B3B1
Algèbre relationnelle : Jointure externe (2/3)
La jointure extérieure conduit à la relation :
021072
S1S8
B8B3
Num_emp Nom_emp Num_service Num_bâtiment
emp1emp2 emp1
43
726225
S6S1S5S4S2
B3B9B8
B3B1
emp1emp3emp4
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 valeurs nulles pour les attributs non
44
valeurs nulles pour les attributs non renseignés.
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.
Exemple de semi-jointure droite :
45
Exemple de semi-jointure droite :
0210726225
S1S8S6S1S5
Num_emp Nom_emp Num_service
emp1emp2 emp1emp3emp4
Soit la relation EMPLOYE Soit la relation SERVICE
Num_service Num_bâtiment
S1S6S8S4S2
B8B9B3B3B1
Algèbre relationnelle : Jointure externe gauche (2/3)
021072
S1S8
B8B3
Num_emp Nom_emp Num_service Num_bâtiment
emp1emp2 emp1
Le résultat de la semi-jointure gauche donne le résultat suivant :
46
726225
S6S1S5
B3B9B8_
emp1emp3emp4
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 R1et R1.
� Soit la relation Enseignant
48
1256274351
AssistantMDCAssistantMDCMDC
9 50010 50011 50012 00012 500
Num_ens Nom_ens Grade Salaire
emp1emp2 emp1emp3emp4
Algèbre relationnelle : Auto-jointure (2/2)
� L’auto-jointure permet de répondre à la question suivante : « Donnez le nom des assistants qui gagnent plus qu’un MDC ?»
49
assistants qui gagnent plus qu’un MDC ?»
� Relation résultat
Nom_ens
emp1
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
50
Nom_étu
Etu1Etu2
Nom_étu Nom_prof
Etu1Etu2Etu3Etu4Etu1Etu2Etu3
Prof3Prof1Prof3Prof4Prof1Prof3Prof4
Soit la relation suivante Soit la relation suivante
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.
51
Nom_prof
Prof3Prof1
La relation résultat est :
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
8Opérateurs arithmétiques
53
8Opérateurs arithmétiques+ - / *
8Fonctions» somme
» moyenne
» maximum, minimum
» compte
Fonctions de calcul (2/2)
� Ces fonctions sont liées à une opération• Par exemple, avec une projection on a :
π A1, A2*A 4-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 valeurs de chaque groupe par application
55
valeurs de chaque groupe par application d’une fonction de calcul.
Les agrégats (2/2)
� Notation : Agrégat (R1 ; attribut de regroupement ; attribut de calcul)
• CumulR = cumul(relation, attribut de regroupement, attribut de calcul)
56
R = cumul(relation, attribut de regroupement, attribut de calcul)
Ou
R=regrouper_calculer (relation,attribut de regroupement, cumul(attribut de calcul))
• CompteR = compte(relation, attribut de regroupement)
Recommended