Report on Relational Data Model of Sale

Added on - 10 May 2020

  • 4


  • 815


  • 130


  • 0


Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 2 of 4 pages
PART-A1.Model Description:Jo Bloggs Auto Traders is atrading 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)
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 keyVehicleIDreferencesVehicle(VehicleID), Foreign keyManufacturerIDreferencesManufacturer (ManufacturerID), Foreign keyDiscountIDreference Discount (DiscountID))Extras(VehicleID, Extras,Foreign keyVehicleIDreferencesVehicle(VehicleID))Sale(VehicleID, ClientID, Price,Commission, Foreign keyVehicleIDreferencesVehicle(VehicleID), foreign key ClientID references Client (ClientID))SalesOfSalesPerson(VehicleID, SalesPersonID, Foreign keyVehicleIDreferencesVehicle(VehicleID), Foreign keySalesPersonIDreferences 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
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document