- Pour les TPs, il faut utiliser Postgresql.
- Pour chaque chapitre, vous aurez des exercices à faire et à rendre.
- La moyenne sera calculée comme suit avec la note de chaque TD représentée par N et la note de l'examen par NE :
M = (N1+N2+N3+N4+N5)/10 + NE/2.
- Coquille. Page 8 du pdf (ou page 11 en bas de page) : Il manque l'attribut “spécialité” dans la relation “Médecin” (au niveau du schémas relationnel).
En résumé:
- Pour chaque entité, on crée une relation (une table) avec les mêmes attributs (colonnes) que l'entité.
- Il y a 4 cas pour les associations en fonction des deux cardinalités de l'association. On va arbitrairement dire qu'on a deux entités, une à droite et une à gauche:
- S'il y a les cardinalités 0,1 ou 1,1 à gauche et 0,n ou 1,n à droite, on ne crée pas de nouvelle relation pour cette association. On ajoute un nouvel attribut dans la relation de gauche qui est la clef primaire de la relation de droite (on dit alors que c'est une clef étrangère).
- S'il y a les cardinalités 0,1 ou 1,1 à gauche et 0,1 ou 1,1 à droite, c'est quasi la même chose. On ne crée pas de nouvelle relation pour cette association. On ajoute dans chaque relation un attribut qui est la clef primaire de l'autre relation.
- S'il y a les cardinalités 0,n ou 1,n à gauche et 0,n ou 1,n à droite, on doit créer une nouvelle relation pour représenter cette association. La clef primaire de cette nouvelle relation sera l'union des clefs primaires des deux relations (celles qui correspondes aux entités). “Union” veut simplement dire que l'on a un attribut pour chaque clef primaire des entités et que c'est l'ensemble de ces attributs qui consititue la clef primaire de la nouvelle relation.
- Le dernier cas est celui des associations n-aires avec 3 ou plus entités. C'est très similaire au cas précédent: on crée une nouvelle relation pour représenter cette association. La clef primaire de cette nouvelle relation sera l'union des clefs primaires des relations (celles qui correspondes aux entités).
- Une voiture a 4 roues. Chaque roue appartient à une seule voiture. C'est donc des cardinaltés 1 / 4 (cas 1). Donc on crée deux relations (voiture, roue). Si la crée primaire de la relation voiture est son numéro de châssis, on ajoute cette clef dans la relation roue. Comme cela, pour chaque roue, on sait à quelle voiture elle appartient.
- Une voiture a 1 volant et chaque volant appartient à une voiture. C'est donc des cardinaltés 1 / 1 (cas 2). Donc on crée juste les deux relations (voiture, volant) et on ajoute la clef primaire de chaque relation dans la relation de l'autre. Par exemple, on ajoute le numéro de châssis de la voiture dans la relation volant et le numéro de série du volant dans la relation voiture.
- Un-e auteur-e peut écrire plusieurs livres et chaque livre peut avoir plusieurs auteurs. Donc c'est des cardinaltés n / n (cas 3). On crée deux relations livre et auteur, et en plus une relation qui fait le lien entre les deux (que l'on peut nommer livre-auteur). Cette dernière relation a deux champs: l'ISBN qui identifie un livre et un numéro unique qui identifie un-e auteur-e. La clef primaire de la relation est composée de ces deux champs.
Pour ceux-celles qui écrivent leur PDF avec LateX, voici quelques informations:
- On peut utiliser le package
amsmath
- produit cartésien:
R_1 \times R_2 \rightarrow ou \longrightarrow
- jointure:
\bowtie . Par exemple: R_1 \underset{R_1 \cdot A_2=R_2 \cdot B_2}{\bowtie} R_2 \rightarrow
- projections:
\pi_{R_1, R_2} (R_1) \rightarrow
- sélections:
\sigma_{A_1 =12 \vee A_2 =14 } (R_1)
Autres ressources :
Attention: les informations ci-dessous datent de 2022. Cela peut etre différent pour les TPs plus récents.
Pour l'ensemble du TD n°2, les requêtes sont à faire avec l'outil RelaX : https://dbis-uibk.github.io/relax/calc/gist/0ed0d9d640284e81f44a79b595a28562
Pour les exercices 1, 4 et 5, il est possible d'importer directement les tables de données.
Pour ça, aller sur l'outil de sélection de dataset (cliquer sur «UIBK - R, S, T»), puis dans «Load dataset stored in a gist» rentrer l'ID qui vous intéresse parmi les suivants :
- Exercice 1 : 337100f14d244444e770314c4e6d3109
- Exercice 4 : 5dae8c0b44fa768f8856a2ec0006b760
- Exercice 5 : ffc91ead4144fdff8570d64ca5f8b4e7
Pour les exercices 2 et 3, il faut remplir soi-même les tables avec des exemples de tuples.
Pour cela, toujours dans l'onglet dataset, aller dans la partie «Create your own Dataset», puis cliquer sur «create new dataset».
Copier/coller par exemple l'un des modèles suivants :
group: Base de donnée TD 2 - Algèbre relationnelle
description: Exercice 2
Enseignant = {
Numen:number, NS:string , PS:string , Grade:string
}
Etudiant = {
NumEtud:number, NE:string , EP:string , NiveauEtud:string
}
UE = {
CodeUE:string, Nom:string , NiveauUE:string, NumEtud:number, Numen:number
}
Ensuite, rentrez les donnés (par exemple 5 enseignants) puis effectuez les requetes SQL.
group: Base de donnée TD 2 - Algèbre relationnelle
description: Exercice 3
MEDICAMENT = {
code:number, libelle:string
}
PATIENT = {
matricule:number, nom:string
}
MEDECIN = {
numss:number, nom:string
}
PRESCRIT = {
codeMed:number, numConsult:number, nbPrises:number
}
CONSULTATION = {
num:number, numss:number, matricule:number, date:date
}
Pour remplir les tables, il suffit ensuite de cliquer sur le symbole de tableau à gauche de la ligne du nom d'une relation. Insérer les données, puis valider. Quand toutes les tables sont prêtes, cliquer sur «preview» pour vérifier, puis au bas de la page «use Group in editor», pour pouvoir effectuer les requêtes dessus.
Le TD demande d'utiliser Postgres 9.4. Or cette version est obsolète et plus supportée. Vous avez donc trois possibilités:
- Utiliser une version plus récente
- Installer Postgres 9.4 depuis de dépôt de paquets de Postgres
- Utiliser Docker et une image toute faite avec Postgres 9.4
Je vous conseille l'option 1, c'est la plus simple et elle convient parfaitement pour ce cours. Pour installer sous Ubuntu:
sudo apt install postgresql
Si vous voulez absoluement installer Postgres 9.4 sous Ubuntu (option 2), c'est possible:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-9.4
Une fois installé (option 1 ou 2), lancer l'interface en ligne de commande:
sudo -u postgres psql
Attention: cela va lancer psql dans le contexte de l'utilisateur postgres. Donc si vous voulez par exemple ouvrir un fichier avec la commande \i, cela ne va probablement pas marcher car psql cherchera dans le home de postgres.
- Pour lancer psql depuis votre utilisateur (càd sans passer par
sudo), vous devez créer une base de données et un utilisateur Postgres avec le même nom que votre compte utilisateur (le -s de createuser est pour créer un super-user, càd un administrateur):
sudo -u postgres createuser -s $(whoami)
sudo -u postgres createdb $(whoami)
Maintenant, vous pouvez simplement lancer la commande:
psql
Le cours comporte certaines imprécisions (des choses possibles avec d'autres systèmes de gestion de bases de données, mais pas avec Postgres)
- Page 26, partie sur les principaux types de données. Le type
NUMBER n'existe pas en SQL.Il existe uniquement dans certaines extensions non-standard. Il faut utiliser à la place DECIMAL ou NUMERIC (les deux sont équivalents)
- Page 26, partie sur les contraintes. L'exemple a plusieurs problèmes: le type
NUMBER n'existe pas, il y a un point virgule à la place d'une virgule. L'exemple devrait être:
CREATE TABLE EMP (NoEMP DECIMAL(8),
Nom VARCHAR(12), JOB VARCHAR(14),
Salaire DECIMAL(6,2), NoDEPT DECIMAL(3),
CONSTRAINT emp_clé_prim PRIMARY KEY (NoEmp),
CONSTRAINT emp_clé_etr FOREIGN KEY (NoDEPT) REFERENCES DEPT(NoDept),
CONSTRAINT Salaire_OK CHECK (Salaire>0));
- Page 27, paragraphe 3,6, pour modifier le type d'une colonne, il faut utiliser
COLUMN, ALTER et TYPE:
ALTER TABLE Nom_de_la_table ALTER COLUMN Nom_de_la_colonne TYPE Type_de_donnees;
- Le TD n'est pas complètement mis à jour pour Postgres et indiquent certaines choses spécifiques à Oracle. Il faut juste transposer:
number devient decimal ou numeric (c'est équivalent)varchar2 est simplement varchar.
- Il y a certaines incohérences ou erreurs dans le TD:
- Exercice 3, question 3: ValComm de type réel (Number(4,2)). Les valeurs sont trop grandes pour 4 chiffres au total, 2 chiffres après la virgule. Vous aurez donc une erreur lorsque vous répondrez aux questions 6 et 8. Il faut prendre un nombre plus grand de chiffres.
- Exercice 5, question 4: Supprimer la table. Comme on a créé une vue juste avant avec cette table, on ne peut pas la supprimer sans supprimer également la vue.
- Exercice 5, question 7: Renommer la colonne COMM. Mais on vient de supprimer la colonne à la question précédente. Le plus simple est de supprimer la table et de la recréer. A ce moment là, on peut renommer la colonne.
Cette vidéo ainsi que les suivantes aident à prendre en main la console postgreSQL. Elles se présentent sous la forme de tutoriels de quelques minutes : https://www.youtube.com/watch?v=XxyxkmH87nU&list=PLwvrYc43l1MxAEOI_KwGe8l42uJxMoKeS&index=8
- Coquille. Page 2 du pdf (ou page 28 en haut de page) : “En revanche l’inverse n’est pas valide car par exemple la valeur d1 est associée à deux valeurs d1 et d2” la fin de la phrase est fausse : “En revanche l’inverse n’est pas valide car par exemple la valeur d1 est associée à deux valeurs e1 et e3”.
- Coquille (sur la même page) : “Réflexivité. Tout ensemble d’attributs détermine lui-même ou une partie de lui-même : y⊂x = ⇒ x→y.” Il manque l'égalité (lui-même) et donc la formule est: y⊆x = ⇒ x→y.
- Coquille (sur la même page) : “Toutes les DF dont la partie droite n’est composée que d’un seul attribut est une DFE”, c'est l'inverse: “Toutes les DF dont la partie gauche n’est composée que d’un seul attribut est une DFE”.
- Erreur (page 31): “Si on retient comme clé de cette relation {Région, Variété} la relation Pomme est 3NF”. C'est faux. L'attribut Pays dépend d'une partie de la clef: Région.
- Coquille (sur le TD) Ex3-4 : Proposer une décomposition de la relation UeEnseignant en troisième forme normale
- Cette vidéo explique le concept de normalisation https://www.youtube.com/watch?v=Uc0NVAJb5Z0
- Cette vidéo est un exemple d'exercice sur la normalisation https://www.youtube.com/watch?v=wIb68JGf8C0
- Ce lien mène vers un cours sur la normalisation https://sgbd.developpez.com/tutoriels/cours-complet-bdd-sql/?page=normalisation#LVIII
- Ce lien mène vers un cours contenant des exercices corrigés sur la normalisation https://stph.scenari-community.org/bdd/nor1-pdf
- Coquille. Page 2 du pdf (ou page 35 en bas de page) : Remplacez tous les “SalaireMoyenne” par “SalaireMoyen” (accompagné du chiffre adéquat)
- Coquille (sur la même page) : Le titre du dernier bloc est le meme que le précédent, il devrait etre “Algorithm 4 revalorisation des salaires de tous les employés d’un département dont le numéro est lu au clavier (ajout de 100e)”.
Le cours est écrit pour Oracle, mais le TD doit être fait en Postgresql. Il y a donc des conversions à faire.
- Pour créer un bloc anonyme, il faut utiliser la commande suivante :
do $$
declare
déclarer ici les variables
begin
raise notice 'Hello World';
end $$;
- comme dans le code précédent, l'affichage ne se fait pas avec dbms_output, mais avec RAISE NOTICE.
- pour écrire une valeur dans RAISE NOTICE, on utilise une le '%' :
do $$
declare
nombre1 integer :=1;
begin
raise notice 'affiche le nombre un : %', nombre1;
end $$;
la structure IF THEN ELSE est également différente
if condition then
statements;
elsif conditions then
statements;
else
alternative-statements;
end if;
- GOTO n'existe pas et il n'y a pas d'équivalent (goto, dans tous les langages est considéré comme une mauvaise pratique de toute façon).
- Pour ouvrir un script sql :
\i test.sql
Le terminal à partir duquel on utilise postgres doit être ouvert dans le même dossier que le fichier test.sql. Plus de ressources pour ouvrir un fichier sql à partir de postgresql : Lien externe
La syntaxe des fonctions est la suivante:
create or replace function NomFonction(a integer)
returns integer
language plpgsql
as $$
declare
...
begin
return a;
end $$;
Il est nécessaire de préciser le language (plpgsql) car Postgres en supporte plusieurs.
- Avec Postgres, la notion de procédure n'a été introduite que très récemment (Postgres version 11). Pour les versions précédente, on peut simuler une procédure en utilisant une fonction qui ne retourne rien (void):
create or replace function pFact(n integer)
returns void
language plpgsql
as $$
declare
...
begin
...
end $$;
Pour appeler cette fonction/procedure dans une autre fonction, on peut utiliser `perform`. Cela indique que l'on ignore la valeur de retour.
- La syntaxe pour les curseurs est la suivante:
do $$
declare
curseur cursor for select ...;
Ensuite, le curseur s'utilise de cette façon, assez proche de PL/SQL:
open curseur;
fetch curseur into ...;
close curseur;
end $$;
Dans une boucle:
open curseur;
loop
fetch curseur into ...;
exit when not found;
end loop;
close curseur;
- La syntaxe pour les triggers est la suivante:
create trigger trigger_1
before insert or update or ...
on table
for each row
execute procedure trigger_1_function();
On peut avoir un trigger par ligne (for each row) ou par commande (for each statement). On peut déclencher le trigger avant la mise à jour (BEFORE) ou après (AFTER). Il faut ensuite définir une fonction qui va s'exécuter quand le trigger est déclenché:
create or replace function trigger_1_function()
returns trigger
language plpgsql
as $$
begin
...
return NEW;
end $$;
NEW permet d'avoir accès aux nouvelles valeurs, OLD aux anciennes (suivant le type de trigger: BEFORE ou AFTER).
- PROMPT et ACCEPT sont des cas bien particulier. En effet, ce ne sont pas des instructions du PL/SQL d'Oracle et il n'y a pas d'équivalent avec Postgresql. En effet, Oracle est un système de base de données qui, comme tous les systèmes du même genre, tourne généralement sur un serveur distant. Donc comment ce serveur distant peut-t'il vous afficher un message et demander une donnée ? C'est simple, il ne peut pas. PROMPT et ACCEPT sont en fait des mots clefs interprétés par le client utilisé pour envoyer le SQL au serveur. Ou plutôt était car ce client (SQL*Plus) est largement déprécié et n'est pas installé avec Oracle depuis Oracle 11g (sorti en 2009).
Alors par quoi remplacer PROMPT et ACCEPT pour les TDs? On peut simplement assigner une valeur à une variable:
a numeric := 1;