Hour 4: The Normalization Process

duplication

Data should not be redundant, which means that the _____________ of data should be kept to a minimum for several reasons. For example, it is unnecessary to store an employee's home address in more than one table. With duplicate data, unnecessary space is used.

If the table has redundant groups of data, this data would be a candidate to remove into a separate table.

How do you determine if data needs to be moved to a separate table when normalizing your database?

It can be more advantageous. However, denormalization, to a point, could be more advantageous. Remember, many factors help determine which way to go. You will probably normalize your database to reduce repetition in the database, but you might turn around and denormalize to a certain extent to improve performance.

It seems to me that normalization is more advantageous than denormalization. Do you agree?

logical model

Logical database design, also referred to as the ________________, is the process of arranging data into organized groups of objects that can easily be maintained. The logical design of a database should reduce data repetition or go so far as to completely eliminate it

+ Greater overall database organization + Reduction of redundant data + Data consistency within the database + A much more flexible database design + A better handle on database security + Enforces concept of referential integrity

Normalization provides numerous benefits to a database. Some of the major benefits include the following:

data integrity

Reducing duplicate data increases _______________, or the assurance of consistent and accurate data within a database

third normal form's *Explanation*: Another table was created to display the use of the third normal form. EMPLOYEE_ PAY_TBL is split into two tables, one table containing the actual employee pay information and the other containing the position descriptions, which really do not need to reside in EMPLOYEE_PAY_TBL.

The _______________ objective is to remove data in a table that is not dependent on the primary key.

+ The first normal form + The second normal form + The third normal form

The following are the three most common normal forms in the normalization process:

first normal form *Explanation*: To achieve the first normal form, data had to be broken into logical units of related information, each having a primary key and ensuring that there are no repeated groups in any of the tables. Instead of one large table, there are now smaller, more manageable tables: EMPLOYEE_TBL, CUSTOMER_TBL, and PRODUCTS_ TBL

The objective of the _________________ is to divide the base data into logical units called tables. When each table has been designed, a primary key is assigned to most or all tables.

second normal form *Explanation*: The second normal form is derived from the first normal form by further breaking two tables into more specific units. EMPLOYEE_TBL is split into two tables called EMPLOYEE_TBL and EMPLOYEE_PAY_TBL. Personal employee information is dependent on the primary key (EMP_ID), so that information remained in the EMPLOYEE_TBL (EMP_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS, CITY, STATE, ZIP, PHONE, and PAGER). CUSTOMER_TBL is split into two tables called CUSTOMER_TBL and ORDERS_TBL. What took place is similar to what occurred in the EMPLOYEE_TBL. Columns that were partly dependent on the primary key were directed to another table. The order information for a customer is dependent on each CUST_ID, but does not directly depend on the general customer information in the original table.

The objective of the _________________ is to take data that is only partly dependent on the primary key and enter that data into another table.

False Not always; normalization can and does slow performance because more tables must be joined, which results in more I/O and CPU time.

True or false: Having no duplicate or redundant data in a database, and having everything in the database normalized, is always the best way to go.

True

True or false: If data is in the third normal form, it is automatically in the first and second normal forms.

True

True or false: Normalization is the process of grouping data into logical related groups.

Having redundant and duplicate data takes up valuable space; it is harder to code, and much more data maintenance is required.

What are some major disadvantages of denormalization?

Overnormalization can lead to excess CPU and memory utilization, which can put excess strain on the server.

What are the disadvantages of over-normalizing your database design?

The major advantage is improved performance.

What is a major advantage of a denormalized database versus a normalized database?

Normal form

_______________ is a way of measuring the levels, or depth, to which a database has been normalized. A database's level of normalization is determined by the normal form.

Referential integrity

_______________ simply means that the values of one column in a table depend on the values of a column in another table

Normalization

_________________ is a process of reducing redundancies of data in a database. Normalization is a technique that is used when designing and redesigning a database. Normalization is a process or set of guidelines used to optimally design a database to reduce redundant data. The actual guidelines of normalization, called normal forms, will be discussed later in this hour.

Denormalization

_______________________ is the process of taking a normalized database and modifying table structures to allow controlled redundancy for increased database performance. Attempting to improve performance is the only reason to ever denormalize a database.