Le Mod

1
Le Modèle Relationnel

• Chapitre 3

2
Objectifs

• Représenter les données en utilisant le modèle
  relationnel.
• Exprimer les contraintes dintégrité sur les
  données.
• Créer, modifier, détruire, altérer, et poser des
  requêtes sur les relations.
• Créer, modifier, détruire, altérer, et poser des
  requêtes sur les relations en utilisant SQL.
• Obtenir une base de données relationnelle à
  partir dun diagramme ER.
• Introduction aux vues.

3
Pourquoi Étudier le Modèle Relationnel?

• Le modèle le plus largement utilisé.
• Vendeurs IBM, Informix, Microsoft, Oracle,
  Sybase, etc.
• Legacy systems en place dans les vieux modèles.
  
• P.ex., IBM IMS
• Récent compétiteur modèle orienté-objet.
• ObjectStore, Versant, Ontos
• Une synthèse émerge modèle relationnel-objet
• Informix Universal Server, UniSQL, O2, Oracle, DB2

4
Concepts des Bases de Donnees Relationnelles

• Relation fait de 2 composantes
• Instance une table, avec lignes et colonnes.
  lignes cardinalité, colonnes degré /
  arité.
• Schéma spécifie le nom de la relation, plus le
  nom et le domaine (type) de chaque colonne
  (attribut).
• P. ex., Students(sid string, name string,
  login string,
• age integer,
  gpa real).
• On peut concevoir une relation comme étant un
  ensemble de lignes ou uplets (tuples) (i.e.,
  toutes les lignes sont distinctes) chaque uplet
  a la même arité que le schéma de la relation.
• Base de données relationnelles un ensemble de
  relations, chacune ayant un nom distinct.
• Schéma relationnel dune BD ensemble de schémas
  des relations dans la BD.
• Schéma relationnel dune instance de la BD
  ensemble des instances relationnelles de la BD.

5
Exemple dInstance de la Relation Students

• Cardinalité 3, arité 5, toutes les lignes
  sont distinctes.
• Les systèmes commerciaux permettent des
  duplicata.
• Lordre des attributs peut ou peut ne pas jouer
  un rôle!
• Toutes les colonnes dune instance relationnelle
  ont-elles à être
• distinctes? Dépend de la présence ou non dun
  ordre.

6
Langages de Requêtes Relationnels

• Un avantage majeur du modèle relationnel est
  quil supporte de simple et puissantes requêtes
  sur les données.
• Les requêtes peuvent être écrit de manière
  intuitive (i.e. déclarative), et le SGBD est
  responsable de leur évaluation efficiente.
• Lutilisateur dit au SGBD quoi faire et le
  système cherche comment faire ce quil y a à
  faire de manière efficiente!
• La clé du succès sémantique précise des
  requêtes.
• Permet à loptimisateur de réordonner les
  opérations tout en garantissant que la réponse ne
  change pas.

7
Language des Requêtes SQL

• Développé par IBM (system R) dans les années
  1970s.
• Besoin dun standard car utilisé par beaucoup de
  vendeurs.
• Standards
• SQL-86
• SQL-89 (révision mineure)
• SQL-92 (révision majeure)
• SQL-99 (extensions majeures, standard courrant)

8
Création des Relations en SQL

• Crée la relation Students. Il est à observer que
  le type (domaine) de chaque attribue est
  spécifié et fait respecter par le SGBD chaque
  fois que des uplets sont ajoutés ou modifiés.
• Autre exemple La table Enrolled enregistrent des
  infos sur les cours que les étudiants prennent.

CREATE TABLE Students (sid CHAR(20), name
CHAR(20), login CHAR(10), age INTEGER,
gpa REAL)
CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2))
9
Destruction et Altération des Relations
DROP TABLE Students

• détruit la relation Students. Le schéma et les
  uplets sont effacés.

ALTER TABLE Students ADD COLUMN firstYear
integer

