CSE 444 Homework 2

Objectives:
To be able to design E/R diagrams, translate from E/R diagrams to a relational database, to understand functional dependencies and normal forms, and to do updates in SQL.
Reading Assignments:
Chapters: 2, 3.2, 3.3, 3.4
Number of points:
100
Due date:
October 23
Assignment Tools:
SQL server
  1. [15 points]  
    1. Create a relational schema that captures this E/R diagram.
    2. Which relation in your relational schema represents the relationship "insures", in the E/R diagram and why is that your representation?
    3. Compare the representation of the relationships "drives" and "operates" in your schema, and explain why they are different.
    4. What are the keys for each relation in your relational schema?

  2. [10 points]  Design an E/R diagram for geography that contains the following kinds of objects together with the listed attributes:

          - countries
                name
                area
                population
                gdp ("gross domestic product")
          - cities
                name
                population
                longitude
                latitude
          - rivers
                name
                length
          - seas
                name
                max depths

       Model the following relationships between the geographical
       objects:

          - each city belongs to exactly one country
          - each river crosses one or several countries
          - each river ends in another river or in a sea
     

     

  3. [15 points]  Consider the relational schema R(A,B,C,D) and the following set of functional dependencies: S = {A -> B, BC -> A, D -> C}.  For each of the functional dependencies X -> Y below indicate whether it logically follows from S.  If your answer is "yes", you should compute X+ showing that it contains Y.  If your answer is "no" you should give an example of a relational instance for R that satisfies S but does not satisfy X -> Y:
    1. A -> C
    2. BD -> A
    3. CD -> A
    4. BCD -> A
    5. ABC -> D
  4. [20 points] For the following relations and functional dependencies
    1. Decompose the relations, as necessary, into collections of relations that are in BCNF. Show all of your work and explain which dependency violations you are correcting by your decompositions.
    2. Decompose the relations into a collection that follows 3NF instead.
    The relations are:
    1. R(A,B,C,D,E) with functional dependencies BD -> E,  C -> A.
    2. S(A,B,C,D,E) with functional dependencies AB -> E, CD -> A, E -> D.
  5. [20 points] Mr. Frumble designed a simple database to record projected monthly sales in his small store. He never took a database class, so he came up with the following schema:
            Sales(name, discount, month, price)
    He inserted his data into the database, then he realized that there is something wrong with it: it was difficult to update. He hires you as a consultant to fix his data management problems. He hands you his data in this file and tells you: "fix it for me !". Help him by normalizing the his database. Unfortunately you cannot sit down with Mr. Frumble and discuss his application to find out the functional dependencies, so you have to reverse engineer them from his data instance. You should do the following steps:
    1. Create a database in SQL server and load the data in a table. There are many ways you can do this. The fastest is: run "Enterprise Manager" (not "Query Analyzer"), click on Tools->Data Transformation Services->Import Data. This brings up a wizard. Just follow the instructions. On the first screen select the "Data Source" to be a text file ("txt" - at the bottom of the scroll list), on the next screen don't forget to check the box saying that the first line is the schema.
    2. Find all functional dependencies in the database. This is a reverse engineering task, so expect to proceed in a trial and error fashion. Search first for the simple dependencies, say name -> discount then try the more complex ones, like name, discount -> month, as needed. To check each functional dependency you have to write a SQL query. 
      For this point you should turn in all functional dependencies that you found, the SQL query that discovered them and its result. You should also turn in the other functional dependencies that you tried and that do not hold, together with the SQL queries that proved that they don't hold: try to be clever in order not to try too many dependencies, but don't miss potential relevant dependencies.
    3. Decompose the table in BCNF, and create SQL tables for the decomposed schema. Create keys and foreign keys where appropriate. For this point turn in the SQL commands for creating the tables.
    4. Populate your BCNF tables from Mr. Frumble's data. For this you need to write SQL queries that load the tables you created at point iii from the table you created at point i. Turn in the SQL queries that load the tables.
  6. [15 points]
    We say a set of attributes X is closed (with respect to a given set of functional dependencies) if X+=X. Given the closed attribute sets, this gives us some information on the underlying functional dependencies.

    Consider a relation with schema R(A,B,C,D) and an unknown set of functional dependencies. For each closed attribute set below, give a set of functional dependencies that is consistent with it.

    1. All sets of attributes are closed.
    2. The only closed sets are {} and {A,B,C,D}.
    3. The only closed sets are {}, {A,B}, and {A,B,C,D}.
  7. [5 points] Please answer the following questions below. Please answer them online, at  http://iinetsrv.cs.washington.edu/CSE444Feedback/Start.aspx
    1. How long did it take you to complete this assignment?
    2. What did you like the best about this assignment?
    3. What did you like the least about this assignment?
    4. What helped you learn the best in this assignment?
    5. What distracted from your learning in this assignment?