Homework 2 Solutions -------------------- 1. (a) see http://www.cs.washington.edu/education/courses/444/04wi/hw2/er_diagram.jpg (b) VacationPackage(vacationId, duration, name, price) - key: vacationId CruisePackage(origin, destination, cruiseLine) - key: vacationId (from VacationPackage) TourPackage(guide, location, company) - key: vacationId (from VacationPackage) ResortPackage(hotelName, numStars) - key: vacationId (from VacationPackage) Ship(shipId, maker, numStars) - key: shipId Reserves(reservationId, vacationId, customerId, creditCardNum) - key: reservationId Customer(emailAddr, name, phone) - key: emailAddr Address(custEmailAddr, streetName, streetNum, city, state, zip) - key: custEmailAddr 2. QUESTION: Given below is the set F of functional dependencies for the relational schema R = {A, B, C, D, E, F, G, H, I, J}. H, D -> A (1) D -> E, F, G (2) H, D, B -> C, J (3) H -> I, J (4) A -> B, C (5) (a) The minimal key of this relation is {H, D}. (b) What are all the nontrivial functional dependencies that follow from the given functional dependencies? H, D -> E, F, G (6) H, D -> A, E, F, G (7) H, D -> I, J (8) H, D -> A, E, F, G, I, J (9) H, D -> A, E, F, G, I, J, B, C (10) D -> E (11) D -> F (12) D -> G (13) H, D, B -> C (14) H, D, B -> J (15) H -> I (16) H -> J (17) A -> B (18) A -> C (19) H, D, B -> C, J, I (20) (c) Decompose the relation into a collection of relations that are in BCNF. R = {A, B, C, D, E, G, H, I, F, J} R => R1 and R2 by (2) R1 = {A, B, C, D, H, I, J} key: {H, D} R2 = {D, E, F, G} key: {D} R1 => R3 and R4 by (4) R3 = {A, B, C, D, H} key: {H, D} R4 = {H, I, J} key: {H} R3 => R5 and R6 by (5) R5 = {A, D, H} key: {H, D} R6 = {A, B, C} key: {A} Result: R2 = {D, E, F, G} key: {D} R4 = {H, I, J} key: {H} R5 = {A, D, H} key: {H, D} R6 = {A, B, C} key: {A} 3. QUESTION: Given below is the set F of functional dependencies for the relational schema R = {F, T, D, N, S}. F -> D (1) D, T -> F (2) F, N -> S (3) (a) A minimal key of this relation is {F, T, N} (another is {D, T, N}). (b) What are all the nontrivial functional dependencies that follow from the given functional dependencies? F, N -> S, D (4) (c) Decompose the relation into a collection of relations that are in BCNF. Is your decomposition information preserving? R => R1 and R2 by (1) R1 = {F, T, N, S} key: {F, T, N} R2 = {F, D} key: {F} R1 => R3 and R4 by (3) R3 = {T, N, F} key: {F, T, N} R4 = {F, N, S} key: {F, N} Result: R2 = {F, D} key: {F} R3 = {T, N, F} key: {T, N} R4 = {F, N, S} key: {F, N} No, the decompositon is not information preserving because it violates the functional dependency D, T -> F (d) Now decompose this relation into a collection of relations that are in 3NF. R => R1 and R2 by (3) R1 = {D, T, N, F} key: {F, T, N} R2 = {F, N, S} key: {F, N} Result: R1 = {D, T, N, F} key: {F, T, N} R2 = {F, N, S} key: {F, N} 4. (a) Yes. 3NF is less constraining than BCNF because it also allows functional dependencies whose right side is a part of a key. (b) No. BCNF is more restrictive than 3NF because it does not allow functional dependencies whose right side is a part of a key. Student(, FName, LName, Class) Professor(, FName, LName, Office, Salary) Section(, Year, Prof_SSN) Transcript(, Grade) 5. Create View a. Specify a view that has the name (first and last) of all students who have ever passed (got a grade of 2.0 or higher) 'CSE444'. ANSWER: CREATE VIEW Passed_444 AS SELECT FName, LName FROM Student AS S, Transcript AS T WHERE S.SSN = T.SSN AND T.CourseId = 'CSE444' AND T.Grade >= 2.0 b. Is your view updatable? Why or why not? ANSWER: No, the big clue that it is not updatable is that there is more than one relation in the from clause. Furthermore, FName and LName is far from enough information to fill in new tuples in both underlying tables (esp. since neither of these are keys to Student and Transcript). 6. Answering Queries with Views a. QUERY1. SELECT DISTINCT FName, LName, Count(*) FROM Professor, Section WHERE Professor.SSN = Section.Prof_SSN GROUP BY LName, FName VIEW1. CREATE VIEW NumClassesTaught AS SELECT DISTINCT FName, LName, Count(*) FROM Professor, Section WHERE Professor.SSN = Section.Prof_SSN AND Section.Year > 1990 GROUP BY LName, FName ANSWER: d. No. Because View1 does not involve classes that took place before 1990. b. QUERY2. SELECT DISTINCT FName, LName, Year FROM Professor, Section WHERE Professor.SSN = Section.Prof_SSN AND Section.CourseId = 'CSE444' VIEW2. CREATE VIEW ClassesTaughtByProf AS SELECT DISTINCT FName, LName, CourseId FROM Professor, Section WHERE Professor.SSN = Section.Prof_SSN ANSWER: No. Year is not kept in View2 so cannot be used in to project out year in Query2. c. QUERY3. SELECT Professor.SSN FROM Student, Professor, Section, Transcript WHERE Student.SSN = 711711777 AND Transcript.SSN = Student.SSN AND Transcript.CourseId = Section.CourseId AND Transcript.Quarter = Section.Quarter AND Professor.SSN = Section.Prof_SSN VIEW3. CREATE VIEW StudentsTaughtByProf AS SELECT Student.SSN As Student, Professor.SSN AS Professor, Transcript.CourseId AS Course, Transcript.Quarter AS Quarter FROM Student, Professor, Section, Transcript WHERE Transcript.SSN = Student.SSN AND Transcript.CourseId = Section.CourseId AND Transcript.Quarter = Section.Quarter AND Professor.SSN = Section.Prof_SSN ANSWER: Yes: SELECT Professor FROM StudentsTaughtByProf WHERE Student = 711711777