Data Normalization

Data Normalization is a process of refining a database model so that its structures conform to certain standards for the design of tables. The tables and fields you define make up the structure of the database. Good data normalization results in data tables that make sense in a fundamental way. Well-normalized tables are easy to understand when looked at. It is easy to see the kind of data stored and the types of updates needed to be performed. It should be easy to create data entry routines and simple reports directly from normalized tables. In fact, the rule of thumb is: If it's hard to work with a data table, more normalization is probably needed.

Normalization ensures that your database design is the most effective, or in other words, the least likely to change. A normalized database also reduces the chances of repetitive data occurring.

There are five rules involved in normalization. Each of these rules is referred to as a normal form. When you refine a data model to comply with one of these rules, the model is said to be normalized to that rule's degree. For instance, once the design is refined to comply with the first normal form, the design is normalized to the first degree.

Following are some of the advantages of normalization:

• Applying normal form rules to your application sometimes reveals new requirements or rules that have been overlooked.
• Normalization leads to accurate and complete reflection of business requirements and rules.
• Normalization minimizes redundancy by means of referential integrity.
• Normalization creates a logical scheme that minimizes the potential for unintentional results.

Normalizations Rules

First Normal Form (1NF) (Eliminate Repeating Groups)

• All the columns in a table must be single-valued and no repeating groups are allowed. In other words, each column must contain one and only one piece of information, and no two rows can contain the same record of information.
• Example of Single Valued: In the Customer table, a column cannot store both the First Name and Phone Number of a customer. Instead, one column stores the first name, and the other column stores the phone number of a customer.

Second Normal Form (2NF)(Eliminate Redundant Data)

• All the non-key attributes in a table must depend only on the primary key. In other words, no attribute may depend on another attribute in a table, if the latter is not a primary key.

pid	empname	deptid	deptname	deptlocation
100	John Smith	10	Accounts	Florida
101	Adam Scott	20	Security	Virginia
102	John Doe	10	Accounts	Florida
103	Mary Jones	20	Security	Virginia
104	Elizabeth London	30	Technical	Texas

Third Normal Form (3NF) (Eliminate Keys Not Dependent on Primary Key)

• Transitive dependencies are not allowed. In other words, any column which is not a key in the table should not depend on another column not a key in the table.

orderid	orderdate	customerid	productid	product_price
100	20-JUN-99	200	12	20.00
101	30-AUG-99	201	13	12.00
102	25-JAN-98	201	12	20.00
103	07-JUL-99	202	14	15.00
104	15-MAR-00	203	15	14.00
105	24-APR-00	204	16	23.00
106	12-JUN-00	205	17	34.00

Fourth Normal Form (4NF)

• Isolate Independent Multiple Relationships

Fifth Normal Form (5NF)

• Isolate Semantically Related Multiple Relationships

Introduction to Data Modeling

Data Modeling is analogous to an architect's initial developing of a blueprint for a building. Before starting actual construction, the architect must first develop a design based upon the owner's requirements-how many rooms, the number of facilities, parking spaces, etc. The architect must also keep in mind various legal requirements and building codes. Data Modeling involves a similar sort of process, of integrating input from end users, determining and documenting their requirements. Based upon all your gathered information, and incorporating business rules and practices, you construct the data model, or prototype.

The application development process should have a data model. A database inadequately designed can prove to be a disaster, both to the business and to the application itself. The time you spend in analyzing user requirements to develop a solid data model will prove well worth the effort. A data model lets you present user requirements in their proper perspective. In the database design process, you design customized tables to accurately reflect elements that the users wish to maintain and which accurately identifies their characteristics. You also define relationships between the tables, and a structure to integrate all the information in the most efficient manner. Following are the processes to analyze user-requirements, design tables, identify attributes, and establish relationships between tables.

Several software tools are available for modeling a database. Some of these tools, called CASE tools (computer-aided software engineering tools), can be used not only to draw diagrams but also to create documentation of the design. The three main components of these diagrams, called Entity/Relationship diagrams, or E/R Diagrams, are:

• Entities
• Attributes
• Relationships

While designing a database, you need to follow several steps, specifically:

• Identify the need for the database,
• Analyze existing information,
• Identify a list of Entities (Tables),
• Identify a list of Attributes (Columns),
• Identify relationships between Entities (Tables),
• Normalize the design.

