BYU Student Author: @Boston
Reviewers: @DylanKing, @TylerBooth, @Christian, @Alex_Garrett
Estimated Time to Solve: 25 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
You are a junior associate at Smarty’s School Supplies that offers bulk supplies ordering. Smarty’s has an audit scheduled next week and the auditor has asked that the databases be in tip top shape when he arrives! This means that all of the databases in Smarty’s system should be in third normal form (this link explains what 3NF means!). Your boss has tasked you with a portion of invoices from the month of September and has asked you to find a way to convert the dataset into smaller tables that can easily reference each other. Good luck!
Instructions
First open the data file and determine what the “key” should be for each table; or in other words, what can uniquely identify each row of data. There should not be any repeat values as primary keys except for the master transaction file which should reference other tables. Make sure to split up the customer’s first and last name into two separate columns as well as the different parts of the address. After you make the four tables, output each one to a separate excel file. Then, upload the four files and output so each excel file is a separate sheet in ONE excel file. Go to this link for help on this complex step.
Data Files
Suggestions and Hints
Try splitting the data into four separate groups: Customer, Product, Invoice, and Invoice Item (the master transaction file). Text-to-column can help split the data. If the second part of the solution brings errors, try using four containers to split the solution into parts to sequentially upload the sheets into one file. The “output option” in the output tool should be “Overwrite sheet or Range”.
Solution
