CREATE TABLE employee -- Creates table 'employee' (Strong entity)
(
    employee_id INTEGER NOT NULL,
    name VARCHAR(255),
    email VARCHAR(255),
    salary INTEGER,
    address VARCHAR(255),
    city VARCHAR(255),
    postal_code CHAR(6),
    PRIMARY KEY(employee_id)
);

INSERT INTO employee -- Insert data into employee table
VALUES ('500111222', 'Jared', 'jared.hump@hotmail.com','40000', '122 Canada Drive', 'Toronto', 'L6Y0R3');

INSERT INTO employee -- Insert data into employee table
VALUES ('500111223', 'Ishaan', 'ishaan.patel@hotmail.com','97000',' 73 Church Street', 'Toronto', 'L6Y0T2');

INSERT INTO employee -- Insert data into employee table
VALUES ('500111224', 'Jesse', 'jesse.olive@live.com', '34000' ,'73 Suits Street', 'New York', 'L6Y0A1');

INSERT INTO employee -- Insert data into employee table
VALUES ('500111225', 'Hema', 'hema.orange@hotmail.com','30000','188 Sunny View Street', 'Brampton', 'L6Y0P6');

SELECT * FROM employee; -- To view employee table

CREATE TABLE receptionist AS -- Duplicate employee into new table, 'receptionist' (Strong entity)
SELECT * FROM employee

CREATE TABLE staff AS -- Duplicate employee into new table, 'staff' (Strong entity)
SELECT * FROM employee

CREATE TABLE dependent -- Create table 'dependent' (Weak entity)
(
    name VARCHAR(255) NOT NULL,
    employee_id INTEGER NOT NULL,
    email VARCHAR(255),
    address VARCHAR(255),
    PRIMARY KEY(name),
    FOREIGN KEY(employee_id) REFERENCES employee(employee_id)
);

INSERT INTO dependent (name, employee_id, email, address) -- Insert data into dependent table
VALUES ('John','500111222','john.dep@live.com','123 Church Street');

INSERT INTO dependent (name, employee_id, email, address) -- Insert data into dependent table
VALUES ('Steve','500111223','steve.dep@live.com','188 Church Street');

INSERT INTO dependent (name, employee_id, email, address) -- Insert data into dependent table
VALUES ('Anurag','500111224','anurag.dep@live.com','423 Church Street');

INSERT INTO dependent (name, employee_id, email, address) -- Insert data into dependent table
VALUES ('Harry','500111225','harry.dep@live.com','500 Church Street');

SELECT * FROM dependent; -- To view dependent table

CREATE TABLE dependent_of -- Create table 'dependent_of' (Weak relationship)
(
    employee_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    relationship VARCHAR(255),
    FOREIGN KEY(employee_id) REFERENCES employee(employee_id),
    FOREIGN KEY(name) REFERENCES dependent(name)
);

INSERT INTO dependent_of (employee_id, name, relationship) -- Insert data into dependent_off table
VALUES ('500111222','John','Friend');

INSERT INTO dependent_of (employee_id, name, relationship) -- Insert data into dependent_off table
VALUES ('500111223','Steve','Father');

INSERT INTO dependent_of (employee_id, name, relationship) -- Insert data into dependent_off table
VALUES ('500111224','Anurag','Boyfriend');

INSERT INTO dependent_of (employee_id, name, relationship) -- Insert data into dependent_off table
VALUES ('500111225','Harry','Grandfather');

SELECT * FROM dependent_of; -- To view dependent_of table

CREATE TABLE hotel -- Create table 'hotel' (Strong entity)
(
    hotel_id INT NOT NULL,
    name VARCHAR(255),
    location VARCHAR(255),
    num_rooms INTEGER,
    PRIMARY KEY(hotel_id)
);

INSERT INTO hotel -- Insert data into hotel table
VALUES ('7','Loda Paradise','69 BC Drive','69');

SELECT * FROM hotel; -- To view hotel table

CREATE TABLE supplier -- Create table 'supplier' (Weak entity)
(
    supplier_id INT  NOT NULL,
    name VARCHAR(255),
    PRIMARY KEY(supplier_id)
);

INSERT INTO supplier -- Insert data into supplier table
VALUES ('223','Carpet Depot');

INSERT INTO supplier -- Insert data into supplier table
VALUES ('555','Sleep Country');

INSERT INTO supplier -- Insert data into supplier table
VALUES ('007','Leons Furniture');

SELECT * FROM supplier; -- To view supplier table

