Bases de données relationnelles Maude Manouvriermanouvri/BD/CoursBDRelationnelles_L3.pdf · Philip...

Licence MIDO - 3ème année

2014-2015

Bases de données relationnelles

Maude Manouvrier

Modélisation Entité/Association et UML (Vocabulaire)

Modèle relationnel et passage au modèle relationnel

Algèbre relationnelle

Calcul relationnel

SQL / Embedded SQL / ODBC / JDBC

Dépendances fonctionnelles

Décomposition de schéma

Formes Normales

©Maude Manouvrier - Univ. Paris Dauphine

Ouvrages de référence utilisés pour le cours et disponibles à la BU

T. Connoly, C. Begg et A. Strachan, Database Systems A Pratical

Approach to Desigh, Implementation and Management, 6ème édition, 2014,

ISBN: 9780132943260

F. Brouad, R. Bruchez, C. Soutou, SQL, Syntex Informatique, Pearson,

2012, ISBN: 978-2-7440-7630-5, disponible à la BU 005.72 SQL

R. Ramakrishnan et J. Gehrke, Database Management Systems, Second

Edition; McGraw-Hill, 2002, ISBN: 0-07-232206-3, disponible à la BU

055.7 RAM

A. Silberschatz, H.F. Korth et S. Sudarshan, Database System Concepts,

McGraw-Hill, 6ème édition, 2010, ISBN: 978-0073523323,

J.D. Ullman et J. Widom, A first Course in Database Systems, Prentice

Hall, 3eme édition, 2014, ISBN: 978-9332535206

BIBLIOGRAPHIE

2


Autres ouvrages de référence, disponibles à la BU :

C.J. Date, An Introduction to Database Systems, Addison Wesley

C.J. Date, A Guide to SQL Standard, Addison Wesley

R.A. El Masri et S.B. Navathe, Fundamentals of Database Systems, Prentice Hall

Ouvrages pédagogiques contenant des exercices corrigés :

Philip J. Pratt, Initiation à SQL - Cours et Exercices corrigés, Eyrolles, 2001

F. Brouard, C. Soutou, ULM 2 pour les bases de données : Modélisation,

normalisation, génération, SQL, outils , Eyrolles, 2012

F. Brouard, C. Soutou , SQL (Synthèse de cours et exercices corrigés). Pearson

Education 2008

R. Stephens, R. Plew, A. Jones, Adapté par Nicolas Larrousse , SQL, Coll.

Synthex, Pearson Education, 2012

BIBLIOGRAPHIE

3

Bases de données :

Collection homogène et structurée d'informations ou de

données qui existent sur une longue période de temps et qui

décrivent les activités d'une ou plusieurs organisations

ensemble de données modélisant les objets d'une partie du

monde réel et servant de support à une application informatique

4 ©Maude Manouvrier - Univ. Paris Dauphine

Exemple 1 :

Organisation : une bibliothèque

Données : les livres, les emprunts, les emprunteurs

Exemple 2 :

Organisation : une Université

Données : les étudiants, les enseignants, les cours, etc.

Chap. I - Introduction


SGBD (1/3)

Exemples : MySQL, PostgreSQL (utilisé en TP), Oracle,

Microsoft SQLServer, etc.

Systèmes de Gestion de Bases de Données (DataBase

Management Systems - DBMS) :

Ensemble de logiciels systèmes permettant aux utilisateurs

d'insérer, de modifier, et de rechercher efficacement des

données spécifiques dans une grande masse

d'informations (pouvant atteindre plusieurs milliards

d'octets) partagée par de multiples utilisateurs


SGBD (2/3)

Principales fonctionnalités d’un SGBD :

Création et mises à jour de la structure de la base de

données (par le concepteur et/ou le DBA DataBase

Administrator)

Administration de la base de données : gestion des

utilisateurs, des droits d’accès etc. (par l’administrateur

– DBA)

Saisie et mises à jour des données (par le concepteur

et/ou les utilisateurs)

Interrogation des données selon différents critères

et/ou en effectuant des calculs (par les utilisateurs)



Principaux composants :

– Système de gestion de fichiers

– Gestionnaire de requêtes

– Gestionnaire de transactions

Principales fonctionnalités :

– Contrôle de la redondance d’information

– Partage des données

– Gestion des autorisations d’accès

– Vérifications des contraintes d’intégrité

– Sécurité et reprise sur panne

7


SGBD (3/3)


Abstraction des données

• Niveau interne ou physique :

– plus bas niveau

– indique comment (avec quelles structures de données) sont

stockées physiquement les données

• Niveau logique ou conceptuel :

– décrit par un schéma conceptuel

– indique quelles sont les données stockées et quelles sont leurs

relations indépendamment de l’implantation physique

• Niveau externe ou vue :

– propre à chaque utilisateur

– décrit par un ou plusieurs schémas externes

8



Instances et schéma

• Instances de base de données :

– données de la base à un instant donné

– manipulées par un langage de manipulation de données (DML - Data Manipulation Language)

• Schéma de base de données :

– description de la structure des données

– ensemble de définitions exprimées en langage de description de données (DDL - Data Definition Language)

9



Petit historique

• 1960 : systèmes de gestion de fichiers

• 1970 : début des SGBD réseaux et hiérarchiques proches des systèmes de

gestion de fichiers pas d’interrogation sans savoir où est l'information

recherchée ("navigation") et sans écrire de programmes

• 1970 : papier fondateur de CODD sur la théorie des relations

fondement de la théorie des bases de données relationnelles

INGRES à Berkeley - langage QUEL

System R IBM à San Jose, Ca. - langages SEQUEL et QBE

• 1980 : Apparition des SGBD relationnels sur le marché (Oracle, Ingres,

Informix, Sybase, DB2 …)

• 1990 : début des SBGD orientés objet (Gemstone, O2, Orion, Objectstore,

Versant, Matisse...).

• Aujourd’hui : relationnel-objet, semi-structuré, multimédia ...

10



Chap II - Modélisation Méthodologie à suivre pour modéliser un problème

• Déterminer les entités/classes et attributs :

– entité/instance de classe = objet décrit par de l’information

– objet caractérisé uniquement par un identifiant = attribut(s)

– attribut multi-valué ou avec une association 1:N = entité ou instance

– attacher les attributs aux ensemble d’entités/classes qu'ils décrivent le

plus directement

– éviter au maximum les identificateurs composites

• Identifier les généralisations-spécialisations/héritage

• Définir les associations – éliminer les associations redondantes

– éviter les associations n-aires

– calculer les cardinalités de chaque association 11


Modélisation Entité/Association (Format Merise)

12

Chap. II - Modélisation

Un automobiliste possède

entre zéro et N voitures

Une voiture a un et un

seul propriétaire


Modélisation UML

13


Un automobiliste possède

entre zéro et N voitures

Une voiture a un et un

seul propriétaire

Attention : petite liberté prise avec UML, les attributs soulignés ici ne correspondent

pas à des attributs dérivés mais aux identificateurs (pour ne pas les oublier lors du

passage au relationnel!!)

©Maude Manouvrier - Univ. Paris Dauphine 14


Généralisation/Spécialisation

(E/A - Merise)

Ensemble d’entités

généralisantes

Ensemble d’entités

spécialisées



Héritage (UML)

Classe mère / Sur-classe

Classes dérivées ou filles / sous-classes



Agrégat (E/A - Merise)

On peut nommer ou

non l’agrégat



Classe-Association (UML)

On peut nommer ou non

la classe-association



Entité Faible (E/A - Merise)

Chaque salle a un

numéro unique dans un

bâtiment donné

Ex. Salle 1 du bâtiment A

et Salle 1 du bâtiment C

Pour distinguer une salle

d’une autre, il faut

connaître le bâtiment

auquel elle est rattachée

Appartient



Association qualifiée (UML)

Chaque salle a un

numéro unique dans un

bâtiment donné

Ex. Salle 1 du bâtiment A

et Salle 1 du bâtiment C

Pour distinguer une salle

d’une autre, il faut

connaître le bâtiment

auquel elle est rattachée

Ap

pa

rtient

1

1



Composition (UML)


Contraintes Contraintes d’intégrité :

toutes règles implicites ou explicites que doivent

suivre les données [Gar99]

– Contraintes d'entité: toute entité doit posséder un

identificateur

– Contraintes de domaine : les valeurs de certains

attributs doivent être prises dans un ensemble donné

– Contraintes d'unicité : une valeur d'attribut ne peut pas

être affectée deux fois à deux entités différentes

– Contraintes générales : règle permettant de conserver

la cohérence de la base de manière générale

21



Exemples de contraintes

– Contraintes de domaine :

"La fonction d’un enseignant à l’Université prend sa valeur

