Share
Explore BrainMass

Oracle 10g

Figures 1-24 and 1-25 (attached) display the data and relationships within the Clearwater Traders database. Reference these figures when completing the following cases.

1. Based on the description of the Clearwater Traders database, determine the appropriate constraints for each table. Make certain you consider the following constraint types for each table in the database:
o Primary key
o Foreign key
o Check condition
o NOT NULL
o Unique
When drafting your results, list each table name, the constraint type, and the column that is referenced by the constraint.

2. Based on the sample data provided, determine the necessary column definition for each column contained in the following tables:
o CUSTOMER
o ORDERS
o ORDER_LINE
o ORDER_SOURCE
o CATEGORY
o ITEM
o INVENTORY

Attachments

Solution Preview

Primary and Foreign keys:

Customer Table: C_ID is the primary key.

Definition for each column:

C_ID: customer id (Unique) (NOT NULL)
C_LAST: customer Last Name (NOT NULL)
C_FIRST: customer First Name (NOT NULL)
C_MI: customer Middle Name
C_DOB: customer Date of Birth (NOT NULL)
C_ADDRESS: Address (NOT NULL)
C_CITY: City (NOT NULL)
C_STATE: State (NOT NULL)
C_ZIP: Zip code (NOT NULL)
C_DPHONE, C_EPHONE: Phone Numbers (NOT NULL)
C_USERID: user id (NOT NULL)
C_PASSWORD: password (NOT NULL)

Check condition:
Zip cannot be less than 5 digits
Sate should be two characters
Date should be in date format

Order_Source: OS_ID is the ...

$2.19