CREATE TABLE has_or_had -- Create table 'has' (Strong relationship)
(
    hotel_id INT NOT NULL,
    employee_id INT NOT NULL,
    supplier_id INT NOT NULL,
    FOREIGN KEY(hotel_id) REFERENCES hotel(hotel_id),
    FOREIGN KEY(employee_id) REFERENCES employee(employee_id),
    FOREIGN KEY(supplier_id) REFERENCES supplier(supplier_id)
);

CREATE TABLE guest -- Create table 'guest' (Strong entity)
(
    guest_id INTEGER NOT NULL,
    number_guests INTEGER,
    last_name VARCHAR(255),
    first_name VARCHAR(255),
    address VARCHAR(255),
    email VARCHAR(255),
    phone_number CHAR(11),
    PRIMARY KEY(guest_id)
);

INSERT INTO guest -- Insert data into guest table
VALUES ('10','1','Bryant','Kobe','12 Hollywood Drive','kobe.bryant@goat.com','905-133-824');

INSERT INTO guest -- Insert data into guest table
VALUES ('11','2','Jordan','Michael','23 Chicago Street','Michael.jordan@goat.com','905-445-823');

INSERT INTO guest -- Insert data into guest table
VALUES ('12','4','James','LeBum','22 Cleveland Court','LeBum.james@weak.com','905-123-776');

SELECT * FROM guest; -- To view guest table

CREATE TABLE receptionist AS -- Duplicate employee into new table, 'receptionist' (Strong entity)
SELECT * FROM employee

CREATE TABLE staff AS -- Duplicate employee into new table, 'staff' (Strong entity)
SELECT * FROM employee

SELECT * FROM receptionist; -- To view receptionist table
SELECT * FROM staff; -- To view staff table

CREATE TABLE rooms -- Create table 'rooms' (Strong entity)
(
    room_id INT NOT NULL,
    STATUS CHAR(10),
    price VARCHAR(225),
    TYPE CHAR(15),
    PRIMARY KEY(room_id)
);

INSERT INTO rooms -- Insert data into rooms table
VALUES ('2','available','$122','single');

INSERT INTO rooms -- Insert data into rooms table
VALUES ('3','available','$1220','double');

INSERT INTO rooms -- Insert data into rooms table
VALUES ('4','available','$2000','royal suite');

INSERT INTO rooms -- Insert data into rooms table
VALUES ('5','available','$1250','queen doubles');

SELECT * FROM rooms; -- To view rooms table

CREATE TABLE book_or_request -- Create table 'book_or_request' (Strong relationship)
(
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    book_date DATE,
    check_in_date DATE,
    check_out_date DATE,
    cancel_date DATE,
    extend_date DATE,
    PRIMARY KEY(book_date),
    FOREIGN KEY(guest_id) REFERENCES guest(guest_id),
    FOREIGN KEY(room_id) REFERENCES rooms(room_id)
);

SELECT * FROM book_or_request;  -- To view book_or_request table

INSERT INTO book_or_request -- Insert data into book_or_request table
VALUES ('10','2','2017-08-23','2017-08-23','2017-08-29',NULL,NULL);

INSERT INTO book_or_request -- Insert data into book_or_request table
VALUES ('11','3','2017-03-11','2017-04-23','2017-04-30','2017-03-25',NULL);

INSERT INTO book_or_request -- Insert data into book_or_request table
VALUES ('12','4','2017-03-14','2017-04-07','2017-04-22',NULL,'2017-05-07');

CREATE TABLE facility -- Create table 'facility' (Strong entity)
(
    facility_id INT NOT NULL,
    name VARCHAR(225) NOT NULL,
    STATUS VARCHAR(255),
    PRIMARY KEY(facility_id)
);

INSERT INTO facility -- Insert data into facility table
VALUES ('1','Swimming Pool', 'Open');

INSERT INTO facility -- Insert data into facility table
VALUES ('2','Gym','Open');

INSERT INTO facility -- Insert data into facility table
VALUES ('3','Theatre','Closed');

SELECT * FROM facility; -- To view facility table

CREATE TABLE payment -- Create table 'payment' (Strong entity)
(
    guest_id INT NOT NULL,
    bill INT,
    PRIMARY KEY(bill),
    FOREIGN KEY(guest_id) REFERENCES guest(guest_id)
);

INSERT INTO payment -- Insert data into payment table
VALUES ('10','122');

INSERT INTO payment -- Insert data into payment table
VALUES ('11','0');

