Adapted from 'Data Analysis for Database Design', D.R.Howe
Borrower (borrower#, borrower-name, borrower-status, address-line1, address-line2, town, post-code)
Copies(accession#, ISBNX, purchase-price, date-purchased)
Book(ISBNX, publisher-code, title, publication-date, current-price)
Limit(borrower-status, loan-limit)
Authorship(ISBNX, author-name)
Publisher(publisher-code, publisher-name)
Loan(accession#, borrower#, loan-date)
Reservation(borrower#, ISBNX, reservation-date)
In 'Data Analysis for Database Design' Howe explains that the relationship between the entities Book and Publisher can be represented by the implicit pre-posting of the publisher code within ISBNX. For simplicity here a slightly different approach has been taken. The flex to the model explained below and shown above makes SQL queries and application programs much more straightforward to develop.
The publisher code within ISBNX is ignored and an explicit publisher-code is used to identify a publisher. This is posted into the book table to represent the publishes relationship. Thus the final model is second level rather than first level and contains some controlled redundancy.
This WWW material is copyright De Montfort University and may only be reproduced or used if this copyright notice is included.