Vehicle Trading Company Database: ER Diagram, Table Descriptions

This assignment details the database design for a vehicle trading company, encompassing both new and used vehicles. The solution begins with a comprehensive model description outlining the entities involved: vehicles, manufacturers, clients, and salespersons. It proceeds to an Entity-Relationship (ER) Diagram illustrating the relationships between these entities. A relational data model is then presented, defining the tables and their attributes, including data types and constraints. The tables include 'person', 'manufacturer', 'manu_address', 'vehicle', 'new_vehicle', 'new_vehicle_extra', 'used_vehicle', 'sale', and 'sales_person_sale'. Each table is meticulously described, specifying attribute names, data types, and any constraints, such as primary and foreign keys. This design facilitates efficient storage and retrieval of vehicle information, manufacturer details, client data, and sales records, enabling effective management of the trading company's operations.

PART-A
1. Model Description:
The trading company which sells vehicles of both new and used type maintains
information about its vehicles, its manufacturer and its sales details in a database. A
vehicle is manufactured by a manufacturer who is uniquely identified with the help of
manufacturer id. The details of the manufacturer like name, phone number and industry
rank are preserved in the database. The manufacture holds many address each for
particular type. The vehicle in the motor company is identified with the help vehicle id.
The chassis number of the vehicle is maintained in the database for reference purpose.
The vehicles of the company are classified into new vehicles and used vehicles. For new
vehicle manufacturer information, color, price and discount information are maintained in
the database. The facilities of the car are mentioned in extras. As the cars have multiple
facility the facilities information of the car are maintained in separate table. The price and
description are maintained for used vehicles. Both client and sales person contact
information like name and address are stored in the database. Each person is identified
with an id. The database maintains sale information of the vehicles. Each vehicle sales
information like the client who bought the vehicle, price it was sold and commission
information is maintained. As the sale commission is shared among more than one sales
person the details of the salesperson who conceded the sales are maintained in the
database.
2. Entity Relationship Diagram:
3. Relational Data Model
 person(person_id, name, address)
 manufacturer(manu_id, manu_name, phone_num, industry_rank)
 manu_address(manu_id, type, address, foregin key(manu_id) references
manufacturer(manu_id))
 vehicle (vehicle_id, chassis_no)
 new_vehicle(vehicle_id, manu_id, colour, price, discount, engine_capacity, foreign
key vehicle_id references vehicle(vehicle_id))

 new_vehicle_extra(vehicle_id, extra, foreign key vehicle_id references
vehicle(vehicle_id))
 used_vehicle(vehicle_id, price, description, foreign key vehicle_id references
vehicle(vehicle_id))
 sale(vehicle_id, client_id, price, commission, foreign key vehicle_id references
vehicle(vehicle_id), foreign key client_id references person(person_id))
 sales_person_sale(vehicle_id, sales_person_id, foreign key vehicle_id references
vehicle(vehicle_id), foreign key sales_person_id references person(person_id))
Table Description:
person Table:
Attribute Name Data Type Other Constraints
person_id int primary key
name varchar(20) not null
address varchar(50) not null
manufacturer Table:
Attribute Name Data Type Other Constraints
manu_id int primary key
manu_name varchar(20) not null
phone_num int(10) not null
industry_rank int not null
manu_address Table:
Attribute Name Data Type Other Constraints
manu_id int primary key, foreign key
references
manufacturer(manu_id)
type varchar(20) primary key
address varchar(40) not null
 new_vehicle(vehicle_id, , foreign key vehicle_id references vehicle(vehicle_id))
vehicle Table:
Attribute Name Data Type Other Constraints
vehicle_id int primary key
chassis_no int(20) not null

new_vehicle Table:
Attribute Name Data Type Other Constraints
vehicle_id int primary key, foreign key
references vehicle(vehicle_id)
manu_id varchar(20) foreign key references
manufacturer(manu_id)
colour varchar(10) not null
price currency not null
discount float not null
engine_capacity int not null
new_vehicle_extra Table:
Attribute Name Data Type Other Constraints
vehicle_id int primary key, foreign key
references vehicle(vehicle_id)
extra varchar(20) not null
used_vehicle Table:
Attribute Name Data Type Other Constraints
vehicle_id int primary key, foreign key
references vehicle(vehicle_id)
price varchar(20) float
description varchar(100) not null
sale Table:
Attribute Name Data Type Other Constraints
vehicle_id int primary key, foreign key
references vehicle(vehicle_id)
client_id varchar(20) foreign key references
person(person_id)
price currency not null
commission float not null
new_vehicle Table:
Attribute Name Data Type Other Constraints
vehicle_id int primary key, foreign key
references vehicle(vehicle_id)
sales_person_id varchar(20) foreign key references
person(person_id)