CSE 444 Homework 2

Objectives:
To be able to translate from E/R diagrams to a relational database, to understand functional dependencies and normal forms, and to write XQuery expressions.
Reading Assignments:
Chapters: 2, 3.2, 3.3, 3.4, lecture notes on XML and XQuery
Number of points:
100
Due date:
November 5th, 10:30am
Note: this homework is a good practice for the midterm. Please work on it BEFORE the midterm.
Assignment Tools:
SQL Server, XQuery (Galax)
  1. [10 points] Consider the following E/R diagram:
     
    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. [20 points] Consider the following two relational schemas and sets of functional dependencies:
    1. R(A,B,C,D,E) with functional dependencies D -> B, CE -> A.
    2. S(A,B,C,D,E) with functional dependencies A -> E, BC -> A, DE -> B.
    For each of the two schemas, do the following:
    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.
    For each of the two questions above you have to turn in a description of your decomposition steps. Show: which is the relation that you are decomposing, what functional dependency do you apply, and which are the two resulting relations.

  3. [30 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 his database. Unfortunately you cannot sit down with Mr. Frumble and discuss his application to find out the functional dependencies, as you should normally do, because Mr. Frumble is simply too busy. So you have to reverse engineer the functional dependencies from his data instance. You should do the following steps:

    1. Create a table in SQL server and load the data in the table. You don't need to turn in anything for this point.

    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. Your challenge is to write this SQL query for every candidate functional dependency that you check, such that (a) the query's answer is always short (say: no more than ten lines or so), and (b) you can determine whether the FD holds or not by looking at the query's answer. Try to be clever in order not to check too many dependencies, but don't miss potential relevant dependencies.

      For this point you should turn in all functional dependencies that you found, and for each of them the SQL query that discovered it, together with the answer of the query. any

    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.

      Here, turn in the SQL queries that load the tables, and the tables' contents after loading them (obtained by running SELECT * FROM Table).

  4. [35 points] Consider the XML data instance Mondial, avaialable here (at the bottom of the page). Write XQueries to answer the following questions. In formulating your questions, you need to understand how various elements are nested: e.g. what is under a country, under which element is a city etc. For that it helps if you inspect the DTD (ignore the warning that the data is not valid), or inspect the data directly. For each question below turn in the XQuery, and the result of running the query on the XML data (which should be an XML document).

    1. Retrieve all the names of all cities located in Peru, sorted alphabetically.
    2. Find all countries with more than 20 provinces.
    3. Find all ethnic groups that live in more than more than 10 countries.
    4. Find the countries adjacent to the 'Pacific Ocean' sea.
    5. Find the names of all countries that have at least 3 mountains over 2000m high, and list the names and heights of all mountains in these countries (regardless of their height). Note: the height attribute is in meters, so you don' have to do any conversions.
    6. One user is interested in long rivers. Produce the following view of the data, containing only rivers longer than 2000 (all units are in km), in the format described below:
      • The root element is user and contains several river elements
      • Each rivercontains a name element with the river's name, and several country elements, one for each country through which it flows. (Note: some rivers may not have any country, due to noise in the data. It is OK to include these rivers, even if they look as they flow through no country at all.)
      • Each country element contains only the name of the country (a string).

    For this question you need to run galax: a brief tutorial on how to do this is Here. Alternatively, you can easily install it yourself on any machine you want, from here (get version 0.3.0 for Win32).

  5. [5 points] Please answer the following questions below.
    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?