INFOB212 - B ASES DE DONNEES 2
Travaux pratiques
SQL
Trouver, dans la table suivante, les utilisateurs ayant le même nom et la même adresse email.
Users
ID Name Email
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected]
4 Tom [email protected]
5 Tom [email protected]
Output attendu :
ID Name Email Count
3 Tom
[email protected] 2
SELECT name, email, COUNT(*) FROM users
GROUP BY name, email
HAVING COUNT(*) > 1
Ajouter, dans la table précédente, une nouvelle colonne de type INT avec une valeur par défaut.
Exemple de résultat :
Users
ID Name Email Age
1 John [email protected] 20
2 Sam [email protected] 20
3 Tom [email protected] 20
4 Tom [email protected] 20
5 Tom [email protected] 20
ALTER TABLE Users ADD Age Int NOT NULL
CONSTRAINT D_Users_Age DEFAULT(20)
Créer les triggers nécessaires à la vérification des dépendances fonctionnelles de la relation
suivante. Et ce pour les trois décomposition possible (Peste, Choléra, Peste et Choléra) :
R(REGION, VILLE, RUE, CPOST, BUREAU)
VILLE, RUE → CPOST
VILLE → REGION
CPOST → VILLE, BUREAU
Les solutions sont également disponibles (ici)
Peste
Il faut éviter les insert et update sur la table R afin de garantir la DF CPOST->VILLE
Selon cette DF on ne peut pas insert la ligne : "Rue Royale, Bruxelles, 5000". Il est également interdit
de modifier le CPOST de la Rue des Brasseurs à 5000.
Rue Ville Cpost
Rue Grandgagnage Namur 5000
Rue des Brasseurs Bruxelles 1000
create or replace function check_insert_rue() returns trigger as
$$ begin
if (exists(select cpost from post_peste.rue where cpost = new.cpost and
ville != new.ville)) then
raise exception 'cpost % is already linked to %', new.cpost, (select
ville from post_peste.rue where cpost = new.cpost limit 1);
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_insert_rue before insert or update post_peste.rue
for each row
execute procedure check_insert_rue()
Choléra
Appendix 1
Peste et choléra
Si l’on ajoute la ligne Rue de la Montagne, Charleroi, 6000 dans la table rue la règle d’inclusion n’est
plus respectée.
alter table post_pstnchol.ville add unique (cpost, ville);
alter table post_pstnchol.rue add foreign key (cpost, ville)
references post_pstnchol.ville(cpost, ville);
Donnez la liste exhaustive des événements de la base de données pouvant potentiellement mener
à la violation de la contrainte ci-dessous.
Contrainte : Une commande d’un client de Namur ne peut pas comporter plus de 2 détails.
Schéma de la base de données :
• Insertion d’un 3ème détail pour une commande passée par un client de Namur
• Mise à jour de la colonne Orders.cli_id afin de référencer un client de Namur pour une
commande avec plus de 2 détails.
• Mise à jour de la colonne Details.ord_id afin de référencer une commande ayant déjà 2 détails
et appartenant à un client de Namur.
• Mise à jour de la localité d’un client de venant pas de Namur et ayant déjà passé une commande
avec plus de 2 détails.
Créer deux triggers, de type after et before pour deux de ces événements.
Insertion d’un 3ème détail d’une commande passée par un client vivant à Namur, la contrainte sera
violée
create or replace function customers.check_insert() returns trigger as
$$ begin
if ((select count(*) from customers.customers CUS, customers.orders ORD,
customers.details DET
where DET.order_id = ORD.id
and ORD.user_id = CUS.id
and CUS.city = 'Namur'
and ORD.id = new.order_id) >= 2)
then
raise exception 'the maximal number of details per order (2) is reached';
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_insert before insert
on customers.details
for each row
execute procedure customers.check_insert();
Mise à jour de la colonne Orders.cli_id afin de référencer un client de Namur pour une commande
avec plus de 2 détails.
create or replace trigger check_insert before insert
on customers.details
for each row
execute procedure customers.check_insert();
-- Mise à jour de la colonne Orders.user_id afin de référencer un client de Namur pour une
commande avec plus de 2 détails.
create or replace function customers.check_update_order() returns trigger as
$$ begin
if ((select city from customers.customers where id = new.user_id) = 'Namur' and
(select count(*) from customers.details where order_id = new.id) > 2)
then
raise exception 'the maximal number of details per order (2) is reached';
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_update_order before update of user_id
on customers.orders
for each row
execute procedure customers.check_update_order();
Mise à jour de la colonne Details.ord_id afin de référencer une commande ayant déjà 2 détails et
appartenant à un client de Namur.
create or replace function customers.check_update_detail() returns trigger as
$$ begin
if ((select count(*)
from customers.customers CUS, customers.orders ORD, customers.details DET
where DET.order_id = ORD.id
and ORD.user_id = CUS.id
and CUS.city = 'Namur'
and ORD.id = new.order_id
) >= 2)
then
raise exception 'the maximal number of details per order (2) is reached';
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_update_detail before update of order_id
on customers.details
for each row
execute procedure customers.check_update_detail();
Mise à jour de la localité d’un client de venant pas de Namur et ayant déjà passé une commande
avec plus de 2 détails.
create or replace function customers.check_update_customer() returns trigger as
$$ begin
if (old.city <> 'Namur'
and new.city = 'Namur'
and exists(
select count(*)
from customers.orders ORD
where ORD.user_id = new.id
and (
select count(*)
from customers.details DET
where DET.order_id = ORD.id
) > 2
))
then
raise exception 'the maximal number of details per order (2) is reached';
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_update_customer before update of city
on customers.customers
for each row
execute procedure customers.check_update_customer();
Appendix 1
create or replace view post_cholera.ville_rue as
select r.rue as rue, v.ville as ville, r.cpost as r_cpost, v.cpost as v_cpost
from post_cholera.ville v
full join post_cholera.rue r on v.cpost=r.cpost
order by r.rue, r.cpost;
select * from post_cholera.ville_rue;
create or replace function post_cholera.check_rue() returns trigger as
$$ begin
if (exists(select *
from post_cholera.ville_rue
where ville=(select ville from post_cholera.ville where new.cpost=cpost limit 1)
and v_cpost != new.cpost
and rue = new.rue
and r_cpost != new.cpost
)) then
raise exception 'the ''ville'' % and the ''rue'' % are already linked to the % ''cpost''',
(select ville from post_cholera.ville where new.cpost = cpost limit 1),
new.rue,
(select v_cpost from post_cholera.ville_rue where ville=(select ville from
post_cholera.ville where new.cpost=cpost limit 1) and rue=new.rue limit 1);
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_rue before insert or update
on post_cholera.rue
for each row
execute procedure post_cholera.check_rue();
create or replace function post_cholera.check_ville() returns trigger as
$$ begin
if (exists(
select *
from post_cholera.ville_rue
where rue = (select rue from post_cholera.rue where new.cpost = rue.cpost limit 1)
and r_cpost != new.cpost
and ville = new.ville
and v_cpost != new.cpost
)) then
raise exception 'the ''ville'' % and the ''rue'' % are already linked to the % ''cpost''',
new.ville,
(select rue from post_cholera.rue where new.cpost = cpost limit 1),
(select r_cpost from post_cholera.ville_rue where rue = (select rue from
post_cholera.rue where new.cpost = cpost limit 1) and ville = new.ville limit 1);
end if;
return new;
end $$
language plpgsql;
create or replace trigger check_ville before insert or update
on post_cholera.ville
for each row
execute procedure post_cholera.check_ville();
