conception de bd relationnelles€¦ · département de génie logiciel et des ti modélisation des...

40
Département de génie logiciel et des TI LOG660 - Bases de données de haute performance Conception de BD relationnelles

Upload: others

Post on 17-Jun-2020

0 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

LOG660 - Bases de données de haute performance

Conception de BD relationnelles

Page 2: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Modélisation des données

■ Schéma conceptuel– Modélise les classes, leurs attributs et leurs relations (ex:

association, agrégation, spécialisation, etc.)– Exemple: diagramme de classe UML

■ Schéma relationnel (conceptuel)– Traduit le schéma conceptuel sous la forme d’un modèle

relationnel (ex: tables, colonnes, clés, contraintes, etc.)– Indépendant de la plateforme/BD utilisée (ex: Oracle versus

SQL Server)– Exemple: diagramme UML avec tables, diagramme entités-

associations

© R. Godin, C. Desrosiers - Hiver 2011 2

Page 3: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Modélisation des données

■ Schéma relationnel (MSP)– MSP: Modèle spécifique à la plateforme– Implémente le schéma relationnel conceptuel en considérant

une plateforme spécifique– Tient compte de la syntaxe spécifique à la plateforme/BD.– Exemple: SQL LDD Oracle (CREATE TABLE,

VARCHAR2, etc.)

© R. Godin, C. Desrosiers - Hiver 2011 3

Page 4: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Processus de conception

■ Objectif:

© R. Godin, C. Desrosiers - Hiver 2011 4

Schéma conceptuel(diagramme de classes UML)

Schéma relationnel(diagramme de tables UML)

Schéma relationnel (MSP)(SQL LDD Oracle)

Page 5: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Schéma relationnel en UML

Com m ande{Clé pr im aire : no Co m m ande}noComm ande : INTEGERdate Co mm ande : DATEnoClient : INTEGER

<<Table>>

Client

{Clé prim aire : noClient}noCl ien t : INTEGERnom Client : VARCHARnoTéléphone : VARCHAR

<<Table>>

LigneCom m ande{Cl é pri maire : no Co mm ande, noArticl e}noComm ande : INTEGERnoArticle : INTEGERquantité : INTEGER

<<Table>>

Article{Clé prim aire : noArticle}noArticle : INTEGERdescription : VARCHARprixUnitaire : DECIMALquantitéEnStock : INTEGER

<<Table>>

Livraison{Clé prim aire : noLivraison}noLivraison : INTEGERdateLivraison : DATE

<<Table>> DétailLivraison{Clé prim aire : noLivraison, noCom m ande, noArticle}noLivrai son : INTEGERnoCom m a nde : INTEGERnoArticle : INTEGERquantitéLivrée : INTEGER

<<Table>>

© R. Godin, C. Desrosiers - Hiver 2011 5

Page 6: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Nom clé étrangère ≠ nom clé primaire

■ Étiquette de la relation de dépendance

num éroClient

Client{Clé prim aire : noClient}noClient : INTEGERnom Client : VARCHARnoTéléphone : VARCHAR

<<Table>>Com m ande

{Clé prim aire : noCom m ande}noCom m ande : INTEGERdateC om m ande : DATEnum éroClie nt : INTEGER

<<Ta ble>>

© R. Godin, C. Desrosiers - Hiver 2011 6

Page 7: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Traduction du schéma conceptuel en schéma relationnel

■ Étapes principales:1. Traduire les classes en tables2. Traduire les attributs et leur type3. Définir la clé primaire4. Traduire les associations

© R. Godin, C. Desrosiers - Hiver 2011 7

Page 8: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Étape 1 : Traduire les classes en tables

Personne<<table>>

Membre<<table>> PrêtEnCours

<<table>>PrêtArchivé<<table>>

Employé<<table>>

Catégorie<<table>>

Auteur<<table>>

Editeur<<table>>

Livre<<ta ble>>

Exemp lai re<<table>>

Prêt<<table>>

Utilisateur<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 8

■ Cas normal: une table par classe

Page 9: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Étape 2 : Traduire les attributs et leur type