• Le schéma de Students est altéré par lajout dun
  nouvel attribut chaque uplet dans linstance
  courrante est augmenté par une valeur null pour
  le nouvel attribut.

10
Ajout et Effacement des uplets

• Un seul uplet est ajouté de la manière suivante

INSERT INTO Students (sid, name, login, age,
gpa) VALUES (53688, Smith, smith_at_ee, 18, 3.2)

• Tous les uplets satisfaisant une certaine
  condition peuvent être effacés (e.g., name
  Smith)

DELETE FROM Students S WHERE S.name Smith

• De puissantes variantes de ces commandes seront
  vues plutard!

11
Contraintes dIntégrité (ICs)

• IC condition qui doit être satisfaite dans
  nimporte quelle instance de la base de données
  p. ex., contraines du domaine.
• Les ICs sont spécifiées lorsque le schéma est
  défini.
• Les ICs sont vérifiées lorsque les relations sont
  modifiées.
• Une instance légale dune relation est une
  instance qui satisfait toutes les ICs spécifiées.
  
• Un SGBD ne doit pas permettre des instances
  illégales.
• Si le SGBD vérifie les ICs, les données stockées
  reflètent mieux la signification du monde réel.
• Évite les erreurs dentrée de données aussi!

12
Contraintes de Clé Primaire

• Un ensemble dattributs est une clé pour une
  relation si
• 1. Deux uplets distincts ne peuvent pas avoir les
  mêmes valeurs pour tous les attributs de la clé,
  et
• 2. Cela nest pas vrai pour un quelconque
  sous-ensemble de la clé.
• Si la partie 2 est fausse, on a une superclé.
• Sil y a plus dune clé pour la relation, une
  delles est choisie (par le DBA) comme clé
  primaire.
• P. ex., sid est une clé pour Students, alors que
  name nen est pas une. Lensemble sid, gpa
  est une superclé.

13
Clé Primaire et Candidates Clé en SQL

• Plusieurs candidates clé (spécifiées par
  UNIQUE) une delles est choisie comme clé
  primaire.

• Pour un étudiant et un cours donné, il y a une
  seule note. vs. Les étudiants ne peuvent
  prendre quun seul cours et navoir quune seule
  note pour ce cours et deux étudiants ne peuvent
  recevoir la même note.
• Une IC utilisée imprudemment peut empêcher le
  stockage dinstances de base de données qui
  apparaissent dans la réalité!

CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid) )
CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid), UNIQUE (cid, grade) )
14
Clés Étrangères et Intégrité Référentielle

• Clé étrangère Ensemble dattributs dune
  relation qui est utilisé pour référer aux uplets
  dune autre relation. (Doit correspondre à la
  clé primaire de la seconde relation.) Ressemble
  à un pointeur logique.
• P. ex. sid est une clé étrangère referant à
  Students
• Enrolled(sid string, cid string, grade string)
• Si toutes les contraintes de clé étrangère sont
  respectées, on atteint une intégrité
  référentielle, i.e., il ny a aucune référence
  pendante.
• Les liens ( links ) en HTML sont un exemple de
  modèle de données sans intégrité référentielle.

15
Clés Étrangère en SQL

• Seuls les étudiants listés dans la relation
  Students devraient être permis de senregistrer
  pour les cours.

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid), FOREIGN KEY (sid) REFERENCES
Students )
Enrolled
Students
16
Exécution ( Enforcing ) de lIntégrité
Référentielle

• Considerez Students and Enrolled sid dans
  Enrolled est une clé étrangère referant à
  Students.
• Que devrait-on faire si un uplet de Enrolled
  ayant un étudiant non-existent est inseré?
  (Rejetez le!)
• Que devrait-on faire si un uplet de Students est
  effacé?
• Effacer également tous les uplets de Enrolled
  qui réfèrent à lui.
• Ne pas permettre un effacement dun uplet auquel
  il est fait référence.
• Donner une valeur  default  au sid des uplets
  de Enrolled qui réfèrent à lui.
