Question

Chapter 6 Homework Answer the questions below Note In drawing the diagrams, use the textbook notation . In drawing the diagram, you can use any (diagraming) software, such as Draw.io, Microsoft Visio, LucidChart, or even Microsoft Word. Copy and paste the diagram to Microsoft Word or convert them PDF and Submit the Word file or PDF file o o Include diagram ONLY, not entire software screen Your submission should be readable . Put your name on your document Due date: March 31 (Sunday) 11:59PM Eastern Time . (Questions) Order ID OD100 OD100 OD100 OD213 OD121 OD121 Order Date 09/08/2000 09/08/2000 09/08/2000 01/01/2001 12/20/2000 12/20/2000 Product ID E34T098 N78S571 B66K881 B66K881 D87D729 E34T098 Product Desc Coffee Table Nightstand Bookcase Bookcase Drawer Dresser Coffee Table Unit-Price S175.00 S150.00 S200.00 S200.00 S225.00 S175.00 ntit 1) 2) In what normal form is this table? Describe the insertion, update, and deletion anomalies of the table by providing example (that is, the problems Refer page 209 for the anomalies explained with example. Your answer should be similar to them. . Yours looks like the one in Figure 6.3. Include both diagram (top portion in Figure 6.3) and textual occurred when delete, update, insert record(s)). . 3) Draw its dependency diagram and identify all dependencies. notation (bottom portion in Figure 6.3) for each identified dependency. But you don't have to include relational schema, that is, INF (PROJ _NUM, EMP NUM, PROJ_NAME.......). 4) Normalize the table so that there are no more partial and/or transitive dependencies, and draw the new dependency diagrams for the normal form. Yours looks like the one in Figure 6.5. Again include both diagram and textual notation. . Customer ID Name 8023 9167 7924 6837 8596 7018 Anderson Bancroft Hobbs Tucker Eckersle Arnold Region South West South Fast West North Smith Hicks Hernandez Hicks Faulb 1) 2) In what normal form is this table? Draw its dependency diagram and identify all dependencies. . Yours looks like the one in Figure 6.3. Again include both diagram and textual notation. 3) Normalize the table so that there are no more partial and/or transitive dependencies, and draw the new dependency diagrams for the normal form. Yours looks like the one in Figure 6.5. Again include both diagram and textual notation.

Answer 1

Normalization : Normalization is process of decomposing of large and complex table in simple and smaller form.Before normalization data is row data or data in textfile.Normalization helps to reduce redundancy and avoid inconsistancy of data.

Question 1 :

1. Given Order_Product table is in the First Normal form (1NF) because all the columns in the table are automic in nature thus means no column contains multiple values and each row is having only one column also no duplicate columns are there.

2.Update anomaly : Here in the table couple of rows in tables having duplicate values like Order_ID but want to update a record with Order_ID OD100 then three rows will get updated.

Delete Anomaly :Likewise if want to delete a record with Order_ID100 three rows will get deleted.Same case happen with OD121.

Insert Anomaly : There is no specific way how Order_ID will generate also if a customer want to order all the items then there is problem duplicate rows exits.

Below is the normalization of this table in 3NF.

First Normal Form (1NF):

In First Normal Form (1NF) all the columns in the table should be atomic in nature that means each column should contain one value & each row should contain the same number of columns. Repeating groups or duplicate columns are not allowed in first normal form.

Above table is in the First Normal Form (1NF). Because each row have only one value and no repeating group of column are in the table.

Second Normal Form (2NF):

Second Normal Form (2NF) should full fill requirements of first normal form (1NF) and all the columns in the table should functionally depend upon primary key column in the table. Partially dependencies are not allowed in second normal form

Here Product_Desc and Unit_Price and depends upon product_ID hence need to separate these into another table.

1.Table Name : Product

Schema : Product_ID -Primary key , Product_Desc, Unit_Price

Below is the table data

Product_ID Product_Desc       Unit_Price

E34T098    Coffee Table         $175.00

N78S571    Nightstand $150.00

B66K881    Bookcase    $200.00

D87D729    Drawer Dresser    $225.00

2.Table Name : Order

Schema : Order_ID-Primary key , Order_Date

Below is the table data

Order_ID    Order_Date

OD100        09/08/2000

OD213        01/01/2001

OD121        12/20/2000

Third Normal Form (3NF):

In Third Normal Form (3NF), It should full fill all the requirements of second normal form and all the columns in the table should be depend upon primary key column. Transitive dependency are not allowed in the 3NF. Transitive dependency means non key column depends upon non key column in the table.

Here primary key of one table is used as foreign key in another table to form a relation as shown below.

Table Name : OrderDetails

Schema : OrderDetailsID-Primary key , OrderID-Foreign key ,ProductID-Foreign Key , Quantity

Below is the table data for the table OrderDetails

OrderDetailsID     OrderID      ProductID   Quantity

1        OD100        E34T098    2

2        OD100        N78S571    3

3        OD100        B66K881    2

4        OD213        B66K881    1

5        OD121        D87D729    2

6        OD121        E34T098    3

Question 2 :

Given table is in the first Normal Form (1NF)

First Notmal form (1NF) : Given table is in the first normal form because all the columns in the table is automic in nature no duplicated columns are there.

Second Normal Form (2NF) : Here this table needs to separate into two tables because name is depends upon CustomerID and Region depends upon Salesperson hence need to have in separate table so below are the tables in Second Normal Form(2NF).

1.Table Name : Customer

Schema : Customer_ID - Primary key , Name

Below is the table data

Customer_ID       Name

8023 Anderson

9167 Bancroft

7924 Hobbs

6837 Tucker

8596 Eckersley

7018 Arnold

2.Table Name : Salesperson

Schema :SalespersonID-Primary key ,Salesperson, Region

below is the table data

SalespersonID      Salesperson          Region

101    Smith          South

102    Hicks West

103    Hernandez East

104    Faulb North

Third Normal Form (3NF) : Tables in 3NF are used to form a relation that means primary key of one table is used as foreign key in another table as given below.

Table Name : CustomerSales

Schema :CustomerSalesID-Primary Key , Customer_ID-Foreign key , SalespersonID-Foreign key

Below is the table data

CustomerSalesID Customer_ID       SalespersonID

1        8023 101

2        9167 102

3        7924 101

4        6837 103

5        8596 102

6        7018 104