-DROP TABLES
DROP TABLE reservation
DROP TABLE customer,
DROP TABLE waiter,
DROP TABLE food_order,
DROP TABLE food,

--CREATE TABLE

CREATE TABLE customer(

customer_id NUMBER (4), PK
title varchar2(4),
customer_fname varchar2 (20),
customer_lname varchar2 (20),
contact_number NUMBER (11),
email varchar2 (20),
Gender varchar2(1),
cus_date DATE)


CREATE TABLE reservation(

res_id NUMBER (4), PK
customer_id NUMBER(4), FK
customer_lname varchar2 (20), FK
res_data DATE,
party_num NUMBER (4),
waiter_lname varchar2 (20))


CREATE TABLE waitor(
wait_id NUMBER (4), PK
wait_fname varchar2 (20),
wait_lname varchar2 (20),
wait_DOB DATE,


CREATE TABLE food_order(
order_id NUMBER (4), PK
food_name varchar2 (20), FK
wait_id NUMBER (4), FK
wait_lname varchar2 (20), FK
customer_lname varchar2 (20))

CREATE TABLE food(
food_id NUMBER (4), PK
food_name varchar2 (20),
food_type varchar2 (20),
food_serving varchar2 (20),

--ADDING SEQUENCE

CREATE SEQUENCE id_seq
(START WITH 1,
INCREMENT BY 1);


--ADDING PRIMARY KEYS

ALTER TABLE CUSTOMER
ADD CONSTRAINT pk_customer_id
PRIMARY KEY (customer_id);

ALTER TABLE reservation
ADD CONSTRAINT pk_res_id
PRIMARY KEY (res_id);

ALTER TABLE waitor
ADD CONSTRAINT pk_wait_id
PRIMARY KEY (wait_id);


ALTER TABLE food_order
ADD CONSTRAINT pk_order_id
PRIMARY KEY (order_id);


ALTER TABLE food
ADD CONSTRAINT pk_food_id
PRIMARY KEY (food_id);


--ADDING FOREIGN KEYS


ALTER TABLE RESERVATION
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id);

ALTER TABLE RESERVATION
ADD CONSTRAINT fk_customer_lname
FOREIGN KEY (customer_lname)
REFERENCE customer(customer_lname);


ALTER TABLE food_order
ADD CONSTRAINT fk_food_name
FOREIGN KEY (food_name)
REFERENCE food(food_name);


ALTER TABLE food_order
ADD CONSTRAINT fk_wait_id
FOREIGN KEY (wait_id)
REFERENCE waitor(wait_id);


ALTER TABLE food_order
ADD CONSTRAINT fk_wait_lname
FOREIGN KEY (wait_lname)
REFERENCE waitor(wait_lname);


--inserting values

INSERT INTO customer

(customer_id, customer_fname, customer_lname, contact_number, email, gender, cus_date)
VALUES
("id_seq", "Alan", "Apple", "07712597234",  "aaple@gmail.com",  "M",  "13/07/2015")


INSERT INTO customer

(customer_id, customer_fname, customer_lname, contact_number, email, gender, cus_date)
VALUES
("id_seq", "brad", "bannana", "07712598534",  "bbannana@gmail.com",  "M",  "11/07/2015")


INSERT INTO customer

(customer_id, customer_fname, customer_lname, contact_number, email, gender, cus_date)
VALUES
("id_seq", "Alan", "Apple", "07712597234",  "aaple@gmail.com",  "M",  "13/07/2015")


INSERT INTO customer

(customer_id, customer_fname, customer_lname, contact_number, email, gender, cus_date)
VALUES
("id_seq", "Dan", "Dashing", "07247857234",  "Ddashing@gmail.com",  "M",  "17/07/2011")

CREATE TABLE reservation(

res_id NUMBER (4), PK
customer_id NUMBER(4), FK
customer_lname varchar2 (20), FK
res_data DATE,
party_num NUMBER (4),
waiter_lname varchar2 (20))

INSERT INTO reservation

(res_id, customer_id, customer_lname, contact_number, email, gender, cus_date)
VALUES
("id_seq", "Dan", "Dashing", "07247857234",  "Ddashing@gmail.com",  "M",  "17/07/2011")