PR AC TI CE EX AM PRACTICE EXAMINATION: NOVEMBER 3, 2020 Unit Code: COMP2350/6350 Unit Name: Database Systems Duration of Exam (including reading time if applicable): Tight Examination. Three hours...

Its a sample question paper


PR AC TI CE EX AM PRACTICE EXAMINATION: NOVEMBER 3, 2020 Unit Code: COMP2350/6350 Unit Name: Database Systems Duration of Exam (including reading time if applicable): Tight Examination. Three hours plus 10 minutes reading time + 30 minutes technical time allowance Total no. of Questions: 6 (three questions in each section: Section A and Section B) Total no. of Pages (including this cover sheet): 7 GENERAL INSTRUCTIONS TO STUDENTS: This is a Tight Examination. Students should start at the announced time, and have 3 hours (and 10 minutes reading time) to complete the exam. This Exam will be available at 3:00 pm (AEDT/ Sydney time) and students should submit by 6:10 pm (Sydney time). There is a Technical Allowance of 30 minutes – so students who could not submit by 6:10 due to technical difficulty can still submit until 6:40pm. Documents submitted after 6:40 pm will not be considered irrespective of any technical problem. EXAMINATION INSTRUCTIONS: Answer ALL questions. This examination consists of two sections, Section A and Section B. Section A has 3 questions, A1, A2 and A3. Section B has 3 questions, B1, B2 and B3. Not all questions are of equal mark. Answers to each question should start with a new page. Add the section name (SECTION A or SECTION B), question name (Question A1 . . . Question B3), your name, and student number at the beginning of your answer to each question. The answer to all questions in this exam should be submitted as a single PDF document to Turnitin via iLearn. The submission you make will not be marked, and you will not receive any feedback on your submission. 1 PR AC TI CE EX AM SECTION A QUESTION A1. (7 marks) Write SECTION A: Question A1, your name and student ID on the top of the first page in your answer to this questions. (a) (3 marks) Provide a set of relational schemas for the high-lighted part (in blue box) of the data model shown in Figure 1 below. Identify primary, alternate, and foreign keys in the tables. (b) (2 marks) (i) What is the significance of integrity constraints/rules in relational database de- sign? Explain in this context why Primary Keys alone are not sufficient to implement referential integrity constraints.. (ii) Give an example of an integrity constraint which can neither be modelled as an entity integrity constraint, nor as a referential integrity constraint. (c) (2 marks) Explain, with at least one example, how these two integrity rules/constraints are implemented in the schemas that you designed as part of your answer to the question 1(a) above. Figure 1: A high-level data model of a company 2 PR AC TI CE EX AM SECTION A QUESTION A2. (7 marks) Write SECTION A: Question A2, your name and student ID on the top of the first page in your answer to this questions. Consider the relation schema: Enrolment (Dept, UnitNo, Offering, SID, SName, EnDate, CName, CLoc) intended to contain student enrolment data for My University, a multi-campus university with different campuses around Sydney. The enrolment date is represented as EnDate, campus name as CName and campus location as CLoc. A unit is offered by a particular department, is taught at a particular campus, and a student must enrol in it for a particular offering by a given date. The table below is a snapshot of the data held at some particular time. Dept UnitNo Offering SID SName EnDate CName CLoc Computing Isys224 2015S2 12345 John 1/6/2015 Main NorthRyde Computing Isys114 2015S1 12345 John 1/1/2015 SIBT NorthRyde Economics Econ223 2015S2 21324 Jack 1/6/2015 CityC CBD Computing Isys224 2015S2 21325 Jill 1/6/2015 Main NorthRyde Economics Econ123 2015S1 21325 Jill 2/1/2015 CityC CBD Economics Econ213 2015S2 12345 John 1/6/2015 Main NorthRyde Table 1: Enrolment at My University (a) (4 marks) The data in the table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on Table 1. Explain what steps can be taken to address these anomalies. (b) (3 marks) (i) Identify the non-trivial functional dependencies based on your understanding of the My University domain. Explain how the data in Table 1 satisfy those dependencies. State any assumptions you have made. (ii) Did you identify UnitNo → Offering as one of the functional dependencies above? Justify why you did, or did not, do so. 3 PR AC TI CE EX AM SECTION A QUESTION A3. (9 marks) Write SECTION A: Question A3, your name and student ID on the top of the first page in your answer to this questions. The following tables form part of a database held in a Relational Database Management System: Employee (empID, fName, lName, address, DOB, sex, deptNo) Department (deptNo, deptName, mgrEmpID) Project (projNo, projName, deptNo) WorksOn (empID, projNo, hoursWorked) 1. Employee table contains employee details and empID is the key. 2. Department table contains department details and deptNo is the key. mgrEmpID identifies the employee who is the manager of the department. There is only one manager for each department. 3. Project table contains details of the projects in each department and the key is projNo (no two departments can run the same project). 4. WorksOn table contains details of the hours worked by employees on each project, and empID and projNo together form the key. Complete the following tasks in this context. In parts (b) and (c), provide the SQL code (DDL or DML) as appropriate. (a) (3 marks) Discuss the differences between the candidate keys and the primary key of a relation. Explain what is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Give examples from the above tables to illustrate your answer. (b) (3 marks) Create the Department table. Make reasonable assumptions regarding the datatypes of the attributes. Make sure that the Primary Key and Foreign Key con- straints, as appropriate, are implemented. (c) (3 marks) Formulate queries in SQL to do the following: (i) List details of all female employees who work in the HR department. (ii) For each employee, provide their names (fName, lName), the projects they work on (projName) and the number of hours they work on those projects. 4 PR AC TI CE EX AM SECTION B QUESTION B1. (10 marks) Write SECTION B: Question B1, your name and student ID on the top of the first page in your answer to this questions. This question assumes the following relational schema: Student (StudentID, Lname, Fname, Address, BirthDate, Phone, MajorID, DeptID ) Primary Key: StudentID You have been given the following stored MySQL procedure which uses a cursor to process the details of all the students studying for a particular major: CREATE PROCEDURE StudentMajors ( IN MajorID INT ) BEGIN DECLARE SID, DID INT; DECLARE LN, FN VARCHAR(25); DECLARE Finished INT DEFAULT 0; DECLARE MajorCohort CURSOR FOR SELECT StudentID, Lname, Fname, DeptID FROM Student WHERE Student.MajorID = MajorID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = 1; ..... REPEAT ...... ...... ...... UNTIL Finished; ...... END (a) (5 marks) Complete this program by providing the corresponding statements for the dotted lines ...... (b) (5 marks) Write a trigger to enforce the following business rule: Each student must be older that 15 years before enrolling in a university. You may use the following trigger template for ensuring this business rule while new rows are inserted into Student: CREATE TRIGGER TR_BDAY BEFORE INSERT ON ... FOR EACH ROW BEGIN ... END 5 PR AC TI CE EX AM SECTION B QUESTION B2. (9 marks) Write SECTION B: Question B2, your name and student ID on the top of the first page in your answer to this questions. (a) (2 marks) Discuss the atomicity and the consistency preservation properties of a database transaction. (b) (3 marks) Consider a database with objects X and Y, and the following two transactions T1 and T2 involving those objects: T1 T2 begin transaction begin transaction read(X); read(X); read(Y); read (Y); X = X-Y; X = X+10; write (X); Y = X -Y; read(Y) write(X); write (Y) (i) Give an example of a schedule involving transactions T1 and T2 that results in a write-write conflict (lost update). (ii) Add lock and unlock instructions to the transaction T1 and T2 so that they observe the strict two-phase locking protocol (2PL). (iii) Explain how the use of the strict two-phase locking protocol can resolve the conflict you identified in part (i) above. (c) (4 marks) (i) Discuss whether any concurrent execution of these two transactions T1 and T2 shown in part (b) can result in a deadlock. (ii) Verify whether the lock/unlock instructions that you introduced in part b(ii) above lead to a deadlock. If there is a deadlock, explain how it could have been prevented. 6 PR AC TI CE EX AM SECTION B QUESTION B3. (8 marks) Write SECTION B: Question B3, your name and student ID on the top of the first page in your answer to this questions. (a) (4 marks) (i) Discuss the function and significance of log files in any database recovery mecha- nism. (ii) What is the main difference in log records used for Deferred database modification recovery and Immediate database modification recovery? Explain why they are different. (b) (4 marks) Consider that immediate update recovery protocol with checkpointing is used in the database management system. The following transaction log shows a particular schedule at the point of system crash for four transactions T1, T2, T3 and T4. Describe the recovery process from the system crash. Specify which transactions are rolled back, which operations are redone, and which operations (if any) are undone. < t1="" starts=""> < d,="" t1,="" 20,="" 25=""> < t1="" commits=""> < t2="" starts=""> < b,="" t2,="" 120,="" 180=""> < t4="" starts=""> < d,="" t4,="" 25,="" 115=""> < t3="" starts=""> < c,="" t3,="" 30,40=""> < a,="" t4,="" 30,="" 20=""> ⇐= System Crash! . . . End of the Practice Exam . . . 7