Quinio1 Bases de données : modèlisation et SGBD Séance 3 B Quinio

Quinio 1

Bases de données :modèlisation et SGBD

Séance 3 B Quinio

Quinio 2

BDD : définitionsBDD : définitions

Table ou relationTable ou relationClé primaireClé primaireChampChamp

Attribut avec un formatAttribut avec un format

DomaineDomaineDomaine de validité de certains attributsDomaine de validité de certains attributs

TupleTupleClé externeClé externe

Quinio 3

BDD : démarcheBDD : démarche

Conception : Conception : modélisationmodélisation

Développement :Développement :création, optimisationcréation, optimisation

TestTestUtilisation et mise à Utilisation et mise à

jourjourMaintenanceMaintenance

Gestion Informatique

Informatique

Gestion Informatique

Gestion

Informatique

Quinio 4

Modèle venant de Merise : Modèle venant de Merise : MCDMCD

EntitéEntitéAssociation

0-n 1-n

cardinalités

5

ELEVE

Numéro élèveNom élèvePrénom élèveDate de naissance

MATIERE

Numéro matièreLibellé

PROFESSEUR

Numéro professeurNom professeurPrénom professeur

APPREND

ENSEIGNE

1-1

0-N

1-N

0-N

6

ELEVE


MATIERE


PROFESSEUR


APPREND

ENSEIGNE

1-1

0-N

1-N

0-NNOTE

7

ELEVE


MATIERE


PROFESSEUR


APPREND

ENSEIGNE

1-1

0-N

1-N

0-NNOTE

INTERVIENT dans

CLASSE

Numéro classeNiveau

EST AFFECTE à

1-1

1-N

1-N

0-N

Quinio 8

Modèle relationnelModèle relationnel

Chaque entité devient une tableChaque entité devient une table

Chaque association porteuse (Chaque association porteuse (relation relation n,nn,n) devient une table) devient une table

Chaque association non porteuse Chaque association non porteuse

((relation 1,nrelation 1,n) devient une clé ) devient une clé externe dans une table externe dans une table

9

1. Elève (Numéro élève, Nom élève, Prénom élève, Date naissance, Numéro classe)

2. Apprend (Numéro élève, Numéro matière, Note)

3. Matière (Numéro matière, Libellé)

4. Professeur (Numéro professeur, Nom professeur, Prénom professeur, Numéro matière)

5. Classe (Numéro classe, Niveau)

6. Intervient dans (Numéro classe, Numéro professeur)

Clef externe

Schéma relationnel

Quinio 10

Normalisation de Codd : Normalisation de Codd : point de départpoint de départ

Liste des attributsListe des attributs Dépendances fonctionnellesDépendances fonctionnelles Dépendances multivaluéesDépendances multivaluées

NON vues iciNON vues ici

Quinio 11

NormalisationNormalisation : processus

Normaliser c’est regrouper la liste Normaliser c’est regrouper la liste

des attributs en relationsdes attributs en relations

- - ayant du sens ayant du sens

- cohérentes - cohérentes

- sans redondance- sans redondance

On obtient une série de tableOn obtient une série de table

Quinio 12

Normalisation : 1FNNormalisation : 1FN

Tout attribut de R contient:

une valeur monovaluée et non composée

Quinio 13

Normalisation : Normalisation : 1FN1FN

Exemples :Exemples :Prénom 1, Prénom 2Prénom 1, Prénom 2Adresse: Numéro Adresse: Numéro voie, type voie, nom voie, type voie, nom voie, nom ville, code voie, nom ville, code postalpostal

Quinio 14


R (R (Numéro élève, Numéro élève, Prénoms, Adresse)Prénoms, Adresse)

n’est pas en 1FNn’est pas en 1FN

Quinio 15

R normalisée en 1FNR normalisée en 1FN

RR ( (Numéro élèveNuméro élève, , Prénom1, Prénom2, Prénom1, Prénom2, Numéro voie, Type voie, Numéro voie, Type voie, Nom voie, Nom ville, Nom voie, Nom ville,

Code postal) Code postal)

Quinio 16

Normalisation :Normalisation : 2FN2FN

1.1. R est en 1FNR est en 1FN

2.2. Tout attribut non clé Tout attribut non clé de R dépend de la de R dépend de la totalité de la clétotalité de la clé

