RouteID

RouteStartPoint

RouteEndPoint

RouteDrivingTime

ScheduleDate

ScheduleDepTime

ScheduleArrTime

DriverID

DriverName

DateJoin

TruckID

TruckMake

TruckModel

DriverCerStartDate

DriverCertEndDate

230

London

Paris

7 Hours

3/30/17

5:25 a.m.

12:25 P.m.

1233

TJ Smith

4/2/12

603

Ford

230

6/10/12

6/9/20

3/30/17

9:30 a.m.

16:55 p.m.

3451

P Pierre

10/12/16

607

Ford

F200

4/12/12

4/11/22

3/30/17

10:30 a.m.

17:55 p.m.

1122

J Patrick

2/24/95

245

Chevy

Midi

8/20/12

8/18/19

561

Amsterdam

Athens

29 Hours

3/31/17

7:00 a.m.

12:00 p.m. (next day)

2001

D Escobar

4/1/11

456

Ford

230

7/3/10

1/31/21

Description:

A transportation company moves heavy machinery to and from different locations in Europe. The company has a fleet of commercial trucks, which are classified in several categories: Class B, Class BE, Class C1 and others. With its increasing needs to manage significant amount of transportation data, the company would like to enhance its existing database to keep track of a new fleet of trucks, drivers, travel routes and many of schedule departures and arrival every day. European Community member states require transportation companies to store data on drivers and the commercial trucks they are allowed to drive. For example, a Class B driver is not allowed to drive Class BE unless he/she is certified to drive Class BE. As a business rule, the database needs to ensure that a certification is specific to a particular Driver-Truck pair to prevent violations.

Required:

• The data given is un-normalized, you are required to run the data through 1NF, 2NF and 3NF tests and normalize as necessary. Normalization will lead to multiple tables that can be the basis for a proto type
• Must do: Include 1NF and 2NF; showing 3NF ONLY will not receive full credit even if it is correct
• Provide tables for each NF, no need to describe what you are doing, unless you have assumptions
• Underline Primary Keys and use dashed foreign keys

Use the table relationship illustration below to complete this assignment in Excel

Table 1 Primary Key Attribute1 Attribute2 Foreign Key Table 2 Primary Key Attribute1