Cars

[45 min]

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

Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
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);
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.

Question

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

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.

Question

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

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.

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.

Question

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

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.