kdnuJBRi

1ROP TABLE IF EXISTS vols;
2DROP TABLE IF EXISTS lignes;
3DROP TABLE IF EXISTS aeroports;
4DROP TABLE IF EXISTS pilotes;
5DROP TABLE IF EXISTS avions;
6
7CREATE TABLE aeroports(
8rcode char(3) NOT NULL UNIQUE,
9libelle text,
10ville text,
11adresseA text,
12CONSTRAINT pk_aeroports PRIMARY KEY (rcode)
13);
14
15CREATE TABLE avions(
16ano int NOT NULL UNIQUE,
17type text,
18places int CHECK (places BETWEEN 100 AND 500),
19compagnie text,
20CONSTRAINT pk_avions PRIMARY KEY (ano)
21);
22
23CREATE TABLE lignes(
24lno int NOT NULL UNIQUE,
25dep char(3),
26arr char(3),
27CONSTRAINT pk_lignes PRIMARY KEY (lno),
28CONSTRAINT fk_aeroport_dep FOREIGN KEY (dep)
29REFERENCES aeroports (rcode)
30ON UPDATE CASCADE
31ON DELETE SET NULL,
32CONSTRAINT fk_aeroport_arr FOREIGN KEY (arr)
33REFERENCES aeroports (rcode)
34ON UPDATE CASCADE
35ON DELETE SET NULL
36);
37
38CREATE TABLE pilotes(
39pno serial,
40nom text,
41prenom text,
42adresse text,
43CONSTRAINT pk_pilotes PRIMARY KEY (pno)
44);
45
46CREATE TABLE vols(
47ano serial, 
48pno serial,
49lno int,
50h_dep time,
51h_arr time,
52CONSTRAINT fk_avions FOREIGN KEY (ano)
53REFERENCES avions (ano)
54ON UPDATE CASCADE,
55CONSTRAINT fk_pilotes FOREIGN KEY (pno)
56REFERENCES pilotes (pno)
57ON UPDATE CASCADE,
58CONSTRAINT fk_lignes FOREIGN KEY (lno)
59REFERENCES lignes (lno)
60ON UPDATE CASCADE,
61CONSTRAINT pk_vols PRIMARY KEY (ano,pno,lno)
62);