(Solved) : Mr Frumble Great Childrens Character Always Gets Trouble Designed Simple Database Record P Q37202952 . . .
- Mr. Frumble, a great childrens character that alwaysgets into trouble, designed a simple database to record projectedmonthly sales in his small store. He came up with the followingschema:
Sales(name, discount,month, price)
He inserted his data into thedatabase, then he realized that there is something wrong with it:it was difficult to update! He hires you as a consultant to fix hisdata management problems. He gives you this file,mrFrumbleData.txt, and says: “Fix it for me!”. Help him bynormalizing his database. That will make his updates mucheasier.
Unfortunately, you cannot sit down andtalk to Mr. Frumble to find out what functional dependencies makesense in his business. Instead, you will reverseengineer the functional dependencies from his datainstance. You can do so with the following steps:
- Using a DBMS of your choice (e.g., SQLite or SQL Server),create the Sales table and load the data from the Mr. Frumble’sfile into it. You don’t need to turn in anything for thispart.
- Find all functional dependencies in the database.
This is a reverse engineering task, soexpect to proceed in a trial and error fashion. Search first forthe simple dependencies, such as name → discount. Then, try themore complex ones, such as (name, discount) → month, as needed.(Try to be clever in order not to check too many potentialdependencies, but don’t miss any!)
To check each functional dependency,you will write a SQL query. Your challenge is to write this SQLquery for every candidate functional dependency that you check,such that (1) the query’s answer is always short (no more than tenlines or so) and (b) you can determine whether the FD holds or notby looking at the query’s answer.
For this point you should turn in allfunctional dependencies that you found and, for each of them, theSQL query that discovered it, together with the answer of thequery.
- Decompose the table in BCNF.
Turn in SQL commands to create the newtables, including any appropriate keys.
- Populate your BCNF tables from Mr. Frumble’s data. For this youneed to write SQL statements that fill in the tables you created atpoint iii from the table you created at point i. Each suchstatement can be of the form INSERT INTO X SELECT … FROM Sales…, where X is the name of a BCNF table.
Expert Answer
Answer to Mr. Frumble, a great childrens character that always gets into trouble, designed a simple database to record projected …
OR