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!
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

Get Data Analysis for Database Design, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.