Bases de Données Relationnelles

105
- 1 - Bases de Données Relationnelles Samir Chouali [email protected] (à partir du cours de G.Cécé )

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

Transcript of Bases de Données Relationnelles

Page 1: Bases de Données Relationnelles

- 1 -

Bases de Données Relationnelles

Samir Chouali [email protected]

(à partir du cours de G.Cécé )

Page 2: Bases de Données Relationnelles

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

Page 3: Bases de Données Relationnelles

- 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

Page 4: Bases de Données Relationnelles

- 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

Page 5: Bases de Données Relationnelles

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

Page 6: Bases de Données Relationnelles

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

Page 7: Bases de Données Relationnelles

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

Page 8: Bases de Données Relationnelles

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

Page 9: Bases de Données Relationnelles

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

Page 10: Bases de Données Relationnelles

- 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

Page 11: Bases de Données Relationnelles

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

Page 12: Bases de Données Relationnelles

- 12 -

Modélisation

Modèle Conceptuel de Données (MCD)

Page 13: Bases de Données Relationnelles

- 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

Page 14: Bases de Données Relationnelles

- 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

Page 15: Bases de Données Relationnelles

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

Page 16: Bases de Données Relationnelles

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

Page 17: Bases de Données Relationnelles

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

Page 18: Bases de Données Relationnelles

- 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

Page 19: Bases de Données Relationnelles

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.

Page 20: Bases de Données Relationnelles

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.

Page 21: Bases de Données Relationnelles

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.

Page 22: Bases de Données Relationnelles

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é

Page 23: Bases de Données Relationnelles

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.

Page 24: Bases de Données Relationnelles

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.

Page 25: Bases de Données Relationnelles

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

Page 26: Bases de Données Relationnelles

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

Page 27: Bases de Données Relationnelles

- 27 -

Le Modèle Relationnel

Page 28: Bases de Données Relationnelles

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

Page 29: Bases de Données Relationnelles

- 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

Page 30: Bases de Données Relationnelles

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

Page 31: Bases de Données Relationnelles

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

Page 32: Bases de Données Relationnelles

- 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

Page 33: Bases de Données Relationnelles

- 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

Page 34: Bases de Données Relationnelles

- 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

Page 35: Bases de Données Relationnelles

- 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

Page 36: Bases de Données Relationnelles

- 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

Page 37: Bases de Données Relationnelles

- 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

Page 38: Bases de Données Relationnelles

- 38 -

Diagramme des Liens

NB NV DATE QUANTITEABUS

NV CRU MILL. DEGREVINSNB NOM PRENOM TYPEBUVEURS

Page 39: Bases de Données Relationnelles

- 39 -

Vocabulaire comparé

MCD Modèle RelationnelEntité Table

Propriété Attribut, ColonneIdentifiant Clé

Association Relation

Page 40: Bases de Données Relationnelles

- 40 -

Du MCD vers les Tables Relationnelles

Page 41: Bases de Données 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.

Page 42: Bases de Données Relationnelles

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

Page 43: Bases de Données Relationnelles

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

À :

Page 44: Bases de Données Relationnelles

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

Page 45: Bases de Données Relationnelles

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

À :

Page 46: Bases de Données Relationnelles

- 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

Page 47: Bases de Données Relationnelles

- 47 -

Contraintes

Page 48: Bases de Données Relationnelles

- 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

Page 49: Bases de Données Relationnelles

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

Page 50: Bases de Données Relationnelles

- 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

Page 51: Bases de Données Relationnelles

- 51 -

Contraintes d’Intégrité Référentielle

NB NV DATE QUANTITEABUS

NV CRU MILL. DEGREVINSNB NOM PRENOM TYPEBUVEURS

Page 52: Bases de Données Relationnelles

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

Page 53: Bases de Données Relationnelles

- 53 -

Algèbre Relationnelle

Page 54: Bases de Données Relationnelles

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

Page 55: Bases de Données Relationnelles

- 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

Page 56: Bases de Données Relationnelles

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

Page 57: Bases de Données Relationnelles

- 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

Page 58: Bases de Données Relationnelles

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

Page 59: Bases de Données Relationnelles

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