• (En SQL, on utilise un  default  spécial pour
  les uplets de Enrolled appelé null, qui signifie
  inconnu ou nonapplicable.)
• Traitement similaire si la clé primaire dun
  uplet de Students est modifié.

17
Intégrité Référentielle en SQL

• SQL/92 et SQL1999 supportent toutes les 4
  options deffacement et de modification
  examinées.
• Defaut NO ACTION (delete/update est rejeté)
• CASCADE (effacer aussi tous les uplets qui
  réfèrent à luplet effacé)
• SET NULL / SET DEFAULT (donner une valeur défaut
  à la clé étrangère de luplet référant)

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid), FOREIGN KEY (sid) REFERENCES
Students ON DELETE CASCADE ON UPDATE SET
DEFAULT )
18
Doù viennent les ICs?

• Les ICs proviennent de la sémantique de
  lentreprise à modéliser.
• Nous pouvons contrôler une instance de base de
  données pour voir si une IC est violée, mais nous
  ne pourrons jamais déduire que une IC est
  satisfaite juste à partir dune instance.
• Une IC est déclaration au sujet de toutes les
  instances possibles!
• De notre exemple, nous savonsque name nest pas
  une clé, mais lassertion que sid est une clé
  nous est donnée.
• Clé et clé étrangère sont les ICs les plus
  courants cependant des ICs plus généraux
  existent aussi.

19
Transactions et Contraintes

• Un programme de transaction est une séquence de
  requêtes, insertions, effacements, etc qui
  accèdent à la base de données.
• Quand est-ce que des contraintes devraient etre
  contrôlées dans une transaction?
• Immédiatement après la déclaration
• Différer le contrôle à plutard
• SQL permet deux modes de contrainte.
• SET CONSTRAINT MyConstraint IMMEDIATE
• SET CONSTRAINT MyConstraint DEFERRED
• Les ICs sont immédiats par défaut les ICs
  différées sont controlées lors de la validation
  ( Commit time ).

20
Requêtes sur Données Relationnelles en SQL

• Trouver tous les étudiants ayant 18 ans

SELECT FROM Students S WHERE S.age18

• Pour trouver juste les noms et logins, remplacer
  la
• 1ère ligne par

SELECT S.name, S.login
21
Requêtes sur Données Relationnelles en SQLn
(Suite)

• Nous pouvons aussi combiner des infos de
  plusieurs relations

SELECT S.name, E.cid FROM Students S, Enrolled
E WHERE S.sidE.sid AND E.gradeA
Étant donnée linstance suivante de Enrolled
Nous obtenons
22
Design Logique du Modèle ER au Relationnel

• Le modèle représente le design initial et
   high-level  de la base de données.
• La tâche est de générer un schéma relationnel qui
  soit le plus proche possible du modèle ER.
• La traduction est approximative car il est
  difficile de traduire toutes les contraintes du
  modèle ER en un modèle logique efficient.

23
De lEnsemble dEntités à une Table

• Lensemble dentités devient une table.
• Chaque attribut de lensemble dentités devient
  un attribut de la table.
• Les contraintes de domaine deviennent des types
  appropriés de SQL.
• La clé primaire de lensemble dentités devient
  la clé primaire de la table.

CREATE TABLE Employees
(ssn CHAR(11), name
CHAR(20), lot INTEGER,
PRIMARY KEY (ssn))
24
De lEnsemble des Relations à une Table

• Un ensemble de relations (sans contraintes) est
  traduit en une table.
• Les attributs de la relation doivent inclure
• Clés pour chaque ensemble dentités participant
  (clés étrangères).
• Cet ensemble dattributs forme une superclé pour
  la nouvelle table.
• Tous les attributs descriptifs.

CREATE TABLE Works_In( ssn CHAR(1), did
INTEGER, since DATE, PRIMARY KEY (ssn,
did), FOREIGN KEY (ssn) REFERENCES
Employees, FOREIGN KEY (did)
REFERENCES Departments)
25
De lEnsemble des Relations à une Table (Suite)

