(f) (5 points)Would any of your answers above change if you were told that R1.X were a foreign key that refers to R2.A?
Problem 3 (25 points)Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Every employee must be in some department, every department must have at least one employee, every department must have associated finances, and finances must have an associated department.Emp(eid: integer, did: integer, sal: integer, hobby: char(20Dept(did: integer, dname: char(20), floor: integer, phone: char(10Finance(did: integer, budget: real, sales: real, expenses: realSELECT D.dname, F.budget FROM emp E, dept D, finance F WHERE E.did = D.did AND D.did = F.did AND D.floor = 1 AND E.sal >= 65000 AND E.hobby = ’Dancing’ Also, suppose the following information is available:●Employee salaries range from 10,000 to 70,000, employees enjoy 200 different hobbies and the company owns two floors in the building.●There are a total of 50,000 employees and 5,000 departments (each with corresponding financial information).●There are 500 blocks of Emp tuples, 50 blocks of Dept tuples and 40 blocks of Finance tuples.(a) (3 points)Give the expected number of tuples in the result for the following subquery. )) )) ) (b) (6 points)Give the expected number of blocks it would take to store the following intermediate results. For simplicity, assume that all attributes have the same size within and between relations (e.g. that Emp.hobby has the same size as Emp.eid as well as Finance.budget).1.Π dname, did (Emp⨝did(σfloor=1 Dept) )
You've reached the end of your free preview.
Want to read all 6 pages?
- Fall '19
- Relational model, relation