CREATE TABLE personne(
id_personne VARCHAR(10),
nom VARCHAR(50),
prenom VARCHAR(50),
CONSTRAINT pk_personne PRIMARY KEY id_personne );

CREATE TABLE guichetier(
id_guichetier VARCHAR(10),
CONSTRAINT pk_guichetier PRIMARY KEY id_guichetier,
CONSTRAINT fk_guichetier_personne FOREIGN KEY (id_guichetier) REFERENCES personne );

CREATE TABLE client(
id_client VARCHAR(10),
email VARCHAR(100),
CONSTRAINT pk_client PRIMARY KEY id_client,
CONSTRAINT fk_client_personne FOREIGN KEY (id_client) REFERENCES personne );

CREATE TABLE spectacle(
n_spectacle INT,
nom VARCHAR(100) NOT NULL,
duree INT,
genre VARCHAR(10),
CONSTRAINT pk_billet PRIMARY KEY n_spectacle,
CHECK (genre IN ('danse', 'théâtre', 'concert') ) 
CHECK (duree > 0 AND duree < 4) );

CREATE TABLE salle(
n_salle INT,
nb_places INT,
CONSTRAINT pk_salle PRIMARY KEY n_salle,
CHECk (nb_places > 100 AND nb_places < 500) );

CREATE TABLE representation(
n_salle INT,
r_date DATE,
prix DECIMAL,
n_spectacle INT NOT NULL,
CONSTRAINT pk_representation PRIMARY KEY (n_salle, r_date),
CONSTRAINT fk_rep_salle FOREIGN KEY n_salle REFERENCES salle,
CONSTRAINT fk_rep_spec FOREIGN KEY n_spectacle REFERENCES spectacle,
CHECK (prix > 10 AND prix < 50) );

CREATE TABLE billet(
n_billet INT,
n_salle INT,
r_date DATE,
id_client VARCHAR(10) NOT NULL,
n_spectacle INT,
CONSTRAINT pk_billet PRIMARY KEY (n_billet, n_salle, r_date),
CONSTRAINT fk_billet_rep FOREIGN KEY (n_salle, r_date) REFERENCES representation,
CONSTRAINT fk_billet_client FOREIGN KEY id_client REFERENCES client,
CONSTRAINT fk_billet_spec FOREIGN KEY n_spectacle REFERENCES spectacle );

CREATE TABLE emmettre(
n_billet INT,
n_salle INT,
r_date DATE,
id_guichetier VARCHAR(10),
etat VARCHAR(10),
PRIMARY KEY
);



