Conception de bases de données

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

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

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

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