Database Design for Jo Bloggs Auto Traders

This assignment tasks you with designing a relational database schema for Jo Bloggs Auto Traders. The schema should encompass various entities crucial to the business, such as manufacturers, vehicles (with details on type, price, extras), clients, sales transactions, and commission structures. You'll need to define attributes for each entity, establish relationships between them, and consider data types appropriate for accurate representation.

PART-A
1. Model Description:
Jo Bloggs Auto Traders is a trading company which sells motor vehicles. The company sells both
new and used vehicle. The company maintains a database which stores the details of those
vehicles, manufacturer of the vehicle together with the sales details of the vehicle to the customer
by the salesperson. Each manufacturer is uniquely identified with the manufacturer id. The
details of the manufacturer like name, phone number and industry rank are stored in the
database. The manufacture has many addresses each for particular purpose. Each vehicle in the
company is identified with a vehicle id. Together with the vehicle id the chassis number of the
vehicle is maintained in the database. For new vehicles the vehicle information like their color,
price, discount type and manufacturer id of the vehicle manufacturer are maintained in the
database. The car has multiple facilities. The facilities of the car are termed as extra. As each car
has multiple facilities, the facility information of the car is maintained in separate table. For used
vehicle its price and description are alone maintained. The database also maintains sale
information of the vehicle. The sale information like the client who sells the vehicle, the sales
person who sold it, price it was sold and commission that could be obtained for selling the
vehicle. As the sale commission of the vehicle is shared among multiple sales person the details
of the sale persons involved in the vehicle sale is maintained in the separate table. The client and
sales person details like their id, name and address are maintained in the database.
2. Entity Relationship Diagram:
3. Relational Data Model
SalePerson(SalesPersonID, Name, Address)
Client(ClientID, Name, Address)

Discount (DiscountID, Name, Percentage)
Manufacturer(ManufacturerID, Name, PhoneNo, IndustryRank)
ManufacturerAddress (ManufacturerID, Purpose, Addres, Foregin key (ManufacturerID)
references Manufacturer (ManufacturerID))
Vehicle (VehicleID, ChassisNo, Price)
UsedVehicles(VehicleID, Description)
NewVehicle (VehicleID, ManufacturerID, Colour, DiscountID, EngineCapacity, Foreign key
VehicleID references Vehicle (VehicleID), Foreign key ManufacturerID references
Manufacturer (ManufacturerID), Foreign key DiscountID reference Discount (DiscountID) )
Extras (VehicleID, Extras, Foreign key VehicleID references Vehicle (VehicleID))
Sale (VehicleID, ClientID, Price, Commission, Foreign key VehicleID references Vehicle
(VehicleID), foreign key ClientID references Client (ClientID))
SalesOfSalesPerson (VehicleID, SalesPersonID, Foreign key VehicleID references Vehicle
(VehicleID), Foreign key SalesPersonID references SalePerson(SalesPersonID))
Table Description:
SalePerson Table:
Attribute Name Data Type Constraints
SalesPersonID INT PRIMARY KEY
Name VARCHAR(25) NOT NULL
Address VARCHAR(40) NOT NULL
Client Table:
Attribute Name Data Type Other Constraints
ClientID INT PRIMARY KEY
Name VARCHAR(25) NOT NULL
Address VARCHAR(40) NOT NULL
Discount Table:
Attribute Name Data Type Other Constraints
DiscountID INT PRIMARY KEY
Name VARCHAR(25) NOT NULL
Percentage FLOAT NOT NULL
Manufacturer Table:
Attribute Name Data Type Other Constraints
ManufacturerID INT PRIMARY KEY
Name VARCHAR(25) NOT NULL
PhonNo INT(10) NOT NULL
IndustryRank INT NOT NULL

ManufacturerAddressTable:
Attribute Name Data Type Other Constraints
ManufacturerID INT PRIMARY KEY, FOREIGN
KEY REFERENCES
manufacturer(ManufacturerID)
Purpose VARCHAR(10) PRIMARY KEY
Address VARCHAR(40) NOT NULL
Vehicle Table:
Attribute Name Data Type Other Constraints
VehicleID INT PRIMARY KEY
ChassisNo INT(10) NOT NULL
Price CURRENCY NOT NULL
UsedVehicle Table:
Attribute Name Data Type Other Constraints
VehicleID INT PRIMARY KEY, FOREIGN KEY
REFERENCES Vehicle(VehicleID)
Description VARCHAR(125) NOT NULL
NewVehicle Table:
Attribute Name Data Type Other Constraints
VehicleID INT PRIMARY KEY, FOREIGN KEY
REFERENCES Vehicle(VehicleID)
ManufacturerID INT FOREIGN KEY REFERENCES
Manufacturer(ManufacturerID)
Colour VARCHAR(10) NOT NULL
DiscountID INT FOREIGN KEY REFERENCES
Discount(DiscountID)
EngineCapacity INT NOT NULL
Extras Table:
Attribute Name Data Type Other Constraints
VehicleID INT PRIMARY KEY, FOREIGN KEY
REFERENCES Vehicle(VehicleID)
Extra VARCHAR(25) NOT NULL
Sale Table:
Attribute Name Data Type Other Constraints
VehicleID INT PRIMARY KEY, FOREIGN KEY
REFERENCES Vehicle(VehicleID)

ClientID INT FOREIGN KEY REFERENCES
Client(ClientID)
Price CURRENCY NOT NULL
Commission CURRENCY NOT NULL
SalesOfSalesPerson Table:
Attribute Name Data Type Other Constraints
VehicleID INT PRIMARY KEY, FOREIGN KEY
REFERENCES Vehicle(VehicleID)
SalesPersonID INT FOREIGN KEY REFERENCES
SalesPerson (SalesPersonID)