Let's consider the database requirements of a certain Mrs. Cruz, who runs a Book Store. Currently she keeps track of all her books in an Excel spreadsheet. Her business is growing and she finds she can no longer keep up with the demand, especially because of increasing difficulties she is having maintaining her Excel spreadsheet. The spreadsheet has no capability of searching by title, author, or ISBN number. Currently, customer orders are manually logged into a registry as they are received. In order to automate all of these features, Mrs. Cruz should design a database for her book store. To help her design such a database, let us walk through the step-by-step process of database design, as outlined above. We will need to help in two ways:

1. Identify the need for a database. The need for the database is usually defined by the client's needs. For example, Mrs. Cruz' needs will include:

• Keeping track of a list of books
• Keeping track of customers buying the books
• Producing a monthly sales report
• Keeping track of all book orders

1. Analyze existing information. This step will involve asking such questions as: Is there an already existing database? If not, how is current data being stored? And so on. This will help in designing tables and columns.

It is important to understand the client's requirements and to prepare specifications, or requirements, document. This document can be submitted to the client so that the client and you agree with the stated requirements. While preparing this document, it is very important to communicate with the end-users of the database and also to understand, as fully as possible, business rules and practices.

The next step is to identify entities and attributes based on the requirements gathered in the above steps. You will identify tables and columns for this database in further topics.

Entities

Entity refers to information of significance in the end-user's working environment. An entity can be considered any substantive object of significance to the business, about which you maintain information. An entity may or may not be a tangible or actual object, such as a person, place, or thing; it may be an abstract activity, like an appointment or an operation. It must, however, be uniquely identifiable. Moreover, every instance or occurrence of the entity must be separate and distinctly identifiable from all other instances of that type of entity. Tables are used to represent entities in a database.

Following are the guidelines to represent an entity:

• Entities are represented in round-cornered boxes, called softboxes:
• Entity names appear in uppercase letters.
• Entity names are nouns, for e.g., CUSTOMER, EMPLOYEE, DEPARTMENT, etc.
• Entity names are singular.

Consider the bookstore example we discussed in the previous topic and identify the entities. Since the bookstore needs to keep track of all the books, you can identify BOOKS as an entity. The bookstore also keeps track of customers and the orders placed by them, so that we will need to establish two more entities called CUSTOMER and ORDERS. You can represent these entities in the E/R Diagram.

Consider another example. You need to design a database for a bank. In this case, you need to keep track of all the accounts for each customer. You will need two entities called ACCOUNT and CUSTOMER, assuming that a customer can have more than one account. Let's say the bank may have several branches and that you need to keep track of all customers individually and their account information by branch. Thus, you would need an entity called BRANCH.

Attributes

Attributes are characteristics or properties of an entity. In other words, any property that helps to describe an entity is called an attribute. An attribute might be either required or optional. When it's required, it must have a value; when it's optional, it may or may not have an assigned value. When an attribute has no value, it is said to be null. Zero and blank spaces are by definition notnull. Examples of typical attributes include name, gender, order date, and quantity. Attributes are analogous to columns in databases.

Following, are the conventional guidelines for accurately representing the attributes of an entity:

• Attributes should help to describe the entity.
• Attributes should appear inside softboxes, located below the name of the appropriate entity.
• Attributes appear in lowercase letters (for example: name, address, phone_number, gender, etc.).
• Attributes are written in the singular.

Consider the book store example, where you defined three entities CUSTOMER, ORDERS, and BOOKS. Let us identify the attributes for each of these entities. For the CUSTOMER entity, you need to record first name, last name, middle initial, address, city, state, zip, and phone number. For the BOOKS entity, you need to record ISBN, title, description, author, year published, and publisher. For the ORDERS entity, you need to keep track of the order number, order date, and shipment type. You can represent these attributes for each of the entities as shown below:

Let's consider another example-a bank database. We will identify the attributes for the entities CUSTOMER, BRANCH, and ACCOUNT. For the BRANCH entity, you can keep track of branch number, address, city, state, and zipcode. For the CUSTOMER entity, you can keep track of first name, last name, middle initial, address, city, state, and phone number. For the ACCOUNT entity you will need the account number, account type, account balance, and date opened.

Relationships

In Relational databases, relationships can exist between data held in different tables. The relationship between tables can be defined according to business requirements and rules. Relationships can be technically defined as bi-directional associations between two or more entities (tables). The four types of relationship are:

• One-to-One
• One-to-Many
• Many-to-Many
• Recursive

Relationships are represented by lines connecting two entities. The following figure illustrates the usual relationship-diagramming conventions:

