Upload
annora
View
25
Download
2
Embed Size (px)
DESCRIPTION
Samir Chouali [email protected]. Bases de Données Relationnelles. (à partir du cours de G.Cécé ). - 1 -. Bibliographie. « Conception et architecture des Bases de Données», R. Elmasri, S. Navathe et D. Serain, Pearson Education, 2004. - PowerPoint PPT Presentation
Citation preview
- 1 -
Bases de Données Relationnelles
Samir Chouali [email protected]
(à partir du cours de G.Cécé )
- 2 -
Bibliographie « Conception et architecture des Bases de Données», R.
Elmasri, S. Navathe et D. Serain, Pearson Education, 2004.
« Des Bases de Données à l'Internet », P. Mathieu, Vuibert, 2000.http://www.lifl.fr/~mathieu/bdd
« Bases de Données Objet & Relationnel »,G. Gardarin, Eyrolles, 1999.
- 3 -
Plan Généralités
Définitions Propriétés des SGBD
Modélisation Le Modèle Conceptuel de Données (MCD) Le Modèle Relationnel (MR) Passage du MCD au Modèle Relationnel
Contraintes d’intégrité Algèbre relationnelle Langage de requêtes SQL Normalisation des relations
- 4 -
Définitions Base de données :
Ensemble de données qui modélisent une partie du monde réel pour une application informatique.
Système de Gestion de Base de Données (SGBD) : Outil qui permet d’insérer, modifier, retirer et rechercher des
données ; le tout de façon efficace. Interface entre les utilisateurs et l’information brute Présente les informations dans une forme exploitable
- 5 -
Les Trois Couches d’un SGBD Système de gestion de fichiers :
gère le stockage physique des informations (dépend du matériel). SGBD interne :
assemble et place les données, gère les liens entre les données et y garantit un accès rapide.
SGBD externe : s’occupe de la présentation et de la manipulation des données. Permet
l’utilisation de langages de requêtes élaborés et d’outils de présentation adaptés.
- 6 -
Propriétés des SGBD (1) Indépendance physique
transparence de la gestion des données au niveau physique. Indépendance logique
chacun possède sa propre vue des données. Manipulable par des non informaticiens
utilisation de langages non procéduraux (pas de programmation).
- 7 -
Propriétés des SGBD (2) Accès aux données efficaces
optimisation des accès aux données. Administration centralisées des données
outils de sauvegarde des données, de réplication, ... Non redondance des données.
évite la duplication des informations ce qui facilite la gestion. Cohérence des données
gestion automatique des contraintes d’intégrité.
- 8 -
Propriétés des SGBD (3) Partage des données
plusieurs personnes peuvent accéder aux données simultanément tout en conservant l’intégrité de la base.
Sécurité des données protection contre les accès non autorisés. tolérance aux pannes.
- 9 -
Types de Bases de Données (1) Les bases hiérarchiques
Les bases réseaux (CODASYL)
Les bases relationnelles données sous formes de tables basées sur l’algèbre relationnelle et un
langage, de manipulation, déclaratif (SQL). Les bases déductives
données sous formes de tables (prédicats), le langage d’interrogation est basé sur le calcul des prédicats et la logique du premier ordre.
- 10 -
Types de Bases de Données (2) Les bases objets
données représentées sous forme d’instances de classes hiérarchisées.
75% des SGBD sont des bases relationnelles
Les bases objets gagnent du terrain
source: www.gartner.com Mai 2004
4. Le marché des SGBD Marché en 2003 : 7 milliards de dollars Aujourd’hui 3 leaders : IBM, Oracle, Microsoft
Parts de marché 2003
IBM
Oracle
Microsoft
NCR
Informix
Autres
- 12 -
Modélisation
Modèle Conceptuel de Données (MCD)
- 13 -
Modélisation
Réalité perçue
Modélisation conceptuelle
Transformation dans un modèle supporté par un SGBD
Modèle entité association
Définition de la structure de données de la base
Modèle relationnel
SQL
- 14 -
Modélisation Le résultat de l’analyse est le Modèle Conceptuel de Données
(MCD) qui décrit la future base de données à l’aide d’entités et d’associations.
Employé
Numéro d’employéNomPrénomDate d’embaucheFonctionRémunération
Tâche
Nom de la tâcheCoût de la tâche
participeDate débutDate fin
0,n 1,n
- 15 -
Vocabulaire (1) Entité :
représentation d’un objet, matériel ou immatériel(ex. : Étudiant, Voiture, Vin, etc...).
une entité est composée de propriétés.
Propriété : donnée élémentaire et indécomposable
(ex. : age, note, nom, adresse, date de naissance, etc...).
- 16 -
Vocabulaire (2) Association
représentation d’un lien entre différentes entités. des propriétés peuvent être attachées à une association.
Dimension nombre d’entités intervenants dans l’association
(1 : association réflexive; 2 : association binaire; n : association n-aire)
Cardinalité caractérise le lien entre une entité et une association. Elle est
constituée d’une borne minimale et d’une borne maximale.
- 17 -
Vocabulaire (3) Cardinalité (suite)
Nombre de fois qu’une occurrence de l’entité participe aux occurrences de l’association.
Identifiant une ou plusieurs propriétés d’une entité telles qu’à chaque valeur de
l’identifiant correspond une et une seule occurrence de l’entité. l’identifiant d’une association est constitué de la réunion des
identifiants des entités qui participent à l’association.
- 18 -
Exemple de MCD
Employé
Numéro d’employéNomPrénomDate d’embaucheFonctionRémunération
Projet
Numéro du projetThème du projetTitre du projetDate de débutDate de fin
encadre
Tâche
Nom de la tâcheCoût de la tâche
coordonne
Constitué_departicipeDate débutDate fin
0,1 0,n
0,n
0,n
1,n
1,1
1,1
1,n
a pour chef
est chef de
Modèle Conceptuel des Données
• Exemple "KaafKaaf"– PARTIE 1– La société "KaafKaaf" désire
informatiser son système de facturation. Les factures devraient se présenter de la façon suivante
– Créez un MCD, qui permet de modéliser correctement le système d'information nécessaire, sachant que:
– Un client peut bien sûr recevoir plusieurs factures, mais il est uniquement considéré comme tel à partir du moment où il reçoit sa première facture.
– Une facture concerne un et un seul client.
Modèle Conceptuel des Données
• Remarque:– Bien que le numéro du client n'apparaisse pas en tant que tel sur la
facture, il est préférable d'ajouter cette propriété artificielle à l'entité Client, et de la définir comme identifiant de cette entité. Cela nous empêche de devoir définir un identifiant composé de trop de propriétés.
Modèle Conceptuel des Données
• PARTIE 2– Il s'agit d'étendre le MCD de la partie 1. – Le responsable de la facturation de la
société désire rendre les factures plus informatives. Comme un client peut acheter plusieurs articles différents en même temps, la facture devrait indiquer pour chaque article le numéro , un libellé, le prix unitaire, la quantité vendue et le prix total pour ce type d'article.
– Voici l'aspect que la facture devrait avoir:
– Proposez un nouveau MCD qui reflète ces modifications, en respectant que:
– Tous les articles disponibles sont stockés (p.ex. No=234 Libellé="Marteau" PU=470 Luf.). Même si un article n'est pas encore considéré par une facture, il existe dans le système d'information.
Modèle Conceptuel des Données
• Remarques:– L'entité Facture ne contient plus la propriété Montant. Il existe une règle générale de conception qui
dit:• Aucune propriété qui peut être calculée à partir d'autres propriétés existantes, ne
devra être stockée dans le MCD.– Pour la même raison, on n'a pas besoin de modéliser explicitement le prix à payer pour l'achat d'une
quantité d'articles donnés. Le prix pour chaque article figurant sur la facture peut être calculé à partir du prix unitaire et de la quantité
Modèle Conceptuel des Données
• Exemple "Gestion d'école"– PARTIE 1– Dans une école, on veut informatiser le système d'information qui gère les classes.– Elaborez un MCD sachant que:– Un élève est caractérisé par son no. matricule, son nom et prénom, ainsi que sa date de naissance.– Une classe est caractérisée par le nom de la classe et par une indication du cycle.– Il faudra prévoir de connaître la fréquentation des classes des élèves sur plusieurs années
consécutives. – Un élève enregistré dans le système fréquente au moins une classe au cours des années.
Modèle Conceptuel des Données• PARTIE 2• Il s'agit maintenant de concevoir une extension
au MCD précédent qui permet de représenter la situation suivante:
• La direction de l'école désire également saisir tous les professeurs dans le système d'information. Un professeur est caractérisé par un code interne unique , son nom et prénom et la matière qu'il enseigne. Nous supposons que chaque professeur enseigne une seule matière.
• Modélisez le fait que chaque classe est enseignée chaque année par un ou plusieurs enseignants. Un enseignant peut bien sûr donner des cours dans plusieurs classes, mais peut également ne pas donner des cours pendant une ou plusieurs années.
- 25 -
Exercices Exercice 1 Un magasin de sport a besoin de stocker ses informations
principales dans une base de données. Il s'agit de stocker toutes les informations relatives à ses produits, ses clients et ses fournisseurs. Il souhaite que l'organisation soit facilitée par le regroupement des produits en différentes catégories. Et il souhaite à partir de là pouvoir facilement consulter ses stocks, la liste de ses meilleurs clients, le top 10 de ses produits vendus.Créer le MCD correspondant.
- 26 -
Exercices Exercice 2 Une agence immobilière a besoin d'une base de données
pour gérer ses locations. Il faut entre autres qu'elle soit capable de cibler les logements pouvant convenir à un client donné. Elle souhaite également pouvoir facilement dresser la facture d'un client donné, savoir ce qu'elle doit verser à un propriétaire donné, ou calculer les primes de ses agents.Créer le MCD correspondant.
- 27 -
Le Modèle Relationnel
- 28 -
Modèle Relationnel
Les SGBD relationnels organisent les données en tables sont basés sur l’algèbre relationnelle
(théorie des ensembles).
- 29 -
ExempleNoCours Intitule
1 Supervision2 Base de données3 Introduction Réseaux
NoProf Nom 1 Cécé 2 Bourgeois
NoProf NoCours VolumeHoraire1 2 7.51 3 92 1 6
Nom Prof Intitule Cours VolumeHoraireCécé Base de données 7.5Bourgeois Supervision 6Cécé Introduction Réseaux 9
- 30 -
Domaines
Exemples ENTIER REEL CHAINES DE CARACTERES
SALAIRE = {4 000..100 000} COULEUR= {BLEU, BLANC, ROUGE}
POINT = {(X:REEL,Y:REEL)} TRIANGLE = {(P1:POINT, P2:POINT, P3:POINT)}
- 31 -
Produit Cartésien
Bleu VraiBleu FauxBlanc VraiBlanc FauxRouge VraiRouge Faux
Le produit cartésien D1x D2x ... x Dn est l'ensemble des tuples (n-uplets) :
<V1,V2, …, Vn> tel que Vi Di
Exemple D1 = {Bleu, Blanc, Rouge} D2 = {Vrai, Faux}
- 32 -
Relation (ou Table)
Bleu Faux
Blanc Vrai
Rouge Vrai
CoulVins Coul Choix
Sous-ensemble du produit cartésien d'une liste de domaines
Une relation est caractérisée par un nom
Exemple D1 = COULEUR D2 = BOOLEEN
- 33 -
Exemple de Relation
VINS CRU MILL REGION COULEUR
CHENAS 1983 BEAUJOLAIS ROUGETOKAY 1980 ALSACE BLANCTAVEL 1986 RHONE ROSECHABLIS 1986 BOURGOGNE BLANCST-EMILION 1987 BORDELAIS ROUGE
- 34 -
Attribut Vision tabulaire du relationnel
Une relation est une table à deux dimensions Une ligne est un tuple Un nom est associé à chaque colonne afin de la repérer autrement que
par sa position
Attribut nom donné à une colonne d'une relation prend ses valeurs dans un domaine
- 35 -
Clé
Groupe d'attributs minimum qui détermine un tuple unique dans une relation
Exemples {CRU,MILLESIME} dans VINS NSS dans PERSONNE
Clé étrangère Groupe d'attributs formant la clé d’une autre relation
- 36 -
Schéma
D’une relation Nom de la relation, liste des attributs avec domaines et clés de la
relation Exemple
VINS(NV :entier, CRU :texte, MILL :entier, DEGRE :réel, REGION :texte)
Par convention, la clé primaire est soulignée Schéma d'une bd relationnelle
C’est l'ensemble des schémas des relations composantes
- 37 -
Exemple de Schéma
Exemple BUVEURS (NB, NOM, PRENOM, TYPE) VINS (NV, CRU, MILL, DEGRE) ABUS (NB, NV, DATE, QUANTITE)
Clés étrangères ABUS.NV fait référence à VINS.NV ABUS.NB fait référence à BUVEURS.NB
- 38 -
Diagramme des Liens
NB NV DATE QUANTITEABUS
NV CRU MILL. DEGREVINSNB NOM PRENOM TYPEBUVEURS
- 39 -
Vocabulaire comparé
MCD Modèle RelationnelEntité Table
Propriété Attribut, ColonneIdentifiant Clé
Association Relation
- 40 -
Du MCD vers les Tables Relationnelles
- 41 -
Transformation Traitement des entités
Chaque entité devient une table. Chaque propriété devient une colonne de cette table. L’identifiant d’une entité devient la clé primaire de la table
correspondante.
Traitement des associations Le traitement des associations dépend des cardinalités des ces
associations.
- 42 -
Association binaire
Cardinalités Traitement
(*,n) - (*,n) Création d’une nouvelle table dont la clé contientnécessairement les identifiants des deux entités.Les propriétés de l’association migrent aussi versla nouvelle table.
(*,*) - (*,1) La clé étrangère du coté (*,*) migre vers le coté(*,1). Les propriétés de l’association migrentaussi vers le coté (*,1).
- 43 -
Exemple : (*,1) - (*,*)
Vin
NoVinCruMill.Degré
Producteur
NoPNomRégion
Recolte
Quantite
1,1 1,n
NoP Nom Région1 Gerardus Monbien2 Toeuf Besancit
NoVin Cru Mill. Degre NoP* Quantite
1 Volnay 1983 12 2 15 2 Chenay 1996 12,5 1 25
De :
À :
- 44 -
Association n-aires
création d’une nouvelle table dont la clé est au moins composée des identifiants des différentes entités.
les propriétés de cette association migrent aussi vers la nouvelle table.
- 45 -
Exemple
Prof
NoProfNom
Cours
NoCoursIntitulé
EnseigneVolumeHoraire
1,n 1,n
NoCours Intitule1Supervision2Base de données3 Introduction Réseaux
NoProf Nom1Cece2Bourgeois
NoProf NoCours VolumeHoraire1 2 7.51 3 92 1 6
De :
À :
- 46 -
Exercice
Employé
Numéro d’employéNomPrénomDate d’embaucheFonctionRémunération
Projet
Numéro du projetThème du projetTitre du projetDate de débutDate de fin
encadre
Tâche
Nom de la tâcheCoût de la tâche
coordonne
Constitué_departicipeDate débutDate fin
0,1 0,n
0,n
0,n
1,n
1,1
1,1
1,n
a pour chef
est chef de
- 47 -
Contraintes
- 48 -
Contraintes d’Intégrité Une contrainte d’intégrité est une assertion (c-à-d une
propriété) qui doit être vérifiée par les données de la base
Si une contrainte d’intégrité n’est pas respectée lors d’une modification des données, la modification est rejetée et l’utilisateur est averti
- 49 -
Contraintes de Clé Tous les constituants d’une clé primaire doivent être
renseignés. Il ne peuvent prendre la valeur Null.
Deux enregistrements différents ne peuvent avoir de valeurs de clé identiques.
- 50 -
Contraintes de Types de Données Permet de spécifier le domaine de validité des valeurs des
attributs.
Exemples :une note doit être comprise entre 0 et 20.la date d’emprunt d’un livre est antérieure à sa date de retour
- 51 -
Contraintes d’Intégrité Référentielle
NB NV DATE QUANTITEABUS
NV CRU MILL. DEGREVINSNB NOM PRENOM TYPEBUVEURS
- 52 -
Contraintes d’Intégrité Référentielle Un constituant d’une clé étrangère doit limiter ses valeurs à
l’ensemble des valeurs présentes dans la table d’origine de la clé.
Si un enregistrement d’une table est supprimé, tous les enregistrements des autres tables faisant référence à cet enregistrement, à travers des clés étrangères, doivent normalement être supprimés.
- 53 -
Algèbre Relationnelle
- 54 -
Concepts Manipulatoires
Un ensemble d'opérations formelles
Ces opérations permettent d'exprimer toutes les requêtes sous forme d'expressions algébriques
Elles sont la base du langage SQL(SQL est un paraphrasage en anglais des expressions algébriques)
- 55 -
Opération ensembliste pour des relations de même schéma UNION notée INTERSECTION notée DIFFERENCE notée — ou \
Opérations Ensemblistes
- 56 -
Projection
VINS Cru Mill Région Qualité
VOLNAY BOURGOGNE
CHENAS BEAUJOLAIS
JULIENAS BEAUJOLAIS
Cru,Région(VINS) Cru Région
VOLNAY 1983 BOURGOGNE A
VOLNAY 1979 BOURGOGNE B
CHENAS 1983 BEAUJOLAIS A
JULIENAS 1986 BEAUJOLAIS C
Cru,Région
Élimination des attributs non désirés et suppression des tuples en double
notée A1,A2,...Ap (R)
- 57 -
Restriction Sélection des tuples de R satisfaisant un critère Q
notée Q(R)
Q est le critère de qualification de la forme : Ai Valeur avec : { =, <, >=, >, <=}
Il est possible de réaliser des "ou" (union) et des "et" (intersection) de critères simples
- 58 -
Exemple de Restriction
MILL>1983
VINS Cru Mill Région Qualité
VOLNAY 1983 BOURGOGNE A
VOLNAY 1979 BOURGOGNE B
CHENAS 1983 BEAUJOLAIS A
JULIENAS 1986 BEAUJOLAIS C
VINS Cru Mill Région QualitéJULIENAS 1986 BEAUJOLAIS C
CRU="VOLNAY"
CRU="CHENAS"
- 59 -
Produit Cartésien
Le produit cartésien R3 = R1 x R2 est une relation qui a pour ensemble d'attributs l'union de ceux de R1 et de ceux de R2 et pour tuples toutes les combinaisons possibles des lignes de R1 et de lignes de R2.
Soient R1 et R2 deux relations.
- 60 -
Exemple de Produit Cartésien
R A B C1 2 34 5 67 8 9
S D E3 16 2
R x S A B C D E1 2 3 3 11 2 3 6 24 5 6 3 14 5 6 6 27 8 9 3 17 8 9 6 2
- 61 -
Soient R(A1, …, An) et S(B1, …, Bm) deux relationset Q un critère impliquant les attributs Ai et Bj.
La jointure de R et de S suivant le critère Q est l’ensemble des éléments du produits cartésien RxS satisfaisant le critère Q
Elle se note : R S
Jointure
Q
- 62 -
Exemple de Jointure
R A B C1 2 34 5 67 8 9
S D E3 16 2
R S A B C D E1 2 3 3 11 2 3 6 24 5 6 6 2
B < D
- 63 -
Jointures Particulières L’équi-jointure est une jointure avec pour critère l’égalité
de certaines colonnes.
La jointure naturelle est une equi-jointure où le critère est l’égalité entre colonnes de même nom, suivie de la projection qui ne conserve qu’une colonne par nom.
- 64 -
Exemple de Jointure Naturelle
VINS Cru Mill QualitéVOLNAY 1983 A
VOLNAY 1979 B
CHABLIS 1983 A
JULIENAS 1986 C
LOCALISATION Cru Région
VOLNAY Bourgogne
CHABLIS Bourgogne
CHABLIS Californie
VINSREG Cru Mill Qualité RégionVOLNAY 1983 A Bourgogne
VOLNAY 1979 B Bourgogne
CHABLIS 1983 A Bourgogne
CHABLIS 1983 A Californie
- 65 -
Utilisation Pratique
Prof
NoProfNom
Cours
NoCoursIntitulé
EnseigneVolumeHoraire
0,n 0,n
- 66 -
Utilisation Pratique (suite)NoCours Intitule
1Supervision2Base de données3 Introduction Réseaux
NoProf Nom1 Cece2 Bourgeois
NoProf NoCours VolumeHoraire1 2 7.51 3 92 1 6
Nom Prof Intitule Cours VolumeHoraireCece Base de données 7.5Bourgeois Supervision 6Cece Introduction Réseaux 9
Projection, (Nom, Intitule, VolumeHoraire) puis renommage des colonnes :
Tables de l’exemple :
Jointure naturelle :NoProf Nom NoCours Intitule VolumeHoraire
1 Cece 2 Base de données 7.52 Bourgeois 1 Supervision 61 Cece 3 Introduction Réseaux 9
- 67 -
Structured Query Language
(SQL)
- 68 -
Définition SQL (Structured Query Language) est un langage de
définition et de manipulation de bases de données relationnelles.
- 69 -
Les Trois Niveaux DDL (Data Definition Language)
permet de créer, modifier, supprimer les tables
DML (Data Manipulation Language) permet de manipuler les données contenues dans les tables (sélection,
ajout, modification, suppression)
DCL (Data Control Language) permet de gérer les accès des utilisateurs aux tables
- 70 -
Principaux ordres SQL
DDL DML DCL
ALTERCREATECOMMENTDROPRENAME
DELETEINSERTSELECTUPDATE
GRANTREVOKE
- 71 -
Tables des exemples
pno design prix poids couleur101 fauteuil 2,000.00 F 7gris102 fauteuil 1,500.00 F 9rouge103 bureau 3,500.00 F 30vert104 bureau 4,000.00 F 40gris105 armoire 2,500.00 F 35rouge106 caison 1,000.00 F 12gris107 caison 1,000.00 F 12jaune108 classeur 1,500.00 F 20bleu
fno nom adresse ville10 Dupont Lille11 Martin Amiens12 J aquet Lyon13 Durand Lyon14 Martin Nice15 Durand Lille16 Dupont Paris17 Lefebvre Lille19 Maurice Paris
cno fno pno qute1001 17 103 101003 15 103 21005 17 102 11007 15 108 11011 19 107 121013 13 107 51017 19 105 31019 14 103 101023 10 102 81029 17 108 15
Fournisseur Produit
Commande
- 72 -
Sélection simple (1)
SELECT DISTINCT designFROM Produit;
SELECT DISTINCT designFROM ProduitWHERE prix > 2000;
designarmoirebureaucaisonclasseurfauteuil
designarmoirebureau
- 73 -
Sélection simple (2)
SELECT DISTINCT design, prixFROM Produit;
SELECT *FROM Produit;
design prixarmoire 2,500.00 EUR bureau 3,500.00 EUR bureau 4,000.00 EUR caison 1,000.00 EUR classeur 1,500.00 EUR fauteuil 1,500.00 EUR fauteuil 2,000.00 EUR
pno design prix poids couleur101fauteuil 2,000.00 F 7gris102fauteuil 1,500.00 F 9rouge103bureau 3,500.00 F 30vert104bureau 4,000.00 F 40gris105armoire 2,500.00 F 35rouge106caison 1,000.00 F 12gris107caison 1,000.00 F 12jaune108classeur 1,500.00 F 20bleu
- 74 -
Sélection simple ordonnée
SELECT DISTINCT design, couleurFROM ProduitWHERE couleur IN ("rouge", "vert")ORDER BY design DESC;
SELECT DISTINCT design AS Nom du produitFROM ProduitWHERE couleur IN ("rouge", "vert")ORDER BY design ASC;
Présentation
design couleurfauteuil rougebureau vertarmoire rouge
Nom du produitarmoirebureaufauteuil
- 75 -
Jointure Produit cartésien
Jointure « Donner toutes les informations concernant les commandes »
SELECT *
FROM Produit, Commande, Fournisseur ;
SELECT *FROM Produit, Commande, Fournisseur WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
- 76 -
Jointure Jointure (résultat)
SELECT * FROM Produit, Commande, Fournisseur WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
Produits.pno
design prix poids couleur cno commande.fno
commande.pno
qute Fournisseurs.fno
nom adresse ville
103 bureau 3,500.00 F 30 vert 1001 17 103 10 17 Lefebvre Lille103 bureau 3,500.00 F 30 vert 1003 15 103 2 15 Durand Lille102 fauteuil 1,500.00 F 9 rouge 1005 17 102 1 17 Lefebvre Lille108 classeur 1,500.00 F 20 bleu 1007 15 108 1 15 Durand Lille107 caison 1,000.00 F 12 jaune 1011 19 107 12 19 Maurice Paris107 caison 1,000.00 F 12 jaune 1013 13 107 5 13 Durand Lyon105 armoire 2,500.00 F 35 rouge 1017 19 105 3 19 Maurice Paris103 bureau 3,500.00 F 30 vert 1019 14 103 10 14 Martin Nice102 fauteuil 1,500.00 F 9 rouge 1023 10 102 8 10 Dupont Lille108 classeur 1,500.00 F 20 bleu 1029 17 108 15 17 Lefebvre Lille
- 77 -
Jointure Jointure - projection
SELECT cno, design, nom AS Nom fournisseur, qute FROM Produit, Commande, Fournisseur WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
cno design Nom fournisseur qute1001bureau Lefebvre 101003bureau Durand 21005fauteuil Lefebvre 11007classeur Durand 11011caison Maurice 121013caison Durand 51017armoire Maurice 31019bureau Martin 101023fauteuil Dupont 81029classeur Lefebvre 15
- 78 -
Calcul Jointure - projection - calcul
SELECT no, design, nom AS Nom fournisseur, prix, qute, prix*qute AS TotalFROM Produit, Commande, Fournisseur WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
cno design Nom fournisseur prix qute Total1001bureau Lefebvre 3,500.00 F 10 35,000 F1003bureau Durand 3,500.00 F 2 7,000 F1005fauteuil Lefebvre 1,500.00 F 1 1,500 F1007classeur Durand 1,500.00 F 1 1,500 F1011caison Maurice 1,000.00 F 12 12,000 F1013caison Durand 1,000.00 F 5 5,000 F1017armoire Maurice 2,500.00 F 3 7,500 F1019bureau Martin 3,500.00 F 10 35,000 F1023fauteuil Dupont 1,500.00 F 8 12,000 F1029classeur Lefebvre 1,500.00 F 15 22,500 F
- 79 -
Sous requête Question
liste des numéros de fournisseurs livrant au moins un produit en quantité supérieure à chacun des produits livrés par le fournisseur 19
SELECT fno FROM Commande WHERE qute > ALL (SELECT qute FROM Commande WHERE fno = 19)
fno17
- 80 -
Fonctions statistiques
AVG Moyenne
COUNT Nombre d’éléments
MAX Maximum
MIN Minimum
SUM Somme
- 81 -
Exemples d'agrégats (Regroupements)
AVG DEGRE
11.2
SUM CRU SUM(QUANTITE)
CHABLIS
VOLNAY
MEDOC
350700200
VINS CRU MILL QUANTITE
CHABLIS
CHABLIS
VOLNAY
VOLNAY
MEDOC
19771987197719861985
10.911.910.811.211.2
100250400300200
DEGRE
SELECT AVG(DEGRE) FROM VINS;
SELECT CRU, SUM(QUANTITE)
FROM VINS
GROUP BY CRU;
- 82 -
COUNT Comptage de tuples
compter le nombre de commandes passéesSELECT COUNT(*) FROM Commande;
COUNT(*)
10
compter le nombre de produits de couleur rougeSELECT COUNT(*) AS NbRouge FROM Produit WHERE couleur = ‘ rouge ’;
NbRouge
2
- 83 -
SUM Sommations
Total des quantités commandées de produits de couleur rouge.
SELECT SUM(qute) AS QuteCmdRouge FROM Commande, ProduitWHERE Commande.pno = Produit.pno AND couleur = rouge;
QuteCmdRouge
12
- 84 -
SUM et agrégats Calculs sur les tuples et regroupement
Total des quantités commandées par numéro de produit.SELECT SUM(qute) AS QuteCmd , pno FROM Commande
GROUP BY pno;
QuteCmd pno9 102
22 1033 105
17 10716 108
- 85 -
Forme générale Consultation de tables
SELECT [ALL | DISTINCT] <attributs>
FROM <tables>
[ WHERE <conditions>
GROUP BY <attributs>
HAVING <conditions>
ORDER BY <attributs> ] ;
- 86 -
Autres Exemples (1) Calcul sur les tuples
« Donner le nom des buveurs ayant consommé plus que la moyenne »
SELECT Buveurs.nom
FROM Buveurs, Abus
WHERE Buveurs.nb = Abus.nb
AND Abus.qte >
( SELECT AVG(Abus.qte) FROM Abus ) ;
- 87 -
Autres Exemples (2) Calcul sur les tuples et regroupements
« Donner le nom et la quantité de vin bue par chaque buveur ayant consommé plus de 10 litres »
SELECT Buveurs.nom, SUM(Abus.qte)
FROM Buveurs, Abus
WHERE Buveurs.nb = Abus.nb
GROUP BY Buveurs.nom
HAVING SUM(Abus.qte) > 10 ;
- 88 -
Autres Exemples (3) Requête d’insertion
« Ajouter un buveur »
INSERT INTO Buveurs (nb, nom, ville, type)
VALUES (8, Dupont, Lyon, Petit)
- 89 -
Autres Exemples (4) Requête d’insertion
« Ajouter dans la table Petit_Buveurs, les petits buveurs contenus dans la table Buveurs »
INSERT INTO Petit_Buveurs (nb, nom)
SELECT Buveurs.nb, Buveurs.nom FROM Buveurs
WHERE Buveurs.type = ‘ Petit ’ ;
- 90 -
Autres Exemples (5) Requête de mise à jour
« Modifier le type des buveurs habitant Bordeaux en gros buveurs »
UPDATE Buveurs
SET Buveurs.type = ‘ gros ’
WHERE Buveurs.ville = ‘ Bordeaux ’ ;
- 91 -
Autres Exemples (6) Requête de suppression
« Supprimer tous les petits buveurs »
DELETE FROM Buveurs
WHERE Buveurs.type = ‘ Petit ’ ;
- 92 -
Normalisations
- 93 -
Nécessité des Normalisations Considérons le schéma de la relation suivante :
Article(NomFnsr, AdresseFnsr, NomArt, PrixArt).
Une table correspondante est :
NomFnsr AdresseFnsr NomArt PrixArt
Dupont Lille Fauteuil 1500
Martin Nice Bureau 5600
Dupont Lille Bureau 6000
Dupont Lille Armoire 4400
- 94 -
Anomalies de Mises à Jour Anomalie d’insertion :
On ne peut mémoriser (insérer) les coordonnées d’un fournisseur s’il ne fourni pas au moins un article.
Anomalie de suppression : La suppression d’un article qui est l’unique article fourni par un
fournisseur entraîne la perte des informations relatives à ce fournisseur.
Anomalies de modification : Si un fournisseur change de coordonnées, il faudra répercuter cette
modification à tous les articles dont il est le fournisseur.
- 95 -
Normalisation de l’exemple
La relation Article(NomFnsr, AdresseFnsr, NomArt, PrixArt)
contient certaines dépendances :NomFnsr AdresseFnsr NomFnsr, NomArt PrixArt
Elle devrait se décomposer en deux relations :Fournisseur(NomFnsr, AdresseFnsr) etArticle(NomFnsr, NomArt, PrixArt)
- 96 -
Normalisation Les règles de normalisation permettent de concevoir un
schéma de base de données correct : sans redondance d’information. sans anomalie de mise à jour.
Elles se basent sur les dépendances fonctionnelles (DF) qui traduisent les relations entre
les données. les formes normales qui définissent les relations bien conçues.
- 97 -
Normalisation Il existe plusieurs niveaux de normalisation :
Première forme normale (1FN) Deuxième forme normale (2FN) Troisième forme normale (3FN)
...
Un modèle relationnel est dit normalisé quand toutes ses tables sont en 3FN.
- 98 -
Dépendance fonctionnelle (DF) Soient
R (A1, A2, …, An) un schéma de relation. X et Y des sous-ensembles d’attributs de la relation R.
X Y qui se lit X détermine Y ou Y dépend (fonctionnellement) de X signifie que si on connaît la valeur de X alors la valeur de Y est automatiquement déduite.
PERSONNE N° SS --> NOM ? NOM --> N° SS ?
- 99 -
Normalisation Première forme normale
But : garantir la manipulation de données élémentaires (indivisibles)
Deuxième forme normale But : éliminer certaines redondances en s’assurant qu’aucun attribut
n’est déterminé par une sous partie de la clé.
Troisième forme normale But : Elimination des dépendances dues à la transitivité des
dépendances transitives.
- 100 -
Première forme normale Définition
Une relation est en 1ère forme normale si tout attribut contient une valeur atomique (unique)
Exemple d’une relation non en 1NF
Une telle relation doit être décomposée en répétant les noms
pour chaque profession
PERSONNE NOM PROFESSION
DUPONT Ingénieur, Professeur
MARTIN Géomètre
- 101 -
Première forme normale Décomposition :
PERSONNE NOM PROFESSION
DUPONT Ingénieur
MARTIN Géomètre
DUPONT Professeur
- 102 -
Deuxième forme normale une relation est en 2e forme normale ssi :
1) elle est en 1ère forme normale2) tout attribut non clé ne dépend pas d'une partie de la clé
Schéma d’une relation non en 2NF :
Une telle relation doit être décomposée en
R1(K1,K2,X) et R2(K2,Y)
R K1 K2 X Y
- 103 -
ExempleArticle(NomFnsr, AdresseFnsr, NomArt, PrixArt).
NomFnsr AdresseFnsr NomArt PrixArt
Dupont Lille Fauteuil 1500
Martin Nice Bureau 5600
Dupont Lille Bureau 6000
Dupont Lille Armoire 4400
NomFnsr AdresseFnsr NomFnsr, NomArt PrixArt
- 104 -
Troisième forme normale une relation est en 3e forme normale ssi :
1) elle est en 2e forme normale2) tout attribut n'appartenant pas a une clé ne dépend pas d’attribut ne
faisant pas partie de la clé Schéma d’une relation non en 3NF :
R K X Y Z
Une telle relation doit être décomposée en
R1(K, X, Y) et R2(X,Z)
- 105 -
Exemple 3ième Forme Normale Exemple
Voiture (NV, marque, type, puissance, couleur)Type --> marque Type --> puissance
Pas en 3eme forme normale !
Devra se décomposer en :Voiture(NV, type, couleur) etTypeVoiture(type, marque, puissance)