evry, 29-31 mai 2000© int 1 programme introduction aux bd et aux sgbd +le modèle relationnel le...

Evry, 29-31 mai 2000© INT 1

Programme

• Introduction aux BD et aux SGBDLe modèle relationnel

• Le langage de requête SQL

• La conception d’une BD relationnelle

• Protection des informations

• Perspectives des BD

Evry, 29-31 mai 2000© INT 2

Le modèle relationnel

• Inventé par E. F. Codd

• Laboratoire de recherche IBM à San José

• Publication ACM 1970 "A Relational Model of Data for Large Shared Data Banks"

Evry, 29-31 mai 2000© INT 3

Les concepts descriptifs

• Notion de domaine • Produit cartésien • Relation• Attribut• Exemples de relations

• Clé• Schéma de relation• Clé étrangère• Métabase• Résumé des notions

Evry, 29-31 mai 2000© INT 4

Notion de domaine • Définition

– Ensemble de valeurs

• Exemples– Entier, réel, chaîne de caractères, booléen

– Salaire = 4000...100000

– Couleur = {bleu, blanc, rouge}

– Point = [X: entier, Y: entier]

– Triangle = [P1, P2, P3: Point]

– Polygone = {Point}

Evry, 29-31 mai 2000© INT 5

Produit cartésien

• Définition– Le produit cartésien de D1, ...., Dn est

l'ensemble des n-uplets (Tuples) <V1, ...., Vn> tel que Vi Di.

• Notation– D1 X ....X Dn

Evry, 29-31 mai 2000© INT 6

Produit cartésien (suite)

• Exemple– D1 = {Cadre, Ouvrier, Directeur}– D2 = {5000, 15000, 30000}

D1 X D2 D1 D2

Cadre 5000Cadre 15000Cadre 30000Ouvrier 5000Ouvrier 15000Ouvrier 30000Directeur 5000Directeur 15000Directeur 30000

Evry, 29-31 mai 2000© INT 7

Relation

• Définition– sous-ensemble du produit cartésien d'une liste

de domaines– caractérisée par un nom.

• Exemple– D1 = Fonction– D2 = Salaire

Salaire_moyen D1 D2

Cadre 15000Ouvrier 5000Directeur 30000

Evry, 29-31 mai 2000© INT 8

Relation (suite)

• Plus simplement, une relation est un tableau à deux dimensions.

• Une ligne est un n-uplet (tuple).

• On associe un nom à chaque colonne afin de la repérer indépendamment de l'ordre.

Evry, 29-31 mai 2000© INT 9

Attribut

• Définition– nom donné à une colonne d'une relation– prend ses valeurs dans un domaine

• Exemple– Fonction

Evry, 29-31 mai 2000© INT 10

Exemples de relations

Salaire_moyen D1 D2

Cadre 15000Ouvrier 5000Directeur 30000

Départements Nom Région Directeur Nb_employéR&D Ile de France Le Blanc 100DRH Ile de France Dupont 300R&D Sarthe Le Bihan 100R&D Ile de France Lajoie 200Production Sarthe Le Bihan 250

Evry, 29-31 mai 2000© INT 11

Clé• Définition

– Une clé est un groupe d'attributs minimum qui détermine un n-uplet unique dans une relation (à tout instant).

• Exemple– Clé de salaire_moyen ?– Clé de départements ?

• Contrainte d'intégrité– Toute relation doit posséder une clé renseignée (sans

valeur inconnue).

Evry, 29-31 mai 2000© INT 12

Schéma de relation• Définition

– Le schéma d'une relation décrit:• son nom• la liste des attributs qu'elle comporte et des domaines

associés• la liste des attributs composant la clé (la clé est soulignée)

• Exemple– Départements (Nom: texte, Région: texte, Directeur:

texte, Nb_employés: entier)

Evry, 29-31 mai 2000© INT 13

Schéma de relation (suite)

• Intention– Un schéma de relation: intention de la relation– table: extension– Le schéma d'une BD relationnelle: ensemble

des schémas des relations

Evry, 29-31 mai 2000© INT 14

Clé étrangère

• Définition– Une clé étrangère est un groupe d'attributs qui

apparaît comme clé dans une autre relation– R1(A1, A2, .... , Ap, Ap+1, ...., An)

– R2(B1, B2, ......, Bn)

• Rôle– Les clés étrangères définissent des contraintes

d'intégrités référentielles entre relations

Evry, 29-31 mai 2000© INT 15

Mises à jour et clés étrangères•

– Insertion: la valeur des attributs doit exister dans la relation référencée.

• Insertion de (R&D4, 2222) ?

– Suppression dans la relation référencée, les n-uplets référençant doivent disparaître.

• Suppression de l'employé 5678

– Les clés étrangères définissent les liens entités-associations.

