[35 points] Mr. Frumble (who is
a great character for small kids that always gets into trouble)
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:
- Create a table on SQL Server and load the data from the
provided file into that table. You will need to do this in two steps:
(a) import the data into a MS Access file, and (b) import from Access
file into SQL Server. The reason you have to take two steps instead of
directly importing from the text file into SQL Server is that the
version of SQL Server we are using is a pre-release version and it
doesn't have all the features enabled in it. The name of the table will
be derived from your SQL server login id. If your login id is foobar, then the
table name *should be* hw2_foobar.
You don't need to turn in anything for this point.
- 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.
- 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.
- 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).