
CREATE TABLE "Affiliated_With" (
	"Physician" INTEGER NOT NULL, 
	"Department" INTEGER NOT NULL, 
	"PrimaryAffiliation" BOOLEAN NOT NULL, 
	PRIMARY KEY ("Physician", "Department"), 
	CONSTRAINT "fk_Affiliated_With_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Affiliated_With_Department_DepartmentID" FOREIGN KEY("Department") REFERENCES "Department" ("DepartmentID")
)


CREATE TABLE "Appointment" (
	"AppointmentID" INTEGER NOT NULL, 
	"Patient" INTEGER NOT NULL, 
	"PrepNurse" INTEGER, 
	"Physician" INTEGER NOT NULL, 
	"Start" DATETIME NOT NULL, 
	"End" DATETIME NOT NULL, 
	"ExaminationRoom" TEXT NOT NULL, 
	PRIMARY KEY ("AppointmentID"), 
	CONSTRAINT "fk_Appointment_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Appointment_Nurse_EmployeeID" FOREIGN KEY("PrepNurse") REFERENCES "Nurse" ("EmployeeID"), 
	CONSTRAINT "fk_Appointment_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID")
)


CREATE TABLE "Block" (
	"BlockFloor" INTEGER NOT NULL, 
	"BlockCode" INTEGER NOT NULL, 
	PRIMARY KEY ("BlockFloor", "BlockCode")
)


CREATE TABLE "Department" (
	"DepartmentID" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Head" INTEGER NOT NULL, 
	CONSTRAINT "pk_Department" PRIMARY KEY ("DepartmentID"), 
	CONSTRAINT "fk_Department_Physician_EmployeeID" FOREIGN KEY("Head") REFERENCES "Physician" ("EmployeeID")
)


CREATE TABLE "Medication" (
	"Code" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Brand" VARCHAR(30) NOT NULL, 
	"Description" VARCHAR(30) NOT NULL, 
	PRIMARY KEY ("Code")
)


CREATE TABLE "Nurse" (
	"EmployeeID" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Position" VARCHAR(30) NOT NULL, 
	"Registered" BOOLEAN NOT NULL, 
	"SSN" INTEGER NOT NULL, 
	PRIMARY KEY ("EmployeeID")
)


CREATE TABLE "On_Call" (
	"Nurse" INTEGER NOT NULL, 
	"BlockFloor" INTEGER NOT NULL, 
	"BlockCode" INTEGER NOT NULL, 
	"OnCallStart" DATETIME NOT NULL, 
	"OnCallEnd" DATETIME NOT NULL, 
	PRIMARY KEY ("Nurse", "BlockFloor", "BlockCode", "OnCallStart", "OnCallEnd"), 
	CONSTRAINT "fk_OnCall_Nurse_EmployeeID" FOREIGN KEY("Nurse") REFERENCES "Nurse" ("EmployeeID"), 
	CONSTRAINT "fk_OnCall_Block_Floor" FOREIGN KEY("BlockFloor", "BlockCode") REFERENCES "Block" ("BlockFloor", "BlockCode")
)


CREATE TABLE "Patient" (
	"SSN" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Address" VARCHAR(30) NOT NULL, 
	"Phone" VARCHAR(30) NOT NULL, 
	"InsuranceID" INTEGER NOT NULL, 
	"PCP" INTEGER NOT NULL, 
	PRIMARY KEY ("SSN"), 
	CONSTRAINT "fk_Patient_Physician_EmployeeID" FOREIGN KEY("PCP") REFERENCES "Physician" ("EmployeeID")
)


CREATE TABLE "Physician" (
	"EmployeeID" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Position" VARCHAR(30) NOT NULL, 
	"SSN" INTEGER NOT NULL, 
	CONSTRAINT pk_physician PRIMARY KEY ("EmployeeID")
)


CREATE TABLE "Prescribes" (
	"Physician" INTEGER NOT NULL, 
	"Patient" INTEGER NOT NULL, 
	"Medication" INTEGER NOT NULL, 
	"Date" DATETIME NOT NULL, 
	"Appointment" INTEGER, 
	"Dose" VARCHAR(30) NOT NULL, 
	PRIMARY KEY ("Physician", "Patient", "Medication", "Date"), 
	CONSTRAINT "fk_Prescribes_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Prescribes_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Prescribes_Medication_Code" FOREIGN KEY("Medication") REFERENCES "Medication" ("Code"), 
	CONSTRAINT "fk_Prescribes_Appointment_AppointmentID" FOREIGN KEY("Appointment") REFERENCES "Appointment" ("AppointmentID")
)


CREATE TABLE "Procedures" (
	"Code" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Cost" REAL NOT NULL, 
	PRIMARY KEY ("Code")
)


