Database Normalization

Database Normalization

• Database normalization is the process of organizing data in a database to minimize data redundancy and dependency.
• Normalization rules are divided into normal forms, with each normal form having specific rules to follow.
• The goal of normalization is to ensure that each piece of data is stored in one place and one place only.

Functional Dependencies

• A functional dependency is a relationship between two attributes where the value of one attribute determines the value of another.
• Transitive dependency is when a third attribute is dependent on a second attribute, which is dependent on a first attribute.

Normal Forms

• First Normal Form (1NF): Eliminate repeating groups in individual tables. Create a separate table for each set of related data.
• Second Normal Form (2NF): Create separate tables for sets of values that apply to multiple records. Relate these tables with a foreign key.
• Third Normal Form (3NF): Eliminate fields that do not depend on the key.

Example of Normalization

• Unnormalized form: Multiple tables with repeating groups.
• 1NF: Eliminate repeating groups and create separate tables for each set of related data.
• 2NF: Create separate tables for sets of values that apply to multiple records.
• 3NF: Eliminate fields that do not depend on the key.

Entity Relationship Diagram (ERD)

• An ERD is a visual representation of data that shows the relationships between entities.
• Entities are things of interest in a system, such as customers, orders, and products.
• Attributes are items of data held about an entity.

Data Modeling

• Data modeling involves examining the data objects in a system and identifying the relationships between these objects.
• A data model is a detailed model that captures the overall structure of organizational data.
• The purpose of a data model is to document the logical structure of a database.

Entity, Attribute, and Relationship

• An entity is a thing of interest in a system, such as a customer or an order.
• An attribute is an item of data held about an entity, such as a customer's name or address.
• A relationship is a connection between two entities, such as a customer placing an order.

Keys

• A primary key is an attribute or set of attributes that uniquely identifies an entity instance.
• A foreign key is a copy of a primary key created in a child table to form a relationship with a parent table.
• A unique key is an attribute or set of attributes that uniquely identifies an entity instance.

ERD Symbols

• SSADM and Chen are two popular approaches to constructing ERDs.
• Visio provides stencils for constructing ERDs using these approaches.
• ERD symbols include entities, attributes, and relationships.### Entity Types and Instances
• An entity type is described once in a data model, but many instances of that data type may be represented by data stored in the system.
• An example is the EMPLOYEE entity type, which may have hundreds or thousands of instances in a company.

ERD Relationships

• A relationship is an association between two entities that is important to the system.
• The extent to which each entity is related to another must be considered.

Cardinality of a Relationship

• Cardinality defines the minimum and maximum number of occurrences of one entity for a single occurrence of the other.
• Cardinality is bi-directional, meaning it must be defined for both directions of the entity relationship.

Types of Relationships

One-to-One Relationship (1:1)

• A one-to-one relationship occurs where a single occurrence of one entity relates to one occurrence of a second entity.
• Example: An estimate can only become one order, and an order is derived from one estimate.

One-to-Many Relationship (1:N)

• One-to-many relationships occur when a single entity instance is related to many occurrences of a second entity.
• Example: One customer can be issued with many estimates.

Many-to-Many Relationship (N:M)

• Many-to-many relationships occur where many instances of an entity relate to many instances of a second entity.
• These relationships usually contain a hidden entity which should be extracted.
• N:M relationships are broken into one or more pairs of one-to-many relationships.

Zero, One, or Many

• Relationships between tables can be zero, one, or many.
• Zero implies that the record does not have to exist in the target table.
• One with zero implies that it can exist.
• One without zero implies that it must exist.
• Many simply implies many.

Cardinality

Optional Cardinality

• Optional cardinality considers whether an entity occurrence at one end of the relationship can ever be present in the system without the presence of the corresponding occurrence of the entity at the other end of the relationship.

Mandatory Cardinality

• Mandatory cardinality requires the occurrence of the entity at either end of the relationship to be present in the system.

Examples of Relationship Cardinality

Mandatory - Mandatory

• A doctor must be responsible for one or more patients, and a patient must be registered with one and only one doctor.

Optional – Mandatory

• A doctor may be responsible for one or more patients, and a patient must be registered with one and only one doctor.

Mandatory – Optional

• A doctor must be responsible for one or more patients, and a patient may be registered with one and only one doctor.

Optional – Optional

• A doctor may be responsible for one or more patients, and a patient may be registered with one and only one doctor.

Graphic Cardinality

• Exactly 1: Exactly one occurrence of one entity relates to one occurrence of a second entity.
• 0 or 1: Zero or one occurrence of one entity relates to one occurrence of a second entity.
• 1 or more: One or more occurrences of one entity relate to one occurrence of a second entity.
• 0 or many: Zero or many occurrences of one entity relate to one occurrence of a second entity.
• More than 1: More than one occurrence of one entity relates to one occurrence of a second entity.