Page 60: Bases de Données Relationnelles

- 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

Page 61: Bases de Données Relationnelles

- 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

Page 62: Bases de Données Relationnelles

- 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

Page 63: Bases de Données Relationnelles

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

Page 64: Bases de Données Relationnelles

- 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

Page 65: Bases de Données Relationnelles

- 65 -

Utilisation Pratique

Prof

NoProfNom

Cours

NoCoursIntitulé

EnseigneVolumeHoraire

0,n 0,n

Page 66: Bases de Données Relationnelles

- 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

Page 67: Bases de Données Relationnelles

- 67 -

Structured Query Language

(SQL)

Page 68: Bases de Données Relationnelles

- 68 -

Définition SQL (Structured Query Language) est un langage de

définition et de manipulation de bases de données relationnelles.

Page 69: 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

Page 70: Bases de Données Relationnelles

- 70 -

Principaux ordres SQL

DDL DML DCL

ALTERCREATECOMMENTDROPRENAME

DELETEINSERTSELECTUPDATE

GRANTREVOKE

Page 71: Bases de Données Relationnelles

- 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

Page 72: Bases de Données Relationnelles

- 72 -

Sélection simple (1)

SELECT DISTINCT designFROM Produit;

SELECT DISTINCT designFROM ProduitWHERE prix > 2000;

designarmoirebureaucaisonclasseurfauteuil

designarmoirebureau

Page 73: Bases de Données Relationnelles

- 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

Page 74: Bases de Données Relationnelles

- 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

Page 75: Bases de Données Relationnelles

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

Page 76: Bases de Données Relationnelles

- 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

Page 77: Bases de Données Relationnelles

- 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

Page 78: Bases de Données Relationnelles

- 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

Page 79: Bases de Données Relationnelles

- 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

Page 80: Bases de Données Relationnelles

- 80 -

Fonctions statistiques

AVG Moyenne

COUNT Nombre d’éléments

MAX Maximum

MIN Minimum

SUM Somme

Page 81: Bases de Données Relationnelles

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

Page 82: Bases de Données Relationnelles

- 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

Page 83: Bases de Données Relationnelles

- 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

Page 84: Bases de Données Relationnelles

- 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

Page 85: Bases de Données Relationnelles

- 85 -

Forme générale Consultation de tables

SELECT [ALL | DISTINCT] <attributs>

FROM <tables>

[ WHERE <conditions>

GROUP BY <attributs>

HAVING <conditions>

ORDER BY <attributs> ] ;

Page 86: Bases de Données Relationnelles

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

Page 87: Bases de Données Relationnelles

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

Page 88: Bases de Données Relationnelles

- 88 -

Autres Exemples (3) Requête d’insertion

« Ajouter un buveur »

INSERT INTO Buveurs (nb, nom, ville, type)

VALUES (8, Dupont, Lyon, Petit)

Page 89: Bases de Données Relationnelles

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

Page 90: Bases de Données Relationnelles

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

Page 91: Bases de Données Relationnelles

- 91 -

Autres Exemples (6) Requête de suppression

« Supprimer tous les petits buveurs »

DELETE FROM Buveurs

WHERE Buveurs.type = ‘ Petit ’ ;

Page 92: Bases de Données Relationnelles

- 92 -

Normalisations

Page 93: Bases de Données Relationnelles

- 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

Page 94: Bases de Données Relationnelles

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

Page 95: Bases de Données Relationnelles

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

Page 96: Bases de Données Relationnelles

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

Page 97: Bases de Données Relationnelles

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

Page 98: Bases de Données Relationnelles

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

Page 99: Bases de Données Relationnelles

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

Page 100: Bases de Données Relationnelles

- 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

Page 101: Bases de Données Relationnelles

- 101 -

Première forme normale Décomposition :

PERSONNE NOM PROFESSION

DUPONT Ingénieur

MARTIN Géomètre

DUPONT Professeur

Page 102: Bases de Données Relationnelles

- 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

Page 103: Bases de Données Relationnelles

- 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

Page 104: Bases de Données Relationnelles

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

Page 105: Bases de Données Relationnelles

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