Representing the relationship in the E/R Diagram depends on carefully considering how the two entities actually relate to each other. For example, consider the relation between the MOVIE and VIDEO_RENTAL entities. The video store may have tapes that have never been rented. In this instance the relation from MOVIE to VIDEO_RENTAL would be optional, which means you would use the dashed line near the VIDEO_RENTAL 's end. On the other hand, anything rented from the store must be a video. This cannot be optional, so we use the solid line near the MOVIE end.

One-to-One Relationships

In One-to-One relationships, the two entities (or tables) are related to each other such that for every row in table X, there exists only one row in the table Y and for every row in table Y, there exists only one row in the table X.

For example, observe the relationship between two tables - EMPLOYEE and EMPLOYEE_CONFIDENTIAL

Each EMPLOYEE can have only one confidential record stored in EMPLOYEE_CONFIDENTIAL table. Each confidential record in EMPLOYEE_CONFIDENTIAL table relates to only one employee in EMPLOYEE table. Hence, there is a One-to-One relationship between these two tables.

Consider the Book Store example discussed earlier. Let's say you want to keep track of certain confidential information relating to customers' credit card accounts, information you will maintain in a different entity called CUSTOMER_CREDITCARD. You need to keep track of only one record for each customer, and let's say it is not mandatory to have such confidential information for each and every customer. You can accomplish this simply by introducing a new, additional entity called CUSOTMER_CREDIT, and by relating it to each CUSTOMER entity as a One-to-One relationship, with an optional relationship for confidential information. This relationship can be represented by dashed line pointing towards the entity called CUSTOMER_CREDIT.

Now consider the Bank database discussed previously. Let's say each branch of the bank also has some confidential information such as a branch security code, locker code, and that every branch must have one and only one confidential record stored separately. In this case, you would need an entity called BRANCH_SECURITY related to the BRANCH entity on a One-to-One basis, with a mandatory relationship on both sides.

One-to-Many Relationships

In One-to-Many relationships, the two entities (or tables) are related to each other such that for every row in table X, there exists one or more rows in table Y, and for every row in table Y, there exists only one row in table X.

For example, observe the relationship between two tables--EMPLOYEE and DEPARTMENT.

Each department can have many employees while each employee can only work in one department. In other words, for each row in the DEPARTMENT table, there could be many corresponding rows in the EMPLOYEE table.

Consider again our Book Store example. A customer can place an order for books any number of times. Furthermore, let's say a customer is entered in the database only if he/she has ordered at least one book. Finally, an order will always relate to only one customer. Based on such business rules and practices, you will have a mandatory One-to-Many relationship between the two entities, CUSTOMER and ORDERS.

Now consider again the example of the Bank database. Let's say each branch of the bank will have many accounts, and that you could have a branch without any accounts. Based on such a business rule or practice, you will have a One-to-Many relationship between BRANCH and ACCOUNT, with an optional relation on the ACCOUNT side, since a branch can exist without any accounts having been opened. Also, let's say a customer can have one or many accounts and that each customer must have at least one account. It follows that you will have a mandatory One-to-Many relationship between CUSTOMER and ACCOUNTS.

Many-to-Many Relationships

In Many-to-Many relationships, the two entities (or tables) are related to each other such that and for every row in table X, there exists one or more rows in table Y, and vice-versa.

When two entities have a Many-to-Many relationship, the storage of repetitive data results, eventually requiring enormous space and becoming increasingly difficult to maintain.You can avoid the Many-to-Many relationship by breaking it into two One-to-Many relationships.

Observe below, for example, the relationship between two tables - ORDERS and PRODUCTS.

In this example, we have a table that stores order information and another table that stores product information. Each order could involve many products and each product could be involved in many orders, leading to a Many-to-Many relationship.

In the Book Store example, where a book can be in one or many orders, and an order can include one or many books, every order should show at least one book ordered. A book in the book's entity may or may not have any orders, meaning it may not have been bought by any customer. In this case the relationship is optional. Therefore, it follows that you have a Many-to-Many relationship between ORDERS and BOOKS, with an optional relation.

Recursive Relationships

In Recursive relationships, an entity has two columns that are related to each other with a hierarchical relationship between the columns. Recursive relationships can, in turn, be One-to-One, One-to-Many or Many-to-Many.

For example, in the employee table, the managers are also considered employees of the company. One manager could be managing several other employees. The Manager column in the EMPLOYEE table thus refers back to the EmployeeID column of the EMPLOYEE table. Such a relationship within a table is called a Recursive relationship.

Identifiers

Identifiers enforce business rules in a database. Identifiers enable you to maintain the integrity of the database. The two identifiers are:

• Primary Key
• Foreign Key

