This repository has been archived by the owner on Oct 9, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
criar.sql
146 lines (119 loc) · 3.21 KB
/
criar.sql
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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
86
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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
DROP TABLE IF EXISTS Cliente;
DROP TABLE IF EXISTS Bilhete;
DROP TABLE IF EXISTS Geral;
DROP TABLE IF EXISTS Diario;
DROP TABLE IF EXISTS Dia;
DROP TABLE IF EXISTS Banda;
DROP TABLE IF EXISTS Membro;
DROP TABLE IF EXISTS Genero;
DROP TABLE IF EXISTS Palco;
DROP TABLE IF EXISTS Infrastrutura;
DROP TABLE IF EXISTS WC;
DROP TABLE IF EXISTS Restauracao;
DROP TABLE IF EXISTS Campismo;
DROP TABLE IF EXISTS Staff;
DROP TABLE IF EXISTS SubContrato;
DROP TABLE IF EXISTS Sponsor;
DROP TABLE IF EXISTS GeneroPalco;
DROP TABLE IF EXISTS GeneroBanda;
DROP TABLE IF EXISTS StaffInfrastrutura;
CREATE TABLE Cliente (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
NIF TEXT UNIQUE
);
CREATE TABLE Bilhete (
id INTEGER PRIMARY KEY AUTOINCREMENT,
clientId INTEGER REFERENCES Client(id)
);
/* < Sub classes de bilhete > */
CREATE TABLE Geral (
bilheteId INTEGER PRIMARY KEY REFERENCES Bilhete(id),
price INTEGER
);
CREATE TABLE Diario (
bilheteId INTEGER PRIMARY KEY REFERENCES Bilhete(id),
price INTEGER,
dayId INTEGER REFERENCES Dia(id)
);
/* </ Sub classes de bilhete > */
CREATE TABLE Dia (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATETIME,
startHour INTEGER,
endHour INTEGER
);
CREATE TABLE Banda (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
membros TEXT, /* Devia ser outra tabela?*/
contacto TEXT
);
CREATE TABLE Membro (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE Genero (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
/* many-to-many genero banda */
CREATE TABLE GeneroBanda (
bandaId INTEGER NOT NULL,
generoId INTEGER NOT NULL,
PRIMARY KEY (bandaId, generoId)
);
CREATE TABLE Infrastrutura (
id INTEGER PRIMARY KEY AUTOINCREMENT,
disponibilidade INTEGER,
capacidade INTEGER,
localizacao TEXT
);
/* < Sub classes de infrastrutura > */
CREATE TABLE Palco (
infrastruturaId INTEGER PRIMARY KEY REFERENCES Infrastrutura(id),
name TEXT
);
CREATE TABLE WC (
infrastruturaId INTEGER PRIMARY KEY REFERENCES Infrastrutura(id),
showers INTEGER,
toilets INTEGER,
sinks INTEGER
);
CREATE TABLE Restauracao (
infrastruturaId INTEGER PRIMARY KEY REFERENCES Infrastrutura(id),
tables INTEGER,
chairs INTEGER
);
CREATE TABLE Campismo (
infrastruturaId INTEGER PRIMARY KEY REFERENCES Infrastrutura(id),
campingZones INTEGER,
trashCans INTEGER
);
/* </ Sub classes de infrastrutura > */
/* many-to-many genero palco */
CREATE TABLE GeneroPalco (
palcoId INTEGER NOT NULL REFERENCES Palco(id),
generoId INTEGER NOT NULL REFERENCES Genero(id),
PRIMARY KEY (palcoId, generoId)
);
CREATE TABLE Staff (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nome TEXT,
turno TEXT,
disponibilidade INTEGER
);
CREATE TABLE SubContrato (
staffId INTEGER PRIMARY KEY REFERENCES Staff(id),
empresa TEXT
);
CREATE TABLE Sponsor (
staffId INTEGER PRIMARY KEY REFERENCES Staff(id),
empresa TEXT
);
/* many-to-many staff infrastrutura */
CREATE TABLE StaffInfrastrutura (
staffId INTEGER NOT NULL REFERENCES Staff(id),
infrastruturaId INTEGER NOT NULL REFERENCES Infrastrutura(id),
PRIMARY KEY (staffId, infrastruturaId)
);