licence de sciences et techniques unité denseignement bases de donnees le modèle relationnel...
TRANSCRIPT
Licence de Sciences et TechniquesUnité d’enseignement BASES DE DONNEES
Le modèle relationnel
François JacquenetProfesseur d'InformatiqueFaculté des SciencesLaboratoire Hubert Curien – UMR CNRS 551618 rue Benoit Lauras42023 Saint-Etienne cedex 2Tél : 04 77 91 58 07e-mail : [email protected] : http://eurise.univ-st-etienne.fr/~fj
Que verrons nous aujourd’hui ?
Le modèle relationnel
Traduction EA --> Relationnel
Validation et transformation de modèle EA
Le modèle relationnel
Modèle de niveau logique, très simple
Défini par Ted Codd en 1970; prix Turing en 1986. Développé au centre de recherche d'IBM (Almaden, Californie)
Aujourd'hui utilisé par beaucoup de SGBD commerciaux (Oracle, Informix, DB2, Ingres, Sybase, dBase, Access …) et SIG
Modèle à deux concepts: relation (table) attribut (colonne)
Concepts de base
Etudiant (N°Etud, nom, prénom, age)
nom de la relation noms des attributs
N°Etud nom prénom age
136 Dupont Jean 19
253 Aubry Annie 20
101 Duval André 21
147 Dupont Marc 21
Etudiantschéma
population
tuple ouoccurrence
Schéma relationnel
une BD = un ensemble de relations
schéma d'une BD relationnelle = un ensemble de schémas de relation: R1, R2, …, Rx
schéma d'une relation = un ensemble d'attributs
Ri = (A1/d1, A2/d2, …, Ay/dy)
ou, plus simplement,
Ri = (A1, A2, …, Ay)
Règles de structuration
attributs: simples et monovalués (domaine de valeurs atomiques)
structure plate regulière
tuple
x: une et une seule valeur atomique par attribut
x x x x x x x x xx
w w w www
INTERDIT
Valeurs nulles
Un attribut peut ne pas être valué pour un tuple: on dit alors qu'il a une valeur nulle
exemple : on ne connaît ni l'age d'Annie ni le prénom de Duval
136 Dupont Jean 19
253 Aubry Annie NULL
101 Duval NULL 21
147 Dupont Marc 21
Les identifiants
Toute relation possède un identifiant (clé) il ne peut y avoir deux tuples identiques dans la même relation
L'identifiant n'admet pas de valeurs nulles
N°Etud nom prénom age
136 Dupont Jean 19
253 Aubry Annie 20
101 Duval André 21
147 Dupont Marc 21
Etudiant
Identifiant externe
Cours(NomC,horaire,prof)
Suit(N°Etud,NomC)
BD Mercredi 15-17 Duval
SE Mardi 16-19 Malin
Suit traduit un TA entreEtudiant et Cours.Elle comporte les identifiants de Etudiant et de Cours.Suit.NomC est un identifiant externe sur Cours.
253 SE
136 BD
253 BD
101 SE
Domaine de valeurs
Un domaine est un ensemble de valeurs atomiques que peut prendre un attribut (cf. EA)
Exemples de domaines: Dnom : chaînes de caractères de longueur maximale 30 Dnum : entiers compris entre 0 et 99999 Dcouleur : {"bleu", "vert", "jaune"} Dâge : entiers compris entre 16 et 65
Définition d'une relation
Une relation est définie par : son nom sa liste de couples <nom d'attribut : domaine> son (ses) identifiant(s) la définition de sa sémantique (phrase en français)
Exemple : Etudiant (N°Etud : Dnum, Nom : Dnom, Prénom : Dnom, Age : Dâge)
Identifiant : N°Etud
Définition : tout étudiant actuellement immatriculé à l'UJM
Contraintes de modélisation
Les notions d'attribut multivalué ou complexe n'existent pas dans le modèle relationnel. Il faut donc les modéliser autrement.
Pour un attribut complexe, il faut choisir entre le composé ou les composants
Pour un attribut multivalué, il faut créer une autre relation (ceci pour chaque attribut multivalué)
Représentation d'attribut complexe
Soit adresse : nom_rue , num , ville, code
Solution 1 un attribut par composant:
nom_rue , num , ville, NPA
"Rue de la presse", "5", "Saint-Etienne", "42000"
il est éventuellement possible de définir par ailleurs une vue restituant la notion globale d'adresse
Solution 2 un attribut adresse dont le domaine est une chaine de caractères
"Rue de la presse 5 Saint-Etienne 42000"
adresse
nom_rue num ville code
Représentation d'attribut multivalué
Exemple: mémoriser les différents prénoms des étudiants Solution incorrecte
Plusieurs attributs : Prénom1, Prénom2,…
Solution correcte : créer une relation supplémentaire:
PrénomsEtudiants( Num_Etud , Prénom )
136 Jean 136 Marie101 André253 Annie253 Claudine
Ou liste ordonnée: EtudPrénoms2 (N°Etud, N°Prénom, Prénom)
Identifiant d'une relation
Une relation peut avoir plusieurs identifiants
PrénomsEtudiants2 (N°Etud, N°Prénom, Prénom) N°Etud + N°Prenom N°Etud + Prenom
Définition : L'identifiant d'une relation est un ensemble minimum d'attributs de la relation, tel qu'il n'existe pas 2 tuples ayant même valeur pour cet identifiant.
Règle : tous les attributs de tout identifiant doivent toujours avoir une valeur connue (non nulle).
Identifiants externes
Décrivent des liens entre relations Suit (N°Etud : Dnum, NomC : Dnom)
N°Etud référence un Etudiant NomC référence un Cours
Si la relation référencée possède plusieurs identifiants, il faut préciser:
N°Etud référence un Etudiant.N°Etud Vérification de l’intégrité référentielle assurée par le SGBD:
les identifiants externes désignent nécessairement des tuples existants.
Récapitulatif
Un schéma relationnel se compose:
pour chaque relation de: nom de la relation définition attributs + domaines identifiant(s) éventuellement identifiant(s) externe(s) contraintes d'intégrité associées
et des autres contraintes d'intégrité qui portent sur plusieurs relations.
Exemple de schéma relationnel
Domaines :
Dnom : chaînes de caractères de longueur inférieure à 30
Dch100 : chaînes de caractères de longueur inférieure à 100
Dannée : [1970 , 1990 ] Dnote : [0.0 , 20.0 ] Ddate : [1 , 31] / [1 , 12] / [1920 , 1990]
Exemple de schéma relationnel
Relation : PersonneAttributs : n°P : entier sans nul
nom : Dnom sans nuladr : Dch100 sans nul
Identifiant: : n°P Définition: tout étudiant et tout enseignant de l'école (état actuel).
Relation : PersonnePrénoms(permet d'avoir une liste de prénoms pour une personne donnée)
Attributs : n°P : entier sans nulprénom : Dnom sans nul
Identifiant: : n°P + prénomIdentifiant externe : n°P référence une PersonneDéfinition : prénoms des personnes
Exemple de schéma relationnel
Relation : EtudiantAttributs : n°P : entier sans nul
n°E : entier sans nuldateN : Ddate sans nul
Identifiants : (n°E ) (n°P )
Identifiant externe : n°P référence une PersonneDéfinition : tout individu qui est actuellement inscrit à l'école, ou qui a déjà passé avec
succès un des cours de l'école
Relation : EtudiantEtudesAttributs : n°E : entier sans nul
année : Dannée sans nuldiplôme : Dnom sans nul
Identifiant: (n°E + diplôme ) Identifiant externe : n°E référence un Etudiant.n°E Définition : études antérieures des étudiants
Exemple de schéma relationnel
Relation : EnseignantAttributs: n°P : entier sans nul
tel: : entier sans nulstatut : Dnom sans nulbanque : Dnom sans nulagence : Dnom sans nulcompte : entier sans nul
Identifiant : (n°P )Identifiant externe : n°P référence une PersonneDéfinition : tout individu assurant actuellement un ou plusieurs cours à l'école
Relation : CoursAttributs : nomC : Dnom sans nul
cycle : entier sans nuln°Ens : entier sans nul
Identifiant : (nomC )Identifiant externe : n°Ens référence un EnseignantDéfinition : tout cours actuellement offert par l'école
Exemple de schéma relationnel
Relation : ObtenuAttributs: n°E : entier sans nul
nomC : Dnom sans nulnote : Dnote sans nulannée : Dannée sans nul
Identifiant : (n°E + nomC )Identifiants externes : n°E référence un Etudiant.n°E
nomC référence un Cours Définition : l'étudiant n°E a réussi le cours nomC telle année et a
obtenu telle note Relation : Inscrit
Attributs: n°E : entier sans nul nomC : Dnom sans nul
Identifiant : (n°E + nomC )Identifiants externes : n°E référence un Etudiant.n°E
nomC référence un Cours Définition : actuellement, l'étudiant n°E est inscrit au cours nomC
Exemple de schéma relationnel
Relation : PrérequisAttributs: nomC : Dnom sans nul
nomCprérequis : Dnom sans nul
Identifiant : (nomC + nomCprérequis )
Identifiants externes : nomC référence un Cours
nomCprérequis référence un Cours
Définition: le cours nomCprérequis est un prérequis pour le cours nomC
Contrainte d'intégrité : dans tout tuple, nomCprérequis doit être différent de nomC
Exemple de schéma relationnel
Contraintes d'intégrité :
Pour tout tuple de Prérequis <nomC, nomCprérequis>, le cycle de nomCprérequis dans Cours doit être inférieur ou égal à celui de nomC:
<x,y> in Prérequis => x.cycle > y.cycle
Pour tout tuple de EtudiantEtudes <n°E,année,diplôme>, soit dateN la date de naissance des étudiants dans la relation Etudiant, alors: dateN < année:
<x,y,z> in EtudiantEtudes, <a,x,d> in Etudiant
=> d < y
Exemple de schéma relationnel
Contraintes d'intégrité (suite):
Pour tout tuple de Etudiant : cette_année - dateN = 18, où cette_année est une variable du SGBD
Pour tout tuple de Obtenu <n°E, nomC, note, année>, soit dateN la date de naissance de l'étudiant dans la relation Etudiant, alors : dateN < année
Pour tout tuple de Inscrit <n°E, nomC>, le n°E doit exister dans Obtenu associé à tous les cours existant dans Prérequis associés à nomC.
On est expert
en modélisation
entité-association...
comment peut-on devenir
expert en modélisation
relationnelle ?
Transformation EA -> Relationnel
Nous avions vu le modèle entité-association
Nous venons de voir le modèle relationnel
Comment peut-on passer du modèle conceptuel au modèle logique ?
Transformation d'un type d'entités
Id A B C D
Type d'Entités
Attributs simples
Tous les attributs monovalués peuvent être regroupés dans une même relation
TE1 (Id, A, B) A NOT NULL
Pour chaque attribut multivalué on créé une relation supplémentaire
TE2 (Id, C) C NOT NULL
TE3 (Id, D)
[Id] TE2 = [Id] TE1 [Id] TE3 [Id] TE1
Id A B C D
Type d'Entités
Attributs complexes
2 solutions :
TE1 (Id, A1, A2) A1 NOT NULL
(on perd le nom A)
TE1’ (Id, A) A NOT NULL
avec
dom (A) = dom (A1) x (dom (A2) )
Id A
TE
A1 A2
Attributs complexes
employé(E#, année-n, mois-n, jour-n, année-e, mois-e, jour-e) 156 1954 12 25 1985 06 10
employé'(E#, date_naissance, date_embauche) 156 19541225 19850610
employé
E# date_naissance
année mois jour
date_embauche
année mois jour
Attributs complexes multivalués1) si A1 A2 : TE1 (Id, A1) & TE2 (A1, A2)
TE (Id, A1, A2)
E# projets
Employé
nomdirecteur
DF
Employé(E#, nomprojet)Projet(nom, directeur)
Id A
TE
A1 A2
Rappel pour les mono valués:
Attributs complexes multivalués
2) si (Id, A1)A2 : TE (Id, A1, A2)
Id A
TE
A1 A2
E# projets
Employé
nom role
DF
Employé(E#, nomprojet, role)
DF
Amas (multisets, multi-ensembles)
Problème: les attributs multivalués peuvent
inclure plusieurs fois la même valeur
Hingis a gagné deux fois dans la même journée
il faut “inventer” un identifiant local : n°_match
Jid match
JoueurTennis
date résultat
JoueurTennis(Jid, date, resultat) ???
Identifiants artificiels
J# match
JoueurTennis
n°_match daterésultat
DFs
JoueurTennis(J#, no-match, date, résultat)
Associations
X YR
Z
On a les relations :X (........) Y (........) Z (........)
On créé la relation : R(Idx, Idy, Idz, Ratt1, ... , Rattn)
et on a : [Idx] R [Idx] X [IdY] R [Idx] Y [IdZ] R [Idx] Z
Associations (cas particuliers)
au moins un role monovalué
Pas besoin de relation R :
X (Idx, ..., Idy, Idz, Ratt1, ..., Rattn)Y (........)Z (........)
Ici on peut étendre X ou Z
X YR
Z
X YR
Z
Contraintes d'intégrité
Contrainte :Seules les personnesde plus de 18 ans peuvent conduire des voitures
x Personne AND (x, v) Conduit x.age > 18
Personne( Pid, ..., age) Voiture( V# , ...) Conduit( Pid, V# , ...)
Personne VoitureConduit
On peut vérifier l'intégritégrâce à une requêteSELECT *FROM Personne, ConduitWHERE Personne.Pid=Conduit.PidAND Personne.age <18
résultat = 0 la contrainte est vérifiéx PersonneAND (x, v) ConduitAND x.age<18= FALSE
Pour vérifier les contraintes d'intégrité
Solution 1:
démarrer une transaction
exécuter la mise à jour
exécuter la requête de validation de la C
si résultat 0 abort de la transaction sinon commit
Pour vérifier les contraintes d'intégrité
Solution 2:
définir une vue qui respecte la CI
valider la mise à jour par rapport à la définition de la vue
s’il y a incohérence abandonner la mise à jour sinon exécuter la mise à jour
Solution 3:
utiliser les triggers
ON UPDATE DO ...
Mais si notre modèle
entité-association
n'était pas correct ?
Validation d'un schéma conceptuel
Syntaxique: respect des règles du modèle
Par confrontation aux dépendances: règles de normalisation
Par jeu d'essai
Complétude par rapport aux traitements
Par les utilisateurs
Règles à connaître et à appliquer !!!
Concept de dépendance
A B si le fait que deux occurrences aient la même valeurpour A entraîne nécessairement qu'elles aient la même valeur pour B.
A B : «B dépend de A», «A détermine B »
N°carte nom, prénoms, date naissance, adresses
Etudiant
N°_carte nom prénoms date_naissance adresses
jour mois année n° rue ville code_postal
liste
Validation d'un TE (TA) /dépendances
Etudiant
N°_carte nom prénoms date_naissance adresses
liste
jour mois année n° rue ville code_postal
Règle 1: dans un TE (TA) valide, tous les attributs directs (simples et complexes) dépendent uniquement de chaque identifiant entier du TE (TA).
n°carte, nom, prénoms, date naissance et adresses sont
les attributs directs d’Etudiant, qui a pour identifiant n°carte
Schéma incorrect
La règle est contredite si un attribut dépend d'une partie de l'identifiant ou d'un autre attribut non identifiant.
No-carte nom-section directeur_section nom_étudiant
Etudiant
No-carte nom_section directeur_section nom_étudiant
Etudiantmauvais
mauvais
Normalisation
Processus de modification d'un schéma qui conduit à obtenir un schéma offrant les propriétés désirées.
Correct !
No-carte section nom étudiant
nom nom directeur
Etudiant
Etudiant
No-carte nom-section directeur section nom étudiant
Mauvais
Dépendances et identifiant
Graphe des dépendances
N°_carte nom_section directeur_section nom_étudiant
L'identifiant est la racine du graphe
N°_carte
nom_section directeur_section nom_étudiant
Validation /attributs complexes
Règle 2: Un attribut du ième niveau peut seulement dépendre d'une combinaison d'attributs du même niveau et de niveaux supérieurs contigus.
nomLab directeur chercheurs
nomCadresse dateentrée %temps projets
nomP budget description
ligne montant
Laboratoire
Dépendances entre TE
Si tout projet n'est fait que par un seul labo,le schéma est incorrect
Labo ChercheurEmploie
Projetmauvais
Règle 3:
un TA n-aire (n>2) avec une dépendanceentre ses TE doit être decomposé
Normalisation incorrecte d'un TA
ChercheurEmploie
Projet
Conduit
Labo
mauvais
Mauvaise décomposition du TA ternaire incorrect en deux TA binaires
Cette décomposition n'est pas correcte car elle induit une perte d'information – on ne sait plus sur quel projet travaille un chercheur !!
Normalisation correcte d'un TA
Décomposition du TA ternaire
incorrect en deux TA binaires sans
perte d'information:
un chercheur est employé par le labo
qui conduit le projet sur lequel le chercheur travaille
ChercheurEmploieProjet
Conduit
Labo
Validation des attributs d'un TA
Règle 4: dans un TA sans dépendance entre les TEs liés, les attributs du TA dépendent de tous les TE liés par ce TA.
(No-carte,No-Mat) moyenne, notes
Etudiant MatièreEvaluation
N°_carte nom moyenne notes N°_Mat coefficient
Validation des attributs d'un TA
Si Coef = fonction du nombre d'heures assurées par l'enseignant dans ce cours.
Alors Coef ne dépend pas d’Etudiant
Etudiant Enseignant
N°_carte notes Nom
Contrôle
Nom Cours
Cours Assure
coef
correct
Etudiant EnseignantContrôle
Nom Cours
Cours
N°_carte notes coef Nom
mauvais
Elimination des TA redondants
Si
"Est élève de" = Inscrit –Cours – Assure
alors il y a redondance inutile.
On supprime "Est élève de".
Etudiant Cours EnseignantInscrit Assure
Est élève de
Remplacement d'un attribut par un TA
Employé Service
N°_emp …. n°_service n° étage nom
mauvais
Règle de remplacement
N°_emp …. n° étage nom
Employé ServiceTravaille
Elimination des TE inutiles
Un TE est inutile s'il ne présente d'intérêt pour aucun traitement de l'application
Si il n'existe pas de requête portant directement sur les services, Services est transformé en attribut.
Employé ServiceTravaille
N°_emp …. service
Employé
n° étage nom
N°_emp …. n° étage nom
TE répertoire ou attribut
Nom Type Num
A moins que l'on souhaite gérer un répertoire des salles.
Cours SalleA lieu dans
Cours
Nom Type Num_salle
Transformation de schémas
La même réalité peut être modélisée de plusieurs façons différentes
Les choix sont dictés par les objectifs des applications
Si les objectifs divergent, le choix le moins contraignant est retenu
Relativisme
cable
bleu rouge
cable
cuivre fibre
cable
couleur materiau
cable
couleur
materiau
Choix de modélisation
TE ou attribut ?
TE ou TA ?
TA ou attribut ?
Types génériques ou types spécialisés ?
Attribut optionnel ou sous-type ?
TE ou attribut
Employé
n°-secu nom service
nom étage
Service
nom étage
Employé
no-AVS nom ?
Transformation d'attribut en TE
Employé Service
n°_secu nom nom étage
Travaillex:y 0:n
Attribut directEmployé
n°_secu nom service
nom étage
x:yLe lien de composition TE-attribut devient un rôle TE-TA, avec les mêmes cardinalités
Transformation d'attribut en TE
Attribut indirect
nomLab directeur chercheurs
nomC adresse date_entrée %temps projets
nomP budgetdescription
ligne montant
Laboratoire
Attribut -> TE : 1ère étape
nomLab directeur chercheurs
nomC adresse date_entrée %temps projets
Laboratoire
nomP budgetdescription
ligne montant
Projet
?
?
projets ---> TE=>
chercheurs ---> TE
Attribut -> TE : 2ème étape
Placement des attributs ?
nomP budget description
ligne montant
nomLab directeur
nomC adresse date_entrée %temps
Laboratoire
Projet
Chercheur
Emploie
Travaille
?
?
Attribut -> TE : 3ème étape
nomLab directeur
nomC adresse date_entrée %temps
Laboratoire
Chercheur
Emploie
Chercheur -> nomC, adresse => nomC et adresse sont attributs de Chercheur
(Chercheur,Laboratoire) -> date_entrée,%temps=> date_entrée et %temps sont attributs de Emploie
?
Attribut -> TE : 3ème étape
nomLab directeur nomC adressedate_entrée %temps
Laboratoire ChercheurEmploie
nomP budget
ligne montant
Projet
nomC -> adresse : nomC identifiant de Chercheur
Projet -> nomP, budget, description => attributs de Projet
nomP -> budget, description
description
Attributs -> TE : résultat
nomLab directeur nomC adressedate_entrée %temps
Laboratoire ChercheurEmploie
nomP budget
ligne montant
Projet
description
Travaille
TE ou TA: reification (TA->TE)
nom adresse échéanceNo-contrat
Personne ContratSouscrit
Objet
numéro type
Voiture
1:1
1:1
nom adresse numéro typeéchéanceNo-contrat
Personne VoitureAssure
TA ou attribut
nom adresse numéro typeéchéanceN°_contrat
Personne VoitureAssure
nomadresse assure
échéance N°_contrat voiture
Personne
numéro type
Similaire TE ou attribut
Attribut de TA ou attribut de TE ?
nom adresse numéro typeéchéanceNo-contrat
Personne VoitureAssure
nomadresse assure
échéance No-contrat voiture
Personne
numéro type
assure:attribut->TA
nom adresse numéro typeéchéanceNo-contrat
Personne VoitureAssure
TE génériques/spécifiques
ou
nom adresse sexe
Personne
nom adresse sexe
Personne
sexe = F
Femme
sexe = M
Homme
Attributs optionnels ou sous-types
ou
nom adresse n°tél
Personne
nom adresse
Personne
n°tél
Communiquant
Conclusion
Les transformations de schéma à semantique équivalente (sans perte d'information) sont un outil puissant de flexibilité
Elles permettent d'offrir des vues différentes (personnalisées) sur un même contenu informatif
Elles permettent de passer d'une structure obéissantà certaines règles à une autre structure équivalenteobéissant à d'autres règles(exemple: traduction d'un schéma EA en schéma relationnel)
RECAPITULONS