KEMBAR78
Assignment 2 | PDF | Data | Computer Data
Scribd
Upload
88 views3 pages

Assignment 2

The document outlines the process of normalizing a library's book table into Second Normal Form (2NF) and a product record table into Third Normal Form (3NF). It explains the identification of partial and transitive dependencies and provides the resulting decomposed tables for both cases. The final tables eliminate these dependencies, ensuring compliance with the respective normal forms.

Uploaded by

Zeenat Mustafa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
88 views3 pages

Assignment 2

The document outlines the process of normalizing a library's book table into Second Normal Form (2NF) and a product record table into Third Normal Form (3NF). It explains the identification of partial and transitive dependencies and provides the resulting decomposed tables for both cases. The final tables eliminate these dependencies, ensuring compliance with the respective normal forms.

Uploaded by

Zeenat Mustafa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Assignment # 01

Question No. 1
A library stores information about books and the genres they belong to. A book can
belong to more than one genre. You are required to convert the following table into
Second Normal Form (2NF).

Book Table:
BookID Genre PublicationYear

101 Fiction 2010

101 Mystery 2010

102 Science 2015

103 History 2018

103 Biography 2018

Answer:
1NF
 All attributes are atomic.
 No repeating groups.
Book Table already in 1NF.
2NF
 Be in 1NF.
 No Partial dependencies (i.e., non-prime attributes must depend on the whole
primary key).
 Partial dependency exist in Book table.
Reason
The composite key is (BookID, Genre).
But Publishon year only depends on BookID, not Genre.
Book Table Decompose into 2NF Tables:
Table 1: Book
BookID Publication Year
101 2010
102 2015
103 2018

Table 2: BookGenre
BookID Genre
101 Fiction
101 Mystery
102 Science
103 History
103 Biography

These are tables remove the partial dependency and satisfy 2NF.
Question No. 2
You are required to convert the following relation into Third Normal Form (3NF).
ProductRecord(ProductID, ProductName, Supplier, SupplierCountry)

The relation contains the following functional dependencies:

 ProductID → ProductName, Supplier, SupplierCountry (ProductID is PK)

 Supplier → SupplierCountry (Each supplier belongs to one country)

Answer:

1NF

 All attributes are atomic.


 No repeating groups.
 ProductRecord Table already in 1NF.

2NF

 Be in 1NF.
 No partial dependencies (i.e., non-prime attributes must depend on the whole
primary key.)
Product Record Table already in 2NF.

3NF
 Be in 2NF.
 No transitive dependencies (i.e., non-prime attributes depending on another non-
primary key.)

Transitive dependency exist in ProductRecord Table.

Because,
SupplierCountry depends on Supplier, not directly on productID.

ProductRecord Table Decompose to remove Transitive dependency:

Table 1: Product
Product(ProductID, ProductName, Supplier)
Primary Key: ProductID

Table 1: SupplierInfo
SupplierInfo(Supplier, SupplierCountry)
Primary Key: Supplier

These two tables remove the transitive dependency and satisfy 3NF.

You might also like