• La traduction dun ensemble de relations
  circulaires (sans contraintes) en une table doit
  inclure les attributs suivants
• Clés construites en concaténant les indicateurs
  de rôle avec la clé primaire de lensemble
  dentités participant (clés étrangères).
• Cet ensemble dattributs forme une superclé pour
  la nouvelle table.
• Tous les attributs descriptifs.
• Une dénomination explicite de la clé référencée.

CREATE TABLE Reports_to( supervisor_ssn
CHAR(11), subordinate_ssn CHAR(11), PRIMARY
KEY (supervisor_ssn,
subordinate_ssn), FOREIGN KEY (supervisor_ssn)
REFERENCES Employees(ssn), FOREIGN KEY
(subordinate_ssn) REFERENCES
Employees(ssn))
26
Rappel Contraintes de Clé

• Chaque dept a au plus un manager en vertu de la
  constrainte de clé sur Manages.

budget
did
Departments
Comment traduire Tout ceci en modèle
relationnel?
Many-to-Many
1-to-1
1-to Many
Many-to-1
27
Traduction des Diagrammes ER avec Contraintes de
Clé
CREATE TABLE Manages( ssn CHAR(11), did
INTEGER, since DATE, PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)

• Traduire la relation en une table
• Notez que le did est la clé maintenant!
• Tables separées pour Employees et Departments.
• Puisque chaque département a un manager unique,
  nous pourrions aussi combiner Manages et
  Departments.

CREATE TABLE Dept_Mgr( did INTEGER, dname
CHAR(20), budget REAL, ssn CHAR(11),
since DATE, PRIMARY KEY (did), FOREIGN
KEY (ssn) REFERENCES Employees)
28
Rappel Contraintes de Participation

• Chaque département a-t-il un manager?
• Si cest le cas, on a une contrainte de
  participation la participation de Departments
  dans lassociation Manages est dite être totale
  (vs. partielle).
• Chaque valeur did dans la table Departments doit
  apparaître dans une ligne de la table Manages
  (avec une valeur de ssn qui nest pas nulle!)

since
since
name
name
dname
dname
lot
budget
did
budget
did
ssn
Departments
Employees
Manages
Works_In
since
29
Contraintes de Participation en SQL

• Nous pouvons capturer les contraintes de
  participation impliquant un ensemble dentités
  participant à une relation binaire, mais rien
  dautre (sans ressortir aux contraintes CHECK
  de SQL).

CREATE TABLE Dept_Mgr( did INTEGER, dname
CHAR(20), budget REAL, ssn CHAR(11) NOT
NULL, since DATE, PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees, ON
DELETE NO ACTION)
30
Rappel Entités Faibles

• Une entité faible ne peut être identifié que par
  lentremise dune clé primaire dune autre entité
  (propriétaire) .
• Lensemble des propriétaires et celui des entités
  faibles doivent participer dans un ensemble de
  relations  one-to-many  (1 propriétaire,
  beaucoup dentités faibles).
• Un ensemble dentités faibles doit avoir une
  participation totale dans cette ensemble de
  relations identifiantes.

name
cost
pname
age
ssn
lot
Dependents
Policy
Employees
31
Traduction dEnsemble dEntités Faibles

• Une ensemble dentités faible ainsi que son
  ensemble de relations identifiantes sont traduits
  en une SEULE table.
• Lorsque lentité propriétaire est effacée, toutes
  les entités faibles possédées par elle doivent
  aussi être effacées.

CREATE TABLE Dep_Policy ( pname CHAR(20),
age INTEGER, cost REAL, ssn CHAR(11) NOT
NULL, PRIMARY KEY (pname, ssn), FOREIGN
KEY (ssn) REFERENCES Employees, ON DELETE
CASCADE)
32
Rappel Hiérarchies ISA
name
ssn
lot
Employees
hours_worked
hourly_wages
ISA

