· 6 years ago · Mar 27, 2019, 08:00 PM
1-- lab 2 acceptable answers:
2
3/* Lab 2 - part 1- Add tables for supplies used by projects */
4
5/*
6Constrains:
7(1) empID is unique per employee
8(2) projID is unique per project
9(3) The empName is the full name of each employee, and consists of a First and Last Name, and optionally a Middle Name.
10(4) The deptID is unique per departments
11(5) A department location is a full address, comprising the street number, street name, city name, province,
12and postal code
13(6) An employee may have more than one role on a project
14(7) An employee may be assigned to more than one project
15(8) A department may have multiple locations
16(9) An employee can be in more than one department
17
18(1) Decompose the database as per BCNF
19(2) Determine the Primary Keys (PKs)
20(3) Determine the Foreign Keys (FKs)
21(4) Create tables and integrity constrians code
22(5) Create views
23 */
24
25/*
26Decompose the initial database into BCNF + Pks & Fks:
27
28**Employee(empID, empFirstname, empLastname, job, salary); PK: empID; no FK
29=> Pks = {empID}
30=> Fks = {}
31
32*/
33
34/*
35**EmployeeMiddle(empID, empMiddle); PK: empID; FK: empID->Employee(empID)
36=> Pks = {empID}
37=> Fks = {empID}
38*/
39
40
41/*
42
43**EmployeeDepartments(empID, deptID)
44=> Pks = {empID, deptID}
45=> Fks = {deptID, empID}
46*/
47
48/*
49
50**Project(projID, title, budget, funds)
51=> Pks = {projID}
52=> Fks = {}
53
54
55**Assigned(empID, projID, role)
56=> Pks = {empID, projID} XXXX More than one role?
57=> Fks = {empID, projID}
58
59
60**Department(deptID, deptName)
61=> Pks = {deptID, deptName}
62=> Fks = {}
63
64**Location(deptID, streetNum, streetName, cityName, postalcode)
65=> PKs = {deptID, streetNum, streetName, cityName, postalcode}
66=> FKs = {deptID}
67
68
69If they really want to make Location correct, since postCode->city, province, it should really be:
70Department2(deptID, streetNum, streetName, postCode); PK: all attributes; FK deptID->Department1(deptID); postCode->Department3(postCode)
71Department3(cityName, province, postCode); PK: postCode; no FK
72
73*/
74
75/*
76** Notes to take into consideration while marking:
77------------------------------------------------------
78
79+ empID does not determine middle name, since middle name is optional;
80this is why the Employee table would require empID,middleName as a key,
81and since empID -> other things, that table needs to be broken down per 3NF and BCNF rules into two tables, one with empID,middleName and the other with empID and everything else.
82
83+ deptID -> deptName; just because a department has more than one location
84doesn't mean it would have different names in each location (which would be the only thing that would enable that to be a correct FD)
85
86+ Per the above comment, again, this doesn't make sense.
87All you can say is that you need one table for deptID,deptName
88and a second one for deptID,department location (where that location is broken down according to constraint 5).
89The PK for the first will be deptID; the PK for the second will be the combination of the deptID and its location; deptID will also have an FK to the deptID,deptName table.
90
91+ You have to split Employee's name into multiple attributes and you cannot put it into a different table
92
93+ You have to split Department's name into multiple attributes and you cannot put it into a different table
94deptID is the primary key. A locationID is not needed, but if you have one, it cannot be in the same table as deptID and deptName, since deptID->deptName.
95
96*/
97
98------------------------------------------------------------------------------------------------------------------
99/*
100Grading scheme requirements: (Total Grade 23 marks)
101---------------------------
102
103(1) Decompose the database as per BCNF (8 marks + 2 recognition marks)
104(2) Determine the Primary Keys (PKs) (3) Determine the Foreign Keys (FKs) (2 & 3 total marks 5)
105(4) Create tables and integrity constrians code (SQL) (5 marks)
106(5) Create views (5 marks)
107
108-->
109(1) Decomposition: (8 marks + 2 recognition marks)
110
111A- Ability to decompose the name into emp_fname and emp_lname in employee (1 mark for the action, and 1 mark for the justification)
112B- Ability to decompose and put emp_Mname into EmployeeMiddle (1 recognition mark for the action, and 1 recognition mark for the justification)
113C- Ability to decompose the name and deptID into BCNF, i.e., create employeedepartment table (1 mark for the action, and 1 mark for the justification)
114D- Ability to decompose the location attribute into (1NF) in Department (1 mark for the action, and 1 mark for the justification)
115E- Ability to decompose the location attribute into (BCNF) into its own table Location (1 mark for the action, and 1 mark for the justification)
116
117(2) Determine the Primary Keys (PKs)
118&
119(3) Determine the Foreign Keys (FKs)
120
121We expect to have 7 tables in this part. This question will be marked out of 5 marks
122
123A- For each table, deduct 0.125 marks if the PK choice is wrong or not reasonable
124B- For each table, deduct 0.125 marks if the FK choice is wrong or not reasonable
125C- For each table, deduct 0.125 marks if the PK SQL synatx is wrong or not reasonable.
126D- For each table, deduct 0.125 marks if the FK SQL synatx is wrong or not reasonable.
127E- For each table, if a student does a wrong action (choosing a wrong PK for example) and did not provide justification, deduct 0.25 marks
128
129The minimum grade of this question is 2.5/5, but provide feedback on the mistakes
130
1314) Write the necessary SQL to create tables for this database, together with the necessary primary and foreign keys)
132
133We expect to have from 5-7 tables in this part. Mark this question from 35. Students who have 5 tables, each one will be worth 7 marks,
134students who have 7 tables, each one will have 5 marks. The final grade will be converted to be out of 5 marks at the end.
135
136A- For each table, if the query does not work, give a 0
137B- For each table, if there is a logical error such as using inappropriate datatypes, deduct 5%
138
139(5) Create views
140
141We expect to have from 2-3 views in this part. This question will be marked out of 5
142
143A- For each view, if the query does not work, give a 0
144B- For each view, if there is a logical error such as using inappropriate datatypes or incorrect joins, deduct 5%
145C- If a student does not do NULLIF checks on the names, give feedback on this issue for the student,
146but do not deduct any marks for this issue.
147
148The minimum grade of this question is 4.25/5
149
150 */
151
152
153DROP TABLE IF EXISTS Assigned;
154DROP TABLE IF EXISTS Project;
155DROP TABLE IF EXISTS Employee;
156DROP TABLE IF EXISTS Department;
157DROP TABLE IF EXISTS Location;
158DROP TABLE IF EXISTS EmpName;
159
160# CREATE tables
161
162CREATE TABLE Department(
163deptID INT,
164deptName VARCHAR(100) NOT NULL,
165PRIMARY KEY(deptID, deptName),
166);
167
168CREATE TABLE Location(
169deptID INT,
170streetNum INT,
171streetName VARCHAR(100),
172cityName VARCHAR(50),
173postalcode VARCHAR(10),
174PRIMARY KEY(deptID, streetNum, streetName, cityName, postalcode),
175FOREIGN KEY(deptID) references Department(deptID)
176);
177
178
179CREATE TABLE Employee(
180empID INT ,
181emp_fname VARCHAR(20) NOT NULL,
182emp_Lname VARCHAR(20) NOT NULL,
183job VARCHAR(100),
184salary INT,
185PRIMARY KEY(empID),
186);
187
188
189CREATE TABLE EmployeeMiddle(
190empID INT ,
191emp_Mname VARCHAR(20),
192primary key(empID)
193);
194
195CREATE TABLE EmployeeDepartments(
196empID INT ,
197deptID INT,
198PRIMARY KEY(empID, deptID),
199foreign key(deptID) references Department(deptID),
200foreign key(empID) references Employee(empID)
201);
202
203
204CREATE TABLE Project(
205projID INT ,
206title VARCHAR(100),
207budget DECIMAL(10,2),
208funds DECIMAL(10,2),
209PRIMARY KEY(projID)
210);
211
212
213CREATE TABLE Assigned(
214empID INT,
215projID INT,
216role_description VARCHAR(100),
217foreign key(empID) references Employee(empID),
218foreign key(projID) references Project(projID),
219-- PRIMARY KEY(role_description,empID,projID)XXXX
220PRIMARY KEY(empID, projID)
221);
222
223
224-- Merge the first, middle, last name to produce empName according to our schema
225
226CREATE VIEW Employee_View (empID, empName, job, deptID, salary) AS
227 SELECT
228 EmployeeDepartment.empID,
229 CONCAT(
230 IFNULL(Employee.firstName, ''), " ",
231 IFNULL(EmployeeMiddle.middleName, ''), " ",
232 IFNULL(Employee.lastName, '') ),
233 Employee.job,
234 EmployeeDepartments.deptID,
235 Employee.salary
236 FROM Employee INNER JOIN EmployeeDepartments
237 ON Employee.empID = EmployeeDepartments.empID AND Employee.deptID = EmployeeDepartments.deptID
238 INNER JOIN EmployeeMiddle
239 ON Employee.empID = EmployeeMiddle.empID;
240
241
242CREATE VIEW Department_View (deptID, deptName, location) AS
243 SELECT
244 Department.deptID,
245 Department.deptName,
246 CONCAT(
247 IFNULL(Location.streetNum, ''), " ",
248 IFNULL(Location.streetName, ''), " ",
249 IFNULL(Location.citytName, ''), " ",
250 IFNULL(Location.postalcode, ''))
251 FROM Department INNER JOIN Location
252 ON Department.deptID = Location.deptID;
253
254-- -----------------------------------------------------------------------
255-- -----------------------------------------------------------------------
256-- -----------------------------------------------------------------------
257-- Question 1: 6) Write a stored procedure (12 marks)
258
259SET SQL_SAFE_UPDATES = 0;
260
261DROP PROCEDURE IF EXISTS payRaise;
262DROP PROCEDURE IF EXISTS all_users;
263
264
265DELIMITER $$
266CREATE PROCEDURE all_users()
267BEGIN
268SELECT * FROM Employee LIMIT 10;
269
270END$$
271
272-- Solution 1:
273/*CREATE PROCEDURE payRaise(IN inEmpID INT , IN inPercentageRaise DOUBLE(4,2) , OUT errorCode INT)
274BEGIN
275
276IF (inEmpID NOT IN (SELECT empID FROM employee))
277THEN
278SELECT @errorCode := -2 ;
279elseif (inPercentageRaise < 0 OR inPercentageRaise >10)
280THEN
281SELECT @errorCode := -1 ;
282else SELECT @errorCode := 0;
283Update Employee SET salary = salary*(1 + inPercentageRaise/100.0) where empID = inEmpID;
284END if;
285END $$ */
286
287
288-- Solution 2:
289DELIMITER $$
290
291
292CREATE PROCEDURE payRaise(inEmpID INT,
293inPercentageRaise DOUBLE(4,2), OUT errorCode INT)
294BEGIN
295DECLARE isEmpExist BOOL;
296
297SET isEmpExist = (SELECT EXISTS
298 (SELECT 1 FROM Employee WHERE empID = inEmpID));
299SET errorCode = 0;
300
301 IF isEmpExist = 1 and (inPercentageRaise <= 0.0
302 or inPercentageRaise >= 10.0)
303 THEN SET errorCode = -1; -- Employee has to exist
304 ELSEIF isEmpExist = 0
305 THEN SET errorCode = -2;
306 ELSE
307
308 -- Set error code
309 SET errorCode = 0;
310 -- Update the salary
311 Update Employee SET salary = salary*(1 + inPercentageRaise/100.0)
312 WHERE empID = inEmpID;
313
314 END IF;
315
316END$$
317
318DELIMITER ;
319
320CALL all_users();
321CALL payRaise(200, 10, @errorCode);
322SELECT @errorCode AS 'errorCode';
323
324
325/* Grading scheme for stored procedure:
326
327The total marks of this question is 12 marks
328
329(3 marks) A- Ability to declare the input variables with their data types
330(deduct 0.25 marks for wrong input variable or wrong datatype)
331(1 mark) B- Ability to declare the output variables with their data types
332(deduct 0.25 marks for wrong output variable or wrong datatype)
333(1 mark) C- Ability to check the existance of an employee (1 mark for being able to select the employee ID
334where input matches the table info)
335(7 marks) D- We have three cases to check, split them into two: -- Total of this part is 7 marks
336D1- If statement (1 mark for writing the condition right)
337D2- Body of the If statement (1 mark for writing the set statement right)
338D3- Special case: Update statement -- give 1 mark for a correct update statement
339
340Any synatax error such as missing delimiters resulting in a query that does not compile, give a 0
341
342
343*/
344
345------------------------------------------------------------------------------------------------
346-- Part 2: Create indexes on Baseball and Yelp databases (15 marks)
347
348-- Baseball Database:
349
350CREATE INDEX master_player_birthDay_index ON Master(birthDay);
351CREATE INDEX master_player_birthMonth_index ON Master(birthMonth);
352CREATE INDEX master_player_birthYear_index ON Master(birthYear);
353
354-- (Optional cause PK) CREATE INDEX master_player_id ON Master(playerID);
355CREATE INDEX hall_of_fame_inducted_index ON HallOfFame(inducted);
356CREATE INDEX master_deathYear_index ON Master(deathYear);
357CREATE INDEX master_deathCity_index ON Master(deathCity);
358CREATE INDEX master_deathCountry_index ON Master(deathCountry);
359CREATE INDEX master_deathDay_index ON Master(deathDay);
360CREATE INDEX master_deathMonth_index ON Master(deathMonth);
361CREATE INDEX master_deathState_index ON Master(deathState);
362
363
364
365CREATE INDEX Salaries_playerID_index ON Salaries(playerID);
366
367
368CREATE INDEX Batting_playerID_index ON Batting(playerID);
369
370
371-- Yelp Database:
372
373CREATE INDEX user_review_counts_index ON user (review_count);
374CREATE INDEX user_review_id_index ON user (review_id);
375-- (Optional cause PK) CREATE INDEX user_id_index ON user (user_id);
376
377CREATE INDEX business_review_counts_index ON business (review_count);
378
379CREATE INDEX review_user_id_index ON review (user_id);
380
381
382-- Grading sceheme:
383-- 7.5 marks for indexes.... Each index right is 0.5 mark (Deducted up to -3.5)
384-- 7.5 marks for Justification .... 0.5 mark for justifying each index (Deducted up to -3.5)
385-- i.e. If a student submits something for this part, give 7/15. Students with no submission at all give zero