CREATE TABLE "Room" (
	"RoomNumber" INTEGER NOT NULL, 
	"RoomType" VARCHAR(30) NOT NULL, 
	"BlockFloor" INTEGER NOT NULL, 
	"BlockCode" INTEGER NOT NULL, 
	"Unavailable" BOOLEAN NOT NULL, 
	PRIMARY KEY ("RoomNumber"), 
	CONSTRAINT "fk_Room_Block_PK" FOREIGN KEY("BlockFloor", "BlockCode") REFERENCES "Block" ("BlockFloor", "BlockCode")
)


CREATE TABLE "Stay" (
	"StayID" INTEGER NOT NULL, 
	"Patient" INTEGER NOT NULL, 
	"Room" INTEGER NOT NULL, 
	"StayStart" DATETIME NOT NULL, 
	"StayEnd" DATETIME NOT NULL, 
	PRIMARY KEY ("StayID"), 
	CONSTRAINT "fk_Stay_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Stay_Room_Number" FOREIGN KEY("Room") REFERENCES "Room" ("RoomNumber")
)


CREATE TABLE "Trained_In" (
	"Physician" INTEGER NOT NULL, 
	"Treatment" INTEGER NOT NULL, 
	"CertificationDate" DATETIME NOT NULL, 
	"CertificationExpires" DATETIME NOT NULL, 
	PRIMARY KEY ("Physician", "Treatment"), 
	CONSTRAINT "fk_Trained_In_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Trained_In_Procedures_Code" FOREIGN KEY("Treatment") REFERENCES "Procedures" ("Code")
)


CREATE TABLE "Undergoes" (
	"Patient" INTEGER NOT NULL, 
	"Procedures" INTEGER NOT NULL, 
	"Stay" INTEGER NOT NULL, 
	"DateUndergoes" DATETIME NOT NULL, 
	"Physician" INTEGER NOT NULL, 
	"AssistingNurse" INTEGER, 
	PRIMARY KEY ("Patient", "Procedures", "Stay", "DateUndergoes"), 
	CONSTRAINT "fk_Undergoes_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Undergoes_Procedures_Code" FOREIGN KEY("Procedures") REFERENCES "Procedures" ("Code"), 
	CONSTRAINT "fk_Undergoes_Stay_StayID" FOREIGN KEY("Stay") REFERENCES "Stay" ("StayID"), 
	CONSTRAINT "fk_Undergoes_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Undergoes_Nurse_EmployeeID" FOREIGN KEY("AssistingNurse") REFERENCES "Nurse" ("EmployeeID")
)

CREATE TABLE "Affiliated_With" (
	"Physician" INTEGER NOT NULL, 
	"Department" INTEGER NOT NULL, 
	"PrimaryAffiliation" BOOLEAN NOT NULL, 
	PRIMARY KEY ("Physician", "Department"), 
	CONSTRAINT "fk_Affiliated_With_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Affiliated_With_Department_DepartmentID" FOREIGN KEY("Department") REFERENCES "Department" ("DepartmentID")
)

/*
2 rows from Affiliated_With table:
Physician	Department	PrimaryAffiliation
1	1	True
2	1	True
*/


CREATE TABLE "Appointment" (
	"AppointmentID" INTEGER NOT NULL, 
	"Patient" INTEGER NOT NULL, 
	"PrepNurse" INTEGER, 
	"Physician" INTEGER NOT NULL, 
	"Start" DATETIME NOT NULL, 
	"End" DATETIME NOT NULL, 
	"ExaminationRoom" TEXT NOT NULL, 
	PRIMARY KEY ("AppointmentID"), 
	CONSTRAINT "fk_Appointment_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Appointment_Nurse_EmployeeID" FOREIGN KEY("PrepNurse") REFERENCES "Nurse" ("EmployeeID"), 
	CONSTRAINT "fk_Appointment_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID")
)

/*
2 rows from Appointment table:
AppointmentID	Patient	PrepNurse	Physician	Start	End	ExaminationRoom
13216584	100000001	101	1	2008-04-24 10:00:00	2008-04-24 11:00:00	A
26548913	100000002	101	2	2008-04-24 10:00:00	2008-04-24 11:00:00	B
*/


CREATE TABLE "Block" (
	"BlockFloor" INTEGER NOT NULL, 
	"BlockCode" INTEGER NOT NULL, 
	PRIMARY KEY ("BlockFloor", "BlockCode")
)

/*
2 rows from Block table:
BlockFloor	BlockCode
1	1
1	2
*/


