JunrWCyA

1-- Provide the corresponding MySQL “CREATE TABLE” statements describing the relational schema.
2-- You must enforce inter- and intra- relational constraints1.
3-- Please include all your statements in anexecutable script (MOH.ddl) that can be run on the
4-- CSC MySQL server. Scripts that do not executeon the server will not be marked.
5
6CREATE TABLE IF NOT EXISTS Person(
7	uid INT PRIMARY KEY,
8	FirstName VARCHAR(100) NOT NULL,
9	LastName VARCHAR(100) NOT NULL,
10	Gender VARCHAR(100) NOT NULL,
11	BirthDate DATE NOT NULL
12);
13
14CREATE TABLE IF NOT EXISTS Address(
15	uid INT PRIMARY KEY,
16	Street VARCHAR(100) NOT NULL,
17	City VARCHAR(100) NOT NULL,
18	Province VARCHAR(100) NOT NULL,
19	PostalCode VARCHAR(100) NOT NULL,
20	CONSTRAINT PERSONADDRESS_FK FOREIGN KEY (uid) REFERENCES Person(uid)
21);
22
23-- CREATE TABLE IF NOT EXISTS Telephone(
24-- 	uid INT,
25-- 	PhoneNumber VARCHAR(100) NOT NULL,
26-- 	ContactType VARCHAR(100) NOT NULL,
27-- 	CONSTRAINT PERSONTELEPHONE_FK FOREIGN KEY (uid) REFERENCES Person(uid),
28--     PRIMARY KEY(uid, PhoneNumber)
29-- );
30
31CREATE TABLE IF NOT EXISTS Telephone(
32	PhoneNumber VARCHAR(100) PRIMARY KEY,
33	--ContactType VARCHAR(100) NOT NULL,
34	ContactType ENUM('Home', 'Work', 'Mobile')
35);
36
37CREATE TABLE IF NOT EXISTS Patient(
38	uid INT PRIMARY KEY,
39	--HealthInsurance VARCHAR(100) NOT NULL,
40	HealthInsurance ENUM('Public', 'Private', 'Self-funded'),
41	CONSTRAINT PERSONPATIENT_FK FOREIGN KEY (uid) REFERENCES Person(uid)
42);
43
44CREATE TABLE IF NOT EXISTS Physician(
45	uid INT PRIMARY KEY,
46	YearsOfPractice INT NOT NULL,
47	Salary INT NOT NULL,
48	Speciality VARCHAR(100) NOT NULL,
49	CONSTRAINT PERSONPHYSICIAN_FK FOREIGN KEY (uid) REFERENCES Person(uid)
50);
51
52CREATE TABLE IF NOT EXISTS Nurse(
53	uid INT PRIMARY KEY,
54	YearsOfPractice INT NOT NULL,
55	Salary INT NOT NULL,
56	CONSTRAINT PERSONNURSE_FK FOREIGN KEY (uid) REFERENCES Person(uid)
57);
58
59CREATE TABLE IF NOT EXISTS Hospital(
60	Name VARCHAR(100) PRIMARY KEY,
61	StreetAddress VARCHAR(100) NOT NULL,
62	City VARCHAR(100) NOT NULL,
63	AnnualBudget INT NOT NULL
64);
65
66CREATE TABLE IF NOT EXISTS MedicalDepartment(
67	Name VARCHAR(100),
68	HospitalName VARCHAR(100),
69	AnnualBudget INT NOT NULL,
70	CONSTRAINT HOSPITALMEDICALDEPARTMENT_FK FOREIGN KEY (HospitalName) REFERENCES Hospital(Name),
71	PRIMARY KEY(Name, HospitalName)
72);
73
74CREATE TABLE IF NOT EXISTS Admission(
75	uid INT,
76	HospitalName VARCHAR(100),
77	BirthDate DATE NOT NULL,
78	--Priority VARCHAR(100) NOT NULL,
79	Priority ENUM('Immediate', 'Urgent', 'Standard', 'Non-urgent'),
80	CONSTRAINT PATIENTADMISSION_FK FOREIGN KEY (uid) REFERENCES Patient(uid),
81	CONSTRAINT MEDICALDEPARTMENTADMISSION_FK FOREIGN KEY (HospitalName) REFERENCES Hospital(Name),
82	PRIMARY KEY(uid, HospitalName)
83);
84
85CREATE TABLE IF NOT EXISTS MedicalTest(
86	unid INT PRIMARY KEY,
87	TestName VARCHAR(100) NOT NULL,
88	Fee INT NOT NULL
89);
90
91CREATE TABLE IF NOT EXISTS UndergoesTest(
92	uid INT,
93	unid INT,
94	TestDate DATE NOT NULL,
95	Results VARCHAR(100) NOT NULL,
96	CONSTRAINT PATIENTUNDERGOTEST_FK FOREIGN KEY (uid) REFERENCES Patient(uid),
97	CONSTRAINT MEDICALTESTUNDERGOTEST_FK FOREIGN KEY (unid) REFERENCES MedicalTest(unid),
98	PRIMARY KEY(uid, unid)
99);
100
101CREATE TABLE IF NOT EXISTS Diagnose(
102	paid INT,
103	phid INT,
104	TestDate DATE NOT NULL,
105	Disease VARCHAR(100) NOT NULL,
106	--Prognosis VARCHAR(100) NOT NULL,
107	Prognosis ENUM('Excellent', 'Good', 'Fair', 'Poor', 'Verypoor'),
108	CONSTRAINT PATIENTDIAGNOSE_FK FOREIGN KEY (paid) REFERENCES Patient(uid),
109	CONSTRAINT PHYSICIANDIAGNOSE_FK FOREIGN KEY (phid) REFERENCES Physician(unid),
110	PRIMARY KEY(paid, phid)
111);
112
113CREATE TABLE IF NOT EXISTS Drug(
114	code SMALLINT PRIMARY KEY,
115	GenericName VARCHAR(100) NOT NULL,
116	Category VARCHAR(100) NOT NULL,
117	Cost INT NOT NULL
118);
119
120CREATE TABLE IF NOT EXISTS Prescribe(
121	paid INT,
122	phid INT,
123	code SMALLINT,
124	Drug VARCHAR(100) NOT NULL,
125	Dosage VARCHAR(100) NOT NULL,
126	CONSTRAINT PATIENTPRESCRIBE_FK FOREIGN KEY (paid) REFERENCES Patient(uid),
127	CONSTRAINT PHYSICIANPRESCRIBE_FK FOREIGN KEY (phid) REFERENCES Physician(unid),
128	CONSTRAINT DRUGPRESCRIBE_FK FOREIGN KEY (code) REFERENCES Drug(code),
129	PRIMARY KEY(paid, phid, code)
130);