ICT701 Relational Database Systems

Students are to create a database design specification (entity relationship diagram (ERD) and relational data model (RDM)) from the given business description below. Students are to present this in a report (as specified in the submission format) which also includes a short discussion of the approach for the solution. This assignment is

to be carried out and reported individually.

The following defines the minimum information that should be reported:

1. A brief discussion of your solution, i.e. how you approached the modelling problem and any issues you may have encountered (maximum of ½ page).
2. The Entity Relationship Diagram (ERD) (produced using a drawing tool such as those found in MS Word or PowerPoint, or a chosen CASE tool) for the system. Your ERD must use the ER notation that was taught in ICT701. Any assumptions made in creating the ERD must be explicitly stated in your report.
3. The Relational Data Model (RDM) which corresponds to the ERD. That is, the set of tables in 3rd normal formthat could be used to represent the whole of this data model. Your RDM should identify primary, alternate and foreign keys.

Jo Bloggs Auto Traders sells motor vehicles. They sell new and used vehicles. For all vehicles, they store the unique vehicle id and the chassis number. For new vehicles, they also store information about the manufacturer name, colour, price, discount (there are 44 types of discount, each of a fixed percentage), engine capacity and details about extras, which would include car radios, air conditioning, etc.

For used cars, they only store vehicle id, price and description. Each new vehicle has only one manufacturer (but a manufacturer may supply many different vehicles), and they store details about the manufacturer’s name, address, telephone and industry rank. A manufacturer may have many addresses, each of a particular type (e.g. postal) and one telephone number only per address. For new cars, each car may have many extras, for example ‘RA’ would signify car radio. Jo Bloggs employs many salespeople who sell to clients. For salesperson and client, they store name and address. For each sale, they store the client details, the vehicle details, the price and the commission to the salesperson. An interesting point: only one client may buy a particular vehicle but several salespeople may share the sale.

Resolve the following relation to 3^rd Normal Form. It is a requirement to show both the 1NF and 2NF that you found as part of the process in moving to 3NF.

COMPACT_DISK(title number, album name, distributor id, distributor name,((track number, track name, track duration, ((artist id, artist name, date of birth, age, instrument_type, instrument description )) )) )

NOTE: The brackets in the above unnormalised relation data indicate non-atomic attributes (or repeating groups)

Complete the following SQL tasks using MySQL. The marks available for each SQL task are indicated.

• Write the SQL code that will create the table structure for a table named EMPLOYEE. The basic EMPLOYEE table structure is summarized in the table below.

Answer:

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.

1. ER Diagram

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)

1. 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.

2. 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 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.