Assignments 2
1. Items 1A - 1C refer to a database that keeps track of automobile and option sales in a car dealership. CAROPTION refers to some optional equipment installed on an automobile that is sold. You will create the database in Oracle.
The ERD, the conceptual model of the database, contained the following:
- three entities: SALESPERSON, CAR and CAROPTION
- The ternary relationship, among the entities SALESPERSON, CAR and CAROPTION
The relations (tables) of the database are listed below. The primary key of each relation is shown in bold, underline and italics lettering.
CAR(SerialNo, Model, Manufacturer, List_Price)
CAROPTION(OptionNo, OptionName, List_Price)
CARSALE(SalespersonId, SerialNo, Date, Car_Sale_price)
OPTIONSALE(SalespersonId, SerialNo, OptionNo, Option_Sale_price)
SALESPERSON(SalespersonId, Name, Phone)
1A. Give the DDL commands to create the tables, primary keys and referential integrity constraints. The DDL must have at least an example of each of the following. Use comments to indicate the examples.
1. Not Null check constraint
2. Column check constraint
3. Table check constraint
4. Column default value
5. Table-level constraint
6. Check constraint
DDL:
1.B Provide the DML and logs that show that you populated the database with the data shown below.
Car
SerialNo Model Manufacturer List_Price
ABC LX Kia 19,000
BCD EX Kia 19,500
EFG SX Kia 26,000
SALESPERSON
SalespersonId Name Phone
101 John 555-555-5555
102 Paul 555-556-5678
103 Mary 555-678-7890
OPTION
OptionNo OptionName List_Price
1 2.4-liter 2,000
2 2.0-liter turbo 3,000
3 Sun roof 1,000
CARSALE
SalespersonId SerialNo Date Car_Sale_price
101 ABC 10/16/2011 18,500
101 BCD 10/16/2011 21,000
102 EFG 10/16/2011 25,500
OPTIONSALE
SalespersonId SerialNo OptionNo Option_Sale_price
101 ABC 1 1,500
101 ABC 3 1,000
102 EFG 2 2,500
102 EFG 3 750
DML:
1.C. 1. Do a search to find out the name of a salesperson who sold a car on 10/16/2011 for 25,500.
1C.2. Find out what kind of model and manufacturer the car that was sold was.