CREATE TABLE "Department" (
	"DepartmentID" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Head" INTEGER NOT NULL, 
	CONSTRAINT "pk_Department" PRIMARY KEY ("DepartmentID"), 
	CONSTRAINT "fk_Department_Physician_EmployeeID" FOREIGN KEY("Head") REFERENCES "Physician" ("EmployeeID")
)

/*
2 rows from Department table:
DepartmentID	Name	Head
1	General Medicine	4
2	Surgery	7
*/


CREATE TABLE "Medication" (
	"Code" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Brand" VARCHAR(30) NOT NULL, 
	"Description" VARCHAR(30) NOT NULL, 
	PRIMARY KEY ("Code")
)

/*
2 rows from Medication table:
Code	Name	Brand	Description
1	Procrastin-X	X	N/A
2	Thesisin	Foo Labs	N/A
*/


CREATE TABLE "Nurse" (
	"EmployeeID" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Position" VARCHAR(30) NOT NULL, 
	"Registered" BOOLEAN NOT NULL, 
	"SSN" INTEGER NOT NULL, 
	PRIMARY KEY ("EmployeeID")
)

/*
2 rows from Nurse table:
EmployeeID	Name	Position	Registered	SSN
101	Carla Espinosa	Head Nurse	True	111111110
102	Laverne Roberts	Nurse	True	222222220
*/


CREATE TABLE "On_Call" (
	"Nurse" INTEGER NOT NULL, 
	"BlockFloor" INTEGER NOT NULL, 
	"BlockCode" INTEGER NOT NULL, 
	"OnCallStart" DATETIME NOT NULL, 
	"OnCallEnd" DATETIME NOT NULL, 
	PRIMARY KEY ("Nurse", "BlockFloor", "BlockCode", "OnCallStart", "OnCallEnd"), 
	CONSTRAINT "fk_OnCall_Nurse_EmployeeID" FOREIGN KEY("Nurse") REFERENCES "Nurse" ("EmployeeID"), 
	CONSTRAINT "fk_OnCall_Block_Floor" FOREIGN KEY("BlockFloor", "BlockCode") REFERENCES "Block" ("BlockFloor", "BlockCode")
)

/*
2 rows from On_Call table:
Nurse	BlockFloor	BlockCode	OnCallStart	OnCallEnd
101	1	1	2008-11-04 11:00:00	2008-11-04 19:00:00
101	1	2	2008-11-04 11:00:00	2008-11-04 19:00:00
*/


CREATE TABLE "Patient" (
	"SSN" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Address" VARCHAR(30) NOT NULL, 
	"Phone" VARCHAR(30) NOT NULL, 
	"InsuranceID" INTEGER NOT NULL, 
	"PCP" INTEGER NOT NULL, 
	PRIMARY KEY ("SSN"), 
	CONSTRAINT "fk_Patient_Physician_EmployeeID" FOREIGN KEY("PCP") REFERENCES "Physician" ("EmployeeID")
)

/*
2 rows from Patient table:
SSN	Name	Address	Phone	InsuranceID	PCP
100000001	John Smith	42 Foobar Lane	555-0256	68476213	1
100000002	Grace Ritchie	37 Snafu Drive	555-0512	36546321	2
*/


CREATE TABLE "Physician" (
	"EmployeeID" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Position" VARCHAR(30) NOT NULL, 
	"SSN" INTEGER NOT NULL, 
	CONSTRAINT pk_physician PRIMARY KEY ("EmployeeID")
)

/*
2 rows from Physician table:
EmployeeID	Name	Position	SSN
1	John Dorian	Staff Internist	111111111
2	Elliot Reid	Attending Physician	222222222
*/


CREATE TABLE "Prescribes" (
	"Physician" INTEGER NOT NULL, 
	"Patient" INTEGER NOT NULL, 
	"Medication" INTEGER NOT NULL, 
	"Date" DATETIME NOT NULL, 
	"Appointment" INTEGER, 
	"Dose" VARCHAR(30) NOT NULL, 
	PRIMARY KEY ("Physician", "Patient", "Medication", "Date"), 
	CONSTRAINT "fk_Prescribes_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Prescribes_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Prescribes_Medication_Code" FOREIGN KEY("Medication") REFERENCES "Medication" ("Code"), 
	CONSTRAINT "fk_Prescribes_Appointment_AppointmentID" FOREIGN KEY("Appointment") REFERENCES "Appointment" ("AppointmentID")
)

/*
2 rows from Prescribes table:
Physician	Patient	Medication	Date	Appointment	Dose
1	100000001	1	2008-04-24 10:47:00	13216584	5
9	100000004	2	2008-04-27 10:53:00	86213939	10
*/


