A bus company is responsible for offering public transportation in the suburbs of a larg...

a) Functional dependencies between the attributes

• The functional dependency is shown for the bus company offering transportation in the larger metropolitan area.

• Using some assumption and user defined constraints the functional dependencies are mapped in a diagram and it is shown below.

Functional dependency:

• An association between two attributes or two set of attributes in a same relational database table which is having some constraints is known as functional dependency.

• In a table one attribute is functionally dependent on another attribute to take one value.

Functional dependencies for the relation are illustrated below.

• In the above relation,

Functional dependency #1:

• RouteStandardDrivingTime, RouteStartPoint and RouteEndPoint are functionally dependent on RouteID.

Functional dependency #2:

• DriverCertEndDate is functionally dependent on DriverID, VehicleID and DriverCertStartDate.

Functional dependency #3:

• DriverDOB, DateDriverJoinedCompany, DriverLName and DriverFName are functionally dependent on DriverID.

Functional dependency #4:

• VehicleMake, VehicleModel and VehiclePassengerCapacity are functionally dependent on VehicleID.

Functional dependency #5:

• ScheduledArrTime, DriverID and VehicleID are functionally dependent on RouteID, ScheduleDate and ScheduledDepTime.

b) Reason for the relation is not in 3NF

This relation is not shown in 3NF; this is because there are multiple issues within the relation.

• It contains both partial functional dependency and transitive dependency, in the sample data given in textbook it is clearly seen the relation consists of repeating groups, so, the relation is not in 1NF also.

Third normal form (3NF):

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes are dependent on any other non-primary key attribute, then it should be moved or deleted.

o It is termed as transitive dependency.

• From the above definition, it is clear that the relation should not contain transitive dependency in the 3NF.

• Other than dependencies the structure of the relation is having so many issues.

Partial dependency in the relation:

Partial functional dependency:

• An association between two attributes or two set of attributes in a same relational database table which is having some constraints is known as functional dependency.

• In a table if one or more non-primary key attribute is partially dependent on the primary key attribute is known as partial functional dependency.

• In the relation, other attributes are dependent on the full primary key attributes made up of RouteID, ScheduleDate and ScheduledDepTime attributes.

• Hence, other attributes are partially dependent on the primary-key attributes.

Transitive dependency in the relation:

Transitive dependency:

• If any non-primary key attributes are functionally depending on any other non-primary key attribute in the relation then it is termed as transitive dependency.

• Non-primary key attribute DriverID gives driver information, non-primary key attribute VehicleID gives vehicle information and combination of these two attributes determines the driver certification end date information.

• Here, non-primary key attribute DriverID and VehicleID are depending on other non-primary key attributes.

Partial dependency and transitive dependency in the relation must be removed to decompose the relation in 3NF.

Therefore, the given set of data is not in any normal form.

c) Relation in 3NF:

Third normal form (3NF):

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes are dependent on any other non-primary key attribute, then it should be moved or deleted.

o It is termed as transitive dependency.

In this new entity should be created which is used to connect the old and new entities by adding of the relationship.

Based on assumptions and some constraints, the attributes as per the position of relations in third normal form (3NF) are as follows.

Note:

• In the relations, to indicate primary and foreign keys notational conventions are used.

o The attributes noted with underlined name are primary key values.

o The attributes noted with italics letters are foreign key values.

ROUTE relation:

ROUTE (RouteID, RouteStartPoint, RouteEndPoint, RouteStandardDrivingTime)

• RouteID, RouteStartPoint, RouteEndPoint and RouteStandardDrivingTime are the attributes of the relation.

o Here, RouteID is the primary key.

SCHEDULE relation:

SCHEDULE (RouteID, ScheduleDate, ScheduledDepTime, ScheduledArrTime, DriverID, VehicleID)

• RouteID, ScheduleDate, ScheduledDepTime, ScheduledArrTime, DriverID and VehicleID are the attributes of the relation.

