DROP TABLE BRANCH;
DROP TABLE OWNER;
DROP TABLE PRESCRIPTION;
DROP TABLE ANIMALS;
DROP TABLE REGISTER;
DROP TABLE EMPLOY;
DROP TABLE STAFF;
DROP TABLE APPOINTMENT;
DROP TABLE DIAGNOSIS;


CREATE TABLE BRANCH(
BRANCH_ID CHAR(12),
BRANCH_ADDRESS_LINE_1 VARCHAR2(30) NOT NULL,
BRANCH_ADDRESS_LINE_2 VARCHAR2(30) NOT NULL,
BRANCH_ZIP VARCHAR2(20),
BRANCH_CITY VARCHAR2(20),
BRANCH_PHONE VARCHAR2(15),
CONSTRAINT BRANCH_PK PRIMARY KEY(BRANCH_ID)
);

CREATE TABLE OWNER(
OWNER_ID CHAR(12) PRIMARY KEY,
OWNER_FIRST_NAME VARCHAR2(30),
OWNER_LAST_NAME VARCHAR2(30),
OWNER_ADDRESS_LINE_1 varchar2(30),
OWNER_ADDRESS_LINE_2 varchar2(30),
OWNER_CITY varchar2(20),
OWNER_ZIP varchar2(20),
OWNER_PHONE CHAR(12),
ANIMAL_NAME VARCHAR2(30)
);

/* ALTER TABLE OWNER ADD CONSTRAINT ANIMAL_NAME_FK FOREIGN KEY (ANIMAL_NAME3) REFERENCES ANIMALS (ANIMAL_NAME); */

CREATE TABLE PRESCRIPTION(
PRESCRIPTION_ID CHAR(12) PRIMARY KEY,
PRESCRIPTION_NAME VARCHAR2(30),
PRESCRIPTION_COST CHAR(3),
OWNER_ID2 CHAR(12),
ANIMAL_ID2 CHAR(12),
ANIMAL_NAME VARCHAR2(30)
);

/* ALTER TABLE OWNER ADD CONSTRAINT OWNER_ID2_FK FOREIGN KEY (OWNER_ID2) REFERENCES OWNER (OWNER_ID);
ALTER TABLE OWNER ADD CONSTRAINT ANIMAL_ID2_FK FOREIGN KEY (ANIMAL_ID2) REFERENCES ANIMALS (ANIMAL_ID); */


CREATE TABLE ANIMALS(
ANIMAL_ID CHAR(5) PRIMARY KEY,
ANIMAL_NAME VARCHAR2(30),
OWNER_FIRST_NAME VARCHAR2(30),
OWNER_LAST_NAME VARCHAR2(30),
APPOINTMENT_ID CHAR(12)
);

/* ALTER TABLE OWNER ADD CONSTRAINT APPOINTMENT_ID_FK FOREIGN KEY (APPOINTMENT_ID) REFERENCES APPOINTMENT (APPOINTMENT_ID); */

CREATE TABLE REGISTER(
REG_ID CHAR(5) PRIMARY KEY,
BRANCH_ADDRESS_LINE_1 VARCHAR2(30) NOT NULL,
BRANCH_ADDRESS_LINE_2 VARCHAR2(30) NOT NULL,
OWNER_FIRST_NAME VARCHAR2(30),
OWNER_LAST_NAME VARCHAR2(30),
OWNER_PHONE CHAR(12),
OWNER_ID CHAR(12),
ANIMAL_ID CHAR(5),
ANIMAL_NAME VARCHAR2(30)
);

CREATE TABLE EMPLOY(
EM_ID CHAR(5) PRIMARY KEY,
EM_TITLE VARCHAR2(30),
EM_FIRST_NAME VARCHAR2(30),
EM_LAST_NAME VARCHAR2(30),
EM_ADDRESS_LINE_1 VARCHAR2(30),
EM_ADDRESS_LINE_2 VARCHAR2(30),
EM_CITY VARCHAR2(30),
EM_POSITION VARCHAR2(30),
EM_HOURLY_WAGE CHAR(30),
EM_PHONE VARCHAR2(30),
EM_EMAIL VARCHAR2(50),
EM_YEARS_EXP VARCHAR2(30),
EM_START_DATE DATE
);

