Normalization and Functional Dependencies.pptx

Normalization and
Functional Dependencies

Table of Contents
• Functional Dependencies
• Normalization in RDBMS
• Relational Data Model Concepts
• Relational Keys
• Relational Constraints
• Relational Database Schema
• Relational Algebra
• ER-to-Relational Mapping

Functional Dependencies
A functional dependency occurs when one attribute uniquely determines
another attribute within a relation. It is a constraint that describes how
attributes in a table relate to each other.
If attribute X uniquely determines attribute Y, we say X → Y (X
functionally determines Y).
X is the determinant attribute
Y is the dependent attribute

Example
RollNo → Name (Each RollNo uniquely decides the student’s name)
RollNo → Dept (Each RollNo uniquely decides the department)

Example
VehicleNo Model, OwnerName
→

Types of Functional Dependencies
• Full Functional Dependency
• Partial Functional Dependency
• Transitive Dependency
• Multivalued Dependency
• Trivial Dependency
• Non-Trivial Dependency
• Join Dependency

Full Functional Dependency {StudentID, CourseID} → Marks
Definition: A functional dependency X → Y is a full functional
dependency if Y depends on the whole of X, not just a part of X.

Partial Functional Dependency
StudentID → StudentName, Dept, HOD
Definition: A dependency X → Y is a partial dependency if Y depends
only on part of a X, not the whole key.

Transitive Dependency
StudentID → Dept
Dept → HOD StudentID → HOD
⇒
Definition: A dependency X → Z is transitive if there exists an
intermediate attribute Y such that X → Y and Y → Z.

Multivalued Dependency (MVD)
StudentID →→ Hobby
StudentID →→ CourseID
Definition: A multivalued dependency occurs when, for a single value of
X, there are multiple independent values of Y.

Trivial Functional Dependency
{StudentID, Name} → Name
Definition: A functional dependency X → Y is trivial if Y is a subset of X.

Non-Trivial Functional Dependency
{StudentID, Name} → Dept
Definition: A functional dependency X → Y is non-trivial if Y is not a
subset of X.

Normalization
Normalization in RDBMS (Relational Database Management System) is the
process of organizing data into tables in such a way that it reduces data
redundancy (duplicate data) and ensures data integrity (accuracy and
consistency).
It is done by dividing large, complex tables into smaller, related tables and
defining relationships between them.

Why Normalize?
• Eliminate redundancy: Avoid storing the same data in multiple places.
• Example: Don’t store a student’s department name in every table if it can be
derived using a key.
• Prevent anomalies:
• Insert anomaly
• Update anomaly
• Delete anomaly
• Improve data integrity – maintain accurate and reliable data.

Normal Forms
Normalization is done in steps called Normal Forms (NF).
1 Normal Form (1NF)
2 Normal Form (2NF)
3 Normal Form (3NF)

First Normal Form (1NF)
A relation is in 1NF if all its attributes contain only atomic
(indivisible) values and each record is unique.
Key Points to Remember:
• No repeating groups or Values in a single column.
• Each cell must hold one value only (atomic).
• Each row must be unique (no duplicate rows).

Second Normal Form (2NF)
A table is in Second Normal Form (2NF) if it is already in 1NF, and No
non-key attribute is partially dependent on a part of a composite primary
key.
• Eliminates partial dependency.
• Every non-key column must depend on the whole
primary key, not just part of it.

Third Normal Form(3NF )
A table is in Third Normal Form (3NF) if it is already in 2NF, and
No non-key attribute is transitively dependent on the primary key.
• Eliminates transitive dependency.
• Non-key columns should depend only on the primary key,
not on other non-key columns.

Boyce-Codd Normal Form (BCNF)
A relation is in BCNF if for every non-trivial functional dependency
X → Y, X is a superkey.

In Table 1, Course is a super key
In Table 2, StudentID is a super key
So both tables are in BCNF.

Relational data model
The relational data model, a foundational concept in Relational Database
Management Systems (RDBMS), organizes data into tables, also known as
relations. Every row in the table represents a collection of related data values.

Basic Concepts
• Relation (Table)
• Tuple (Row)
• Attribute (Column)
• Domain (set of allowed values)
• Degree (number of attributes)
• Cardinality (number of tuples).

1. Relation (Table)
• A relation is a table that stores data in rows and columns.
• Each relation has a unique name and represents a specific entity or
concept.
• All data in a relational database is organized in such tables.

2. Tuple (Row)
• A tuple is a single row in a relation.
• It represents one record and contains values for each
attribute.
Example: (1, "Ravi", 19) is a tuple in the STUDENTS table.

3. Attribute (Column)
• An attribute is a column in a relation that represents a property or
characteristic of the entity.
• Each attribute has a name and a specific data type.
• For example, in a STUDENTS table, attributes may include Student_ID,
Name, and Age.

