Cars

[45 min]

Soit la base de données définie par le code SQL LDD ci-après.

1
CREATE TABLE Personne(
2
pk_id NUMERIC PRIMARY KEY,
3
nom VARCHAR(30),
4
prenom VARCHAR(30),
5
code_postal NUMERIC(5),
6
ville VARCHAR(30)
7
);
8
--
9
CREATE TABLE Voiture(
10
pk_immatriculation CHAR(7) PRIMARY KEY,
11
modele VARCHAR(30),
12
marque VARCHAR(30),
13
couleur VARCHAR(30),
14
fk_proprietaire NUMERIC REFERENCES Personne(pk_id)
15
);
16
--
17
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (1, 'Crozat', 'Stéphane', 60200, 'Compiègne');
18
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (2, 'Bernier', 'Emmanuel', 60420, 'Dompierre');
19
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (4, 'Vincent', 'Antoine', 60420, 'Mery-la-Bataille');
20
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (5, 'Boscolo', 'Corinne', 60680, 'Canly');
21
--
22
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('AA123AA', 'Clio', 'Renault', 'Noir', 4);
23
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('AB123NB', '807', 'Peugeot', 'Bleu', 1);
24
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('DE001TR', 'Clio', 'Renault', 'Rouge', 2);
25
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur) VALUES ('AM007JB', '205', 'Peugeot', 'Rose');
26
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('BK200OB', 'Cayenne', 'Porsche', 'Noir', 2);
27
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('ZX987FR', 'Twingo', 'Renault', 'Jaune', 5);

Question

Dessiner les tableaux correspondant à cette base de données.

Solution

données

Question

Rétro-concevoir le MLD correspond à cette base de données.

Solution

1
Personne(#pk_id, nom, prenom, code_postal, ville)
2
Voiture(#pk_immatriculation, modele, marque, couleur, fk_proprietaire=>Personne)

Question

En se basant sur les données existantes, ce MLD respecte-t-il la 3FN ? Si oui, justifier ; sinon proposer un second MLD respectant la 3FN.

Solution

Il n'est pas en 3NF, car modele détermine marque.

1
Personne(#pk_id, nom, prenom, code_postal, ville)
2
Voiture(#pk_immatriculation, fk_modele=>Modele, couleur, fk_proprietaire=>Personne)
3
Modele(#pk_modele, marque)

Question

Rétro-concevoir le MCD correspondant au MLD en 3NF.

Solution

Modèle UML

Question

Écrire une requête (en algèbre relationnel et en SQL) permettant de trouver les véhicules dont le modèle commence par la lettre C.

Solution

1
R = Restriction(Voiture, modele COMME 'C%')
1
SELECT *
2
FROM Voiture
3
WHERE modele LIKE 'C%';

Question

Écrire une requête (en algèbre relationnel et en SQL) permettant de trouver tous les véhicules n'ayant pas de propriétaire.

Solution

1
R = Restriction(Voiture, fk_proprietaire = NULL)
1
SELECT pk_immatriculation
2
FROM Voiture
3
WHERE V.fk_proprietaire IS NULL;

Question

Écrire une requête (en SQL) permettant de trouver les personnes possédant au moins une voiture noire, trié par ordre alphabétique.

Solution

1
SELECT DISTINCT nom, prenom
2
FROM Voiture V, Personne P
3
WHERE V.fk_proprietaire = P.pk_id
4
AND couleur = 'Noir'
5
ORDER BY nom, prenom;

Question

Écrire une requête (en SQL) permettant de compter le nombre de personnes qui possèdent au minimum deux véhicules, par nombre décroissant de voitures.

Solution

1
SELECT pk_id, nom, prenom, COUNT(pk_immatriculation) AS nombre
2
FROM Voiture V, Personne P
3
WHERE V.fk_proprietaire = P.pk_id
4
GROUP BY pk_id, nom, prenom
5
HAVING COUNT(pk_immatriculation) > 1
6
ORDER BY nombre DESC;