Standardization Avoids• Duplication of Data – The same information is listed in multiple lines of the database • Insert Anomaly – A record about an entity can non be inserted into the tabular array without first infixing information about another entity – Can non come in a client without a gross revenues order • Delete Anomaly – A record can non be deleted without canceling a record about a related entity. Can non cancel a gross revenues order without canceling all of the customer’s information. • Update Anomaly – Can non update information without altering information in many topographic points. To update client information. it must be updated for each gross revenues order the client has placed
Standardization is a three phase procedure – After the first phase. the information is said to be in first normal signifier. after the 2nd. it is in 2nd normal signifier. after the 3rd. it is in 3rd normal signifier
Before Standardization
1. Get down with a list of all of the Fieldss that must look in the database. Think of this as one large tabular array. 2. Make non include computed Fieldss3. One topographic point to get down acquiring this information is from a printed papers used by the system. 4. Extra properties besides those for the entities described on the papers can be added to the database.
Before Normalization – Example
See Gross saless Order from below:
William claude dukenfields in the original informations tabular array will be as follows:SalesOrderNo Date CustomerNo CustomCustomerAdderName
The repetition Fieldss will be removed from the original informations tabular array. go forthing the followers. SalesOrderNo Date CustomerNo CustomerName CustomerAdd ClerkNo ClerkName
These two tabular arraies are a database in first normal signifier
What if we did non Normalize the Database to First Normal Form?
Repeat of Data – SO Header informations repeated for every line in gross revenues order.
Standardization: Second Normal Form
• Remove Partial Dependencies.• Functional Dependency The value of one property in a tabular array is determined wholly by the value of another. • Partial Dependency A type of functional dependence where an property is functionally dependent on lone portion of the primary key ( primary key must be a composite key ) . • Create separate tabular array with the functionally dependent informations and the portion of the key on which it depends. Tables created at this measure will normally incorporate descriptions of resources.
Second Normal Form Example
The new tabular array will incorporate the undermentioned Fieldss:ItemNo DescriptionAll of these Fieldss except the primary key will be removed from the original tabular array. The primary key will be left in the original tabular array to let linking of informations: SalesOrderNo ItemNo Qty UnitPrice
Never dainty monetary value as dependant on point. Monetary value may be different for different gross revenues orders ( price reductions. particular clients. etc. ) Along with the unchanged tabular array below. these tabular arraies make up a database in 2nd normal signifier: SalesOrderNo Date CustomerNo CustomerName CustomerAdd ClerkNo ClerkName
What if we did non Normalize the Database to Second Normal Form?
• Repetition of Data – Description would look every clip we had an order for the point • Delete Anomalies – All information about stock list points is stored in the SalesOrderDetail tabular array. Delete a gross revenues order. cancel the point. • Insert Anomalies – To infix an stock list point. must infix gross revenues order. • Update Anomalies – To alter the description. must alter it on every SO.
Standardization: Third Normal Form
• Remove transitive dependences.• Transitive Dependency A type of functional dependence where an property is functionally dependent on an property other than the primary key. Thus its value is merely indirectly determined by the primary key. • Create a separate tabular array incorporating the property and the Fieldss that are functionally dependent on it. Tables created at this measure will normally incorporate descriptions of either resources or agents. Keep a transcript of the cardinal property in the original file.
Third Normal Form Example
The new tabular arraies would be:CustomerNo CustomerName CustomerAdd
ClerkNo ClerkNameAll of these Fieldss except the primary key will be removed from the original tabular array. The primary key will be left in the original tabular array to let linking of informations as follows:
SalesOrderNo Date CustomerNo ClerkNo
Together with the unchanged tabular arraies below. these tabular arraies make up the database in 3rd normal signifier. ItemNo Description
SalesOrderNo ItemNo Qty UnitPrice
What if we did non Normalize the Database to Third Normal Form?
• Repetition of Data – Detail for Cust/Clerk would look on every SO • Delete Anomalies – Delete a gross revenues order. cancel the customer/clerk • Insert Anomalies – To infix a customer/clerk. must infix gross revenues order. • Update Anomalies – To alter the name/address. etc. must alter it on every SO.
Completed Tables in Third Normal Form
Customers: CustomerNo. CustomerName. CustomerAddClerks: ClerkNo. ClerkNameInventory Items: ItemNo. DescriptionGross saless Orders: SalesOrderNo. Date. CustomerNo. ClerkNoSalesOrderDetail: SalesOrderNo. ItemNo. Qty. UnitPrice
