cutZaiAi

1-- \i GenBDCine.sql
2SET datestyle TO "DMY" ;
3SET datestyle TO "SQL" ;
4
5drop table if exists jouer;
6drop table if exists projection;
7drop table if exists film;
8drop table if exists individu;
9drop table if exists cinema;
10
11create table individu (
12    num_individu integer primary key, 
13    nom varchar(64) NOT NULL, 
14    prenom varchar(64) NOT NULL
15);
16
17create table film (
18    num_film integer primary key, 
19    num_realisateur integer references individu ON UPDATE CASCADE ON DELETE RESTRICT, 
20    titre varchar(128) NOT NULL, 
21    genre varchar(64) DEFAULT 'Drame',
22    annee date NOT NULL check (annee >= '01/01/1929'),
23    UNIQUE (num_realisateur,titre,annee)
24);
25
26create table cinema (
27    num_cinema integer primary key, 
28    nom varchar(64), 
29    adresse varchar(128));
30
31create table jouer (
32    num_acteur integer references individu ON UPDATE CASCADE ON DELETE SET NULL, 
33    num_film integer references film ON UPDATE CASCADE ON DELETE RESTRICT,
34    role varchar(128),
35    UNIQUE (num_acteur, num_film),
36    UNIQUE (num_film,role)
37);
38
39create table projection (
40    num_cinema integer references cinema ON UPDATE CASCADE ON DELETE CASCADE, 
41    num_film integer references film ON UPDATE CASCADE ON DELETE CASCADE,
42    jour date,
43    primary key (num_cinema, num_film, jour));
44
45\copy individu from individu.txt
46\copy cinema from cinema.txt
47\copy film from film.txt
48\copy jouer from jouer.txt
49\copy projection from projection.txt