Home Library Relation Assignment

MN621 Local area network design and setup
BX2112: LAW OF BUSINESS ORGANISATIONS SP 21 2018
April 22, 2018
MN621 Local area network design and setup
PROJECT MANAGEMENT ASSIGNMENT
April 25, 2018

Home Library Relation Assignment

MN621 Local area network design and setup

 

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));

For this assignment contact www.punjabassignmenthelp.com

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *