HONG KONG BAPTIST UNIVERSITY SEMESTER 1 EXAMINATION, 20XX-20XX Course Code:
HONG KONG BAPTIST UNIVERSITY SEMESTER 1 EXAMINATION, 20XX-20XX Course Code: Course Title: COMP1160 Section No.: All Database Management Page: Time Allowed: 1 2 of 4 Hours Total Number of Pages: 4 *** SAMPLE PAPER *** This is a CLOSE book examination. This examination contains 8 questions and you must answer ALL the questions. Question 1: Concepts/Short Answers (20 marks) Briefly answer the following questions: No short questions this year! a) What are the two parts of a relation? Briefly explain each part. b) Assume that an ER model can capture only single-valued attributes. How can we handle multi-valued attributes (e.g., phone numbers) with this ER model? c) List the three problems with data redundancy. d) List three types of integrity constraints that can be enforced in table creation statements. e) Consider the following relation: Enrolled(snum: integer, cname: string, grade: char) Finish the following table creation statement such that it will ensure the grade must be one of the following values: „P‟, „F‟, or NULL. CREATE TABLE Enrolled ( snum INTEGER, cname CHAR(20), grade CHAR, PRIMARY KEY (snum, cname), FOREIGN KEY (snum) REFERENCES Student, FOREIGN KEY (cname) REFERENCES Class, CHECK ( )) Question 2: ER Modeling (12 marks) Design an ER diagram for the following scenario. Use ER modeling constructs, such as keys, participation, weak entities, as appropriate. A company database needs to store information about employees (identified by eid, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), children of employees (with name and age as attributes). Employees work in departments. Each department is managed by an employee. A child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. HONG KONG BAPTIST UNIVERSITY SEMESTER 1 EXAMINATION, 20XX-20XX Course Code: Course Title: COMP1160 Section No.: All Database Management Page: Time Allowed: 2 2 of 4 Hours Total Number of Pages: 4 Question 3: Translating ER to Relational Model (10 marks) Consider the scenario from Question 2, where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. Also state the constraints that you cannot capture, if any. Question 4: Relational Models and Triggers (15 marks) Consider the following tables: Students(sid, sname, major, year_of_study, gpa) Courses(cid, cname, enroll_count, lecturer) Enrolled(sid, cid, score) a) Perform the following actions using SQL. 1. Enrolled all CS/IS majored year 1 students into COMP1160, with initial scores set to NULL 2. Delete all courses whose enrollments are less than 5. 3. Update the gpa of all students enrolled in COMP1160 by 10%. b) Automate the following procedures by using trigger: whenever a record Enrolled is inserted/deleted, update enroll_count in Courses. Question 5: Relational Algebra (10 marks) Consider the following tables: B(bid, bname, color) R(sid, bid, day) S(sid, sname, rating, age) Write the following queries in relational algebra. 1. Find the names of sailors who‟ve reserved boat #103. 2. Find the names of sailors who‟ve reserved a red and a green boat. 3. Find the names of sailors who‟ve reserved all boats. Question 6: SQL (18 marks) Consider the following tables: B(bid, bname, color) R(sid, bid, day) S(sid, sname, rating, age) Write the following queries in SQL. 1. Find the names of sailors who‟ve reserved boat #103. HONG KONG BAPTIST UNIVERSITY SEMESTER 1 EXAMINATION, 20XX-20XX Course Code: Course Title: 2. 3. 4. 5. COMP1160 Section No.: Database Management All Page: Time Allowed: 3 2 Total Number of Pages: of 4 Hours 4 Find the names of sailors who‟ve reserved a red boat. Find the names of sailors who‟ve reserved a red and a green boat. Find the names of sailors who‟ve reserved all boats. Find the names of sailors who‟ve reserved at least two boats. Question 7: Functional Dependency and Normalization (10 marks) Consider the following relation and sample data: ProjectID EmpName EmpSalary 100A Jones 64K 100A Smith 51K 100B Smith 51K 200A Jones 64K 200B Jones 64K 200C Parks 28K 200C Smith 51K 200D Parks 28K a) Assuming that all of the functional dependencies and constraints are apparent in this data, which of the following statements is true? 1. (ProjectID, EmpName) EmpSalary 2. EmpName EmpSalary 3. EmpSalary (ProjectID, EmpName) b) What‟s the primary key? c) In what normal form is PROJECT? d) Redesign this relation to get it into 3NF. Question 8: XML Data Model (5 marks) Consider the following XML document: <?xml version = “1.0” encoding = “UTF-8” standalone =”yes”?> <db> <course> <title>Database Management Systems</title> <sem>Fall, 2009</sem> <lecturer> Prof. A</lecturer> <lecschedule day = “Monday” stime = “9:00 am” etime = “10:00 am”/> <lecschedule day = “Wednesday” stime = “9:00 am” etime = “10:00 am”/> <lecplace >LT1</lecplace> </course> HONG KONG BAPTIST UNIVERSITY SEMESTER 1 EXAMINATION, 20XX-20XX Course Code: Course Title: COMP1160 Section No.: All Database Management Page: Time Allowed: 4 2 Total Number of Pages: of 4 Hours 4 <course> <title>Database System Implementation</title> <sem>Fall, 2009</sem> <lecturer>Prof. B</lecturer> <lecschedule day = “Tuesday” stime = “1:00 pm” etime = “2:00 pm”/> <lecschedule day = “Thursday” stime = “1:00 pm” etime = “2:00 pm”/> <lecschedule day = “Friday” stime = “1:00 pm” etime = “2:00 pm”/> <lecplace>LT2</lecplace> <stunum>35</stunum> </course> </db> a) Given the following DTD definition: <?xml version='1.0'?> <!ELEMENT db (course)* > <!ELEMENT course (title, sem, lecturer, lecschedule+, lecplace?, stunum> <!ELEMENT title (#PCDATA)> <!ELEMENT sem (#PCDATA)> <!ELEMENT lecturer (#PCDATA)> <!ELEMENT lecschedule> <!ATTLIST lecschedule day CDATA #REQUIRED> <!ATTLIST lecschedule stime CDATA #REQUIRED> <!ATTLIST lecschedule etime CDATA #REQUIRED> <!ELEMENT lecplace (#PCDATA)> <!ELEMENT stunum (#PCDATA)> Is the above XML document valid with respect to this DTD? Explain your answer. b) Write an XQuery query that lists the names of all professors. - The End -
© Copyright 2024