Assignments 2 | Complete Solution

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. (5 Points) 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 (5 Points) 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:

1C
1.C. 1. ( 5 points) Do a search to find out the name of a salesperson who sold a car on 10/16/2011 for 25,500.

1C.2. ( 5 points)  Find out what  kind of model and manufacturer the car that was sold was.