CREATE TABLE STAFF(
STAFF_ID CHAR(5) PRIMARY KEY,
EM_ID CHAR(5),
EM_FIRST_NAME VARCHAR(30),
EM_LAST_NAME VARCHAR(30),
EM_POSITION VARCHAR(30),
EM_PHONE VARCHAR(30),
EM_EMAIL VARCHAR(50),
EM_YEARS_EXP VARCHAR(30)
);

CREATE TABLE APPOINTMENT(
APPOINTMENT_ID CHAR(12) PRIMARY KEY,
APPOINTMENT_DATE DATE, -- YYYY-MM-DD
APPOINTMENT_ATTENDING_SURGEON VARCHAR2(25),
OWNER_FIRST_NAME VARCHAR2(30),
OWNER_LAST_NAME VARCHAR2(30),
BRANCH_ADDRESS_LINE_1 VARCHAR2(30) NOT NULL,
BRANCH_ADDRESS_LINE_2 VARCHAR2(30) NOT NULL
);

CREATE TABLE DIAGNOSIS(
DIAGNOSIS_ID CHAR(5) PRIMARY KEY,
ANIMAL_DIAGNOSIS VARCHAR(30),
ANIMAL_ID CHAR(5),
ANIMAL_NAME VARCHAR2(30),
APPOINTMENT_ATTENDING_SURGEON VARCHAR2(25)
);

INSERT INTO BRANCH (BRANCH_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, BRANCH_ZIP, BRANCH_CITY, BRANCH_PHONE)
VALUES (1, '21_Green_Street', 'Park Avenue', 'GA51_3LT', 'London', 02075920226);
INSERT INTO BRANCH (BRANCH_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, BRANCH_ZIP, BRANCH_CITY, BRANCH_PHONE)
VALUES (2, '13_Purple_Street', 'Blue Avenue', 'GA52_3DT', 'London', 02074210226);
INSERT INTO BRANCH (BRANCH_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, BRANCH_ZIP, BRANCH_CITY, BRANCH_PHONE)
VALUES (3, '2_Red_Street', 'Red Avenue', 'GA21_3AT', 'London', 02032120226);
INSERT INTO BRANCH (BRANCH_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, BRANCH_ZIP, BRANCH_CITY, BRANCH_PHONE)
VALUES (4, '6_Orange_Street', 'Yellow Road', 'GA11_3LH', 'London', 02076548226);
INSERT INTO BRANCH (BRANCH_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, BRANCH_ZIP, BRANCH_CITY, BRANCH_PHONE)
VALUES (5, '11_Blue_Street', 'Red Road', 'SA50_6RE', 'London', 02075925482);

INSERT INTO OWNER (OWNER_ID, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_ADDRESS_LINE_1, OWNER_ADDRESS_LINE_2, OWNER_CITY, OWNER_ZIP, OWNER_PHONE, ANIMAL_NAME)
VALUES (1, 'Rob', 'Jones', '11 Holt Road', 'Green Lane', 'London', 'GA42_5FS', 02075920241, 'Whiskers');
INSERT INTO OWNER (OWNER_ID, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_ADDRESS_LINE_1, OWNER_ADDRESS_LINE_2, OWNER_CITY, OWNER_ZIP, OWNER_PHONE, ANIMAL_NAME)
VALUES (2, 'Jack', 'Smith', '14 Holt Road', 'Green Lane', 'London', 'GA43_5FS', 02075920243, 'Tom');
INSERT INTO OWNER (OWNER_ID, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_ADDRESS_LINE_1, OWNER_ADDRESS_LINE_2, OWNER_CITY, OWNER_ZIP, OWNER_PHONE, ANIMAL_NAME)
VALUES (3, 'Tom', 'Potts', '17 Holt Road', 'Green Lane', 'London', 'GA44_5FS', 02075920244, 'Jerry');
INSERT INTO OWNER (OWNER_ID, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_ADDRESS_LINE_1, OWNER_ADDRESS_LINE_2, OWNER_CITY, OWNER_ZIP, OWNER_PHONE, ANIMAL_NAME)
VALUES (4, 'Lewis', 'Jones', '22 Holt Road', 'Green Lane', 'London', 'GA45_5FS', 02075920242, 'Daisy');
INSERT INTO OWNER (OWNER_ID, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_ADDRESS_LINE_1, OWNER_ADDRESS_LINE_2, OWNER_CITY, OWNER_ZIP, OWNER_PHONE, ANIMAL_NAME)
VALUES (5, 'Megan', 'Jones', '20 Long Road', 'Red Lane', 'London', 'GB15_1FD', 02075330341, 'Misty');

