Database, Its Characteristics, and Applications

A Database is a structured collection of data that allows efficient storage, retrieval, and
management of information. It ensures data integrity, security, and scalability while enabling
multiple users to access and manipulate data simultaneously.

Characteristics of a Database

1. Data Persistence – Data remains stored even after the system is shut down.
2. Data Integrity – Ensures accuracy, consistency, and reliability of data.
3. Data Security – Access control mechanisms prevent unauthorized access.
4. Concurrency Control – Multiple users can access data simultaneously without conflicts.
5. Data Redundancy Control – Minimizes data duplication to improve efficiency.
6. Scalability – Supports growing amounts of data and users.
7. Backup and Recovery – Ensures data safety in case of failures.

Applications of Databases

• Banking Systems – For transactions, customer accounts, and loan management.

• Healthcare – Patient records, billing, and hospital management.
• E-Commerce – Inventory, customer orders, and payments.
• Social Media – User profiles, posts, and messages.
• Education – Student records, course management, and results tracking.

Data Abstraction and Independence

Data Abstraction

Data abstraction refers to hiding complex data storage details and presenting only necessary
information to the users. It consists of three levels:

1. Physical Level – Lowest level describing how data is stored (e.g., file organization,
indexing).
2. Logical Level – Defines what data is stored and relationships between data elements.
3. View Level – Highest level where users interact with the data via interfaces or
applications.

Data Independence

Data independence ensures that changes in one level of abstraction do not affect others. It has
two types:

1. Logical Data Independence – Changes in the logical schema do not affect applications.
 2. Physical Data Independence – Changes in the storage structure do not impact the
logical schema.

Schemas and Instances

• Schema – A blueprint or structure of the database that defines tables, attributes, and
relationships.
o Physical Schema – Describes physical storage.
o Logical Schema – Defines logical relationships and data types.
o View Schema – Represents different user views.
• Instance – The actual data stored in the database at a given time.

Classification of DBMS
DBMS can be classified based on different criteria:

1. Based on Data Model:

o Hierarchical DBMS – Data stored in a tree-like structure (e.g., IBM IMS).
o Network DBMS – Data stored as records connected through links (e.g., IDMS).
o Relational DBMS (RDBMS) – Data stored in tables (e.g., MySQL,
PostgreSQL).
o Object-Oriented DBMS (OODBMS) – Data stored as objects (e.g., ObjectDB).
2. Based on Number of Users:
o Single-User DBMS – Supports one user at a time.
o Multi-User DBMS – Supports multiple users simultaneously.
3. Based on Distribution:
o Centralized DBMS – Entire database is stored in one location.
o Distributed DBMS – Database is spread across multiple locations.
4. Based on Usage:
o OLTP (Online Transaction Processing) DBMS – Focuses on transaction-
oriented tasks.
o OLAP (Online Analytical Processing) DBMS – Supports complex queries and
data analysis.

DDL and DML

DDL (Data Definition Language)

DDL consists of commands that define the structure of the database. Examples:
 • CREATE – Creates a new database or table.
• ALTER – Modifies an existing database object.
• DROP – Deletes a table or database.
• TRUNCATE – Removes all records from a table without deleting the structure.

DML (Data Manipulation Language)

DML consists of commands used to manipulate and retrieve data. Examples:

• INSERT – Adds new records.

• UPDATE – Modifies existing records.
• DELETE – Removes records from a table.
• SELECT – Retrieves data from one or more tables.

Data Models
A data model defines how data is structured and related. The Entity-Relationship (E-R) Model is a
conceptual model used to represent real-world objects and relationships in databases.

Entity-Relationship Model (E-R Model)

An E-R Model is a high-level diagram representing data entities, attributes, and relationships.

Entities

Entities represent real-world objects (e.g., Student, Employee).

• Entity Type: A collection of similar entities (e.g., "Student" as a category).

• Entity Set: A collection of entities stored in the database (e.g., all students).

Attributes & Keys

Attributes define characteristics of an entity.

• Simple Attribute: Atomic value (e.g., Name, Age).

• Composite Attribute: Can be divided into sub-parts (e.g., Name → First Name, Last Name).
• Derived Attribute: Computed from other attributes (e.g., Age from Date of Birth).
• Multivalued Attribute: Can have multiple values (e.g., Phone Numbers).
• Key Attribute: Uniquely identifies an entity (e.g., Student ID).

Types of Keys

• Primary Key: Uniquely identifies each entity (e.g., Employee ID).

 • Candidate Key: A set of attributes that can be a primary key.
• Foreign Key: An attribute linking to another table’s primary key.
• Composite Key: A combination of two or more attributes to form a unique identifier.

Relationships

A relationship is an association between entities.

• Relationship Type: Defines a set of similar relationships.

• Relationship Set: A collection of relationships stored in the database.

Types of Relationships:

1. One-to-One (1:1): One entity relates to only one entity (e.g., One country has one president).
2. One-to-Many (1:M): One entity relates to multiple entities (e.g., One teacher teaches many
students).
3. Many-to-Many (M:M): Many entities relate to many entities (e.g., Students enroll in multiple
courses).