dans l’ensemble {vacataire, moniteur, ATER, MCF, Prof.,

PRAG, PAST}."

– Contraintes d'unicité :

"Un département, identifié par son numéro, a un nom unique

(il n’y a pas deux départements de même nom)."

– Contraintes générales :

"Un même examen ne peut pas avoir lieu dans deux salles

différentes à la même date et à la même heure. "

22




Un attribut (ou un ensemble d'attributs) Y dépend

fonctionnellement d'un attribut (ou ensemble

d'attributs) X si :

étant donné une valeur de X, il lui correspond une

valeur unique de Y ( l'instant considéré)

XY : Y dépend fonctionnellement de X

ou X détermine Y

Déclaration des dépendances au niveau du schéma

conceptuel 23



Exemple de dépendances fonctionnelles

24


Marque, Type, Puissance, Année Immatriculation

Type Marque

EnseignantID Nom, Prénom, Position …

Nom, Prénom, Position, … Enseignant_ID

Ex. Le type "Twingo" sera toujours

associé, dans la base de données, à la

marque "Renault".

Immatriculation Marque, Type, Puissance, Année

identificateur Tous les autres attributs

Si un numéro de téléphone est associé à un seul enseignant :

Telephone Enseignant_ID


Chap III - Modèle relationnel

• Domaine : ensemble de valeurs caractérisé par un nom

• Relation : sous-ensemble du produit cartésien d'une liste de domaines

caractérisé par un nom unique

– représentée sous forme de table à deux dimensions

– colonne = un domaine du produit cartésien

– un même domaine peut apparaître plusieurs fois

– ensemble de nuplets sans doublon

• Attribut : une colonne dans une relation

– caractérisé par un nom et dont les valeurs appartiennent à un domaine

– les valeurs sont atomiques

• Nuplet : une ligne d'une relation

– suite de valeurs d'attribut décrivant une entité/instance de classe

– les nuplets d'une relation sont tous différents 25


Exemple de relation

26

Chap. III - Modèle relationnel

Nom d’attribut

Nuplets ou tuples

La relation Enseignant

NSS Nom Prénom Fonction

273… Manouvrier Maude MCF

…

…


Instances et schéma

• Instances de base de données :

les nuplets (les valeurs) contenus dans la base à un instant donné

• Schéma de base de données :

– ensemble de schémas de relation

– modélisation logique de la base de données à l’aide du modèle relationnel

• Schéma de relation :

liste d’attributs et leurs domaines

27



Passage au relationnel Transformation des ensembles d’entités :

• chaque ensemble d'entités/classes E

– une relation R dont le schéma est celui de l'ensemble d'entités/classe

– l'identificateur de E devient la clé de R

• chaque ensemble d'entités faibles/association qualifiée E

une relation R qui comprend tous les attributs de E +

l'identificateur de l'ensemble d'entités fortes/classe associé(e)

• généralisation-spécialisation/héritage

– l'ensemble d'entités généralisante/classe mère E une relation R

– chaque ensemble d'entités Ei spécialisé/classe fille une relation Ri dans laquelle l'identifiant est de même domaine

que l'identifiant de E

28



Transformation des ensembles d’associations E/A


Accident ( Auto_ID, Nom, Prénom, Adresse,

Immatriculation, Marque, Type, Puissance, Année )

29

Accidente lors

de la location

On peut choisir l’un ou l’autre comme clé primaire

Automobiliste ( Auto_ID, Nom, Prénom, Adresse)

Voiture (Immatriculation, Marque, Type, Puissance, Année )

Comment faire le lien ?



Accidente ( Auto_ID, Nom, Prénom, Adresse,


Transformation des ensembles d’associations UML

On peut choisir l’un ou l’autre comme clé primaire

Accidente lors de la location




,

Automobiliste ( Auto_ID, Nom, Prénom, Adresse )

Voiture ( Immatriculation, Marque, Puissance, Type, Année ) Voiture ( Immatriculation, Marque, Puissance, Type, Année,

#Auto_ID )

NB : #Auto_ID fait référence à Auto_ID de Automobiliste

ou 1:N

ou 0:1




Voiture ( Immatriculation, Marque, Puissance, Type, Année,

#Auto_ID )

NB : #Auto_ID fait référence à Auto_ID de Automobiliste


ou 1..*

ou 0..1




Voiture ( Immatriculation, Marque, Puissance, Type, Année )


Location ( #Auto_ID, #Immatriculation, Date ) ou

Location ( Loc_ID, #Auto_ID, #Immatriculation, Date )

ou 1:N

ou 0:1

ou 1:M

ou 0:1




Voiture ( Immatriculation, Marque, Puissance, Type, Année )

Location ( #Auto_ID, #Immatriculation, Date ) ou

Location ( Loc_ID, #Auto_ID, #Immatriculation, Date )


ou 1..*

ou 0..1

ou 1..*

ou 0..1



NB : #Personne_ID dans

Enseignant et Etudiant font

référence à Personne_ID dans

Personne

Transformation des concepts Généralisation-Spécialisation / Héritage

Enseignant ( #Personne_ID, Position )

Etudiant ( #Personne_ID, Adresse,

Ville ...)

Personne ( Personne_ID,

Nom,Prénom, Téléphone ... )

Solution possible (une autre sera

donnée en cours) :



Transformation des entités faibles E/A

Bâtiment ( Bâtiment_ID, ... )

NB : Une salle est identifiée par le

couple (Numéro,#Bâtiment_ID)

#Bâtiment_ID fait référence à

Bâtiment_ID de Bâtiment

Salle ( Numéro, #Bâtiment_ID, Capacité)



Transformation des associations qualifiées UML

Bâtiment ( Bâtiment_ID, ... )

Salle ( Numéro, #Bâtiment_ID, Capacité)

NB : Une salle est identifiée par le

couple (Numéro,#Bâtiment_ID) ;

#Bâtiment_ID fait référence à

Bâtiment_ID de Bâtiment

1



Voi

Voiture ( Immatriculation, Marque,

Puissance, Type, Année )

Moteur (Numéro, #Immatriculation,

Chevaux)

PotEchappement (Num_Série,

#Immatriculation, DateInstallation)

Transformation de la composition UML



Ne pas oublier de définir les DF :

39


Accidente ( Auto_ID, Nom, Prénom, Adresse,


Immatriculation Marque, Type, Puissance, Année

Type Marque


Auto_ID ) Immatriculation Auto_ID

+ les Dépendances fonctionnelles de Voiture

Location ( Auto_ID, Immatriculation, Date )

Pas de dépendance non triviale

Auto_ID Immatriculation et Immatriculation Auto_ID

Auto_ID Nom, Prénom, Adresse

Auto_ID Immatriculation


Chap. III – Modèle relationnel

Attribut (ou ensemble d’attributs) permettant d’identifier de

manière unique les nuplets de la relation

Clé

Exemples :

L’attribut ISBN pour une relation Livre

L’attribut NuméroImmatriculation pour une relation Voiture

L’attribut NuméroCarte pour une relation Emprunteur

Par défaut : Création d’un attribut numérique s’incrémentant

automatiquement

! Une clé est unique (pas deux fois la même valeur) et a

forcément une valeur (pas de valeur null)

Clé artificielle

(surrogate key)


Intégrité structurelle • Unicité des clés

– ensemble minimal d'attributs dont la connaissance des valeurs permet d'identifier un nuplet unique de la relation considérée

– R a pour clé K si : t1, t2 nuplets d’une instance de R

t1.K t2.K

– Clé primaire de R : une clé minimale parmi toutes les clés minimales possibles de R

• Contraintes d’intégrité référentielle

‎ contrainte d'intégrité portant sur une relation R et imposant à la valeur d'une suite d'attributs d'apparaître comme valeur de clé dans une autre relation

41




Attribut (ou ensemble d’attributs) d’une relation qui fait (font)

référence à la clé primaire d’une autre relation

Clé étrangère (1/5)

Problèmes :

Répétition des noms, prénoms, dates de naissances, ISBN, etc.

autant de fois qu’il y a d’emprunts = Redondance d’information

Comment identifier les nuplets ?

Ne pas mettre toutes les données dans une seule relation !!! !

A quoi cela sert ? Exemple d’une mauvaise relation :




La solution ? Diviser les données en plusieurs relations

Division en 3 relations associées : Personne, Emprunt et Livre

Stockage unique des informations de chaque livre

Stockage unique des informations de chaque emprunteur

Stockage unique des informations de chaque emprunt

[PK]




L’attribut livre_id de la relation Emprunt est une clé étrangère qui fait

référence à l’attribut livre_id de la relation Livre

L’attribut personne_id de la relation Emprunt est une clé étrangère qui

fait référence à l’attribut personne_id de la relation Personne

Relation Livre Relation Emprunt

Relation Personne




Intégrité référentielle : Ensemble de règles garantissant la

cohérence (l'intégrité) des données réparties dans plusieurs

relations

Insertion dans la relation Emprunt : autorisée uniquement si on

spécifie une valeur personne_id qui existe dans la relation Personne et

une valeur livre_id qui existe dans la relation Livre

Suppression d’un nuplet/Mise à jour de la valeur de clé primaire dans

la relation Personne :

o Non autorisée si un nuplet dans Emprunt fait référence au

nuplet supprimé/mis à jour dans Personne

o Ou autorisée mais avec suppression/mise à jour en cascade des

nuplets correspondant dans Emprunt




Insertion dans la relation où est définie la clé étrangère : autorisée

uniquement si on spécifie une valeur de clé étrangère qui existe

dans la relation référencée

Suppression d’un nuplet/mise à jour de la valeur de la clé

primaire dans la relation référencée :

o Non autorisée si un nuplet de la relation référençant fait

référence au nuplet supprimé dans la relation référencée

o Ou autorisée mais

avec suppression/mise à jour en cascade des nuplets

correspondant dans la table référençant ou avec

affectation d’une valeur NULL

avec affectation d’une valeur NULL ou d’une valeur

par défaut à la clé étrangère des nuplets

correspondants dans la relation référençant


Clé /Clé minimale /Surclé

47


Accident ( Auto_ID, Nom, Prénom, Adresse,


Clés minimales possibles : Auto_ID ou Immatriculation

Surclé : (Auto_ID et/ou Immatriculation) + d’autres attributs


Auto_ID )

Clé minimale (et primaire) : Immatriculation

Surclé : (Immatriculation, Marque, Puissance, Type, Année,Auto_ID)

Location ( Auto_ID, Immatriculation, Date )

Clé minimale (et primaire) : (Auto_ID, Immatriculation, Date)


Intégrité structurelle

• Valeur nulle

– valeur conventionnelle introduite dans une relation pour représenter une information inconnue ou inapplicable

– tout attribut peut prendre une valeur nulle excepté les

attributs de la clé primaire (contrainte d’entité)

• Contraintes de domaine

contrainte d'intégrité imposant qu'un attribut d'une relation

comporte des valeurs vérifiant une assertion logique

48



Contraintes

Contraintes d’intégrité :

toutes règles implicites ou explicites que doivent

suivre les données

– Contraintes d‘identité: tout nuplet doit posséder une

valeur de clé primaire unique

– Contraintes de domaine : les valeurs de certains

attributs doivent être prises dans un ensemble donné

– Contraintes d'unicité : une valeur d'attribut ne peut pas

être affectée deux fois à deux entités différentes

– Contraintes générales : règle permettant de conserver

la cohérence de la base de manière générale

49



Exemples de contraintes – Contraintes de domaine :

"La fonction d’un enseignant à l’Université prend sa valeur

dans l’ensemble {vacataire, moniteur, ATER, MCF, Prof.,

PRAG, PAST}."

– Contraintes d'unicité :

"Un département, identifié par son numéro, a un nom unique

(il n’y a pas deux départements de même nom)."

– Contraintes générales :

"Un même examen ne peut pas avoir lieu dans deux salles

différentes à la même date et à la même heure "

"La date de début d’emprunt doit être antérieure à la date de

fin d’emprunt"

50

Chap. II – Modèle relationnel

Création d’une base de données

Étape N°1 : Concevoir la base de données

= Réfléchir à ce que va contenir la base de données et

comment structurer les données

= Modélisation de la base de données

Modèle conceptuel de données

(Modèle Entité/Association ou UML - hors programme)

Démarche :

Établir la liste des données devant être stockées dans la

base

Définir la structure des données


Étape N°2 : Définir le modèle relationnel

= le schéma des relations de la base de données

Démarche :

Pour chaque relation :

• Définir les différents attributs

• Définir la clé primaire

Pour chaque attribut de chaque relation

• Définir le type et le domaine

• Préciser les propriétés (taille, format, etc.)

Quand il y a plusieurs relations : définir les clés

étrangères



Quelques règles



53

Bien réfléchir aux schémas des relations et vérifier qu’ils sont

corrects avant d’y insérer des données

Utiliser des noms de relations et d’attributs compréhensibles (penser

aux utilisateurs!!)

Choisir le type de données adéquate pour chaque attribut

Ne pas créer d’attribut de trop grande taille

Ne pas créer d’attribut ayant des valeurs trop variables (ex. Age)

Spécifier toutes les contraintes de domaines (en particulier quand

l’ensemble de valeurs est limité), d’unicité etc.

Préférer les clés primaires de type entier et en particulier des clés

artificielles

Exemple 1:

On veut créer une base de données stockant des enseignants

(avec leur nom, prénom etc.) et des départements, chaque

enseignant appartenant à un et un seul département.



Modèle relationnel correspondant : Departement(Dept_ID,Nom_Dept)

Nom_Dept est unique et non NULL

Enseignant(Ens_ID, NUMEN, Nom, Prénom …,

#Dep_ID, Grade)

#Dept_ID est une clé étrangère faisant

référence à la clé primaire de Departement

Grade {"Professeur", "MCF", "ATER" …}

Email peut prendre la valeur NULL



Relation Departement

Relation Enseignant

Exemple 2:

On veut créer une base de données gérant des énoncés

d’examens et des exercices.

Quelles sont les différences entre les modèles relationnels ?



Modèle relationnel 1 : Examen(Exam_ID, Date, Heure)

Exercice(Exo_ID, Enoncé)

Contenu_Exam(#Exam_ID, #Exo_ID, Numero_Ordre)

Modèle relationnel 2 : Examen(Exam_ID, Date, Heure)

Exercice(Exo_ID, Enoncé, #Exam_ID)


Exemple 3:

Relation Salle

Relation Reservation

Reservation contient une clé

étrangère à 2 attributs faisant

référence à la clé primaire de

Salle

!


Langages d’interrogation

• Algèbre relationnelle

Opérations utilisées par le SGBD pour évaluer les requêtes

• Calcul relationnel à variable nuplet

Fondement logique du langage SQL

• Calcul relationnel à variable domaine

Fondement logique des langages de requêtes graphiques

• SQL (Structured Query Langage)

Ces langages sont équivalents : ils permettent de

désigner les mêmes ensembles de données

58

Différents types de requête :

o Requêtes d’interrogation (Data Manipulation Language)

o Requêtes d’insertion, de mise à jour et de suppression des

données (Data Manipulation Language)

o Requêtes de définition de schéma (Data Definition

Language)

Requêtes d’interrogation :

Entrée : une ou plusieurs relations [+ prédicats]

Sortie : une relation temporaire (en mémoire)


Requêtes et langage d’interrogation


Chap IV - Algèbre relationnelle

Opérations unaires :

• sélection des nuplets satisfaisant un certain prédicat

Etudiant(Etudiant_ID, Nom, Prénom, Rue, Ville, Code-Postal,

Téléphone, Fax, Email, NumAnnées)

(Ville=‘ Paris ’) (Etudiant)

(Ville=‘ Paris ’) (NumAnnées 2) (Etudiant)

• projection : élimination de certains attributs d’une relation

Nom,Prénom(Etudiant)

Nom,Prénom( (Ville=‘ Paris ’) (Etudiant) )

60


Exemples de résultats d’opérations unaires

61

Relation Enseignant

:

Résultat de la sélection (grade=‘ MCF ’) (Enseignant) :

Résultat de la projection

Nom,Prénom(Enseignant) : Résultat de la requête

Nom,Prénom ((grade=‘ MCF ’) (Enseignant)) :


Opérations binaires • Union : { nuplet de 2 relations schéma-compatibles }

Enseignant( Enseignant_ID, Département_ID, Nom, Prénom, Grade,

Téléphone, Fax, Email )

Nom,Prénom(Etudiant) Nom,Prénom(Enseignant)

• Différence : des nuplets de 2 relations compatibles

Nom,Prénom(Enseignant) - Nom,Prénom(Etudiant)

Produit cartésien : concaténation couples nuplets de 2 relations

Département(Département_ID, Nom_Département)

Schéma du Produit Cartésien de Enseignant Departement :

(Enseignant_ID, Enseignant.Département_ID, Nom, Prénom, Grade,

Téléphone, Fax, Email, Département.Département_ID,

Nom_Département) 62

Chap. IV - Algèbre

relationnelle


Exemple d’union et de différence

63

Chap. IV - Algèbre

relationnelle

Nom,Prénom(Etudiant) Nom,Prénom(Enseignant) :

Nom,Prénom(Enseignant) - Nom,Prénom(Etudiant) :


Produit cartésien

64

Chap. IV - Algèbre

relationnelle

NSS Nom Prénom Grade Dept

12345 Manouvrier Maude MCF 1

45678 Toto Titi Prof 2

La relation Enseignant

Dept_ID Nom_Dept_

1 Info

2 Math

La relation Département

NSS Nom Prénom Grade Dept Dept_ID Nom_Dept

12345 Manouvrier Maude MCF 1 1 Info

45678 Toto Titi Prof 2 1 Info

12345 Manouvrier Maude MCF 1 2 Math

45678 Toto Titi Prof 2 2 Math

La relation Enseignant Département


Autres opérations

65

Chap. IV - Algèbre

relationnelle

• Renommage :

A’,B’, …(r AA’, B B’, … )

• Intersection :

r s = r - (r - s)

• Théta-jointure :

r s = (r s)

• Jointure naturelle : r(R) et s(S) avec R S = {A1,A2, …, An}

r s = R S ((r.A1=s.A1) (r.A2=s.A2) … (r.An=s.An) (r s))


Exemple de renommage et d’intersection

66

Chap. IV - Algèbre

relationnelle

Last_Name,First_Name(Enseignant Nom Last_Name, Prénom First_Name ) :

Nom,Prénom(Enseignant) Nom,Prénom(Etudiant) :


Exemple de produit cartésien Chap. IV - Algèbre

relationnelle

La relation Enseignant :

La relation Departement :

Enseignement Departement :

67


Exemple de jointure Chap. IV - Algèbre

relationnelle

La relation Enseignant :

La relation Departement :

Enseignement Departement_ID Departement :

68


Division

69

Chap. IV - Algèbre

relationnelle

Certaines requêtes contenant le terme « pour tous »

Soient r(R) et s(S) avec S R

la relation r s a pour schéma R - S

un nuplet t appartient à r s si :

t R - S (r)

ts nuplet de s, tr dans r qui satisfait :

tr(S) = ts(S)

tr(R-S) = t

r s = R - S (r) - R - S [ ( R - S (r) s ) - R - S, S (r) ]


Division

70

Chap. IV - Algèbre

relationnelle

La relation Enseignement :

La relation

Inscription :

Etudiant_ID, Enseignement_ID, Departement_ID (Inscription) Enseignement_ID, Departement_ID (Enseignement) :


Contraintes et DF

71

Chap. IV - Algèbre

relationnelle

• Expressions des contraintes d’intégrité référentielle :

Département_ID(Enseignant) Département_ID(Département)

Département_ID(Enseignant) - Département_ID(Département) =

• Expressions des dépendances fonctionnelles :

XY r et t1, t2 r on a :

t1.X = t2.X t1.Y = t2.Y


Chap V - Calcul relationnel

• Langage déclaratif (ou non-procédural): le quoi

• Algèbre relationnelle (procédural) : le comment

• Calcul à variable nuplet :

{ t | P(t) } avec P(t) tel que :

r(t) : t est un nuplet de r

t.att1 = valeur1

t.att1 > s.att2 ...

{t | Etudiant(t) (t.Ville=‘ Paris ’) (t.NumAnnées 2)}

{t.Nom, t.Prénom | Etudiant(t) (t.Ville=‘ Paris) }

72


Quantificateurs

73

Chap. V - Calcul à variable nuplet

• t (Q(t)) : il existe un nuplet dans la base qui vérifie Q(t)

• t (Q(t)) : pour tous les nuplets, Q(t) est vrai

• Variable liée

• Variable libre

• Expression saine

• Expression non saine


Expression des opérateurs algébriques

74


• (r) :

• A1,A2, …, An (r) :

• r s :

• r - s :

• r s :

• r s :

• r s :


Division

75


Division : Livre(ISBN, Titre, Editeur)

Emprunt(EmpruntID, ISBN, DateEmprunt,EtudiantID)

Etudiant(EtudiantID, Nom, Prenom)

« Quels sont les noms et prénoms des étudiants ayant emprunté tous les livres ? »

{ t.Nom, t.Prenom / Etudiant(t) [ u Livre (u)

( v Emprunt(v)

(v.Etudiant_ID=t.Etudiant_ID) (v.ISBN=u.ISBN )

)

]

}

La requête retourne les valeurs des att. Nom et Prenom des nuplets t de la relation Etudiant tq :

Il existe un nuplet v dans Emprunt

associant le livre u à l’étudiant t

{ t.Nom, t.Prenom / Etudiant(t) [ u ¬ ( Livre(u) ) v

( v Emprunt(v)

(v.Etudiant_ID=t.Etudiant_ID) (v.ISBN=u.ISBN )

) ]

]

}

Quel que soit un nuplet u si

c’est un nuplet de Livre (i.e.

quel que soit un livre) alors

Quel que soit u, u n’est pas

dans Livre ou (u est dans

Livre et ) il existe v …


Calcul à variable domaine

76

Chap. V - Calcul relationnel

{<x1,x2, … xn> | P( x1 ,x2 , …, xn )}

avec x1,x2, … xn variable domaine et P formule

Exemples :

Marin(MiD, Nom, Grade, Date-Nais)

Bateau(Bid, Bnom,Couleur)

Reservation(#Mid,#Bid,Date)

Nom et grade des marins : { < n, g > \ id, dn Marin(id,n,g,dn) }

Marins ayant réservé tous les bateaux :

{ < i, n, g, dn > \ Marin(i,n,g,dn) [ b, bn, c Bateau(b,bn,c)

( m, br, d Reservation(m,br,d) (m = i) (br = b) ) ] }


QBE

77


Query By Exemple :

– langage de requête graphique

– mise en œuvre du calcul à variable domaine

Oracle Query Builder

Etape 1 : sélection des

relations de la requête


QBE

78


Etape 2 :

sélection des

attributs de

la requête


QBE

79


Etape 3 :

possibilité

d’appliquer

des fonction

d’agrégation

sur les nuplets

résultats


Expression des contraintes en logique

des prédicats

80


Agence(nom_banque,ville ...)

Emprunt(num_emprunt,nom_banque,num_client, montant ...)

Compte(nom_banque,num_client,num_compte,solde ...)

« Chaque emprunteur possède un compte en banque dans l'agence

dont le solde est au minimum égal à la moitié de son emprunt »

{ ( e Emprunt(e) ( c Compte(c)

(c.num_client=e.num_client)

(c.nom_banque = e.nom_banque)

( c.solde (e.montant / 2)

)

)

}


Chap VI - Algèbre relationnelle étendue

• Projection généralisée :

ajout d’expressions arithmétiques dans une projection

Nom_Client, (Crédit - Débit) (Compte_en_Banque )

• Jointure externe (outer-join) :

– jointure externe à gauche : ]

– jointure externe à droite : [

– jointure externe : ][

r ] s r s et conservation des attributs des nuplets de r

qui ne joignent avec aucun nuplet de s (les valeurs des

attributs de s sont mises à NULL) 81


Chap. VI - Algèbre relationnelle étendue

Nom_Employé Ville

Tom Marseille

Jerry Paris

Alex Limoges

Marthe Perpignan

Nom_Employé Filiale Salaire

Tom SUD_EST 10000

Jerry IDF 25000

Sophie IDF 15000

Marthe SUD_OUEST 12000

Nom_Employé Ville Filiale Salaire

Tom Marseille SUD_EST 10000

Jerry Paris IDF 25000

Alex Limoges NULL NULL

Marthe Perpignan SUD_OUEST 12000

Nom_Employé Ville Filiale Salaire

Tom Marseille SUD_EST 10000

Jerry Paris IDF 25000

Sophie NULL IDF 15000

Marthe Perpignan SUD_OUEST 12000

Personnel Employé

Personnel

]

Employé

Personnel

[

Employé


Fonction d’agrégation

83

• Somme des places disponibles dans l’Université

SumCapacité(Salle )

• Nombre moyen de places disponibles dans les salles de l’Université

AvgCapacité (Salle)

• Nombre d’étudiants à l’Université

CountEtudiant_ID (Etudiant)

• Capacité de la plus petite salle

MinCapacité(Salle)

• Nombre d’enseignants par départements :

Nom_Département CountEnseignant_ID(Enseignant Département)



Mise à jour de la base

84

• Insertion

Salle Salle {("B", "038", 15)}

• Suppression

Salle Salle - Capacité 10 (Salle)

• Mise à jour : utilsation de la projection généralisée

r Etudiant_ID [(Nom=‘ Dupont ’ ) (Prénom=‘ Jacques ’) (Etudiant)]

Etudiant (Etudiant.Etudiant_ID <> r.Etudiant_ID) (Etudiant)

Etudiant_ID, Nom, Prénom, Rue, Ville, Code-Postal,

Téléphone " 06 45 12 45 86 ", Fax, Email, NumAnnées

[(Etudiant.Etudiant_ID =r.Etudiant_ID) (Etudiant) ]


Mise à jour du

téléphone


Vue

85

Relation virtuelle de schéma et instances dérivés de la base par une requête. Utilisée pour :

• Cacher certaines informations à un groupe d’utilisateurs

• Faciliter l’accès à certaines données

create view nom_vue as < requête >

Exemple :

create view Info_Non_Confidentielle_Etudiant

as Etudiant_ID, Nom, Prénom, Email (Etudiant)



Chap VII - SQL Structured Query Language (normalisé en 1986)

• SQL2/SQL92 : standard adopté en 1992

• SQL3/SQL99 : extension de SQL2 avec "gestion" d’objets, déclencheurs …

• SQL2003: auto-incrémentation des clés, colonne calculée, prise en compte de

XML, …

• SQL2008: correction de certains défauts et manques (fonctions, types,

curseurs…)

SQL :

• Langage de Manipulation de Données (DML) : interroger et modifier les

données de la base

• Langage de Définition de Données (DDL) : définir le schéma de la base de

données

• Langage de contrôle d’accès aux données (DCL) : pour définir les privilèges

d’accès des utilisateurs

• Langage de contrôle des transactions (TCL) : pour gérer les transactions.

86


DML

87

SELECT [DISTINCT] *

FROM table_1 [synonyme_1], table_2 [synonyme_1], …

[WHERE prédicat_1

AND [ou OR] prédicat_2 …]

SELECT [DISTINCT] exp_1 [AS nom_1], exp_2 ...

FROM table_1 [synonyme_1], table_2 [synonyme_1], …

[WHERE prédicat_1

AND [ou OR] prédicat_2 …]

Chap. VII - SQL


DML

88

SELECT Nom, Prénom

FROM Etudiant

WHERE Ville = ‘ Paris ’ ;

SELECT Nom, Prénom

FROM Etudiant

WHERE Ville = ‘ Paris ’

AND Nom

LIKE ‘ _AR% ’;

SELECT Nom, Prénom

FROM Etudiant

WHERE Fax IS NULL;

Chap. VII - SQL

SELECT Intitulé,

(NbSeances*3) AS NbHeures

FROM Cours

WHERE (NbSeances*3)

BETWEEN 24 AND 27 ;

SELECT Nom, Prénom

FROM Enseignant

WHERE Département_ID IN

(‘ INFO ’, ‘ MATH ’, ‘ ECO ’)


DML

89

Prédicats du WHERE de la forme :

exp1 = exp2 exp op ANY (SELECT …)

exp1 != exp2 exp op ALL (SELECT …)

exp1 > exp2 avec op tel que =, !=, <, > ...

exp1 < exp2

exp1 <= exp2 exp IN (SELECT …)

exp1 >= exp2 exp NOT IN (SELECT …)

exp1 BETWEEN exp2 AND exp3

exp1 LIKE exp2

exp1 IN (exp2, exp3, …)

exp1 NOT IN (exp2, exp3, …)

exp1 IS NULL

exp1 IS NOT NULL

Chap. VII - SQL

SELECT Intitulé,

FROM Cours

WHERE NbSeances <=

( SELECT AVG(NbSeances)

FROM Cours);


DML

90

Chap. VII - SQL

Clause EXISTS :

• Retourne VRAI si au moins un nuplet est retourné par la requête

• FAUX si aucun nuplet n’est retourné.

• La valeur NULL n’a aucun effet sur le booléen résultat

SELECT Nom, Prénom

FROM Enseignant E

WHERE NOT EXISTS

( SELECT *

FROM Reservation_Salle S

WHERE S.Enseignant_ID = E.Enseignant_ID

);


DML

91

Chap. VII - SQL

Fonctions statistique et de groupage :

COUNT, MIN, MAX, AVG, SUM, EVERY, …

ORDER BY, GROUP BY

SELECT COUNT(*)

FROM Etudiant ;

SELECT AVG(Capacité), SUM(Capacité)

FROM Salle ;

SELECT Département_ID, Nom, Prénom

FROM Enseignant

ORDER BY Département_ID DESC, Nom, Prénom ;

SELECT Département_ID, COUNT(*)

FROM Réservation_Salle

GROUP BY Département_ID HAVING COUNT(*) >=4 ;


DML

92

Chap. VII - SQL

Fonctions statistique et de groupage :

EVERY retourne un booléen qui est vrai si toutes les occurrences sont vraies

SELECT date_resa, COUNT(*) FROM Reservation

GROUP BY date_resa

HAVING EVERY(date_resa <= CURRENT_DATE)

SELECT date_resa , COUNT(*) FROM Reservation

GROUP BY date_resa

HAVING EVERY(date_resa >= CURRENT_DATE)

SELECT EVERY(date_resa >= CURRENT_DATE)

FROM Reservation

SELECT EVERY(date_resa <= CURRENT_DATE)

FROM Reservation


DML

93

Chap. VII - SQL

Jointure : forme générale

SELECT Nom, Prénom, Nom_Departement

FROM Enseignant E, Departement D

WHERE E.Departement_ID = D.Departement_ID ;

Jointure interne :

SELECT Nom, Prénom, Nom_Département

FROM Enseignant INNER JOIN Département

ON Enseignant.Departement_ID = Departement.Departement_ID ;


DML

94

Chap. VII - SQL

Jointure externe :


FROM Enseignant LEFT OUTER JOIN Departement ON

Enseignant.Departement_ID = Derpartement.Departement_ID ;

S’il existe des enseignants attaché à aucun département, la valeur de Département_ID sera NULL.

En SQL2 : [RIGHT | LEFT | FULL] OUTER JOIN

Jointure naturelle :


FROM Enseignant NATURAL JOIN Département

Produit cartésien:


FROM Enseignant CROSS JOIN Département


DML

95

Chap. VII - SQL

Opérateurs ensemblistes : sous Oracle

SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ INFO ’

INTERSECT

SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ MATH ’


UNION


ORDER BY Nom,Prénom


MINUS


MINUS = EXCEPT en standard SQL2


DML

96

Chap. VII - SQL

Division : Livre(ISBN, Titre, Editeur)

Emprunt(EmpruntID,ISBN, DateEmprunt,EtudiantID)

Etudiant(EtudiantID,Nom, Prenom)

« Quels livres ont été empruntés par tous les étudiants? »

{t.Titre / Livre(t) [ u ( Etudiant (u) ) v

( v Emprunt(v)

(v.Etudiant_ID=u.Etudiant_ID) (v.ISBN=t.ISBN )

)

]

}

SELECT t.Titre FROM Livre t WHERE NOT EXISTS

( SELECT * FROM Etudiant u WHERE NOT EXISTS

( SELECT * FROM Emprunt v

WHERE u.EtudiantID=v.EtudiantID AND

v.ISBN=t.ISBN

)

) ;

{t.Titre / Livre(t) [ u Etudiant (u)

( v Emprunt(v)

(v.Etudiant_ID=u.Etudiant_ID) (v.ISBN=t.ISBN )

)

]

} Il n’y a pas de mot-clé

"quel que soit " en SQL2


DML

97

Chap. VII - SQL

• Insertion

INSERT INTO table(col1, col2, … coln)

VALUES (val1, val2, … valn)

INSERT INTO table(col1, col2, … coln)

SELECT

• Suppression

DELETE FROM table

WHERE prédicat

• Mise à jour

UPDATE table

SET col1 = exp1, col2 = exp2 WHERE prédicat

• Transactions : COMMIT, ROLLBACK [TO], SAVE POINT


DDL

98

Chap. VII - SQL

CREATE TABLE table (col1 type 1 [NOT NULL] ,

col2 type2 [NOT NULL] …

)

Contraintes :

CONSTRAINT nom_contrainte

PRIMARY KEY (liste attributs clé primaire)

| NOT NULL immédiatement après la déclaration de l’attribut

| CHECK (condition) après la déclaration de l’attribut

| UNIQUE après la déclaration de l’attribut

| FOREIGN KEY (clé étrangère)

REFERENCES nom_table (liste-colonne)

CREATE TABLE table

AS SELECT ...


DDL

99

Chap. VII - SQL

CREATE TABLE Enseignant

(

Enseignant_ID integer,

Departement_ID integer NOT NULL,

Nom varchar(25) NOT NULL,

Prenom varchar(25) NOT NULL,

Grade varchar(25)

CONSTRAINT CK_Enseignant_Grade

CHECK (Grade IN ('Vacataire', 'Moniteur','ATER', 'MCF', 'PROF')),

Telephone varchar(10) DEFAULT NULL,

Fax varchar(10) DEFAULT NULL,

Email varchar(100) DEFAULT NULL,

CONSTRAINT PK_Enseignant PRIMARY KEY (Enseignant_ID),

CONSTRAINT "FK_Enseignant_Departement_ID"

FOREIGN KEY (Departement_ID)

REFERENCES Departement (Departement_ID)

ON UPDATE RESTRICT ON DELETE RESTRICT

);

Contrainte

de domaine

Définition de la

clé primaire

Définition d’une clé

étrangère

10

0

CREATE TABLE Reservation

(

Reservation_ID integer,

Batiment varchar(1) NOT NULL,

Numero_Salle varchar(10) NOT NULL,

Enseignement_ID integer NOT NULL,

Departement_ID integer NOT NULL,

Enseignant_ID integer NOT NULL,

Date_Resa date NOT NULL DEFAULT CURRENT_DATE,

Heure_Debut time NOT NULL DEFAULT CURRENT_TIME,

Heure_Fin time NOT NULL DEFAULT '23:00:00',

Nombre_Heures integer NOT NULL,

CONSTRAINT PK_Reservation PRIMARY KEY (Reservation_ID),

CONSTRAINT "FK_Reservation_Salle" FOREIGN KEY (Batiment,Numero_Salle) REFERENCES

Salle (Batiment,Numero_Salle) ON UPDATE RESTRICT ON DELETE RESTRICT,

CONSTRAINT "FK_Reservation_Enseignement" FOREIGN KEY (Enseignement_ID,Departement_ID)

REFERENCES Enseignement (Enseignement_ID,Departement_ID) ON UPDATE RESTRICT ON

DELETE RESTRICT,

CONSTRAINT "FK_Reservation_Enseignant" FOREIGN KEY (Enseignant_ID) REFERENCES

Enseignant (Enseignant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT,

CONSTRAINT CK_Reservation_Nombre_Heures CHECK (Nombre_Heures >=1),

CONSTRAINT CK_Reservation_HeureDebFin

CHECK (Heure_Debut < Heure_Fin)

);


DDL

10

1

Chap. VII - SQL

MATCH SIMPLE/PARTIAL/FULL: CREATE TABLE inscription(

inscription_id serial NOT NULL,

etudiant_id integer,

enseignement_id integer,

master_id integer,

CONSTRAINT pk_inscription PRIMARY KEY (inscription_id ),

CONSTRAINT "FK_Inscription_Enseignement"

FOREIGN KEY (enseignement_id, master_id)

REFERENCES enseignement (enseignement_id, master_id)

MATCH SIMPLE

ON UPDATE RESTRICT ON DELETE RESTRICT,

CONSTRAINT "FK_Inscription_Etudiant" FOREIGN KEY (etudiant_id)

REFERENCES etudiant (etudiant_id) MATCH SIMPLE

ON UPDATE RESTRICT ON DELETE RESTRICT,

CONSTRAINT "UN_Inscription" UNIQUE (etudiant_id ,

enseignement_id , master_id )

)


DDL

10

2

Chap. VII - SQL

MATCH SIMPLE/PARTIAL/FULL :

• MATCH SIMPLE (par défaut) :

violation si toutes les valeurs sont à la fois divergentes et renseignées

INSERT INTO INSCRIPTION VALUES (3,2,NULL,NULL) OK

INSERT INTO INSCRIPTION VALUES (4,2,3,NULL) OK

même si pas d’enseignement_id 3 dans la table enseignement

INSERT INTO INSCRIPTION VALUES (5,2,NULL,1) OK

INSERT INTO INSCRIPTION VALUES (6,2,3,4) KO

car la clé (enseignement_id, master_id)=(3, 4) n'est pas présente dans la table enseignement

• MATCH PARTIAL : violation si une seule des valeurs divergent

INSERT INTO INSCRIPTION VALUES (4,2,3,NULL) KO

car pas d’enseignement_id 3 dans la table enseignement

• MATCH FULL : violation si toutes les valeurs divergent ou si une colonne est renseignée et l’autre non

INSERT INTO INSCRIPTION VALUES (3,2,NULL,NULL) OK

INSERT INTO INSCRIPTION VALUES (4,2,1,NULL) KO

même si enseignement_id 1 existe dans la table enseignement


DDL

10

3

Chap. VII - SQL

Héritage de table (norme SQL99) :

CREATE TABLE Personne

( PID Integer,



CONSTRAINT PK_Personne PRIMARY KEY (PID),

)

Tables héritées de Personne :

CREATE TABLE Etudiant UNDER Personne

( NumCarte varchar(25) NOT NULL)

CREATE TABLE Enseignant UNDER Personne

( Grade varchar(25)

CONSTRAINT CK_Enseignant_Grade

CHECK (Grade IN ('Vacataire', 'Moniteur','ATER', 'MCF', 'PROF'))

)


DDL

10

4

Chap. VII - SQL

Colonne calculée (norme SQL2003) :

CREATE TABLE Etudiant

(

Etudiant_ID integer,

Ville varchar(25),



Date_Naissance date NOT NULL,

Rue varchar(50) DEFAULT NULL,

CP varchar(9) DEFAULT NULL,

Adresse GENERATED ALWAYS AS (rue || ‘ ‘ || CP || ‘ ‘ || Ville)

Telephone varchar(10) DEFAULT NULL,

Fax varchar(10) DEFAULT NULL,

Email varchar(100) DEFAULT NULL,

CONSTRAINT PK_Etudiant PRIMARY KEY (Etudiant_ID)

);


DDL

10

5

Chap. VII - SQL

CREATE ASSERTION <nom contrainte>

[ {BEFORE COMMIT |

AFTER {INSERT | DELETE | UPDATE[OF (Attributs)]} ON

<Relation>} …]

CHECK <Condition>

[FOR [EACH ROW OF] <Relation> ]

CREATE ASSERTION CA_Place_Université

BEFORE COMMIT

CHECK( (SELECT SUM(Capacité) FROM Salle)

>= (SELECT COUNT(*) FROM Etudiant)

)


DDL

10

6

Chap. VII - SQL

CREATE [OR REPLACE] TRIGGER nom {BEFORE | AFTER}

événement_déclencheur ON nom_table

[FOR EACH ROW]

[WHEN (condition) ]

bloc PL/SQL sous Oracle

| inst_de_suppr | inst_de_modif | instr_d_ajout | ERROR en SQL2

événement_déclencheur = INSERT, UPDATE, DELETE

• Déclencheur de niveau instruction : pas de clause FOR EACH ROW

• Déclencheur de niveau ligne : variables liens :new et :old

– INSERT : valeurs à insérer dans :new.nom_colonne

– UPDATE : valeur originale dans :old.nom_colonne, nouvelle valeur dans

:new.nom_colonne

– DELETE : valeur en cours de suppression :old.nom_colonne


DDL

10

7

Chap. VII - SQL

CREATE OR REPLACE TRIGGER Enseignant_Actif

BEFORE DELETE ON Enseignant

FOR EACH ROW

declare

counter number;

begin

SELECT count(*) INTO counter

FROM Enseignements

WHERE Enseignant_ID = :old.Enseignant_ID;

if counter > 0 then

raise_application_error (-20800, ’Enseignant actif ne

pouvant être supprimé');

end if;

end;


DDL

10

8

Chap. VII - SQL

CREATE OR REPLACE TRIGGER UPD_salaire_personnel

BEFORE UPDATE salaire ON Personnel

FOR EACH ROW

WHEN (:old.salaire > :new.salaire)

declare

salaire_diminution EXCEPTION;

begin

raise salaire_diminution ;

when salaire_diminution then

raise_application_error(-20001, ’Le salaire ne peut pas

diminuer ’)

end;


DDL

10

9

Sous PostgreSQL :

Chap. VII - SQL

CREATE OR REPLACE FUNCTION GetSalleCapaciteSuperieurA(int)

RETURNS SETOF Salle

AS '

SELECT * FROM Salle WHERE Capacite > $1;

'

LANGUAGE SQL;

SELECT * FROM GetSalleCapaciteSuperieurA(300) ;

11

0

CREATE OR REPLACE FUNCTION FunctionTriggerReservation()

RETURNS trigger AS

' DECLARE

resa Reservation.Reservation_ID%TYPE;

BEGIN

SELECT INTO resa Reservation_ID

FROM Reservation

WHERE …

IF FOUND THEN RAISE EXCEPTION ''Réservation impossible, salle

occupée à la date et aux horaires demandés'';

ELSE RETURN NEW;

END IF;

END;'

LANGUAGE 'plpgsql';

DDL Chap. VII - SQL



DDL

11

1

Sous PostgreSQL :

Chap. VII - SQL

CREATE TRIGGER InsertionReservation

BEFORE INSERT ON Reservation

FOR EACH ROW

EXECUTE PROCEDURE

FunctionTriggerReservation();


DDL

11

2

Chap. VII - SQL

ALTER TABLE table

ADD (col1 type1, col2 type2 …)

| MODIFY (col1 type1, col2 type2 …)

| DROP PRIMARY KEY

| DROP CONSTRAINT nom_contrainte

DROP TABLE table

CREATE VIEW vue (col1, col2)

AS SELECT …

DROP VIEW vue

CREATE [UNIQUE] INDEX nom_index ON table (col1,col …)


Embedded SQL

11

3

Chap. VII - SQL

Utilisation de commandes SQL à l’intérieur d’un langage hôte :

• Commandes SQL remplacées par des appels de fonctions

du langage hôte par le précompilateur.

• Commandes SQL reconnues par EXEC SQL

/* déclaration de variables hôtes */

EXEC SQL BEGIN DECLARE SECTION

char d_name[20];

char d_id;

EXEC SQL END DECLARE SECTION

...

EXEC SQL INSERT INTO Department

VALUES (:d_id, :d_name);


Embedded SQL

11

4

Chap. VII - SQL

Gestion des erreurs :

EXEC INCLUDE SQLCA;

...

EXEC SQL WHENEVER SQLERROR GOTO erreur

...

erreur :

printf(``erreur : les transactions en

cours vont être annulées'\n'');

EXEC SQL ROLLBACK WORK RELEASE;

exit(1);


Embedded SQL

11

5

Chap. VII - SQL

Gestion de curseur :

/* Déclaration d'un curseur pour manipuler la

table Department */

EXEC SQL DECLARE c1 CURSOR FOR

SELECT * FROM Department ;

/* Ouverture du curseur */

EXEC SQL OPEN c1;

/* Lecture de la première ligne de la table */

EXEC SQL FETCH c1 INTO :d_id, :d_name ;

printf(``Nom du département %s, identifiant :

%s\n'',d_name, d_id);

/* fermeture du curseur */

EXEC SQL CLOSE c1;


Middleware d’accès

aux bases de données

11

6

Chap. IV - SQL

• Architecture logicielle définissant une interface standard d’accès

aux SGBD

• A chaque SGBD correspond un pilote (driver)

Indépendance vis à vis du SGBD par simple configuration du

pilote

Possibilité pour un même programme d’interroger différentes

bases de données dans différents SGBD

1. Java DataBase Connectivity (JDBC)

2. Open DataBase Connectivity (ODBC) - Middleware propriétaire

(Windows)


JDBC/ODBC

11

7

Chap. IV - SQL

• Appel des fonctions de l’API :

lien entre l’application et le gestionnaire de pilotes

• Pilote = librairie contenant les appels JDBC/ODBC

et traduisant les requêtes en requêtes propres au

SGBD

• Gestionnaire de pilotes (ODBC) = librairie

chargeant les pilotes associés à chaque source de

données (BD + SGBD)


JDBC

11

8

Chap. IV - SQL

Exemple de programme Java sur la base exemple :

import java.sql.*; // Tout ce qui est nécessaire pour JDBC

import java.text.*; // Pour formater les dates

import java.util.*;

public class TestJDBCPostgresql

{

Connection db=null; // Variable de connexion

Statement sql=null; // Variable pour un ordre SQL

DatabaseMetaData dbmd; // Méta-données nécessaires

// au pilote.


JDBC

11

9

Chap. IV - SQL

public TestJDBCPostgresql(String argv[])

throws ClassNotFoundException, SQLException,

java.io.IOException

{ String database = argv[0];

String username = argv[1];

String password = argv[2];

// Récupération du pilote du SGBD (ici PostgreSQL)

Class.forName("org.postgresql.Driver");

// Connexion à la base de données

db = DriverManager.getConnection("jdbc:postgresql:"

+database, username,

password);

// Si AUTOCOMMIT=true => le SGBD exécute COMMIT

// après chaque requête

// Si AUTOCOMMIT= false => le SGBD attend un COMMIT

// explicite

db.setAutoCommit(false);


JDBC

12

0

Chap. IV - SQL

dbmd = db.getMetaData(); // Récuparation des méta-données

// Pour tester que la connexion est OK

System.out.println("Connection to SGBD «

+ dbmd.getDatabaseProductName()+ " version «

+ dbmd.getDatabaseProductVersion()+ " database "

+ dbmd.getURL()+ " \nusing " + dbmd.getDriverName()

+ " version "+ dbmd.getDriverVersion()+ " " +

"successful.\n");

// Création d’un ordre SQL pour créer une relation

sql = db.createStatement();

String sqlText = "CREATE TABLE … ";

System.out.println("Executing this command: "+sqlText+"\n");

sql.executeUpdate(sqlText);

// Ne pas oublier le COMMIT si AUTOCOMMIT = false

db.commit();

// Code équivalent pour un INSERT, UPDATE, DELETE


JDBC

12

1

Chap. IV - SQL

// Exemple de requête de sélection

ResultSet rset = sql.executeQuery("SELECT Capacité FROM

Reservation " + "WHERE Batiment='B' " + "AND

Numero_Salle='022'");

// Parcours des nuplets résultat

while (rset.next()) {

System.out.println("Nom de la table : (généralement vide

bug du driver)"

+ rset.getMetaData().getTableName(1));

System.out.println("Type de la colonne : "

+ rset.getMetaData().getColumnTypeName(1));

System.out.println("Nom de la colonne : "

+ rset.getMetaData().getColumnName(1) + "\n";

//Numérotation des colonnes à partir de 1 (et non de 0!!)

System.out.println("Capacité de la salle B020 : "

+ rset.getInt(1) + "\n");


JDBC

12

2

Chap. IV - SQL

// Création d’un ordre SQL paramétré

// Code SQL de la requête

sqlText = "INSERT INTO Salle VALUES (?,?,?)";

// Préparation de l’ordre SQL – non encore exécuté

//car il manque des paramètres

PreparedStatement ps = db.prepareStatement(sqlText);

String [] NumBatiment = {"A", "B", "C", "P", "D"};

String [] NumSalle = {"208", "026", "405", "340", "120"};

int lenNB = NumBatiment.length;

for (int i=0, c=30 ; (i<lenNB) && (c<35) ;c++,i++)

{ps.setString(1,NumBatiment[i]); // Paramètre 1

ps.setString(2,NumSalle[i]); // Paramètre 2

ps.setInt(3,c); // Paramètre 3

ps.executeUpdate(); // Exécution de l’ordre SQL

}

// Ne pas oublier le COMMIT!!

db.commit();

ps.close(); // Fermeture de la préparation de l’ordre


ODBC

123

Chap. VII - SQL

• Appel des fonctions de l’API ODBC :

lien entre application et gestionnaire de pilotes

• Gestionnaire de pilotes = DLL chargeant les pilotes

associés à chaque source de données (BD + SGBD)

• Pilote = DLL contenant les appels ODBC et traduisant

les requêtes en requêtes propres au SGBD

ODBC

124

Chap. VII - SQL



ODBC

125

Chap. VII - SQL

Exemple de programme en C sous Visual C++ :

#include <stdio.h>

#include <conio.h>

#include <afxdb.h> // MFC ODBC database classes

char *cBASE ; /* Nom de la source de données */

char *cLOGIN ; /* Login utilisateur */

char *cPASSWD ; /* Mot de passe utilisateur */

void main()

{

HENV d_env; /* Descripteur d'environnement */

HDBC d_connex; /* Descripteur de connexion */

HSTMT curseur; /* Curseur */

RETCODE retcode; /* Code de retour de fonction */

ODBC

126

Chap. VII - SQL

UCHAR ucLastName[20],ucCity[20];

SDWORD ceLastName,ceCity;

char *cREQUETESQL; /* Variable recevant une requête SQL */

/* Saisie du nom de la source de données */

cBASE=(char*)malloc(20);

printf("Nom de la base de donnees :"); scanf("%s",cBASE);

/* Saisie du login */

cLOGIN=(char*)malloc(20); printf("Login :");

scanf("%s",cLOGIN);

/* Saisie du password */

cPASSWD=(char*)malloc(20); printf("Mot de passe : ");

/* Pour ne pas afficher le mot de passe à l'écran */

int iPosCaractere=0; fflush(stdin);

do { if((cPASSWD[iPosCaractere]=_getch())!='\r')

printf("*");

} while(cPASSWD[iPosCaractere++]!='\r' && iPosCaractere <20);

cPASSWD[--iPosCaractere]='\0';


ODBC

127

Chap. VII - SQL

/* Création d'un environnement ODBC */

retcode = SQLAllocEnv(&d_env);

/* Si la création d'un environnement ODBC est correcte */

if (retcode == SQL_SUCCESS)

{

/* Création d'une connexion ODBC */

retcode = SQLAllocConnect(d_env, &d_connex);

/* Si la connexion ODBC s'est bien passée */


{

/* Initialisation du temps de connexion à 5 secondes. */

SQLSetConnectOption(d_connex, SQL_LOGIN_TIMEOUT, 5);

/* Connexion à une source de données */

retcode = SQLConnect(d_connex,(unsigned char*)cBASE,SQL_NTS,(unsigned

char*)cLOGIN,SQL_NTS,(unsigned char*)cPASSWD,SQL_NTS);

Longueur de la chaîne ou on indique que la chaîne se termine par le code NULL


ODBC

128

Chap. VII - SQL

/* Si la connexion à la source de données s'est bien passée */

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

printf("Connection a la base (source de données).\n");

/* Pause dans l'affichage */

printf("Taper une touche pour continuer \n"); getchar();

/* Allocation mémoire du curseur et association du curseur à la

source de données identifiée par d_connex. */

retcode = SQLAllocStmt(d_connex, &curseur);

/* Si l'allocation mémoire du curseur est correcte */


{ /* Création de la requête SQL */

cREQUETESQL = "SELECT Nom, Ville FROM Etudiant";

/* Execution directe de la requête sur la base */

retcode = SQLExecDirect(curseur, (unsigned char*)cREQUETESQL,

SQL_NTS);

printf("EXECUTION DE LA REQUETE, CODE ERREUR %d, CODE DE SUCCES

%d \n",retcode,SQL_SUCCESS);


ODBC

129

Chap. VII - SQL

/* Tant le parcours du curseur est valide */

while (retcode == SQL_SUCCESS)

{

/* Parcourt de l'enregistrement résultat de la requête */

retcode = SQLFetch(curseur);

/* Si le parcourt est incorrect */

if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)

{

printf("Erreur %d\n",SQL_ERROR);

}

/* Si le parcourt des enregistrements est correct */

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

/* récupération des données des colonnes 1 et 2 de la table

résultat */

SQLGetData(curseur, 1, SQL_C_CHAR, ucLastName, 30, &ceLastName);

SQLGetData(curseur, 2, SQL_C_CHAR, ucCity, 30, &ceCity);

/* Affichage du résultat */

printf("Etudiant : %s %s\n",ucLastName,ucCity);



Chap VIII - Dépendances fonctionnelles

• Dépendance fonctionnelle sur R

A1, A2, …, An B

"Si deux nuplets de R ont mêmes valeurs pour les attributs de A1, A2, …,

An alors ils ont même valeur pour les attributs de B. »

• Une dépendance A1, A2, …, An B1, B2, …, Bm est :

– triviale : si l’ensemble B1, B2, …, Bm est sous-ensemble de

A1, A2, …, An

– non triviale : si au moins un Bi n’appartient pas à

l’ensemble de A1, A2, …, An

– complètement non triviale : si aucun des Bi n’appartient à

l’ensemble de A1, A2, …, An

130


Règles

131

Chap. VIII - DF

Axiomes de Amstrong :

• Réflexivité : si Y X alors X Y

• Augmentation : Si X Y alors Z XZ YZ

• Transitivité :

Si X Y et Y Z alors X Z

On déduit :

• Union : {X Y, X Z} |= {X YZ}

• Pseudo-transitivité :

{X Y,WY Z} |= {XW Z}

• Décomposition :

Si X Y et Z Y alors X Z


Fermeture

132

Chap. VIII - DF

• Fermeture d’une famille de dépendances fonctionnelles

F+ = {X Y \ F |= X Y }

• Fermeture d’un ensemble d’attributs X par rapport à

une famille de dépendances fonctionnelles F

[X]+ = {A \ F |= X A}

• Lemme

La dépendance fonctionnelle X Y peut être déduite

des axiomes d’Amstrong si Y [X] +


Equivalence et Couverture

133

Chap. VIII - DF

• Deux familles de dépendances fonctionnelles F et G sont

équivalentes si F+ = G+

• Si F+ G+ alors G est une couverture de F

• Une famille de dépendances fonctionnelles F est

minimale si :

1. En partie droite de toute dépendance de F, il n’y a qu’un seul

attribut

2. Il n’y a pas de dépendance fonctionnelle X A dans F telle que

(F \ {X A }) soit équivalente à F

3. Il n’y a pas de dépendance fonctionnelle X A et Z X tels que

(F \ {X A }) {Z A } soit équivalente à F

Attention: pas d’unicité des couvertures minimales


Clé

134

Chap. VIII - DF

Soit R(A1, A2, …, An) et F une famille de DF sur R

• Un sous-ensemble X de {A1, A2, …, An} est une clé

minimale de R si

La dépendance fonctionnelle X A1, A2, …, An F+

Y X, on a pas Y A1, A2, …, An

• Si X n’est pas un ensemble minimal alors X est une surclé

• Les dépendances fonctionnelles permettent de déduire les clés

des relations


Chap IX - Décomposition de schéma

• Décomposition sans perte d’information (Lossless jointure)

La décomposition de R en R1, R2, .., Rn est sans perte

d’information si et seulement si r, instance de R :

r = R1(r) R2

(r) ... Rn(r)

• Décomposition sans perte de dépendances

La décomposition de R, munie d’une famille de

dépendances F, en R1, R2, .., Rn est sans perte de

dépendance si et seulement si :

F+ = F+R1

F+R2

... F+Rn

avec FRi = {X Y, XY Ri

et X Y F+}

135


Décomposition SPI

136

Chap. IX - Décomposition

•

•

? )(1iR

n

i rr

? )(1iR

n

i rr

)( deissu génériquenuplet un ,,..., Soit 121 iR

n

in r)a,a(at

nRn

R

R

rt

rt

rt

t

)(

...

)(

)( avec

t... t tdepartir àconstruit est

2

1

2

1

n21

? que-ce-Est rt

r


Décomposition SPD

137

Chap. IX - Décomposition

• Association d’une famille Fi à chaque sous-

relation Ri

• Calcul des Fi à partir de F+ :

X Y F+ et XY Ri X Y Fi

• Perte de dépendances

X Y F tq X Y

iF

X ][

])[(par ent itérativem calculé][ ji RRXXXiF


Chap X - Formes normales

Forme normale de Boyce-Codd (BCNF)

• Un schéma de relation est BCNF si et seulement si :

DF élémentaires telles que une clé détermine un attribut

• Un schéma de relation est BCNF si :

X A DF non triviale, A X :

X est une (sur)clé et A n’est pas un attribut de clé

• La forme normale BCNF évite la redondance d’information

• La décomposition de schéma BCNF sans perte d’information ne

préserve pas les dépendances fonctionnelles

138


3ème forme normale

139

Chap. X - Formes normales

• Un schéma de relation est 3NF si :

A, attribut une clé,

A ne dépend pas d’un ensemble d’attributs qui n’est pas une clé

(i.e. pas de dépendance transitive ni de dépendance partielle)

• X A est partielle si X est une partie de clé et A ne l’est pas

• X A est transitive si ni X ni A ne sont des parties de clé

• Si K est clé alors K X A est une chaîne non triviale

• Un schéma R, munie d’une famille F de DF, est 3NF si :

X A, DF non triviale de F :

X est une surclé ou A appartient à une clé


3ème forme normale

140


• La 3NF n’élimine pas le redondance d’information

• Objectif principaux de la décomposition de schéma :

–BCNF

–Non perte d’information

–Non perte de dépendance

On préférera sacrifier la forme normale BCNF

et avoir une 3NF SPI-SPD


1ère, 2ème et 3ème formes normales

141


• Un schéma de relation R est 1NF si :

attribut de R, il contient une valeur atomique

• Un schéma de relation R est 2NF si et seulement si :

– le schéma est en 1NF

– A, attribut une clé, A ne dépend pas d’une partie de clé

c-à-d ( une dépendance fonctionnelle partielle )

• Un schéma de relation R est 3NF si :

– le schéma est 2NF

– ( une dépendance fonctionnelle transitive )

Bases de données relationnelles Maude Manouvriermanouvri/BD/CoursBDRelationnelles_L3.pdf · Philip...

Documents

Transcript of Bases de données relationnelles Maude Manouvriermanouvri/BD/CoursBDRelationnelles_L3.pdf · Philip...