21/05/2014© robert godin. tous droits réservés.1 3introduction au modèle relationnel 3.1concepts...

11/04/23 © Robert Godin. Tous droits réservés. 1

3 Introduction au modèle relationnel

3.1 Concepts de base

Domaine : ensemble de valeurs Relation : ensemble de n-uplets (tuples) SQL : multi-ensemble

noClient nomClient noTéléphone10 Luc Sansom (999)999-999920 Dollard Tremblay (888)888-888830 Lin Bô (777)777-777740 Jean Leconte (666)666-666650 Hafedh Alaoui (555)555-555560 Marie Leconte (666)666-666670 Simon Lecoq (444)444-441980 Dollard Tremblay (333)333-3333

ligne /tuple

colonne /attribut

table /relation


suite

Deux facettes du concept de table

– Schéma d'une table (table schema)

définition de son type (intention)

ex: Client(noClient, nomClient, noTéléphone)

– Instance ou extension d'une table

état de la table ~ variable qui contient un ensemble de lignes modifications d ’état

Schéma relationnel

– ensemble de schémas de tables

Instance de BD VentesPleinDeFoinTable Commande

noCommande dateCommande noClient1 01/06/2000 102 02/06/2000 203 02/06/2000 104 05/07/2000 105 09/07/2000 306 09/07/2000 207 15/07/2000 408 15/07/2000 40

Table LigneCommandenoCommande noArticle quantité1 10 101 70 51 90 12 40 22 95 33 20 14 40 14 50 15 70 35 10 55 20 56 10 56 40 17 50 17 95 28 20 3

Table ArticlenoArticle description prixUnitaire quantitéEnStock10 Cèdre en boule 10.99 1020 Sapin 12.99 1040 Epinette bleue 25.99 1050 Chêne 22.99 1060 Erable argenté 15.99 1070 Herbe à puce 10.99 1080 Poirier 26.99 1081 Catalpa 25.99 1090 Pommier 25.99 1095 Génévrier 15.99 10

Table DétailLivraisonnoLivraison noCommande noArticle quantitéLivrée100 1 10 7100 1 70 5101 1 10 3102 2 40 2102 2 95 1100 3 20 1103 1 90 1104 4 40 1105 5 70 2

Table LivraisonnoLivraison dateLivraison100 3/06/2000101 4/06/2000102 4/06/2000103 5/06/2000104 7/07/2000105 9/07/2000

Table ClientnoClient nomClient noTéléphone10 Luc Sansom (999)999-999920 Dollard Tremblay (888)888-888830 Lin Bô (777)777-777740 Jean Leconte (666)666-666650 Hafedh Alaoui (555)555-555560 Marie Leconte (666)666-666670 Simon Lecoq (444)444-441980 Dollard Tremblay (333)333-3333


3.2 Contraintes d'intégrité du modèle relationnel 3.2.1 Contrainte de domaine et de valeur non nulle

T(A1 : D1, A2 : D2, …, An : Dn)

– Di : domaine de Ai

Valeur nulle– Comportement particulier– Valeur inconnue– Valeur non applicable– ...


3.2.2 Clé primaire et contrainte d'entité Clé unique (unique key)

– ou superclé (superkey )– identifiant

Clé candidate (candidate key)– clé unique minimale

Clé primaire (primary key)– sert de mécanisme de référence aux lignes de la

table Contrainte d'entité (entity constraint)

clé primaire – non nulle


3.2.3 Contrainte d'intégrité référentielle Clé étrangère non nulle clé primaire

noClient nomClient noTéléphone10 Luc Sansom (999)999-999920 Dollard Tremblay (888)888-888830 Lin Bô (777)777-777740 Jean Leconte (666)666-666650 Hafedh Alaoui (555)555-555560 Marie Leconte (666)666-666670 Simon Lecoq (444)444-441980 Dollard Tremblay (333)333-3333


Table Commande Table Client

Clé étrangère Clé primaire


Représentation UML

Commande

{Clé primaire : noCommande}noCommande : INTEGERdateCommande : DATEnoClient : INTEGER

<<Table>>

Client

{Clé primaire : noClient}noClient : INTEGERnomClient : VARCHARnoTéléphone : VARCHAR

<<Table>>

LigneCommande

{Clé primaire : noCommande, noArticle}noCommande : INTEGERnoArticle : INTEGERquantité : INTEGER

<<Table>>

Article

{Clé primaire : noArticle}noArticle : INTEGERdescription : VARCHARprixUnitaire : DECIMALquantitéEnStock : INTEGER

<<Table>>

Livraison

{Clé primaire : noLivraison}noLivraison : INTEGERdateLivraison : DATE