E-R Diagrams
An E-R Diagram is a visual representation of entities, attributes, and relationships in a database. It
includes:

• Rectangles – Represent entities.

• Ellipses – Represent attributes.
• Diamonds – Represent relationships.
• Lines – Connect entities with attributes and relationships.

Normalization and Its Importance

Normalization is the process of organizing data in a database to reduce redundancy and improve
data integrity. It involves decomposing large tables into smaller, related tables and defining
relationships between them while ensuring that data dependencies make sense. The main goals of
normalization are:

• Minimizing redundancy: Reduces duplicate data, which saves storage space.

• Improving data integrity: Ensures data consistency and accuracy.
• Enhancing query performance: Optimizes database performance by reducing
anomalies.
 • Simplifying data maintenance: Makes it easier to update, insert, or delete data without
inconsistencies.

Normal Forms (NF)

Normalization is carried out through different levels, called normal forms (NF). Each level
eliminates specific types of redundancy and anomalies.

1st Normal Form (1NF) - Eliminate Repeating Groups

A table is in 1NF if:

• Each column contains atomic (indivisible) values.

• Each row is unique and identified by a primary key.
• There are no repeating groups (i.e., no multiple values in a single column).

Example (Non-1NF Table)

Student_ID Name Courses

101 John Math, Science
102 Alice Science

Issue: The "Courses" column contains multiple values.

1NF Table (Atomic Values)

Student_ID Name Course

101 John Math
101 John Science
102 Alice Science

2nd Normal Form (2NF) - Remove Partial Dependencies

A table is in 2NF if:

 • It is in 1NF.
• No partial dependencies exist (i.e., no non-key attribute should depend on a part of the
primary key).

Example (Non-2NF Table)

Order_ID Product_ID Product_Name Quantity

1 P1 Laptop 2
2 P2 Mouse 3

