IST223-GHW7FS13

Group Project Homework [78 pts]

Note: This assignment can become very large, due to the screen shots. Use trimming and compression to keep it at a manageable size.

Suppose that you have designed a database for Marcia's Dry Cleaning that has the following tables:
CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)
ORDER (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax, TotalAmount)
ORDER_ITEM (InvoiceNumber, ItemNumber, Service, Quantity, UnitPrice, ExtendedPrice)
SERVICE (Service, Description, UnitPrice)

Add the prefix "HW7G_" to each table name.

Do parts A through M as given below. Those that involve SQL should be written in SQL*Plus and run, unless indicated otherwise.
Include screen shots of the results of your runs.
Note: This database has unnecessary duplicated data, which the owner wants to keep.

Here is a crow's foot diagram of the relationships that helps answer some parts:

A. Specify NULL/NOT NULL constraints for each table column. Also specify alternate keys, if any. Use the following table form. (Note: This is a big table, since every column of every table appears as a row.)

Table
Column
NULL/NOT NULL
Alternate Key?
Customer
CustomerID
NOT NULL
No
Customer
Phone
NOT NULL
Yes
Customer
Email
NULL
Yes
Customer
FirstName
NOT NULL
No
Customer
LastName
NOT NULL
No
Order
InvoiceNumber
NOT NULL
No
Order
Date
NOT NULL
No
Order
CustomerID
NOT NULL
No
Order
Subtotal
NULL
No
Order
Tax
NULL
No
Order
Total
NULL
No
Order_Item
InvoiceNumber
NOT NULL
No
Order_Item
ItemNumber
NOT NULL
No
Order_Item
Quantity
NOT NULL
No
Order_Item
Service
NOT NULL
No
Order_Item
UnitPrice
NULL
No
Order_Item
ExtendedPrice
NULL
No
Service
Service
NOT NULL
No
Service
Description
NOT NULL
No
Service
UnitPrice
NOT NULL
No

B. State relationships as implied by foreign keys and specify the maximum and minimum cardinality of each relationship. Justify your