Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BUS 440 Database Management

Assignment 3

Use Case:

This assignment will instruct you to design and create a database for a small business, Locklead Solutions. The business performs small home-building and remodeling services. The company does electrical, interior, and exterior work for clients. The company is currently involved with three projects: Highland House, Baker Remodel, and Hew Remodel. They purchase various products and pieces of equipment, such as wire, siding, doors, shingles, electrical panels, etc. from a supplier (often at a discount). Some of their trusted suppliers are NW Electric, EB Supplies, Contractor, Inc., and Interior, Inc.

To help manage the projects, the company owner tracks each project, the homeowner’s name and phone number, the project category on a project equipment list on an Excel spreadsheet. He has hired you to design and build a small database so he can better track his projects, the equipment used on them, and the associated costs from supplier quotes.

Instructions:

1. Using the data in Figure 1, design a database. To do so, look at the data and the use case. What are some business rules?

2. Write out the relational schema for all of the data and the functional dependencies.

3. Normalize to 3NF.

a. Normalize to 1NF. Be sure to use proper primary keys.

b. Normalize to 2NF.

c. Normalize to 3NF.  Assign foreign keys, as appropriate.

4. Names are usually not appropriate, so add a code as a surrogate PK for each table as an improvement.

5. Create an EER in MySQL Workbench to show your final design (model) for the database, including all entities, relationships, and columns. Save the EER as a MySQL Workbench mode (.mwb) file.

6. Build the database and each database table in MySQL from your design. To do so, synchronize your model to forward-engineer your design to your build via MySQL Workbench.

7. Populate the tables with the data from Figure 1. You may import the data from another source, key the data into the MySQL database tables, or use SQL statements to insert the data into tables.

8. Create and run the following SQL programs. Save them in script named LockleadSolutions.sql. Be sure to validate your results using an alternate method. Screenshot your code and answer in this document for each question.

a. What is the total final price for the Highland House project? Be sure to apply the discount to derive the final price (e.g., ExtendedPrice x (1-Discount) = FinalPrice). Do not store FinalPrice in your database. Instead, it will be a derived value from your SQL calculation. Round the result to two decimal places.

b. What the total final price for all items, grouped by category and sorted in descending order of total final price? Be sure to apply the discount to derive the total final price and round the result to two decimal places.

c. List all of the company projects (project name), the owner name and contact information for each project, the equipment (item description) needed for that project, and the total final price (which includes the discount, defined above). Round the result to two decimal places and sort first by project name, and then by item description. (To sort by two fields, just add a comma between both in the ORDER BY clause (e.g., ORDER BY projectname, itemdescription).

d. List the total final price (defined above) grouped by each supplier name. Round the result to two decimal places. Sort the result in descending order by total final price.

e. Add one other query of your choosing. It must use a subquery. 

9. One person per team, upload the following to Moodle in a zip file: 1) this database design document with all answers; 2) the EER file; and 3) the .sql script.

Figure 1: Project Equipment List