辅导案例-CSI 2132 A
PRACTICE EXAM Course CSI 2132 A - Databases I Academic year 2019/2020 Semester Winter Instructor Paula Branco Please, read carefully the following instructions: The exam contains 3 different parts (Part I, Part II and Part III). In each part you have 4 questions available. You must select and provide the answers to exactly 2 questions in each part. If you provide answers for more than two questions in any part of the exam only the first 2 questions answered will be considered. The total number of questions to be answered is 6, with 2 mandatory questions in each part. Part I 1. (10%) A worldwide package delivery company named WorldDelivery wants to store information in a database. The database must be able to keep track of: - Customers who ship items and customers who receive items; some customers may do both. The customer ID, name and address must be stored. - Each package must be identifiable and trackable, so the database must be able to store the location of the package and its history of locations. The package weight must also be stored. - Locations include trucks, planes, airports, and warehouses. For each location the database should store the corresponding id, city, country and address. Draw an ER diagram for the database described. 2. (10%) Consider the ER diagram below for modeling an online bookstore. a. (6%) Translate the ER diagram into a relational model. b. (2%) Provide the Tuple Relational Calculus expression for the following query: “List the names of all books published in the year 2020, by ‘Wiley’ publisher.” c. (2%) Provide the Relational Algebra expression for the following query: “Find the names of all books with price lower than $50 that are stored in the warehouse with code ‘556677’.” 3. (10%) Consider a relation with schema R (A, B, C, D) and the set F of functional dependencies: F= { AB→C , C→D , D→A } a. (2%) Find all the candidate keys of R. b. (4%) Indicate all BCNF violations for R and decompose the relations into collections of relations that are in BCNF. c. (4%) Indicate which dependencies, if any, are not preserved by the BCNF decomposition. 4. (10%) An agency called InstantCover supplies part-time/temporary staff to hotels throughout Scotland. The following table lists the time spent by agency staff working at two hotels. The National Insurance Number (NIN) is unique for each employee. Knowing the NIN and the contractNo the company is able to determine the hoursPerWeek that the employee is doing. The NIN also allows the company to know the employee name (eName). Using the hotelNo one can know the hotelLocation. Each contractNo is associated with a particular hotel, which means that by knowing the contractNo the company also knows the hotelNo. a. (3%) Based on the information above, identify the four functional dependencies described. b. (3%) List all candidate keys. c. (4%) Normalize the relation to the third Normal Form and show the resulting relations. Part II 5. (10%) Consider the following relational schemas: professor(profname, deptname) department(deptname, building) committee(commname, profname) a. Explain what data the following queries retrieve: i) (2%) select distinct B.profname from committee A, committee B where A.profname = 'Piper' and B.commname = A.commname ii) (3%) select distinct B.profname from committee B where not exists ( (select commname from committee A where profname = 'Piper') except (select commname from committee A where A.profname = B.profname) ) b. Write the following queries in SQL: i) (2%) “Find all the committees that integrate both professor ‘Piper’ and professor ‘John’.” ii) (3%) “Find the names of all professors who have not offices in any of those buildings that Professor Piper has offices in.” 6. (10%)The following relations keep track of airline flight information: Flights(flno: integer, origin: string, destination: string, distance: integer, departs: timestamp, arrives: timestamp, price: integer) Aircraft(aid: integer, aname: string, cruisingrange: integer) Certified(eid: integer, aid: integer) Employees(eid: integer, ename: string, salary: integer) Write the SQL necessary to implement the following requirements: a. (2%) Add a new attribute to the relation Flights that stores the currency of price. b. (2%) Define CAD as the default value for the attribute currency created. c. (2%) flno, in relation Flights, should be the primary key d. (2%) aid, in relation Certified, should be a foreign key to Aircraft relation e. (2%) Check that all employees have a salary higher than 2000. 7. (10%) Consider again the relations that keep track of airline flight information from que previous question: Flights(flno: integer, origin: string, destination: string, distance, integer, departs: timestamp, arrives: timestamp, price: integer) Aircraft(aid: integer, aname: string, cruisingrange: integer) Certified(eid: integer, aid: integer) Employees(eid: integer, ename: string, salary: integer) a. (2%) Write a SQL query to list in reverse alphabetical order all pilots who are certified to fly some Airbus plane. b. (2%) Write a SQL view multi that displays all pilots and the number of airplanes that they can fly. c. (3%) Using the SQL view multi above write a query for finding the name(s) and salary(salaries) of the pilot(s) who is(are) certified to fly the largest number of planes. d. (3%) Write a SQL query that, for each plane that has at least six pilots, shows the name of the plane and the average salary of the pilots who are certified to fly it. 8. (10%) Consider the following relations: Sailors(sid, sname, rating, age) Reserves(sid, bid, day) Boat(bid, bname, bcolor) Write expressions in Relational Algebra (RA), Tuple Relational Calculus (TRC) or Domain Relational Calculus (DRC) as indicated for the following queries: a. (2%) (RA) List the colors of boats reserved by Albert. b. (2%) (RA) List the id’s of all sailors who have a rating of at least 8 or reserved boat 103. c. (2%) (TRC) List the names and age of all sailors who have a rating lower than 3 d. (2%) (DRC) List the id’s of all boats that were reserved on 2019-04-28. e. (2%) (DRC) List the colors of all boats reserved by Lubber. Part III 9. Select the correct correspondences (2% each correct answer): A - I can use a RAID level 0 technique because 1 - fault tolerance is important for my application and I need to protect my data even if two disks fail at the same time. B - I can use a RAID level 1 technique because 2 - I’m not concerned with losing data. My main goal is to be able to read and write at high speed. C - I can use a RAID level 5 technique because 3 - I have 6 disks available but I need the capacity of 5 of them which means that I can only spare the space corresponding to a single disk to ensure redundancy. D - I can use a RAID level 6 technique because 4 - I only have two disks available which represent more than the double of the capacity that I need for my application and I’m concerned with being able to recover data if necessary. E - I prefer to use a parity-based approach instead of a mirrored approach because 5 - Fault tolerance is important for my application but I don’t have much space available to spare. 10. (10%) Consider the following B+-tree with n=4. a. (5%) Show the B+-tree that results after inserting (in the given order) 56, 50, 75, 87, 48. b. (5%) Using the B+-tree previously obtained in (a.) show the B+-tree that results after delete (in the given order) 50, 24, 65, 93, 75. 11. (10%) Consider the following instance of the relation cars: a. (4%) Construct a bitmap index for the attributes Brand and Color for this table. b. Show how bitmap indices can be used to answer the queries: i. (3%) “Show the Brand of all cars that are not black” ii. (3%) “Give the total number of red Opel cars with a medium risk score.” 12. (10%) Consider the following hash function h(x)=x mod 4 for building an hash index. This function allows you to use 4 different buckets. Assume that each bucket can only contain 3 index entries. a. (5%) Use this function to build the hash index of the following search key values: 2, 4, 6, 12, 13, 16, 20, 24, 28, 40 b. (5%) Given the search key values, is this function a good hash function? Explain your answer.
