Consider the following relation for a database that keep tra

Consider the following relation for a database that keep track of Automobile sales, in a car dealers up (OPTION refers to some optional equipment installed on an automobile) Cars (Serial No: String model: String manufacturer: String, price Real) Options (Serial No: String option Name String price: Real) Salespersons (Sales personal ID: String, name: String phone: String) Sales(Sales personal ID: String, serial No: Strings: s Date: Date, sale Price, Real) There are three foreign keys for this schema the attribute Serial No of relation Options that references relation Cars, the attribute Serial No of relation Sales that references relation Cars, and the attribute Salesperson ID of relation Sales that references relation Salespersons Your task is to write SQL statement, to define tables tor the corresponding relations and capture as many of the constraints as possible. Make sure that the data type and size for each attribute are appropriate for example, a-numerical data type is a better choice than a String data type for the attribute sale Price

Solution

CREATE TABLE Cars
(
   SerialNo VARCHAR2(20) NOT NULL,
   model VARCHAR2(100),
   manufacturer VARCHAR2(100),
   price NUMBER(13,2),
   CONSTRAINT Cars_PK PRIMARY KEY (SerialNo)
);

CREATE TABLE Options
(
   SerialNo VARCHAR2(20) NOT NULL,
   optionName VARCHAR2(100),
   price NUMBER(13,2),
   CONSTRAINT Options_PK PRIMARY KEY (SerialNo, optionName),
   CONSTRAINT FK_Cars
       FOREIGN KEY (SerialNo)
       REFERENCES Cars(SerialNo)
);

CREATE TABLE Salespersons
(
   SalespersonID NUMBER(6),
   Name VARCHAR2(200),
   phone VARCHAR2(12),
   CONSTRAINT Salespersons_PK PRIMARY KEY (SalespersonID)
);

CREATE TABLE Sales
(
   SalespersonID NUMBER(6),
   serialNo VARCHAR2(20),
   sDate DATE,
   salePrice NUMBER(13,2),
   CONSTRAINT Sales_PK PRIMARY KEY (SalespersonID,serialNo),
   CONSTRAINT FK_Cars
       FOREIGN KEY (serialNo)
       REFERENCES Cars(SerialNo),
   CONSTRAINT FK_Salespersons
       FOREIGN KEY (SalespersonID)
       REFERENCES Salespersons(SalespersonID)
);