Chapter Exercises

11/7/22, 12:05 AM M04E02 - Chapter Exercises (p. 131) - 22FA DATABASE MANAGEMENT 90147

https://mylearning.suny.edu/d2l/le/content/51044/viewContent/8601342/View 1/4

M04E02 - Chapter Exercises (p. 131)

Instructions

22FA DATABASE MANAGEMENT 90147 AA

4 Points for each sub-part. Make sure to answer the questions completely and in your own words/ideas.

E4.5 Consider the following relation with sample data.

AIRPORT KLX TABLE

Date AirlineID AirlineName TerminalID Num

11-Dec UA United A 20

11-Dec NW Northwest A 20

11-Dec AA American A 20

11-Dec DL Delta B 15

11-Dec JB Jet Blue B 15

12-Dec UA United A 20

12-Dec DL Delta B 15

12-Dec SWA Southwest C 15

• The AIRPORT KLX Taexul1e captures the data about daily departing flights at the KLX Airport.

• Each airline operating at KLX airport has a unique Airline ID and an Airline Name.

• Each terminal at KLX airport has a unique Terminal ID and a fixed Number of Gates.

• Each airline is permanently assigned to one (and only one) terminal at the KLX Airport.

• Each terminal at KLX Airport can have multiple airlines assigned to it.

11/7/22, 12:05 AM M04E02 - Chapter Exercises (p. 131) - 22FA DATABASE MANAGEMENT 90147

https://mylearning.suny.edu/d2l/le/content/51044/viewContent/8601342/View 2/4

• Each day (Date), this taexul1e records the Number of Departing Flights at KLX Airport for each airline.

E4.5a Using the AIRPORT KLX Table, describe an example that illustrates the insertion anomaly.

E4.5b Using the Airport KLX Table, describe an example that illustrates the deletion anomaly.

E4.5c Using the Airport KLX Table, describe an example that illustrates the modification anomaly.

E4.5d Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the Airport KLX Table.

E4.5e Show the result of normalizing the Airport KLX Table to 2NF.

E4.5f Show the result of normalizing the Airport KLX Table to 3NF.

E4.5g Using the set of tables resulting from E4.5f, describe how the anomalies shown in E4.5a, E4.5b, and E4.5c are eliminated.

E4.14 Consider the following relation with sample data.

STUDENT WORKERS

SID Sname FID FName

111 Sarah 1 Prof. Jukic

222 Melanie 1 Prof. Jukic

333 Joe 2 Prof. Jones

444 Amy 2 Prof. Jones

555 Ryan 2 Prof. Jones

666 Joe 3 Prof. Jones

• A student has a unique SID and a non-unique name.

• A faculty member has a unique FID and a non-unique name.

• A student can work for only one faculty member.

• A faculty member can have multiple students working for him or her.

• The start date when a student started working for the faculty member is recorded.

E4.14a Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the STUDENT WORKERS Table.

E4.14b Show the result of normalizing the STUDENT WORKERS Table to 2NF.

11/7/22, 12:05 AM M04E02 - Chapter Exercises (p. 131) - 22FA DATABASE MANAGEMENT 90147

https://mylearning.suny.edu/d2l/le/content/51044/viewContent/8601342/View 3/4

Submissions

No submissions yet. Drag and drop to upload your assignment below.

Drop files here, or click below!

E4.14c Show the result of normalizing the STUDENT WORKERS Table to 3NF.

E4.16 Consider the following table with sample data.

BANK ACCOUNTS TABLE

AccountID AccountType CurrentBalance A

A111 Checking $1,000.00 C

C

A222 Checking $2,000.00 C

A333 Money Market $15,000.00 C

C

C

A444 Savings $3,000.00 C

C

E4.16a Normalize the Bank Accounts Table to 1NF by increasing the number of records in the existing table.

E4.16b Normalize the Bank Accounts Table to 1NF by creating a new separate table in addition to the existing table.

E4.16c Normalize the Bank Accounts Table to 2NF and describe what you've changed

E4.16d Normalize the Bank Accounts Table to 3NF and describe what you've changed

Make sure to copy the instructions and type your answers below the questions.

11/7/22, 12:05 AM M04E02 - Chapter Exercises (p. 131) - 22FA DATABASE MANAGEMENT 90147

https://mylearning.suny.edu/d2l/le/content/51044/viewContent/8601342/View 4/4

Overdue - 6 minutes ago

Upload Choose Existing

You can upload files up to a maximum of 2 GB.

Task: Submit to complete this assignment

Last Visited Nov 6, 2022 12:54 PM

Record

Activity Details