■ Cas possibles:1. Type simple (ex: Integer)2. Type énuméré (ex: enum)3. Type complexe (ex: struct en C/C++)4. Attributs multivalués (ex: tableau Integer[0..*])5. Attributs de classe (ex: static)

© R. Godin, C. Desrosiers - Hiver 2011 9

Page 10: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Attributs de type simple

■ Attribut de la classe → colonne de la table

Livre{Clé candidate: ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : Dom aineAnnée

<<table>>Livre

{UNIQUE: ISBN}ISBN : Stringti tre : StringannéeParution : T ypeDonnéesAnnée

© R. Godin, C. Desrosiers - Hiver 2011 10

Note: les attributs UNIQUE deviennent des clés candidates

Page 11: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Traduction des types de données

Type OCL Type SQL2 Oracle 11 Boolean BIT(1) BOOLEAN ou CHAR(1) + CHECK Integer INTEGER ou SMALLINT NUMBER(n), INTEGER String CHARACTER (CHAR) (n), CHARACTER

VARYING (VARCHAR) (n) VARCHAR2(n) (chaîne jusqu’à 4000 bytes), LONG ou LONG VARCHAR (chaîne jusqu'à 2G), CLOB (chaîne jusqu'à 4G), NCLOB (chaîne pour caractères encodés sur plusieurs octets)

Real NUMERIC(p,s) (précision exacte), DECIMAL(p,s), REAL, DOUBLE PRECISION, FLOAT(p)

NUMBER(p,s), FLOAT, DOUBLE

Enum{v1,…vn} CHARACTER (CHAR) ou VARCHAR + CHECK … IN (v1,…,vn) (possibilité de création de domaine)

Domaine non supporté

DATE DATE inclut TIME TIME TIMESTAMP BIT(n), BIT VARYING(n) RAW(n : max = 255), LONG RAW (binaire

jusqu'à 2G), BLOB (binaire jusqu'à 4G) BFILE (pointeur à un fichier externe)

© R. Godin, C. Desrosiers - Hiver 2011 11

Page 12: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Types énumérés (cas 1)

■ Petit domaine invariant– création d ’un domaine VARCHAR + CHECK

Exem plaire{Clé candidate : idExem plaire}idExem plaire : VARCHAR(10)dateAchat : Dates tatut : Dom aineStatut

<<table>>

Dom aineStatut

{VARCHAR(15) CHECK value IN ('prêté','disponible','retiré')}

<<dom ain>>

Exem plaire{UNIQUE: idExem plaire}i dExem plai re : Str ingdateAchat : Dates tatut : enum (prêté, disponible, retiré)

© R. Godin, C. Desrosiers - Hiver 2011 12

Page 13: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Types énumérés (cas 2)

■ Gros domaine ou extensible– création d ’une table à part– utilisé comme liste de valeurs (LOV Designer)– introduction d ’une clé primaire artificielle ?

Exem plaire{Clé candidate : idExem plaire}idExem plaire : VARCHAR(10)dateAchat : Dates tatut : VARCHAR(15)

<<table>>

D om aineSta tut{Clé prim aire : s tatut}s tatut : VARCHAR(15)

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 13

Page 14: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Types complexes (cas 1)

■ Représentation explicite des attributs du type complexe

Membreadresse : typeDonnéesAdresse

typeDonnéesAdressenuméroCiviquenuméroA ppartementnomRuenomVillenom Provinc enomPayscodePostal

<<datatype>>Membre

numéroCiviquenuméroAppartementnomRuenomVillenomProvincenomPayscodePostal

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 14

Page 15: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Types complexes (cas 2)

■ Création d’une nouvelle table

Mem bre{Clé primaire : idMembre}idMembre

<<table>>

Adresse{Clé primaire : idMembre}idMembrenuméroCiviquenuméroAppartementnomRuenomVillenomProvincenomPayscodePostal

<<table>>

Membreadresse : typeDonnéesAdresse

typeDonnéesAdressenuméroCiviquenuméroA ppartementnomRuenomVillenom Provinc enomPayscodePostal

<<datatype>>

© R. Godin, C. Desrosiers - Hiver 2011 15

Schéma conceptuel

Schéma relationnel

Page 16: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Attributs multivalués

■ Table à part– clé étrangère + colonne pour l ’attribut

■ Petit tableau de taille fixe– n colonnes (valeurs nulles)

■ Encodage– invisible au SGBD

■ Oracle8– VARRAY, NESTED TABLE

© R. Godin, C. Desrosiers - Hiver 2011 16

Page 17: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Attributs de classe

Mem breGénéralnbMaxPrêts : INTEGER = 5duréeMaxPrêts : INTEGER = 7

<<table>>

Mem bretéléphoneRés idence : String$ nbMaxPrêts : Integer = 5$ duréeMaxPrêts : Integer = 7

Mem bre

téléphoneRés idence : VARCHAR(15)

<<ta ble>>

© R. Godin, C. Desrosiers - Hiver 2011 17

■ Création de tables supplémentaires

Page 18: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Étape 3 : Définir la clé primaire

■ Choix possibles:1. Clé artificielle générée2. Clé naturelle3. Simple ou composée

© R. Godin, C. Desrosiers - Hiver 2011 18

Page 19: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Clés primaires: valeur générée

■ De manière systématique ?■ Mécanisme de SEQUENCE Oracle

PrêtArchivé{Clé prim aire : noSequence}noSequence : INTEGERdateRetour : DATE

<<table> >PrêtEnCours

{Clé prim aire : noSequence}noSequence : INTEGER

<<table>>

Prêt{Clé prim aire : noSequence}noSequence : INTEGERdatePrêt : DATE

<<table>>PrêtdatePrêt : Date

PrêtArchivé

d ateRetour : DatePrêtEnCours

© R. Godin, C. Desrosiers - Hiver 2011 19

Page 20: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Clés primaires: identifiant naturel

■ Utilisation d'un identifiant naturel (UNIQUE)

■ Si identifiant naturel trop lourd:– introduire clé primaire artificielle

Livre{Clé candidate: ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : DomaineAnnée

<<table>>Livre

{Clé primaire: ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : DomaineAnnée

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 20

Page 21: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Clés primaires composées

■ Formées de plusieurs colonnes de la table■ Performance réduite pour l’indexage■ Utilisées lorsque les lignes de la table ne sont

pas référencées (ex: table de jointure)

© R. Godin, C. Desrosiers - Hiver 2011 21

Page 22: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Étape 4 : Traduire les relations

■ Cas possibles:1. Plusieurs à plusieurs 2. Un à plusieurs3. Un à un4. Agrégation, composition5. Spécialisation

© R. Godin, C. Desrosiers - Hiver 2011 22

Page 23: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Exemple de schémaPrêt

{Clé primaire : noSequence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExemplaire : VARCHAR(10)

<<table>>

Personne{Clé primaire : noSequence}noSequence : INTEGERnom : VARCHAR2(20)prénom : VARCHAR2(20)

<<table>>

Membre{Clé primaire : noSequence}noSequence : INTEGERtéléphoneRésidence : VARCHAR(15)

<<table>>PrêtEnCours

{Clé primaire : noSequence}noSequence : INTEGER

<<table>>PrêtArchivé{Clé primaire : noSequence}noSequence : INTEGERdateRetour : DATE

<<table>>Employé

{Clé primaire : noSequence}noSequence : INTEGER{Clé candidate : codeMatricule}codeMatricule : CHAR(6)catégorieEmployé : DomaineCatégorieEmployé

<<table>>

Catégorie{Clé primaire : code}code : VARCHAR(10)descripteur : VARCHAR(20)codeParent : VARCHAR(10)

<<table>>

Auteur{Clé primaire : noSequence}noSequence : INTEGER

<<table>>

Editeur{Clé primaire : nomEditeur}nomEditeur : VARCHAR(20)ville : VARCHAR(20)

<<table>>

Livre{Clé primaire : ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : DomaineAnnéenomEditeur : VARCHAR(20)code : VARCHAR(10)

<<table>> Exemplaire{Clé primaire : idExemplaire}idExemplaire : VARCHAR(10)dateAchat : Datestatut : DomaineStatutISBN : CHAR(13)

<<table>>

Utilisateur{Clé primaire : noSequence}noSequence{Clé candidate : idUtilisateur}idUtilisateur : VARCHAR(10)motPasse : VARCHAR(10)catégorieUtilisateur : DomaineCatégorieUtilisateur

<<table>>

MembreGénéral{Clé primaire : noSequence}noSequencenbMaxPrêts : INTEGER = 5duréeMaxPrêts : INTEGER = 7

<<table>>

AuteurLivre{Clé primaire : noSequence, ISBN}noSequence : INTEGER{Clé candidate : ISBN, ordreAuteur}ISBN : CHAR(13)ordreAuteur : INTEGER

<<table>>

{Un Auteur ne peut exister sans AuteurLivre}

{Un Livre ne peut exister sans AuteurLivre}

noSequenceUtilisateur

{Un Editeur ne peut exister sans Livre}

codeParent

{Un Livre ne peut exister sans Exemplaire}

© R. Godin, C. Desrosiers - Hiver 2011 23

Page 24: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Association plusieurs à plusieurs

Auteur{Clé prim aire : noSequence}noSeq uence : INTEGER

<<table>>Livre

{Clé prim aire : ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : Dom aineAnnée

<<table>>

AuteurLivre{Clé prim aire : noSequence, ISBN}noSequence : INTEGERISBN : CHAR(13)

<< table>>{Un Auteur ne peut exis ter sans AuteurLivre}

{Un Livre ne peut exis ter sans AuteurLivre}

Auteur

1..* 1..*1..* 1..*

L iv re

{U NIQUE: ISB N}ISBN : Stringti tre : St rin gann éeParut io n : T yp eDonn éesAnnée

© R. Godin, C. Desrosiers - Hiver 2011 24

■ Traduction par une table

Page 25: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Classe associative

Cours{UNIQUE : s igl e}s i gletitrenbCrédits

Etudiant{UNIQUE : codeP erm anent}cod ePerm anentnomprénom

** **

NoteObtenuenotesess ion

Cours{Clé primaire : sigle}sigletitrenbCrédits

<<table>>Etudiant

{Clé primaire : codePermanent}codePermanentnomprénom

<<table>>

NoteObtenue{Clé primaire : codePermament, sigle}codePermamentsiglenotesession

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 25

■ Classe qualifiant une association

Page 26: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Association un à plusieurs (cas 1)

■ Traduction par une tableC até gor ie

{UNIQUE: code}code : Stringde scr ipteur : S trin g 1 **1

Livre

{UNIQUE: ISBN}ISBN : Stringti tre : StringannéeParution : T ypeDonnéesAnnée

Livre{Clé primaire : ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : DomaineAnnée

<<table>>

Catégorie{Clé primaire : code}code : VARCHAR(10)descripteur : VARCHAR(20)

<<table>>

CatégorieLivre{Clé primaire : ISBN}ISBN : CHAR(13)code : VARCHAR(10)

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 26

NOT NULL

Page 27: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Association un à plusieurs (cas 2)

■ Ajout d’une clé étrangère■ Navigation plus performante

C até gor ie

{UNIQUE: code}code : Stringde scr ipteur : S trin g 1 **1

Livre

{UNIQUE: ISBN}ISBN : Stringti tre : StringannéeParution : T ypeDonnéesAnnée

Livre{Clé prim aire : ISBN}ISBN : CHAR(13)titre : VARCHAR(50)annéeParution : Dom aineAnnéecode : VARCHAR(10)

<<table>>

Catégorie{Clé prim aire : code}code : VARCHAR(10)descripteur : VARCHAR(20)

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 27

NOT NULL

Page 28: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Renommer la clé étrangère au besoin

Utilisateur{Clé primaire : noSequence}noSequence{Clé candidate : idUtilisateur}idUtilisateur : VARCHAR(10)motPasse : VARCHAR(10)catégorieUtilisateur : DomaineCatégorieUtilisateur

<<table>>

noSequenceUtilisateur

Prêt{Clé primaire : noSequence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExemplaire : VARCHAR(10)

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 28

NOT NULL

Page 29: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Association un à un (cas 1 → 0..1)

■ Une clé étrangère (du côté obligatoire)

Passeport{UNIQUE : noPas seport}noPasseportdateExpiration

Citoyen{UNIQUE : noAssurranceSociale}noAssurranceSocialenomprénom

0..11 0..11

Passeport{Clé primaire : noPasseport}noPasseportdateExpiration{Clé candidate : noAssurranceSociale}noAssurranceSociale

<<table>>

Citoyen{Clé primaire : noAssurranceSociale}noAssurranceSocialenomprénom

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 29

NOT NULL

Page 30: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Association un à un (cas 0..1 → 0..1)

Hom me{UNIQUE : noAssurranceSociale}noAssurranceSocialenomprénom

Fem me{UNIQUE : noA ssurranceSociale}noAssurranc eSocialenomprénom

0. .10..10..1 0. .1

grasAVie vieAGrasMariage

Hom me{Clé primaire : noAssurranceSociale}noAssurranceSocialenomprénom

<<table>>Femme

{Clé primaire : noAssurranceSociale}noAssurranceSocialenomprénom

<<table>>

Mariage{Clé candidate : noAssSocFemme}noAssSocFemme{Clé candidate : noAssSocHomme}noAssSocHom me

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 30

Note: Si la relation est fréquente, utiliserplutôt une approche 1 → 0..1

Page 31: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Association un à un (cas 1 → 1)

■ Fusion des deux classes dans une seule table■ Permet d’éviter la jointure

© R. Godin, C. Desrosiers - Hiver 2011 31

Page 32: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Relation d’agrégation

■ Comme une association normaleCatégorie

{UNIQUE: code}code : Stringdescripteur : String

enfant

0..1

*

parent 0..1

*

Catégor ie{Clé prim aire : code}code : VARCHAR(10)descripteur : VARCHAR(20)codeParent : VARCHAR(10)

<<table>>

codeParent

© R. Godin, C. Desrosiers - Hiver 2011 32

Page 33: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Relation de composition

■ Cas 1 → 1– ~ attribut complexe

■ Mode SQL CASCADE■ Oracle8:

VARRAY, NESTED TABLE

© R. Godin, C. Desrosiers - Hiver 2011 33

Page 34: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Relation de généralisation / spécialisation

■ Choix possibles:1. Délégation2. Fusion3. Concaténation

■ À considérer:1. Spécialisation complète ou incomplète (extensible)2. Spécialisation disjointe ou non (plusieurs sous-

classes)

© R. Godin, C. Desrosiers - Hiver 2011 34

Page 35: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Approche 1 : délégation

Personne

{Cl é pri m aire : noSeque nce}noSequence : INTEGERnom : VARCHAR2(20)prénom : VARCHAR2(20)

<<table>>

Mem bre

{Clé prim aire : noSequence}n oSequence : INTEGERtéléphoneRés idence : VARCHAR(15)

<<table>>

Em ployé{Clé prim aire : noSe quence}noSequence : INTEGER{Clé candidate : codeMatricule}codeMatricule : CHAR(6)catégorieEm ployé : Dom aineCatégorieEm ployé

<<table>>

Auteur{Clé prim aire : noSequence}noSequence : INTEGER

<<table>>Utilisateur

{Cl é pri maire : n oSequence}noSequence : INTEGER{Clé candidate : i dU til isate ur}idUtili sateur : VARCHAR(10)motPasse : VARCHAR(10)/ catégorieUti lisateur : Dom aineCatégorieUtilisateur

<<table>>

{catégorieUtilisateur doit être cohérent avec la table d'appartenance de l 'ob jet}

© R. Godin, C. Desrosiers - Hiver 2011 35

Note: Réutiliser la clé primaire de la table parent pour les tables enfant

Page 36: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Contrainte {disjointe, complète}

Prêt{Clé prim aire : noSeq uence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExem plaire : VARCHAR(10)

<<table>>

{Exclus ives et une des deux es t néces saire}

PrêtArchivé{Cl é pri m aire : noSequ ence}noSequence : INTEGERdateRetour : DATE

<<table>>

PrêtEnCours{Cl é p ri maire : noSequence}noSequence : INTEGER

<<table>>

PrêtdatePrêt : Date

{dis j oin te, comp lète}

PrêtArchivé

dateRetour : DatePrêtEnCours

© R. Godin, C. Desrosiers - Hiver 2011 36

Page 37: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Cas de l’héritage multiple

Auteur{Clé primaire : noSequence}noSequence

<<table>>Utilisateur

{Clé primaire : noSequence}noSequence{Clé candidate : idUtilisateur}idUtilisateur : VARCHAR(10)motPasse : VARCHAR(10)catégorieUtilisateur : DomaineCatégorieUtilisateur

<<table>>

Employé{Clé primaire : noSequence}noSequence : INTEGER{Clé candidate : codeMatricule}codeMatricule : CHAR(6)catégorieEmployé : DomaineCatégorieEmployé

<<table>>Membre

{Clé primaire : noSequence}noSequence : INTEGERtéléphoneRésidence : VARCHAR(15)

<<table>>

AuteurMembre{Clé primaire : noSequence}noSequence

!1. Créer une table de jointure

© R. Godin, C. Desrosiers - Hiver 2011 37

Page 38: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Approche 2 : fusion

Prêt{Clé primaire : noSequence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExemplaire : VARCHAR(10){catégoriePrêt = 'prêtEnCours' ssi dateRetour nulle}dateRetour[0..1] : DATEcatégoriePrêt : DomaineCatégoriePrêt

<<table>>

DomaineCatégoriePrêt{VARCHAR(15) CHECK valeur IN ('prêtEnCours', 'prêtArchivé')}

<<domain>>

Prêt{Clé prim aire : noSeq uence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExem plaire : VARCHAR(10)

<<table>>

{Exclus ives et une des deux es t néces saire}

PrêtArchivé{Cl é pri m aire : noSequ ence}noSequence : INTEGERdateRetour : DATE

<<table>>

PrêtEnCours{Cl é p ri maire : noSequence}noSequence : INTEGER

<<table>>

© R. Godin, C. Desrosiers - Hiver 2011 38

Page 39: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Approche 3 : concaténation

{La contrainte de cl é prim aire es t gl obal e pour les deux tabl es}

PrêtEnCours{Clé prim aire : noSequence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExem plaire : VARCHAR(10)

<<table>>PrêtArchivé

{Clé prim aire : noSequence}noSequence : INTEGERdateRetour : DATEdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExem plaire : VARCHAR(10)

<<table>>

Exe mplai re{Clé prim aire : idExem plaire}idExem plaire : VARCHAR(10)dateAchat : Dates tatut : Dom aineStatutISBN : CHAR(13)

<<table>>

Utili sate ur{Clé cand idate : noSequ ence}noSequence{Clé prim aire : idUtilisateur}idUti lisa teur : VARCHAR(10)m otPasse : VARCHAR(10)catégorieUtilisateur : Dom aineCatégorieUtilisateur

<<table>>

noSequenceUtilisateur noSequenceUtilisateurPrêt

{Clé prim aire : noSeq uence}noSequence : INTEGERdatePrêt : DATEnoSequenceUtilisateur : INTEGERidExem plaire : VARCHAR(10)

<<table>>

{Exclus ives et une des deux es t néces saire}

PrêtArchivé{Cl é pri m aire : noSequ ence}noSequence : INTEGERdateRetour : DATE

<<table>>

PrêtEnCours{Cl é p ri maire : noSequence}noSequence : INTEGER

<<table>>

Conserver une table pour le parent ?

© R. Godin, C. Desrosiers - Hiver 2011 39

Page 40: Conception de BD relationnelles€¦ · Département de génie logiciel et des TI Modélisation des données Schéma conceptuel – Modélise les classes, leurs attributs et leurs

Département de génie logiciel et des TI

Comparaison

© R. Godin, C. Desrosiers - Hiver 2011 40

Délégation Fusion Concaténation

Disjointe Oui ~ ✓ ~Non ✓ ✕ ✓

Complète Oui ~ ✓ ~Non ✓ ~ ✓