sample-final-b

.pdf

School

University of Alberta *

*We aren’t endorsed by this school

Course

291

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

9

Uploaded by CorporalFoxPerson645

Report
Family Name: ----------------------------------------------------- Given Name: ----------------------------------------------------- Student ID (last 3 digits): ----------------------------------------------------- University of Alberta Faculty of Science Fall CMPUT 291 – A1 File Structures and Data Management Duration: 2 Hours Aids Allowed: a one-page cheat sheet only. Your examination booklet must have 9 pages (including this page). QUESTION VALUE SCORE 1 9 2 16 3 4 4 4 5 12 6 7 8 8 6 13 TOTAL 72 © Davood Rafiei 2023
CMPUT 291 / A1 Final Exam - Fall Page 2 of 9 Question 1 [ 9 marks in total ] TRUE or FALSE: 1.5 marks for each correct answer; 0 mark for each incorrect answer; 0.5 mark if no choice is selected. a) In ER diagrams, a thick bold line from an entity set to a relationship set means every entity in the set must participate in the relationship. ( ) TRUE ( ) FALSE b) In the relational model, every key is a super key but not vice versa. ( ) TRUE ( ) FALSE c) Every relational algebra query can be expressed in relational calculus. ( ) TRUE ( ) FALSE d) For any pair of relations R and S, . ( ) TRUE ( ) FALSE e) Clustered indexes are generally more efficient than unclustered indexes for both searches and updates. ( ) TRUE ( ) FALSE f) The seek time is the time it takes to search through an index for a key. ( ) TRUE ( ) FALSE Question 2 [ 16 marks in total ] Consider the following tables with the primary key of each table underlined, and a few sample tuples shown. The columns cid and rid in bookings are foreign keys referencing customers and resorts respectively. (( R ▹◃ S ) ▹◃ R ) ▹◃ S = R ▹◃ S cid name city country age c10 Davood Edmonton Canada 20 c20 John Victoria Canada 65 cid rid arrival departure c10 r300 2021/12/17 2021/12/27 c20 r200 2021/12/24 2022/01/03 bookings customers
CMPUT 291 / A1 Final Exam - Fall Page 3 of 9 Express the following queries in SQL. In each case, write a single SQL statement. a) [4 marks] Find the name and the city of customers who have booked a resort in Mexico. b) [4 marks] Find the rid of resorts with at least 80% of their bookings made by customers from Canada. c) [4 marks] Find the names of all pairs of different customers from Edmonton such that both customers stay in the same resort and the days of their stays overlap. rid resort city country suites r200 Andaz Guanacaste Costa Rica 80 r300 Dreams Sands Cancun Mexico 60 resorts
CMPUT 291 / A1 Final Exam - Fall Page 4 of 9 d) [4 marks] For each country and city that has a resort, list the country and the city, the number of resorts and the number of resorts with a customer booking from Canada. Question 3 [ 4 marks ] Based on the schema in Question 2, write a relational algebra or a relational calculus query (but not both) to find the rid of resorts that have no booking for customers of age 30 or less. Question 4 [ 4 marks ] Based on the schema in Question 2, write a SQL assertion to enforce that no resort can have more bookings (ignoring the dates) than the number of suites in the resort.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help