Department of Information Studies University College London Module INST2005

Database Systems Normalisation Exercises 1: Answer notes
1) Unnormalised Title Author Publisher Publisher's address *Book number Borrower number Borrower name Borrower address Loan date Return date 1NF *Book number Title Author Publisher Publisher's address Borrower number Borrower name Borrower address Loan date Return date 2NF *Book number Title Author Publisher Publisher's address Borrower number Borrower name Borrower address Loan date Return date 3NF *Book number Title Borrower number Loan date

Title Author Publisher Publisher Publisher's address Borrower number Borrower name Borrower address Loan date Return date Queries: Is title unique? If not what should we do? *Use ISBN13? Is the return date solely dependent on the loan date? Is it logical to keep the "loan record" in the copy? Do we want to store old loan info? Will this structure support/allow us to easily fulfil our purpose? Further exercises: try developing an e-r diagram based on the 3NF data; consider some of the ideas mentioned, incorporate some changes, and reapply the normalisation process. 2) Unnormalised Student no Student name Prog. Name Prog. Length Module code Module name Module tutor 1NF Student no Student name Prog. Name Prog. Length 2NF Student no Student name Prog. Name Prog. Length 3NF Student no Student name Prog. Name

Student no Module code Module name Module tutor

Student no Module code Module code Module name Module tutor

Prog. Name Prog. Length Student no Module code Module code Module name Module tutor

Queries: Is Prog length only dependent on the Prog name? What if not? Can we not determine module tutor from module name? What if we had more tutor data on file? Further exercise: again, develop an e-r diagram based on this data and if you wish to restructure it, do so and reapply the