Use the table below which shows data about daily departing f

Use the table below which shows data about daily departing flights at an airport to answer the questions which follow. For all diagrams you must use a software application (MS Visio). No age hand drawn images will be accepted Each airline at the airport has a unique Airline ID and an airline Name Each terminal at the 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 airport Each terminal can have multiple airlines assigned to it Each day (date) the table records the number of departing flights at the airport for each Using the AIRPORT Table, describe an example that illustrates the insertion anomaly. Using the AIRPORT Table, describe an example that illustrates the deletion anomaly Using the AIRPORT Table, describe an example that illustrates the modification anomaly. Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the AIRPORT Table. (i.e. draw the dependency diagram) Show the result of normalizing the AIRPORT Table to 2NF (i.e. Show the dependency diagram)

Solution

Anomaly\'s are occurred because of unnormalised tables i.e. If we maintain the database as unnormalised we will face the anomalys. Generally of three types. They are

1. Insertion anomaly:- This occurs when we insert the attributes into the database but it is not possible without the presence of other attributes in the database. we must insert those attributes also along with the one\'s we want to add then it will allow to insert in the database .

For example consider AIRPORT table, for an instance we have a new terminal in the airport, now we are trying to insert the new terminal data in the AIRPORT table but it will not allow us to update untill unless we must assign atleast one airline to that terminal. This is called insertion anomaly.

2. Deletion Anomaly:- This anomaly occurs when certain attributes are lost by deleting other attributes in the table.I.e. Deletion of some tuples leads the deletion of some other information which is not intended to delete.

For example take AIRPORT table, for instance one terminal in the airport is got damaged, now we want to delete this from database untill it is renovated. In this case whenever we delete the terminal from the database the airlines information is also get deleted which are assigned to that terminal.

3. Modification Anomaly:- Whenever we modify the data in the table that will not be updated in all the instances. i.e. modification anomaly exists when one or more instances of updated data is changed but not in all the instances.

For example take airport table, for instance we have updated one terminal id by replacing the previous id but this change of terminal id will not be updated in all the airlines which are assigned to that terminal.This leads to the inconsistency of data.