• Une déclaration A ISA B signifie que chaque
  entité de A est aussi à considérer comme une
  entité de B.

contractid
Contract_Emps
Hourly_Emps

• Contraintes de superposition Joe peut-il être à
  la fois dans Hourly_Emps et dans Contract_Emps?
• Contraintes de couverture Y a-t-il des employés
  qui ne sont ni dans Hourly_Emps ni dans
  Contract_Emps?

33
Traduction des Hiérarchies ISA en Relations

• Approche générale
• 3 relations Employees, Hourly_Emps and
  Contract_Emps.
• Hourly_Emps Chaque employé est enregistré dans
  Employees. Pour les employés journaliers, de
  linfo supplémentaire est enregistré dans
  Hourly_Emps (hourly_wages, hours_worked, ssn) un
  uplet de Hourly_Emps doit être effacé si sa
  référence dans Employees est effacée.
• Les requêtes impliquants tous les employés sont
  faciles, mais celles impliquant juste les uplets
  de Hourly_Emps requièrent un join pour accéder à
  des attributs supplémentaires.
• Alternative utiliser exactement Hourly_Emps et
  Contract_Emps.
• Hourly_Emps ssn, name, lot, hourly_wages,
  hours_worked.
• Chaque employé doit être exactement dans lune de
  ces 2 sous-classes.
• Les contraintes de superposition et de couverture
  sont exprimées en SQL par des assertions que nous
  verrons plutard.

34
Rappel Relation Binaire vs. Ternaire
pname
age
Dependents
Covers

• Notez les contraintes additionnelles introduites
  dans le 2ème diagramnme.

Mauvais design
pname
age
Dependents
Purchaser
Meilleur design
35
Relation Binaire vs. Ternaire (Suite)
CREATE TABLE Policies ( policyid INTEGER,
cost REAL, ssn CHAR(11) NOT NULL,
PRIMARY KEY (policyid). FOREIGN KEY (ssn)
REFERENCES Employees, ON DELETE CASCADE)

• La contrainte de clé nous permets de combiner
  Purchaser avec Policies ainsi que Beneficiary
  avec Dependents.
• Les contraintes de participation conduisent à des
  contraintes NOT NULL.

CREATE TABLE Dependents ( pname CHAR(20),
age INTEGER, policyid INTEGER, PRIMARY
KEY (pname, policyid). FOREIGN KEY (policyid)
REFERENCES Policies, ON DELETE CASCADE)
36
Vues

• Une vue est simplement une relation dont la
  définition est stockée plutôt que un ensemble de
  uplets.

CREATE VIEW YoungActiveStudents (name,
grade) AS SELECT S.name, E.grade FROM
Students S, Enrolled E WHERE S.sid E.sid and
S.agelt21

• Les vues peuvent être détruites en utilisant la
  commande DROP VIEW.
• Comment traiter DROP TABLE sil y a une vue sur
  la table?
• La commande DROP TABLE a des options pour
  permettre à lusager de spécifier cela RESTRICT
  / CASCADE.

37
Vues et Sécurité

• Les vues peuvent être utilisées pour présenter de
  linfo nécessaire à lusager tout en lui
  interdisant laccès aux relations sous-jacentes.
  
• Étant donné la vue YoungStudents, avec les
  tables Students et Enrolled cachées, nous pouvons
  trouver les étudiants qui sont inscrits, mais pas
  les cids des cours auxquels ils sont inscrits!

38
Résumé

• Le modèle relationnel est une présentation
  tabulaire ses données.
• Simple et intuitif présentement largement
  utilisé.
• Les contraintes dintégrité peuvent être
  spécifiées par le DBA sur base de la sémantique
  de lapplication. Le SGBD en contrôle les
  violations.
• Deux ICs importants clé primaire et clés
  étrangères
• De plus, on a toujours les contraintes du domaine
• Un langage de requêtes puissant et naturel
  existe.
• Il existe des règles pour traduire les diagramme
  ER en un modèle relationnel.