Neds Dry Cleaning Case Questions

Ned Smith owns and operates Neds Dry Cleaning , which is an upscale dry cleaner in a well-to-do suburban neighbourhood. Ned makes his business stand out from the competition by providing superior customer service. He wants to keep track of each of his customers and their orders. Ultimately, he wants to notify them that their clothes are ready via email. Suppose you have designed a database for Neds Dry Cleaning that has the following tables:

Client (ClientID, FirstName, LastName, Phone)

INVOICE (InvoiceNumber, ClientID*, DateIn, DateOut, Subtotal, Tax, TotalAmount)

INVOICE_ITEM (ItemNumber, InvoiceNumber*, ServiceID, Quantity, UnitPrice, ExtendedPrice)

SERVICE (ServiceID, ServiceDescription, UnitPrice)

Assume that all relationships have been defined, as implied by the foreign keys in this table list, and that the appropriate referential integrity constraints are in place.

Create database Ned_DB;

Questions

1. Alter client table by adding a new column email for each client. [slide 36]
2. Alter client table by changing the phone column name to contact for each client. [slide 40]
3. Create a Constraint on unitPrice column of Invoice_Item table. UnitPrice value should be greater than $20. [slide 44]
4. Add a computed full_name to the Client table by using the ALTER TABLE ADD column.
5. Suppose Ned considers changing the primary key of the table Service to (ServiceID and ServiceDescription). Make appropriate changes to the table design with this new primary key.