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 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 (If you are more comfortable doing this assignment in some other format, it is also acceptable).

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 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 (If you are more comfortable doing this assignment in some other format, it is also acceptable).

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

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

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 (If you are more comfortable doing this assignment in some other format, it is also acceptable).

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

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.