24 Write out all the dependencies of 2.3. 2.5 Convert the relation in 23 into 2" Normal Form. Write your answer as relation. 2.6 Convert the relation in 2.5 into 3 Normal Form. Write your answer as a relation. 2.7 What is Normalization? 2.8 Explain Boyce-Codd Normal Form.

Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
icon
Related questions
Question
100%

"2.1,2.2,2.3, and 2.4 are answered"

Introduction

The given problem is related to the normalization process of a database table where the requirement is to identify the normal forms of the given sample table and decompose the table.

 

The normalization is a process to break the table into smaller tables to that:

 

a) There is no redundancy

b) Table is free from insertion, update and delete anomaly.

c) Data in the tables can be handled easily

 

**As per Bartleby guidelines, as the required number of sub-parts to be answered is not mentioned and it is needed to solve first 3 sub-parts. Kindly find the solution to half assignment(first 4 sub-parts).

2.1 Normal form of given table

The given table is in unnormalized form i.e. it is not even in the first normal form of the database normalization.

 

Explanation:

 

a) For a single Invoice number, there are many products added to the table.

 

b) There is no direct primary key that can be used to store the data uniquely.

c) The given table has blank values for Invoice and Client details as previous invoice data is segregated to next records.

 

 

2.2 Steps to convert table from Unnormalized form to 3NF

The normalization process will decompose the table into smaller units to make it normalized based on various conditions.

 

The process to normalize the data from unnormalized form to 3NF includes below steps: 

 

Step 1: Normalization to 1NF

To convert to 1NF, below conditions are to be satisfied: 

 

a) The table should have a primary key which is a non-null and unique attribute of the table.

b) Primary key can be composite key also i.e. composed of more than one attribute.

c) There should be no cell in the table with multiple values.

d) The repeating groups should be removed i.e. for the same value of an attribute (attributes), there should be no other attribute(s) whose value is changing.

 

Step 2: Normalization to 1NF

Below are the conditions to be satisfied to make a table in 2NF:

 

a) Decompose to the table to 1NF.

b) Decompose the table if there is a non key attribute that is not dependent on the key attribute.

c) Decompose the table if a table has a composite key and a nonkey attribute in the table depends on the subset of the key i.e. depends on one attribute of composite ket instead of a complete key.

 

Step 3: Normalization to 1NF

 

Below are the steps to be followed to make a table in 3NF:

a) The table should be in 2NF, if it is not in 2NF then apply decomposition so that tables are in 2NF.

b) There should be NO non key attribute that depends on another non key attribute which is called a transitive dependency. In such case, the table is needed to be decomposed to remove this dependency.

2.3 Conversion to 1st Normal Form

The given table can be converted to 1st Normal form with all the conditions  specified for Step 1 in 2.2 are satisfied:

 

1) Consider that the table has unique invoiceNo for each invoice.

2) There are many ProductNo per single Invoice NO

3) If we make InvoiceNo, ProductNo as composite key, below repeatig group will be intorudced: 

 

Invoice-No, Client-No , Client-name {many records for same left side attributes } -> Product-No, Product-Name, Product-Price , Quantity.

 

Thus same table cannot be considered to be in 1NF.

 

The solution is to decompose the table in below forms:

 

INVOICE(Invoice-No, Client-No, Client-Name)

Invoice_Products(Invoice-No, Product-No, Product-Name, Product-Price , Quantity): Invoice-No is foreign key from INVOICE.

 

The final 1NF tables in required format are as below: 

 

  1. INVOICE[Invoice-No, Client-No, Client-Name]
  2. Invoice_Products[Invoice-No, Product-No, Product-Name, Product-Price , Quantity]

 

2.4 Dependencies of 2.3

There are below types of dependencies to be considered in database normalization: 

 

  1. Full Functional Dependency:  When the non key attribute depends on COMPLETE key attribute, it is called full functional dependency.
  2. Transitive Dependency: When anon key attribute determines another non-key attribute, it is called transitive dependency
  3. Partial Dependency: When table has a composite key and subset of the key determines the non key attribute, it is called partial dependency.

The identified tables as below functional dependencies: 

 

  1. INVOICE[Invoice-No, Client-No, Client-Name]

Full Functional Dependency: Invoice-No -> Client-No, Client-Name

Transitive dependency:  Client-No ->  Client-Name

 

2. Invoice_Products[Invoice-No, Product-No, Product-Name, Product-Price , Quantity]

 

Full Functional Dependency: Invoice-No, Product-No -> Quantity

Partial dependency: Product-No -> Product-Name, Product-Price

Conclusion

The given table is concluded to be in Unnormalized form.

The table is decomposed to 1NF and then the dependencies of the 1NF database tables is mentioned as a solution of 2.4 part.

Invoice-No Client-No Client-Name Product-No
John
Product-Name Product-Price
RA 000
RS 000
Quant
1234
4321
12344
Screen
2
12345
Case
4532
2345
Carl
13678
USB
RA00
10
9876
8765
Remy
67800
R20
24
2.1 In which Normal Form is the above table? Explain how you arrived at your
answer.
22 Explain the steps required to convert from an unnormalized table to 3" Normal
Form.
2.3 Convert the given table into 1" Normal Form. Write your answer as a relation.
Eg table name(att1, att2)
2.4 Write out all the dependencies of 2.3.
2.5 Convert the relation in 2.3 into 2 Normal Form. Write your answer as a
relation.
2.6 Convert the relation in 2.5 into 34 Normal Form. Write your answer as a relation.
2.7 What is Normalization?
2.8 Explain Boyce-Codd Normal Form.
Transcribed Image Text:Invoice-No Client-No Client-Name Product-No John Product-Name Product-Price RA 000 RS 000 Quant 1234 4321 12344 Screen 2 12345 Case 4532 2345 Carl 13678 USB RA00 10 9876 8765 Remy 67800 R20 24 2.1 In which Normal Form is the above table? Explain how you arrived at your answer. 22 Explain the steps required to convert from an unnormalized table to 3" Normal Form. 2.3 Convert the given table into 1" Normal Form. Write your answer as a relation. Eg table name(att1, att2) 2.4 Write out all the dependencies of 2.3. 2.5 Convert the relation in 2.3 into 2 Normal Form. Write your answer as a relation. 2.6 Convert the relation in 2.5 into 34 Normal Form. Write your answer as a relation. 2.7 What is Normalization? 2.8 Explain Boyce-Codd Normal Form.
Expert Solution
steps

Step by step

Solved in 3 steps

Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY