Data Normalization (12 Marks – Expanded Answer)

Definition

Data normalization is the process of organizing data in a database to reduce redundancy and improve
data integrity by dividing large tables into smaller related tables.

Objectives

 Eliminate duplicate data

 Ensure data consistency

 Improve storage efficiency

 Avoid update, insert, delete anomalies

Types of Normal Forms (With Explanation + Diagrams)

1NF (First Normal Form)

Rule

 No repeating groups

 Each field contains atomic (single) values

Explanation

In an unnormalized table, a column may contain multiple values (e.g., multiple phone numbers).
1NF ensures each column has only one value.

Example

❌ Before 1NF (Not Atomic)

StudentID Name Phone

1 A 123, 456

✅ After 1NF

StudentID Name Phone

1 A 123

1 A 456

Diagram (Write in Exam)

Before 1NF:
[StudentID | Name | Phone(123,456)]
After 1NF:
[StudentID | Name | Phone]
[1 | A | 123 ]
[1 | A | 456 ]

2NF (Second Normal Form)

Rule

 Must be in 1NF

 No partial dependency (non-key attributes depend on full primary key)

Explanation

Occurs when a table has a composite key and some attributes depend only on part of the key.

Example

❌ Before 2NF

StudentID CourseID StudentName CourseName

 StudentName depends only on StudentID

 CourseName depends only on CourseID

Fix (Decomposition)

✅ After 2NF

Table 1: Student
| StudentID | StudentName |

Table 2: Course
| CourseID | CourseName |

Table 3: Enrollment
| StudentID | CourseID |

Diagram

Before 2NF:
[StudentID + CourseID] → StudentName, CourseName

After 2NF:
Student(StudentID → StudentName)
Course(CourseID → CourseName)
Enrollment(StudentID, CourseID)

3NF (Third Normal Form)

Rule
  Must be in 2NF

 No transitive dependency

Explanation

A non-key attribute should not depend on another non-key attribute.

Example

❌ Before 3NF
| StudentID | DeptID | DeptName |

 DeptName depends on DeptID (not directly on StudentID)

Fix

✅ After 3NF

Student Table
| StudentID | DeptID |

Department Table
| DeptID | DeptName |

Diagram

Before 3NF:
StudentID → DeptID → DeptName

After 3NF:
Student(StudentID → DeptID)
Department(DeptID → DeptName)

BCNF (Boyce-Codd Normal Form)

Rule

 Stronger version of 3NF

 Every determinant must be a candidate key

Explanation

Handles special cases where 3NF still has anomalies.

Example

❌ Before BCNF
| Teacher | Subject | Room |

 Teacher → Subject

 Subject → Room

 Subject is not a candidate key → violates BCNF

Fix

✅ After BCNF

Table 1
| Teacher | Subject |

Table 2
| Subject | Room |

Diagram

Before BCNF:
Teacher → Subject → Room

After BCNF:
Teacher → Subject
Subject → Room

Advantages

 Reduces redundancy

 Improves consistency

 Prevents anomalies

 Efficient data organization

Disadvantages

 Requires more joins

 Query complexity increases

 Slower read performance in some cases

Difference Between OLAP and OLTP (12 Marks)

Definition

 OLTP (Online Transaction Processing):

OLTP systems are used to manage real-time transactional data. They support day-to-day
operations such as insert, update, and delete.

 OLAP (Online Analytical Processing):

OLAP systems are used for data analysis and decision-making, working mainly on historical
data.
Detailed Explanation

OLTP

 Designed for operational tasks

 Handles large number of short transactions

 Ensures data accuracy and integrity

 Used in applications like banking, e-commerce, ATM systems

👉 Focus: “Write operations + real-time data”

OLAP

 Designed for analytical tasks

 Handles complex queries on large datasets

 Used for reporting, forecasting, business intelligence

 Data is usually stored in a data warehouse

👉 Focus: “Read operations + historical data analysis”

Differences Table (Write neatly in exam)

Feature OLTP OLAP

Purpose Transaction processing Data analysis

Data Current, real-time Historical

Queries Simple Complex

Users End users Analysts, managers

Speed Fast writes Fast reads

Operations INSERT, UPDATE, DELETE SELECT, aggregation

Database Design Highly normalized Denormalized

Schema ER model Star/Snowflake

Size Small (MB–GB) Large (GB–TB)

Example Banking system Data warehouse

Architecture Diagram (VERY IMPORTANT FOR MARKS)

OLTP SYSTEM OLAP SYSTEM

--------------------- ------------------------
 Users Analysts
↓ ↓
Applications OLAP Tools
↓ ↓
OLTP Database Data Warehouse
↓ ↑
Transactions ETL (Extract Transform Load)
(Insert/Update/Delete) ↑
Multiple Data Sources

Explanation of Diagram

 OLTP collects real-time data from users

 Data is transferred through ETL process

 Stored in Data Warehouse

 OLAP tools analyze data and generate reports

Examples

 OLTP:
ATM withdrawal, online shopping order, ticket booking

 OLAP:
Sales trend analysis, profit report, forecasting

Advantages

OLTP

 Fast transaction processing

 High data accuracy

 Supports many users simultaneously

OLAP

 Helps in decision making

 Handles large datasets

 Provides summarized insights

Hadoop and MapReduce (12 Marks)

Hadoop Definition
Hadoop is an open-source framework used to store and process large volumes of data (Big Data)
using distributed computing across multiple machines.

👉 It follows the principle:

“Store data across many systems and process it in parallel.”

Key Features of Hadoop

 Distributed storage

 Parallel processing

 Fault tolerance

 High scalability

 Cost-effective (uses commodity hardware)

Core Components of Hadoop

1. HDFS (Hadoop Distributed File System)

 Responsible for data storage

 Stores large files by splitting them into blocks

 Data is distributed across multiple nodes

 Provides fault tolerance using replication

👉 Example: A 1GB file is split into blocks and stored on different machines

2. MapReduce (Processing Layer)

 Responsible for processing data

 Uses parallel computation

 Works in two main steps: Map and Reduce

3. YARN (Yet Another Resource Negotiator)

 Manages resources and job scheduling

 Allocates CPU and memory to different tasks

 Acts as a bridge between storage and processing

MapReduce Working Process

1. Map Phase
  Input data is split into smaller chunks

 Each chunk is processed independently

 Output is in the form of (key, value) pairs

👉 Example:
Input → “big data big”
Output → (big,1), (data,1), (big,1)

2. Shuffle Phase

 Intermediate step between Map and Reduce

 Groups all values with the same key

👉 Example:
(big,1), (big,1) → (big, [1,1])

3. Reduce Phase

 Performs aggregation or summarization

 Produces final output

👉 Example:
(big, [1,1]) → (big,2)

Architecture Diagram (VERY IMPORTANT FOR MARKS)

HADOOP ARCHITECTURE

+-------------------+
| HDFS |
| (Storage Layer) |
+-------------------+
|
v
+-------------------+
| MapReduce |
| (Processing Layer)|
+-------------------+
|
v
+-------------------+
| YARN |
| (Resource Manager)|
+-------------------+
MapReduce Flow Diagram (Must Draw in Exam)

Input Data
↓
[ Split into Blocks ]
↓
Map Phase
(key, value pairs)
↓
Shuffle Phase
(Group by Key)
↓
Reduce Phase
(Aggregation)
↓
Final Output

Advantages

 Highly scalable (can handle TBs/PBs of data)

 Fault tolerant (data replication in HDFS)

 Parallel processing → faster computation

 Suitable for big data applications

Disadvantages (Add for Extra Marks)

 Not suitable for real-time processing

 High latency

 Complex setup

Applications of Hadoop

 Search engines (Google-like systems)

 Social media analytics

 Log processing

 Recommendation systems

Types of SQL (12 Marks)

Introduction
SQL (Structured Query Language) is used to manage and manipulate relational databases.
It is divided into different types based on functionality.

1. DDL (Data Definition Language)

Definition

DDL is used to define and modify the structure of the database (schema).

Commands

 CREATE → create tables/databases

 ALTER → modify structure

 DROP → delete table/database

 TRUNCATE → remove all records

Explanation

DDL commands affect the structure, not the data itself.

Example

CREATE TABLE Student(

id INT,
name VARCHAR(50)
);

ALTER TABLE Student ADD age INT;

DROP TABLE Student;

2. DML (Data Manipulation Language)

Definition

DML is used to insert, update, and delete data in a database.

Commands

 INSERT → add data

 UPDATE → modify data

 DELETE → remove data

Explanation

DML works on the records inside tables.

Example
INSERT INTO Student VALUES(1, 'A');
UPDATE Student SET name='B' WHERE id=1;
DELETE FROM Student WHERE id=1;

3. DQL (Data Query Language)

Definition

DQL is used to retrieve data from the database.

Command

 SELECT

Explanation

It allows filtering, sorting, and grouping of data.

Example

SELECT * FROM Student;

SELECT name FROM Student WHERE id=1;

4. DCL (Data Control Language)

Definition

DCL is used to control access and permissions in a database.

Commands

 GRANT → give access

 REVOKE → remove access

Explanation

Ensures security and authorization of database users.

Example

GRANT SELECT ON Student TO user1;

REVOKE SELECT ON Student FROM user1;

5. TCL (Transaction Control Language)

Definition

TCL is used to manage transactions in a database.

Commands

 COMMIT → save changes

  ROLLBACK → undo changes

 SAVEPOINT → set checkpoint

Explanation

Ensures data consistency and follows ACID properties.

Example

BEGIN;
UPDATE Student SET name='C' WHERE id=1;
ROLLBACK;

Diagram (Write This for Extra Marks)

SQL TYPES
---------------------------------------
| DDL → Structure (Tables) |
| DML → Data Modification |
| DQL → Data Retrieval |
| DCL → Access Control |
| TCL → Transaction Control |
---------------------------------------

Key Differences Summary

Type Purpose Commands

DDL Structure CREATE, ALTER, DROP

DML Data changes INSERT, UPDATE, DELETE

DQL Data retrieval SELECT

DCL Permissions GRANT, REVOKE

TCL Transactions COMMIT, ROLLBACK

Types of Databases (12 Marks)

Introduction
A database is an organized collection of data that can be easily accessed, managed, and updated.
Different types of databases are designed to handle various data structures and applications.

1. Relational Database (RDBMS)

Definition

A relational database stores data in the form of tables (rows and columns), where relationships are
established using keys.

Features

 Uses Structured Query Language (SQL)

 Data stored in tables

 Supports primary key and foreign key relationships

 Ensures data integrity and consistency

Example

 MySQL

 Oracle

 PostgreSQL

Use Case

 Banking systems

 Student management systems

2. NoSQL Database

Definition

NoSQL databases store data in non-tabular formats and are designed for handling large-scale and
unstructured data.

Types of NoSQL

a) Key-Value Store

 Data stored as key-value pairs

 Example: Redis

b) Document Database

 Stores data in JSON/XML format

 Example: MongoDB

c) Column-Based Database
  Stores data in columns instead of rows

 Example: Cassandra

d) Graph Database

 Stores data as nodes and relationships

 Example: Neo4j

Features

 High scalability

 Flexible schema

 Suitable for big data

3. Distributed Database

Definition

A distributed database is a database where data is stored across multiple physical locations or
systems.

Features

 Data is distributed across servers

 Improves availability and reliability

 Supports parallel processing

Example

 Google Spanner

 Apache Cassandra

Use Case

 Large-scale applications

 Global systems

4. Cloud Database

Definition

A cloud database is a database that is hosted on cloud platforms and accessed via the internet.

Features

 On-demand scalability

 High availability
  Managed by cloud providers

Examples

 Amazon RDS

 Google Cloud SQL

 Azure SQL Database

Use Case

 Web applications

 SaaS platforms

5. Object-Oriented Database (OODBMS)

Definition

An object-oriented database stores data in the form of objects, similar to object-oriented

programming.

Features

 Supports classes, objects, inheritance

 Stores complex data types

 Direct mapping with programming languages

Example

 db4o

 ObjectDB

Use Case

 Multimedia systems

 CAD applications

Diagram (Draw This for Extra Marks)

TYPES OF DATABASES
------------------------------------------------
| Relational → Tables (rows & columns) |
| NoSQL → Non-tabular (JSON, key-value) |
| Distributed → Multiple locations |
| Cloud → Internet-based storage |
| Object-Oriented → Objects & classes |
------------------------------------------------
Comparison Summary

Type Structure Use Case

Relational Tables Banking

NoSQL Flexible Big data

Distributed Multiple nodes Large systems

Cloud Online Web apps

Object- Objects Complex data

Oriented

Advantages (General)

 Efficient data management

 Scalability

 High performance

 Flexibility (NoSQL)

Types of Tables (12 Marks)

Introduction

In database systems (especially in data warehousing), different types of tables are used to organize
and manage data efficiently.
Each table serves a specific purpose such as storing facts, attributes, or temporary data.

1. Fact Table

Definition

A fact table contains quantitative (measurable) data related to business processes.

Features

 Stores numerical values (measures)

 Contains foreign keys linkin g to dimension tables

 Large in size

Examples (Measures)

 Sales amount

 Quantity
  Profit

Example Table

ProductID CustomerID TimeID Sales

👉 Central table in a data warehouse

2. Dimension Table

Definition

A dimension table contains descriptive attributes that provide context to the fact table.

Features

 Contains textual/qualitative data

 Smaller in size

 Used for filtering and grouping

Examples

 Customer name

 Product category

 Location

Example Table

CustomerID Name City

3. Lookup Table

Definition

A lookup table stores reference or static data used by other tables.

Features

 Contains predefined values

 Improves consistency

 Avoids repetition

Examples

 Country codes

 Status values (Active/Inactive)

Example
StatusID Status

1 Active

2 Inactive

4. Temporary Table

Definition

A temporary table is used to store data temporarily during query execution.

Features

 Exists only for a session or transaction

 Automatically deleted after use

 Improves performance for intermediate results

Example

CREATE TEMP TABLE temp_sales AS

SELECT * FROM Sales WHERE amount > 1000;

5. External Table

Definition

An external table refers to data that is stored outside the database but can be accessed as if it were
inside.

Features

 Data stored in files (CSV, JSON, etc.)

 Used in big data tools (Hadoop, Hive)

 No actual storage inside DB

Example

 Data stored in Hadoop HDFS accessed via Hive

Diagram (Very Important for Marks)

DATA WAREHOUSE TABLE STRUCTURE

FACT TABLE
(Sales, Quantity)
|
-------------------------------------
| | | |
Dimension Dimension Dimension Lookup
(Customer) (Product) (Time) Table

Temporary Table → Used during processing

External Table → Outside database (files)

Explanation of Diagram

 Fact table is at the center storing measurable data

 Dimension tables provide descriptive information

 Lookup table provides reference values

 Temporary table is used during processing

 External table connects external data sources

Advantages

 Efficient data organization

 Supports analytical queries

 Reduces redundancy

 Improves performance

Data Cubes (12 Marks)

Definition

A data cube is a multidimensional data structure used in data warehousing and OLAP to represent
data across multiple dimensions for fast and efficient analysis.

👉 It allows users to analyze data from different perspectives (angles).

Key Concepts

1. Dimensions

Dimensions are the axes of the cube that describe data.

Common Dimensions

 Time (Day, Month, Year)

 Location (City, State, Country)

  Product (Item, Category)

👉 Dimensions answer: “In what context is data analyzed?”

2. Measures

Measures are the numerical values stored in the cube.

Examples

 Sales

 Revenue

 Profit

 Quantity

👉 Measures answer: “What is being measured?”

Example

A company wants to analyze sales based on:

 Time (2024, 2025)

 Location (Chennai, Mumbai)

 Product (Laptop, Mobile)

This forms a 3D data cube.

Operations on Data Cube

1. Slice

 Selects data along one dimension

 Reduces cube into a smaller subset

👉 Example: Sales for only 2025

