2002 Fall Assignment Prof. Sin-Min LeeDue date: October 8,2002Reminder: 2nd Midterm Oct 17,2002 Consider the relations Students, Faculty, Courses, Rooms, Enrolled, Teaches and Meets_inin the following relational schema: Students(sid: string, name: string, login: string, age: integer, gpa: real) Faculty (fid: string, fname: string, sal: real) Courses(cid: string, cname: string, credits: integer) Rooms(rno: integer, address: string, capacity: integer) Enrolled (sid: string, cid: string, grade: string) Teaches( fid: string, cid: string) Meets_In( cid: string, rno: integer, time: string)1. List all the foreign constraints among these relations.2. Give an example of a (plausible) constraints involving one or more of these relations thatis not a primary key or foreign key constraint. 2. Discuss each of the following concepts in the context of the relational data model:(a) relation(b) attribute(c) tuple (d) degree and cardinality.3.Discuss the differences between the candidate days and the primary key of a relation. Explain what is meant by a foreign key. How do foreign keys of relations relate to candidate keys?4.Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.5. Is the result of a query to a relational database a relation? 6..Is it possible to combine several relational algebra operation into a complex expression? 7.Can both select and project operations decrease number of tuples? 8.What are set operations in relational algebra? 9. If the relation A has 40 tuples and the relation B has 20 tuples, what is the size of the cartesian product?10..For each of the following operators - union, cartesian product, substraction, natural(eqvi) join, semi join and division - decide whether: Is it commutative: R1 x R2 = R2 x R1 Is it associate: (R1 x R2) x R3 = R1 x (R2 x R3) Does it have a neutral relation, that is a relation Ro such that for every relation R, R x Ro = R Is there a funtion F whose domain and range are relations, such that for every R, R x F(R) = R Explain your answers shortly. Formal proofs are not required.11. Given R(A B C D) as follows, R( A B C D) 1 2 3 4 2 2 3 5 3 2 5 1 1 2 5 6 2 2 4 1 4 2 5 4What is the functional dependency graph.12. Given the following relational databaseLIVES(person-name, street, city)WORKS(person-name, company-name, salary)LOCATED-IN( company-name, city)MANAGES(person-name, manager-name)Express the following queries in relational calculus(a) Find all people who earn less than $50,000.(b) Find all people who live in the same city as the company they work for.(c) Find all people who live in the different city as the company they work for.(d) List all people who live in the same city and on the same street as their manager. (e) List all people who live neither in San Francisco nor Redwood City.13. Given the relational scheme R( A B C D E F) and functional dependenciesFD1. A B CFD2. D E B FFD3. B D C EFD4. C B (1) Find the closure of {A C}(2) Decide whether {A, C, D} is a key or
View Full Document
Unlocking...