create.sql

-- ###################################################################
-- # Application : SQL script
-- # File        : create.sql
-- # Date        : 04/2016
-- # Function    : Create the database
-- # Nom de base : gestion
-- ###################################################################
DROP TABLE IF EXISTS compte_client;
DROP TABLE IF EXISTS salle;
DROP TABLE IF EXISTS piece;
DROP TABLE IF EXISTS representation;
DROP TABLE IF EXISTS place;
DROP TABLE IF EXISTS billet;


CREATE TABLE compte_client(
   n_compte INTEGER, 
   nom VARCHAR(100) NOT NULL,
   prenom VARCHAR(100) NOT NULL,
   DoB DATE,
   MdP VARCHAR(100) NOT NULL,
   CONSTRAINT pk_compte PRIMARY KEY (n_compte)
   );

CREATE TABLE salle(
   n_salle INTEGER,
   vip_places INTEGER,
   min_places INTEGER,
   moyen_places INTEGER,
   CONSTRAINT pk_salle PRIMARY KEY (n_salle),
   CONSTRAINT vip CHECK (vip_places >= 0),
   CONSTRAINT min CHECK (min_places >= 0),
   CONSTRAINT moyen CHECK (moyen_places >= 0)
   );
   
CREATE TABLE piece(
   nom_piece VARCHAR(100),
   realisateur VARCHAR(100),
   heros VARCHAR(100),
   heroine VARCHAR(100),
   CONSTRAINT pk_piece PRIMARY KEY (nom_piece)
   );

CREATE TABLE representation(
   n_representation INTEGER,
   nom_piece VARCHAR(100),    
   horaire TIME,
   jour DATE,
   n_salle INTEGER,  
   CONSTRAINT pk_representation PRIMARY KEY (n_representation), 
   CONSTRAINT fk_representation_salle 
      FOREIGN KEY (n_salle) REFERENCES Salle ON DELETE RESTRICT, 
   CONSTRAINT fk_representation_piece 
      FOREIGN KEY (nom_piece) REFERENCES piece ON DELETE CASCADE  
   );

CREATE TABLE place(
   n_place INTEGER,    
   p_type VARCHAR(100),
   n_salle INTEGER,  
   CONSTRAINT pk_place PRIMARY KEY (n_place), 
   CONSTRAINT fk_place_salle 
      FOREIGN KEY (n_salle) REFERENCES salle ON DELETE CASCADE, 
   CONSTRAINT type_de_place CHECK (p_type IN ('vip', 'min','moyen'))
   );

CREATE TABLE billet(
   n_compte INTEGER,
   n_place INTEGER,
   n_representation INTEGER,
   pourcentage DECIMAL,
   CONSTRAINT pk_billet PRIMARY KEY (n_compte,n_place,n_representation),
   CONSTRAINT type_de_pourcentage CHECK (pourcentage IN (0.5, 0.6, 0.8)),
   CONSTRAINT fk_billet_place 
      FOREIGN KEY (n_place) REFERENCES place ON DELETE CASCADE, 
   CONSTRAINT fk_billet_compte 
      FOREIGN KEY (n_compte) REFERENCES compte_client ON DELETE CASCADE,
   CONSTRAINT fk_billet_representation 
      FOREIGN KEY (n_representation) REFERENCES representation ON DELETE CASCADE
   );