COMPSCI 351 Fundamentals of Database Systems (exams) 2021
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
1 Under the relational data model concepts , provide the definitions of superkey, key, primary key and candidate key. What are the differences among these four terminologies? [4 marks]
Maximum marks: 4
2 Consider the partial ER diagram below, in which the 'enrols', 'teaches' and 'uses' relationship types record the current enrolment, teaching and textbook adoption information correspondingly with the following restrictions .
Assume that a course may or may not use a textbook , but a textbook by definition is a book that is used in some courses . A course can not use more than three textbooks . An instructor teaches from one to five courses at any time. A course must have at least one
instructor, and it can be taught by up to four instructors at any time.
A course must have students enrol in it. A student may not enrol in any course, or can enrol
in up to 6 courses at any time.
Specify the (min, max) constraints for three relationship types of the ER diagram .
[6 marks]
Fill in your answer here. Note that you can use the 'Insert Draw ing' function (the pen icon) below to w rite the answer.
Format Σ |
|
Words: 0 |
Maximum marks: 6
3 Using the algorithm taught in the course, map the following simplified 'MOVIES' ER diagram into a relational database schema. Specify all primary keys and foreign keys .
[8 marks]
Fill in your answer here. Note that you can use the 'Insert Draw ing' function (the pen icon) below to w rite the answer.
Format Σ |
|
Words: 0 |
Maximum marks: 8
4 The following questions use the LIBRARY database schema shown below.
Suppose that the above database has been created and populated with valid data, define the following queries using SQL statements:
i. List all the overdue books as per today (‘2021-06-25’) and their borrower information (show attributes such as Book_id, Title, Branch_id, Due_date, Card_no and Name). Sort the query output tuples by their due dates in descending order.
[4 marks]
ii. List how many different books each library branch has (show attributes such as Branch_id, Branch_name, Number_of_books). Note that multiple copies of the same book in a branch should be counted only once.
[3 marks]
Maximum marks: 7
5 The following questions use the LIBRARY database schema shown below.
Suppose that the above database has been created and populated with valid data. Define the following queries using Relational Algebra expressions:
i. For each branch, retrieve the number of times books authored by 'Isaac Asimov' have been loaned. [3 marks]
ii. Retrieve the title of books that have never been loaned. [2 marks]
Fill in your answer here. Note that you can use the 'Insert Draw ing' function (the pen icon) below to w rite the answers. Please label your answers for each sub-questions.
Format Σ |
|
Words: 0 |
Maximum marks: 5
6 Consider the relation schema
Supplies = {Article, Supplier, Location, Cost}
that lists the cost of articles supplied by a supplier from a location. Consider also the set of functional dependencies
F = {{Supplier, Location} → {Article}, {Article} → {Cost}, {Article} → {Supplier}}
(a) Find a Third Normal Form decomposition of Supplies with respect to the given set of functional dependencies . Is the decomposition lossless? [5 marks]
(b) Find a Boyce-Codd Normal Form decomposition of Supplies with respect to the given set of functional dependencies . Is the decomposition lossless? [5 marks]
Fill in your answer here. Please label your answers for each sub-questions.
Format Σ |
|
Words: 0 |
Maximum marks: 10
7 Assume for our enrolment system that if a course has labs , then students must be enrolled in both the course and the lab in order to participate. Let's assume both labs and course are restricted to a maximum number of enrolments .
Discuss in about 10 sentences whether one should use transaction processing to manage such an enrolment system and discuss what could go wrong without transaction processing. Refer to the ACID properties where it is helpful.
[10 marks]
Maximum marks: 10
8 Consider the following schedule,
s: r1[x], r2[x], r1[y], r3[y], c2, w 1[x], w 1[y], c 1, r3[x], w3[x], r3[y], c3
(a) which phenomenon is appearing in this schedule, if any? [2 marks]
Select one alternative:
lost update
fuzzy read
write skew
dirty read
none of the others
(b) which operation violates the rules of the common scheduler first, if any? [2 marks]
Select one alternative
r2[x]
None of the others
w 1[y]
r3[y] after w3[x]
w 1[x]
Maximum marks: 4
9 Consider the following schedule,
s: r1[x], r2[x], r1[y], r3[x], c2, w 1[x], w 1[y], c 1, r3[y], w3[x], w3[y], c3
(a) which phenomenon is appearing in this schedule, if any? [2 marks]
Select one alternative:
none of the others
fuzzy read
dirty read
write skew
lost update
(b) which operation violates the rules of the common scheduler first, if any? [2 marks]
Select one alternative
w 1[x]
w 1[y]
w3[y]
r2[x]
None of the others
Maximum marks: 4
10 Crash recovery for steal, no-force policy.
Our database, which follows the steal, no-force policy, encounters a system crash. After the crash, the stable database contains the following pages , objects on these pages and their values:
Page 1: x = 67
Page 2: y = 61, z = 8
The stable log records contains the following last entries:
[nr: 718, ta :32, BOT]
[nr: 719, ta :32, obj:z , b:7, a:8]
[nr: 720, ta :33, BOT]
[nr: 721, ta :34, BOT]
[nr: 722, ta :33, obj:y, b:47, a:61]
[nr: 723, ta :32, obj:x , b:24, a:38]
[nr: 724, ta :32, commit]
[nr: 725, ta :33, obj:x , b:38, a:67]
[nr: 726, ta :34, obj:z , b:8, a:72]
(a) What is the state of Stable Database Page 2 after crash recovery? [2 marks]
Select one alternative:
y = 61, z = 72
y = 47, z = 7
y = 61, z = 7
None of the others
y = 47, z = 8
(b) What is the state of Stable Database Page 1 after crash recovery? [2 marks]
Select one alternative
x=24
x=67
x=38
None of the others
x=61
(c) Which pages on the stable database had uncommitted writes on it at the time of the crash? [3 marks]
Select one alternative
Page 1 only
No page
Page 2 only
None of the others
Both Page 1 and Page2
Maximum marks: 7
2023-06-03