A Primary key is a column or combination of columns whose values uniquely identify a row in a table. Each and every table in the database must have a primary key. The two properties of the primary key are that:

• its value is unique
• its value is not null

Therefore it is said that a primary key's value is definitely known and is unique in the entire table. For example, the VIDEO table can have a unique identifier called video_id. Attributes are identified by their role in the table or entity as follows:

• Unique attributes that are a part of the primary key are represented with the pound symbol, #.
• Mandatory attributes are represented by the * symbol.
• Optional attributes are represented by the letter, o.

A Foreign Key is a column in a table that refers to a primary key of another table. For example, movie_id (foreign key) in the VIDEO_RENTAL table refers to movie_id (primary key) in the MOVIE table. Foreign keys are generally represented as (fk) in the E/R Diagram.

Relational Database Model

Introduction

Inherent limitations of the Hierarchical and Network database models slowly led (with the advent of microcomputer systems) to the increased popularity of Relational database models. Dr. E.F. Codd, who invented the Relational model in the early 1970's while heading a research project sponsored by IBM, based the new model on principles of relational algebra in order to solve problems associated with storing large volumes of data. The fundamental improvements in the Relational model are founded upon the design principles of the table, in which all the data is stored. In the Relational model, each table has a unique name, so that all the user needs to know is the name of the table, and not where or how the data is stored. In traditional databases, by contrast, you would have had to understand how the database was structured, in order to reach the sought-after data. By contrast, now to find the table or data you are looking for in the Relational model, you use a new, specialized language, called Structured Query Language (SQL), in order to "talk" to the database; for example, you can now type a command essentially asking the database to show you all the rows in the video table. This methodology has made the understanding and functionality of database principles easier and more accessible, and, hence, more popular. Furthermore, database administration is now a lot easier, too. Tables in this model can be used not only for storing the actual data, but also for managing access rights to the database; thereby, ensuring data integrity. Everything in the Relational model can be stored in tables. Today, the Relational model has been implemented in a large number of databases, also known as Relational Database Management Systems (RDBMS).
The Relational database model, thus presents the following advantages:

• Ease of Understanding: data is stored in a way that even end-users understand at least at the conceptual level;


• Visible Relationships: the relationships between entities are clearer, no longer embedded or hidden from the end user;


• Data Independence: modifications may be made to the data without making global modifications to the application's structure.

The Relational database stores its data using, of course, what is known as the Relational model. In a Relational database, the data is stored in a set of related tables. The term relational is used because relationships are established between multiple tables. A system that manages a Relational database is referred to as a Relational Database Management System (RDBMS). The data contained in such a database is stored across several tables. To access and manipulate this data, a user types in (or enters) specific commands in a language understood by the database. Structured Query Language (or SQL) is one of the languages used to communicate with Databases.


Object-Relational Database Management Systems

Over the last two decades, a new style of programming has developed, called Object-Oriented Programming (OOP), which differs significantly from traditional programming. Basically, data structures processed by OOP are much more complex than those processed by traditional programming languages. OOP data structures more accurately approximate real-world entities and thus help in representing data as it actually exists in the real world. Because these data structures have remained difficult to incorporate into existing relational DBMS products, a new category of DBMS products, called object-oriented database systems, have evolved, and continue to evolve. They provide certain significant advantages over RDBMS's.
Oracle has extended its RDBMS model to an Object-Relational model, which makes it possible to store object data structures within the Relational model. The Object-Relational model allows users to create user-defined data structures (called object types) and to apply them within the Relational model.
In ORDBMS, users themselves are free to define additional kinds of data types in ways which specify both the structure of the data and the ways of operating (methods) on it. This flexible approach adds value to the data stored in the database because, again, both the structure of the data and the ways of operating on it can be independently specified.
Such user-defined data types are therefore termed object types.
Let us consider a purchase order system. Its tables may contain such data as customer, item, purchase order, etc. The application would need dynamically created status information about the purchase order, such as current value of the shipped or unshipped items, and the total money value of a purchase order. For this, we would normally have to create some sort of data logic within the application (e.g., with triggers), but now, in an ORDBMS, we can simply define the purchase order itself as a datatype (called an object type). This object type will specify those attributes that make up the purchase order and will also specify the operations (called methods) we perform on the data unit or object type (i.e., the purchase order), such as determining the total money value of an individual purchase order. The application thus will not be required to calculate the total value of the purchase order since the logic of the purchase order's structure and behavior is already included in the database schema. The application will no longer need to know the details and thus will not need to keep up with most changes.
So, by using object types, the application need only contain application logic not data logic.

Object Types

