Normalization Flashcards
What is the normalization process used for?
We use the normalization process to group data elements into tables that represent entities.
What is normalization?
Normalization is the process of organizing data to minimize redundancy. It does not have storage anomalies.
How is normalization accomplished?
The organization of data is done by applying a series of rules to a data model.
What are the 4 most common stages of normal forms?
- 0 Normal Form (0NF): un-normalized model before the normalization rules have been applied - First Normal Form (1NF) - Second Normal Form (2NF) - Third Normal Form (3NF
What is the downside of normalization?
While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into many different tables This will require most queries to use table joins
How do you get to First Normal Form (1NF)?
- Reduce entities to first normal form (1NF) by removing repeating or multi-valued attributes to another, child entity - To simplify, you cannot have multiple fields to capture multiple values - Most common ‘sins’ against the first normal form are the lack of a primary key and the use of repeating columns - Also, remove calculated field
How do you get to Second Normal Form (2NF)?
- Reduce first normal form entities to second normal form (2NF) by removing attributes that are not dependent on the whole primary key - The primary key for each record must be able to determine the value for all of the other fields in the record
How do you get to Third Normal Form (3NF)?
- Third normal form is violated when a non-key field is a fact about another non-key field - Reduce second normal form entities to third normal form (3NF) by removing attributes that are dependent on other attribute
Quickly explain what the three normal forms eliminate.
1NF - Eliminate repeating groups and derived attributes. 2NF - Eliminate Redundant Data. 3NF - Eliminate Columns not dependent on key.
What are the Anomalies in 1NF?
Insertion Anomaly, Update Anomaly, Deletion Anomaly
Go into detail on the 1NF Insertion Anomaly.
Recall the first normal form again for the Purchase Order. If the database had only this table in it, we could not set up a Vendor or Item unless we had all the PO information
Go into detail on the 1NF Update Anomaly.
If the Description for an item changed, we would have to find the Description and change all occurrence
Go into detail on the 1NF Deletion Anomaly
Deleting the rows for a Purchase Order causes the loss of Vendor and Item information
What are the advantages on the 2NF anomalies?
INSERTION– An item can be added by itself without PO information UPDATE– If the Description for an item changed, only one occurrence would be affected DELETION– deleting a row for a PO has no effect on an Item record
What are the disadvantages on the 2NF anomalies?
INSERTION– Vendor cannot be set up by itself UPDATE– multiple records may need to be updated if Vendor address information changed DELETION– deleting rows for a PO causes loss of Vendor information
Advantages of 3NF
- Vendor can be set up by itself. - Only one record needs updating if vendor address changes - Deleting rows for a PO does not cause loss of vendor information
How would you take this Unnormalized table to 1NF?
By Populating STU_NO
What is the key for this table in 1NF?
STU_NO + FEE
How would you turn this 2NF to 3NF?
How would you display a composite key in an ERD?
What is a natural key?
A natural key is one that “naturally” occurs in
the data, like nursing unit id in CHD
What is a surrogate key?
A surrogate key does not naturally occur in the
data and is usually a meaningless but unique
number (MBUN
What Is OLTP and what does it stand for?
All examples considered thus far have been geared to Online Transaction Processing (OLTP)
Most common
Normalization rules promote unique (non redundant) data
What Is OLAP and what does it stand for?
Why would data be collected, if it wasn’t going to be analyzed too?
Online Analytical Processing (OLAP) is one of the other significant database uses
Business Intelligence (BI) tools work with OLAP data to provide end users with the ability to analyze and report on the data in whatever way makes sense to the particular busines
What are OLAP’s main attributes?
- OLAP emphasizes speed and simplicity of access over data integrity
- In fact, OLAP databases are explicitly de-normalized for this purpose
- Data is also pre-summarized according to expected queries
- Also known as Big Data or a Data Warehouse
What is a Data Warehouse?
Offloading data from the OLTP to the OLAP database allows each database to be optimized for its particular task
This separation ensures that complex, heavy inquiries into the OLAP database don’t cause slow downs in the “production” databas
