Le Modle Relationnel

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 et altérer des
  relations
• Créer, modifier, détruire, altérer, et poser des
  requêtes sur les relations en utilisant SQL
• Traduire un diagramme ER en une base de données
  relationnelles
• Introduire les vues

3
Pourquoi Étudier le Modèle Relationnel?

• Le modèle le plus largement utilisé.
• Vendeurs IBM, Informix, Microsoft, Oracle,
  Sybase, etc.
• Systèmes patrimoniaux  (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 Données Relationnelles

• Relation, faite 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).
• Une relation est un ensemble de lignes (tuples)
  distinctes chaque tuple a la même arité que le
  schéma de la relation.
• Base de données relationnelles un ensemble des
  relations de la BD, chacune ayant un nom
  distinct.
• Schéma dune BD ensemble de schémas des
  relations dans la BD.
• Instance de la BD ensemble des instances
  relationnelles de la BD.

5
Exemple de Relation

• Schema  Students(sid string, name string,
  login string,
• age
  integer, gpa real).

Instance

• Cardinalité 3, arité 5, les lignes sont
  distinctes.
• Les systèmes commerciaux permettent des
  duplicata.
• 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 Relationnelles

• Un avantage majeur du modèle relationnel est
  quil supporte de simples et puissantes requêtes
  sur les données.
• Les requêtes peuvent être écrites 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
SQL Langage des Requêtes pour Données
Relationnelles

• Développé par IBM ( système 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 triggers, oo, )
• SQL-99 (extensions majeures datawarehousing, )

8
Création des Relations en SQL
CREATE TABLE Students (sid CHAR(20), name
CHAR(20), login CHAR(10), age INTEGER,
gpa REAL)

• Avec la commande CREATE TABLE, on crée une
  relation. Il est à observer que le type (domaine)
  de chaque attribut est spécifié. Chaque fois
  que des tuples sont ajoutés ou modifiés, le SGBD
  veille au respect du type.

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2))
9
Destruction et Altération des Relations
DROP TABLE Students

• La commande DROP TABLE détruit la relation
  Students. Le schéma et les tuples sont effacés.

ALTER TABLE Students ADD COLUMN firstYear
integer

• Avec la commande ALTER TABLE, le schéma de
  Students est altéré par lajout dun nouvel
  attribut chaque tuple dans linstance courrante
  est augmenté par une valeur null pour le nouvel
  attribut.

10
Ajout et Effacement des Tuples

• Un seul tuple 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 tuples satisfaisant une certaine
  condition peuvent être effacés comme suit

DELETE FROM Students S WHERE S.name Smith
11
Contraintes dIntégrité (CIs)

• CI condition qui doit être satisfaite dans
  toutes les instances de la base de données.
• Exemple simple contraintes du domaine.
• Les CIs sont spécifiées lorsque le schéma est
  défini.
• Les CIs sont vérifiées lorsque les relations sont
  modifiées.
• Une instance légale dune relation est une
  instance qui satisfait toutes les CIs spécifiées.
  
• Un SGBD ne doit pas permettre des instances
  illégales.
• Si le SGBD vérifie les CIs, 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é dune
  relation si
• 1. Deux tuples 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

• Parmi plusieurs candidates clé (spécifiable par
  UNIQUE), une delles est choisie comme clé
  primaire.

CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid) )

• Un étudiant na quune note pour chaque cours
  dans lequel il est enrôlé. 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.
• Leçon une CI imprudente peut empêcher le
  stockage dinstances désirables de la base de
  données.

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 tuples
  dune autre relation.
• Doit correspondre à la clé primaire de la seconde
  relation.
• Est 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 (IR), i.e., il ny a aucune
  référence pendante (  dangling references).

15
Spécification des Clés Étrangères 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 de lIntégrité Référentielle

• Considérez Students and Enrolled sid dans
  Enrolled est une clé étrangère referant à
  Students.
• Que devrait-on faire si un tuple de Enrolled
  ayant un étudiant non-existent est inseré?
  (Rejetez le!)
• Que faire si un tuple de Students est effacé?
• Effacer également tous les tuples de Enrolled
  qui réfèrent à lui.
• Ne pas permettre un effacement dun tuple auquel
  il est fait référence.
• Donner une valeur par défaut au sid des tuples de
  Enrolled qui réfèrent à lui.

17
Exécution de lIR en SQL

• SQL-92 et SQL-1999 supportent toutes les 3
  options deffacement et de modification
  examinées.
• Défaut NO ACTION (delete/update est rejeté)
• CASCADE (effacer aussi tous les tuples qui
  réfèrent au tuple effacé)
• SET NULL / SET DEFAULT (donner une valeur
   null  défaut à la clé étrangère du tuple
  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 )
18
Doù viennent les CIs?

• Les CIs proviennent de la sémantique de
  lentreprise à modéliser.
• Une CI est déclaration au sujet de toutes les
  instances possibles!
• De notre exemple, nous savons que name ne peut
  pas être une clé, mais sid en est une.
• Clé et clé étrangère sont les CIs les plus
  courantes cependant des CIs plus généralles
  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 les CIs sont contrôlées dans une
  transaction?
• Immédiatement après la déclaration
• Plutard (p.ex., en fin de transaction)
• SQL permet deux modes de contrainte.
• SET CONSTRAINT ConstraintName IMMEDIATE
• SET CONSTRAINT ConstraintName DEFERRED
• Les CIs sont immédiates par défaut les CIs
  différées sont contrôlées lors de la validation.

20
Design Logique du Modèle ER au Relationnel

• Le modèle ER représente le design initial 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 génération est approximative car il est
  difficile de traduire toutes les contraintes du
  modèle ER en un modèle logique efficient.

21
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))
22
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)
23
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))
24
Rappel Contraintes de Clé

• Chaque dept a au plus un manager en vertu de la
  contrainte 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
25
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 séparées pour Employees et Departments.
• Puisque chaque département na quun 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)
26
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
27
Contraintes de Participation en SQL

• Nous ne pouvons exprimer que les contraintes de
  participation impliquant un ensemble dentités
  participant à une relation binaire.
• Pour les relations non binaires, recourir aux
  contraintes CHECK (plutard).

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)
28
Rappel Entités Faibles

• Une entité faible ne peut être identifiée 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
29
Traduction dEnsemble dEntités Faibles

• Un ensemble dentités faibles 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)
30
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?

31
Traduction des Hiérarchies ISA en Relations

• Approche générale
• 3 relations Employees, Hourly_Emps et
  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
  tuple 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 tuples
  de Hourly_Emps p.ex. 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.

32
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
33
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)
34
Vues

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

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.

35
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 YoungActiveStudents, 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!

36
Résumé

• Le modèle relationnel est une présentation
  tabulaire des données.
• Il est 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 CIs importantes 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 (pas toujours exactes!!!!)
  pour traduire les diagrammes ER en un modèle
  relationnel.