Question

Book List BKNO TITLE YEARIPRICE PUBLISHER LOCATION AUTHOR LIVED asebook Of Sherlock olmes, The 1950 4.25 Pocket 19665.75 Dell 1960 6.50 Dell 1946 6.25 Pocket New York, NY Doyle, Sir Arthur 1910- 1980 1920 1975 1914 1984 New York, NY Benet, Stephen 1920- 2010 1940 Books Conan 4 Red Badge Of Courage Chicago, IL Crane, Stephen urn of the Screw hicago, IL James, Henry 22 Pock, The Books s Angeles, Wallach, Ira s Angeles, Hale, Lucretia s Angeles, Hale, Lucretia 31 Hopalong-Freud & Other 19666.50 Dover Parodies 64 Peterkin Papers, The 1925- 1985 1925- 1985 New York, NY Vandercook, John 1930- 2005 New York, NY Wangerin, Walter1950- 1963 6.75 Dover 65 ast Of The Peterkins, The 1965 5.50 Dover 70 Black Majesty: Life Of Christo Book Of The Dun Cow 1952 9.00 Pocket Books 1979 8.50 Pocket Books 75

1. Do a listing that represents the entities and attributes in the Book List. Select or create an appropriate primary key column for each entity. Make sure your data is atomic. List at least three entities: Book, Author, and Publisher.

*PLEASE NOTE: I want a listing of the attributes that you think should be there (e.g. columns split up for atomicity, surrogate keys, etc.)

*PLEASE NOTE AS WELL: You have to store all of the information from the original list one way or another (you cannot "throw away" columns)

  Example of a Listing:

EntityName(primary key, attribute1,attribute2,attribute3)

where: EntityName <--- name of your entity (e.g. Book)

attribute1 <---- an attribute that goes with this entity (e.g.Title)

Also, describe at least three specific problems with the Book List above (hint: look for redundant information, atomicity, etc.).

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

BookList table given in the question needs to normalize into First Normal Form (1NF) , Because

Problem 1 : All the columns in the BookList table are not automic in nature because column author contains multiple values and hence multivalued column is not allowed in first normal form.

Problem 2 : Some of the columns like Location of publisher is depends upon Publisher and hence need to identify another table with name Publisher and keep all information related to publisher in this table.Likewise same with the Lived column which depends upon Author.

Problem 3 : This BookList table contains some duplicate information and hence need to remove that by doing normalization.

Normalization : Normalization is process of decomposing of large and comples table into simple and smaller form.Before normalization data is row data. Normalization helps to remove redundant data and inconsistancy will be avoided.

Following section gives the process of normalizing above BookList table into First , Second , Third Normal Form.

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.

Here above BookList table needs to normalize into first normal form (1NF) as given below. Separate Author column into FirstName and LastName.

Table :BookList

BKNO	TITLE	YEAR	PRICE	PUBLISHER	LOCATION	FIRSTNAME	LASTNAME	LIVED
2	The Casebook of Sherlock Holmes	1950	4.25	Pocket Books	New York NY	Sir Arthur Conan	Doyle	1910-1980
4	The Red Badge of Courage	1966	5.75	Dell	Chicago. IL	Stephen	Crane	1920-1975
5	Turn of the Screw	1960	6.50	Dell	Chicago ,IL	James	Henry	1914-1984
22	The Pock	1946	6.25	Pocket Books	New York , NY	Stephen	Banet	1920-2010
31	Hopalong -Freud & Other Parodies	1966	6.50	Dover	Los Angeles. CA	Ira	Wallach	1940-
64	The Peterkin Papers	1963	6.75	Dover	Los Angeles,CA	Lucretia	Hale	1925-1985
65	The Last of the Peterkins	1965	5.50	Dover	Los Angeles, CA	Lucretia	Hale	1925-1985
70	Black Majestry : Life of Christop	1952	9.00	Pocket Books	New York ,NY	John	Vandercook	1930-2005
75	The Book of the Dun Cow	1979	8.50	Pocket Books	New York , NY	Walter	Wangerin	1950-

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 above table needs to normalize into second normal form (2NF) to remove all the partiall dependancies like column Lived in depends upon author hence need to create new table which store author information. also publisher details needs to be keep into seprate table because Location is depends on publisher.

Below are the tables in Second Normal Form (2NF)

1.Table Name : Publisher

Schema : PublisherName- Primary key , Location

Below is the table data

PublisherName	Location
Pocket Books	New York , NY
Dell	Chicago , IL
Dover	Los Angeles, CA

2.Table Name :Author

Schema : AuthorId-Primary key , FirstName ,LastName, Lived

Here AuthorId is newly added column to identify each author uniquely.

AuthorId	FirstName	LastName	Lived
101	Sir Arthur Conan	Doley	1910-1980
102	Stephen	Crane	1920-1975
103	James	Henry	1914-1984
104	Stephen	Benet	1920-2010
105	Ira	Wallach	1940-
106	Lucretia	Hale	1925-1985
107	John	Vandercook	1930-2005
108	Walter	Wangerin	1950-

3.Table Name : Book

Schema : BKNO-Primary key , Title , Year , Price

Below is the table data.

BKNO	TITLE	YEAR	PRICE
2	The Casebook of Sherlock Holmes	1950	4.25
4	The Red Badge of Courage	1966	5.75
5	Turn of the Screw	1960	6.50
22	The Pock	1946	6.25
31	Hopalong -Freud & Other Parodies	1966	6.50
64	The Peterkin Papers	1963	6.75
65	The Last of the Peterkins	1965	5.50
70	Black Majestry : Life of Christop	1952	9.00
75	The Book of the Dun Cow	1979	8.50

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 all the table needs to normalize into Third Normal Form (3NF).To form a relation primary key of one table is used as foreign key in another table. Below are the tables in third normal forms.

1.Table Name : Publisher

Schema : PublisherName- Primary key , Location

Below is the table data

PublisherName	Location
Pocket Books	New York , NY
Dell	Chicago , IL
Dover	Los Angeles, CA

2.Table Name :Author

Schema : AuthorId-Primary key , FirstName ,LastName, Lived

Here AuthorId is newly added column to identify each author uniquely.

AuthorId	FirstName	LastName	Lived
101	Sir Arthur Conan	Doley	1910-1980
102	Stephen	Crane	1920-1975
103	James	Henry	1914-1984
104	Stephen	Benet	1920-2010
105	Ira	Wallach	1940-
106	Lucretia	Hale	1925-1985
107	John	Vandercook	1930-2005
108	Walter	Wangerin	1950-

3.Table Name : Book_Publisher

Schema : BKNO-Primary key , Title , Year , Price , Publisher_name - Foreign key

Below is the table data.

BKNO	TITLE	YEAR	PRICE	PUBLISHER
2	The Casebook of Sherlock Holmes	1950	4.25	Pocket Books
4	The Red Badge of Courage	1966	5.75	Dell
5	Turn of the Screw	1960	6.50	Dell
22	The Pock	1946	6.25	Pocket Books
31	Hopalong -Freud & Other Parodies	1966	6.50	Dover
64	The Peterkin Papers	1963	6.75	Dover
65	The Last of the Peterkins	1965	5.50	Dover
70	Black Majestry : Life of Christop	1952	9.00	Pocket Books
75	The Book of the Dun Cow	1979	8.50	Pocket Books

4.Table Name : Book_Authors

Schema : BKNO-Foreign key ,AuthorId-Foreign key

Below is the table data

BKNO	AuthorId
2	101
4	102
5	103
22	104
31	105
64	106
65	106
70	107
75	108

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.