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);
Dessiner les tableaux correspondant à cette base de données.
Rétro-concevoir le MLD correspond à cette base de données.
Personne(#pk_id, nom, prenom, code_postal, ville)
Voiture(#pk_immatriculation, modele, marque, couleur, fk_proprietaire=>Personne)
É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.
pk_id →nom, prenom, code_postal, ville
pk_immatriculation → modele, marque, couleur, fk_proprietaire
modele → marque
Ce MLD respecte-t-il la 3FN ? Sinon proposer un second MLD respectant la 3FN.
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.
Rétro-concevoir le MCD correspondant au MLD en 3NF.
É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.
R = Restriction(Voiture, modele COMME 'C%')
SELECT *
FROM Voiture
WHERE modele LIKE 'C%';
Écrire une requête (en algèbre relationnel et en SQL) permettant de trouver tous les véhicules n'ayant pas de propriétaire.
R = Restriction(Voiture, fk_proprietaire = NULL)
SELECT pk_immatriculation
FROM Voiture
WHERE V.fk_proprietaire IS NULL;
Écrire une requête (en SQL) permettant de trouver les personnes possédant au moins une voiture noire, trié par ordre alphabétique.
SELECT DISTINCT nom, prenom
FROM Voiture V, Personne P
WHERE V.fk_proprietaire = P.pk_id
AND couleur = 'Noir'
ORDER BY nom, prenom;
É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.
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;
Navigation :