Cars
[45 min]
Soit la base de données définie par le code SQL LDD ci-après.
CREATE TABLE Personne(
pk_id NUMERIC PRIMARY KEY,
nom VARCHAR(30),
prenom VARCHAR(30),
code_postal NUMERIC(5),
ville VARCHAR(30)
);
--
CREATE TABLE Voiture(
pk_immatriculation CHAR(7) PRIMARY KEY,
modele VARCHAR(30),
marque VARCHAR(30),
couleur VARCHAR(30),
fk_proprietaire NUMERIC REFERENCES Personne(pk_id)
);
--
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (1, 'Crozat', 'Stéphane', 60200, 'Compiègne');
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (2, 'Bernier', 'Emmanuel', 60420, 'Dompierre');
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (4, 'Vincent', 'Antoine', 60420, 'Mery-la-Bataille');
INSERT INTO Personne (pk_id, nom, prenom, code_postal, ville) VALUES (5, 'Boscolo', 'Corinne', 60680, 'Canly');
--
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('AA123AA', 'Clio', 'Renault', 'Noir', 4);
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('AB123NB', '807', 'Peugeot', 'Bleu', 1);
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('DE001TR', 'Clio', 'Renault', 'Rouge', 2);
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur) VALUES ('AM007JB', '205', 'Peugeot', 'Rose');
INSERT INTO Voiture (pk_immatriculation, modele, marque, couleur, fk_proprietaire) VALUES ('BK200OB', 'Cayenne', 'Porsche', 'Noir', 2);
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
![](../res/cars1.png)
Question
Rétro-concevoir le MLD correspond à cette base de données.
Solution
Personne(#pk_id, nom, prenom, code_postal, ville)
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
.
Personne(#pk_id, nom, prenom, code_postal, ville)
Voiture(#pk_immatriculation, fk_modele=>Modele, couleur, fk_proprietaire=>Personne)
Modele(#pk_modele, marque)
Question
Rétro-concevoir le MCD correspondant au MLD en 3NF.
Solution
![](../res/cars2.png)
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
R = Restriction(Voiture, modele COMME 'C%')
SELECT *
FROM Voiture
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
R = Restriction(Voiture, fk_proprietaire = NULL)
SELECT pk_immatriculation
FROM Voiture
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
SELECT DISTINCT nom, prenom
FROM Voiture V, Personne P
WHERE V.fk_proprietaire = P.pk_id
AND couleur = 'Noir'
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
SELECT pk_id, nom, prenom, COUNT(pk_immatriculation) AS nombre
FROM Voiture V, Personne P
WHERE V.fk_proprietaire = P.pk_id
GROUP BY pk_id, nom, prenom
HAVING COUNT(pk_immatriculation) > 1
ORDER BY nombre DESC;