Bases de Donn

1
Bases de DonnéesMeltem Öztürk

• Introduction
• Modèle Entité/Association
• Modèle relationnel
• Algèbre relationnelle
• Algèbre relationnelle étendue
• SQL

2
Bases de données

• 24h Cours, 24h TD, 12h TP
• note finale TP/54/5sup(EX, (CCEX)/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

3
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 dinformations structurées
  mémorisées sur un support permanent

4
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

5
Introduction

• Exemples de SGBD
• BD duniversité (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.)

6
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 lintégrité (obéir à des contraintes)

7
Introduction

• Principales fonctionnalités dun SGBD
• Contrôle de la redondance dinformation
• Partage des données
• Gestion des autorisations daccès
• Vérifications des contraintes dintégrité
• Contraintes structurelles (un employé a un seul
  chef), contraintes
• dynamiques (un salaire ne peut diminuer), etc.
• Sécurité et reprise sur panne

8
Introduction

• Principaux composants dun 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
• dinterrogation
• Gestionnaire de transactions
• Regroupement des actions (modifications, mises à
  jour, etc.) qui doivent être
• exécutées ensemble séquentiellement (ex
  virement dune somme de  A 
• à  B , lire la somme de A, effacer de A,
  ajouter à B, etc. )

9
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

10
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
  limplantation physique
• Niveau externe ou vue

11
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

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

13
Introduction

• Modèle de données (décrire les données, les
  relations entre elles, leur sémantique, les
  contraintes dinté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.

14
Bases de Données

• Introduction
• Modèle Entité/Association
• Modèle relationnel
• Algèbre relationnelle
• Algèbre relationnelle étendue
• SQL

15
Exemple de base de données
16
Exemple de base de données

• Critiques sur notre exemple
• Anomalie lors dune insertion
• Insertion du même film plusieurs fois, et même
  avec différentes données!
• Comment distinguer un film dun autre?
• Anomalie lors dune modification
• Si on modifie la date de naissance de Hitchcock?
• Anomalie lors dune destruction
• Si on supprime un film, on supprime toutes
  données associées, y compris celles du réalisateur

17
Bonne méthode

• représenter individuellement les films et les
  réalisateurs (une action sur lun nentraîne pas
  systématiquement une action sur lautre)
• 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

18
Changeons notre modèle
19
Changeons notre modèle
20
Type d entité
21
Schéma complet de notre exemple
22
Modèle Entité/Association

• Entité objet concret ou abstrait, identifiable,
  décrit par linformation et pertinent pour
  lapplication
• Ex Vertigo (film), Hitckcock (réalisateurs)
• Une entité est représentée par un ensemble d
  attributs qui la décrive.
• Ex film décrit par le nom, lannée de création,
  
• Chaque attribut a un domaine qui correspond à
  lensemble des valeurs quil peut prendre
• Ex les années compris entre 1920-2005

23
Type dentité
24
Modèle Entité/Association

• Clé (Identificateur) Un sous-ensemble
  dattribut permettant didentifier lentité 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 nompaysannée?
• Identificateur abstrait numéro séquentiel qui
  sera incrémenté au fur et à mesure des insertions

25
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é dune association nombre dentités
  que lassociation relie.
• Noté (Min, Max)
•      0. ,   1 1.1
• Une association peut avoir des attributs

26
Format Merise
27
Modélisation UML
28
Attribut dune association
29
Clé d une association

• Clé dune 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

30
Association n-aires
31
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
32
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 dune autre,
il faut connaître le cinéma auquel elle est
rattachée
33
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

34
Association n-aire (cardinalité  0., clé ?)
35
Association n-aire (cardinalité  0., clé ?)

• Remplacer l association par un type d entité!
• Règles
• on attribue un identifiant autonome à
  lassociation
• on crée une association Ai de type 1.1    0.
  entre l association et chacun des types
  d entité.

36
Association ternaire - - gt entité
37
Généralisation/Spécialisation (E/A - Merise)
Ensemble dentités généralisantes
Ensemble dentités généralisées
38
Héritage (UML)
Classe mère / Sur-classe
Classes dérivées ou filles / sous-classes
39
Contraintes

• Contraintes dinté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

40
Exemples de contraintes

• Contraintes de domaine
• "La fonction dun enseignant à lUniversité prend
  sa valeur dans lensemble vacataire, moniteur,
  ATER, MCF, Prof., PRAG, PAST."
• Contraintes d'unicité
• "Un département, identifié par son numéro, a un
  nom unique (il ny 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. "

41
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 au niveau du schéma
  conceptuel

42
Exemple de dépendances fonctionnelles
43
(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

44
Bases de Données

• Introduction
• Modèle Entité/Association
• Modèle relationnel
• Algèbre relationnelle
• Algèbre relationnelle étendue
• SQL

45
Modèle relationnel

• La relation du  nom  Film
• Film (titre string, année number, genre
  string)

46
Modèle relationnel

• Domaine ensemble dinstance dun type
  élémentaire (en extension ou en intension)
• Ex réels, boolean, chaine de caracteres, etc.
• Attribut colonne dune 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)

47
Modèle relationnel

• Relation (R) sous-ens. fini du produit
  cartésien des domaines des attributs de R
  (produit cartésien D1xD2xxDn 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 na pas dimportance
• pas de case vide dans la table

48
Modèle relationnel

• Clé dune relation le petit sous-ens. des
  attributs qui permet didentifier chq ligne dune
  manière unique
• ex film (titre, année, genre)
• Tuple (n-uplet) une liste de n valeurs (v1,
  vn), où chq vi est la valeur dun attribut Ai du
  domain Di
• ex (Cyrano, 1992, Rappeneau)
• Base de données ensemble fini de relations.

49
Passage de E/A au relationnel

50
Règles de passages (1)

• Règles générales Entité
• On crée une relation de même nom que lentité.
• Chaque propriété de lentité, y compris
  lidentifiant, devient un attribut de la
  relation.
• Les attributs de lidentifiant 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)

51
Règles de passages (2)

• Association de un à plusieurs
• 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.
• Lidentifiant 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)

52
Exemple de passage
53
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é)

54
Règles de passage (4)

• Association binaire de plusieurs à plusieurs
• On crée les relations Ra et Rb des entités A et
  B.
• On crée une relation Rab pour lassociation
• La clé de Ra et la clé de Rb deviennent des
  attributs de Rab
• La clé de cette relation est la concaténation des
  clés des relations Ra et Rb
• Les propriétés de lassociation deviennent des
  attributs de Rab
• Ex
• Film (idFilm, titre, année, genre, résumé, idMES,
  codePays)
• Artiste (idArtiste, nom, prénom, annéeNaissance)
• Rôle (idFilm, idActeur, nomRôle)

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

56
Règles de passage (5)

• Association ternaire
• Même principe dune 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)

