View the step-by-step solution to:

Knight Hardware Co. Knight Hardware Co. is a retailer company that sells home hardware to general customers.


1.    Knight Hardware Co.


Knight Hardware Co. is a retailer company that sells home hardware to general customers. The company has decided to develop a database to keep track of parts inventory, customer information, and sales orders.


Before the software development team designs and constructs a relational database, the company was maintaining its data using the following un-normalized data table.


Table 1. Un-normalized order entry data table for Knight Hardware Co.

Customer

Number

Order

Number

Part Number

Customer

Name

City

State

Order

Date

Part

Description

Inventory

On-Hand

Price

Number Ordered

Part Class

Class

Description

148

21608

AT94

Al's Appliance and Sport

Fillmore

FL

10/20/2017

Iron

50

24.95

11

HW

Housewares

21619

DR93

10/23/2017

Gas Range

8

495

1

AP

Appliances

282

21614

KT03

Brookings Direct

Grove

FL

10/21/2017

Dishwasher

8

595

2

AP

Appliances

356

21610

DR93

Ferguson's

Northfield

FL

10/19/2017

Gas Range

8

495

1

AP

Appliances

DW11

Washer

12

399.99

1

AP

Appliances

408

21613

KL62

The Everything Shop

Crystal

FL

10/22/2017

Dryer

12

349.95

4

AP

Appliances

608

21617

BV06

Johnson's Department Store

Sheldon

FL

10/23/2017

Home Gym

45

794.95

2

SG

Sporting Goods

CD52

Microwave Oven

32

165

4

AP

Appliances

21623

KV29

10/21/2017

Treadmill

9

1390

2

SG

Sporting Goods

The team wants to design their relational database by following steps in the data normalization process. Before following the steps, the team found that there are the following associations (i.e. functional dependencies) for the attributes of customers, parts, and orders.


-      Customer Number -> Customer Name

-      Customer Number -> City

-      Customer Number -> State

-      Part Number -> Part Description

-      Part Number -> Inventory On-hand

-      Part Number -> Price

-      Part Number -> Part Class

-      Part Number -> Class Description

-      Part Class -> Class Description

-      Order Number -> Customer Number

-      Order Number -> Order Date

-      Order Number, Part Number -> Number Ordered


Using the un-normalized data table and the functional dependencies of the attributes, do the following tasks.


a.    Convert the un-normalized data table into 1 NF table and explain the conversion process in detail (HINT: you will need to create one table with data).


i.     Using MySQL Workbench, 1) make a database schema named 'knight_hardware_1nf'. 2) Make a table named 'orders' in the database for the 1 NF table. 3) Make sure to properly set a primary key for the 'orders' table. 4) Then insert the given data into the newly created table using SQL insert statements. 5) Then export and submit the database as a script (knight_hardware_1nf.sql) and an E-R diagram (knight_hardware_1nf.png).


b.    Convert the 1 NF table created previously into 2 NF tables (HINT: you will need to create 4 tables with data) and explain the conversion process in detail.


i.     Using MySQL Workbench, 1) make a database schema named 'knight_hardware_2nf'. 2) Make four tables named 'customer', 'part', 'orders', and 'order_item' in the database for the 2 NF tables. 3) Make sure to properly set primary keys for the four tables and foreign keys in the 'orders' and 'order_item' tables. 4) Then insert the given data into the newly created tables using SQL insert statements. 5) Then export and submit the database as a script (knight_hardware_2nf.sql) and an E-R diagram (knight_hardware_2nf.png).


c.    Convert the 2 NF tables created previously into 3 NF tables (HINT: you will need to create 5 tables with data) and explain the conversion process in detail.


i.     Using MySQL Workbench, 1) make a database schema named 'knight_hardware_3nf'. 2) Then make four tables named 'customer', 'part', 'orders', 'order_item', and 'part_class' in the database for the 3 NF tables. 3) Make sure that you properly set primary keys for the five tables and foreign keys in the 'orders', 'order_item', and 'part_class' tables. 4) Then insert the given data into the newly created tables using SQL insert statements. 5) Then export and submit the database as a script (knight_hardware_3nf.sql) and an E-R diagram (knight_hardware_3nf.png).


three sql scripts, three E-R diagrams and a word document for detailed explanation of each conversion process.

part 2


1.    Henry Bookstore.


You have just been named Director of Data Administration of Henry Bookstore. Henry Bookstore maintains a central IS organization with several operational relational databases on several large servers at its headquarters. Each of its branches has a medium-scale server with its own databases that help manage the running of the branch. Real-time transmissions are made via VPN between headquarters and the branches that keep both the headquarters and branch databases constantly up to date.


You have been given a free hand to make a data administration department and supporting database administration department for Henry Bookstore and its IS department.


a.    Design your data and database administration functions. Include their responsibilities and explain how they will add value to the bookstore chain.


b.    make a data dictionary TABLES table and an ATTRIBUTES table and enter data in them for Henry Bookstore database, given in Assignment#7. Your answer should be based on the format shown in Figure 10.6. Use your judgement as to attribute type values, length values, etc.


c.    make a relationships table for the tables and attributes data in 3NF, using the format in Figure 10.7.




 

 

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question