For the modelling exercise the entities are to be identified from provided case study and also attributes for each of the entities are identified for the solution. After the identification of the entities it is also very important to identify the primary keys that were identified for the entities in the system. After the identification of the entities the ER Diagram of the system is designed. In addition to this, the Relation schema are also designed for the systems.
The main entities that are identified in the system are vehicles, clients, salesperson, manufacturer and sale. In addition to this, the main primary key identified for each of the entities are vehiclesId, ClientsId, SalespersonId, ManufacturerId and SaleId. After the relational schema that are designed the tables that were defined in the relation schema are being normalized to the third normal forma and also the relational schema is being reconstructed. After the reconstruction of the tables of the database the actual database is being implemented using MySQL. After the implementation of the database are completed the data are inserted into the database and it is saved. The database would be ready where the queries can be run to fetch the data.
Vehicles (vehicleId (primarykey), chasisNumber (primarykey), manufacturerName, colour, Price, Model, discount, engineCapacity, extras, Description, Used/Unused)
Salesperson (salespersonId (primarykey), salespersonName, sAdresss)
Manufacturer (ManufacturerId (primarykey), manufacturerName, address, addressType telephone, industry rank)
Clients (clientId (primarykey), clientName, cAdresss)
Sale (SaleId (primarykey), vehicleId (foreignkey), Description, ClientId (foreignkey), ClientName, salespersonId (foreignkey), Price)
- Supplementary Data
In additional to the relational schema the additional information about the database are provided here. It is known that the addressType would have a constrain in which the input is only of four types. The Used/Used field in the Vehicles table is a binary having only two types of input.
It is assumed both the data for the used cars and the unused cars are maintained in the same table. Hence only a single table is maintained for the vehicles in the database. The used and unused field in the database is used for indicating whether the car is used or unused.
Bornea, M.A., Dolby, J., Kementsietsidis, A., Srinivas, K., Dantressangle, P., Udrea, O. and Bhattacharjee, B., 2013, June. Building an efficient RDF store over a relational database. In Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data (pp. 121-132). ACM.
Elmasri, R. and Navathe, S.B., 2015. Fundamentals of database systems. Pearson.