5/1/2020 COMP3311 20T1 Final Exam
https://cgi.cse.unsw.edu.au/~cs3311/20T1/sample-exam/index.html 1/5
COMP3311 20T1 Database Systems
The University of New South Wales
COMP3311 Database Systems
Final Exam 20T1 (SAMPLE ONLY)
Thursday 7 May 2020
1. Time Allowed: 3 hours
2. To be completed between:
9:00am Thu 7 May 2020 - 9:00am Fri 8 May 2020 (AEST)
3. Total number of questions: 9
4. Total marks available: 50
5. Questions are not of equal value.
6. Marks are shown on each question.
7. Carefully read the notes below before you start the exam.
By submitting the exam answers via WebCMS or give, you declare that all of the work
submitted for this exam is your own work, completed without assistance from anyone else.
Please refer to the Student Conduct web site for details.
Notes
1. General Instructions:
Answer all questions.
Questions are not worth equal marks.
Questions may be answered in any order.
You may create additional views to help formulating your queries, if needed, but
you are not allowed to create any tables.
During the 3-hr Exam, you must not:
access any of your own files
access any web pages except the standard documentation in this course.
During the entire 24-hr period from 9:00am Thu 7 May 2020 (AEST), you must not:
communicate with other students in any way
Your answers must be submitted using give or via WebCMS
If you have any clarification questions between AEST 9:00am-5:00pm 7th May,
2020 (we may be unavailable outside this period), please email
via an UNSW email account.
Please fill in your answers in the supplied template ( ans.sql ). The comments in the
template file indicate where you can fill in the answers. If there are extra files (e.g.,
drawings) needed to be submitted, the corresponding questions will have instructions
specified. SQL queries will be auto-marked by using sqlite3 installed on CSE linux
machines, on a database with the same schema with data possibly modified. You can
only receive marks for correctly-working queries that loads with no warnings.
2. Submission:
You can submit your exam solution either using:
5/1/2020 COMP3311 20T1 Final Exam
https://cgi.cse.unsw.edu.au/~cs3311/20T1/sample-exam/index.html 2/5
i) WebCMS: Login to Course Web Site > Exam > Final Exam > Final Exam Paper >
Make Submission > upload required files > [Submit]
Or:
ii) The give command: give cs3311 exam ans.sql er.pdf
Required Files: ans.sql er.pdf
Deadline: Friday 8 May 2020 at 9:00am (AEST)
No late submissions will be accepted.
3. Downloads:
Downloads: exam.tgz or exam.zip
Both .tgz and .zip files contain the same material.
Each archive contains the sqlite3 IMDB database dump exam.db, plus the answer
template file called ans.sql
4. How to Start:
read these notes carefully and completely
download one of the archive files above
unpack the downloaded file
get familiar with the schema and data by exploring and querying the provided
database using the command: sqlite3 exam.db
the schema in exam.db is identical to the database used in Assignment 2 that you
should be familiar with
read ans.sql and identify where you can fill in your answer for each question
attempt the questions and fill in the answers in ans.sql
you are allowed to create additional views to help to formulate your queries if
needed, but you are not allowed to create any extra tables
login to grieg or a CSE linux machine, and test your ans.sql
submit all the Required Files via WebCMS3 (or give) as described above
End of Notes
Exam Questions
Question 1 (5 marks)
Given the provided exam.db, write an SQL query to find the titles and years of movies
with IMDB score of at least 8.5.
Instructions:
Your answer will be expressed as a view with its name and arguments already
defined in ans.sql
5/1/2020 COMP3311 20T1 Final Exam
https://cgi.cse.unsw.edu.au/~cs3311/20T1/sample-exam/index.html 3/5
Question 2 (5 marks)
Given the provided exam.db, write an SQL query to find the titles and years of movies
acted by James Franco.
Instructions:
Your answer will be expressed as a view with its name and arguments already
defined in ans.sql
Question 3 (5 marks)
Given the provided exam.db, write an SQL query to determine the number of movies
from year 2010.
Instructions:
Your answer will be expressed as a view with its name and arguments already
defined in ans.sql
Question 4 (5 marks)
Given the provided exam.db, write an SQL query to determine the number of movies with
no director information.
Instructions:
Your answer will be expressed as a view with its name and arguments already
defined in ans.sql
Question 5 (4 marks)
Consider the following (slightly unusual) definition for a table of enrolment information in
a student information system:
create table Enrolments (
student_id integer,
course_code char(8),
semester char(4),
prac_mark integer,
exam_mark integer,
final_mark integer,
grade char(1),
primary key (student_id, course_code, semester)
);
The table currently has no constraints (apart from the primary key) to ensure that the
attributes have sensible values. Add constraints to ensure that each of the following
conditions is satisfied:
a. student IDs are 7-digit numbers in the range 2000000 to 4999999 inclusive
b. course codes are like UNSW course codes (4 upper-case letters followed by 4
digits)
c. semesters are like UNSW semester codes (YYsN e.g. '13s1', '00s2', '05x1',
'07x2')
5/1/2020 COMP3311 20T1 Final Exam
https://cgi.cse.unsw.edu.au/~cs3311/20T1/sample-exam/index.html 4/5
d. the prac mark must be in the range 0 to 50 inclusive
Instructions:
Please modify the dummy placeholder REPLACE ME of the corresponding answer
in ans.sql
Question 6 (8 marks)
Consider a relation R(A,B,C,D). For each of the following sets of functional
dependencies, assuming that those are the only dependencies that hold for R, list all of
the candidate keys (separated by commas) for R.
a. C -> D, C -> A, B -> C
b. B -> C, D -> A
c. ABC -> D, D -> A
Instructions:
Please modify the dummy placeholder REPLACE ME of the corresponding answer
in ans.sql
Question 7 (8 marks)
Consider a relation R(A,B,C,D). For each of the following sets of functional
dependencies, assuming that those are the only dependencies that hold for R, if R is not
already in BCNF, decompose it into a set of BCNF relations (separated by commas). If it
is already in BCNF, just write ABCD as the final relation (i.e., no need for any BCNF
decomposition).
a. A -> BCD
b. ABC -> D, D -> A
Instructions:
Please modify the dummy placeholder REPLACE ME of the corresponding answer
in ans.sql
Question 8 (6 marks)
For each of the following schedules, determine if it is serializable.
a. T1: R(X) W(X) W(Z) R(Y) W(Y)
T2: R(Y) W(Y) R(Y) W(Y) R(X) W(X) R(V) W(V)
b. T1: R(X) R(Y) W(X) W(X)
T2: R(Y) R(Y)
T3: W(Y)
Instructions:
Please modify the dummy placeholder REPLACE ME of the corresponding answer
in ans.sql
5/1/2020 COMP3311 20T1 Final Exam
https://cgi.cse.unsw.edu.au/~cs3311/20T1/sample-exam/index.html 5/5
Question 9 (4 marks)
Draw an ER diagram for the following application from the manufacturing industry:
Each supplier has a unique name.
More than one supplier can be located in the same city.
Each part has a unique part number.
Each part has a colour.
A supplier can supply more than one part.
A part can be supplied by more than one supplier.
A supplier can supply a fixed quantity of each part.
Instructions:
Save your drawing as a file called er.pdf in PDF format.
End of Exam