ITEC3245 Database Principles

ITEC 3245 Database Principles

1. The Henry Books Store.

Ray Henry is the owner of a bookstore chain named Henry Books. Ray has decided to store his data in a database. He wants to ensure that his data is current and accurate.

Before he designs and constructs a relational database, he was maintaining his data using the following un-normalized branch, book, and inventory data table with the attributes.

*Un-normalized branch, book, and inventory data table*
Branch Number	Book Code	Branch Name	Branch Location	Book Title	Book Price	Book Type	Type Description	Book Quantity
1	3350	Henry Downtown	16 Riverview	Six People in Search of a Life	10.40	PSY	Psychology	2
	669X			A Guide to SQL	37.95	CMP	Computer	1
	9701			The Grapes of Wrath	13.00	FIC	Fiction	2
2	138X	Henry On The Hill	1289 Bedford	Beloved	12.95	FIC	Fiction	3
	3906			The Soul of a New Machine	11.16	SCI	Science	1
	6128			Jazz	12.95	FIC	Fiction	4
	9701			The Grapes of Wrath	13.00	FIC	Fiction	1
3	3906	Henry Brentwood	Brentwood Mall	The Soul of a New Machine	11.16	SCI	Science	2
	6128			Jazz	12.95	FIC	Fiction	3
	7405			East of Eden	12.95	FIC	Fiction	2
	8092			Godel, Escher, Bach	14.00	PHI	Philosophy	1
	9627			Song of Solomon	14.00	FIC	Fiction	5
	9701			The Grapes of Wrath	13.00	FIC	Fiction	3
4	5790	Henry Eastshore	Eastshore Mall	Catch-22	12.00	FIC	Fiction	2
	9627			Song of Solomon	14.00	FIC	Fiction	2
	9701			The Grapes of Wrath	13.00	FIC	Fiction	2

He wants to design his relational database by following steps in the data normalization process. Before following the steps, he found that there are the following associations (i.e. functional dependencies) for the attributes of branches and books.

-	Branch Number -> Branch Name
-	Branch Number -> Branch Location
-	Book Code -> Book Title
-	Book Code -> Book Price
-	Book Code -> Book Type
-	Book Code -> Type Description
-	Book Type -> Type Description
-	Branch Number, Book Code -> Book Quantity

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

Explain what the First Normal Form (1 NF) is. Then 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).
Explain what the Second Normal Form (2 NF) is. Then convert the 1 NF table created previously into 2 NF tables (HINT: you will need to create 3 tables with data) and explain the conversion process in detail.
Explain what the Third Normal Form (3 NF) is. Then convert the 2 NF tables created previously into 3 NF tables (HINT: you will need to create 4 tables with data) and explain the conversion process in detail.

Lost Password