CREATE TABLE "Procedures" (
	"Code" INTEGER NOT NULL, 
	"Name" VARCHAR(30) NOT NULL, 
	"Cost" REAL NOT NULL, 
	PRIMARY KEY ("Code")
)

/*
2 rows from Procedures table:
Code	Name	Cost
1	Reverse Rhinopodoplasty	1500.0
2	Obtuse Pyloric Recombobulation	3750.0
*/


CREATE TABLE "Room" (
	"RoomNumber" INTEGER NOT NULL, 
	"RoomType" VARCHAR(30) NOT NULL, 
	"BlockFloor" INTEGER NOT NULL, 
	"BlockCode" INTEGER NOT NULL, 
	"Unavailable" BOOLEAN NOT NULL, 
	PRIMARY KEY ("RoomNumber"), 
	CONSTRAINT "fk_Room_Block_PK" FOREIGN KEY("BlockFloor", "BlockCode") REFERENCES "Block" ("BlockFloor", "BlockCode")
)

/*
2 rows from Room table:
RoomNumber	RoomType	BlockFloor	BlockCode	Unavailable
101	Single	1	1	False
102	Single	1	1	False
*/


CREATE TABLE "Stay" (
	"StayID" INTEGER NOT NULL, 
	"Patient" INTEGER NOT NULL, 
	"Room" INTEGER NOT NULL, 
	"StayStart" DATETIME NOT NULL, 
	"StayEnd" DATETIME NOT NULL, 
	PRIMARY KEY ("StayID"), 
	CONSTRAINT "fk_Stay_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Stay_Room_Number" FOREIGN KEY("Room") REFERENCES "Room" ("RoomNumber")
)

/*
2 rows from Stay table:
StayID	Patient	Room	StayStart	StayEnd
3215	100000001	111	2008-05-01 00:00:00	2008-05-04 00:00:00
3216	100000003	123	2008-05-03 00:00:00	2008-05-14 00:00:00
*/


CREATE TABLE "Trained_In" (
	"Physician" INTEGER NOT NULL, 
	"Treatment" INTEGER NOT NULL, 
	"CertificationDate" DATETIME NOT NULL, 
	"CertificationExpires" DATETIME NOT NULL, 
	PRIMARY KEY ("Physician", "Treatment"), 
	CONSTRAINT "fk_Trained_In_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Trained_In_Procedures_Code" FOREIGN KEY("Treatment") REFERENCES "Procedures" ("Code")
)

/*
2 rows from Trained_In table:
Physician	Treatment	CertificationDate	CertificationExpires
3	1	2008-01-01 00:00:00	2008-12-31 00:00:00
3	2	2008-01-01 00:00:00	2008-12-31 00:00:00
*/


CREATE TABLE "Undergoes" (
	"Patient" INTEGER NOT NULL, 
	"Procedures" INTEGER NOT NULL, 
	"Stay" INTEGER NOT NULL, 
	"DateUndergoes" DATETIME NOT NULL, 
	"Physician" INTEGER NOT NULL, 
	"AssistingNurse" INTEGER, 
	PRIMARY KEY ("Patient", "Procedures", "Stay", "DateUndergoes"), 
	CONSTRAINT "fk_Undergoes_Patient_SSN" FOREIGN KEY("Patient") REFERENCES "Patient" ("SSN"), 
	CONSTRAINT "fk_Undergoes_Procedures_Code" FOREIGN KEY("Procedures") REFERENCES "Procedures" ("Code"), 
	CONSTRAINT "fk_Undergoes_Stay_StayID" FOREIGN KEY("Stay") REFERENCES "Stay" ("StayID"), 
	CONSTRAINT "fk_Undergoes_Physician_EmployeeID" FOREIGN KEY("Physician") REFERENCES "Physician" ("EmployeeID"), 
	CONSTRAINT "fk_Undergoes_Nurse_EmployeeID" FOREIGN KEY("AssistingNurse") REFERENCES "Nurse" ("EmployeeID")
)

/*
2 rows from Undergoes table:
Patient	Procedures	Stay	DateUndergoes	Physician	AssistingNurse
100000001	6	3215	2008-05-02 00:00:00	3	101
100000001	2	3215	2008-05-03 00:00:00	7	101
*/
How many rooms does each block floor have?
SELECT count(*) ,  T1.blockfloor FROM BLOCK AS T1 JOIN room AS T2 ON T1.blockfloor  =  T2.blockfloor AND T1.blockcode  =  T2.blockcode GROUP BY T1.blockfloor
[{'count(*)': 9, 'BlockFloor': 1}, {'count(*)': 9, 'BlockFloor': 2}, {'count(*)': 9, 'BlockFloor': 3}, {'count(*)': 9, 'BlockFloor': 4}]