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 [email protected] 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′, ’05×1′, ’07×2′) 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
