Sample Assignment

Table 1 : Home Library

Below is the Home Library table

ISBN	Title	Author_LastName	Author_FirstName	Publisher	Date	Edition	Media
369852	Cosmos	Sagan	Carl	Random House	1980	1	Book
741258	No Screts	Simon	Carly	Elektra	1972	1	CD
654789	Symphony No 3 Dur Eroica Op 55	Beethoven	Ludwig		1805	1	CD
789654	On the Decay of the Art of Lying	Twain	Mark	Project Gutenberg	1880	1	eBook
258963	The Adventures of Sherlock Holmes	Conan Doyle	Arthur	Project Gutenberg		1	eBook
125896	The Divine Comedy	Alighieri	Dante	Project Gutenberg		1	eBook
357951	The Hitchhickers Guide to the Galaxy	Adams	Douglas	Pan books	1979	1	Book
852369	The Return of the King, Soundtrack	Shore	Howard	Reprise	2003	1	CD
831975	Unseen Academicals	Pratchett	Terry	Dobleday	2009	1	Book

Dependency diagram

The dependency diagram for the Home Library table is as shown below.

1NF Relations

The 1NF relation involves removing repeating groups in individual tables, creating separate tables for every set of related data, and identifying the primary key in the related data. The Home Library relation to 1NF is as shown below.

ISBN

Title

Author_LastName

Author_FirstName

Publisher

Date

Edition

Media

 

2NF Relations

In 2NF all non-key attributes should be fully functional and dependent on the primary key. The Home Library 2NF relation is as shown below.

ISBN

AuthorID

PublisherID

 

ISBN

Title

 

AuthorID

Author_LastName

Author_FirstName

 

PublisherID

Publisher

Date

Edition

Media

 

3NF Relations

In the 3NF relation there should not be any transitive functional dependancies.

The 3NF relations for the Home Library table are as shown below

ISBN

AuthorID

PublisherID

Title

 

AuthorID

Author_LastName

Author_FirstName

 

PublisherID

Publisher

Date

Edition

Media

 

 

 

 

 

 

 

Relational Schema

The relational schema for the 3NF relations is as shown below.

BOOK(ISBN[PRIMARY KEY], Publisher, Date, Edition, Media)

When creating the table BOOK, below are the constraints used.

CREATE TABLE BOOK(

ISBN INT NOT NULL,

Publisher VARCHAR (100) NOT NULL,

Date DATE,

Edition VARCHAR (100),

PRIMARY KEY (ISBN)

);

 

 

AUTHOR(ISBN[FOREIGN KEY], Author_LastName, Author_FirstName)

The unique field in the Home Library is the ISBN which becomes the primary key in the BOOK relation and the foreign key in the AUTHOR relation.

When creating the table AUTHOR, below are the constraints used.

CREATE TABLE(ISBN INT NOT NULL,Author_LastName VARCHAR (100) NOT NULL,Author_FirstName VARCHAR (100),PRIMARY KEY (ISBN));