4. Domain
• A domain is the set of possible values that an attribute can take.
• Each attribute is defined over a domain.
• Domains help ensure data validity and consistency
• Example:
The domain of Age might be integer values between 17 and 30.

5. Degree
• The degree of a relation refers to the number of attributes (columns) in the
table.
• For example, if a table has three attributes Student_ID, Name, and Age
the degree of that relation is 3.

6. Cardinality
• The cardinality of a relation refers to the number of tuples (rows) it
contains.
• If the STUDENTS table contains five student records, then its cardinality
is 5.
• It gives an idea of how many entries the table currently holds.
Example:
If there are 5 rows in the STUDENTS table, its cardinality is 5.

Relational Keys
Keys are special attributes (or sets of attributes) used to uniquely identify
tuples (rows) in a relation (table). They play a vital role in maintaining
uniqueness and relationships in databases.
• Super key
• Candidate key
• Primary key
• Alternate key
• Foreign key

1. Super Key
• A set of one or more attributes that can uniquely identify each tuple in a
relation.
• Every relation must have at least one super key.
• A super key may contain extra attributes that are not necessary.
{RollNo} is a super key.
{Phone} is also a super key.
{RollNo, Name} is a super key

2. Candidate Key
A candidate key is an attribute (or a group of attributes) that has the
capability to become a primary key because it can uniquely identify every
tuple in a table by itself.
{RollNo} is a candidate key.
{Phone} is also a candidate key.

3. Primary Key
• A chosen candidate key used to uniquely identify tuples in the table.
• It cannot have NULL values and must be unique.
• Every relation must have one primary key.
{RollNo} as the primary key

4. Alternate Key
The candidate keys that are not selected as the primary key.
They are still unique identifiers but not used as the main reference.
{RollNo} is a candidate key.
{Phone} is also a candidate key.
If RollNo is chosen as primary key, then Phone becomes an alternate key.

5. Foreign Key
An attribute in one table that refers to the primary key in another table.
Maintains referential integrity between two relations.
Example:
STUDENT(RollNo, Name, DeptID)
DEPARTMENT(DeptID, DeptName)
DeptID in DEPARTMENT is Primary Key.
DeptID in STUDENT is a Foreign Key referencing DEPARTMENT.

Relational Constraints
• Relational constraints are rules that ensure the accuracy and integrity of data.
• These rules are applied to the values stored in the database to maintain consistency
and prevent invalid data.

1. Domain Constraints
• Domain constraints define the allowed values for each attribute (column)
in a relation.
• Every attribute in a table is associated with a domain, which limits the data
type and value range that can be stored.
Example:
An attribute like Age may only accept integers between 18 and 60. You
cannot insert a name or negative number into the Age column.

2. Key Constraints (or Uniqueness Constraints)
• Key constraints ensure that each row in a table is uniquely identifiable.
• This is typically enforced using Primary Keys and Candidate Keys.
 Primary Key must be unique and not null.
 No two rows can have the same primary key value.
Example:
In a STUDENTS table, no two students can have the same Student_ID.

3. Entity Integrity Constraints
• Entity integrity constraint ensures that primary key values are never null.
• Since the primary key is used to uniquely identify each tuple (row), it
must always contain a valid and non-null value.
Example:
A Student_ID field in a STUDENTS table cannot be left blank.

4. Referential Integrity Constraints
• Referential integrity constraints are used to maintain consistency between
two related tables.
• It ensures that a foreign key value in one table must match an existing
primary key value in another table, or be NULL.
Example:
If a Course_ID in the ENROLLMENT table refers to a Course_ID in the
COURSES table, then that course must exist in the COURSES table.

Relational Database Schema
A Relational Database Schema is the blueprint or structure that defines
how data is organized in a relational database.
Key Components of a Relational Schema
1. Table Names – Each table represents an entity (like STUDENTS, COURSES, EMPLOYEES).
2. Attributes – Columns of each table, representing properties of that entity.
3. Data Types – Specifies what kind of data an attribute can hold (e.g., INTEGER, VARCHAR, DATE).
4. Keys –
o Primary Key: Uniquely identifies each row in a table.
o Foreign Key: Connects two related tables.
5. Relationships – Defines how tables are linked using keys (one-to-one, one-to-many, etc.).

Student_ID is Primary Key → unique for each student
Course_ID in STUDENTS is Foreign Key → links to COURSES table
Relationship: Many students can enroll in one course (1:N)

Relational Algebra
Relational Algebra is a formal language used to query and manipulate
relational databases. It provides a set of mathematical operations that take
one or more relations (tables) as input and produce a new relation as
output.

Basic Relational Algebra Operations
• Selection (σ)
• Projection (π)
• Union ( )
∪
• Set Difference (−)
• Cartesian Product (×)
• Rename (ρ)

