Ch. 4: MARICA’S CLEANERS PROJECT

1. Ch. 4: MARICA’S CLEANERS PROJECT Marcia is in the process of creating databases to support the operation and management of her business. For the past year, she and her staff have been using a cash register system that collects the following data: SALE (InvoiceNumber, DateIn, DateOut, Total, Phone, FirstName, LastName) Will this give her a satisfactory database? If not, why not? What if the invoice include various services such as shirts, suits, mending, etc? Multiplicity?

2. Problems: • Not all of the data are entered • 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.

3. Attempted solution • 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)

4. Problems with the database: • Some phone number has multiple names. • Some customer may have multiple phones • 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.

5. Question A. • Design an updateable 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.

6. What’s the problem? • The SALE database has two themes: • CUSTOMER • SALE • Therefore, both CUSTOMER and SALE are candidates for new split tables • Also, assume that (FirstName, LastName) is not unique; this is realistic assumption. • Create a surrogate key CustomerID to CUSTOMER table

7. Realized tables CUSTOMER (CustomerID, Phone, FirstName, LastName, Street, City, State, Zip, Apartment) SALE (InvoiceNumber, CustomerID, DateIn, DateOut, Total)

8. Investigate SALE table SALE (InvoiceNumber, CustomerID, DateIn, DateOut, Total) • MultivaluedDependencies: • None • Functional Dependencies: InvoiceNumber  CustomerID InvoiceNumber  DateIn InvoiceNumber  DateOut InvoiceNumber  Total • Candidate Keys • InvoiceNumber

9. CUSTOMER (CustomerID, Phone, FirstName, LastName, Street, City, State, Zip, Apartment) • MultivaluedDependencies: • Phone  (FirstName, LastName) • Phone  CustomerID • CustomerID  Phone • Functional Dependencies: • CustomerID  Phone • CustomerID  FirstName • CustomerID  LastName • CustomerID  Street • CustomerID City • CustomerID  State • CustomerID  Zip • CustomerID  Apartment • Zip  (City, State) • Candidate Keys • CustomerID • (Phone, FirstName, LastName)

10. Final Design CUSTOMER (CustomerID, FirstName, LastName, Street, City, State, Zip, Apartment) CUSTOMER_PHONE (CustomerID, Phone) WHERE CUSTOMER_PHONE.CustomerID must exist in CUSTOMER.CustomerID SALE (InvoiceNumber, CustomerID, DateIn, DateOut, Total) WHERE SALE.CustomerID must exist in CUSTOMER.CustomerID CUSTOMER (CustomerID, Phone, FirstName, LastName, Street, City, State, Zip, Apartment) SALE (InvoiceNumber, CustomerID, DateIn, DateOut, Total)

11. Missing Data- CUSTOMER - • Assume that the data in HOUSEHOLD is correct • Enter as much data as possible from the cash register system into the new CUSTOMER table. • For each entry, verify it against the data in HOUSEHOLD. If the data is complete and verifiable, we will consider that data accurate and enter the associated data from HOUSEHOLD. • From the data in A, if any address data is missing, • call the Customer to obtain the data and update the CUSTOMER table, or • create an application that notifies employees when a customer has come in for whom we need data and get it at that time. • From the data in A, list any incomplete or inconsistent data. If we have a phone number, call to try to resolve the problems. Resolved data will be entered