IST223-GHW7FS13 Essay

2209 Words Dec 25th, 2013 9 Pages
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
…show more content…
Explain how you would enforce the data constraint that HW7G_ORDER_ITEM.UnitPrice be equal to HW7G_SERVICE.UnitPrice, where HW7G_ORDER_ITEM.Service = HW7G_SERVICE.Service. Again, no code at this time, just an explanation.

We would use a interrrelation constraint to enforce the data constraint. An interrrelation constraint can be used to set it so UnitPrice will equal each other.

F. Insert all of the following data into the tables, using the CustID Sequence where appropriate:

The data used here is essentially the same data used in Chapter Two. Note that a Sales Tax Rate is needed for calculating the ORDER.Total. This data set (as given) uses 7.90% (.079). Note that dates are not in Oracle style, so you'll have to change them when you enter them.

TABLE OF CUSTOMER DATA:

CustomerID
FirstName
LastName
Phone
Email
100
Nikki
Kaccaton
723-543-1233
NKaccaton@somewhere.com
etc.,
Brenda
Catnazaro
723-543-2344
BCatnazaro@somewhere.com as specified
Bruce
LeCat
723-543-3455
BLeCat@somewhere.com by the
Betsy
Miller
725-654-3211
BMiller@somewhere.com sequence George
Miller
725-654-4322
GMiller@somewhere.com

Kathy
Miller
723-514-9877
KMiller@somewhere.com

Betsy
Miller
723-514-8766
BMiller@elsewhere.com

TABLE OF SERVICES:

Service
Description
UnitPrice
10
Mens Shirt
1.50
11
Dress Shirt
2.50
15
Womens Shirt
1.50
16
Blouse
3.50
17
Dress Blouse
4.50
20
Mens Slacks
5.00
25
Womens Slacks
6.00
30

More about IST223-GHW7FS13 Essay