create3NF.sql 2.48 KB
Newer Older
NF18 Etudiant's avatar
NF18 Etudiant committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DROP VIEW IF EXISTS comptes_doubles;
DROP TABLE IF EXISTS reservation;
DROP TABLE IF EXISTS place;
DROP TABLE IF EXISTS abonnement;
DROP TABLE IF EXISTS abonne;
DROP TABLE IF EXISTS ticket;
DROP TABLE IF EXISTS occasionnel;
DROP TABLE IF EXISTS compte;
DROP TABLE IF EXISTS parking;
DROP TABLE IF EXISTS zone;
DROP TABLE IF EXISTS vehicule;
DROP TABLE IF EXISTS utilisateur;
DROP TYPE IF EXISTS typePaiement;



CREATE TYPE typePaiement AS ENUM ('guichet','automate','abonne');
CREATE TYPE typeVehicule AS ENUM ('camion','2 roues','vehicule simple');

CREATE TABLE utilisateur (
id INTEGER PRIMARY KEY
);

CREATE TABLE vehicule (
immat VARCHAR PRIMARY KEY,
Pierre Guerin's avatar
Pierre Guerin committed
26
infos JSON,
NF18 Etudiant's avatar
NF18 Etudiant committed
27
28
proprietaire INTEGER NOT NULL,
FOREIGN KEY (proprietaire) REFERENCES utilisateur(id),
Pierre Guerin's avatar
Pierre Guerin committed
29
FOREIGN KEY (modele,marque) REFERENCES tVehicule(modele,marque)
NF18 Etudiant's avatar
NF18 Etudiant committed
30
31
32
33
34
35
36
37
38
39
40
);


CREATE TABLE zone (
nom VARCHAR PRIMARY KEY,
prix FLOAT CHECK (prix>0)
);

CREATE TABLE parking (
nom VARCHAR PRIMARY KEY,
zone VARCHAR REFERENCES zone(nom),
Pierre Guerin's avatar
Pierre Guerin committed
41
NbrePlaces JSON
NF18 Etudiant's avatar
NF18 Etudiant committed
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
);


CREATE TABLE compte (
mail VARCHAR PRIMARY KEY,
mdp VARCHAR NOT NULL,
abonne BOOLEAN NOT NULL
);

CREATE TABLE occasionnel (
id INTEGER,
compte VARCHAR,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES utilisateur(id),
FOREIGN KEY (compte) REFERENCES compte(mail),
UNIQUE (compte)
);

CREATE TABLE ticket (
id INTEGER PRIMARY KEY,
date TIMESTAMP NOT NULL,
type_transac typePaiement NOT NULL,
occasionnel INTEGER,
parking VARCHAR REFERENCES parking(nom) NOT NULL,
FOREIGN KEY (occasionnel) REFERENCES occasionnel(id)
);

CREATE TABLE abonne (
id INTEGER,
compteurFidelite INTEGER CHECK(compteurFidelite > 0),
compte VARCHAR REFERENCES compte(mail) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES utilisateur(id),
FOREIGN KEY (compte) REFERENCES personne(compte)
);

CREATE TABLE personne (
compte VARCHAR PRIMARY KEY,
nom VARCHAR NOT NULL,
prenom VARCHAR NOT NULL
);

CREATE TABLE abonnement (
num_abo INTEGER PRIMARY KEY,
Pierre Guerin's avatar
Pierre Guerin committed
86
infosAbo JSON,
NF18 Etudiant's avatar
NF18 Etudiant committed
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
parking  VARCHAR REFERENCES parking(nom),
abonne INTEGER REFERENCES abonne(id)
);

CREATE TABLE place (
id INTEGER,
vehicule typeVehicule NOT NULL,
parking VARCHAR,
PRIMARY KEY (id, parking)
);

CREATE TABLE reservation (
id INTEGER,
date TIMESTAMP NOT NULL,
type_transac typePaiement NOT NULL,
compte VARCHAR REFERENCES compte(mail),
place_id INTEGER,
place_parking VARCHAR,
FOREIGN KEY (place_id,place_parking) REFERENCES place(id, parking),
PRIMARY KEY (id, place_id, place_parking)
);

CREATE VIEW comptes_doubles AS
SELECT compte FROM occasionnel
INTERSECT
SELECT compte FROM abonne;