Br5mYnxR

1drop database if exists emc;
2create database emc;
3use emc;
4
5create table client_type (
6id			int(4) 			PRIMARY KEY,
7name 		varchar(255) 	NOT NULL);
8
9create table client (
10id 			int(4) 			PRIMARY KEY,
11type 		int(4)			NOT NULL,
12name 		varchar(255)	NOT NULL,
13rName 		varchar(255)	NOT NULL,
14rContact 	int				NOT NULL,
15CONSTRAINT 	clientType_fk FOREIGN KEY (type) REFERENCES client_type(id));
16
17create table client_contact (
18name		varchar(255)	NOT NULL,
19contact 	int				NOT NULL,
20email 		varchar(255)	NOT NULL,
21clientID 	int(4)			NOT NULL,
22CONSTRAINT 	clientID_fk FOREIGN KEY (clientID) REFERENCES client(id));
23
24create table acc_type (
25id 			int(4) 			PRIMARY KEY,
26name 		varchar(255)	NOT NULL);
27
28create table acc (
29id 			int(4) 			PRIMARY KEY,
30accType 	int(4)			NOT NULL,
31name 		varchar(255)	NOT NULL,
32maxOccupants int			NOT NULL,
33CONSTRAINT 	accType_fk FOREIGN KEY (accType) REFERENCES acc_type(id));
34
35create table booking (
36id 			int(4) 			PRIMARY KEY,
37client 		int(4)			NOT NULL,
38bookingDate date			NOT NULL,
39startDate 	date			NOT NULL,
40endDate 	date			NOT NULL,
41totalPeople int				NOT NULL,
42acc 		int(4)			NOT NULL,
43catering 	varchar(255)	NOT NULL,
44CONSTRAINT 	client_fk FOREIGN KEY (client) REFERENCES client(id),
45CONSTRAINT 	acc_fk FOREIGN KEY (acc) REFERENCES acc(id),
46CONSTRAINT 	my_catering CHECK (catering = 'RESTAURANT' OR catering = 'SELF'));
47
48create table facilities (
49id			int(4)			PRIMARY KEY,
50name		varchar(255)	NOT NULL,
51charge		smallint		NOT NULL);
52
53create table outdoors (
54id			int(4)			PRIMARY KEY,
55name		varchar(255)	NOT NULL,
56charge 		smallint		NOT NULL);
57
58create table staff (
59id			int(4)			PRIMARY KEY,
60name		varchar(255)	NOT NULL,
61contract	varchar(255)	NOT NULL,
62supervisor	int(4)			NOT NULL,
63CONSTRAINT my_contract CHECK (contract = 'PART-TIME' OR contract = 'FULL-TIME'),
64CONSTRAINT supervisor_fk FOREIGN KEY (supervisor) REFERENCES outdoors(id));
65
66create table booking_facilities (
67bookingID	int(4)			NOT NULL,
68facilities 	int(4)			NOT NULL,
69date		date			NOT NULL,
70CONSTRAINT bookingfacID_fk	FOREIGN KEY (bookingID) REFERENCES booking(id),
71CONSTRAINT facilities_fk FOREIGN KEY (facilities) REFERENCES facilities(id));
72
73create table booking_outdoors (
74bookingID	int(4)			NOT NULL,
75outdoors 	int(4)			NOT NULL,
76date		date			NOT NULL,
77CONSTRAINT bookingoutID_fk FOREIGN KEY (bookingID) REFERENCES booking(id),
78CONSTRAINT outdoors_fk FOREIGN KEY (outdoors) REFERENCES outdoors(id));
79
80create table booking_snoopyLupiz (
81bookingID	int(4)			NOT NULL,
82singleroom	tinyint			NULL,
83doubleroom 	tinyint			NULL,
84kingsuite	tinyint			NULL,
85luxurysuite	tinyint			NULL,
86CONSTRAINT bookingID_fk FOREIGN KEY (bookingID) REFERENCES booking(id));