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
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
Établissez les DF du modèle sachant que l'on pose que :
Paris a plusieurs codes postaux ;
Il n'existe pas deux voitures avec le même nom de modèle.
Solution
pk_id →nom, prenom, code_postal, ville
pk_immatriculation → modele, marque, couleur, fk_proprietaire
modele → marque
Question
Ce MLD respecte-t-il la 3FN ? 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)
pk_id →nom, prenom, code_postal, ville
pk_immatriculation → fk_modele, couleur, fk_proprietaire
pk_modele → marque
Toutes les DFE sont du type k → a (avec k clé), on est donc en BCNF.
Question
Rétro-concevoir le MCD correspondant au MLD en 3NF.
Solution
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 lister les 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;