| Question |
Answer |
| NORMALIZATION |
- the process of eliminating duplicates in a relational database design- converting an entity into tables of progressively smaller degree and cardinality until you reach an optimum level of decomposition |
| What are the Pro's of Normalization? |
- the process of eliminating duplicates in a relational database design- You save space by storing data element only once- It's easier to maintain data consistecy because you have only one instance of the data element to maintain |
| What are the Con's of Normalization? |
- you need to perform expensive join operations again to retrieve related data- It's possible to over normalize |
| What is the most normal of NF's ("Normal Forms")? |
3rd Normal Form |
| Explain First Normal Form (1NF): |
- every column is atomic- it is a valid table, so you separate any repeating groups or multivalued columns- a unique key has been identified for each row- All attributes are functionally dependent on all or part of the key |
| Explain Second Normal Form |
- 1NF+ (ALL RULES OF NORMAL FORM, ANDL..)
- All non-key attribute are fully functionally dependent on the entire key( you are getting rid of partial entities)
|
| What are you looking for when you want to go to 2NF (Second Normal Form)? |
1. Look for composite keys
2. Look for attributes that are dependent on only one of the key columns |
| How to you achieve Third normal form? |
2NF+ (All 2NF rules, and...)
Look for transitive dependencies (are there non-key attributes that depend on another nonkey attribute?) |
| De-Normalization |
bringing back levels of redundancy to increase database performance |
| Pro's of De-Normalization |
- Fewer joins and locks to maintain
- Fewer number of calculations for SQL Server to perform |
| Con's of De-Normalization |
- Multiple instances of the same data (maintenance and data integrity issues)
- Update anomalies will occur |
| Requirements of De-Normalization |
1. data must be up-to-date
1a. Use DML triggers for data that needs to be maintained in real-time.
1b. If 100% accuracy is not important, try running a T-SQL script after hours via SQL Server Agent job
2. data should be non-volatile |
IVC Lesson 11