57
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

58
Bases de Données

• Introduction
• Modèle Entité/Association
• Modèle relationnel
• Algèbre relationnelle
• Algèbre relationnelle étendue
• SQL

59
Algèbre relationnelle

• Algèbre ensemble dopérateurs manipulant des
  expressions dont le résultat est une expression
  qui peut être manipulée
• Algèbre relationnelle ensemble dopérateurs
  prenant en entrée une ou deux expressions
  relationnelles et produise une expression
  relationnelle à la sortie.

60
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 sapplique à
  un ensemble de relations et produit une relation
  finale.

61
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

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

63
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 (, lt, gt, 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égionAntilles (Station)

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

65
Algèbre relationnelle

• Combinaison de projection et sélection
• ex ?nomStation(?(libelléplongée) ? (prixlt140)
  (Activité) )

66
Algèbre relationnelle

• Lunion (R ? S) crée une relation comprenant
• tous les tuples existants dans lune ou lautre
  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)

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

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

69
ExempleStation x Activité
70
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
  lattribut
• A en A et B en B

71
Algèbre relationnelle

• La jointure (? ) sélection produit cartésien
• Ex informations sur les stations et leur
  activité
• ?S.nomStationA.nomStation (Station x Activité)
  
• Station (? nomStationnomStation ) Activité

