Ask a question from expert

Ask now

A Model Description: Jo Bloggs Auto Traders

4 Pages815 Words451 Views
   

Added on  2020-05-11

About This Document

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. Entity Relationship Diagram: Relational Data Model SalePerson(SalesPersonID, Name, Address) Client(ClientID, Name, Percentage) Manufacturer(ManufacturerID, Name, PhoneNo, IndustryRank) ManufacturerAddress (ManufacturerID, Purpose, Addres, Foregin key (ManufacturerID) references Manufacturer (ManufacturerID)

A Model Description: Jo Bloggs Auto Traders

   Added on 2020-05-11

BookmarkShareRelated Documents
PART-A1.Model Description:Jo Bloggs Auto Traders is a trading company which sells motor vehicles. The company sells bothnew and used vehicle. The company maintains a database which stores the details of thosevehicles, manufacturer of the vehicle together with the sales details of the vehicle to the customerby the salesperson. Each manufacturer is uniquely identified with the manufacturer id. Thedetails of the manufacturer like name, phone number and industry rank are stored in thedatabase. The manufacture has many addresses each for particular purpose. Each vehicle in thecompany is identified with a vehicle id. Together with the vehicle id the chassis number of thevehicle 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 thedatabase. The car has multiple facilities. The facilities of the car are termed as extra. As each carhas multiple facilities, the facility information of the car is maintained in separate table. For usedvehicle its price and description are alone maintained. The database also maintains saleinformation of the vehicle. The sale information like the client who sells the vehicle, the salesperson who sold it, price it was sold and commission that could be obtained for selling thevehicle. As the sale commission of the vehicle is shared among multiple sales person the detailsof the sale persons involved in the vehicle sale is maintained in the separate table. The client andsales person details like their id, name and address are maintained in the database. 2.Entity Relationship Diagram:3.Relational Data ModelSalePerson(SalesPersonID, Name, Address)Client(ClientID, Name, Address)
A Model Description: Jo Bloggs Auto Traders_1
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 keyVehicleID references Vehicle (VehicleID), Foreign key ManufacturerID referencesManufacturer (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 NameData TypeConstraintsSalesPersonIDINTPRIMARY KEYNameVARCHAR(25)NOT NULLAddressVARCHAR(40)NOT NULLClient Table:Attribute NameData TypeOther ConstraintsClientIDINTPRIMARY KEYNameVARCHAR(25)NOT NULLAddressVARCHAR(40)NOT NULLDiscount Table:Attribute NameData TypeOther ConstraintsDiscountIDINTPRIMARY KEYNameVARCHAR(25)NOT NULLPercentageFLOATNOT NULLManufacturer Table:Attribute NameData TypeOther ConstraintsManufacturerIDINTPRIMARY KEYNameVARCHAR(25)NOT NULLPhonNoINT(10)NOT NULLIndustryRankINTNOT NULL
A Model Description: Jo Bloggs Auto Traders_2

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Model Description of the Trading Company
|3
|736
|354

Relational Database Assignment
|5
|538
|84

ITECH 1006 - Database Management Systems
|14
|4017
|282

Database Management Systems Assignment
|29
|3646
|65

Database Management System
|25
|1773
|306

Rationale & Assumption
|11
|1212
|460