o Here, ScheduleDate and ScheduledDepTime is the primary key and composite key attributes.

o Here, RouteID, DriverID and VehicleID are the foreign key attribute which is used to refer ROUTE, DRIVER and VEHICLE relation.

CERTIFICATION relation:

CERTIFICATION (DriverID, VehicleID, DriverCertStartDate, DriverCertEndDate)

• DriverID, VehicleID, DriverCertStartDate, and DriverCertEndDate are the attributes of the relation.

o Here, DriverCertStartDate is the primary key.

o Here, DriverID and VehicleID are the foreign key attribute which is used to refer DRIVER and VEHICLE relation.

DRIVER relation:

DRIVER (DriverID, DriverFName, DriverLName, DateDriverJoinedCompany, DriverDOB)

• DriverID, DriverFName, DriverLName, DateDriverJoinedCompany and DriverDOB are the attributes of the relation.

o Here, DriverID is the primary key.

VEHICLE relation:

VEHICLE (VehicleID, VehicleMake, VehicleModel, VehiclePassengerCapacity)

• VehicleID, VehicleMake, VehicleModel and VehiclePassengerCapacity are the attributes of the relation.

o Here, VehicleID is the primary key.

d) ER (Entity Relationship) diagram for normalized relation:

Entity-relationship model:

• It is used to represent data logically in business areas or in a service organization.

• It is most commonly used model for the communication between the end users and designers, and it is used in the analysis phase of the system development.

• The ER diagram showing the relations is as given below:

Cardinality notations in the relationship:

Explanation:

In the above ER diagram, this system contains the three entities such as ROUTE, SCHEDULE, CERTIFICATION, DRIVER and VEHICLE.

ROUTE entity:

• The entity ROUTE is identified by the identifier has RouteID.

• RouteID, RouteStartPoint, RouteEndPoint and RouteStandardDrivingTime are the attributes of the entity.

SCHEDULE relation:

• The entity SCHEDULE is identified by the identifier has ScheduleID.

• ScheduleDate, ScheduledDepTime, ScheduledArrTime and ScheduleID are the attributes of the entity.

DRIVER relation:

• The entity DRIVER is identified by the identifier has DriverID.

• DriverID, DriverFName, DriverLName, DateDriverJoinedCompany and DriverDOB are the attributes of the entity.

VEHICLE relation:

• The entity VEHICLE is identified by the identifier has VehicleID.

• VehicleID, VehicleMake, VehicleModel and VehiclePassengerCapacity are the attributes of the relation.

CERTIFICATION relation:

• The entity CERTIFICATION is identified by the identifier has CertID.

• DriverID, VehicleID, DriverCertStartDate, and DriverCertEndDate are the attributes of the relation.

Relationship between entities ROUTE and SCHEDULE ,

• The cardinality between the entities is “Mandatory one to mandatory many”.

• This is because a route should have one or many schedules.

Relationship between entities DRIVER and SCHEDULE ,

• The cardinality between the entities is “Mandatory one to mandatory many”.

• This is because a driver should have one or many schedules.

Relationship between entities VEHICLE and SCHEDULE ,

• The cardinality between the entities is “Mandatory one to mandatory many”.

• This is because a vehicle should have one or many schedules.

Relationship between entities DRIVER and CERTIFICATION ,

• The cardinality between the entities is “Mandatory one to mandatory many”.

• This is because a driver should have one or many driving certificates.

Relationship between entities VEHICLE and CERTIFICATION ,

• The cardinality between the entities is “Mandatory one to optional many”.

• This is because a driver may have zero or many driving certificates.

e) Expansion of data model in some key areas:

• To achieve better tracking of bus details and other operations of the company the data model can be expanded.

• One idea of improving clarity in tracking the bus company operations is, the application should track the actual departure time of a scheduled route and it should estimate the expected arrival time automatically.

• This idea will improve cooperation between customer and the company.