Département No_dept Directeur Employé No NomR&D1 1234 1234 Le BlancR&D2 5678 5678 Le BihanR&D3 4545 4545 LajoieProd 5678 1111 DupontDRH 1111

Evry, 29-31 mai 2000© INT 16

Clé étrangère• Exemples

– Département(No_départ, Libellé, Région)– Employé(No_emp, Nom, Prénom, Adresse)– Travaille_dans(No_dept, No_emp, Date_embauche)– Clés étrangères:

• No_dept dans Travaille_dans référence No_départ dans Département;

• No_emp dans Travaille_dans référence No_emp dans Employé.

Evry, 29-31 mai 2000© INT 17

Métabase• Définition

– base de données contenant l'ensemble des schémas et des règles de correspondances associées à une base de données

• Principe– Une base décrivant les autres bases, c'est-à-dire:

• les relations• + les attributs• + les domaines• + les clés .....

– Notion de dictionnaire de données

– Base particulière, système, gérée par l'administrateur de BD

Evry, 29-31 mai 2000© INT 18

Résumé des notions

Evry, 29-31 mai 2000© INT 19

Les opérateurs de manipulation• Tout résultat d'une opération est une relation;

celui-ci peut donc être réutilisé en entrée d'un nouvel opérateur.

• Les opérateurs peuvent être classifiés en :– opérateurs ensemblistes / opérateurs relationnels– opérateurs unaires / opérateurs binaires– opérateurs de base / opérateurs dérivés

• Base: union, différence, restriction, projection, produit cartésien

• dérivés: intersection, jointure, division

Evry, 29-31 mai 2000© INT 20

Union

• But– Permet de fusionner deux relations

• Contraintes– Binaire– Même schéma

Evry, 29-31 mai 2000© INT 21

Union (suite)

• ExempleEmployé No_emp Nom Embauche No_emp Nom

1234 Le Blanc 2222 Martin4545 Lajoie 3333 Augier5678 Le Bihan1111 Dupont

Employé No_emp Nom1234 Le Blanc4545 Lajoie5678 Le Bihan1111 Dupont2222 Martin3333 Augier

Evry, 29-31 mai 2000© INT 22

Union (suite)

• Notation– Notation textuelle: T = R S– Notation graphique: R S

T

Evry, 29-31 mai 2000© INT 23

Différence

• But– Conserver les tuples d'une relation ne figurant

pas dans une autre

• Contraintes– Binaire– Même schéma– Non commutatif

Evry, 29-31 mai 2000© INT 24

Différence (suite)

• ExempleEmployé No_emp Nom Licenciement No_emp Nom

1234 Le Blanc 1111 Dupont4545 Lajoie5678 Le Bihan2222 Martin3333 Augier1111 Dupont

Employé No_emp Nom1234 Le Blanc4545 Lajoie5678 Le Bihan2222 Martin3333 Augier

Evry, 29-31 mai 2000© INT 25

Différence (suite)

• Notation– Notation textuelle: T = R - S– Notation graphique: R S

T

Evry, 29-31 mai 2000© INT 26

Restriction

• But– Permet de "sélectionner" des tuples– La restriction réduit la taille de la relation

verticalement

• Contraintes– Unaire– Spécifier une condition

Evry, 29-31 mai 2000© INT 27

Restriction (suite)

• Exemple

• Restriction telle que salaire 15000

Employé No_emp Nom Salaire1234 Le Blanc 150004545 Lajoie 140005678 Le Bihan 160002222 Martin 140003333 Augier 16000

• Restriction telle que Nom > 'Le Blanc'

Employé No_emp Nom Salaire1234 Le Blanc 150004545 Lajoie 140002222 Martin 14000

Employé No_emp Nom Salaire2222 Martin 14000

Evry, 29-31 mai 2000© INT 28

Restriction (suite)

• Notation– Notation textuelle: T = cond (R)

– Notation graphique: R

Cond

T

Evry, 29-31 mai 2000© INT 29

Projection

• But– Permet de "sélectionner" des attributs– La projection réduit la taille de la relation

horizontalement

• Contraintes– Unaire– Spécifier une liste d'attributs

Evry, 29-31 mai 2000© INT 30

Projection (suite)

• Exemple

• Projection sur No_emp

Employé No_emp Nom Salaire1234 Le Blanc 150004545 Lajoie 140005678 Le Bihan 160002222 Martin 140003333 Augier 15000

• Projection sur Nom

• Projection sur No_emp et salaire

Employé No_emp12344545567822223333

Employé NomLe BlancLajoieLe BihanMartinAugier

Employé No_emp Salaire1234 150004545 140005678 160002222 140003333 15000

Evry, 29-31 mai 2000© INT 31

Projection (suite)

• Notation– Notation textuelle: T = liste d'attributs (R)

– Notation graphique: R

Attributs

T

Evry, 29-31 mai 2000© INT 32

