CREATE DATABASE hospital1;
USE hospital1;

CREATE TABLE patient(
ssn int(30) NOT NULL,
fname varchar(255) NOT NULL,
minit varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
phone_number int(30) NOT NULL,
address varchar(255) NOT NULL,
age int(30) NOT NULL,
gender varchar(255) NOT NULL,
PRIMARY KEY(ssn));


CREATE TABLE doctor(
ssn int(30) NOT NULL,
fname varchar(255) NOT NULL,
minit varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
phone_number int(30) NOT NULL,
address varchar(255) NOT NULL,
age int(30) NOT NULL,
gender varchar(255) NOT NULL,
years_of_exp int(30) NOT NULL,
PRIMARY KEY(ssn));

CREATE TABLE pharmacompany(
name varchar(255) NOT NULL,
phone_number int(30) NOT NULL,
PRIMARY KEY(name));


CREATE TABLE pharmacy(
name varchar(255) NOT NULL,
address varchar(255) NOT NULL,
phone_number int(30) NOT NULL,
PRIMARY KEY(name));


CREATE TABLE drug(
pharmacomp_name varchar(255) ,
trade_name varchar(255) ,
formula varchar(255) ,
PRIMARY KEY(trade_name,pharmacomp_name));


CREATE TABLE soldby(
drug_trade_name varchar(255) ,
pharmacy_name varchar(255) ,
company_name varchar(255) ,
price float(30) NOT NULL,
PRIMARY KEY(drug_trade_name,pharmacy_name,company_name));

CREATE TABLE visit(
patient_ssn int(30) NOT NULL,
doctor_ssn int(30) NOT NULL,
date_of_visit DATETIME ,
PRIMARY KEY(date_of_visit,patient_ssn,doctor_ssn));


CREATE TABLE illness(
patient_ssn int(30) ,
p_illness varchar(255) ,
doctor_ssn int(30) ,
date_of_visit DATETIME ,
PRIMARY KEY(date_of_visit,patient_ssn,doctor_ssn,p_illness));

CREATE TABLE prescribe(
patient_ssn int(30) ,
drug_trade_name varchar(255) ,
doctor_ssn int(30) ,
company_name varchar(255) ,
PRIMARY KEY(patient_ssn,drug_trade_name,doctor_ssn,company_name));


CREATE TABLE specializations(
doctor_ssn int(30) ,
dspecialization varchar(255) ,
PRIMARY KEY(doctor_ssn,dspecialization));

ALTER TABLE drug ADD FOREIGN KEY(pharmacomp_name) REFERENCES pharmacompany(name)
ON DELETE CASCADE;
ALTER TABLE soldby ADD FOREIGN KEY(pharmacy_name) REFERENCES pharmacy(name);
ALTER TABLE soldby ADD FOREIGN KEY(company_name) REFERENCES pharmacompany(name);
ALTER TABLE soldby ADD FOREIGN KEY (drug_trade_name) REFERENCES drug(trade_name);
ALTER TABLE visit ADD FOREIGN KEY(patient_ssn) REFERENCES patient (ssn);
ALTER TABLE visit ADD FOREIGN KEY(doctor_ssn) REFERENCES doctor(ssn);
ALTER TABLE illness ADD FOREIGN KEY(patient_ssn) REFERENCES patient(ssn);
ALTER TABLE illness ADD FOREIGN KEY(doctor_ssn) REFERENCES doctor(ssn);
ALTER TABLE illness ADD FOREIGN KEY(date_of_visit) REFERENCES visit(date_of_visit);
ALTER TABLE prescribe ADD FOREIGN KEY(patient_ssn) REFERENCES patient(ssn);
ALTER TABLE prescribe ADD FOREIGN KEY(doctor_ssn) REFERENCES doctor(ssn);
ALTER TABLE prescribe ADD FOREIGN KEY(company_name) REFERENCES pharmacompany(name);
ALTER TABLE prescribe ADD FOREIGN KEY(drug_trade_name) REFERENCES drug(trade_name);
ALTER TABLE specializations ADD FOREIGN KEY(doctor_ssn) REFERENCES doctor(ssn);