Relational Database Design and Implementation

This assignment focuses on designing and implementing a relational database. Students are tasked with identifying entities, attributes, primary keys, and relationships from a provided case study. They then create an ER diagram, define the relational schema for tables like Vehicles, Salesperson, Manufacturer, Clients, and Sale, normalize the schema to the third normal form, and finally implement the database using MySQL. The assignment also includes supplementary data, assumptions, and a bibliography.

Running head: RELATIONAL DATABASE
Relational Database
Name of the Student:
Name of the University:
Author Note

1
RELATIONAL DATABASE
1. Approach for the modelling Exercise
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.
2. ER Diagram

2
RELATIONAL DATABASE
3. Relational Schema
Vehicles (vehicleId (primarykey), chasisNumber (primarykey), manufacturerName, colour,
Price, Model, discount, engineCapacity, extras, Description, Used/Unused)
Salesperson (salespersonId (primarykey), salespersonName, sAdresss)

3
RELATIONAL DATABASE
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)
4. 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.
5. Assumptions
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.
Bibliography
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

4
RELATIONAL DATABASE
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.