Dictionary and Normalization Guidelines

CSS/559 - Data Base Concepts II

Data Dictionary and Normalization Guidelines

The purpose of a data dictionary is to define the objects and data within an organization's database management system. This metadata does not contain any of the database's data, but only information about the structure, storage and use of the database's data. A data dictionary can be stored in files or documents, or can be stored within a database in the DBMS.

For example, a database may contain an employee table that has an emp_no column and an identification_no column. A data dictionary can explain that the emp_no column is an integer value containing the employee number assigned by the system. When a new employee is added to the system, a next number value is retrieved from a next number table and assigned to the new employee record. The data dictionary would also explain that the identification_no column is an alpha-numeric value that contains the employee's social security number for US residents and for non-US residents contains the employee's country code followed by their work visa number.

The data dictionary can also be used to define aliases for the column names since column names can become a bit cryptic. It can also provide information regarding different type of database objects such as tables, views, stored procedure, user groups, functions, triggers, etc. Furthermore, it can also provide information on how database objects are related to one another.

An important aspect of a data dictionary is that it provides database administrators, programmers, and any other user of the database data a clear, consistent and centralized source of information about the database objects and the data stored within or accessed by them. This helps to ensure that the data is used appropriately and can reduce data redundancy. A data dictionary is also important when you are integrating your data with another system, since you will be certain of which data needs to be integrated.

Using a data dictionary makes it easier for users to analyze the data and will help to enforce programming standards as well. Additionally, when database changes need to be made, a data dictionary will help you to estimate the effect of the change on the whole database. Therefore, a data dictionary can help you to maintain a normalized database structure.

Database normalization is "the process of efficiently organizing data in a database" (Chapple, M). The goals of normalization are "to eliminate redundant data and ensure data dependencies make sense" (Chapple, M). Normalization may seem like a difficult task, but the principles are simple ideas that are easy to apply and have many benefits including a more efficient, flexible and easier to maintain database structure, an avoidance of redundant fields, a better understanding of your data, and fewer costly surprises in the future (Adams, D. & Beckett, D., 2004).

There are guidelines or rules that, when followed ensure a normalized database. If a database follows the first rule, it is said to be in first normal form (1NF), and if it follows the first three rules then it is said to be in third normal form (3NF). As you can see, the normalization forms are cumulative, and a database can achieve up to fifth normal form.

To achieve first normal form you must eliminate duplicative columns from the same table, create separate tables for each group of related data and identify each group of related data with a primary key. This also means that you cannot use multiple fields in a single table to store similar data. For example, in a customer table you cannot have a "contact1" field and a "contact2" field. The contact information needs to be moved into its own table.

Second normal form