<<Table>> DétailLivraison

{Clé primaire : noLivraison, noCommande, noArticle}noLivraison : INTEGERnoCommande : INTEGERnoArticle : INTEGERquantitéLivrée : INTEGER

<<Table>>


Nom clé étrangère nom clé primaire Étiquette de la relation de

dépendance

numéroClient

Client


<<Table>>Commande

{Clé primaire : noCommande}noCommande : INTEGERdateCommande : DATEnuméroClient : INTEGER

<<Table>>


3.3 Conception logique : traduction du modèle conceptuel de données en schéma conceptuel relationnel

Output : schéma relationnel (niveau conceptuel) – tables– colonnes

types, domaines, ...

– contraintes d ’intégrité clés primaires, étrangères, ...

Input à la conception– modèle conceptuel des données

MDA– Platform Specific Model (PSM)


3.3.1 Première ébauche du schéma : une table par classe

Personne<<table>>

Membre<<table>> PrêtEnCours

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

Employé<<table>>

Catégorie<<table>>

Auteur<<table>>

Editeur<<table>>

Livre<<table>>

Exemplaire<<table>>

Prêt<<table>>

Utilisateur<<table>>


3.3.2 Traduction des attributs

Attribut de la classe -> colonne de la table

Livre

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

<<table>>Livre

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


3.3.2.1 TRADUCTION DES CONTRAINTES D'IDENTIFICATION (UNIQUE)

Contrainte UNIQUE -> clé candidate relationnelle– clé primaire ?

Livre


<<table>>

Livre



3.3.2.2 TRADUCTION DES TYPES DE DONNÉESType OCL Type SQL2 Oracle 8Boolean BIT(1) Non supporté (utiliser CHAR(1) + CHECK)Integer INTEGER ou SMALLINT NUMBER(n)String CHARACTER (CHAR) (n), CHARACTER

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

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

NUMBER(p,s)

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

Domaine non supporté

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

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


Types de données déclarés

Domaine pas toujours supporté par le dialecte SQL

Livre


TypeDonnéesAnnée

{Integer > 0 }

<<datatype>>

Livre


<<table>>

DomaineAnnée

{INTEGER CHECK value > 0 }

<<domain>>


3.3.2.3 TYPES ÉNUMÉRÉS

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

CHECKExemplaire

{Clé candidate : idExemplaire}idExemplaire : VARCHAR(10)dateAchat : Datestatut : DomaineStatut

<<table>>

DomaineStatut

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

<<domain>>

Exemplaire

{UNIQUE: idExemplaire}idExemplaire : StringdateAchat : Datestatut : enum(prêté, disponible, retiré)


Création d ’une table

Gros domaine ou extensible– création d ’une table à part– utilisé comme liste de valeurs (LOV

Designer)– introduction d ’une clé primaire

artificielle ?Exemplaire

{Clé candidate : idExemplaire}idExemplaire : VARCHAR(10)dateAchat : Datestatut : VARCHAR(15)

<<table>>

DomaineStatut

{Clé primaire : statut}statut : VARCHAR(15)

<<table>>


3.3.2.4 TYPES COMPLEXES

1. Représentation explicite des attributs du type complexe

Membre

adresse : typeDonnéesAdresse

typeDonnéesAdresse

numéroCiviquenuméroAppartementnomRuenomVillenomProvincenomPayscodePostal

<<datatype>>Membre


<<table>>


2. Création d'une nouvelle table

N.B. Pas de partage de la même adresse !

Membre

{Clé primaire : idMembre}idMembre

<<table>>

Adresse

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

<<table>>

Membre

adresse : typeDonnéesAdresse

typeDonnéesAdresse


<<datatype>>


3.3.2.5 QUALIFICATEUR

Colonne dans la table du rôle opposé– voir 3.3.3.2

Groupe

nbMaximumInscrits

Cours

{UNIQUE:sigle}sigletitrenbCrédits 1

0..1

numérosession

numérosession

10..1

Groupe

{Clé primaire : sigle, numéro, session}siglenumérosessionnbMaximumInscrits

<<table>>

Cours

{Clé primaire :sigle}sigletitrenbCrédits

<<table>>


3.3.2.6 DISCRIMINANT

Redondant ?Utilisateur

{UNIQUE :idUtilisateur}idUtilisateur : StringmotPasse : String

catégorieUtilisateur

Membre

téléphoneRésidence : String$ nbMaxPrêts : Integer = 5$ duréeMaxPrêts : Integer = 7

Employé

{UNIQUE : codeMatricule}codeMatricule : StringcatégorieEmployé : enum(bibliothécaire, commis)

{disjointe, complète}