1. Selection (σ)
The Selection operation retrieves rows (tuples) from a relation that satisfy a
given condition.
Syntax : σ<condition>(Table_Name)
Example: σ(Age > 20)(STUDENTS)

σ(Branch='CSE’)(STUDENT)
Selects only those students who belong to the CSE branch.

2. Projection (π)
The Projection operation retrieves specific columns (attributes) from a
relation. It removes duplicates automatically.
Syntax : π<attribute_list>(Table_Name)
Example: π(Name, Course_ID)(STUDENTS)

πName, Marks(STUDENT)
Shows only the Name and Marks of all students.

3. Union ( )
∪
The Union operation combines the tuples from two compatible relations
(same number of attributes and same data types) and removes duplicates.
Syntax:
Table1 Table2
∪
Example: STUDENTS_2024 STUDENTS_2025
∪

STUDENT STUDENT_NEW
∪
Combines both tables and removes duplicate records.

4. Set Difference (−)
The Set Difference operation returns tuples that are in the first relation but
not in the second.
Syntax:
Table1 − Table2
Example: STUDENTS_2024 − STUDENTS_2025
Students who are in 2024 but not in 2025.

STUDENT − STUDENT_NEW
This represents the Set Difference operation,
showing students who are present in student but not in
student_new.

5. Cartesian Product (×)
The Cartesian Product operation returns all possible combinations of
tuples from two relations. It is also known as the cross product.
Syntax:
Table1 × Table2
Example: STUDENTS × COURSES

STUDENT × DEPT
Each student is paired with every departmen

6. Rename (ρ)
The Rename operation is used to rename a relation or its attributes. This is
helpful for readability or for intermediate steps in complex queries.
Syntax :
ρNewName(TableName)

STUDENTTABLE:
ρSTU(STUDENT)
Renames the table STUDENT to STU
(The data remains the same, only the
table name changes.)

Examples of Queries in the Relational Algebra
Assume a Banking database with the following relations
(tables):
Query : Get all customers who live in
'Mumbai'.
Solution:
σ_City = 'Mumbai'(CUSTOMER)

(tables):
Query : List the names of all customers.
Solution:
π_Name(CUSTOMER)

(tables):
Query : Get the names of customers who have an account (JOIN
query).
Solution:
π_Customer_ID,Account_No(Customer U Account)

Relational Database Design using ER-to-Relational Mapping
The ER-to-Relational Mapping process is used to convert an Entity-
Relationship (ER) diagram, which represents data conceptually, into a
relational schema consisting of tables, keys, and relationships.
Why is ER-to-Relational Mapping Important?
• Data Integrity: Ensures relationships and constraints are enforced using keys.
• Efficiency: Creates a schema optimized for queries and storage.
• Clarity: Translates conceptual designs into practical database tables

Steps for ER-to-Relational
Mapping
1. Mapping Regular Entity Types
2. Mapping Weak Entity Types
3. Mapping Binary One-to-One Relationships
4. Mapping Binary One-to-Many Relationships
5. Mapping Binary Many-to-Many Relationships
6. Mapping Multivalued Attributes
7. Mapping N-ary Relationships (n > 2)

1. Mapping Regular Entity Types
Each strong entity in the ER diagram is converted into a relation
(table).
All simple attributes of the entity become columns of the table.
The entity’s primary key becomes the table’s primary key.
The entity Student has three attributes: StuID, name, and degree.
StuID is underlined, indicating that it is the primary key - a unique identifier
for each student.

2. Mapping Weak Entity Types
A weak entity depends on a strong entity and does not have a primary key of
its own.
For such entities, a new relation is created that includes all its attributes
along with the primary key of the strong entity it depends on. The primary
key of this relation is a composite key made up of its partial key and the
primary key of the strong entity.

3. Mapping Binary One-to-One Relationships
For a 1:1 relationship between two entities, we add the primary key of one entity as a
foreign key in the relation of the other. The choice depends on total participation.
The entity with total participation should include the foreign key.

4. Mapping Binary One-to-Many Relationships
In a 1:N relationship, the primary key of the "one" side is added as a foreign key to the
relation representing the "many" side.

5. Mapping Binary Many-to-Many Relationships
For M:N relationships, a new relation is created to represent the relationship. This
relation includes the primary keys of both entities as foreign keys, and these together
form the composite primary key of the new relation.

6. Mapping Multivalued Attributes
Multivalued attributes are represented by creating a new table. This table includes the
primary key of the original entity and the multivalued attribute. The combination of
both forms the primary key of the new table..

7. Mapping N-ary Relationships (n > 2)
For relationships involving more than two entities, a new relation is created. This
relation includes the primary keys of all participating entities as foreign keys and any
attributes of the relationship itself.

Normalization and Functional Dependencies.pptx

More Related Content

Similar to Normalization and Functional Dependencies.pptx

Recently uploaded

Normalization and Functional Dependencies.pptx