Question

1 Approved Answer

Posted on Sep 07, 2024

Assignment: Problem #11 from Chapter #6. (Be sure to note that this is PROBLEM and not REVIEW QUESTION). a.) Write the relational schema and draw

Assignment: Problem #11 from Chapter #6.

(Be sure to note that this is PROBLEM and not REVIEW QUESTION).

a.) Write the relational schema and draw the dependency diagram for the table structure. Make sure you label all dependencies. CHAR_PAX indicated the number of passengers carried. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the nature of the relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip as copilot.)

b.) Decompose the dependency diagram you drew to solve Problem 11a to create table structures that are in 3NF and write the relational schema.

c.) Draw the Crow's Foot ERD to reflect the properly decomposed dependency diagrams you created in Problem 11b. Make sure the ERD yields a database that can track all of the data shown in Problem 11. Show all entities, relationships, connectivities, optionalities, and cardinalities.

image text in transcribed

ble P6.11, do the for the table AX indicates the ed on round-trip to determine the kon has flown 11. Given the sample records in the CHARTER table shown in Table P6.11... following: a. Write the relational schema and draw the dependency diagram for the structure. Make sure that you label all dependencies. CHAR PAX indice number of passengers carried. The CHAR_MILES entry is based on rou miles, including pickup points. (Hint: Look at the data values to determ nature of the relationships. For example, note that employee Melton has two charter trips as pilot and one trip as copilot.) b. Decompose the dependency diagram you drew to solve Problem lla to table structures that are in 3NF and write the relational schema. C. Draw the Crow's Foot ERD to reflect the properly decomposed dependence grams you created in Problem 11b. Make sure the ERD yields a databas can track all of the data shown in Problem 11. Show all entities, relation connectivities, optionalities, and cardinalities. em 11a to create dependency dia a database that es, relationships TABLE P6.11 SAMPLE VALUE 10232 15-Jan-2018 STL 580 784 Brown SAMPLE VALUE 10233 15-Jan-2018 MIA 1,290 231 Hanson ATTRIBUTE NAME CHAR_TRIP CHAR_DATE CHAR_CITY CHAR_MILES CUST_NUM CUST_LNAME CHAR_PAX CHAR_CARGO PILOT COPILOT FLT_ENGINEER LOAD_MASTER AC_NUMBER MODEL_CODE MODEL_SEATS MODEL_CHG_MILE SAMPLE VALUE 10234 16-Jan-2018 TYS 524 544 Bryana 2 348 lbs. Henderson Melton SAMPLE VALUE 10235 17-Jan-2018 ATL 768 784 Brown 5 155 lbs. Melton one 12 235 lbs. Melton 18,940 lbs. Chen Henderson O'Shaski Benkasi 3456Y CV-580 12340 PA31-350 10 $2.79 12340 PA31-350 10 $2.79 2256W PA31-350 10 $2.79 38 $23.36 SAMPLE VALUE 10233 15-Jan-2018 MIA 1,290 231 Hanson 12 SAMPLE VALUE 10234 16-Jan-2018 TYS 524 544 Bryana SAMPLE VALUE 10235 17-Jan-2018 ATL 768 784 Brown 5 TABLE P6.11 ATTRIBUTE NAME SAMPLE VALUE CHAR_TRIP 10232 CHAR DATE 15-Jan-2018 CHAR CITY STL CHAR_MILES 580 CUST_NUM 784 CUST_LNAME Brown CHAR_PAX CHAR_CARGO 235 lbs. PILOT Melton COPILOT FLT ENGINEER LOAD_MASTER AC NUMBER 12340 12240 MODEL_CODE PA31-350 MODEL SEATS 10 MODEL_CHG_MILE $2.79 18,940 lbs. Chen Henderson O'Shaski Benkasi 348 lbs. Henderson Melton 155 lbs. Melton 3456Y 1234Q PA31-350 land 2256W PA31-350 10 CV-580 38 $23.36 10 $2.79 $2.79 ble P6.11, do the for the table AX indicates the ed on round-trip to determine the kon has flown 11. Given the sample records in the CHARTER table shown in Table P6.11... following: a. Write the relational schema and draw the dependency diagram for the structure. Make sure that you label all dependencies. CHAR PAX indice number of passengers carried. The CHAR_MILES entry is based on rou miles, including pickup points. (Hint: Look at the data values to determ nature of the relationships. For example, note that employee Melton has two charter trips as pilot and one trip as copilot.) b. Decompose the dependency diagram you drew to solve Problem lla to table structures that are in 3NF and write the relational schema. C. Draw the Crow's Foot ERD to reflect the properly decomposed dependence grams you created in Problem 11b. Make sure the ERD yields a databas can track all of the data shown in Problem 11. Show all entities, relation connectivities, optionalities, and cardinalities. em 11a to create dependency dia a database that es, relationships TABLE P6.11 SAMPLE VALUE 10232 15-Jan-2018 STL 580 784 Brown SAMPLE VALUE 10233 15-Jan-2018 MIA 1,290 231 Hanson ATTRIBUTE NAME CHAR_TRIP CHAR_DATE CHAR_CITY CHAR_MILES CUST_NUM CUST_LNAME CHAR_PAX CHAR_CARGO PILOT COPILOT FLT_ENGINEER LOAD_MASTER AC_NUMBER MODEL_CODE MODEL_SEATS MODEL_CHG_MILE SAMPLE VALUE 10234 16-Jan-2018 TYS 524 544 Bryana 2 348 lbs. Henderson Melton SAMPLE VALUE 10235 17-Jan-2018 ATL 768 784 Brown 5 155 lbs. Melton one 12 235 lbs. Melton 18,940 lbs. Chen Henderson O'Shaski Benkasi 3456Y CV-580 12340 PA31-350 10 $2.79 12340 PA31-350 10 $2.79 2256W PA31-350 10 $2.79 38 $23.36 SAMPLE VALUE 10233 15-Jan-2018 MIA 1,290 231 Hanson 12 SAMPLE VALUE 10234 16-Jan-2018 TYS 524 544 Bryana SAMPLE VALUE 10235 17-Jan-2018 ATL 768 784 Brown 5 TABLE P6.11 ATTRIBUTE NAME SAMPLE VALUE CHAR_TRIP 10232 CHAR DATE 15-Jan-2018 CHAR CITY STL CHAR_MILES 580 CUST_NUM 784 CUST_LNAME Brown CHAR_PAX CHAR_CARGO 235 lbs. PILOT Melton COPILOT FLT ENGINEER LOAD_MASTER AC NUMBER 12340 12240 MODEL_CODE PA31-350 MODEL SEATS 10 MODEL_CHG_MILE $2.79 18,940 lbs. Chen Henderson O'Shaski Benkasi 348 lbs. Henderson Melton 155 lbs. Melton 3456Y 1234Q PA31-350 land 2256W PA31-350 10 CV-580 38 $23.36 10 $2.79 $2.79