72
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

73
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ù lon
  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 loccasion 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

74
Expression de requêtes par des exemples

• Requêtes avec ? et -
• (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 nont pas reçu de
  client américain
• (15) lId des clients qui ne sont pas allés aux
  Antilles
• Complément d un ensemble
• (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

75
Bases de Données

• Introduction
• Modèle Entité/Association
• Modèle relationnel
• Algèbre relationnelle
• Algèbre relationnelle étendue
• SQL

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

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

78
Exemple
79
Exemple
80
Exemple
81
Fonctions dagré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)

82
Fonctions dagré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é)

83
Fonctions dagré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é)

84
Fonctions dagrégation

• Opération sur un ensemble de nuplets!
• Nombre denseignants par départements
• Nom_Département?CountEns_ID(Enseignant?Département
  )

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

86
Mise à jour de la base

• Mise à jour dun nuplet projection généralisée!
• r ? ?Etudiant_ID ?(Nom Dupont   )?
  (Prénom Jacques ) (Etudiant)
• Etudiant ? ?(Etudiant.Etudiant_ID ltgt
  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)

87
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
  dutilisateurs
• Faciliter laccès à certaines données
• create view nom_vue as lt requête gt
• Exemple
• create view Info_Non_Confidentielle_Etudiant
• as ? Etudiant_ID, Nom, Prénom, Email
  (Etudiant)

88
Bases de Données

• Introduction
• Modèle Entité/Association
• Modèle relationnel
• Algèbre relationnelle
• Algèbre relationnelle étendue
• SQL

89
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 daccès aux données

90
SQL / types
91
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))

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

93
SQL / création des tables

• NULL absence de valeur (pas valeur zéro ou
• chaîne vide!!!)
• Pas dopé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)

94
SQL/ Contraintes

• Contraintes quon peut demander
• Un attribut doit toujours avoir une valeur (NOT
  NULL)
• Un attribut (ou ensemble dattributs)
  constitue(nt) la clé de la relation
• Un attribut dans une table est liée à la clé
  primaire dune autre table
• La valeur dun attribut doit être unique au sein
  de la relation
• Dautres types de règles

95
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))
96
SQL/ création des tables

• Clé secondaire(unique)
• CREATE TABLE Cinéma
• (nom VARCHAR (30) NOT NULL,
• Adresse VARCHAR (30) UNIQUE,
• PRIMARY KEY (nom))

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

98
SQL / création des tables

• Clé étrangère (Foreign key) les attributs
  faisant
• Reference une ligne dune 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)

99
SQL/création des tables

• Modification??
• Insertion dans Film avec une valeur inconnue pour
  idMES
• La destruction dun artiste
• La modification de id dans Artiste ou de idMES
  dans Film
• En cas de violation dune contrainte dintegrité
  
• demande rejetée

100
SQL / création des tables

• la destruction dun metteur en scène déclenche la
  mise à NULL
• (SET NULL) de la clé étrangère idMES pour tous
  les films quil 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)

101
SQL / création des tables

• Quand on détruit un cinéma, on veut également
  détruire les salles
• quand on modifie la clé dun 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)

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

103
SQL / Modification du schéma

• ALTER TABLE nomTable ACTION description
• Action
• ADD ajouter
• MODIFY modifier
• DROP supprimer
• RENAME renommer

104
SQL / Modification du schéma

• ALTER TABLE nomTable ACTION description
• Action ADD
• ALTER TABLE Internaute ADD region VARCHAR(10)

105
SQL / Modification du schéma

• ALTER TABLE nomTable ACTION description
• Action MODIFY
• ALTER TABLE Internaute MODIFY region
• VARCHAR(30) NOT NULL

106
SQL / Modification du schéma

• ALTER TABLE nomTable ACTION description
• Action DROPP
• ALTER TABLE Internaute DROP region

107
SQL / création dindex

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

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

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

110
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 leuro
  , 6.56
• FROM Activite
• WHERE nomStation Santalba

111
SQL / Requêtes