Utilisateur

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

<<table>>

DomaineCatégorieUti lisateur

{VARCHAR(14) CHECK value IN ('employé, 'membre')}

<<domain>>


3.3.2.7 ATTRIBUT MULTIVALUÉ

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


3.3.2.8 COMPRESSION DES VALEURS DES ATTRIBUTS DE GRANDE TAILLE

Supporté par le SGBD ? Multimédia


3.3.2.9 CRÉATION DE TABLES SUPPLÉMENTAIRES POUR LES ATTRIBUTS DE CLASSE

MembreGénéral

nbMaxPrêts : INTEGER = 5duréeMaxPrêts : INTEGER = 7

<<table>>

Membre

téléphoneRésidence : String$ nbMaxPrêts : Integer = 5$ duréeMaxPrêts : Integer = 7

Membre

téléphoneRésidence : VARCHAR(15)

<<table>>


3.3.3 Réalisation de l'identité par les clés primaires

MembreGénéral

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

<<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)

<<table>>

Auteur

{Clé primaire : noSequence}noSequence

<<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ée

<<table>> Exemplaire

{Clé primaire : idExemplaire}idExemplaire : VARCHAR(10)dateAchat : Datestatut : DomaineStatut

<<table>>

Prêt

{Clé primaire : noSequence}noSequence : INTEGERdatePrêt : DATE

<<table>>

Utilisateur

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

<<table>>

AuteurLivre

{Clé primaire : noSequence, ISBN}noSequenceISBN


3.3.3.1 CRÉATION D'UNE CLÉ PRIMAIRE ARTIFICIELLE

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

PrêtArchivé


<<table>>PrêtEnCours


<<table>>

Prêt

{Clé primaire : noSequence}noSequence : INTEGERdatePrêt : DATE

<<table>>Prêt

datePrêt : Date

PrêtArchivé

dateRetour : DatePrêtEnCours


3.3.3.2 UTILISATION D'UN IDENTIFIANT NATUREL COMME CLÉ PRIMAIRE

1. Utilisation d'un identifiant naturel (UNIQUE)

Si identifiant naturel trop lourd– introduire clé primaire artificielle

Livre


<<table>>Livre

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

<<table>>


2. Utilisation du qualificateur

Groupe

nbMaximumInscrits

Cours

{UNIQUE:sigle}sigletitrenbCrédits 1

0..1

numérosession

numérosession

10..1

Groupe

{Clé primaire : sigle, numéro, session}siglenumérosessionnbMaximumInscrits

<<table>>

Cours

{Clé primaire :sigle}sigletitrenbCrédits

<<table>>


3.3.3.3 IDENTIFIANT NATUREL POUR UNE SPÉCIALISATION

Éviter de changer de clé primaire dans le contexte d ’une hiérarchie de généralisation

Si la table du parent est omise– considérer identifiant naturel de la

spécialisation


3.3.4 Traduction des associationsPrêt

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

<<table>>

Personne


<<table>>

Membre


<<table>>PrêtEnCours


<<table>>PrêtArchivé


<<table>>

Employé


<<table>>

Catégorie

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

<<table>>

Auteur


<<table>>

Editeur


<<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


<<table>>

MembreGénéral


<<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}


3.3.4.1 CAS GÉNÉRAL : TRADUCTION D'UNE ASSOCIATION PAR UNE TABLE

Auteur


<<table>>Livre

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

<<table>>

AuteurLivre

{Clé primaire : noSequence, ISBN}noSequence : INTEGERISBN : CHAR(13)

<<table>>{Un Auteur ne peut exister sans AuteurLivre}


Auteur

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

Livre



3.3.4.2 TRADUCTION D'UN RÔLE ORDONNÉ POUR UNE ASSOCIATION

Auteur

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

Livre


{ordonné}

A u teu r

{C lé p rim a ire : n o S eq u en c e}n o S eq u en c e : IN T E G E R

< < ta b le> >

A u teu rL iv re

{C lé p rim a ire : n o S eq u en c e , IS B N }n o S eq u en c e : IN T E G E R{C lé c a n d id a te : IS B N , o rd reA u teu r }IS B N : C H A R (1 3 )o rd reA u teu r : IN T E G E R

< < ta b le> >

L iv re

{C lé p rim a ire : IS B N }IS B N : C H A R (1 3 )t it re : V A R C H A R (5 0 )a n n é eP a ru tio n : D om a in eA n n é e

< < ta b le> >


3.3.4.3 CLASSE ASSOCIATIVE

Cours

{UNIQUE : sigle}sigletitrenbCrédits

Etudiant

{UNIQUE : codePermanent}codePermanentnomprénom

** **