Issue: The "Product_Name" depends only on "Product_ID", not on the full key ("Order_ID,
Product_ID").

2NF Solution (Separate into Two Tables)

Orders Table

Order_ID Product_ID Quantity

1 P1 2
2 P2 3

Products Table

Product_ID Product_Name
P1 Laptop
P2 Mouse

3rd Normal Form (3NF) - Remove Transitive Dependencies

A table is in 3NF if:

• It is in 2NF.
• No transitive dependencies exist (i.e., non-key attributes should not depend on other
non-key attributes).

Example (Non-3NF Table)

Employee_ID Name Department Department_Location

1 John HR New York
2 Alice IT San Francisco
 Issue: "Department_Location" depends on "Department", not on "Employee_ID".

3NF Solution (Separate into Two Tables)

Employees Table

Employee_ID Name Department

1 John HR
2 Alice IT

Departments Table

Department Department_Location
HR New York
IT San Francisco

Boyce-Codd Normal Form (BCNF) - Stronger 3NF

A table is in BCNF if:

• It is in 3NF.
• Every determinant is a candidate key (i.e., no non-trivial functional dependency should
exist where a non-key attribute determines a key attribute).

Example (Non-BCNF Table)

Student_ID Course Instructor

1 Math Prof. A
2 Science Prof. B
1 Science Prof. B

Issue: "Instructor" depends on "Course", not on "Student_ID", leading to redundancy.

BCNF Solution (Separate into Two Tables)

Students_Courses Table

Student_ID Course
1 Math
2 Science
1 Science
Courses_Instructors Table

Course Instructor
Math Prof. A
Science Prof. B

Relational Language: SQL and Its Features

SQL (Structured Query Language) is the standard language for managing and manipulating
relational databases. It allows users to store, retrieve, modify, and delete data efficiently.

Key Features of SQL

1. Data Definition Language (DDL) – Commands like CREATE, ALTER, and DROP define
the structure of the database.
2. Data Manipulation Language (DML) – Commands like INSERT, UPDATE, and DELETE
allow data modifications.
3. Data Query Language (DQL) – The SELECT statement retrieves data from databases.
4. Data Control Language (DCL) – Commands like GRANT and REVOKE manage user
permissions.
5. Transaction Control Language (TCL) – Commands like COMMIT, ROLLBACK, and
SAVEPOINT control transactions.

INSERT, UPDATE, DELETE Queries

These SQL statements are used to manipulate records in a database table.

1. INSERT Query

Used to add new records to a table.

INSERT INTO employees (id, name, department, salary)

VALUES (1, 'John Doe', 'HR', 50000);

2. UPDATE Query

Modifies existing records in a table.

UPDATE employees
SET salary = 60000
WHERE id = 1;

3. DELETE Query

Removes records from a table.

DELETE FROM employees

WHERE id = 1;

Joins in SQL
Joins are used to retrieve data from multiple tables based on a related column.

Types of Joins:

1. INNER JOIN – Returns matching records from both tables.

SELECT [Link], departments.department_name

FROM employees
INNER JOIN departments
ON employees.department_id = [Link];

2. LEFT JOIN (or LEFT OUTER JOIN) – Returns all records from the left table and
matching records from the right table.

SELECT [Link], departments.department_name

FROM employees
LEFT JOIN departments
ON employees.department_id = [Link];

3. RIGHT JOIN (or RIGHT OUTER JOIN) – Returns all records from the right table
and matching records from the left table.

SELECT [Link], departments.department_name

FROM employees
RIGHT JOIN departments
ON employees.department_id = [Link];

4. FULL JOIN (or FULL OUTER JOIN) – Returns all records when there is a match in
either table.

SELECT [Link], departments.department_name

FROM employees
FULL JOIN departments
ON employees.department_id = [Link];

5. CROSS JOIN – Returns the Cartesian product of both tables (every combination).
SELECT [Link], departments.department_name
FROM employees
CROSS JOIN departments;

Views in SQL
A view is a virtual table based on the result of an SQL query.

Creating a View
CREATE VIEW employee_salaries AS
SELECT name, salary
FROM employees
WHERE salary > 50000;

Using a View
SELECT * FROM employee_salaries;

Updating a View
CREATE OR REPLACE VIEW employee_salaries AS
SELECT name, salary, department
FROM employees
WHERE salary > 60000;

Dropping a View
DROP VIEW employee_salaries;

Query Processing and Optimization in SQL

1. Query Processing
Query processing refers to the series of steps taken by a database management system (DBMS) to
execute a SQL query and return the desired results efficiently. The main stages of query processing
include:

Steps in Query Processing

1. Parsing and Translation

o The SQL query is parsed for syntax and semantic correctness.
 o The parsed query is translated into an internal representation (usually a tree or graph).
2. Optimization
o The query is analyzed for potential optimization strategies.
o The system selects an execution plan that minimizes cost (e.g., choosing indexes or join
algorithms).
3. Execution Plan Generation
o The optimizer generates different possible execution plans.
o The best plan is chosen based on estimated cost.
4. Execution and Result Retrieval
o The query is executed based on the selected execution plan.
o The results are fetched and returned to the user.

2. Query Cost Estimation

Query cost estimation helps the optimizer determine the most efficient way to execute a query. The
cost is measured in terms of:

Factors Affecting Query Cost

1. Disk I/O Cost

o Number of disk accesses required to fetch data.
2. CPU Cost
o Processing time for filtering, sorting, and joining operations.
3. Memory Usage
o Amount of RAM required for query execution.
4. Network Cost
o Data transfer cost in distributed databases.

Example: Cost Comparison of Index vs. Full Table Scan

• Without Index:

SELECT * FROM employees WHERE salary > 50000;

o Requires scanning the entire table.

• With Index:

CREATE INDEX idx_salary ON employees(salary);

SELECT * FROM employees WHERE salary > 50000;

o Uses an index to fetch only relevant records, reducing disk I/O.

3. Query Operations
Query operations are fundamental operations that a DBMS performs to execute queries.

Common Query Operations

1. Selection (σ) – Filters rows based on conditions.

SELECT * FROM employees WHERE department = 'IT';

2. Projection (π) – Selects specific columns.

SELECT name, salary FROM employees;

3. Join (⨝) – Combines records from multiple tables.

SELECT [Link], departments.department_name

FROM employees
INNER JOIN departments ON employees.department_id = [Link];

4. Sorting (ORDER BY) – Arranges results in a specific order.

SELECT * FROM employees ORDER BY salary DESC;

5. Aggregation (SUM, COUNT, AVG, MIN, MAX) – Performs calculations on grouped data.

SELECT department, AVG(salary) FROM employees GROUP BY department;

4. Evaluation of Expressions
The DBMS evaluates complex SQL expressions to optimize performance.

Expression Optimization Techniques

• Reordering of operations
o Push WHERE conditions before JOIN to filter early.
• Index Utilization
o Use indexes to speed up lookups.
• Materialized Views
o Precompute and store frequently used query results.

Example: Optimization Using Index

Without Optimization:
SELECT * FROM employees WHERE LOWER(name) = 'john';

• The function LOWER(name) prevents index usage.

Optimized Version:

SELECT * FROM employees WHERE name = 'John';

• Avoids unnecessary function calls and uses the index.

5. Query Optimization
Query optimization aims to improve the efficiency of SQL queries.

Types of Query Optimization

1. Heuristic Optimization
o Uses general rules like pushing WHERE clauses early and minimizing intermediate results.
2. Cost-Based Optimization
o Evaluates multiple query execution plans and selects the least expensive.
3. Rule-Based Optimization
o Uses predefined rules to optimize queries.

Techniques for Query Optimization

• Using Indexes:
o Speeds up search operations.
• **Avoiding SELECT ***
o Fetch only required columns.
• Using EXISTS Instead of IN:

SELECT * FROM employees WHERE EXISTS (

SELECT 1 FROM departments WHERE employees.department_id =
[Link]
);

o EXISTS is generally faster than IN for large datasets.

• Using Joins Efficiently:
o Prefer INNER JOIN over CROSS JOIN when possible.
• Partitioning Large Tables:
o Improves query performance for large datasets.