bases exemple

La base etude

Pour illustrer les requêtes sur une BD, nous allons utiliser la base etude.sqlite.

Nous présentons ci-dessous les tables de cette base. Pour chaque table est donnée son entête (les attributs) et quelques lignes de la table, ainsi qu'une lecture des données.

Nous n'apprendrons pas à construire des bases de données avec toute la rigueur nécessaire (cela nécessiterait un temps d'enseignement plus long que celui dont on dispose en ICN, pour en savoir plus vous pouvez taper "formes normales d'une BD relationnelle" dans un moteur de recherche). Quelques remarques sont toutefois faites ci-dessous dans la description des tables pour souligner quelques types de problème qui se posent avec une BD mal construite.

La table "personne".

id_personne est l'identifiant d'une ligne (clef primaire).

Code de définition de la table :

  
  CREATE TABLE "personne" (
  	"id_personne" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 
  	"nom" VARCHAR NOT NULL , 
  	"prenom" VARCHAR NOT NULL , 
  	"anneeNaissance" INTEGER NOT NULL 
  )
  
  

La table "cours".

id_cours est l'identifiant d'une ligne (clef primaire). id_enseignant est une clef étrangère faisant référence à la clef primaire de la table "personne". Par exemple, le cours d'identifiant 1 est un cours sur la théorie des graphes et ce cours a été donné par l'enseignant d'identifiant 2, c'est-à-dire Adèle Gemlamorte.

Code de création de la table :

  
  CREATE TABLE "cours" (
	`id_cours`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`id_enseignant`	INTEGER,
	`intitule`	VARCHAR NOT NULL,
	FOREIGN KEY(`id_enseignant`) REFERENCES personne
)
  
  

La table "horaire".

id_horaire est la clef primaire.

Code de création de la table :

  
 CREATE TABLE "horaire" (
	`id_horaire`	TEXT NOT NULL,
	`plage`	TEXT NOT NULL,
	PRIMARY KEY(id_horaire)
)
  
  

La table "aSuivi".

Lecture : l'étudiant d'identifiant 1 (Adam Labrosse) a suivi les cours d'identifiant 1 et 7 (théorie des graphes et langage python), il a obtenu la note de 15 en théorie des graphes et la note de 16 en langage python.

Code de création de la table ci-dessous. On voit au passage que l'on peut imposer la contrainte note au moins égale à 0 et au plus égale à 20 : si l'utilisateur se trompe en rentrant une note, le SGBD lui signalera qu'une contrainte n'est pas respectée. Le contrôle par le SGBD de telles contraintes est évidemment l'un des intérêts d'un SGBD.

  
 CREATE TABLE "aSuivi" (
	`id_etudiant`	INTEGER NOT NULL,
	`id_cours`	INTEGER NOT NULL,
	`note`	INTEGER CHECK(note >= 0 AND note <= 20),
	FOREIGN KEY(`id_etudiant`) REFERENCES personne,
	FOREIGN KEY(`id_cours`) REFERENCES cours
)
  
  

• Sur cette table, vous remarquez une erreur de conception: elle ne présente pas de clef primaire. Un SGBD bien conçu ajoute dans ce cas automatiquement un identifiant qui permettra de pallier cette erreur de conception; mais il est déconseillé très fortement de procéder ainsi.
• Il est obligatoire d'avoir un identifiant sur chaque ligne : si deux lignes sont identiques, soit il y a redondance d'informations (et vu la taille des bases de données actuelles, ceci doit absolument être évité car cela augmente en particulier le temps de tout traitement sur la base), soit il y a effectivement deux informations très proches (par exemple deux personnes ayant même nom et prénom) et dans ce cas, il est clair que la base doit vous permettre de les distinguer.
• On peut remarquer également que dans l'état actuel, on pourrait rentrer deux fois un couple (étudiant , cours), ce qui n'a pas de sens : un étudiant ne suit pas deux fois le même cours (on suppose ici que la base concerne une année fixée). Les SGBD permettent d'ajouter des contraintes pour signaler à l'utilisateur lorsqu'il entre dans la base deux lignes incompatibles.
• Ici plutôt que de définir une telle contrainte, nous allons faire du couple (id_etudiant, id_cours) la clef primaire d'une ligne, cela imposera la contrainte d'unicité également.

Le code de définition de la table devient ainsi :

  
 CREATE TABLE "aSuivi" (
	`id_etudiant`	INTEGER NOT NULL,
	`id_cours`	INTEGER NOT NULL,
	`note`	INTEGER CHECK(note >= 0 AND note <= 20),
	FOREIGN KEY(`id_etudiant`) REFERENCES personne,
	FOREIGN KEY(`id_cours`) REFERENCES cours,
	PRIMARY KEY ([id_etudiant],[id_cours])
)
  
  

La table "salle".

Dans la salle A101 a eu lieu entre 8h et 9h (les jours ne sont pas indiqués ici pour ne pas alourdir...), le cours de théorie des graphes.

Code de création de la page :

 
 CREATE TABLE "salle" (
	`nom`	TEXT NOT NULL,
	`id_cours`	INTEGER NOT NULL,
	`id_horaire`	INTEGER,
	FOREIGN KEY(`id_cours`) REFERENCES cours,
	FOREIGN KEY(`id_horaire`) REFERENCES horaire,
	PRIMARY KEY ([nom],[id_horaire])
)

Avant de lancer des requêtes sur cette base, vous devriez constater à nouveau des possibilités d'incohérence : on risque fort d'avoir inscrit des étudiants sur deux cours différents se déroulant en même temps, ou deux cours dirigés par le même enseignant sur une même plage horaire... Les SGBD permettent évidemment de définir également des contraintes de ce type afin que l'utilisateur soit prévenu dès qu'il cherche à rentrer une valeur incompatible avec les valeurs déjà présentes.
Nous ne détaillerons pas plus avant ces contraintes dans ce cours qui est surtout destiné à vous apprendre comment interroger une base de données existante.

La base cine

Une seconde base pour les exercices : cine2.sqlite.

La table personne

Quelques lignes de la table :

Le code de création de la table :

CREATE TABLE "personne" (
	"id_personne" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 
	"nom" VARCHAR NOT NULL , 
	"prenom" VARCHAR NOT NULL
)

La table film

Quelques lignes de la table :

Le code de la table :

CREATE TABLE "film" (
	`id_film`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`id_realisateur`	INTEGER,
	`titre`	TEXT NOT NULL,
	`genre`	TEXT,
	`année`	INTEGER,
	FOREIGN KEY(`id_realisateur`) REFERENCES personne
)

La table joue

Quelques lignes de la table :

Le code de création de la table :

CREATE TABLE "joue" (
	`id_role`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`id_acteur`	INTEGER NOT NULL,
	`id_film`	INTEGER NOT NULL,
	`role`	TEXT NOT NULL,
	FOREIGN KEY(`id_acteur`) REFERENCES personne,
	FOREIGN KEY(`id_film`) REFERENCES film
)

La table cinema

Quelques lignes de la table :

Le code de création :

CREATE TABLE "cinema" (
	"id_cinema" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 
	"nom" VARCHAR NOT NULL , 
	"rue" VARCHAR NOT NULL , 
	"ville" VARCHAR NOT NULL 
)

La table seance

Quelques lignes de la table :

Lecture : la séance d'identifiant 8 concerne le film d'identifiant 5, c'est à dire "E.T. l'extraterrestre". La première séance pour ce film dans le cinéma d'identifiant 6 (c'est à dire UGC Ciné Cité internationale) a eu lieu le 01/12/1982 (ps : il s'agit ici de renseignement fictif ! Ce cinéma n'existait pas à l'époque !)

Code de création :

CREATE TABLE "seance" (
	`id_seance`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`id_cinema`	INTEGER NOT NULL,
	`id_film`	INTEGER NOT NULL,
	`jour`	TEXT NOT NULL,
	FOREIGN KEY(`id_cinema`) REFERENCES cinema,
	FOREIGN KEY(`id_film`) REFERENCES film
)