23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 1/7
Practice Final Exam 50 Possible Points
IN PROGRESS
Next Up: Submit Assignment
Unlimited Attempts Allowed
Attempt 1 Add Comment
Details
In case the images do not load properly in your browser,
you may download the images from here Practice Exam
images.pdf
(https://canvas.sydney.edu.au/courses/35658/files/20438202?
wrap=1)
1. Consider a CONFERENCE_REVIEW database in which
researchers submit their research papers for
consideration. Reviews by reviewers are recorded for use
in the paper selection process. The database system
caters primarily to reviewers who record answers to
evaluation questions for each paper they review and make
recommendations regarding whether to accept or reject
the paper. The data requirements are summarized as
follows:
Authors of papers are uniquely identified by e-mail id.
First and last names are also recorded.
Each paper is assigned a unique identifier by the
system and is described by a title, abstract, and the
name of the electronic file containing the paper.
A paper may have multiple authors, but one of the
authors is designated as the contact author.
Reviewers of papers are uniquely identified by e-mail
address. Each reviewer’s first name, last name, phone
number, affiliation, and topics of interest are also
recorded.
Each paper is assigned between two and four
reviewers. A reviewer rates each paper assigned to
him or her on a scale of 1 to 10 in four categories:
technical merit, readability, originality, and relevance to
the conference. Finally, each reviewer provides an
overall recommendation regarding each paper.
6
marks
Submit Assignment
https://canvas.sydney.edu.au/courses/35658/files/20438202?wrap=1
23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 2/7
Each review contains two types of written comments:
one to be seen by the review committee only and the
other as feedback to the author(s).
Design an Entity-Relationship diagram for the
CONFERENCE_REVIEW database.
2. This question is based on an E-R diagram, such as the
following, which describes the information kept on traffic
accidents.
4
marks
Translate this E-R diagram into a relational model
using the following textual notation. Each relation
should be written in the form:
Name(attribute, attribute, …) PK=(attribute list), CK=
(attribute list), FK=(FK list)
An ‘attribute list’ is one or more, comma-separate attribute
names. Each relation must have a primary key (PK), and
can have 0 or more candidate keys (CK) and 0 or more
foreign keys (FK). An ‘FK list’ consists of 1 or more,
comma-separated, attribute->parent table.
For example: Enrollment(studentId, courseId, mark)
PK=(studentId, courseId) FK=(studentId ->
Student, coursed -> Course)
Do not write this relational model in SQL DDL syntax, use
the above convention to describe your model.
Submit Assignment
23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 3/7
3. Write SQL CREATE TABLE statements for Vehicle relation
from your relational models in question 2. You should
include foreign key integrity constrains, like ON DELETE
clauses if suitable, as well as any necessary NOT NULL or
UNIQUE constraints.
3
marks
4. Consider the bank database consisting of the following
tables:
9
marks
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street,
customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)
Write down the SQL expressions for the following queries:
Find the names of all branches that have assets
greater than at least one branch located in Gold Coast
city
Find the average balance for each customer who lives
in Johnson street and has at least three accounts
Find all customers who have both an account and a
loan at the Darling Harbour branch
5. Consider the relation R, which has attributes that hold
schedules of courses and sections at a university.
7
marks
R = Course_no, Sec_no, Offering_dept, Credit_hours,
Course_level, Instructor_id, Semester, Year, Days_hours,
Room_no, No_of_students.
Submit Assignment
23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 4/7
Suppose that the following functional dependencies hold
on R:
Course_no → Offering_dept, Credit_hours, Course_level
Course_no, Sec_no, Semester, Year → Days_hours,
Room_no, No_of_students, Instructor_id
Room_no, Days_hours, Semester, Year → Instructor_id,
Course_no, Sec_no
Identify the candidate key(s) for R.
If R is not in BCNF, decompose it into a set of BCNF
relations that preserve the dependencies.
6. Consider the bank database consisting of the following
tables:
5
marks
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street,
customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)
In case you cannot type the usual RA Greek letters easily,
you should use the following convention shown below.
Operator parameters should be enclosed in square
brackets.
π ( σ (courses)) can be written as P[title] ( title points = 6
Submit Assignment
23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 5/7
S[points = 6] (courses))
Write down the Relation Algebra (RA) expressions for the
following queries:
Find out the customer names who live in Sydney city
Find the names of all customers who have a loan at the
Redfern branch but do not have an account at any
branch of the bank.
7. Which of the following schedules is conflict serializable?
For each serializable schedule, determine the equivalent
serial schedules.
6
marks
a. r1(X); r3(X); w1(X); r2(X); w3(X);
b. r1(X); r3(X); w3(X); w1(X); r2(X);
8. Assume there are 4,000,000 records in a table and each
record is 100 bytes long, including a primary key tuplekey
of 4 bytes, an attribute attr of 4 bytes, and other attributes.
Assume that each page is 8K bytes, of which 150 bytes
are reserved for header and array of record pointers.
4
marks
How many records can we store per page?
How many pages required to store the table?
9. Assume that we have two tables Employee and
Department. Employee table contains 2000 records
scattered in 200 blocks. Department table contains 20000
records distributed in 500 pages.
3
marks
Calculate the I/O cost of joining these two tables using
Submit Assignment
23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 6/7
Academic honesty
While the University is aware that the vast majority of students and staff act ethically and honestly, it
is opposed to and will not tolerate academic dishonesty or plagiarism and will treat all allegations of
dishonesty seriously.
Further information on academic honesty, academic dishonesty, and the resources available to all
students can be found on the academic integrity pages on the current students website:
https://sydney.edu.au/students/academic-integrity.html (https://sydney.edu.au/students/academic-
integrity.html) .
Further information for on research integrity and ethics for postgraduate research students and
students undertaking research-focussed coursework such as Honours and capstone research
projects can be also be found on the current students website:
https://sydney.edu.au/students/research-integrity-ethics.html
(https://sydney.edu.au/students/research-integrity-ethics.html) .
Compliance statement
In submitting this work, I acknowledge I have understood the following:
I have read and understood the University of Sydney’s Academic Honesty in Coursework
Policy 2015 (https://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2012/254&RendNum=0) .
The work is substantially my own and where any parts of this work are not my own I have
indicated this by acknowledging the source of those parts of the work and enclosed any quoted
text in quotation marks.
The work has not previously been submitted in part or in full for assessment in another unit unless
I have been given permission by my unit of study coordinator to do so.
Nested loop join algorithm.
10.
Assume that we have two tables Employee and
Department. Employee table contains 2000 records
scattered in 200 blocks. Department table contains 20000
records distributed in 500 pages.
3
marks
Calculate the I/O cost of joining these two tables using
Block-nested loop join algorithm.
Submit Assignment
https://sydney.edu.au/students/academic-integrity.html
https://sydney.edu.au/students/research-integrity-ethics.html
https://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2012/254&RendNum=0
23/11/2021, 14:34 Practice Final Exam
https://canvas.sydney.edu.au/courses/35658/assignments/315591 7/7
The work will be submitted to similarity detection software (Turnitin) and a copy of the work will be
retained in Turnitin’s paper repository for future similarity checking. Note: work submitted by
postgraduate research students for research purposes is not added to Turnitin’s paper repository.
Engaging in plagiarism or academic dishonesty in coursework will, if detected, lead to the
University commencing proceedings under the Academic Honesty in Coursework Policy 2015
(https://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2012/254&RendNum=0) and the
Academic Honesty Procedures 2016 (http://sydney.edu.au/policies/default.aspx?
mode=glossary&word=Academic+honesty) .
Engaging in plagiarism or academic dishonesty in research-focussed work will lead to the
University commencing proceedings under the Research Code of Conduct 2013
(https://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2013/321&RendNum=0) and the
Academic Honesty Procedures 2016 (http://sydney.edu.au/policies/default.aspx?
mode=glossary&word=Academic+honesty) .
Engaging another person to complete part or all of the submitted work will, if detected, lead to the
University commencing proceedings against me for potential student misconduct under the
University of Sydney (Student Discipline) Rule 2016
(http://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2017/441&RendNum=0) .
Choose a submission type.
Upload Studio More
Webcam Canvas Files
Choose a file to upload
Submit Assignment
https://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2012/254&RendNum=0
http://sydney.edu.au/policies/default.aspx?mode=glossary&word=Academic+honesty
https://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2013/321&RendNum=0
http://sydney.edu.au/policies/default.aspx?mode=glossary&word=Academic+honesty
http://sydney.edu.au/policies/showdoc.aspx?recnum=PDOC2017/441&RendNum=0