9f6zEdj3

1CREATE TABLE IF NOT EXISTS hostInformation (
2
3hostID INT AUTO_INCREMENT,
4hostTitle CHAR(4) DEFAULT NULL, 
5hostSurname VARCHAR(30) NOT NULL,
6hostFirstName VARCHAR(30) NOT NULL,
7address1 VARCHAR(30),
8address2 VARCHAR(30),
9address3 VARCHAR(30),
10city VARCHAR(30),
11postCode VARCHAR(10),
12acceptJunior VARCHAR(3) DEFAULT 'Yes',
13petFree VARCHAR(3) DEFAULT 'No',
14nonSmoking VARCHAR(3) DEFAULT 'No',
15acceptSmoker VARCHAR(3) DEFAULT 'Yes',
16PRIMARY KEY (hostID),
17UNIQUE (hostFirstName, hostSurname))
18
19CREATE TABLE IF NOT EXISTS hostUnavailable(
20
21hostID INT,
22unavailableStartDate DATE,
23unavailableEndDate DATE,
24
25FOREIGN KEY(hostID) REFERENCES hostInformation(hostID))
26
27CREATE TABLE IF NOT EXISTS hostRoom( 
28
29hostID INT,
30roomID INT AUTO_INCREMENT,
31bedType VARCHAR(30),
32studentID INT DEFAULT 0,
33
34PRIMARY KEY(roomID),
35FOREIGN KEY(hostID) REFERENCES hostInformation(hostID),
36FOREIGN KEY(studentID) REFERENCES clientInformation(studentID))
37
38CREATE TABLE IF NOT EXISTS bookedRoom(
39
40roomID INT,
41studentID INT,
42
43PRIMARY KEY(roomID),
44FOREIGN KEY(roomID) REFERENCES hostRoom(roomID),
45FOREIGN KEY(studentID) REFERENCES clientInformation(studentID),
46UNIQUE (roomID, studentID))