Transcript Diapositive 1

Bases de Données
Meltem Öztürk
Introduction
Modèle
Entité/Association
Modèle relationnel
Algèbre relationnelle
Algèbre relationnelle étendue
SQL
Bases de données

24h Cours, 24h TD, 12h TP

note finale= TP/5+4/5*sup(EX, (CC+EX)/2)

References:
 Ch. Date Introduction aux bases de données, Vuibert, Paris 2004
 G. Gardarin, Bases de Données - objet/relationnel, Eyrolles,
1999
 Polycopiés de Ph. Rigaux, M. Manouvrier et St. Gançarski
Introduction
Base de données:

collection 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

un gros ensemble d’informations
mémorisées sur un support permanent
structurées
Introduction
SGBD
Systèmes de Gestion de Bases de Données
(DataBase Management Systems - DBMS)
ensemble de logiciels systèmes permettant aux
utilisateurs de faire des applications (insérer,
modifier, et 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
Introduction
Exemples de SGBD:



BD d’université (données sur les étudiants,
les enseignements, les salles, etc.)
BD de compagnie aérienne (données sur les
clients, les vols, les réservations, etc.)
BD bancaire (données sur les clients, les
comptes, les transactions, etc.)
Problèmes en absence de SGB

Programmes d ’application écrits directement
au-dessus du système de gestion de fichier




redondance (coût de stockage et d ’accès)
incohérence (ex: changement d ’adresse)
difficulté d ’accès (requêtes non prévues dans les programmes )
isolation des données (nouveau programme qui cherche des
données dans des fichiers variés de différents formats)


manque de sécurité
gestion de l’intégrité (obéir à des contraintes)
Introduction
Principales fonctionnalités d’un SGBD:

Contrôle de la redondance d’information

Partage des données

Gestion des autorisations d’accès

Vérifications des contraintes d’intégrité
Contraintes structurelles (un employé a un seul chef), contraintes
dynamiques (un salaire ne peut diminuer), etc.

Sécurité et reprise sur panne
Introduction
Principaux composants d’un SGBD

Système de gestion de fichiers (physique)
Stockage et accès des fichiers…

Gestionnaire de requêtes
Traduction des requêtes des mis à jour et
d’interrogation

Gestionnaire de transactions
Regroupement des actions (modifications, mises à jour, etc.) qui doivent être
exécutées ensemble séquentiellement (ex : virement d’une somme de « A »
à « B », lire la somme de A, effacer de A, ajouter à B, etc. )
Introduction
Abstraction de données : 3 niveaux (abstraction
des données, indépendance entre utilisateurs et gestion):

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

Niveau externe ou vue
Introduction
Abstraction de données : 3 niveaux

Niveau interne ou physique

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
Introduction
Abstraction de données : 3 niveaux

Niveau interne ou physique

Niveau logique ou conceptuel

Niveau externe ou vue :
 propre à chaque utilisateur
 décrit par un ou plusieurs schémas externes
Introduction
Différents langages d ’un SGBD :

LDD : Langage de Définition de Données,


construire un schéma pour décrire la structure,
incluant les contraintes
LMD : Langage de Manipulation de Données

appliquer les opérations aux données (retrouver
et mettre à jour les données)
Introduction
Modèle de données (décrire les données, les relations entre
elles, leur sémantique, les contraintes d’intégrité, etc.):

Modèle conceptuel (entité/association)



Plus lisibles (graphiques)
Entité, association, spécialisation, attribut, identificateur,
etc.
Modèle logique (logique relationnel)


Plus facilement implantable
Relation, attribut, domaine, clé, n-uplet, etc.
Bases de Données
Introduction
Modèle
Entité/Association
Modèle relationnel
Algèbre relationnelle
Algèbre relationnelle étendue
SQL
Exemple de base de données
Exemple de base de données
Critiques sur notre exemple:

Anomalie lors d’une insertion :



Anomalie lors d’une modification


Insertion du même film plusieurs fois, et même avec différentes
données!
Comment distinguer un film d’un autre?
Si on modifie la date de naissance de Hitchcock?
Anomalie lors d’une destruction

Si on supprime un film, on supprime toutes données associées, y
compris celles du réalisateur
Bonne méthode:

représenter individuellement les films et les
réalisateurs (une action sur l’un n’entraîne pas
systématiquement une action sur l’autre)

méthode d ’identification d ’un film ou d ’un
réalisateur (permet d ’assurer que la même information est
représentée une seule fois)

préserver le lien entre les films et les
réalisateurs
Changeons notre modèle
Changeons notre modèle
Type d ’entité
Schéma + complet de notre exemple
Modèle Entité/Association

Entité : objet concret ou abstrait, identifiable, décrit par
l’information et pertinent pour l’application


Une entité est représentée par un ensemble d’ attributs
qui la décrive.


Ex : Vertigo (film), Hitckcock (réalisateurs)
Ex : film décrit par le nom, l’année de création, …
Chaque attribut a un domaine qui correspond à
l’ensemble des valeurs qu’il peut prendre
 Ex : les années compris entre 1920-2005
Type d’entité
Modèle Entité/Association

Clé (Identificateur) : Un sous-ensemble d’attribut permettant
d’identifier l’entité de manière unique.

sa valeur doit être connue pour toute entité


on ne doit jamais avoir besoin de le modifier
sa taille de stockage doit être la plus petite possible

Exemple:


internaute: e-mail
film: nom+pays+année?

Identificateur abstrait: numéro séquentiel qui sera incrémenté au fur et à
mesure des insertions
Modèle Entité/Association

Une association binaire entre les ensembles d ’entités E1 et E2 est
un ensemble de couple (a,b) avec aE1 et b E2
 Ex: Hitchcock a réalisé Vertigo

Cardinalité d’une association : nombre d’entités que l’association
relie.
 Noté (Min, Max)
 « * » : « 0. * », « 1 »: « 1.1 »

Une association peut avoir des attributs
Format Merise
Un automobiliste
possède entre zéro et
N voitures
Une voiture a un et
un seul propriétaire
Modélisation UML
Un automobiliste peut
apparaître entre zéro et N
fois dans l ’association
Une voiture peut
apparaître une et une
seule fois dans
l ’association
Attribut d’une association
Clé d ’une association

Clé d’une association : couple formé des clés des deux
entités

ex: l ’association « note » entre internaute et film!

Comment faire si l ’internaute veut donner différentes notes pour
le même film à des dates différentes? (pas de liens multiples!!)

Solution : ajouter un nouveau type d ’entité: date
Association n-aires
Entité faible
Chaque salle est notée
par un numéro
Il y a tant de numéro que
le nombre de salles
Numérotation
indépendant du cinéma
Entité faible
Chaque salle a un
numéro unique dans un
cinéma donné
Ex. Salle 1 du cinéma A
et Salle 1 du cinéma C
Pour distinguer une salle
d’une autre, il faut
connaître le cinéma
auquel elle est rattachée
Association n-aire

Une association n-aire entre n types entités E1, E2, …, En est un
ensemble de n-uplets (e1,e2, …,e3) avec eiEi pour tout i.



difficile à comprendre
cardinalité ambiguë : explicitement de type « 0..* »
clé?

Ex: [nomcinéma, noSalle, idFilm, idHorraire]?



Contrainte du type « dans une salle pour une horaire donnée il n ’y
a qu ’un seul film »… [nomcinéma, noSalle, idHorraire]?
« Connaissant le film et l ’horaire je connais la salle »: [idFilm,
idHorraire]?
Donc on a des clés candidates
Association n-aire (cardinalité « 0.*», clé ?)
Association n-aire (cardinalité « 0.*», clé ?)

Remplacer l ’association par un type d ’entité!

Règles:


on attribue un identifiant autonome à l’association
on crée une association Ai de type «1.1» « 0. *»
entre l ’association et chacun des types d ’entité.
Association ternaire - - > entité
Généralisation/Spécialisation
(E/A - Merise)
Ensemble d’entités
généralisantes
Ensemble d’entités
généralisées
Héritage (UML)
Classe mère / Sur-classe
Classes dérivées ou filles
/ sous-classes
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
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. "
Dépendances fonctionnelles
Un attribut (ou un groupe d'attributs) Y dépend
fonctionnellement d'un attribut (ou groupe
d'attributs) X si :
étant donné une valeur de X, il lui correspond une
valeur unique de Y ( l'instant considéré)
XY : Y dépend fonctionnellement de X
ou X détermine Y
Déclaration des dépendances
schéma conceptuel
au
niveau
du
Exemple de dépendances fonctionnelles
identificateur
Tous les autres attributs
Immatriculation  Marque, Type, Puissance, Année
Marque, Type, Puissance, Année  Immatriculation
Type  Marque
Ex. Le type "Twingo" sera toujours
associé, dans la base de données, à la
marque "Renault".
(Des)Avantages du modèle E/A

Avantages



Que 3 concepts: entités, associations, attributs
Représentation graphique et rapide
Désavantages

Pas de règle absolue pour déterminer qui est
attribut, entité ou association…
Bases de Données
Introduction
Modèle
Entité/Association
Modèle relationnel
Algèbre relationnelle
Algèbre relationnelle étendue
SQL
Modèle relationnel
La relation du « nom » Film :
Film (titre: string, année: number, genre : string)
domaine
Attribut
N-uplet (tuple)
entité
Modèle relationnel

Domaine : ensemble d’instance d’un type
élémentaire (en extension ou en intension)

Ex : réels, boolean, chaine de caracteres, etc.

Attribut : colonne d’une relation, associé a un
domaine

Schéma de relation : nom suivi de la liste des
attributs, chq attribut étant associé à son domain

R(A1 : D1, A2 : D2, …, An : Dn)

Ex : Film (titre: string, année: number, genre : string)
Modèle relationnel

Relation (R) : sous-ens. fini du produit
cartésien des domaines des attributs de R
(produit cartésien D1xD2x…xDn est l ’ens. De tous les tuples (v1,
…vn) où vi Di)






représentée par une table à 2 dimensions
colonne = domaine du produit cartésien
même domaine peut apparaître +ieurs fois
ensemble de tuplets sans doublons (pas 2 fois même
ligne)
ordre des lignes n’a pas d’importance
pas de case vide dans la table
Modèle relationnel

Clé d’une relation : le + petit sous-ens. des
attributs qui permet d’identifier chq ligne d’une
manière unique


Tuple (n-uplet) : une liste de n valeurs (v1, …vn), où
chq vi est la valeur d’un attribut Ai du domain Di


ex : film (titre, année, genre)
ex : (‘Cyrano’, 1992, ‘Rappeneau’)
Base de données : ensemble fini de relations.
Passage de E/A au relationnel
Règles de passages (1)

Règles générales – Entité :
1.
2.
3.
On crée une relation de même nom que l’entité.
Chaque propriété de l’entité, y compris l’identifiant,
devient un attribut de la relation.
Les attributs de l’identifiant constituent la clé de la
relation.
Ex:




Film (idFilm, titre, année, genre, résumé)
Artiste (idArtiste, nom, prénom, annéeNaissance)
Internaute (email, nom, prénom, région)
Pays (code, nom, langue)
Règles de passages (2)
Association de un à plusieurs :
1.
2.
3.
4.
B est en association avec plusieurs A et A est en relation
avec un seul B.
B est dit « père », A est dit « fils » (A: film, B: réalisateurs)
On crée les relations Ra et Rb correspondant
respectivement aux entités A et B.
L’identifiant de B devient un attribut de Ra (id du père devient
attribut de son fils)
ex:



Film (idFilm, titre, année, genre, résumé, idArtiste,
codePays)
Artiste (idArtiste, nom, prénom, annéeNaissance)
Pays (code, nom, langue)
Exemple de passage
Règles de passage (3)
Association avec entité faible : même que le
passage des associations du type de un à
plusieurs
Ex:


Cinéma (nomCinéma, numéro, rue, ville)
Salle (nomCinéma, no, capacité)
Règles de passage (4)
Association binaire de plusieurs à plusieurs :
1.
2.
3.
4.
5.
On crée les relations Ra et Rb des entités A et B.
On crée une relation Ra+b pour l’association
La clé de Ra et la clé de Rb deviennent des attributs de
Ra+b
La clé de cette relation est la concaténation des clés des
relations Ra et Rb
Les propriétés de l’association deviennent des attributs de
Ra+b
Ex :
 Film (idFilm, titre, année, genre, résumé, idMES,
codePays)
 Artiste (idArtiste, nom, prénom, annéeNaissance)
 Rôle (idFilm, idActeur, nomRôle)
Exemple de passage
Ex :
 Film (idFilm, titre, année, genre, résumé, idMES,
codePays)
 Artiste (idArtiste, nom, prénom, annéeNaissance)
 Role (idFilm, idActeur, nomRôle)
Règles de passage (5)
Association ternaire :
1. Même principe d’une association binaire:




Salle (nomCinéma, no, capacité)
Film (idFilm, titre, année, genre, résumé, idMES, codePays)
Horaire (idHoraire, heureDébut, heureFin)
Séance (idFilm, nomCinéma, noSalle, idHoraire, tarif)
Exemple de passage

Problème avec notre exemple : même salle présente
deux films différents au même horaire




Salle (nomCinéma, no, capacité)
Film (idFilm, titre, année, genre, résumé, idMES,
codePays)
Horaire (idHoraire, heureDébut, heureFin)
Séance ( nomCinéma, noSalle, idHoraire, tarif)
Clé de la relation est un sous-ensemble de la
concaténation des clés
Bases de Données
Introduction
Modèle
Entité/Association
Modèle relationnel
Algèbre relationnelle
Algèbre relationnelle étendue
SQL
Algèbre relationnelle
Algèbre : ensemble d’opérateurs manipulant
des expressions dont le résultat est une
expression qui peut être manipulée …
Algèbre relationnelle : ensemble d’opérateurs
prenant en entrée une ou deux expressions
relationnelles et produise une expression
relationnelle à la sortie.
Algèbre relationnelle

La définition de l ’algèbre relationnelle nous
permet de faire des opérations soit sur une
relation soit entre deux relations. Le résultat
étant aussi une relation, nous pouvons
composer des opérations et construire des
expressions algébriques complexes.

Requête: expression algébrique qui
s’applique à un ensemble de relations et
produit une relation finale.
Algèbre relationnelle

Opérateurs fondamentaux



Opérateurs unaires
 Sélection
 Projection
Opérateurs binaires
 Produit cartésien
 Union
 Différence
Opérateurs dérivés


Jointure
…
Exemple:organisme de voyage




Station (nomStation, capacité, lieu, région, tarif)
Activite (nomStation, libellé, prix)
Client (id, nom, prénom, ville, région, solde)
Séjour (idClient, station, début, nbPlaces)
Algèbre relationnelle
La sélection (F ( R )) : extrait de la relation R les
tuples qui satisfont la critère de sélection F.

F (formule logique): comparaison entre





un attribut de la relation R
constante
Les comparateurs de F (=, <, >, etc.)
le résultat est une relation ayant les mêmes attributs que R
composer plusieurs comparaisons par des connecteurs
logiques (ou, et, non, etc.)
Ex : toutes les stations aux Antilles:

région=‘Antilles’
(Station)
Algèbre relationnelle
La projection (  A 1 , A2, …, Ak ( R )): ne garde que
les attributs A1, A2 , …, Ak
Ex : les noms des stations et leur région / seulement les
régions (pas de doublure de ligne)
nomStation, région(Station)
 région(Station)
Algèbre relationnelle

Combinaison de projection et sélection:
ex : nomStation((libellé=plongée)  (prix<140) (Activité) )
Algèbre relationnelle
L’union (R  S) : crée une relation comprenant
tous les tuples existants dans l’une ou l’autre des
relations R et S.
 R et S doivent avoir le même schéma! (c.à.d
mêmes attributs)

ex: réunir ensemble les noms et prénoms des
travailleurs et des clients:
nom, prénom (travailleurs)  nom, prénom (clients)
Algèbre relationnelle
La différence (R - S) : crée une relation
comprenant tous les tuples de R qui ne sont pas
dans S

R et S doivent avoir le même schéma! (c.à.d mêmes
attributs)

ex: les noms et prénoms des gens qui ne sont
pas clients
nom, prénom (personne)  nom, prénom (clients)
Algèbre relationnelle
Produit cartésien (R x S) : crée une nouvelle
relation où chaque tuple de R est associé à
chaque tuple de S.
Exemple:Station x Activité
Algèbre relationnelle
Conflit de nom avec produit cartésien: nomStation
appartient en même temps à la relation Station et à la
relation Activité!
Alors on écrit: S.nomStation et A.nomStation
ou on fait un renommage
Le renommage ( r AA’, B B’ ) : renomme l’attribut
A en A’ et B en B’
Algèbre relationnelle
La jointure ( ) : sélection + produit cartésien
Ex: informations sur les stations et leur activité
S.nomStation=‘A.nomStation’ (Station x Activité)
Station ( nomStation=nomStation ) Activité
Expression de requêtes par des exemples

(1) comment faire l ’intersection

Sélection généralisée

(2) les stations qui sont aux Antilles et dont la capacité est
supérieure à 200 :

(3) les stations qui sont aux Antilles, ou dont la capacité est
supérieure à 200 :
(4) les stations dont la capacité est supérieure à 200 mais qui ne
sont pas aux Antilles :

Expression de requêtes par des exemples

Requêtes conjonctives







(5) le nom des stations aux Antilles :
(6) le nom et prénom des clients européens
(7) le nom et la région des stations où l’on pratique la voile:
(8) le nom des clients qui sont allés à Passac :
(9) quelles régions a visité le client 30 :
(10) le nom des clients qui ont eu l’occasion de faire de la voile :
(11) les noms des clients qui sont partis en vacances dans leur
région, ainsi que le nom de cette région :
Expression de requêtes par des exemples

Requêtes avec  et 




Complément d ’un ensemble


(12) quelles sont les stations qui ne proposent pas de voile ?
(13) le nom des régions où il y a des clients, mais pas de station :
(14) le nom des stations qui n’ont pas reçu de client américain :
(15) l’Id des clients qui ne sont pas allés aux Antilles :
(16) les ids des clients et les noms des stations où ils ne sont pas
allés.
Quantification universelle


(17) quelles sont les stations dont toutes les activités ont un prix
supérieur à 100 ?
(18) les ids des clients qui sont allés dans toutes les stations :
Bases de Données
Introduction
Modèle
Entité/Association
Modèle relationnel
Algèbre relationnelle
Algèbre relationnelle étendue
SQL
Extension des opérations


Projection généralisée: étend la projection
par ajout de fonctions arithmétiques
F1, F2, …, Fn (E):
 Fi : expressions arithmétiques sur les
constantes ou attributs


E: relation
Ex: Compte(Id, Nom_client, debit, credit)

Nom_Client, (Crédit - Débit) (Compte)
Extension des opérations

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)
Exemple
Exemple
Exemple
Fonctions d’agrégation

Sum : permet de faire la somme (entier ou réel)


Sum capacité (Station)
Avg : calcule la moyenne de valeurs (entier ou
réel)

Avg solde (Client)
Fonctions d’agrégation

Count : retourne le nombre d’éléments dans
la collection


Count id (Client)
Count_distinct : identique à Count mais ne
tient pas compte des doublons

Count_distinct libellé (Activité)
Fonctions d’agrégation

Max : retourne la plus grande valeur de la
collection


Max capacité (Station)
Min : retourne la plus petite valeur de la
collection

Min prix (Activité)
Fonctions d’agrégation

Opération sur un ensemble de nuplets!
•
Nombre d’enseignants par départements :
•
Nom_DépartementCountEns_ID(EnseignantDépartement)
Mise à jour de la base

Insertion
RRe


Ex:
Salle  Salle  {(« B », « 038 », 15)}
Suppression
RR-e

Ex:
Salle  Salle - capacité  10 (Salle)
Mise à jour de la base

Mise à jour d’un nuplet : 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  « 45 12 45 86 », Fax, Email, NumAnnées
[(Etudiant.Etudiant_ID =r.Etudiant_ID) (Etudiant) ]
Vue
Table virtuelle dont le schéma et les instances sont dérivés de
la base réelle par une requête et qui est 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)
Bases de Données
Introduction
Modèle
Entité/Association
Modèle relationnel
Algèbre relationnelle
Algèbre relationnelle étendue
SQL
SQL
SQL : Structured Query Language (langage
structuré de requêtes)
•
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
SQL / types
SQL / création des tables
CREATE TABLE nom de la table
(attribut1 type(attribut1), attribut2 type(attribut2), …)
CREATE TABLE Internaute
(email VARCHAR (50),
nom VARCHAR (20),
prenom VARCHAR (20),
motDePasse VARCHAR (60),
anneeNaiss INTEGER (4))
SQL / création des tables

NOT NULL: il y a toujours une valeur!
CREATE TABLE Internaute
(email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss INTEGER (4))
SQL / création des tables
NULL: absence de valeur (pas valeur zéro ou
chaîne vide!!!)


Pas d’opération incluant le NULL
Pas de comparaison avec un NULL
Comment forcer un attribut?
Réponse: Par DEFAULT
CREATE TABLE Cinéma
(nom VARCHAR (50) NOT NULL,
adresse VARCHAR (50) DEFAULT ’Inconnue’)
SQL/ Contraintes

Contraintes qu’on peut demander:





Un attribut doit toujours avoir une valeur (NOT
NULL)
Un attribut (ou ensemble d’attributs) constitue(nt)
la clé de la relation
Un attribut dans une table est liée à la clé primaire
d’une autre table
La valeur d’un attribut doit être unique au sein de
la relation
D’autres types de règles…
SQL/ création des tables
Clé primaire (primary key),
CREATE TABLE Notation
(idFilm INTEGER NOT NULL,
email VARCHAR (50) NOT NULL,
note INTEGER DEFAULT 0,
PRIMARY KEY (idFilm, email))
SQL/ création des tables
Clé secondaire(unique)
CREATE TABLE Cinéma
(nom VARCHAR (30) NOT NULL,
Adresse VARCHAR (30) UNIQUE,
PRIMARY KEY (nom))
SQL/ création des tables
Clé secondaire(unique)
CREATE TABLE Artiste
(id INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
PRIMARY KEY (id),
UNIQUE (nom, prenom));
SQL / création des tables
Clé étrangère (Foreign key) : les attributs faisant
Reference une ligne d’une autre table
CREATE TABLE Film
(idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
SQL/création des tables

Modification??



Insertion dans Film avec une valeur inconnue
pour idMES
La destruction d’un artiste
La modification de id dans Artiste ou de idMES
dans Film
En cas de violation d’une contrainte d’integrité :
demande rejetée
SQL / création des tables
la destruction d’un metteur en scène déclenche la mise à NULL
(SET NULL) de la clé étrangère idMES pour tous les films qu’il a
réalisé:
CREATE TABLE Film
(titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste
ON DELETE SET NULL,
FOREIGN KEY (codePays) REFERENCES Pays);
SQL / création des tables
Quand on détruit un cinéma, on veut également détruire les salles ;
quand on modifie la clé d’un cinéma, on veut répercuter la
modification sur ses salles :
CASCADE appliquer la même opération
CREATE TABLE Salle
(nomCinema VARCHAR (30) NOT NULL,
no INTEGER NOT NULL,
capacite INTEGER,
PRIMAR KEY (nomCinema, no),
FOREIGN KEY (nomCinema) REFERENCES Cinema
ON DELETE CASCADE
ON UPDATE CASCADE)
SQL / création des tables
Enumération des valeurs possibles avec CHECK
CREATE TABLE Film
(titre VARCHAR (50) NOT NULL,
annee INTEGER
CHECK (annee BETWEEN 1890 AND 2000) NOT NULL,
genre VARCHAR (10)
CHECK (genre IN (’Histoire’,’Western’,’Drame’)),
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
SQL / Modification du schéma
ALTER TABLE nomTable ACTION description
Action :
 ADD: ajouter
 MODIFY: modifier
 DROP: supprimer
 RENAME: renommer
SQL / Modification du schéma
ALTER TABLE nomTable ACTION description
Action : ADD

ALTER TABLE Internaute ADD region
VARCHAR(10);
SQL / Modification du schéma
ALTER TABLE nomTable ACTION description
Action : MODIFY

ALTER TABLE Internaute MODIFY region
VARCHAR(30) NOT NULL;
SQL / Modification du schéma
ALTER TABLE nomTable ACTION description
Action : DROPP
ALTER TABLE Internaute DROP region;
SQL / création d’index
Index pour les clés primaires (systématique),les clés
secondaires (UNIQUE) ou les attributs normaux:
CREATE [UNIQUE] INDEX nomIndex ON nomTable
(attribut1 [, ...])
Ex :
CREATE UNIQUE INDEX idxNom ON Artiste (nom, prenom);
CREATE INDEX idxGenre ON Film (genre);
Exemple:organisme de voyage




Station (nomStation, capacité, lieu, région, tarif)
Activite (nomStation, libellé, prix)
Client (id, nom, prénom, ville, région, solde)
Séjour (idClient, station, début, nbPlaces)
SQL / Requêtes
SELECT nomStation
FROM Station
WHERE region = ’Antilles’

FROM indique la (ou les) tables dans lesquelles on trouve les
attributs utiles à la requête.

SELECT indique la liste des attributs constituant le résultat.

WHERE indique les conditions que doivent satisfaire les n-uplets
de la base pour faire partie du résultat.
SQL / Requêtes
SELECT libelle, prix
FROM Activite
WHERE nomStation = ’Santalba’
SELECT libelle, prix / 6.56
FROM Activite
WHERE nomStation = ’Santalba’
SELECT libelle, prix / 6.56, ’Cours de l’euro = ’, 6.56
FROM Activite
WHERE nomStation = ’Santalba’
SQL / Requêtes
SELECT libelle, prix / 6.56 AS prixEnEuros,
’Cours de l’euro = ’, 6.56 AS cours
FROM Activite
WHERE nomStation = ’Santalba’
SQL / Requêtes
Doublons :
SELECT libelle
FROM Activite

SELECT DISTINCT libelle
FROM Activite
SQL / Requêtes
SELECT *
FROM Station
Tri du résultat
(ascendant; pour descendant ajout DESC)
SELECT *
FROM Station
ORDER BY tarif, nomStation
SQL / Requêtes
Where :
AND, OR, NOT, <, <=, >, >=, <>, !=,BETWEEN

SELECT nomStation, libelle
FROM Activite
WHERE nomStation = ’Santalba’
AND (prix>50 AND prix<120)
SQL / Requêtes
Where :
AND, OR, NOT, <, <=, >, >=, <>, !=,BETWEEN

SELECT nomStation, libelle
FROM Activite
WHERE nomStation = ’Santalba’
AND prix BETWEEN 50 AND 120
SQL / Requêtes

Chaîne de caractères:

Attention: chaînes de longueur fixe différentes des
chaînes de longueur variable.

Si SQL ne distingue pas majuscules et
minuscules pour les mot-clés, il n’en va pas de
même pour les valeurs. Donc ’SANTALBA’ est
différent de ’Santalba’.
SQL / Requêtes

Chaîne de caractères:
LIKE : pattern matching
 ‘_’ : n’importe quel caractère
 ‘%’ : n’importe quelle chaîne de caractères
Ex:

SELECT nomStation
FROM Station
WHERE nomStation LIKE ’%a’
SELECT nomStation
FROM Station
WHERE nomStation LIKE ’V_____’
(se termine par ‘a’)
(commence par ‘v’ et a 6 caractères)
SQL/Requêtes


Dates: aaaa-mm-jj
ID des clients qui ont commencé un séjour en
juillet 98:
SELECT idClient
FROM Sejour
WHERE debut BETWEEN DATE ’1998-07-01’
AND DATE ’1998-07-31’
SQL/Requêtes

Valeurs nulles: NULL



Toute opération appliquée à NULL donne pour
résultat NULL.
Toute comparaison avec NULL donne un résultat
qui n’est ni vrai, ni faux mais une troisième valeur
booléenne, UNKNOWN.
TRUE=1, FALSE=0 et UNKNOWN=1/2.



x AND y=Min(x,y);
x OR y= Max(x,y);
NOT x=1-x
SQL/Requêtes
SELECT station
FROM Sejour
WHERE nbPlaces <= 10 OR nbPlaces >= 10
SQL/Requêtes

nbPlaces = NULL est incorrecte.
SELECT *
FROM Sejour
WHERE nbPlaces IS (NOT) NULL
SQL/Requêtes =ieurs tables
Afficher le nom des clients et les stations où ils ont
fait un séjour:
SELECT nom, station
FROM Client, Sejour
WHERE id = idClient
SQL/Requêtes =ieurs tables
Afficher le nom d ’une station, son tarif, ses
activités et leurs prix:

SELECT nomStation, tarif, libelle, prix
FROM Station, Activite
WHERE Station.nomStation = Activite.nomStation
SELECT S.nomStation, tarif, libelle, prix
FROM Station S, Activite A
WHERE S.nomStation = A.nomStation
SQL/Requêtes =ieurs tables
Afficher le nom des clients habitant a Paris, les
stations où ils ont séjourné avec la date et le tarif
pour chaque station:

SELECT nom, station, debut, tarif
FROM Client, Sejour, Station
WHERE ville = ’Paris’
AND id = idClient
AND station = nomStation
SQL/Requêtes =ieurs tables

Donner les couples de stations situées dans
la même région:
SELECT s1.nomStation, s2.nomStation
FROM Station s1, Station s2
WHERE s1.region = s2.region
SQL/Requêtes =ieurs tables

Donnez tous les noms de région dans la base:
SELECT region FROM Station
UNION
SELECT region FROM Client
Donnez les régions où l’on trouve à la fois des
clients et des stations:

SELECT region FROM Station
INTERSECT
SELECT region FROM Client
SQL/Requêtes =ieurs tables

Quelles sont les régions où l’on trouve des
stations mais pas des clients ?
SELECT region FROM Station
EXCEPT
SELECT region FROM Client
SQL/Requêtes imbriquées

Noms de stations où ont séjourné des clients parisiens:
SELECT station
FROM Sejour, Client
WHERE id=idclient
AND ville =‘ Paris ’
SELECT station
FROM Sejour
WHERE idclient IN (SELECT id
FROM Client
WHERE ville =‘ Paris ’)
SQL/Requêtes imbriquées

!!! Si on sait que la sous requête ramène un et un seul
tuple:
SELECT station
FROM Sejour
WHERE idclient = (SELECT id
FROM Client
WHERE ville =‘ Paris ’)
SQL/Requêtes imbriquées




EXISTS R. Renvoie TRUE si R n ’est pas vide, FALSE
sinon.
t IN R, est un tuple dont le type est celui de R. TRUE si t
appartient à R, FALSE sinon.
v cmp ANY R, où cmp est un comparateur SQL (<,>,=,
etc.). Renvoie TRUE si la comparaison avec au moins
un des tuples de la relation unaire R renvoie TRUE.
v cmp ALL R, où cmp est un comparateur SQL (<,>,=,
etc.). Renvoie TRUE si la comparaison avec tous les
tuples de la relation unaire R renvoie TRUE
SQL/Requêtes imbriquées

Où (station, lieu) ne peut-on pas faire du ski ?
SELECT nomStation, lieu
FROM Station
WHERE nomStation NOT IN
(SELECT nomStation FROM Activité
WHERE libelle = `ski`)
SQL/Requêtes imbriquées

Quelle station pratique le tarif le plus élevé?
SELECT nomStation
FROM Station
WHERE tarif >= ALL (SELECT tarif FROM Station)
SQL/Requêtes imbriquées

Dans quelle station pratique-t-on une activité
aux même prix qu ’à Santalba?
SELECT NomStation, libelle
FROM Activite
WHERE prix IN (SELECT prix FROM Activite
WHERE NomStation=`Santalba`)
SQL/ sous requêtes correllées

Quels sont les clients (nom, prénom) qui ont
séjourné a Santalba?
SELECT nom, prenom
FROM Client
WHERE EXISTS (SELECT `x` FROM Sejour
WHERE station = `Santalba`
AND
id=idClient)
SQL/ sous requêtes correllées

Dans quelle station pratique-t-on une activité
au même prix qu ’à Santalba?
SELECT nomStation
FROM Activite A1
WHERE EXISTS (SELECT `x` FROM Activite A2
WHERE nomStation=`Santalba`
AND
A1.libelle=A2.libelle
AND
A1.prix=A2.prix)
SQL/Agrégation




COUNT : compte le nombre de valeurs non
nulles.
MAX et MIN
AVG : calcule la moyenne des valeurs de la
colonne.
SUM : effectue le cumul.
SQL/Agrégation

Exemples:
SELECT COUNT(nomStation) AVG(tarif)
MIN(tarif) MAX(tarif)
FROM Station
Requête incorrecte:
SELECT nomStation, AVG(tarif) MIN(tarif) MAX(tarif)
FROM Station
SQL/Agrégation

Combien de place a reservé Mr Kerouac pour
l ’ensemble des séjours?
SELECT SUM (nbPlaces)
FROM Client, Sejour
WHERE nom=`Kerouac`
AND id=idClient
GROUP BY

Afficher les régions avec le nombre de
stations:
SELECT region, COUNT(nomStation)
FROM Station
GROUP BY region
GROUP BY

Afficher le nombre de places reservées par
client (nom):
SELECT nom, SUM(nbPlaces)
FROM Client, Sejour
WHERE id=idClient
GROUP BY id, nom
HAVING

Afficher le nombre de places réservées, par client,
pour les clients ayant réservé plus de 10 places:
SELECT nom, SUM(nbPlaces)
FROM Client, Séjour
WHERE id=idClient
GRUOP BY nom
HAVING SUM(nbPlaces)>=10
SQL / création des tables
Insertion des données avec INSERT INTO
CREATE TABLE Pays
(code VARCHAR (4) DEFAULT 0 NOT NULL,
nom VARCHAR (30) NOT NULL,
langue VARCHAR (30) NOT NULL,
PRIMARY KEY (code))
INSERT INTO Pays VALUES (0, ’Inconnu’, ’Inconnue’);
INSERT INTO Pays VALUES (1, ’France’, ’Français’);
INSERT INTO Pays VALUES (2, ’USA’, ’Anglais’);
INSERT INTO Pays VALUES (3, ’Allemagne’, ’Allemand’);
INSERT INTO Pays VALUES (4, ’Angleterre’, ’Anglais’);