Produit cartésien• But

– Ensemble de tous les tuples obtenus par concaténation de chaque tuple de R avec chaque tuple de S

– Relation X Relation Relation

• Contraintes– Binaire– Schéma du résultat:

• R(a1, a2, ...., an), S(b1, b2, ..., bp)• T = R X S T(a1, a2, ...., an, b1, b2, ..., bp)

– Card (R X S) = Card (R) * Card (S)

Evry, 29-31 mai 2000© INT 33


• Exemple

Employé No_emp Nom Salaire Département No_dept1234 Le Blanc 15000 R&D15678 Le Bihan 16000 R&D22222 Martin 140003333 Augier 15000

Employé X Département No_emp Nom Salaire No_dept1234 Le Blanc 15000 R&D11234 Le Blanc 15000 R&D25678 Le Bihan 16000 R&D15678 Le Bihan 16000 R&D22222 Martin 14000 R&D12222 Martin 14000 R&D23333 Augier 15000 R&D13333 Augier 15000 R&D2

Evry, 29-31 mai 2000© INT 34


• Notation– Notation textuelle: T = R X S– Notation graphique: R S

X

T

Evry, 29-31 mai 2000© INT 35

Intersection

• But– Permet d’obtenir l’ensemble des tuples

appartenant à deux relations

• Contraintes– Binaire– Même schéma

Evry, 29-31 mai 2000© INT 36

Intersection (suite)

• Exemple

Directeur R&D No_emp Nom Directeur Prod No_emp Nom1234 Le Blanc 5678 Le Bihan4545 Lajoie5678 Le Bihan1111 Dupont

Directeurs R&D et Prod No_emp Nom5678 Le Bihan

Evry, 29-31 mai 2000© INT 37

Intersection (suite)

• Notation– Notation textuelle: T = R S– Notation graphique: R S

T

• Dérivation– T = R - (R - S)– T = S - (S - R)

Evry, 29-31 mai 2000© INT 38

Jointure

• But– Permet d’établir le lien sémantique entre les

relations

• Contraintes– Binaire– Schéma du résultat:

• R(a1, a2, ...., an)• S(b1, b2, ..., bp)• T = R X S T(a1, a2, ...., an, b1, b2, ..., bp)

Evry, 29-31 mai 2000© INT 39

Jointure (suite)

• ExempleDépartement No_dept Directeur Employé No Nom

R&D1 1234 1234 Le BlancR&D2 5678 4545 LajoieR&D3 4545 5678 Le BihanProd 5678 1111 DupontDRH 1111 2222 Martin

3333 Augier

Département No_dept Directeur No NomR&D1 1234 1234 Le BlancR&D2 5678 5678 Le BihanR&D3 4545 4545 LajoieProd 5678 5678 Le BihanDRH 1111 1111 Dupont

Evry, 29-31 mai 2000© INT 40

Jointure (suite)• Notation

– Notation textuelle: T = R >< S Critère de jointure

– Notation graphique : R S

Critère de jointure

T

• Dérivation– Produit cartésien + restriction

Evry, 29-31 mai 2000© INT 41

Division• But

– Répondre aux requêtes de type « tous les »

– Un tuple t est dans T si et seulement si pour tout tuple s de S, le tuple <t,s> est dans R

• Contraintes – Binaire

– Schéma du résultat:• R(a1, a2, ...., an, b1, b2, ..., bp)• S(b1, b2, ..., bp)• T = R ¸ S T(a1, a2, ...., an)

Evry, 29-31 mai 2000© INT 42

Division (suite)

• Exemple

– Quels sont les employés qui travaillent sur tous les projets ?

Projet No_proj No_empP2 1234P1 5678P5 5678P3 1111P1 1234P1 1111P3 5678

Evry, 29-31 mai 2000© INT 43

Division (suite)

• Exemple– Construire R: ensemble de toutes les

informations dont on a besoin– Construire S: ensemble correspondant à "tous

les" (projets)R = Projet No_proj No_emp S No_projetP2 1234 P1P1 5678 P2P5 5678 P3P3 1111 P5P1 1234P1 1111P3 5678

T No_emp5678

Evry, 29-31 mai 2000© INT 44

Division (suite)• Notation

– Notation textuelle: T = R S– Notation graphique: R S

T• Dérivation

– Projection + Produit cartésien + Différence.– R S = T1 - T2 avec:

• T1 = schéma(R) - schéma(S) (R)• T2 = schéma(R) - schéma(S) (( schéma(R) - schéma(S) (R) X (S) - R)

Evry, 29-31 mai 2000© INT 45

Bilan

Opérateurs Unaire/Binaire Contraintes Notations

Union Binaire Même schéma R S

Différence Binaire Même schéma R - S

Restriction Unaire Condition cond (R)

Projection Unaire Liste attributs liste d'attributs (R)

Produit cartésien Binaire Schéma résultat R X S

Intersection Binaire Même schéma R S

Jointure Binaire Schéma résultat R S critère

Division Binaire Schémas R S

Evry, 29-31 mai 2000© INT 46

Exemples de requêtes en algèbre relationnelle

• Base de données– Employé(Nom_emp, Prénom_emp, No_ss,

Date_naiss, Adresse_emp, Sexe, Salaire, No_chef, Nod)

– Département(Nom_d, No_dept, No_dir, Date_affect)

– Dept_local(Num_dept, Id_loc)– Projet(Nom, No_proj, Local_proj, Nod)– Travaille_sur(No_ss, No_projet, Nb_heures)

Evry, 29-31 mai 2000© INT 47

Requête 1

• Nom et adresse de tous les employés travaillant pour le département "R&D"

• Solution textuelle– DEPT_RECH <- Nom_d="R&D" (Département)– EMP_DE_RECH <- DEPT_RECH ><Employé

No_dept = Nod

– RESULTAT <- Nom_emp, Prénom_emp, Adresse_emp (EMP_DE_RECH)

Evry, 29-31 mai 2000© INT 49

Requête 2• Pour chaque projet situé à Paris, donner le numéro du

projet, le numéro du département correspondant, et le nom, l'adresse et la date de naissance du directeur

• Solution textuelle– PROJ_PARIS <- Local_proj="Paris" (Projet)

– DEPT_DE_PROJ_PARIS <- PROJ_PARIS >< Département

Nod= No_dept– DIR_DE_PROJ_PARIS <- DEPT_DE_PROJ_PARIS >< Employé

No_dir= No_ss

– RESULTAT <- No_proj, No_dept, Nom_emp, Adresse_emp, Date_naiss (DIR_DE_PROJ_PARIS)

Evry, 29-31 mai 2000© INT 51

Requête 3• Donner le nom des employés qui travaillent

sur tous les projets du departement

• Solution textuelle– PROJ_DE_DEPT5 <- No_proj ( Nod=5 (Projet))

– EMP_PROJ <- No_ss, No_projet(Travaille_sur)

– EMP_PROJETS_DEPT5 <- EMP_PROJ PROJ_DE_DEPT5

– RESULTAT <- Nom_emp (EMP_PROJETS_DEPT5 >< Employé)

No_ss = No_ss

Evry, 29-31 mai 2000© INT 53

Requête 4• Donner la liste des numéro de projets sur lesquels

travaille "Karl", soit en tant que directeur soit en tant qu'exécutant

• Solution textuelle– EMP_KARL <- Prénom_emp="Karl" (Employé)

– TRAVAILLEUR_KARL No_projet (EMP_KARL >< Travaille_sur) No_ss = No_ss– DEPT_DIRIGES_KARL No_dept (EMP_KARL >< Département) No_ss = No_dir

– PROJ_DIR_KARL <- No_projet (DEPT_DIRIGES_KARL >< Projets) No_dept = No_d– RESULTAT <- TRAVAILLEUR_KARL PROJ_DIR_KARL

Evry, 29-31 mai 2000© INT 55

Requête 5

• Donner le nom des employés qui ne travaillent sur aucun projet

• Solution textuelle– TOUS_EMP <- No_ss (Employé)

– EMP_SUR_PROJ <- No_ss (Travaille_sur)

– EMP_SANS_PROJ <- TOUS_EMP - EMP_SUR_PROJ

– RESULTAT <- Nom_emp (EMP_SANS_PROJ >< Employé) No_ss = No_ss

Evry, 29-31 mai 2000© INT 57

Requête 6

• Donner le nom des directeurs qui travaillent au moins sur un projet

• Solution textuelle– DIREC <- No_dir (Département)

– TRAV <- No_ss (Travaille_sur)

– RESULTAT <- Nom_emp ((DIREC TRAV) >< Employé) No_ss = No_ss

Evry, 29-31 mai 2000© INT 59

Optimisation de requêtes

• On peut répondre de diverses façon à une même requête

• Optimiser = choisir la meilleure façon

Evry, 29-31 mai 2000© INT 61

Facteurs déterminants

• Ordre d'exécution des opérations algébriques

• Algorithme implantant les opérations algébriques

• Placement des données sur le disque

• Taille des relations intermédiaires

Evry, 29-31 mai 2000© INT 62

Restructuration• Règles

– Exécuter les opérateurs unaires le plus tôt possible (restriction, projection) => diminution de la taille des relations

– Exécuter les jointures le plus tard possible

• Propriétés– Associativité des jointures– Commutativité restriction / projection– Commutativité restriction / jointure– Commutativité projection / jointure

evry, 29-31 mai 2000© int 1 programme introduction aux bd et aux sgbd +le modèle relationnel le...

Documents