CREATE TABLE employee -- Creates table 'employee' (Strong entity)
(
    emp_id INT NOT NULL,
    name varchar(255),
    email varchar(255),
    address varchar(255),
    city varchar(255),
    state varchar(255),
    postal_code char(6),
    PRIMARY KEY(emp_id)
);

INSERT INTO employee -- Inserts 1 row of data into employee table
VALUES ('500111222', 'Jared', 'jared.hump@hotmail.com', '122 Canada Drive', 'Toronto', 'Ontario', 'L6Y0R3');

INSERT INTO employee -- Inserts 1 row of data into employee table
VALUES ('500111223', 'Ishaan', 'ishaan.patel@hotmail.com', '134 Church Street', 'Toronto', 'Ontario', 'L6Y0Q9');

INSERT INTO employee -- Inserts 1 row of data into employee table
VALUES ('500111224', 'Jess', 'jess.olive@live.com', '73 Suits Street', 'New York', 'New York', '155333');

INSERT INTO employee -- Inserts 1 row of data into employee table
VALUES ('500111225', 'Hema', 'hema.often@hotmail.com', '188 Sunny View Street', 'Brampton', 'Ontario', 'L6Y0P6');

SELECT * FROM employee; -- To view table of employee

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

INSERT INTO receptionist
SELECT * FROM employee
WHERE

INSERT INTO staff
SELECT * FROM employee
WHERE

SELECT * FROM receptionist;
SELECT * FROM staff;

CREATE TABLE dependent
(
    name varchar(255) NOT NULL,
    emp_id INT,
    email varchar(255),
    address varchar(255),
    PRIMARY KEY(name),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id)
);

INSERT INTO dependent (name, email, address)
VALUES ('John','john.dep@live.com','123 Church Street');

INSERT INTO dependent (name, email, address)
VALUES ('Steve','steve.dep@live.com','188 Church Street');

INSERT INTO dependent (name, email, address)
VALUES ('Anurag','anurag.dep@live.com','423 Church Street');

INSERT INTO dependent (name, email, address)
VALUES ('Harry','harry.dep@live.com','500 Church Street');

SELECT * FROM dependent;

CREATE TABLE dependent_of
(
    emp_id INT NOT NULL,
    name varchar(255) NOT NULL,
    relationship varchar(255),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
    FOREIGN KEY(name) REFERENCES dependent(name)
);

CREATE TABLE hotel
(
    hotel_id INT NOT NULL
    name varchar(255),
    location varchar(255),
    num_rooms INTEGER,
    PRIMARY KEY(hotel_id)
);

CREATE TABLE has
(
    hotel_id INT NOT NULL,
    emp_id INT NOT NULL,
    supplier_id INT NOT NULL,
    FOREIGN KEY(hotel_id) REFERENCES hotel(hotel_id),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
    FOREIGN KEY(supplier_id) REFERENCES supplier(supplier_id)
);

CREATE TABLE guests
(
    guest_id INT NOT NULL,
    number_guests INT,
    surname varchar(255),
    first_name varchar(255),
    address varchar(255),
    email varchar(255),
    phone_number char,
    PRIMARY KEY(guest_id)
);

AlTER TABLE guests
DROP COLUMN num_guests;

CREATE TABLE book_or_request
(
    book_date varchar(255),
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    cancel_date varchar(255),
    extend_date varchar(255),
    check_in varchar(255),
    check_out varchar(255),
    PRIMARY KEY(book_date),
    FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
    FOREIGN KEY(room_id) REFERENCES rooms(room_id)
);

CREATE TABLE rooms
(
    room_id INT NOT NULL,
    status char(10),
    price varchar(225),
    type char(10),
    PRIMARY KEY(room_id)
);

CREATE TABLE facilities
(
    facility_id INT NOT NULL,
    name varchar(225),
    status varchar(255),
    PRIMARY KEY(facility_id)
);

CREATE TABLE payment
(
    bill INT NOT NULL,
    guest_id INT NOT NULL,
    PRIMARY KEY(bill),
    FOREIGN KEY(guest_id) REFERENCES guests(quest_id)
);

CREATE TABLE supplier
(
    supplier_id INT  NOT NULL,
    PRIMARY KEY(supplier_id)
);

CREATE TABLE season
(
    name varchar(255) NOT NULL,
    end_date char(15),
    start_date char(15),
    PRIMARY KEY(name)
);

CREATE TABLE discount
(
    amount_off INT NOT NULL,
    PRIMARY KEY(amount_off)
);

CREATE TABLE reservations
(
    confirm_guest varchar(255) NOT NULL,
    guest_id INT NOT NULL,
    book_date varchar(255),
    check_in varchar(255),
    check_out varchar(255),
    extend_date varchar(255),
    cancel_date varchar(255),
    PRIMARY KEY(confirm_guest),
    FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
    FOREIGN KEY(book_date) REFERENCES book_or_request(book_date)
);

CREATE TABLE maintains
(
    emp_id INT,
    room_id INT,
    facility_id INT,
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
    FOREIGN KEY(room_id) REFERENCES rooms(room_id),
    FOREIGN KEY(facility_id) REFERENCES facilities(facility_id)
);

CREATE TABLE confirms
(
    emp_id INT,
    guest_id INT,
    bill INT,
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
    FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
    FOREIGN KEY(bill) REFERENCES payment(bill)
);

CREATE TABLE orders
(
    emp_id INT,
    supplier_id INT,
    amount INT,
    item INT,
    date_in varchar(255),
    date_out varchar(255),
    PRIMARY KEY(amount),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
    FOREIGN KEY(supplier_id) REFERENCES supplier(supplier_id)
);

CREATE TABLE pays
(
    guest_id INT,
    bill INT,
    method varchar(255),
    PRIMARY KEY(method),
    FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
    FOREIGN KEY(bill) REFERENCES payment(bill)
);

CREATE TABLE affects
(
    points INT NOT NULL,
    name varchar(255),
    amount_off INT,
    PRIMARY KEY(points),
    FOREIGN KEY(name) REFERENCES season(name),
    FOREIGN KEY(amount_off) REFERENCES discount(amount_off)
);

CREATE TABLE reduces
(
    amount_off INT,
    bill INT,
    FOREIGN KEY(amount_off) REFERENCES discount(amount_off),
    FOREIGN KEY(bill) REFERENCES payment(bill)
);