Quinio 17


R (R (Numéro élève, Numéro élève, Numéro matièreNuméro matière, note, , note, Nom élève)Nom élève)

Le Nom ne dépend F. que Le Nom ne dépend F. que du Numéro de l’élèvedu Numéro de l’élève

18

100100 005005 1212 DupontDupont

100100 001001 1616 DupontDupont

100100 002002 0808 DupontDupont

100100 003003 1010 DupontDupont

200200 004004 1212 MartinMartin

Quinio 19


R n’est pas en 2FNR n’est pas en 2FNOn normalise :On normalise :

R1 (R1 (Numéro élèveNuméro élève, Nom , Nom élève)élève)

R2 (R2 (Numéro élève,Numéro Numéro élève,Numéro matièrematière, Note), Note)

Quinio 20


1.1. R est en 2FNR est en 2FN

2.2. Tout attribut de R ne Tout attribut de R ne dépend pas de la clé dépend pas de la clé

par transitivité par transitivité

Quinio 21

Normalisation :Normalisation : 3FN3FN

R (R (Numéro professeurNuméro professeur, Nom , Nom professeur, Numéro matière, professeur, Numéro matière, Libellé matière) Libellé matière)

Libellé matière dépend Libellé matière dépend fonctionnellement de Numéro, fonctionnellement de Numéro, professeur par transitivité,à professeur par transitivité,à travers le Numéro matièretravers le Numéro matière

22

100100 DuvalDuval 001001 AnglaisAnglais

101101 FarencFarenc 001001 AnglaisAnglais

102102 GervaisGervais 001001 AnglaisAnglais

103103 JustinJustin 001001 AnglaisAnglais

104104 LoliéeLoliée 002002 EspagnolEspagnol

Quinio 23

On normalise en 3 FN

R1 (Numéro professeur, Nom professeur, numéro matière)

R2 (Numéro matière, libellé matière)

Quinio 24

Normalisation Normalisation 4FN et 5 FN4FN et 5 FN

Non traitéesNon traitées

Quinio 25

NORMALISATION : préparation

NumNumééro ro ééllèève = Ave = A Nom Nom ééllèève = Bve = B PrPréénom nom ééllèève = Cve = C Date naissance = DDate naissance = D NumNumééro matiro matièère = Ere = E LibellLibelléé = F = F Note = GNote = G NumNumééro professeur = Hro professeur = H Nom professeur = INom professeur = I PrPréénom professeur = Jnom professeur = J NumNumééro classe = Kro classe = K Niveau = LNiveau = L

A A DFDF B, C, D, K B, C, D, K E E DFDF F F AE AE DF GDF G H H DFDF I, J, E I, J, E K K DFDF L L

Quinio 26

NORMALISATION : exécution

A A DFDF B, C, D, K B, C, D, K E E DFDF F F AE AE DF GDF G H H DFDF I, J, E I, J, E K K DFDF L L

1) DF Elève (Elève (AA, B, C, D, K), B, C, D, K) Matière (Matière (EE, F), F) Apprend (Apprend (A, E,A, E, G)G) Professeur (Professeur (H,H, I, J, E) I, J, E) Classe (Classe (K,K, L) L)

Quinio 27

Création & manipulation des BDD : Création & manipulation des BDD : requêtes SQLrequêtes SQL

• « « SStructured tructured QQuery uery LLanguage » 1986anguage » 1986

• Opérateurs de l’algèbre Opérateurs de l’algèbre relationnelle relationnelle

• Utilisé dans toutes les BDUtilisé dans toutes les BD

Quinio 28

« LDD » : « LDD » : langage de définition des donnéeslangage de définition des données

CREATE TABLE élèveCREATE TABLE élève

(numéroélève NUM(6) [ UNIQUE] (numéroélève NUM(6) [ UNIQUE] [NOT NULL] [PRIMARY KEY], [NOT NULL] [PRIMARY KEY], nomélève CHAR(30)[NOT nomélève CHAR(30)[NOT NULL], prénomélève CHAR(20) NULL], prénomélève CHAR(20) [NOT NULL],annaissance [NOT NULL],annaissance NUM(4)…)NUM(4)…)

Quinio 29

ALTER TABLE matière ALTER TABLE matière