NoteObtenue

notesession

Cours

{Clé primaire : sigle}sigletitrenbCrédits

<<table>>Etudiant

{Clé primaire : codePermanent}codePermanentnomprénom

<<table>>

NoteObtenue

{Clé primaire : codePermament, sigle}codePermamentsiglenotesession

<<table>>


3.3.4.4 CAS UN À PLUSIEURS

1. Traduction par une table ?Catégorie

{UNIQUE: code}code : Stringdescripteur : String

1 **1

Livre


Livre

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

<<table>>

Catégorie

{C lé prim aire : code}code : VARCHAR(10)descripteur : VARCHAR(20)

<<table>>

CatégorieL ivre

{C lé prim aire : ISBN}ISBN : CHAR(13)code : VARCHAR(10)

<<table>>


2. Traduction par l'ajout d'une clé étrangère à privilégier

Navigation plus performanteCatégorie


1 **1

Livre


Livre

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

<<table>>

Catégorie

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

<<table>>


Renommer la clé étrangère au besoin

Utilisateur


<<table>>

noSequenceUtilisateur

Prêt


<<table>>


3.3.4.5 CAS UN À UN

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

Passeport

{UNIQUE : noPasseport}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>>


2. Une nouvelle tableHomme


Femme


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

grasAVie vieAGrasMariage

Homme


<<table>>Femme


<<table>>

Mariage

{Clé candidate : noAssSocFemme}noAssSocFemme{Clé candidate : noAssSocHomme}noAssSocHomme

<<table>>


3. Fusion

Cas 1-1 obligatoire


3.3.4.6 CAS DE L'AGRÉGATION

Comme une association normaleCatégorie


enfant

0..1

*

parent 0..1

*

Catégorie


<<table>>

codeParent


Composition

Cas 1-1– ~ attribut complexe

Mode SQL CASCADE Oracle8

– VARRAY, NESTED TABLE


3.3.5 Traduction des relations de généralisation/spécialisation

Délégation Fusion Concaténation


3.3.5.1 DÉLÉGATIONPersonne


<<table>>

Membre


<<table>>

Employé


<<table>>

Auteur


<<table>>Utilisateur

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

<<table>>

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


Changement de clé primaire ? Extraire le téléphone résidence d'un

Membre possédant l'idUtilisateur 12345

Personne


<<table>> Utilisateur

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

<<table>>

Employé

{Clé primaire : idUtilisateur}idUtilisateur : VARCHAR(10){Clé candidate : codeMatricule}codeMatricule : CHAR(6)catégorieEmployé : DomaineCatégorieEmployé

<<table>>

Membre

{Clé primaire : idUtilisateur}idUtilisateur : VARCHAR(10)téléphoneRésidence : VARCHAR(15)

<<table>>


Traduction de la contrainte {disjointe, complète}

Prêt


<<table>>

{Exclusives et une des deux est nécessaire}

PrêtArchivé


<<table>>

PrêtEnCours


<<table>>

Prêt

datePrêt : Date

{disjointe, complète}

PrêtArchivé

dateRetour : DatePrêtEnCours


3.3.5.1.1 Cas de la multi-classification et de l'héritage multiple

Auteur


<<table>>Utilisateur

{Clé primaire : noSequence}noSequence{Clé candidate : idUtilisateur}idUtilisateur : VARC HAR(10)motPasse : VAR CHAR(10)catégorieUtilisateur : D omaineC atégorieUtilisateur

<<table>>

Employé

{Clé primaire : noSequence}noSequence : INTEGER{Clé candidate : codeMatricule}codeMatricule : CH AR(6)catégorieEmployé : D omaineC atégorieEmployé

<<table>>

Membre

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

<<table>>

AuteurMembre


1. Créer une table de jointure


Alternatives

2. Identité commune sans table de jointure

3. Identité séparée et redondance de données


3.3.5.2 ANALOGIE AVEC UNE ASSOCIATION UN À UN

Clé étrangère– dans l ’enfant– dans le parent ???

Approche Arc de Designer

Fusion– vers le parent (approche par fusion)– vers l ’enfant (approche par

concaténation) Nouvelle table ???


3.3.5.3 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


<<table>>


PrêtArchivé


<<table>>

PrêtEnCours


<<table>>


3.3.5.4 CONCATÉNATION

{La contrainte de clé primaire est globale pour les deux tables}

PrêtEnCours


<<table>>PrêtArchivé

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

<<table>>

Exemplaire


<<table>>

Utilisateur

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

<<table>>

noSequenceUtilisateur noSequenceUtilisateurPrêt


<<table>>


PrêtArchivé


<<table>>

PrêtEnCours


<<table>>

