u0rv3JAQ

1--== PARTIE 1 ==--
2
3-- \i GenBDCine.sql
4SET datestyle TO "DMY" ;
5SET datestyle TO "SQL" ;
6
7drop table if exists jouer;
8drop table if exists projection;
9drop table if exists film;
10drop table if exists individu;
11drop table if exists cinema;
12
13create table individu (
14    num_individu integer primary key, 
15    nom varchar(64) NOT NULL, 
16    prenom varchar(64) NOT NULL
17);
18
19create table film (
20    num_film integer primary key, 
21    num_realisateur integer references individu, 
22    titre varchar(128) NOT NULL, 
23    genre varchar(64) DEFAULT 'Drame',
24    annee date NOT NULL check (annee >= '01/01/1929'),
25    UNIQUE (num_realisateur,titre,annee)
26);
27
28create table cinema (
29    num_cinema integer primary key, 
30    nom varchar(64), 
31    adresse varchar(128));
32
33create table jouer (
34    num_acteur integer references individu, 
35    num_film integer references film,
36    role varchar(128),
37    primary key (num_acteur, num_film),
38    UNIQUE (num_film,role)
39);
40
41create table projection (
42    num_cinema integer references cinema, 
43    num_film integer references film,
44    jour date,
45    primary key (num_cinema, num_film, jour));
46
47\copy individu from individu.txt
48\copy cinema from cinema.txt
49\copy film from film.txt
50\copy jouer from jouer.txt
51\copy projection from projection.txt
52
53/*
54requetes a tester :
55
56INSERT INTO individu (num_individu) VALUES (20);
57INSERT INTO individu (num_individu,nom) VALUES (20,'toto');
58INSERT INTO film (num_film) VALUES (10);
59INSERT INTO film (num_film,titre) VALUES (10,'filmtoto');
60INSERT INTO film (num_film,titre,annee) VALUES (10,'filmtoto','31/12/1928');
61INSERT INTO film (num_film,titre,annee) VALUES (10,'filmtoto','01/01/1929'); 
62		=> constater la veleur de 'genre'
63INSERT INTO jouer VALUES (2,5,'Grace');
64INSERT INTO film VALUES (15,13,'Dogville','Comique','01/01/2002');
65
66*/
67
68
69
70--== PARTIE 2 ==--
71
72-- \i GenBDCine.sql
73SET datestyle TO "DMY" ;
74SET datestyle TO "SQL" ;
75
76drop table if exists jouer;
77drop table if exists projection;
78drop table if exists film;
79drop table if exists individu;
80drop table if exists cinema;
81
82create table individu (
83    num_individu integer primary key, 
84    nom varchar(64) NOT NULL, 
85    prenom varchar(64) NOT NULL
86);
87
88create table film (
89    num_film integer primary key, 
90    num_realisateur integer references individu ON UPDATE CASCADE ON DELETE RESTRICT, 
91    titre varchar(128) NOT NULL, 
92    genre varchar(64) DEFAULT 'Drame',
93    annee date NOT NULL check (annee >= '01/01/1929'),
94    UNIQUE (num_realisateur,titre,annee)
95);
96
97create table cinema (
98    num_cinema integer primary key, 
99    nom varchar(64), 
100    adresse varchar(128));
101
102create table jouer (
103    num_acteur integer references individu ON UPDATE CASCADE ON DELETE SET NULL, 
104    num_film integer references film ON UPDATE CASCADE ON DELETE RESTRICT,
105    role varchar(128),
106    UNIQUE (num_acteur, num_film),
107    UNIQUE (num_film,role)
108);
109
110create table projection (
111    num_cinema integer references cinema ON UPDATE CASCADE ON DELETE CASCADE, 
112    num_film integer references film ON UPDATE CASCADE ON DELETE CASCADE,
113    jour date,
114    primary key (num_cinema, num_film, jour));
115
116\copy individu from individu.txt
117\copy cinema from cinema.txt
118\copy film from film.txt
119\copy jouer from jouer.txt
120\copy projection from projection.txt
121
122/*
123requetes a tester :
124
125DELETE FROM individu WHERE num_individu=19;
126DELETE FROM individu WHERE num_individu=1;
127DELETE FROM film WHERE num_film=8;
128DELETE FROM film WHERE num_film=6;
129UPDATE film SET num_film=6 WHERE num_film=8;
130*/