INSERT INTO PRESCRIPTION (PRESCRIPTION_ID, PRESCRIPTION_NAME, PRESCRIPTION_COST, OWNER_ID2, ANIMAL_ID2, ANIMAL_NAME)
VALUES (1, 'Amoxicillin', 5, 222, 555, 'Whiskers');
INSERT INTO PRESCRIPTION (PRESCRIPTION_ID, PRESCRIPTION_NAME, PRESCRIPTION_COST, OWNER_ID2, ANIMAL_ID2, ANIMAL_NAME)
VALUES (2, 'Gentamicin', 5, 223, 556, 'Tom');
INSERT INTO PRESCRIPTION (PRESCRIPTION_ID, PRESCRIPTION_NAME, PRESCRIPTION_COST, OWNER_ID2, ANIMAL_ID2, ANIMAL_NAME)
VALUES (3, 'Doxycycline', 5, 224, 557, 'Jerry');
INSERT INTO PRESCRIPTION (PRESCRIPTION_ID, PRESCRIPTION_NAME, PRESCRIPTION_COST, OWNER_ID2, ANIMAL_ID2, ANIMAL_NAME)
VALUES (4, 'Chloramphenicol', 5, 225, 558, 'Poppies');
INSERT INTO PRESCRIPTION (PRESCRIPTION_ID, PRESCRIPTION_NAME, PRESCRIPTION_COST, OWNER_ID2, ANIMAL_ID2, ANIMAL_NAME)
VALUES (5, 'Sulfamethoxole', 5, 226, 559, 'Misty');

INSERT INTO ANIMALS (ANIMAL_ID, ANIMAL_NAME, OWNER_FIRST_NAME, OWNER_LAST_NAME, APPOINTMENT_ID)
VALUES (1, 'Tubby', 'Jason', 'Bourne', 222);
INSERT INTO ANIMALS (ANIMAL_ID, ANIMAL_NAME, OWNER_FIRST_NAME, OWNER_LAST_NAME, APPOINTMENT_ID)
VALUES (2, 'Jerry', 'Abigail', 'Jones', 223);
INSERT INTO ANIMALS (ANIMAL_ID, ANIMAL_NAME, OWNER_FIRST_NAME, OWNER_LAST_NAME, APPOINTMENT_ID)
VALUES (3, 'Daisy', 'Daisy', 'Jones', 224);
INSERT INTO ANIMALS (ANIMAL_ID, ANIMAL_NAME, OWNER_FIRST_NAME, OWNER_LAST_NAME, APPOINTMENT_ID)
VALUES (4, 'Misty', 'Abby', 'Jenkins', 225);
INSERT INTO ANIMALS (ANIMAL_ID, ANIMAL_NAME, OWNER_FIRST_NAME, OWNER_LAST_NAME, APPOINTMENT_ID)
VALUES (5, 'Poppies', 'Louise', 'Jones', 226);

INSERT INTO REGISTER (REG_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_PHONE, OWNER_ID, ANIMAL_ID, ANIMAL_NAME)
VALUES (111, 'Green Park Avenue', 'Yellow Road', 'Jamie', 'Owens', '01978432119', 222, 333, 'Poppies');
INSERT INTO REGISTER (REG_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_PHONE, OWNER_ID, ANIMAL_ID, ANIMAL_NAME)
VALUES (112, 'Red Park Avenue', 'Orange Road', 'Lauren', 'Jones', '01972232119', 223, 334, 'Misty');
INSERT INTO REGISTER (REG_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_PHONE, OWNER_ID, ANIMAL_ID, ANIMAL_NAME)
VALUES (113, 'Green Park Avenue', 'Red Road', 'Amie', 'Smith', '01978242119', 224, 335, 'Jerry');
INSERT INTO REGISTER (REG_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_PHONE, OWNER_ID, ANIMAL_ID, ANIMAL_NAME)
VALUES (114, 'Red Park Avenue', 'Orange Road', 'Thomas', 'Smith', '01978454619', 225, 336, 'Tom');
INSERT INTO REGISTER (REG_ID, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2, OWNER_FIRST_NAME, OWNER_LAST_NAME, OWNER_PHONE, OWNER_ID, ANIMAL_ID, ANIMAL_NAME)
VALUES (115, 'Green Park Avenue', 'Yellow Road', 'Jason', 'Bourne', '01946832119', 226, 337, 'Daisy');

