ABC Vehicle Company is planning to maintain their record properly on the software. In this regard they have hired a software manager to perform this task. The software manager first plans to collect data and create database. Following is the data collected:

The company sells vehicles and also rent out their vehicles too

For each vehicle company assign it a unique Vehicle_id and also maintain the record of its model, price, engine size, No of seats, tonnage and manufacturing date.

Also company maintains customer record with the vehicle record to easily identify that which customer bought which vehicle.

The company maintains a separate record of employees also because for same model of vehicles only one employee is assigned for its management.

One employee can maintain atmost three different models but atleast 2 same models.

The company maintains the daily sales record also for each vehicle also.

Name of working employees with their working hrs: Ali (Morning), Ali (Evening), Asif (Morning), Adnan (Morning), Mubashir (Morning), Salman (Evening) and Atif (Evening).

Employee id: 001,002,003,004,005,006 and 007 respectively.

Vehicle model with their quantity: Honda (2), Cultus (1), Ferrari (4), Toyota (6), Suzuki (4) and Passo (3)

Engine Size: 4, 6, 7, 2, 7 and 8 respectively

Note:

      You can set random values for not mentioned columns.

      You can assign random employees to random vehicles by your own choice.

 

Tasks to perform:

Formulate the above data in the form of relations

Apply 2NF and 3NF on the given scenario.

Apply inner join on any of two relations with proper justification.

Select those employees who maintain same model but their working hrs are different (Relational Algebra)

Show collective data of employees and vehicles (Relational Algebra).