Cuisine italienne
[45 min]
Un groupe de projet NF17 a réalisé un MCD pour constituer une base de données des consommations des clients d'un restaurant italien.
Informations extraites du document de synthèse fourni par les étudiants : « Une table est caractérisée par un numéro et possède toujours une capacité maximale théorique et un type. Un client, en réalité celui qui paye l'addition, possède un nom, un prénom et une date de naissance et peut avoir en plus une adresse courriel ou un numéro de téléphone (tous les deux facultatifs). Un ticket concerne un client, et chaque ticket possède un numéro unique et toujours renseigné, un nombre de couvert et le montant de l'addition réglé. Nous n'avons pas utilisé la date du ticket comme clé primaire car il est possible d'avoir la génération de deux tickets exactement au même moment. Un met est une entrée, un plat ou un dessert ; et il possède un prix. »
« NB : Un attribut supplément dans la classe table permet de faire payer un potentiel supplément en fonction du type de table (petite terrasse, grande terrasse, etc.). »
Question
Proposez un modèle logique de données en relationnel à partir de l'UML, en ajoutant éventuellement des clés artificielles lorsque c'est utile, et en explicitant la solution adoptée pour transformer l'héritage.
Solution
Héritage exclusif
Héritage complet
Classe mère abstraite
On choisit l'héritage par classe mère pour des raisons de simplicité : 2 tables contre 6 à cause de l'association N:M.
Table(#numero, nbCouvert, type, supplement) avec nbCouvert, type NOT NULL
Client(#id:integer, nom, prenom, dateDeNaissance, courriel, telephone) avec (nom, prenom, dateDeNaissance) clé candidate
Ticket(#numero, date, nbCouvert, addition, table=>Table(numero), client=>Client(id)) avec date, nbCouvert, table, client NOT NULL
Met(#nom, prix, type:{'E', 'P', 'D'}) avec prix et type NOT NULL
Compose(#ticket=>Ticket(numero), #met=>Met(nom))
Remarque :
Les types non précisés sont ceux du diagramme UML.
Question
Écrivez en algèbre relationnelle la ou les vues induites par la transformation de la relation d'héritage.
Solution
vEntree = RESTRICTION (Met, Type = 'E')
vPlat = RESTRICTION (Met, Type = 'P')
vDessert = RESTRICTION (Met, Type = 'D')
Question
Énoncez pour chaque relation du schéma la liste des DF sous la forme d'une fermeture transitive.
Solution
Table
numero -> nbCouvert, type, supplement
type -> supplement
Client
id -> nom, prenom, dateDeNaissance, courriel, telephone
nom, prenom, dateDeNaissance -> id, courriel, telephone
Ticket
numero -> date, nbCouvert, addition, table, client
Met
nom -> prix, type
Question
Ce MLD est-il en 3FN ? Si oui, justifiez précisément ; sinon proposez une solution pour respecter la troisième forme normale.
Solution
Le modèle n'est pas en 3NF, car un attribut non clé (supplement
) est déterminé par un autre attribut non clé (type
).
Il faut décomposer table
en :
Type_Table(#type, supplement)
Table (#numero, nbCouvert, type=>type_table)
Question
Écrivez les instructions SQL LDD permettant de créer la base de données PostgreSQL correspondant au modèle en 3NF.
Solution
CREATE TABLE Type_Table(
type VARCHAR PRIMARY KEY,
supplement REAL
);
CREATE TABLE Table(
numero INTEGER PRIMARY KEY,
nbCouvert INTEGER NOT NULL,
type VARCHAR REFERENCES Type_Table(type)
);
CREATE TABLE Client(
id INTEGER PRIMARY KEY,
nom VARCHAR NOT NULL,
prenom VARCHAR NOT NULL,
dateDeNaissance DATETIME NOT NULL,
courriel VARCHAR,
telephone VARCHAR,
UNIQUE(nom, prenom, dateDeNaisance)
);
CREATE TABLE Ticket(
numero INTEGER PRIMARY KEY,
date DATETIME NOT NULL,
nbCouvert INTEGER NOT NULL,
addition DECIMAL,
table INTEGER REFERENCES Table(numero),
client INTEGER REFERENCES Client(id)
);
CREATE TABLE Met(
nom VARCHAR PRIMARY KEY,
prix REAL NOT NULL,
type CHAR(1) CHECK (type='E' OR type='P' OR type='D')
);
CREATE TABLE Compose(
ticket INTEGER REFERENCES Ticket(numero),
met VARCHAR REFERENCES Met(nom),
PRIMARY KEY(ticket, met)
);