create table doctor2medicine2patient(
patient ssn int,
doctor ssn int,
tade name varchar(16),
quality varchar(16),
datee date,
primary key(patient ssn,doctor ssn,tade name),
foregin key patient ssn referances patient(ssn),
foregin key tade name referances medicine(tade name),
foregin key doctor ssn referances doctors(ssn));



create table department(
dep name varchar(32),
depno int,
mgr ssn int,
mgr ssn int,
foregin key depno,
primary key depno,
foregin key mgr ssn referances doctors(ssn),
foregin key mgr ssn referances nurse(ssn));



create table room(
room reservation date,
room name varchar(32),
roomno int,
room cost int,
mgr depno int,
primary key roomno,
foregin key mgr depno referances department(depno));



create table pharmacy(
roomno int,
name varchar(32),
primary key roomno);



create table doctors(
ssn int,
name varchar(32),
speciality varchar(32),
mgr ssn int,
mgr depno int,
primary key ssn,
foregin key mgr depno referances department(depno),
foregin key mgr ssn referances nurse(ssn));



create table patient(
ssn int,
age int,
name varchar(64),
address int,
mgr roomno int,
mgr nurse ssn int,
primary key ssn,
foregin key mgr nurse ssn referances department(ssn));



create table supplier(
mgr gha.roomnoint,
supplies types varchar(32),
name varchar(32),
address int,
primary key ssn,
foregin key mgr pha.roomno  referances department(roomno));


create table nurse(
mgr ssn int,
mgr depno,
name varchar(32),
shift time ,
ssn int,
primary key ssn,
foregin key mgr ssn referances doctors(ssn),
foregin key  mgr depno referances department(depno));


create table patient2lab (
lab roomno int,
ssn int,
primary key(lab roomno,ssn),
foregin key lab roomno referances laboratory(lab roomno),
foregin key ssn referances patient (ssn));


create table medicine(
supplier name varchar(64),
trade name varchar(64),
formula varchar(32),
mgrsuppliername varchar(64),
primary key tradename,
foregin key  mgrsuppliername referances department(name));


create table laboratory(
lab name varchar(64),
speciality varchar(64),
lab roomno int,
primary key labroomno);