It reduces data redundancies and helps eliminate the data anomalies. Normalization works through a series of stages called normal forms: First normal form (1 NFG) Second normal form (INFO) Third normal form (INFO) ME AS MUM ME NAME Corollaries The highest level of normalization is not always desirable. Facilitates data integration. Reduces data redundancy.
Provides a robust architecture for retrieving and maintaining data. Compliments data modeling. Reduces the chances of data anomalies occurring.A relationship is functionally dependent when one attribute value implies or determines the attribute value for the other attribute. Normalization Benefits The attribute values in a relational table should be functionally dependent (FED) on the primary key value.
Corollary 1: No repeating groups allowed in relational tables. Corollary 2: A relational table should not have attributes involved in a transitive dependency relationship with the primary key.The Need for Normalization Case of a Construction Company Building project Project number, Name, Employees assigned to the project. Employee Employee number, Name, Job classification The company charges its clients by billing the hours spent on each project. The hourly billing rate is dependent on the employee's position.
Deletion Anomaly Problems with the Table 5. 1 The project number is intended to be a primary key, but it contains nulls. The table displays data redundancies. The table entries invite data inconsistencies.The data redundancies yield the following anomalies: Update anomalies.
Addition anomalies. Deletion anomalies. Occurs when the removal of a record results in a loss of important information about an entity. Example: All the information about a customer is contained in an order file, if the order is manacled, all the customer information could be lost when the order record is deleted Solution: Create two tables--one table contains order information and the other table contains customer information.Update Anomaly Occurs when a change of a single attribute in one record requires changes in multiple records staff person changes their telephone number and every potential customer that person ever worked with has to have the corrected number inserted.
Insertion Anomaly Occurs when there does not appear to be any reasonable place to assign attribute values to records in the database. Probably have overlooked a critical entity. U Adding new attributes or entire records when they on new Evaluator's?Do you create a dummy Lead. U Put the employees telephone number in one location--as an attribute in the employee table. U Create a new table with a primary key that contains the relevant or functional dependent attributes. Conversion to First Normal Form A relational table must not contain repeating groups.
Repeating groups can be eliminated by adding the appropriate entry in at least the primary key column(s). (See Database Table 5. 3) Dependency DiagramThe arrows above the entity indicate that the entity attributes are dependent on the combination of PROP_MUM and MME_MUM. The arrows below the dependency diagram indicate less desirable dependencies based on only a part of the primary key partial dependencies.
Database Table 5. 2 The Evergreen Data Figure 5. 1 A Dependency Diagram: First Normal Form 1 NFG Definition The term first normal form (1 NFG) describes the tabular format in which: All the key attributes are defined. There are no repeating groups in the table. All attributes are dependent on the primary key.
