Le Modle Relationnel Chapitre 3 1 Objectifs v

Le Modèle Relationnel Chapitre 3 1

Objectifs v v v Représenter les données en utilisant le modèle relationnel Exprimer les contraintes d’inté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 2

Pourquoi Étudier le Modèle Relationnel? v Le modèle le plus largement utilisé. § v Systèmes patrimoniaux (“legacy systems”) en place dans les vieux modèles. § v Vendeurs: IBM, Informix, Microsoft, Oracle, Sybase, etc. P. ex. , IBM IMS Récent compétiteur: modèle orienté objet. § § Object. Store, Versant, Ontos Une synthèse émerge: modèle relationnel-objet • Informix Universal Server, Uni. SQL, O 2, Oracle, DB 2 3

Concepts des Bases de Données Relationnelles v 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. § v Base de données relationnelles: un ensemble des relations de la BD, chacune ayant un nom distinct. § Schéma d’une BD: ensemble de schémas des relations dans la BD. § Instance de la BD: ensemble des instances relationnelles de la BD. 4

Exemple de Relation v Schema : Students(sid: string, name: string, login: string, age: integer, gpa: real). Instance : Cardinalité = 3, arité = 5, les lignes sont distinctes. v Les systèmes commerciaux permettent des duplicata. v Toutes les colonnes d’une instance relationnelle ont-elles à être distinctes? Dépend de la présence ou non d’un ordre. v 5

Langages de Requêtes Relationnelles Un avantage majeur du modèle relationnel est qu’il supporte de simples et puissantes requêtes sur les données. v Les requêtes peuvent être écrites de manière intuitive (i. e. déclarative), et le SGBD est responsable de leur évaluation efficiente. v § § § L’utilisateur dit au SGBD quoi faire et le système cherche comment faire ce qu’il y a à faire de manière efficiente! La clé du succès: sémantique précise des requêtes. Permet à l’optimisateur de réordonner les opérations tout en garantissant que la réponse ne change pas. 6

SQL: Langage des Requêtes pour Données Relationnelles Développé par IBM ( « système R » ) dans les années 1970 s. v Besoin d’un standard car utilisé par beaucoup de vendeurs. v Standards: v § § SQL-86 SQL-89 (révision mineure) SQL-92 (révision majeure: triggers, oo, …) SQL-99 (extensions majeures: datawarehousing, …) 7

Création des Relations en SQL v 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 CREATE TABLE Enrolled respect du type. (sid: CHAR(20), cid: CHAR(20), grade: CHAR(2)) 8

Destruction et Altération des Relations DROP TABLE v Students La commande DROP TABLE détruit la relation Students. Le schéma et les tuples sont effacés. ALTER TABLE Students ADD COLUMN first. Year: v integer Avec la commande ALTER TABLE, le schéma de Students est altéré par l’ajout d’un nouvel attribut; chaque tuple dans l’instance courrante est augmenté par une valeur null pour le nouvel attribut. 9

Ajout et Effacement des Tuples v Un seul tuple est ajouté de la manière suivante: INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3. 2) v Tous les tuples satisfaisant une certaine condition peuvent être effacés comme suit: DELETE FROM Students S WHERE S. name = ‘Smith’ 10

Contraintes d’Intégrité (CIs) CI: condition qui doit être satisfaite dans toutes les instances de la base de données. v Exemple simple: contraintes du domaine. v § § v Une instance légale d’une relation est une instance qui satisfait toutes les CIs spécifiées. § v 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. 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 d’entrée de données aussi! 11

Contraintes de Clé Primaire v Un ensemble d’attributs est une clé d’une relation si: 1. Deux tuples distincts ne peuvent pas avoir les mêmes valeurs pour tous les attributs de la clé, et 2. Cela n’est pas vrai pour un quelconque sous-ensemble de la clé. • • v Si la partie (2) est fausse, on a une superclé. S’il y a plus d’une clé pour la relation, une d’elles est choisie (par le DBA) comme clé primaire. P. ex. , sid est une clé pour Students, alors que name n’en est pas une. L’ensemble {sid, gpa} est une superclé. 12

