TUT8 - tutorial PDF

Preview

Summary

tutorial...

Description

Database Systems Tutorial – 8(Week 19-24 Sept) Question 1. Consider the following schema: Tool(tid, brand, cost) Jobsite(location, compensation, task) Toolbox(tbid, location) – location is a foreign key to Jobsite Holds(tbid, tid) – tbid is a foreign key to Toolbox, tid is a foreign key to Tool. A. Which of the following would be an equivalent Relational Algebra query for following SQL? SELECT T.tid FROM Tool T, Holds H, Toolbox B, Jobsite J WHERE T.tid = H.tid AND H.tbid = B.tbid AND B.location = J.location AND J.task = ’Plumbing’

B. Interpret the following query written in Relational Algebra query and rewrite in SQL ?

Question 2. Consider the following relational schema. An supplier can supply more than one part and one part can be supplied by more than one supplier. Suppliers (sid: integer, sname: string, address: string) Parts (pid: integer, pname: string, color: string) Catalog (sid: integer, pid: integer, cost: real) 1. 2. 3. 4. 5.

Find the IDs of suppliers who supply some red or green part. Find the IDs of suppliers who supply some red part or are based at 21 George Street. Find the names of suppliers who supply some red part or are based at 21 George Street. Find the IDs of suppliers who supply some red part and some green part. Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid. 6. Find the IDs of suppliers who supply only red parts. 7. Find the IDs of suppliers who supply every part.

Question 3 . Consider the following schema: Vehicle ( VIN, model, year ) Driver ( license, name, age ) Insured ( license, VIN, premium ) The key fields are underlined: VIN is the key for Vehicle, license is the key for Driver, and VIN and license together form the key for Insured. Also, Insured. license is a foreign key that references Driver.license, and Insured.VIN is a foreign key that references Vehicle.VIN. A. Write the following queries in Relational Algebra . 1. Find the VINs of vehicles that are insured for a driver between 20 and 30 years of age. 2. Find the VINs of vehicles that are insured for some driver under 25 years of age and another driver who is over 50 years of age. 3. Some vehicles are operated by more than one driver, and a different premium may be charged for each driver for the same vehicle. Find pairs of license numbers such that the driver with the first license number gets charged a higher premium for the same vehicle compared to the driver with the second license number. 4. Find the number of different models of vehicle in each year. B. For each one of the Relational Algebra (RA) expressions that follow, state what they compute....