Data Analysis for Database Design, 3rd Edition

manifestation being explicit, and the other being concealed in customerCode. Likewise,

the table Customer (customerCode, customerName, salesAreaName) also looks fully-

normalised, but the salesAreaCode hidden in customerCode is a determinant of

salesAreaName but not a candidate identifier of Customer. However, many attributes are

potentially composite, and excessive zeal in decomposing them will cause an unnecessary

proliferation of attributes. A price might be decomposed into {poundsPrice, pencePrice} or

a date into {year, month, day}, but in most cases neither decomposition will be useful.

Step 4 Doubtful entity types should be omitted at this stage. The need for any further

entity types will become apparent at step 9. If you have difficulty selecting an identifier,

it can be helpful to invent an identifierNo, at least for temporary use. For example, an

employee might be identified by {employeeName, address, dateOfBirth}, but it will be

simpler to invent employeeNo if it does not already exist. In any case the value of the

composite attribute would change if an employee’s name or address changed, making it

unsuitable as an identifier.

Step 5 If the degree of a relationship is in doubt, prefer many:many to 1:many, and

l:many to 1:1, so that undesirable constraints are avoided. However, a 1:1 relationship

will be simpler to process than a 1:many relationship, and a 1:many will be simpler than

a many:many. Also, remember that a 1:many ‘snapshot’ view may correspond to a

many:many long-term view. Likewise a 1:1 snapshot view may correspond to a 1:many

or a many:many long-term view. If a relationship type seems unduly complex, consider

whether it would be simpler to regard it as an entity type instead.

Steps 7 and 8 Deletion of attributes from the attribute list ensures that all the

attributes are dealt with, and that no attribute appears more often than it should in the

tables. In step 8, the return of a previously assigned attribute to the attribute list implies

that a new entity type is going to be required.

Step 11 Obvious candidates for entity subtypes can be split off at this stage, but any

decisions requiring detailed analysis should be deferred.

Step 12 Having got this far, you may find that your choice of attributes, entities and

relationships is now suspect as an accurate representation of the enterprise. As you now

understand the problem better, start again from step 1!

 )LUVWOHYHOGHVLJQH[DPSOH

6FHQDULR

A library keeps records of current loans of books to borrowers. Each borrower is

identified by a borrowerNo, and each copy of a book by an accessionNo (the library

may have more than one copy of any given book). The name and address of each

borrower is held so that communications, such as overdue loan reminders, can be sent

when necessary. The information required about books is the title, authors, publisher,

publication date, international standard book number (ISBN), purchase price and

current price, where purchase price is the price the library paid at the time of purchase,

and current price is the current list price. There is a restriction on the number of books

a borrower may have on loan at any one time, the limit depending on whether a

First-level design 161