An object type is a user-defined data type which encapsulates the data structure along with the operation needed to manipualte it. An object type represents a real-world entity such as a car dealer, employee, and address. By using object types, programs can better reflect the world they seek to emulare.

An object type has three components:

• Name -- the name of the object type
• Attribute -- characteristics representing the structure of the real-world entity
• Methods -- representating behaviours of the real-world entity, and operations the applacation can perform on the real-world entity

OJO: Object types are analogous ti IC's that can be plugged into various electronic devices. To plug an object type into your program, you need only know what it does, not how it works.

Advantages of an Object-Relational Model over Strictly Relational Model

• An ORDMS by design bundles the data structure with the operation performed on it. Consequently, the application is no longer burdened with variations on how to operate on the data object. For instance, in the purchase order example, the application need not write code to calculate the sum of the line items in order to calculate the total cost to the customer. By contrast, while an RDBMS provides a very efficient way of storing and retrieving data, every application developer must first write the necessary code for an operation to be performed on the data.
• Because object types store data in its more natural form and allow applications to retrieve it that way, ORDBMS better represents the real world.
  Since the logic of the object type's structure and behavior is contained within the database schema itself, application logic need not account for most details nor track most changes. Thus, an application need only contain application logic and not data logic.
• Object types can be used across applications, allowing users to be concerned with only what they do, not how they do it.
• User-defined types make it much easier to work with multimedia data such as sound, video, graphics, etc.
• The ORDBMS approach greatly enhances transmission efficiency. For example, a client-side application can request a purchase order from the server and receive all relevant data in a single transmission.

Basic Methodology Common to Both Hierarchical and Network Models

In both Hierarchical and Network models, relationships must be predefined-and therefore fully anticipated before implementing the database. These relationships are represented by means of separate data structures, such as indexes or linked lists. By contrast, in the Relational model, as we will see, relationships are established through data values.

Interestingly, transaction processing systems actually have two specific advantages over systems that rely on storing relationships within their data. The first advantage relates to performance efficiency. When relationships are predefined, data structures can be selected and fine-tuned according to an anticipated workload, thereby optimizing performance. The second advantage relates to control. By control we mean that different users (or applications) can be assigned different, limited tasks. For example, an order-entry program need not access the accounts-payable data. A particular user (or program) is therefore given a restricted view of the data, a feature leading in this one respect to a greater central control.

Hierarchical Database Model

As the name suggests, the Hierarchical database employs a hierarchical structure. The effects of such a structure include:

• All data relationships are assimilated into hierarchies;
  
• Files are arranged in tiers, or layers, of relationships;
  
• Relationships are analogous to the parent-child relationship, though, as we will see, it is significant that in the Hierarchical model, while the parent may have more than one child, the child may have only one parent;
  
• Data access may occur only through predefined relationships, a feature rendering the Hierarchical database model relatively rigid. Just as you cannot create a subfolder without having a main, or parent, folder in Windows Explorer, so you cannot have information about a child in a Hierarchical database without having the corresponding information about the parent.
  
• Repetition of data is required within the database, an inevitable redundancy making the Hierarchical database model very inefficient.

The most common example of the Hierarchical database model is Data Language I (DL/I), implemented on the IMS (Information Management System), and developed by IBM.

Network Database Model

This model is based upon the networking of data relationships and was developed to overcome the inefficiency of the Hierarchical database model. In a Network data model, multiple computers are connected, enabling them to share information and to communicate. Although there have been several kinds of Network models, the major one is the CODASYL DBTG model, named after the committee that developed it. It was the DBTG model that introduced the terms Data Definition Language (DDL) and Data Manipulation Language (DML). The Hierarchical model did not include a DDL, so the CODASYL model was an immediate improvement over the Hierarchical model and over earlier database systems that had only data manipulation capabilities.

Program and Data View Relationship

The Network database model provides for two alternative views of the database:

• Schema-complete logical view of the database (the entire database)
  
• Subschema-a subordinate view of the database, the limited perspective an application program sees; a subset of the database.

Users interact with the database exclusively by means of an application program (no independent interactive query language exists). Moreover, applications view the database through a subschema, as shown below:

Various programs might share subschemas, or each may have its own.

The fundamental building blocks for such a database include:

• Data items-Data items make up a field equivalent to a column in a table.
• Record-A record is a collection of data items.
• Set-A set is a One-to-Many relationship between records. Sets have owners and members. The owner of a set is the parent and the members are the children.

In the Network model, children are allowed to have multiple parents, a feature providing for improved relationships and resulting in a reduction in data redundancy.