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.
Al's Appliance and Sport
The Everything Shop
Johnson's Department Store
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.
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
- The following molecule can be classified as A. S B. meso C. R D. cis 2. Sn2 reactions invert the stereochemistry of the products. True False Secondary alkyl
- What are the pros and cons of insisting that the entire family attend the initial consultation? What would you do if you learned that some of the family
- I need help finding a current event that is relatable to western history between 1500's-late 1800's, so that I can write short presentation on.