Exercice : Cuisine italienne [La théorie de la normalisation relationnelle]

Cuisine italienne

[30 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.

UML proposé pour la base de données des clients du restaurant

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 NOT NULL
Compose(#ticket=>Ticket(numero), #met=>Met(nom))

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 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')

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

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)
);

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)
);