Database Design Using Normalization - Database Processing: Fundamentals, Design, and Implementation - page 177

Unfortunately, during rush times, not all of the data are entered, and there are many null
values in Phone, FirstName, and LastName. In some cases, all three are null; in other cases,
one or two are null. InvoiceNumber, DateIn, and Total are never null. DateOut has a few null
values. Also, occasionally during a rush, phone number and name data have been entered
incorrectly. To help create her database, Marcia purchased a mailing list from a local business
bureau. The mailing list includes the following data:
HOUSEHOLD ( Phone , FirstName , LastName , Street, City, State, Zip, Apartment)
In some cases, a phone number has multiple names. The primary key is thus the composite
(Phone, FirstName, LastName). There are no null values in Phone, FirstName, and LastName,
but there are some null values in the address data.
There are many names in SALE that are not in HOUSEHOLD, and there are many names
in HOUSEHOLD that are not in SALE.
A. Design an updatable database for storing customer and sales data. Explain how to deal
with the problems of missing data. Explain how to deal with the problems of incorrect
phone and name data.
B. Design a read-only database for storing customer and sales data. Explain how to deal
with the problems of missing data. Explain how to deal with the problems of incorrect
phone and name data.
The Queen Anne Curiosity Shop questions in Chapter 3 asked you to create a set of
relations to organize and link The Queen Anne Curiosity Shop typical sales data shown
in Figure 3-33 and the typical purchase data shown in Figure 3-34. The set of relations
may look like the following:
CUSTOMER (CustomerID, LastName, FirstName, Phone, Email)
SALE (SaleID, CustomerID, InvoiceDate, PreTaxTotal, Tax, Total)
SALE_ITEM (SaleID, SaleItemID, PurchaseID, SalePrice)
PURCHASE (PurchaseID, PurchaseItem, PurchasePrice, PurchaseDate, VendorID)
VENDOR (VendorID, Vendor, Phone)
Use these relations and the data in Figures 3-33 and 3-34 to answer the following
questions.
A. Follow the procedure shown in Figure 4-1 to assess these data.
1. List all functional dependencies.
2. List any multivalued dependencies.
3. List all candidate keys.
4. List all primary keys.
5. List all foreign keys.
6. State any assumptions you make as you list these components.
B. List questions you would ask the owners of The Queen Anne Curiosity Shop to verify
your assumptions.
C. If there are any multivalued dependencies, create the tables needed to eliminate these
dependencies.
D. Do these data have the multivalue, multicolumn problem? If so, how will you deal with it?
e. Do these data have the inconsistent data problem? If so, how will you deal with it?
F. Do these data have a null value data problem? If so, how will you deal with it?
G. Do these data have the general-purpose remarks problem? If so, how will you deal with it?