ADD COLUMN coefficient ADD COLUMN coefficient NUM(1)NUM(1)

DROP TABLE apprendDROP TABLE apprendRENAME apprend to notationRENAME apprend to notation

« LDD » « LDD »

30

ELEVE


MATIERE

Numéro matièreLibellécoefficient

PROFESSEUR


APPREND

ENSEIGNE

1-1

0-N

1-N

0-NNOTE

INTERVIENT dans

CLASSE


EST AFFECTE à

1-1

1-N

1-N

0-N

ALTER

31

ELEVE


MATIERE


PROFESSEUR


ENSEIGNE

1-1

0-N

1-N

INTERVIENT dans

CLASSE


EST AFFECTE à

1-1

1-N

0-N

DROP

32

ELEVE


MATIERE


PROFESSEUR


NOTATION

ENSEIGNE

1-1

0-N

1-N

0-NNOTE

INTERVIENT dans

CLASSE


EST AFFECTE à

1-1

1-N

1-N

0-N

RENAME

Quinio 33

« Les autorisations d’accès» : « Les autorisations d’accès» :

GRANT & REVOKEGRANT & REVOKEGRANT privilège ON objet TO public

Qui (public) a le droit de Qui (public) a le droit de faire quoi (privilège) faire quoi (privilège)

à quoi (objet)à quoi (objet)

Quinio 34

GRANT INSERT ON apprend TO professeur

GRANT SELECT ON apprend TO PUBLIC

REVOKE ALL ON apprend FROM PUBLIC

« Les autorisations d’accès» :« Les autorisations d’accès» :

Quinio 35

« LMD » : langage de manipulation « LMD » : langage de manipulation des donnéesdes données

SELECTSELECTINSERTINSERTUPDATEUPDATEDELETEDELETE

Quinio 36

SELECT SELECT

nomélève, prénomélèvenomélève, prénomélève

FROM élèveFROM élève

WHERE annaissance = 1994WHERE annaissance = 1994

ORDER BY nomélève ORDER BY nomélève

SELECTSELECT

Quinio 37

SELECT SELECT nomélève, prénomélèvenomélève, prénomélèveFROM élève, apprendFROM élève, apprendWHERE annaissance = 1994WHERE annaissance = 1994AND élève.numéro-élève = AND élève.numéro-élève =

apprend.numéro-élèveapprend.numéro-élèveAND note NOT NULLAND note NOT NULL ORDER BY nomélève ORDER BY nomélève

SELECT avec jointureSELECT avec jointure

Quinio 38

INSERTINSERT

INSERT INTO élève INSERT INTO élève (numéroélève, nomélève, (numéroélève, nomélève, prénomélève, annaissance)prénomélève, annaissance)

VALUES (105, durant, alexis, VALUES (105, durant, alexis, 2000)2000)

Quinio 39

UPDATEUPDATE

UPDATE apprendUPDATE apprend

SET note = 14SET note = 14

WHERE numéroélève = 106 WHERE numéroélève = 106

AND numéromatière = 5AND numéromatière = 5

Quinio 40

DELETEDELETE

DELETE FROM DELETE FROM élève, apprendélève, apprendWHERE WHERE numéroélève = 78numéroélève = 78

Quinio 41

Les agrégats : SUMLes agrégats : SUM

SELECT SUM (montant) SELECT SUM (montant)

FROM commande FROM commande

GROUP BY numéro-clientGROUP BY numéro-client

Quinio 42

AVGAVG

SELECT nom-élève SELECT nom-élève FROM élèveFROM élèveSELECT AVG (note) SELECT AVG (note) FROM apprendFROM apprendGROUP BY numéro-élèveGROUP BY numéro-élève

Quinio 43

MIN ou MAXMIN ou MAX

SELECT MAX(note) SELECT MAX(note)

FROM apprendFROM apprend

WHERE numéro-élève = 105WHERE numéro-élève = 105

Quinio 44

COUNTCOUNT

SELECT COUNT SELECT COUNT

FROM apprendFROM apprend

SELECT COUNT FROM SELECT COUNT FROM apprendapprend

WHERE numéro-élève = 102WHERE numéro-élève = 102

Documents

Quinio1 Bases de données : modèlisation et SGBD Séance 3 B Quinio