Mr. Frumble, a great children’s character that always gets into trouble, designed a simple database to record projected monthly sales in his small store. 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 gives you this file, mrFrumbleData.txt (attached to this assignment), and says: “Fix it for me!”. Help him by normalizing his database. That will make his updates much easier. The attributes fields are delimited by tab characters, rows are delimited by newlines, and the first line contains the headers.

Unfortunately, you cannot sit down and talk to Mr. Frumble to find out what functional dependencies make sense in his business. Instead, you will reverse engineer the functional dependencies from his data instance. You can do so with the following steps:

1. Using a DBMS of your choice (e.g., SQLite or SQL Server), create the Sales table and load the data from the Mr. Frumble’s file into it. You don’t need to turn in anything for this part.

I did it

2. Find all functional dependencies in the database.

I got FDs:

name → price

month → discount

name, discount → price

name, month → discount

name, month → price

month, price → discount

name,discount,month → price

name,month,price → discount

Is this right? if yes, could you please start with question 3?

This is a reverse engineering task, so expect to proceed in a trial and error fashion. Search first for the simple dependencies, such as name → discount. Then, try the more complex ones, such as (name, discount) → month, as needed. (Try to be clever in order not to check too many potential dependencies, but don’t miss any!)

To check each functional dependency, you will write a SQL query. Your challenge is to write this SQL query for every candidate functional dependency that you check, such that (1) the query’s answer is always short (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.

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.

3. Decompose the table in BCNF.

Turn in SQL commands to create the new tables, including any appropriate keys.

4. Populate your BCNF tables from Mr. Frumble’s data. For this you need to write SQL statements that fill in the tables you created at point iii from the table you created at point i. Each such statement can be of the form INSERT INTO X SELECT … FROM Sales …, where X is the name of a BCNF table.

Turn in the SQL statements along with the tables’ contents after loading them. (You can obtain the latter by running SELECT * FROM X.)