|
|
StudentMajor(ID,major) // ID is key StudentDorm(ID,dorm) // ID is key StudentPhone(ID,phone) // ID is key MajorAdvisor(major,prof) // major is keyand the following view that joins all four tables:
create view AllInfo as (select SM.ID, SM.major, SD.dorm, SP.phone, MA.prof from StudentMajor as SM, StudentDorm as SD, StudentPhone as SP, MajorAdvisor as MA where SM.ID = SD.ID and SD.ID = SP.ID and SM.major = MA.major)
(a) Write a SQL query to find all dorms with at least one student whose major advisor is 'Widom.' Do not use the view, and eliminate duplicate dorms in your result.
(b) Now write the same query using the view only, not the relations over which the view is defined. Again, eliminate duplicate dorms in your result.
(c) Are the queries in parts (a) and (b) equivalent? If so, briefly explain why. If not, give a simple counterexample consisting of four relation instances, the view instance, and the two different query results.
Draw the eight possible entity-relationship (E/R) diagrams for relationship R and entity sets E1, E2, and E3 when multiplicities are specified. (There are eight possibilities because each entity set can either have or not have an arrow pointing to it. Do not consider rounded referential-integrity arrows.) For each E/R diagram, specify a relation that captures the information in R, and underline in the relation a minimal key - a key is minimal if attributes that are not needed in the key are not included.
|