Consider the following relation with sample data DEPARTMENT OF TRANSPORTATION (D

Question

Consider the following relation with sample data DEPARTMENT OF TRANSPORTATION (DOT) PROJECT TABLE ProjectiD ProjectName CountylD CountyName ProjectManagerlD Road X Road X Road X Road Y Bridge A 1 Bridge A 2 Tunnel O ProjectManagerName ProjectMilesWithinCounty Bob Bob Bob Sue Wilson Ottawa Davis M1 M1 M1 M2 M3 M3 M1 M4 10.00 17.00 12.00 23.00 0.50 0.30 2.00 23.00 3 3 Wilson Ottawa Ottawa Lee Bob Bob 5 Road W The DEPARTMENT OF TRANSPORTATION (DOT) Project Table captures the data about projects and their . Each project has one project manager . A project manager can manage several projects. e A project can span across several counties. . This table records the length of the project in a county in 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 the ProjectMilesWithinCounty colunn. Project Manager Name 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

Explanation / Answer

Anomalies in database in poorly planned , unnormalised databases where all the data is stored in one table.

Question a:
An insert anomaly occurs when certain attributes cannot be inserted into the database without the presence of others.
Example:
Say a new project say RoadZ with id 6 is approved but has not been assigned a project manager yet.
Therefore this information cannot be added in database since project manager id cannot be null (because it is unique)

Question b:
A delete anomaly occurs when certain attributes are lost due to deletion of other attributes.
Example:
If we remove the entity ProjectID:2 from the database, the details of Project manager "Sue" and her ID gets deleted as well.
This implies the corresponding
project manager details also get deleted from database.

Question c:
A modification anomaly occurs when one or more instances of duplicated data is updated, but not all.
Example:
Name of projectID 1 has changed from RoadX to RoadS. For updating this single entity ProjectName, we have to update projectName
for all rows where projectID is 1.
If update of all rows do not take place, it leads to inconsistent data

Question d:
Full key dependencies are shown below:

ProjectID -> ProjectName (this is because ProjectName is dependant on ProjectID
CountryID -> CountryName (similar to above)
ProjectManagerID -> ProjectManagerName (similar to above)

Partial functional dependencies are shown below:
The entity ProjectMilesWithinCountry depends on ProjectID and CountryID
therefore ProjectID,CountryID -> ProjectMilesWithinCountry
then {ProjectID -> ProjectMilesWithinCountry, CountryID -> ProjectMilesWithinCountry} are the partial dependencies.

There are no transitive dependencies

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.