· 9 years ago · Feb 02, 2017, 03:38 PM
15019COMP
2Database Design, Apps & Management
3
4Model Solution Database
5SQL Scripts (DDL & DML Statements)
6
7DROP TRIGGER IF EXISTS grade_modify;
8DROP TABLE IF EXISTS audit;
9
10DROP PROCEDURE IF EXISTS schedule_course;
11
12DROP VIEW IF EXISTS `schedule`;
13
14DROP TABLE IF EXISTS take;
15DROP TABLE IF EXISTS delegate;
16DROP TABLE IF EXISTS `session`;
17DROP TABLE IF EXISTS module;
18DROP TABLE IF EXISTS course;
19
20
21CREATE TABLE Statements (plus Constraints).
22
23CREATE TABLE course (
24 `code` CHAR(3) NOT NULL,
25 `name` VARCHAR(30) NOT NULL,
26 credits TINYINT NOT NULL,
27 CONSTRAINT pri_course PRIMARY KEY (`code`),
28 CONSTRAINT chk_course
29 CHECK (credits IN (50, 75, 100)));
30
31CREATE TABLE module (
32 `code` CHAR(2) NOT NULL,
33 `name` VARCHAR(30) NOT NULL,
34 cost DECIMAL(8,2) NOT NULL,
35 credits TINYINT NOT NULL,
36 course_code CHAR(3) NOT NULL,
37 CONSTRAINT pri_module PRIMARY KEY (`code`),
38 CONSTRAINT chk_module
39 CHECK (credits IN (25, 50)),
40 CONSTRAINT for_module FOREIGN KEY (course_code)
41 REFERENCES course (`code`) ON UPDATE CASCADE ON DELETE CASCADE);
42
43CREATE TABLE `session` (
44 `code` CHAR(2) NOT NULL,
45 `date` DATE NOT NULL,
46 room VARCHAR(30) NULL,
47 CONSTRAINT pri_session PRIMARY KEY (`code`, `date`),
48 CONSTRAINT for_session FOREIGN KEY (`code`)
49 REFERENCES module (`code`) ON UPDATE CASCADE ON DELETE CASCADE);
50
51CREATE TABLE delegate (
52 `no` INT NOT NULL,
53 `name` VARCHAR(30) NOT NULL,
54 phone VARCHAR(30) NULL,
55 CONSTRAINT pri_delegate PRIMARY KEY (`no`));
56
57CREATE TABLE take (
58 `no` INT NOT NULL,
59 `code` CHAR(2) NOT NULL,
60 grade TINYINT NULL,
61 CONSTRAINT pri_take PRIMARY KEY (`no`, `code`),
62 CONSTRAINT for1_take FOREIGN KEY (`no`)
63 REFERENCES delegate (`no`) ON UPDATE CASCADE ON DELETE CASCADE,
64 CONSTRAINT for2_take FOREIGN KEY (`code`)
65 REFERENCES module (`code`) ON UPDATE CASCADE ON DELETE CASCADE);
66
67
68CREATE INDEX index_module ON module (course_code);
69CREATE INDEX index_session ON `session` (`code`);
70CREATE INDEX index1_take ON take (`no`);
71CREATE INDEX index2_take ON take (`code`);
72
73
74CREATE VIEW Statements.
75
76CREATE VIEW `schedule`
77AS
78 SELECT `code`, `date`, room
79 FROM `session`
80 WHERE `date` > CURRENT_DATE()
81 WITH CHECK OPTION;
82
83
84Write a simple statement to test rejection !
85
86INSERT INTO `schedule` VALUES ('A2', '2012.12.12', NULL);
87
88
89CREATE PROCEDURE Statements.
90
91DELIMITER $$
92
93CREATE PROCEDURE schedule_course (IN schedule_code CHAR(3), IN schedule_date DATE)
94 BEGIN
95 DECLARE complete BOOLEAN DEFAULT FALSE;
96 DECLARE module_code CHAR(2);
97 DECLARE module_c CURSOR FOR
98 SELECT `code` FROM module WHERE course_code = schedule_code;
99 DECLARE CONTINUE HANDLER FOR NOT FOUND
100 SET complete = TRUE;
101
102 -- ToDo : check if course exists ?
103
104 IF (schedule_date < DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)) THEN
105 SIGNAL SQLSTATE '45000'
106 SET MESSAGE_TEXT = ' schedule_date is not a month in the future';
107 END IF;
108
109 OPEN module_c;
110
111 loopy : LOOP
112 FETCH NEXT FROM module_c INTO module_code;
113
114 IF complete THEN
115 LEAVE loopy;
116 END IF;
117
118 IF WEEKDAY(schedule_date) = 5 THEN -- NOTE : Saturday !
119 SET schedule_date = DATE_ADD(schedule_date, INTERVAL 2 DAY);
120 ELSEIF WEEKDAY(schedule_date) = 6 THEN -- NOTE : Sunday !
121 SET schedule_date = DATE_ADD(schedule_date, INTERVAL 1 DAY);
122 END IF;
123
124 INSERT INTO `session` VALUES (module_code, schedule_date, NULL);
125
126 SET schedule_date = DATE_ADD(schedule_date, INTERVAL 1 DAY);
127 END LOOP;
128
129 CLOSE module_c;
130 END$$
131
132DELIMITER ;
133
134
135CREATE TRIGGER Statements.
136
137CREATE TABLE audit (
138 delegate_no INT NOT NULL,
139 module_code CHAR(2) NOT NULL,
140 grade_old TINYINT NULL,
141 grade_new TINYINT NULL,
142 audit_no INT NOT NULL AUTO_INCREMENT,
143 audit_who VARCHAR(30) NOT NULL,
144 audit_when DATETIME NOT NULL,
145 CONSTRAINT pri_audit PRIMARY KEY (audit_no));
146
147DELIMITER &&
148
149CREATE TRIGGER grade_modify
150 AFTER UPDATE ON take FOR EACH ROW
151 BEGIN
152 IF (OLD.grade <> NEW.grade) THEN -– ToDo : problem when comparing nullable ?
153 INSERT INTO audit
154 (delegate_no, module_code, grade_old, grade_new, audit_who, audit_when)
155 VALUES
156 (NEW.`no`, NEW.`code`, OLD.grade, NEW.grade, CURRENT_USER(), NOW());
157 END IF;
158 END&&
159
160DELIMITER ;
161
162
163INSERT Statements (from Sample Data).
164
165INSERT INTO course VALUES ('WSD', 'Web Systems Development', 75);
166INSERT INTO course VALUES ('DDM', 'Database Design & Management', 100);
167INSERT INTO course VALUES ('NSF', 'Network Security & Forensics', 75);
168
169INSERT INTO module VALUES ('A2', 'ASP.NET', 250, 25, 'WSD');
170INSERT INTO module VALUES ('A3', 'PHP', 250, 25, 'WSD');
171INSERT INTO module VALUES ('A4', 'JavaFX', 350, 25, 'WSD');
172INSERT INTO module VALUES ('B2', 'Oracle', 750, 50, 'DDM');
173INSERT INTO module VALUES ('B3', 'SQLS', 750, 50, 'DDM');
174INSERT INTO module VALUES ('C2', 'Law', 250, 25, 'NSF');
175INSERT INTO module VALUES ('C3', 'Forensics', 350, 25, 'NSF');
176INSERT INTO module VALUES ('C4', 'Networks', 250, 25, 'NSF');
177
178INSERT INTO `session` VALUES ('A2', '2015.06.05', '305');
179INSERT INTO `session` VALUES ('A3', '2015.06.06', '307');
180INSERT INTO `session` VALUES ('A4', '2015.06.07', '305');
181INSERT INTO `session` VALUES ('B2', '2015.08.22', '208');
182INSERT INTO `session` VALUES ('B3', '2015.08.23', '208');
183INSERT INTO `session` VALUES ('A2', '2016.05.01', '303');
184INSERT INTO `session` VALUES ('A3', '2016.05.02', '305');
185INSERT INTO `session` VALUES ('A4', '2016.05.03', '303');
186INSERT INTO `session` VALUES ('B2', '2016.07.10', NULL);
187INSERT INTO `session` VALUES ('B3', '2016.07.11', NULL);
188
189INSERT INTO delegate VALUES (2001, 'Mike', NULL);
190INSERT INTO delegate VALUES (2002, 'Andy', NULL);
191INSERT INTO delegate VALUES (2003, 'Sarah', NULL);
192INSERT INTO delegate VALUES (2004, 'Karen', NULL);
193INSERT INTO delegate VALUES (2005, 'Lucy', NULL);
194INSERT INTO delegate VALUES (2006, 'Steve', NULL);
195INSERT INTO delegate VALUES (2007, 'Jenny', NULL);
196INSERT INTO delegate VALUES (2008, 'Tom', NULL);
197
198INSERT INTO take VALUES (2003, 'A2', 68);
199INSERT INTO take VALUES (2003, 'A3', 72);
200INSERT INTO take VALUES (2003, 'A4', 53);
201INSERT INTO take VALUES (2005, 'A2', 48);
202INSERT INTO take VALUES (2005, 'A3', 52);
203INSERT INTO take VALUES (2002, 'A2', 20);
204INSERT INTO take VALUES (2002, 'A3', 30);
205INSERT INTO take VALUES (2002, 'A4', 50);
206INSERT INTO take VALUES (2008, 'B2', 90);
207INSERT INTO take VALUES (2007, 'B2', 73);
208INSERT INTO take VALUES (2007, 'B3', 63);
209
210
211Query Functionality.
212
2131. SELECT `code`, `name`, credits
214 FROM module;
215code name credits
216A2 ASP.NET 25
217A3 PHP 25
218A4 JavaFX 25
219B2 Oracle 50
220B3 SQLS 50
221C2 Law 25
222C3 Forensics 25
223C4 Networks 25
224
225
2262. SELECT `no`, `name`
227 FROM delegate
228 ORDER BY `name` DESC;
229no name
2302008 Tom
2312006 Steve
2322003 Sarah
2332001 Mike
2342005 Lucy
2352004 Karen
2362007 Jenny
2372002 Andy
238
239
2403. SELECT `code`, `name`, credits
241 FROM course
242 WHERE `name` LIKE '%Network%';
243code name credits
244NSF Network Security & Forensics 75
245
246
2474. SELECT MAX(grade) AS 'highest'
248 FROM take;
249highest
25090
251
252
2535. SELECT `no`
254 FROM take
255 WHERE grade =
256 (SELECT MAX(grade) FROM take);
257no
2582008
259
260
2616. SELECT `no`, `name`
262 FROM delegate
263 WHERE `no` =
264 (SELECT `no` FROM take WHERE grade =
265 (SELECT MAX(grade) FROM take));
266no name
2672008 Tom
268
269
2707. SELECT `code`, `date`
271 FROM `session`
272 WHERE (`date` BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 1 YEAR)
273 AND (room IS NULL));
274code date
275B2 2016.07.10
276B3 2016.07.11
277
278
2798. SELECT D.`no`, D.`name`, M.`code`, M.`name`
280 FROM delegate D INNER JOIN take T
281 ON D.`no` = T.`no`
282 INNER JOIN module M
283 ON T.`code` = M.`code`
284 WHERE T.grade < 40;
285no name code name
2862002 Andy A2 ASP.NET
2872002 Andy A3 PHP
288
289
2909. SELECT D.`no`, D.`name`
291 FROM delegate D INNER JOIN take T
292 ON D.`no` = T.`no`
293 WHERE T.grade =
294 (SELECT MAX(grade) FROM take);
295no name
2962008 Tom
297
298
29910. SELECT D.`no`, D.`name`, SUM(M.credits) AS 'attained', C.`code`, C.`name`, C.credits
300 FROM delegate D INNER JOIN take T
301 ON D.`no` = T.`no`
302 INNER JOIN module M
303 ON T.`code` = M.`code`
304 INNER JOIN course C
305 ON M.course_code = C.`code`
306 WHERE T.grade >= 40
307 GROUP BY D.`no`, D.`name`, C.`code`, C.`name`, C.credits;
308no name attained code name credits
3092002 Andy 25 WSD Web Systems Development 75
3102003 Sarah 75 WSD Web Systems Development 75
3112005 Lucy 50 WSD Web Systems Development 75
3122007 Jenny 100 DDM Database Design & Management 100
3132008 Tom 50 DDM Database Design & Management 100
314
315
31611. SELECT D.`no`, D.`name`, SUM(M.credits) AS 'attained', C.`code`, C.`name`, C.credits
317 FROM delegate D INNER JOIN take T
318 ON D.`no` = T.`no`
319 INNER JOIN module M
320 ON T.`code` = M.`code`
321 INNER JOIN course C
322 ON M.course_code = C.`code`
323 WHERE T.grade >= 40
324 GROUP BY D.`no`, D.`name`, C.`code`, C.`name`, C.credits
325 HAVING SUM(M.credits) = C.credits;
326no name attained code name credits
3272003 Sarah 75 WSD Web Systems Development 75
3282007 Jenny 100 DDM Database Design & Management 100