Conserver une table pour le parent ?


Cas d ’une référence au parent

Membre

{Clé primaire : noSequence}noSequence : INTEGERnom : VARCHAR2(20)prénom : VARCHAR2(20){Clé candidate : idUtilisateur}idUtilisatuer : VARCHAR(10)motPasse : VARCHAR(10)téléphoneRésidence : VARCHAR(15)

<<table>>

Employé

{Clé primaire : noSequence}noSequence : INTEGERnom : VARCHAR2(20)prénom : VARCHAR2(20){Clé candidate : idUtilisateur}idUtilisateur : VARCHAR(10)motPasse : VARCHAR(10)catégorieEmployé : DomaineCatégorieEmployé{Clé candidate : codeMatricule}codeMatricule : CHAR(6)

<<table>>

{exclusives}

noSequenceEmployé

noSequenceMembre

Prêt

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

<<table>>


3.3.5.5 TRADUCTION DES GÉNÉRALISATIONS POUR SYLERAT

Membre


<<table>>

PrêtEnCours

{Clé primaire : noSequence}noSequence : INTEGERdatePrêt : DATEidUtilisateur : VARCHAR(10)idExemplaire : VARCHAR(10)

<<table>>

PrêtArchivé

{Clé primaire : noSequence}noSequence : INTEGERdateRetour : DATEdatePrêt : DATEidUtilisateur : VARCHAR(10)idExemplaire : VARCHAR(10)

<<table>>

Employé

{Clé primaire : idUtilisateur}idUtilisateur : VARCHAR(10){Clé candidate : codeMatricule}codeMatricule : CHAR(6)catégorieEmployé : DomaineCatégorieEmployé

<<table>>

Catégorie


<<table>>

Auteur


<<table>>

Editeur


<<table>>

Livre

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

<<table>> Exemplaire


<<table>>

Utilisateur

{Clé primaire : idUtilisateur}idUtilisateur : VARCHAR(10)motPasse : VARCHAR(10)nom : VARCHAR2(20)prénom : VARCHAR2(20)/ catégorieUtilisateur : DomaineCatégorieUtilisateur

<<table>>

MembreGénéral


<<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 Editeur ne peut exister sans Livre}

codeParent

{Un Livre ne peut exister sans Exemplaire}