INSERT INTO EMPLOY  (EM_ID, EM_TITLE, EM_FIRST_NAME, EM_LAST_NAME, EM_ADDRESS_LINE_1, EM_ADDRESS_LINE_2, EM_CITY, EM_POSITION, EM_HOURLY_WAGE, EM_PHONE, EM_EMAIL, EM_YEARS_EXP, EM_START_DATE)
VALUES (1, 'MR', 'Jason', 'Hughes', '1 James Street', 'Grace Park Avenue', 'London', 'Surgeon', 30, '02079847362', 'jasonhughes99@gmail.com', 5, DATE '2014-03-24');
INSERT INTO EMPLOY  (EM_ID, EM_TITLE, EM_FIRST_NAME, EM_LAST_NAME, EM_ADDRESS_LINE_1, EM_ADDRESS_LINE_2, EM_CITY, EM_POSITION, EM_HOURLY_WAGE, EM_PHONE, EM_EMAIL, EM_YEARS_EXP, EM_START_DATE)
VALUES (2, 'MRS', 'Jessica', 'Jones', '2 Olive Street', 'South Park Avenue', 'London', 'Surgeon', 30, '02079873862', 'jessjones99@gmail.com', 3, DATE '2012-07-18');
INSERT INTO EMPLOY  (EM_ID, EM_TITLE, EM_FIRST_NAME, EM_LAST_NAME, EM_ADDRESS_LINE_1, EM_ADDRESS_LINE_2, EM_CITY, EM_POSITION, EM_HOURLY_WAGE, EM_PHONE, EM_EMAIL, EM_YEARS_EXP, EM_START_DATE)
VALUES (3, 'MRS', 'Sian', 'Hughes', '22 Orange Street', 'Green Park Avenue', 'London', 'Surgeon', 30, '02079847343', 'sianhughes99@gmail.com', 4, DATE '1994-04-10');
INSERT INTO EMPLOY  (EM_ID, EM_TITLE, EM_FIRST_NAME, EM_LAST_NAME, EM_ADDRESS_LINE_1, EM_ADDRESS_LINE_2, EM_CITY, EM_POSITION, EM_HOURLY_WAGE, EM_PHONE, EM_EMAIL, EM_YEARS_EXP, EM_START_DATE)
VALUES (4, 'MRS', 'Faith', 'Jones', '28 Green Street', 'Blue Park Avenue', 'London', 'Surgeon', 30, '02079238362', 'faithjones99@gmail.com', 8, DATE '2002-03-24');
INSERT INTO EMPLOY  (EM_ID, EM_TITLE, EM_FIRST_NAME, EM_LAST_NAME, EM_ADDRESS_LINE_1, EM_ADDRESS_LINE_2, EM_CITY, EM_POSITION, EM_HOURLY_WAGE, EM_PHONE, EM_EMAIL, EM_YEARS_EXP, EM_START_DATE)
VALUES (5, 'MR', 'Jack', 'Tomlinson', '4 Yellow Street', 'Yellow Park Avenue', 'London', 'Surgeon', 30, '02079842361', 'jacktomlinson99@gmail.com', 8, DATE '1998-03-24');

INSERT INTO STAFF (STAFF_ID, EM_ID, EM_FIRST_NAME, EM_LAST_NAME, EM_POSITION, EM_PHONE, EM_EMAIL, EM_YEARS_EXP)
VALUES (888, 333, 'Jason', 'Hughes', 'Surgeon', '02079847362', 'jasonhughes99@gmail.com', 5);
INSERT INTO STAFF (STAFF_ID, EM_ID, EM_FIRST_NAME, EM_LAST_NAME, EM_POSITION, EM_PHONE, EM_EMAIL, EM_YEARS_EXP)
VALUES (889, 334, 'Jessica', 'Jones', 'Surgeon', '02079873862', 'jasonhughes99@gmail.com', 3);
INSERT INTO STAFF (STAFF_ID, EM_ID, EM_FIRST_NAME, EM_LAST_NAME, EM_POSITION, EM_PHONE, EM_EMAIL, EM_YEARS_EXP)
VALUES (890, 335, 'Sian', 'Hughes', 'Surgeon', '02079847343', 'jasonhughes99@gmail.com', 4);
INSERT INTO STAFF (STAFF_ID, EM_ID, EM_FIRST_NAME, EM_LAST_NAME, EM_POSITION, EM_PHONE, EM_EMAIL, EM_YEARS_EXP)
VALUES (891, 336, 'Faith', 'Jones', 'Surgeon', '02079238362', 'jasonhughes99@gmail.com', 8);
INSERT INTO STAFF (STAFF_ID, EM_ID, EM_FIRST_NAME, EM_LAST_NAME, EM_POSITION, EM_PHONE, EM_EMAIL, EM_YEARS_EXP)
VALUES (892, 337, 'Jack', 'Tomlinson', 'Surgeon', '02079842361', 'jasonhughes99@gmail.com', 8);

