A coach-trip company keeps the company's historical/current trip records and corresponding details about each trip -...

    

Transcribed Image Text:

A coach-trip company keeps the company's historical/current trip records and corresponding details about each trip - including the trip date, city, travel miles, the capacity (maximum number of passengers), the bus driver, the bus used for the trip, and the customer who made the booking. The table below shows some sample records the company keeps. Attribute Name TRIP-CODE TRIP-DATE TRIP-CITY TRIP-MILES MAX-PAX (Capacity) DRIVER CUST-NUM CUST-NAME BUS-REG-NO Sample Value Sample Value Sample Value Sample Va 38633 38635 38636 38637 18-July-2020 19-July-2020 Sydney 8000 28 Perth 1300 28 Thomson 8133 Sally 5101H BMW-3000 BUS-MODEL-CODE BUS-MODEL-SEATS 60 BUS-MODEL-CHG-MILE $13.63 18-July-2020 Melbourne 1500 30 Donny 3818 Janet 5231J CV-580 50 $18.33 Stewart 3318 Selene 5101H BMW-3000 60 $13.63 20-July-2020 Sydney 6830 50 Thomson 8133 Sally 8763P BMW-3000 60 $13.63 www Identify all partial dependencies and transitive dependencies. (Note: Primary keys are identified as TRIP-CODE) You can notate each dependency using text presentation as seen in the following example: (assuming that A, B, C, D, E, and F are attribute names) - partial dependency: A --> B, C transitive dependency: D --> E, F Normalize the table to the third normal form (3NF) and present or draw the dependency diagrams of the final 3NF tables. Note: You are not required to make an effort to improve the existing records. For example, you do not need to change existing attribute names to better-appropriate names. You do not need to add additional attributes to upgrade or improve existing record (attribute) structures. You can use one of the following ways to present dependency diagrams. 1. draw final 3NF dependency diagrams on the paper, use your mobile phone to take a photo, transfer the photo to your computer as an image file, and attach the photo (image) file using the 'Insert Local Files' menu. OR 1. present the final 3NFS using text (table schema). • Example: TRIP (A B, C) . (assuming that TRIP is a table name and A, B, C are attribute names, and A is the primary key) . A coach-trip company keeps the company's historical/current trip records and corresponding details about each trip - including the trip date, city, travel miles, the capacity (maximum number of passengers), the bus driver, the bus used for the trip, and the customer who made the booking. The table below shows some sample records the company keeps. Attribute Name TRIP-CODE TRIP-DATE TRIP-CITY TRIP-MILES MAX-PAX (Capacity) DRIVER CUST-NUM CUST-NAME BUS-REG-NO Sample Value Sample Value Sample Value Sample Va 38633 38635 38636 38637 18-July-2020 19-July-2020 Sydney 8000 28 Perth 1300 28 Thomson 8133 Sally 5101H BMW-3000 BUS-MODEL-CODE BUS-MODEL-SEATS 60 BUS-MODEL-CHG-MILE $13.63 18-July-2020 Melbourne 1500 30 Donny 3818 Janet 5231J CV-580 50 $18.33 Stewart 3318 Selene 5101H BMW-3000 60 $13.63 20-July-2020 Sydney 6830 50 Thomson 8133 Sally 8763P BMW-3000 60 $13.63 www Identify all partial dependencies and transitive dependencies. (Note: Primary keys are identified as TRIP-CODE) You can notate each dependency using text presentation as seen in the following example: (assuming that A, B, C, D, E, and F are attribute names) - partial dependency: A --> B, C transitive dependency: D --> E, F Normalize the table to the third normal form (3NF) and present or draw the dependency diagrams of the final 3NF tables. Note: You are not required to make an effort to improve the existing records. For example, you do not need to change existing attribute names to better-appropriate names. You do not need to add additional attributes to upgrade or improve existing record (attribute) structures. You can use one of the following ways to present dependency diagrams. 1. draw final 3NF dependency diagrams on the paper, use your mobile phone to take a photo, transfer the photo to your computer as an image file, and attach the photo (image) file using the 'Insert Local Files' menu. OR 1. present the final 3NFS using text (table schema). • Example: TRIP (A B, C) . (assuming that TRIP is a table name and A, B, C are attribute names, and A is the primary key) .