If you have not already done so, create
entity-relationship diagrams for Questions A and C in the FiredUp Project at
the end of Chapter 3.
A. Transform the
entity-relationship diagram from Question A at the end of Chapter 3 into a set
of relations in domain/key normal form. For each relation, specify the primary
key, candidate keys, if any, and foreign keys. Specify all interrelation
constraints. If necessary, make and justify assumptions regarding the
underlying semantics of the application.
In the following, primary keys are underlined; foreign keys are in
italics. Note how all relationships are
replaced by the definition of foreign keys.
Relations:
CUSTOMER (CustomerID, Name,
StreetAddress, ApartmentNumber, Zip/PostalCode,
EmailAddress, PhoneNumber)
LOCATION-CODE (Zip/PostalCode, City,
State/Provice, Country)
STOVE (SerialNumber, Type,
ManufacturerDate, InspectorInitials, CustomerID)
INVOICE (InvoiceNumber, Date, TotalPrice)
INV-LINE-ITEM (InvoiceNumber, LineNumber, PartNumber, Quantity, ExtendedPrice)
REPAIR (RepairNumber, Date, Description,
TotalAmount, SerialNumber)
REP-LINE-ITEM (RepairNumber, LineNumber, PartNumber, Quantity, ExtendedPrice)
PART (Number, Description, Cost,
SalesPrice)
This design uses a surrogate key for CUSTOMER. EmailAddress is therefore a candidate key for
CUSTOMER. There are no other candidate
keys. Date has been added to REPAIR
because it should be there.
Referential integrity constraints:
Zip/PostalCode in CUSTOMER must exist in
Zip/PostalCode in LOCATION-CODE
CustomerID in STOVE must exist in CustomerID in
CUSTOMER
InvoiceNumber in INV-LINE-ITEM must exist in
InvoiceNumber in INVOICE
PartNumber in INV-LINE-ITEM must exist in Number
in PART
RepairNumber in REP-LINE-ITEM must exist in
RepairNumber in REPAIR
PartNumber in REP-LINE-ITEM must exist in Number
in PART
B. Adjust your
answer to Question A to allow un-normalized relations if you think just
relations are appropriate. Justify any non-normalized relations you have. If
necessary, make and justify assumptions regarding the underlying semantics of
the application.
Get rid of the LOCATION-CODE relation and place, City,
State/Province, Country in CUSTOMER.
Normalizing Zip codes adds complexity and really doesn’t buy
anything. Locations don’t change their
Zip codes very much, and when they do, the change can be handled as any other
address change. Normalization is not
worth the trouble.
All the rest of the normalized tables seem OK as is.
C. Transform the
entity-relationship diagram from Question C at the end of Chapter 3 into a set
of relations, preferably in domain/key normal form. If any of your relations
are not in domain/key normal form, explain why not. For each relation, specify
the primary key, candidate keys, if any, and foreign keys. Specify all
referential integrity constraints.
Leave Zip un-normalized with justification as in
Item B.
Relations:
CUSTOMER (CustomerID, Name,
StreetAddress, ApartmentNumber, City, State/Province, Country)
CUSTOMER-PHONE (CustomerID, Type, PhoneNumber)
CUSTOMER-EMAIL (CustomerID, EmailAddress)
STOVE (SerialNumber, Type, ManufacturerDate,
InspectorInitials, CustomerID)
INVOICE (InvoiceNumber, Date, TotalPrice)
INV-LINE-ITEM (InvoiceNumber, LineNumber, PartNumber, Quantity, ExtendedPrice)
REPAIR (RepairNumber, Date, Description,
TotalAmount, SerialNumber)
REP-LINE-ITEM (RepairNumber, LineNumber, PartNumber, Quantity, ExtendedPrice)
PART (Number, Description, Cost,
SalesPrice)
This design uses a surrogate key for CUSTOMER. EmailAddress is therefore a candidate key for
CUSTOMER. There are no other candidate
keys. Date has been added to REPAIR
because it should be there.
Referential integrity constraints:
CustomerID in CUSTOMER-PHONE must exist in
CustomerID in CUSTOMER
CustomerID in CUSTOMER-EMAIL must exist in
CustomerID in CUSTOMER
CustomerID in STOVE must exist in CustomerID in
CUSTOMER
InvoiceNumber in INV-LINE-ITEM must exist in
InvoiceNumber in INVOICE
PartNumber in INV-LINE-ITEM must exist in Number
in PART
RepairNumber in REP-LINE-ITEM must exist in
RepairNumber in REPAIR
PartNumber in REP-LINE-ITEM must exist in Number
in PART
D. Adjust your
answer to Question C, above, to assume that home, fax, and cell phone are to be
represented by separate, single-value attributes. Is this a better design than
in your answer to Question C? Explain why or why not.
Delete
CUSTOMER-PHONE. Change the CUSTOMER
relation as follows:
CUSTOMER (CustomerID, Name, StreetAddress, ApartmentNumber,
City, State/Province, Country, HomePhone, FaxPhone, CellPhone)
It
may be better, depending on the use of the relation. If no other phone types are ever added and if
there is no need to query on a phone number of an uncertain type, then this
probably is a better design because no join will need to be made to obtain all
of the CUSTOMER data. On the other hand,
if other types of phone may be created and if there’s a need to find out which
customer has a phone number of unspecified type, then the original design may
be better.