Using the relations that you have defined for the library system, normalize your relations into first, second, and third normal form. Your output should be an Entity Relationship diagram that is in 3NF. All attributes, entities, keys, and relationships should be included and labeled. Although you do not need to include an ER diagram (unless you want to) for the relations in 1^st normal form or 2^nd normal form, you must describe the changes required to your relations to meet the standard of first 1^st normal form and then 2^nd normal form and of course 3^rd normal form.

Assignment Instructions:

the assignment must define or describe the changes required to get the relations into the 1st normal form

the assignment must define or describe the changes required to get the relations into the 2nd normal form

the assignment should include an Entity Relationship diagram that details each of the following relations and their attributes in 3rd normal form.

Book

Borrower

BookLended

Librarian

the primary and foreign keys, at a minimum are defined for each relation in the ER diagram

the assignment should include an Entity Relationship diagram that details the relationships between Relations. Be sure to copy and paste this diagram into your assignment.

Borrower ISBN Number sequence Title Author PublicationDate date oCost integer integer char (60) char (40) 'LibraryCard# integer Name Address PostalCode char (10) PhoneNumber char (20) 0MemberShipDate date char (40) char (40) number All primary keys are identified as attributes in bold type face BookLended 'LibraryCarde integer 'Librarian!d integer|0d." ISBN number integer sequence integer checkoutdate date returndate date NOTE The combination of ISBN_number and sequence in the booklended relation is a foreign key to book LibraryCard# is a foreign key to the borrower relation Librarian Librarianld is a foreign key to the librarian relation LibrarianID integer Name PhoneNumber char (20) *supervisor intege char (40) Checkout date is part of the candiate (primary) key of booklended relation along with all other attributes in bold Return date in this case is not necessarily derived from checkoutdate so we have included it in the relation. If we knew that returndate could alwasy be derived from the checkoutdate then this attribute would be redundant The supervisor is a librarian so the supervisor attribute must have a valid librarian id The supervisor is a foreign key to librarian There must be a referential integrity constraint on supervisor to ensure that only a valid librarian id is entered for this value Borrower ISBN Number sequence Title Author PublicationDate date oCost integer integer char (60) char (40) 'LibraryCard# integer Name Address PostalCode char (10) PhoneNumber char (20) 0MemberShipDate date char (40) char (40) number All primary keys are identified as attributes in bold type face BookLended 'LibraryCarde integer 'Librarian!d integer|0d." ISBN number integer sequence integer checkoutdate date returndate date NOTE The combination of ISBN_number and sequence in the booklended relation is a foreign key to book LibraryCard# is a foreign key to the borrower relation Librarian Librarianld is a foreign key to the librarian relation LibrarianID integer Name PhoneNumber char (20) *supervisor intege char (40) Checkout date is part of the candiate (primary) key of booklended relation along with all other attributes in bold Return date in this case is not necessarily derived from checkoutdate so we have included it in the relation. If we knew that returndate could alwasy be derived from the checkoutdate then this attribute would be redundant The supervisor is a librarian so the supervisor attribute must have a valid librarian id The supervisor is a foreign key to librarian There must be a referential integrity constraint on supervisor to ensure that only a valid librarian id is entered for this value