I. Introduction▲
L'objectif est l'utilisation d'une base de données pour gérer une liste de films avec l'année de sortie ou de réalisation ainsi que le genre du film un peu comme IMDB.
Le nom donné à cette base sera AMDB.
Hypothèses
Le film a un seul nom. Un film a une seule année de sortie, et peut avoir un ou plusieurs genres trois tableaux seront utilisés un comportant le nom des films, un autre les années et un troisième les genres. Le tableau année pourra servir à d'autres relations avec d'autres tableaux dans le cas d'une extension de la base. Il faudra créer un 4e tableau pour les relations multiples entre les films et les genres (Tab_film_genre).
Relations entre les tables
II. CRÉATION DE LA BASE DE DONNÉES▲
Juste après l'installation, vous avez des palettes de composants en plus :
Sélectionner postgrest - ajouter un objet - ajouter une base de données
indiquer le nom de la base amdb
on obtient :
III. CRÉATION DES TABLES▲
III-A. TABLE GENRE▲
III-A-1. Ouvrir schéma public▲
ensuite table , souris bouton droit sélectionner ajouter une table
Ajout d'une table tabgenre Et validation
La table est créée
III-A-2. création des colonnes▲
La table comporte deux colonnes Genre et idgenre, idgenre sera utilisée comme clé primaire avec incrémentation automatique
Ajout des colonnes
Nom de la colonne : idgenre
sélectionner le type de données sérial pour l'incrémentation automatique valider la commande par OK
La colonne idgenre est créée
Création de la colonne genre De 40 caractères non nuls
La table contient deux colonnes :genre et idgenre
III-A-3. création d'une clé primaire▲
nom de la clé : clégenre
ensuite sélectionner colonne
Sélectionner comme colonne idgenre correspondant à clé primaire Ajouter et OK
III-A-4. Visualiser le contenu d'une table▲
il n'y a rien, seules les colonnes apparaissent
III-A-5. Le panneau sql affiche les commandes, pour la colonne idgenre :▲
III-A-6. table année tabannée▲
Deux colonnes :
idannée type serial
année type nombreentier
cléannée clé primaire sur idannée
III-A-7. table film tabfilm▲
et enfin la table tabfilm
idfilm : type serial, titre : type 40 caractères, annéesortie : type entier (clé étrangère, pointe sur la tabannée). )
cléfilm clé primaire colonne idfilm
III-B. RELATION UN À PLUSIEURS ENTRE DEUX TABLES▲
Entre film et année, une seule année de sortie pour un film et plusieurs films pour une année
(Créer une colonne du type annéesortie dans tabfilm)
La colonne annéesortie de la table tabfilm va pointer sur la cléannée de tabannée type de relation n - 1
Annéesortie et cléannée sont de même type intéger
La clé étrangère est créée dans la table ou il y a « plusieurs »
III-B-1. Créer une clé étrangère▲
Nom de la clé étrangère = keyannée
Référence = la table tabannée
III-B-2. RELATION PLUSIEURS À PLUSIEURS ENTRE DEUX TABLES▲
Entre film et genre, plusieurs genres pour un film et plusieurs films pour un genre
Relation entre deux tables du type 1- n et n - 1
Une table intermédiaire est nécessaire
III-B-2-a. table intermédiaire entre tabfilm et tagenre▲
Créer une table intermédiaire ou de jointure tab_film_genre
avec 3 colonnes
la 1re pour la clé de la table key de type serial
la 2e pour pointer sur la clé du tableau tabfilm nom de la colonne film type entier
la 3e pour pointer sur la clé de tabgenre nom de la colonne genre type entier
clé primaire sur colonne key
III-B-2-b. Ajouter les clés étrangères pour les liaisons avec tabfilm et tabgenre▲
Créer une clé étrangère ayant pour nom titre et référence la table tabfilm
ensuite
définir la colonne locale film qui va correspondre à la clé idfilm de tabfilm.
III-B-2-c. Créer une deuxième clé étrangère genre▲
genre
qui a pour référence la table tabgenre
avec colonne locale genre et
référencé dans le tableau tabgenre par la clé de ce tableau idgenre
Résultat
III-C. INSÉRER LES DONNÉES DANS LES TABLES▲
III-C-1. Insérer les données▲
Pour insérer des données dans une table, utiliser la fonction script : cliquer sur l'icône SQL
On obtient
III-C-2. Insérer les genres ligne par ligne pour le tableau genre▲
on indique le nom du tableau et ensuite les valeurs dans l'ordre des colonnes
idgenre = 1 et genre = comédie
INSERT INTO tabgenre VALUES ( 1,'comédie');
ou uniquement la valeur de genre, idgenre sera automatiquement incrémenté
INSERT INTO tabgenre(genre) VALUES ( 'drame');
résultats
ajouter les genres suivants
INSERT INTO tabgenre(genre) VALUES ( 'policier');
INSERT INTO tabgenre(genre) VALUES ('horreur');
INSERT INTO tabgenre(genre) VALUES ('aventure');
INSERT INTO tabgenre(genre) VALUES ( 'histoire');
INSERT INTO tabgenre(genre) VALUES ( 'fiction');
III-C-3. Insérer des données dans tabannée▲
INSERT INTO tabannée(année) VALUES ( 2000');
III-C-4. Insérer des données dans tabfilm▲
ajouter des films
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Men ', 2);
INSERT INTO tabfilm(titre,annéesortie) VALUES ('Forge', 3);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Star', 4);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'histoire', 2);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Soleil' , 1);
III-C-5. Insérer des données dans tab_film_genre▲
structure de la table
INSERT INTO tab_film_genre(film,genre) VALUES ( 4,2);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,1);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,4);
INSERT INTO tab_film_genre(film,genre) VALUES ( 3,2);
INSERT INTO tab_film_genre(film,genre) VALUES ( 2,3);
INSERT INTO tab_film_genre(film,genre) VALUES ( 2,6);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,1);
Contenu de la table
III-D. POUR AFFICHER LE CONTENU D'UNE SEULE TABLE▲
III-D-1. le contenu d'une table▲
SELECT * from tabannée;
III-D-2. Le contenu d'une colonne d'une table▲
SELECT année FROM tabannée;
III-D-3. Le contenu sélectionné d'une colonne d'une table▲
SELECT année FROM tabannée WHERE année>2000;
III-D-4. Le contenu trié d'une colonne d'une table▲
SELECT * FROM tabannée ORDER BY année;
ou SELECT année FROM tabannée ORDER BY année Pour afficher la colonne année
III-D-5. Même principe pour genre▲
III-D-6. Compter le nombre de films▲
pour la liste des films
SELECT titre FROM tabfilm;
pour le nombre de films
SELECT count( titre )FROM tabfilm;
III-E. AFFICHER LE CONTENU DE DEUX TABLES LIÉES PAR UNE CLÉ ÉTRANGÈRE▲
III-E-1. Afficher le film et l'année de sortie▲
SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie
III-E-2. afficher le film et l'année de sortie, tri sur l'année▲
SELECT tabfilm.titre,tabannée.année FROM tabfilm , tabannée WHERE tabannée.idannée=tabfilm.annéesortie ORDER BY année
il est plus rapide de remplacer le nom de la table par une lettre
SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie ORDER BY année
III-E-3. afficher le film et l'année de sortie, sur une année▲
SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie and a.année=2000
III-E-4. afficher le film et l'année de sortie, depuis une année▲
SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie and a.année>2000
III-F. AFFICHER LE CONTENU DE DEUX TABLES LIÉES PAR UNE TABLE INTERMÉDIAIRE DE TYPE n-n▲
tabfilm F
tab_film_genre A
tabgenre G
III-F-1. Édition triée des genres par films▲
SELECT F.titre,G.genre
FROM tabfilm AS F, tabgenre AS G ,tab_film_genre AS A
where
F.idfilm = A.film
AND
A.genre=G.idgenre
ORDER BY titre, genre
III-F-2. Édition triée des films par genres▲
SELECT F.titre,G.genre
FROM tabfilm AS F, tabgenre AS G ,tab_film_genre AS A where F.idfilm = A.film AND A.genre=G.idgenre
ORDER BY genre,titre
Variante avec du texte
III-F-3. Édition triée des films par genres▲
III-F-4. Édition du nombre de films par genre et avec du texte▲
