· 10 years ago · Sep 17, 2016, 05:52 AM
1----------------------------------------------------------------
2
3--
4
5-- Please enter your UID here:
6
7--
8
9----------------------------------------------------------------
10
11-- The following SQL statements were written by the IT Service for creating the database.
12
13-- You're not allowed to make any changes or add any SQL statements in the following section.
14
15----------------------------------------------------------------
16
17DROP TABLE IF EXISTS ActivePermit;
18
19DROP TABLE IF EXISTS PUser;
20
21DROP TABLE IF EXISTS ParkingPermit;
22
23
24
25-- User = {UserID, Name, PhoneNumber}
26
27CREATE TABLE PUser (
28
29 UserID int PRIMARY KEY,
30
31 Name varchar(255),
32
33 Email varchar(255)
34
35);
36
37
38
39-- ParkingPermit = {PermitTypeID, Location, Fee, Duration, Description}
40
41CREATE TABLE ParkingPermit (
42
43 PermitTypeID int PRIMARY KEY,
44
45 Location varchar(255),
46
47 Fee int,
48
49 Duration interval,
50
51 Description text
52
53);
54
55
56
57-- ActivePermit = {PermitTypeID, UserID, IssueTime, VehicleRegNo}
58
59CREATE TABLE ActivePermit (
60
61 PermitTypeID int,
62
63 UserID int,
64
65 IssueTime timestamp,
66
67 VehicleRegNo varchar(7),
68
69 PRIMARY KEY(PermitTypeID, UserID)
70
71);
72
73
74
75----------------------------------------------------------------
76
77--Please enter your answers to Q1.1-Q1.6 of Question 1 in the following section
78
79----------------------------------------------------------------
80
81
82
83-- Your answer to Q1.1]
84
85
86
87CREATE TABLE ParkingPermit (
88
89 PermitTypeID int PRIMARY KEY,
90
91 Location varchar(255),
92
93 Fee numeric(7,2) CHECK (Fee BETWEEN 0 AND 10000),
94
95 Duration interval,
96
97 Description text
98
99);
100
101
102
103Fee numeric(7,2) CHECK (Fee BETWEEN 0 AND 10000),
104
105
106
107
108
109-- Your answer to Q1.2
110
111
112
113
114
115CREATE TABLE ParkingPermit (
116
117 PermitTypeID int PRIMARY KEY,
118
119 Location varchar(255) CHECK (Location IN ('Main Car Park',
120
121 'Building Front Reserved Parking',
122
123 'Visitor Parking Area')),
124
125 Fee numeric(7,2) CHECK (Fee BETWEEN 0 AND 10000),
126
127 Duration interval,
128
129 Description text
130
131);
132
133
134
135Location varchar(255) CHECK Location IN ('Main Car Park','Building Front Reserved Parking','Visitor Parking Area'),
136
137
138
139-- Your answer to Q1.3
140
141
142
143
144
145CREATE TABLE ParkingPermit (
146
147 PermitTypeID int PRIMARY KEY,
148
149 Location varchar(255) CHECK (Location IN ('Main Car Park',
150
151 'Building Front Reserved Parking',
152
153 'Visitor Parking Area')),
154
155 Fee numeric(7,2) CHECK (Fee BETWEEN 0 AND 10000),
156
157 Duration interval,
158
159 Description text,
160
161 PhoneNumber varchar(10) CHECK (length(PhoneNumber) = 8
162
163 OR length(PhoneNumber) = 10)
164
165);
166
167
168
169PhoneNumber varchar(10) CHECK (length(PhoneNumber) = 8
170
171 OR length(PhoneNumber) = 10)
172
173
174
175-- Your answer to Q1.4
176
177
178
179CREATE TABLE ActivePermit (
180
181 PermitTypeID int REFERENCES ParkingPermit,
182
183 UserID int REFERENCES PUser,
184
185 IssueTime timestamp,
186
187 VehicleRegNo varchar(7),
188
189 PRIMARY KEY(PermitTypeID, UserID)
190
191);
192
193
194
195-- Your answer to Q1.5
196
197
198
199INSERT INTO PUser VALUES
200
201 (0,'J Smith','jsmith@gmail.com','0425123456'),
202
203 (1,'Young Kerry','young10@hotmail.com','65003047');
204
205
206
207INSERT INTO ActivePermit VALUES
208
209 (10000,0,2016-08-01 00:00:00,'JEWUK7'),
210
211 (10001,1,2016-08-23 00:00:00,'SW4GKD');
212
213
214
215INSERT INTO ParkingPermit VALUES
216
217 (10000,'Main Car Park',400,'6 months','Employee 6-months Parking'),
218
219 (10001,'Building Front Reserved Parking',800,'3 months','Reserved 3-months Parking'),
220
221 (10003,'Visitor Parking Area',10,'1 hour','Visitor 1-hour Parking');
222
223
224
225-- Your answer to Q1.6
226
227
228
229UPDATE ParkingPermit
230
231SET Fee = Fee * 1.1
232
233WHERE Location = 'Main Car Park';
234
235
236
237----------------------------------------------------------------
238
239-- End of your answers
240
241----------------------------------------------------------------
242
243-- Do not make any changes to the following SQL statements
244
245----------------------------------------------------------------
246
247\d PUser;
248
249SELECT * from PUser;
250
251\d ParkingPermit;
252
253SELECT * from ParkingPermit;
254
255\d ActivePermit;
256
257SELECT * from ActivePermit;