2. Dice

 Selects a sub-cube by choosing multiple dimensions

👉 Example: Sales for

(Product = Laptop, Location = Chennai)
3. Drill-Down

 Moves from summary to detailed data

👉 Example: Year → Month → Day

4. Roll-Up

 Moves from detailed to summarized data

👉 Example: Day → Month → Year

Diagram (VERY IMPORTANT – Draw in Exam)

3D Data Cube Representation

Location
↑
|
|
|
+-------- Product
/
/
/
Time

👉 Label the axes clearly:

 X-axis → Product

 Y-axis → Location

 Z-axis → Time

👉 Inside cube → write “Sales / Revenue”

Operations Representation (Optional Extra Diagram)

Slice → Cutting one layer of cube

Dice → Selecting smaller cube inside
Drill-down ↓ (more detail)
Roll-up ↑ (more summary)

Advantages

 Enables fast data analysis

 Supports multidimensional queries

  Easy aggregation and summarization

 Improves decision-making

Disadvantages (Add for Extra Marks)

 Complex to design

 Requires large storage

 Processing overhead

Applications

 Business Intelligence

 Sales analysis

 Financial reporting

 Market trend analysis

9. CRUD Operations (12 Marks)

Definition
CRUD stands for Create, Read, Update, Delete, which are the four basic operations performed on a
database.

👉 These operations form the foundation of all database-driven applications.

CRUD Operations Explained

1. Create (INSERT)

Definition

Used to add new records into a table.

Example

INSERT INTO student VALUES(1,'A');

👉 Adds a new student record.

2. Read (SELECT)

Definition

Used to retrieve data from the database.

Example

SELECT * FROM student;

👉 Fetches all records.

3. Update (UPDATE)

Definition

Used to modify existing data.

Example

UPDATE student SET name='B' WHERE id=1;

👉 Changes student name.

4. Delete (DELETE)

Definition

Used to remove records from a table.

Example
DELETE FROM student WHERE id=1;

👉 Deletes specific record.

Diagram (Draw This for Extra Marks)

CRUD OPERATIONS

+-------------------+
| CREATE |
| (INSERT) |
+-------------------+
↓
+-------------------+
| READ |
| (SELECT) |
+-------------------+
↓
+-------------------+
| UPDATE |
| (UPDATE) |
+-------------------+
↓
+-------------------+
| DELETE |
| (DELETE) |
+-------------------+

Importance

 Core operations of all database systems

 Used in web apps, mobile apps, banking systems

 Enables data management and manipulation

 Essential for backend development

Advantages

 Simple and fundamental

 Supports all data interactions

 Easy to implement

Conclusion (Write this for full marks)

CRUD operations are essential for managing data efficiently, forming the backbone of all database
applications.

10. ERD (Entity Relationship Diagram) (12 Marks)

Definition

An ERD (Entity Relationship Diagram) is a graphical representation of entities, attributes, and

relationships in a database.

👉 It is used for database design and planning.

Components of ERD

1. Entity

 A real-world object or concept

 Represented by a rectangle

👉 Example: Student, Course

2. Attribute

 Properties of an entity

 Represented by an oval

👉 Example: Name, ID

3. Relationship

 Defines how entities are connected

 Represented by a diamond

👉 Example: Enrolls

Types of Relationships

1. One-to-One (1:1)

 One entity is related to only one entity

👉 Example: Person → Passport

2. One-to-Many (1:M)

 One entity relates to multiple entities

👉 Example: Teacher → Students

3. Many-to-Many (M:N)

 Many entities relate to many entities

👉 Example: Students ↔ Courses

Diagram (VERY IMPORTANT – Draw in Exam)

(ID) (Name)
\ /
\ /
+-----------+
| STUDENT |
+-----------+
|
| Enrolls
|
+-----------+
| COURSE |
+-----------+
/ \
(CourseID) (Title)

Symbols Used

Symbol Meaning

Rectangle Entity

Oval Attribute

Diamond Relationship

Line Connection

Advantages

 Easy to understand

 Helps in database design

 Reduces errors

 Improves communication