• SELECT libelle, prix / 6.56 AS prixEnEuros,
• Cours de leuro , 6.56 AS cours
• FROM Activite
• WHERE nomStation Santalba

112
SQL / Requêtes

• Doublons
• SELECT libelle
• FROM Activite
• SELECT DISTINCT libelle
• FROM Activite

113
SQL / Requêtes

• SELECT
• FROM Station
• Tri du résultat
• (ascendant pour descendant ajout DESC)
• SELECT
• FROM Station
• ORDER BY tarif, nomStation

114
SQL / Requêtes

• Where
• AND, OR, NOT, lt, lt, gt, gt, ltgt, !,BETWEEN
• SELECT nomStation, libelle
• FROM Activite
• WHERE nomStation Santalba
• AND (prixgt50 AND prixlt120)

115
SQL / Requêtes

• Where
• AND, OR, NOT, lt, lt, gt, gt, ltgt, !,BETWEEN
• SELECT nomStation, libelle
• FROM Activite
• WHERE nomStation Santalba
• AND prix BETWEEN 50 AND 120

116
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 nen va pas de même pour
  les valeurs. Donc SANTALBA est différent de
  Santalba.

117
SQL / Requêtes

• Chaîne de caractères
• LIKE pattern matching
• _ nimporte quel caractère
• nimporte quelle chaîne de caractères
• Ex

SELECT nomStation FROM Station WHERE nomStation
LIKE a (se termine par a)
SELECT nomStation FROM Station WHERE nomStation
LIKE V_____ (commence par v et a 6
caractères)
118
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

119
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
  nest ni vrai, ni faux mais une troisième valeur
  booléenne, UNKNOWN.
• TRUE1, FALSE0 et UNKNOWN1/2.
• x AND yMin(x,y)
• x OR y Max(x,y)
• NOT x1-x

120
SQL/Requêtes

• SELECT station
• FROM Sejour
• WHERE nbPlaces lt 10 OR nbPlaces gt 10

121
SQL/Requêtes

• nbPlaces NULL est incorrecte.
• SELECT
• FROM Sejour
• WHERE nbPlaces IS (NOT) NULL

122
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

123
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

124
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

125
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

126
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ù lon trouve à la fois des
• clients et des stations
• SELECT region FROM Station
• INTERSECT
• SELECT region FROM Client

127
SQL/Requêtes ieurs tables

• Quelles sont les régions où lon trouve des
  stations mais pas des clients ?
• SELECT region FROM Station
• EXCEPT
• SELECT region FROM Client

128
SQL/Requêtes imbriquées

• Noms de stations où ont séjourné des clients
  parisiens

SELECT station FROM Sejour, Client WHERE
ididclient AND ville  Paris 
SELECT station FROM Sejour WHERE idclient IN
(SELECT id FROM Client WHERE ville  Paris )
129
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 )
130
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
  (lt,gt,, 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
  (lt,gt,, etc.). Renvoie TRUE si la comparaison
  avec tous les tuples de la relation unaire R
  renvoie TRUE

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

132
SQL/Requêtes imbriquées

• Quelle station pratique le tarif le plus élevé?
• SELECT nomStation
• FROM Station
• WHERE tarif gt ALL (SELECT tarif FROM Station)

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

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

135
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 nomStationSantalba
• AND
  A1.libelleA2.libelle
• AND
  A1.prixA2.prix)

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

137
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

138
SQL/Agrégation

• Combien de place a reservé Mr Kerouac pour
  l ensemble des séjours?
• SELECT SUM (nbPlaces)
• FROM Client, Sejour
• WHERE nomKerouac
• AND ididClient

139
GROUP BY

• Afficher les régions avec le nombre de stations
• SELECT region, COUNT(nomStation)
• FROM Station
• GROUP BY region

140
GROUP BY

• Afficher le nombre de places reservées par client
  (nom)
• SELECT nom, SUM(nbPlaces)
• FROM Client, Sejour
• WHERE ididClient
• GROUP BY id, nom

141
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 ididClient
• GRUOP BY nom
• HAVING SUM(nbPlaces)gt10

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