8.3

A car-rental company maintains a database for all vehicles in its current fleet. For all vehicles, it includes the vehicle identification number, license number, manufacturer, model, date of purchase, and color. Special data are included for certain types of vehicles: * Trucks: cargo capacity. * Sports car: horsepower, renter age requirement. * Vans: number of passengers. * Off-road vehicles: ground clearance, drivetrain (four- or two-wheel drive).

Construct an SQL schema definition for this database. Use inheritance where appropriate.

---

For this problem, we use table inheritance. We assume that MyDate, Color and DriveTrainType are pre-defined types.

CREATE TYPE Vehicle (
    vehicle_id INTEGER, 
    license_number CHAR(15), 
    manufacturer CHAR(30), 
    model CHAR(30), 
    purchase_date MyDate, 
    color Color
);

CREATE TABLE vehicle OF TYPE Vehicle;

CREATE TABLE truck (
    cargo_capacity INTEGER
) UNDER vehicle;

CREATE TABLE sportsCar ( 
    horsepower INTEGER, 
    renter_age_requirement INTEGER
) UNDER vehicle;

CREATE TABLE van (
    num_passengers INTEGER
) UNDER vehicle;

CREATE TABLE offRoadVehicle ( 
    ground_clearance REAL, 
    driveTrain DriveTrainType
) UNDER vehicle;

If you use postgresql you can use the following code:-

CREATE TYPE COLOR AS ENUM (
    'Red',
    'Orange',
    'Yellow',
    'Green',
    'Cyan',
    'Blue',
    'Magenta',
    'Purple'
);

CREATE TYPE DriveTrainType AS ENUM (
    '4-wheel',
    '2-wheel'
);

CREATE TABLE vehicle (
    vehicle_id INTEGER PRIMARY KEY, 
    license_number CHAR(15), 
    manufacturer CHAR(30), 
    model CHAR(30), 
    purchase_date DATE, 
    vehicle_color COLOR
);

CREATE TABLE truck (
    cargo_capacity INTEGER
) INHERITS (vehicle);

CREATE TABLE sportsCar ( 
    horsepower INTEGER, 
    renter_age_requirement INTEGER
) INHERITS (vehicle);

CREATE TABLE van (
    num_passengers INTEGER
) INHERITS (vehicle);

CREATE TABLE offRoadVehicle ( 
    ground_clearance REAL, 
    driveTrain DriveTrainType
) INHERITS (vehicle);