-
Notifications
You must be signed in to change notification settings - Fork 0
/
createtbl.sql
133 lines (118 loc) · 3.92 KB
/
createtbl.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
-- Include your create table DDL statements in this file.
-- Make sure to terminate each statement with a semicolon (;)
-- LEAVE this statement on. It is required to connect to your database.
CONNECT TO cs421;
-- Remember to put the create table ddls for the tables with foreign key references
-- ONLY AFTER the parent tables has already been created.
-- This is only an example of how you add create table ddls to this file.
-- You may remove it.
CREATE TABLE Category
(
categoryName VARCHAR(50) NOT NULL
,priority INT NOT NULL
,PRIMARY KEY (categoryName)
);
CREATE TABLE Person
(
healthInsuranceNo VARCHAR(12) NOT NULL
,personName VARCHAR(50) NOT NULL
,doBirth DATE NOT NULL CHECK (doBirth>='1900-01-01')
,gender VARCHAR(10) NOT NULL CHECK (gender IN ('Male', 'Female', 'Other'))
,phoneNo VARCHAR(10) NOT NULL
,streetAddress VARCHAR(100) NOT NULL
,postalCode VARCHAR(6) NOT NULL
,city VARCHAR(30) NOT NULL
,registrationDate DATE NOT NULL
,categoryName VARCHAR(50) NOT NULL
,PRIMARY KEY (healthInsuranceNo)
,FOREIGN KEY(categoryName) REFERENCES Category(categoryName)
);
CREATE TABLE VaccinationLocation
(
locationName VARCHAR(50) NOT NULL
,streetAddress VARCHAR(100) NOT NULL
,postalCode VARCHAR(6) NOT NULL
,city VARCHAR(30) NOT NULL
,PRIMARY KEY (locationName)
);
CREATE TABLE Hospital
(
locationName VARCHAR(50) NOT NULL
,PRIMARY KEY (locationName)
,FOREIGN KEY(locationName) REFERENCES VaccinationLocation(locationName)
);
CREATE TABLE Nurse
(
licenseNo VARCHAR(15) NOT NULL
,nurseName VARCHAR(50) NOT NULL
,locationName VARCHAR(50) NOT NULL
,PRIMARY KEY (licenseNo)
,FOREIGN KEY(locationName) REFERENCES Hospital(locationName)
);
CREATE TABLE Vaccine
(
vaccineName VARCHAR(50) NOT NULL
,totalDosesNeeded INT NOT NULL
,timeBetweenDoses VARCHAR(20) NOT NULL
,PRIMARY KEY (vaccineName)
,CHECK(totalDosesNeeded>=0)
);
CREATE TABLE Batch
(
vaccineName VARCHAR(50) NOT NULL
,batchNo VARCHAR(20) NOT NULL
,expiryDate DATE NOT NULL
,manufactureDate DATE NOT NULL
,count INT NOT NULL
,locationName VARCHAR(50) NOT NULL
,PRIMARY KEY (vaccineName, batchNo)
,FOREIGN KEY(vaccineName) REFERENCES Vaccine(vaccineName)
,FOREIGN KEY(locationName) REFERENCES VaccinationLocation(locationName)
,CHECK (count>=0)
--,CHECK (expiryDate>manufactureDate) this was asked in Q6 hence commented out
);
CREATE TABLE Vial
(
vaccineName VARCHAR(50) NOT NULL
,batchNo VARCHAR(20) NOT NULL
,vialNo INT NOT NULL
,PRIMARY KEY (vaccineName, batchNo, vialNo)
,FOREIGN KEY (vaccineName, batchNo) REFERENCES Batch(vaccineName, batchNo)
,CHECK (vialNo>=1)
);
CREATE TABLE AdministratedDate
(
slotDate DATE NOT NULL
,locationName VARCHAR(50) NOT NULL
,PRIMARY KEY (slotDate, locationName)
,FOREIGN KEY (locationName) REFERENCES VaccinationLocation(locationName)
);
CREATE TABLE Deputed
(
licenseNo VARCHAR(15) NOT NULL
,locationName VARCHAR(50) NOT NULL
,slotDate DATE NOT NULL
,PRIMARY KEY (licenseNo, slotDate, locationName)
,FOREIGN KEY (licenseNo) REFERENCES Nurse(licenseNo)
,FOREIGN KEY (slotDate, locationName) REFERENCES AdministratedDate(slotDate, locationName)
);
CREATE TABLE Slot
(
slotID VARCHAR(50) NOT NULL
,slotDate DATE NOT NULL
,slotTime TIME NOT NULL
,allocationDate DATE
,allocationTime TIME
,locationName VARCHAR(50) NOT NULL
,licenseNo VARCHAR(15)
,vaccineName VARCHAR(50)
,batchNo VARCHAR(20)
,vialNo INT
,healthInsuranceNo VARCHAR(12)
,PRIMARY KEY (slotID)
,FOREIGN KEY (slotDate, locationName) REFERENCES AdministratedDate(slotDate, locationName)
,FOREIGN KEY (licenseNo) REFERENCES Nurse(licenseNo)
,FOREIGN KEY (vaccineName, batchNo,vialNo) REFERENCES Vial(vaccineName, batchNo,vialNo)
,FOREIGN KEY (healthInsuranceNo) REFERENCES Person(healthInsuranceNo)
,CHECK (allocationDate<=slotDate)
);