1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
1/22

By submitting this assessment, I agree to the following declaration:
As a member of the University’s student body, I will complete this assessment in a fair, honest,
responsible and trustworthy manner. This means that:
· I will not seek out any unauthorised help in completing this assessment. (NB. Unauthorised help
includes a tutorial or answer service whether in person or online, friends or family, etc.)
· I will not discuss or share the content of the assessment with anyone else in any form, including
but not limited to, Canvas, Piazza, Chegg, Facebook, Twitter, Discord or any other social media
within the assessment period.
· I will not reproduce and/or share the content of this assessment in any domain or in any form
where it may be accessed by a third party.
· I am aware the University of Auckland may use Turnitin or any other plagiarism detecting
methods to check my content.
· I declare that this assessment is my own work, except where acknowledged appropriately (e.g.,
use of referencing).
· I declare that this work has not been submitted for academic credit in another University of
Auckland course, or elsewhere.


Note: Include as applicable e.g., STEM subjects (Science, Technology, Engineering and
Mathematics)
• I declare that I generated the calculations and data in this assessment independently, using only
the tools and resources defined for use in this assessment.
• I declare that I composed the writing and/or translations in this assessment independently, using
only the tools and resources defined for use in this assessment.


I understand the University expects all students to complete coursework with integrity and
honesty. I promise to complete all online assessment with the same academic integrity standards
and values.
Any identified form of poor academic practice or academic misconduct will be followed up and
may result in disciplinary action.


I agree
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
2/22
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]
Fill in your answer here

Maximum marks: 4
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
3/22
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]
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
4/22
Fill in your answer here. Note that you can use the 'Insert Drawing' function (the pen icon)
below to write the answer.

Format   Σ


Words: 0
Maximum marks: 6
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
5/22
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 Drawing' function (the pen icon)
below to write the answer.

Format   Σ


Words: 0
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
6/22
Maximum marks: 8
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
7/22
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]
Fill in your answer here

1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
8/22
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]
Fill in your answer here

Maximum marks: 7
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
9/22
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]
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
10/22
Fill in your answer here. Note that you can use the 'Insert Drawing' function (the pen icon)
below to write the answers. Please label your answers for each sub-questions.

Format   Σ


Words: 0
Maximum marks: 5
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
11/22
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
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
12/22
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]
Fill in your answer here

Format   Σ


Words: 0
Maximum marks: 10
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
13/22
8 Consider the following schedule,

s: r1[x], r2[x], r1[y], r3[y], c2, w1[x], w1[y], c1, r3[x], w3[x], r3[y], c3

(a) which phenomenon is appearing in this schedule, if any?
[2 marks]
Select one alternative:

(b) which operation violates the rules of the common scheduler first, if any?
[2 marks]
Select one alternative

lost update
fuzzy read
write skew
dirty read
none of the others
r2[x]
None of the others
w1[y]
r3[y] after w3[x]
w1[x]
Maximum marks: 4
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
14/22
9 Consider the following schedule,

s: r1[x], r2[x], r1[y], r3[x], c2, w1[x], w1[y], c1, r3[y], w3[x], w3[y], c3

(a) which phenomenon is appearing in this schedule, if any?
[2 marks]
Select one alternative:

(b) which operation violates the rules of the common scheduler first, if any?
[2 marks]
Select one alternative

none of the others
fuzzy read
dirty read
write skew
lost update
w1[x]
w1[y]
w3[y]
r2[x]
None of the others
Maximum marks: 4
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
15/22
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:

(b) What is the state of Stable Database Page 1 after crash recovery?
[2 marks]
y = 61, z = 72
y = 47, z = 7
y = 61, z = 7
None of the others
y = 47, z = 8
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
16/22
Select one alternative

(c) Which pages on the stable database had uncommitted writes on it at the time of the crash?
[3 marks]
Select one alternative

x=24
x=67
x=38
None of the others
x=61
Page 1 only
No page
Page 2 only
None of the others
Both Page 1 and Page2
Maximum marks: 7
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
17/22
11 In this question, 1 point is equivalent to 1 mark.

1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
18/22
Select one alternative:

1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
19/22
Select one alternative
Select one alternative
Select one alternative

6
5
4
2
3
Right after inserting 8
None of these.
Right after inserting 1
Right after inserting 7
Right after inserting 5
15
75
150
30
375
Maximum marks: 10
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
20/22
12 In this question, 1 point is equivalent to 1 mark.

i) (2 points) What is the global depth of the resulting directory table?
ii) (2 points) What is the local depth of the bucket containing 78?
iii) (2 points) What is the local depth of the bucket containing 117?
i) (2 points) Which key will first make the directory double in size?
ii) (2 points) How many different block pointers are there in the resulting directory?
Maximum marks: 10
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
21/22
13 In this question, 1 point is equivalent to 1 mark.
i. (2 points) How many passes (including pass 0) are engaged by the sorting?
ii. (2 points) How many I/Os are engaged by the sorting?




Maximum marks: 8
1213-1 COMPSCI 351 (24/06/2021 09:00) Fundamentals of Database Systems (exams)
22/22
14 A popular social app has three functions, i.e., sending messages to contacts, receiving message
from contacts and displaying the number of unread messages on the upper right corner of the
app logo. However, users are reflecting the inconsistency between the displayed number and the
number of unread messages. Specifically, when the number displayed is (updated) increased,
one usually has to wait for a long time to see the new messages.

(a) Among the following statements, which can be the possible reason(s) for the inconsistency?
[3 marks]
Select one or more alternatives:

(b) Which of the following statements is (are) correct?
[4 marks]
Select one or more alternatives

The app treats user with a high number of contacts differently with the other users.
The app only keeps a list of sent messages for each user.
None of these
The app has a cache for some of the user in storing their received message.
The app only keeps a list of received messages for each user.
The app cannot handle the load produced by the current users while a more scalable
architecture should be deployed.
Data stored in columns (column store) cannot use B-tree to speed up searching.
LSM-tree is better than B-tree in searching efficiency.
LSM-tree is ideal for write-intensive applications because it extensively engages sequential
writing.
RAID can speed up data read by introducing redundancy, which will slow down the data
writing.
Maximum marks: 7