INSERT INTO APPOINTMENT (APPOINTMENT_ID, APPOINTMENT_DATE, APPOINTMENT_ATTENDING_SURGEON, OWNER_FIRST_NAME, OWNER_LAST_NAME, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2)
VALUES (1, DATE '2019-11-10', 'Jason Hughes', 'Jake', 'Hughes', 'Blue Park Avenue', 'Blue Road');
INSERT INTO APPOINTMENT (APPOINTMENT_ID, APPOINTMENT_DATE, APPOINTMENT_ATTENDING_SURGEON, OWNER_FIRST_NAME, OWNER_LAST_NAME, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2)
VALUES (2, DATE '2019-11-10', 'Jessica Jones', 'Steph', 'Jones', 'Blue Park Avenue', 'Blue Road');
INSERT INTO APPOINTMENT (APPOINTMENT_ID, APPOINTMENT_DATE, APPOINTMENT_ATTENDING_SURGEON, OWNER_FIRST_NAME, OWNER_LAST_NAME, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2)
VALUES (3, DATE '2019-11-11', 'Sian Hughes', 'Amie', 'Gabriel', 'Blue Park Avenue', 'Blue Road');
INSERT INTO APPOINTMENT (APPOINTMENT_ID, APPOINTMENT_DATE, APPOINTMENT_ATTENDING_SURGEON, OWNER_FIRST_NAME, OWNER_LAST_NAME, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2)
VALUES (4, DATE '2019-11-12', 'Jason Hughes', 'John', 'Smith', 'Blue Park Avenue', 'Blue Road');
INSERT INTO APPOINTMENT (APPOINTMENT_ID, APPOINTMENT_DATE, APPOINTMENT_ATTENDING_SURGEON, OWNER_FIRST_NAME, OWNER_LAST_NAME, BRANCH_ADDRESS_LINE_1, BRANCH_ADDRESS_LINE_2)
VALUES (5, DATE '2019-11-15', 'Jack Tomlinson', 'Jason', 'Bourne', 'Blue Park Avenue', 'Blue Road');

INSERT INTO DIAGNOSIS (DIAGNOSIS_ID, ANIMAL_DIAGNOSIS, ANIMAL_ID, ANIMAL_NAME, APPOINTMENT_ATTENDING_SURGEON)
VALUES (1, 'Skin allergies', 222, 'Poppies', 'Jason Hughes');
INSERT INTO DIAGNOSIS (DIAGNOSIS_ID, ANIMAL_DIAGNOSIS, ANIMAL_ID, ANIMAL_NAME, APPOINTMENT_ATTENDING_SURGEON)
VALUES (2, 'Diabeties', 222, 'Poppies', 'Jessica Jones');
INSERT INTO DIAGNOSIS (DIAGNOSIS_ID, ANIMAL_DIAGNOSIS, ANIMAL_ID, ANIMAL_NAME, APPOINTMENT_ATTENDING_SURGEON)
VALUES (3, 'Vomiting', 222, 'Poppies', 'Sian Hughes');
INSERT INTO DIAGNOSIS (DIAGNOSIS_ID, ANIMAL_DIAGNOSIS, ANIMAL_ID, ANIMAL_NAME, APPOINTMENT_ATTENDING_SURGEON)
VALUES (4, 'Inflammatory bowel', 222, 'Poppies', 'Jason Hughes');
INSERT INTO DIAGNOSIS (DIAGNOSIS_ID, ANIMAL_DIAGNOSIS, ANIMAL_ID, ANIMAL_NAME, APPOINTMENT_ATTENDING_SURGEON)
VALUES (5, 'Ear infection', 222, 'Poppies', 'Jack Tomlinson');