Database Design for a Motor Company

This assignment focuses on designing a relational database schema for a motor company. The database should store information about vehicles (new and used), manufacturers, sales transactions, and extra features. The description provides detailed specifications for each table, including attributes, data types, primary keys, and foreign keys. This comprehensive design illustrates the relationships between different entities within the motor 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)