Looking at the stats of Yackity Blog Blog, I noticed that First 3 Rules of Data Normalization for Newbies gets a lot of hits. So, I thought today I would write about a very basic concept - the Basic Constructs of Entity Relationship Diagrams (ERD).

The three basic constructs are:

1. Entity: A significant thing (place, person, concept, event) which information needs to be known or held
2. Attribute: A characteristic that serves to identify, quantify, classify or qualify an entity
3. Relationship: The way in which two entities are related

In ERD,
Entity is mapped to Table

Attribute is mapped to Column

Relationship is mapped to Foreign Key

Click to view original size

Getting input from the users increases the quality of a data model. Joint Application Development (JAD) sessions facilitate and accelerate the modeling process. During the JAD sessions, subject matter experts validate the model with respect to supported and identified functions and processes.

CRUD (Create Read Update Delete) Matrix is one technique to map the data model to the process model. The CRUD matrix helps identify:

• Missing Process : A business process exists that is not supported by the model; The data model needs to be adjusted to include the missing process
• Redundant Data Entity : A data entity exists that is not required to support a process

Think of CRUD in terms of how the data and process interact with each other. Using the simple example from the First 3 Rules of Data Normalization for Newbies, below is a simple example of a CRUD matrix:

Click to view original size

In this example, the process “Establish New Employee”, the entities (can be thought of as tables) of the model that are required to support the process are Employee, Employee Dependents, Warehouse (which warehouse will the new employee be assigned to).

Let us assume further that this model is only for employees and their training needs. Having an entity Product is a redundant entity. Of course, one can leave that entity in the model. However, it shows that for the scope of this particular model, it is not needed.

This is the last of the series on the 3 Rules of Normalization for Newbies. If you have forgotten the first 2 rules, review the 2 previous blogs because inherent to the 3NF Rule is compliance with the first two.

The 3NF Rule states: "No non-UID can depend upon another non-UID attribute." This basically means an entity is in the Third Normal Form if it is in 2NF and every one of its attributes is NOT dependent on any other describing attribute.

Let us consider the following example:

Training_Score is dependent on Training_Date. To make this in 3NF, move Training_Score and Training_Date to the entity Training.

So, this concludes our series on the 3 First Normalization Rules. The complete picture of our simple example is:

In yesterday's blog, I discussed 1NF. Today, as promised, is the blog about 2NF.

The 2NF Rule states: "An attribute must depend upon its entity's entire UID". This basically means that every one of the attributes is functionally dependent on the whole key. Also, implicit to this rule is the fact that the entitiy is in its 1NF. So, if you have forgotten what 1NF is all about, review yesterday's blog.

Consider the following example:

The attribute Warehouse_Location is not functionally dependent on Emp_ID. To make this Entity Relationship Diagram in 2NF, move the attribute Warehouse_Location to the entity Warehouse. To be compliant with the 1NF, Warehouse_Location is put in its basic form as Warehouse_Address1, Warehouse_City, Warehouse_State, Warehouse_ZIPCode.

This will result in:

Tomorrow's blog is the last installment of this series on the First 3 Rules of Normalization.

The First Normal Form States: "All attributes are not repeated or must be single-valued"

In identifying whether a table is in its 1NF, consider this 3-points checklist:
1. Does a Primary Key exist?
2. Have you ensured that each attribute is in its basic form? Being in its basic form means that it is not derived
3. Have you ensured that the attribute is not repeated and considered single-valued?

Let us examine the following simple example :

1. Existence of a Primary Key: EMPLOYEE is uniquely identified by its attribute Employee_ID.

2. Verification that the attribute is in its basic form: The attribute Emp_Age is derived. Emp_Age is broken to its basic form, e.g. Emp_DateOfBirth.

3. Verification that the attribute is not repeating: In the example, Emp_Dependent, Emp_DependentNM, Emp_DependentDOB can be classified as repeating occurrences of EmployeeDependent. To make it in 1NF, move these to its own Entity.

The final result of the example will be:

Next blog will be on the 2NF.