Consider the following relation with sample data. DEPARTMENT OF TRANSPORTATION (DOT) PROJECT TABLE Projectib ProjectName CountylD...

  

Transcribed Image Text:

Consider the following relation with sample data. DEPARTMENT OF TRANSPORTATION (DOT) PROJECT TABLE Projectib ProjectName CountylD CountyName ProjectManagerID ProjectManagerName ProjectMilesWithinCounty Road X Wilson M1 Bob 10.00 1. Road X Ottawa M1 Bob 17.00 Road X 3 Davis M1 Bob 12.00 2 Road Y 3 Davis M2 Sue 23.00 Bridge A Wilson M3 Lee 0.50 Bridge A Tunnel Q 2. Ottawa M3 Lee 0.30 4 2. Ottawa M1 Bob 2,00 Road W 4. Pony M4 Bob 23.00 • The DEPARTMENT OF TRANSPORTATION (DOT) Project Table captures the data about projects and their length (in miles). • Each project has a unique Project ID and Project Name. • Each county has a unique County ID and County Name. • Each project manager has a unique Project Manager ID and Project Manager Name. • Each project has one project manager. A project manager can manage several projects. A project can span across several counties. This table records the length of the project in a county in the ProjectMilesWithinCounty column. Question a: Using the above DOT Project Table, describe an example that illustrates the insertion anaomoly. Question b: : Using the above DOT Project Table, describe an example that illustrates the deletion anaomoly. Question c: : Using the above DOT Project Table, describe an example that illustrates the modification anaomoly. Question d: Depict full key dependecies, partial function dependencies (if any), and transative functional dependencies (if any) in the DOT Project Table. Question e: Show the result of normalizing the DOT Project Table to 2NF. Question f: Show the result of normalixing the DOT Project Table to 3NF. Consider the following relation with sample data. DEPARTMENT OF TRANSPORTATION (DOT) PROJECT TABLE Projectib ProjectName CountylD CountyName ProjectManagerID ProjectManagerName ProjectMilesWithinCounty Road X Wilson M1 Bob 10.00 1. Road X Ottawa M1 Bob 17.00 Road X 3 Davis M1 Bob 12.00 2 Road Y 3 Davis M2 Sue 23.00 Bridge A Wilson M3 Lee 0.50 Bridge A Tunnel Q 2. Ottawa M3 Lee 0.30 4 2. Ottawa M1 Bob 2,00 Road W 4. Pony M4 Bob 23.00 • The DEPARTMENT OF TRANSPORTATION (DOT) Project Table captures the data about projects and their length (in miles). • Each project has a unique Project ID and Project Name. • Each county has a unique County ID and County Name. • Each project manager has a unique Project Manager ID and Project Manager Name. • Each project has one project manager. A project manager can manage several projects. A project can span across several counties. This table records the length of the project in a county in the ProjectMilesWithinCounty column. Question a: Using the above DOT Project Table, describe an example that illustrates the insertion anaomoly. Question b: : Using the above DOT Project Table, describe an example that illustrates the deletion anaomoly. Question c: : Using the above DOT Project Table, describe an example that illustrates the modification anaomoly. Question d: Depict full key dependecies, partial function dependencies (if any), and transative functional dependencies (if any) in the DOT Project Table. Question e: Show the result of normalizing the DOT Project Table to 2NF. Question f: Show the result of normalixing the DOT Project Table to 3NF.

Answer rating: 100% (QA)

E46a Insertion anomaly refers to the situation where it is not possible to enter data of certain attributes into the database without entering data of other attributes Assume that there is a project m... View the full answer