INSERT INTO payment -- Insert data into payment table
VALUES ('12','2000');

SELECT * FROM payment; -- To view payment table

CREATE TABLE reservations -- Create table 'reservations' (Strong entity)
(
    confirm_guest CHAR(6) NOT NULL,
    guest_id INT NOT NULL,
    book_date DATE,
    check_in_date DATE,
    check_out_date DATE,
    cancel_date DATE,
    extend_date DATE,
    PRIMARY KEY(confirm_guest),
    FOREIGN KEY(guest_id) REFERENCES guest(guest_id),
    FOREIGN KEY(book_date) REFERENCES book_or_request(book_date)
);

SELECT *  FROM reservations; -- To view reservations table

INSERT INTO reservations (confirm_guest,guest_id,book_date,check_in_date,check_out_date,cancel_date,extend_date) -- To insert data into reservations table
VALUES ('Y-10','10','2017-08-23','2017-08-23','2017-08-29',NULL,NULL);

INSERT INTO reservations (confirm_guest,guest_id,book_date,check_in_date,check_out_date,cancel_date,extend_date) -- To insert data into reservations table
VALUES ('N-11','11','2017-03-11','2017-04-23','2017-04-30','2017-03-25',NULL);

INSERT INTO reservations (confirm_guest,guest_id,book_date,check_in_date,check_out_date,cancel_date,extend_date) -- To insert data into reservations table
VALUES ('Y-12','12','2017-03-14','2017-04-07','2017-04-22',NULL,'2017-05-07');

-- I had a query to copy columns from book_or_request but for some reason it did not work. So had to copy myself. Will be done for assignment 5.

CREATE TABLE maintains -- Create table 'maintains' (Strong entity)
(
    employee_id INT,
    room_id INT,
    facility_id INT,
    FOREIGN KEY(employee_id) REFERENCES employee(employee_id),
    FOREIGN KEY(room_id) REFERENCES rooms(room_id),
    FOREIGN KEY(facility_id) REFERENCES facility(facility_id)
);

SELECT * FROM maintains; -- To view maintain table

INSERT INTO maintains -- To insert data into maintains table
VALUES ('500111222','5','1');

INSERT INTO maintains -- To insert data into maintains table
VALUES ('500111224','4','2');

INSERT INTO maintains -- To insert data into maintains table
VALUES ('500111225','3','3');

CREATE TABLE confirms -- Create table 'confirms' (Strong relationship)
(
    employee_id INT,
    guest_id INT,
    bill INT,
    FOREIGN KEY(employee_id) REFERENCES employee(employee_id),
    FOREIGN KEY(guest_id) REFERENCES guest(guest_id),
    FOREIGN KEY(bill) REFERENCES payment(bill)
);

SELECT * FROM confirms; -- To view confirms table

INSERT INTO confirms -- To insert data into confirms table
VALUES ('500111223','10','122');

INSERT INTO confirms -- To insert data into confirms table
VALUES ('500111223','11','0');

INSERT INTO confirms -- To insert data into confirms table
VALUES ('500111223','12','2000');

CREATE TABLE orders -- Create table 'orders' (Strong relationship)
(
    employee_id INT,
    supplier_id INT,
    amount INT NOT NULL,
    date_in DATE,
    date_out DATE,
    PRIMARY KEY(amount),
    FOREIGN KEY(employee_id) REFERENCES employee(employee_id),
    FOREIGN KEY(supplier_id) REFERENCES supplier(supplier_id)
);

DROP TABLE orders;
SELECT * FROM orders; -- To view orders table

INSERT INTO orders -- To insert data into orders table
VALUES ('500111223','223','12','2017-04-20','2017-04-29');

INSERT INTO orders -- To insert data into orders table
VALUES ('500111223','555','10','2017-04-21','2017-04-29');

INSERT INTO orders -- To insert data into orders table
VALUES ('500111223','007','15','2017-03-15',NULL);

CREATE TABLE pays -- Create table 'pays' (Strong relationship)
(
    guest_id INT,
    bill INT,
    method VARCHAR(255),
    PRIMARY KEY(method),
    FOREIGN KEY(guest_id) REFERENCES guest(guest_id),
    FOREIGN KEY(bill) REFERENCES payment(bill)
);

INSERT INTO pays -- To insert data into pays
VALUES ('10','122','VISA-10');

INSERT INTO pays -- To insert data into pays
VALUES ('12','2000','CASH-12');

SELECT * FROM pays; -- To view pays table