Clé Primaire et Candidates Clé en SQL v v v Parmi plusieurs candidates clé CREATE TABLE Enrolled (spécifiable par UNIQUE), une (sid CHAR(20) d’elles est choisie comme clé cid CHAR(20), primaire. grade CHAR(2), “Un étudiant n’a qu’une note pour PRIMARY KEY (sid, cid) ) chaque cours dans lequel il est enrôlé. ” vs. “Les étudiants ne peuvent CREATE TABLE Enrolled prendre qu’un seul cours et n’avoir (sid CHAR(20) qu’une seule note pour ce cours; et cid CHAR(20), deux étudiants ne peuvent recevoir la grade CHAR(2), même note. ” PRIMARY KEY (sid), Leçon: une CI imprudente peut UNIQUE (cid, grade) ) empêcher le stockage d’instances désirables de la base de données. 13

Clés Étrangères et Intégrité Référentielle v Clé étrangère : Ensemble d’attributs d’une relation qui est utilisé pour référer aux tuples d’une autre relation. § Doit correspondre à la clé primaire de la seconde relation. § Est un ‘pointeur logique’. v 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 n’y a aucune référence pendante ( « dangling references» ). 14

Spécification des Clés Étrangères en SQL v Seuls les étudiants listés dans la relation Students devraient être permis de s’enregistrer pour les cours !!! Enrolled CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students ) Students 15

Exécution de l’Intégrité Référentielle Considérez Students and Enrolled; sid dans Enrolled est une clé étrangère referant à Students. v Que devrait-on faire si un tuple de Enrolled ayant un étudiant non-existent est inseré? (Rejetez le!) v Que faire si un tuple de Students est effacé? v § § § Effacer également tous les tuples de Enrolled qui réfèrent à lui. Ne pas permettre un effacement d’un 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. 16

Exécution de l’IR en SQL v SQL-92 et SQL-1999 supportent CREATE TABLE Enrolled toutes les 3 options d’effacement et (sid CHAR(20), de modification examinées. cid CHAR(20), § Défaut: NO ACTION grade CHAR(2), (delete/update est rejeté) PRIMARY KEY (sid, cid), § CASCADE (effacer aussi tous FOREIGN KEY (sid) les tuples qui réfèrent au tuple effacé) REFERENCES Students § SET NULL / SET DEFAULT ON DELETE CASCADE ) (donner une valeur « null » défaut à la clé étrangère du tuple référant) 17

D’où viennent les CIs? v Les CIs proviennent de la sémantique de l’entreprise à modéliser. § § v 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. 18

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. v Quand est-ce que les CIs sont contrôlées dans une transaction? v § § v Immédiatement après la déclaration Plutard (p. ex. , en fin de transaction) SQL permet deux modes de contrainte. § § § SET CONSTRAINT Constraint. Name IMMEDIATE SET CONSTRAINT Constraint. Name DEFERRED Les CIs sont immédiates par défaut; les CIs différées sont contrôlées lors de la validation. 19

Design Logique: du Modèle ER au Relationnel Le modèle ER représente le design initial de la base de données. v La tâche est de générer un schéma relationnel qui soit le plus proche possible du modèle ER. v 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. v 20

De l’Ensemble d’Entités à une Table L’ensemble d’entités devient une table. v Chaque attribut de l’ensemble d’entités devient un attribut de la table. v Les contraintes de domaine deviennent des types appropriés de SQL. v La clé primaire de l’ensemble d’entités devient la clé primaire de la table. CREATE TABLE Employees (ssn CHAR(11), name ssn lot name CHAR(20), lot INTEGER, Employees PRIMARY KEY (ssn)) v 21

De l’Ensemble des Relations à une Table v v Un ensemble de relations (sans contraintes) est traduit en une table. Les attributs de la relation doivent inclure: § Clés pour chaque ensemble d’entités participant (clés étrangères). • Cet ensemble d’attributs 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) 22

De l’Ensemble des Relations à une Table (Suite) v La traduction d’un ensemble de relations CREATE TABLE Reports_to( circulaires (sans contraintes) en une table supervisor_ssn CHAR(11), doit inclure les attributs suivants: § Clés construites en concaténant les subordinate_ssn CHAR(11), indicateurs de rôle avec la clé PRIMARY KEY (supervisor_ssn, primaire de l’ensemble d’entités participant (clés étrangères). subordinate_ssn), • Cet ensemble d’attributs forme FOREIGN KEY (supervisor_ssn) une superclé pour la nouvelle REFERENCES Employees(ssn), table. § Tous les attributs descriptifs. FOREIGN KEY (subordinate_ssn) § Une dénomination explicite de la clé REFERENCES Employees(ssn)) référencée. 23

Rappel: Contraintes de Clé since v Chaque dept a au plus un manager en vertu de la contrainte de clé sur Manages. name ssn dname lot Employees did Manages budget Departments Comment traduire Tout ceci en modèle relationnel? 1 -to-1 1 -to Many-to-1 Many-to-Many 24