{CatégorieUtilisateur doit être cohérente avec la table d'appartenance}

{Lors d'un retour le PrêtArchivé est généré à partir du PrêtEnCours en réutilisant le même noSequence}


3.3.6 Traduction des autres contraintes

PrêtEnCours

{Clé primaire : idExemplaire}idExemplaire : VARCHAR(10)datePrêt : DATEidUtilisateur : VARCHAR(10)

<<table>>

PrêtArchivé

{Clé primaire : noSequence}noSequence : INTEGER{dateRetour >= datePrêt}dateRetour : DATEdatePrêt : DATEidUtilisateur : VARCHAR(10)idExemplaire : VARCHAR(10)

<<table>>

Exemplaire


<<table>>

MembreGénéral


<<table>>

{statut = 'prêté' si et seulement si un PrêtEnCours fait référence à l'Exemplaire}

{Le nombre de PrêtEnCours d'un Membre <= nbMaxPrêts}

Utilisateur

{Clé primaire : idUtilisateur}idUtilisateur : VARCHAR(10)motPasse : VARCHAR(10)nom : VARCHAR2(20)prénom : VARCHAR2(20)/ catégorieUtilisateur : DomaineCatégorieUtilisateur

<<table>>

Membre


<<table>>


3.4 Algèbre relationnelle

Opérations de manipulation de données

Entrée

– une table : opération unaire

– deux tables : opération binaire

Sortie

– une table

– permet la composition

Origine de SQL (« SQUARE de IBM, San Jose »)


3.4.1 Sélection ()Table Article

noArticle description prixUnitaire quantitéEnStock10 Cèdre en boule 10.99 1020 Sapin 12.99 1040 Epinette bleue 25.99 1050 Chêne 22.99 1060 Erable argenté 15.99 1070 Herbe à puce 10.99 1080 Poirier 26.99 1081 Catalpa 25.99 1090 Pommier 25.99 1095 Génévrier 15.99 10

noArticle description prixUnitaire quantitéEnStock60 Erable argenté 15.99 1070 Herbe à puce 10.99 1095 Génévrier 15.99 10

prixUnitaire < 20.00 ET noArticle > 30 (Article)


3.4.2 Projection ()

noClient, dateCommande (Commande)

Table CommandenoCommande dateCommande noClient1 01/06/2000 102 02/06/2000 203 02/06/2000 104 05/07/2000 105 09/07/2000 306 09/07/2000 207 15/07/2000 408 15/07/2000 40

noClient dateCommande10 01/06/200020 02/06/200010 02/06/200010 05/07/200030 09/07/200020 09/07/200040 15/07/2000


3.4.3 Expressions complexes

noClient, dateCommande ( dateCommande > 05/07/2000 Commande)


noCommande dateCommande noClient5 09/07/2000 306 09/07/2000 207 15/07/2000 408 15/07/2000 40

dateCommande > 05/07/2000 Commande

noClient dateCommande30 09/07/200020 09/07/200040 15/07/2000


3.4.4 Produit cartésien ()


Client Commande

Client.noClient nomClient noTéléphone noCommande dateCommande Commande. noClient10 Luc Sansom (999)999-9999 1 01/06/2000 1010 Luc Sansom (999)999-9999 2 02/06/2000 2010 Luc Sansom (999)999-9999 3 02/06/2000 1010 Luc Sansom (999)999-9999 4 05/07/2000 1010 Luc Sansom (999)999-9999 5 09/07/2000 3010 Luc Sansom (999)999-9999 6 09/07/2000 2010 Luc Sansom (999)999-9999 7 15/07/2000 4010 Luc Sansom (999)999-9999 8 15/07/2000 4020 Dollard Tremblay (888)888-8888 1 01/06/2000 1020 Dollard Tremblay (888)888-8888 2 02/06/2000 2020 Dollard Tremblay (888)888-8888 3 02/06/2000 10… … … … … …

...



Exemple. Produire les informations au sujet des Clients et de leurs CommandesClient Commande

Client.noClient nomClient noTéléphone noCommande dateCommande Commande. noClient10 Luc Sansom (999)999-9999 1 01/06/2000 1010 Luc Sansom (999)999-9999 2 02/06/2000 2010 Luc Sansom (999)999-9999 3 02/06/2000 1010 Luc Sansom (999)999-9999 4 05/07/2000 1010 Luc Sansom (999)999-9999 5 09/07/2000 3010 Luc Sansom (999)999-9999 6 09/07/2000 2010 Luc Sansom (999)999-9999 7 15/07/2000 4010 Luc Sansom (999)999-9999 8 15/07/2000 4020 Dollard Tremblay (888)888-8888 1 01/06/2000 1020 Dollard Tremblay (888)888-8888 2 02/06/2000 2020 Dollard Tremblay (888)888-8888 3 02/06/2000 10… … … … … …

Client.noClient nomClient noTéléphone noCommande dateCommande Commande. noClient10 Luc Sansom (999)999-9999 1 01/06/2000 1010 Luc Sansom (999)999-9999 3 02/06/2000 1010 Luc Sansom (999)999-9999 4 05/07/2000 1020 Dollard Tremblay (888)888-8888 2 02/06/2000 2020 Dollard Tremblay (888)888-8888 6 09/07/2000 2030 Lin Bô (777)777-7777 5 09/07/2000 3040 Jean Leconte (666)666-6666 7 15/07/2000 4040 Jean Leconte (666)666-6666 8 15/07/2000 40

clé primaire = clé étrangère

Client.noClient = Commande.noClient (Client Commande)


Exemple. Produire les informations au sujet des Clients et de leurs Commandes Client.noClient = Commande.noClient (Client Commande)Client.noClient nomClient noTéléphone noCommande dateCommande Commande. noClient10 Luc Sansom (999)999-9999 1 01/06/2000 1010 Luc Sansom (999)999-9999 3 02/06/2000 1010 Luc Sansom (999)999-9999 4 05/07/2000 1020 Dollard Tremblay (888)888-8888 2 02/06/2000 2020 Dollard Tremblay (888)888-8888 6 09/07/2000 2030 Ye San Le Sou (777)777-7777 5 09/07/2000 3040 Jean Leconte (666)666-6666 7 15/07/2000 4040 Jean Leconte (666)666-6666 8 15/07/2000 40

Client.noClient, nomClient, noTéléphone, noCommande,

dateCommande (…))

Colonne redondante

Client.noClient nomClient noTéléphone noCommande dateCommande10 Luc Sansom (999)999-9999 1 01/06/200010 Luc Sansom (999)999-9999 3 02/06/200010 Luc Sansom (999)999-9999 4 05/07/200020 Dollard Tremblay (888)888-8888 2 02/06/200020 Dollard Tremblay (888)888-8888 6 09/07/200030 Ye San Le Sou (777)777-7777 5 09/07/200040 Jean Leconte (666)666-6666 7 15/07/200040 Jean Leconte (666)666-6666 8 15/07/2000


3.4.5 Jointure naturelle ()

noClient nomClient noTéléphone noCommande dateCommande10 Luc Sansom (999)999-9999 1 01/06/200010 Luc Sansom (999)999-9999 3 02/06/200010 Luc Sansom (999)999-9999 4 05/07/200020 Dollard Tremblay (888)888-8888 2 02/06/200020 Dollard Tremblay (888)888-8888 6 09/07/200030 Lin Bô (777)777-7777 5 09/07/200040 Jean Leconte (666)666-6666 7 15/07/200040 Jean Leconte (666)666-6666 8 15/07/2000


Client Commande

Colonnes communes : colonnes de jointure ou clé de jointure

Schéma : union des colonnes



Sélectionner les nomClient qui ont commandé au moins un plant d'herbe à puce

Commande


<<Table>>

Client


<<Table>>

LigneCommande


<<Table>>

Article


<<Table>>

Livraison




<<Table>>


Sélectionner les nomClient qui ont commandé au moins un plant d'herbe à puce

Commande


<<Table>>

Client


<<Table>>

LigneCommande


<<Table>>

Article


<<Table>>

Livraison




<<Table>>

nomClient ( description = “Herbe à puce ”

(Client Commande LigneCommande Article))


Formulations équivalentes

nomClient ( description = “Herbe à puce ” (Client Commande LigneCommande Article))

nomClient ( description = “Herbe à puce ” (((Client Commande) LigneCommande) Article))

nomClient ( description = “Herbe à puce ” (((Client LigneCommande) Commande) Article))

…


Quelques règles d ’équivalence

Eclatement d'une sélection conjonctive (SE)– e1 ET e2 (T) = e1 ( e2 (T))

Commutativité de la sélection (SC) e1 ( e2 (T)) = e2 ( e1 (T))

Elimination des projections en cascades (PE) liste1 ( liste2 (T)) = liste1 (T)

Commutativité de la jointure (JC)– T1 T2 = T1 T2

Associativité de la jointure (JA)– T1 (T2 T3) = (T1 T2) T3

Voir évaluation des requêtes


Représentation sous forme d ’arbre de l ’expression algébrique

nomClient ( description = “Herbe à puce ” (((Client Commande) LigneCommande) Article))

Client Commande

LigneCommande

Article

description = «Herbe à puce»

nomClient


Cas d ’une clé de jointure composée

LigneCommande DétailLivraison



noCommande noArticle quantité noLivraison quantitéLivrée1 10 10 100 71 10 10 101 31 70 5 100 51 90 1 103 12 40 2 102 22 95 3 102 13 20 1 100 14 40 1 104 15 70 3 105 2


3.4.6 Opération renommer ()

Quels sont les Clients qui ont le même numéro de

téléphone?

Client2 (Client)

Client.noClient, Client2.noClient, ( Client.noTéléphone = Client2.noTéléphone (Client Client2 (Client)))


Table Client2noClient nomClient noTéléphone10 Luc Sansom (999)999-999920 Dollard Tremblay (888)888-888830 Lin Bô (777)777-777740 Jean Leconte (666)666-666650 Hafedh Alaoui (555)555-555560 Marie Leconte (666)666-666670 Simon Lecoq (444)444-441980 Dollard Tremblay (333)333-3333

Client. noClient Client2.noClient40 6060 4010 1020 2030 3040 4050 5060 6070 7080 80


Renommer les colonnes

Quels sont les Clients qui ont le même numéro de

téléphone?

Client2(noClient2, nomClient2,

noTéléphone) (Client) noClient, noClient2 (Client Client2(noClient2, nomClient2,

noTéléphone) (Client))


Table Client2noClient2 nomClient2 noTéléphone10 Luc Sansom (999)999-999920 Dollard Tremblay (888)888-888830 Lin Bô (777)777-777740 Jean Leconte (666)666-666650 Hafedh Alaoui (555)555-555560 Marie Leconte (666)666-666670 Simon Lecoq (444)444-441980 Dollard Tremblay (333)333-3333

noClient noClient240 6060 4010 1020 20… …


3.4.7 Jointure-Table Commande altérée

noCommande dateCommande numéroClient1 01/06/2000 102 02/06/2000 203 02/06/2000 104 05/07/2000 105 09/07/2000 306 09/07/2000 207 15/07/2000 408 15/07/2000 40

noClient nomClient noTéléphone noCommande dateCommande numéroClient10 Luc Sansom (999)999-9999 1 01/06/2000 1010 Luc Sansom (999)999-9999 3 02/06/2000 1010 Luc Sansom (999)999-9999 4 05/07/2000 1020 Dollard Tremblay (888)888-8888 2 02/06/2000 2020 Dollard Tremblay (888)888-8888 6 09/07/2000 2030 Ye San Le Sou (777)777-7777 5 09/07/2000 3040 Jean Leconte (666)666-6666 7 15/07/2000 4040 Jean Leconte (666)666-6666 8 15/07/2000 40

Client noClient = numéroClient Commande altérée =

noClient = numéroClient (Client Commande altérée)



Cherchez les LigneCommandes qui ont été livrées en une seule livraison

LigneCommande.noCommande,

LigneCommande.noArtilce, quantité, noLivraison,

quantitéLivrée (LigneCommande

LigneCommande.noCommande =

DétailLivraison.noCommande ET

LigneCommande.noArticle = DétailLivraison.noArticle ET

quantité = quantitéLivrée DétailLivraison)



noCommande noArticle quantité noLivraison quantitéLivrée1 70 5 100 51 90 1 103 12 40 2 102 23 20 1 100 14 40 1 104 1


3.4.8 Jointure externe

Inclure les « célibataires » La jointure externe à gauche

T1 = T2 (célibataires de T1 )

La jointure externe à droite

T1 = T2

La jointure externe pleine

T1 == T2


Produire les informations au sujet des Clients et de leurs Commandes incluant les informations sur les Clients qui n’ont pas placé de Commande


Client = Commande

noClient nomClient noTéléphone noCommande dateCommande10 Luc Sansom (999)999-9999 1 01/06/200010 Luc Sansom (999)999-9999 3 02/06/200010 Luc Sansom (999)999-9999 4 05/07/200020 Dollard Tremblay (888)888-8888 2 02/06/200020 Dollard Tremblay (888)888-8888 6 09/07/200030 Lin Bô (777)777-7777 5 09/07/200040 Jean Leconte (666)666-6666 7 15/07/200040 Jean Leconte (666)666-6666 8 15/07/200050 Hafedh Alaoui (555)555-5555 null null60 Marie Leconte (666)666-6666 null null70 Simon Lecoq (444)444-4419 null null80 Dollard Tremblay (333)333-3333 null null



3.4.9 Semi-jointure ()Table Commande


Client Commande

noClient nomClient noTéléphone10 Luc Sansom (999)999-999920 Dollard Tremblay (888)888-888830 Lin Bô (777)777-777740 Jean Leconte (666)666-6666



3.4.10Opérations ensemblistes (,, -) Schémas compatibles

Table T1

A B1 12 2

Table T2

A B2 23 3

T1 T2

A B1 12 23 3

T1 T2

A B2 2

T1 - T2

A B1 1

T2 – T1

A B3 3


Produire les noms et numéros de téléphone des Employés qui sont aussi des Clients de la pépinière

( nomPersonne, noTéléphone ( Client (noClient, nomPersonne, noTéléphone) (Client))) ( nomPersonne, noTéléphone ( Employé (codeEmployé, nomPersonne,

noTéléphone) (Employé)))

Table EmployécodeEmployé nomEmployé noTéléphoneCASD1 Dollard Tremblay (888)888-8888PIOY1 Yan Piochuneshot 911LAFH1 Yvan Lafleur (111)111-1111HASC1 Jean Leconte (666)666-6666

nomPersonne noTéléphoneDollard Tremblay (888)888-8888Jean Leconte (666)666-6666

équivalent à si compatibles



3.4.11Division ()

A B– plus grande table C qui est telle que

B C A– ~ inverse du produit cartésien

T1 (X) T2 (Y) =– X-Y (T1) - (( X-Y (T1) T2 ) - T1)


Quelles sont les noCommande des Commandes qui incluent tous les Articles dont le prixUnitaire est $10.99

noArticle1070

noArticle ( prixUnitaire = 10.99 (Article))noCommande noArticle

1 101 701 902 402 953 204 404 505 705 105 206 106 407 507 95

noCommande, noArticle (LigneCommande)

noCommande15

noCommande, noArticle (LigneCommande) noArticle ( prixUnitaire = 10.99 (Article))


3.4.12 Ensemble minimal d'opérations de l'algèbre relationnelle

Sélection () Projection () Produit cartésien () Union () Différence (-) Renommage ()


3.4.13 Extensions de l'algèbre relationnelle pour les bases de données

Fonctions sur colonnes– opérations arithmétiques (+, - , *, /,…)– manipulation de chaînes (concaténation,

…)– ...

Fonctions de groupe (ou d'agrégat)– e.g. total, moyenne, écart-type, min, max,…

Récursivité– e.g. fermeture transitive

21/05/2014© robert godin. tous droits réservés.1 3introduction au modèle relationnel 3.1concepts...

Documents