ANOMALIESUpdate AnomalyWhat if the name of the project PPV5678 is changed to Sludge disposal?Insertion AnomalyWhat if a new project is negotiated when there is no employee assigned to it yet?What if a new employee joins who is not yet assigned to any project?Deletion AnomalyWhat if Judy resigns from her job? What happens to the Sludge Floatation and N2O generation projects?68
NORMALIZATIONNormalization is a process that enables a database designer to eliminate design problems by decomposing (or reorganizing) the existing table structuresThe benefi ts of Normalization are,a model that better represents the actual business situationa model with less redundancyin the dataa model which allows easy updateof dataa model that allows for accurate query results70
NORMALIZATION (CONT’D)Normalization•is a step-by-step process•has several levels of normalization–first, second, and third levels are what we will consider•If a data model has passed the first level, we say that it is in first normal form•If it has passed the second level, second normal form•Etc.
NORMALIZATION (CONT’D)Normalization Example•A business keeps data on its employees–ID, first name, last name, rank, department, salary•Rank is a number (e.g., 1 for lowest, 5 for highest)•An employee’s salary depends only on the rank and department of the employee•Employees can take extra training courses, and this data is stored as well–Course ID, course description, completion date
NORMALIZATION (CONT’D)Data Model #1
NORMALIZATION (CONT’D)First Normal Form (1NF)A model is in 1NF if, for every instance of an entity, each attribute of the instance is completely determined by the instance’s PKIn other words, 1NF holds when there are no instance attributes that have multiple valuesTo put an ERD into 1NF, add another entity and a 1:M relationship to it
NORMALIZATION (CONT’D)Data Model #1Data Model #2 (in 1NF)
NORMALIZATION (CONT’D)Second Normal Form (2NF)A model is in 2NF if•it is in 1NF, and•for every instance of an entity having a PK