Traduction des Diagrammes ER avec Contraintes de Clé v v 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 n’a qu’un manager unique, nous pourrions aussi combiner Manages et Departments. CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES 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) 25

Rappel: Contraintes de Participation v Chaque département a-t-il un manager? § Si c’est le cas, on a une contrainte de participation: la participation de Departments dans l’association 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 n’est pas nulle!) since name ssn did lot Employees dname Manages budget Departments Works_In since 26

Contraintes de Participation en SQL Nous ne pouvons exprimer que les contraintes de participation impliquant un ensemble d’entités participant à une relation binaire. v Pour les relations non binaires, recourir aux contraintes CHECK (plutard). v 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) 27

Rappel: Entités Faibles v Une entité faible ne peut être identifiée que par l’entremise d’une clé primaire d’une autre entité (propriétaire). § § L’ensemble des propriétaires et celui des entités faibles doivent participer dans un ensemble de relations « one-to-many » (1 propriétaire, beaucoup d’entités faibles). Un ensemble d’entités faibles doit avoir une participation totale dans cette ensemble de relations identifiantes. name ssn lot Employees cost Policy pname age Dependents 28

Traduction d’Ensemble d’Entités Faibles v Un ensemble d’entités faibles ainsi que son ensemble de relations identifiantes sont traduits en une SEULE table. § Lorsque l’entité 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) 29

name ssn Rappel: Hiérarchies ISA hourly_wages lot Employees hours_worked ISA Une déclaration A ISA B signifie que chaque entité de A est aussi à considérer comme une entité de B. v v v Hourly_Emps contractid Contract_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? 30

Traduction des Hiérarchies ISA en Relations v Approche générale: § v Alternative: utiliser exactement Hourly_Emps et Contract_Emps. § § v 3 relations: Employees, Hourly_Emps et Contract_Emps. • Hourly_Emps: Chaque employé est enregistré dans Employees. Pour les employés journaliers, de l’info 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. Hourly_Emps: ssn, name, lot, hourly_wages, hours_worked. Chaque employé doit être exactement dans l’une de ces 2 sous-classes. Les contraintes de superposition et de couverture sont exprimées en SQL par des assertions que nous verrons plutard. 31

Rappel: Relation Binaire vs. Ternaire ssn name Employees v Notez les contraintes additionnelles introduites dans le 2ème diagramnme. pname lot Policies policyid ssn name Dependents Covers Mauvais design age cost pname lot age Dependents Employees Purchaser Meilleur design policyid Beneficiary Policies cost 32

Relation Binaire vs. Ternaire (Suite) CREATE TABLE Policies ( v La contrainte de clé policyid INTEGER, nous permets de cost REAL, combiner Purchaser ssn CHAR(11) NOT NULL, avec Policies ainsi PRIMARY KEY (policyid). que Beneficiary FOREIGN KEY (ssn) REFERENCES avec Dependents. ON DELETE CASCADE) v Employees, Les contraintes de CREATE TABLE Dependents ( participation pname CHAR(20), conduisent à des age INTEGER, contraintes NOT policyid INTEGER, NULL. PRIMARY KEY (pname, policyid). FOREIGN KEY (policyid) REFERENCES Policies, ON DELETE CASCADE) 33

Vues v Une vue est simplement une relation dont la définition est stockée plutôt que un ensemble de tuples. CREATE VIEW Young. Active. Students (name, AS SELECT S. name, E. grade FROM Students S, Enrolled E WHERE S. sid = E. sid and S. age<21 v grade) Les vues peuvent être détruites en utilisant la commande DROP VIEW. § Comment traiter DROP TABLE s’il y a une vue sur la table? • La commande DROP TABLE a des options pour permettre à l’usager de spécifier cela: RESTRICT / CASCADE. 34

Vues et Sécurité v Les vues peuvent être utilisées pour présenter de l’info nécessaire à l’usager tout en lui interdisant l’accès aux relations sous-jacentes. § Étant donné la vue Young. Active. Students, avec les tables Students et Enrolled cachées, nous pouvons trouver les étudiants qui sont inscrits, mais pas les cid’s des cours auxquels ils sont inscrits! 35

Résumé v v v Le modèle relationnel est une présentation tabulaire des données. Il est simple et intuitif, présentement largement utilisé. Les contraintes d’intégrité peuvent être spécifiées par le DBA sur base de la sémantique de l’application. Le SGBD en contrôle les violations. § § v v 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. 36