CSEP 544 Homework 2

Objectives:

To gain experience with ER Diagrams, normalization and views.

Related Reading:

Chapter 2, 3.6, 6.7

Number of points:

135

Due date:

Monday, April 26th, 2004 5:45 pm

Turn-in:

Turn in a zip archive called solution.zip using the turn-in web form. The archive should contain the written answers to questions 1.b-6 in a text file called solution.txt, and the diagram for 1.a in PDF, PostScript, JPG or PNG format. Alternatively, you may hand in a printout at the beginning of lecture.

Homework Problems:

For the decomposition problems, please give all intermediate steps and what functional dependency caused you to take that intermediate step.

1. ER Diagram -> Schema [20 points]

Assume you have been hired to design a database for the following scenario. You may add any necessary attributes, but overall try to keep your design simple.

  1. Design an E/R schema diagram for this database using as much of the E/R model as appropriate.
  2. Map the schema that you designed above to relations. For each relation, give all attributes and keys.
Scenario: Your friend has decided to form a website company that sells vacation package specials. The website will sell discount vacation packages for cruises, tours and resorts. A customer should be able to search for vacation packages by price, name and duration. Additionally, for cruise packages, the customer should be able to find out the origin, destination, and cruiseline. Also associated with each cruise is the ship the cruise will take place on. Your friend wants his customers to be able to find out the maker of the ship, the number of stars (the rating) of the ship and when the ship was built. Before a customer decides to book a tour package off of this website, they should be able to find out the location of the tour, the name of the tour guide and the tour company. Similarly, if a customer decides to book a resort package through this website, they should be able to find out the hotel name and the number of stars (the rating) of the hotel. Once a customer has decided on a vacation package, the can make a reservation with a credit card number. However, before making a reservation, the customer must register with the website by giving their name, address, email address and phone number. A customer's address consists of a street name, street number, a city, a state and a zip code (assume an address can only be associated with one customer).

2. Functional Dependencies and BCNF [30 points]

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
D -> E, F, G
H, D, B -> C, J
H -> I, J
A -> B, C

  1. Find a minimal key for this relation.
  2. What are all the nontrivial functional dependencies that follow from the give functional dependencies?
  3. Decompose the relation into a collection of relations that are in BCNF.

3. Functional Dependencies and 3NF [30 points]

Given below is the set F of functional dependencies for the relational schema R = {F, T, D, N, S}.
F -> D
D, T -> F
F, N -> S

  1. Find a minimal key for this relation.
  2. What are all the nontrivial functional dependencies that follow from the given functional dependencies?
  3. Decompose the relation into a collection of relations that are in BCNF.
  4. Now decompose this relation into a collection of relations that are in 3NF.

4. Relationship between BCNF and 3NF [5 points]

  1. If a schema is in BCNF is it also true that it is in 3NF? Explain why or why not in a sentence or two.
  2. If a schema is in 3NF is it also true that it is in BCNF? Explain why or why not in a sentence or two.

5. Specifying/Updating Views [20 points]

Use the following schema to answer the following questions:

Student(SSN, FName, LName, Class)
Professor(SSN, FName, LName, Office, Salary)
Section(CourseId, Quarter, Year, Prof_SSN)
Transcript(SSN, CourseId, Quarter, Grade)
This schema is part of a database schema for a university. Each Student is identified by their social security number and the database includes their first name, last name and class standing. Each Professor is identified by their social security number and the database includes their first name, last name, their office number and salary. The Section table has information about one section of a class. Each Section is uniquely identified by the course id and the quarter (assume a course cannot have more than one section per quarter and a quarter is identified in YYQQ form where YY are the last two digits of the year and QQ is the quarter within the year, e.g. '04WI'). Each Section also includes the year the section took place in and the social security number of the professor that taught this section. Lastly, Transcript associates a student with a section and contains the grade the student made in that section.

  1. 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'.
  2. Is the view you specified in part (a) updatabale? Why or why not?

6. Querying Views [30 points]

For the following 3 query-view pairs, consider whether it is possible to use the view to answer the query. If yes, show the query that uses the view. If not, explain why not.
  1. 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

  2. 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

  3. 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