To design a database for a bank application to store information about Customers and the

Bank, we need to first define entities, attributes, relationships, and constraints. Below is a
complete breakdown, including an E-R diagram description.

Assumptions

1. A Customer can have multiple accounts in one or more Branches of a Bank.

2. A Bank can have multiple Branches.

3. Each Account is associated with only one branch.

4. An Account can be of different types (e.g., savings, current).

5. Each Transaction is linked to one account.

6. A Loan can be taken by one or more customers, and a customer can take multiple
loans.

7. We will keep track of basic transaction history.

8. Each customer has a unique Customer ID, and each account has a unique Account
Number.

(i) Entities and Related Attributes

1. Customer

• Customer_ID (PK)

• Name

• Date_of_Birth

• Gender

• Address

• Phone

• Email

• PAN_Number

• Aadhar_Number

2. Bank

• Bank_ID (PK)

• Bank_Name

• Head_Office_Address

3. Branch
 • Branch_ID (PK)

• Branch_Name

• IFSC_Code

• Address

• Bank_ID (FK)

4. Account

• Account_Number (PK)

• Account_Type

• Balance

• Date_Opened

• Customer_ID (FK)

• Branch_ID (FK)

5. Transaction

• Transaction_ID (PK)

• Account_Number (FK)

• Transaction_Date

• Transaction_Type (Credit/Debit)

• Amount

• Description

6. Loan

• Loan_ID (PK)

• Amount

• Interest_Rate

• Loan_Type

• Branch_ID (FK)

7. Customer_Loan (To handle Many-to-Many between Customer and Loan)

• Customer_ID (FK)

• Loan_ID (FK)

• Disbursal_Date

(ii) Relationships Among Entities

 1. Bank — Branch

o One Bank has many Branches (1:M)

2. Branch — Account

o One Branch has many Accounts (1:M)

3. Customer — Account

o One Customer can have many Accounts (1:M)

4. Account — Transaction

o One Account can have many Transactions (1:M)

5. Branch — Loan

o One Branch can issue many Loans (1:M)

6. Customer — Loan (via Customer_Loan)

o Many-to-Many relationship

(iii) Constraints

1. Primary Keys: Customer_ID, Account_Number, Transaction_ID, Loan_ID

2. Foreign Keys:

o Account.Customer_ID → Customer.Customer_ID

o Account.Branch_ID → Branch.Branch_ID

o Branch.Bank_ID → Bank.Bank_ID

o Transaction.Account_Number → Account.Account_Number

o Loan.Branch_ID → Branch.Branch_ID

o Customer_Loan.Customer_ID → Customer.Customer_ID

o Customer_Loan.Loan_ID → Loan.Loan_ID

3. Balance ≥ 0 in Account

4. Transaction amount > 0

5. PAN and Aadhar must be unique per customer

6. Email and Phone should be unique

(iv) E-R Diagram Description

Customer ——< Account >—— Branch >—— Bank

| |

| v

v Transaction

Customer ——< Customer_Loan >—— Loan ——< Branch

Entities: Boxes

• Rectangles: Customer, Bank, Branch, Account, Transaction, Loan

• Diamond Shapes: Relationships (has, maintains, holds, issues, etc.)

• Ovals: Attributes (attached to each entity)

Relationships:

• Bank —< Branch

• Branch —< Account

• Customer —< Account

• Account —< Transaction

• Branch —< Loan

• Customer —< Customer_Loan >—> Loan

Definition: Serializable Schedule

A serializable schedule in database systems is a concurrent execution of transactions that

results in a database state that could be obtained if the transactions were executed serially
(one after another), without overlapping.

Types of Serializability:

• Conflict Serializability: Based on reordering non-conflicting operations.

• View Serializability: Based on final read/write results (more general, but harder to
check).

We will focus on conflict serializability, which is commonly asked and easier to test.

Schedule A:

Operation T1 T2

Step 1 Read(X)
Operation T1 T2

Step 2 Read(X)

Step 3 Write(Y)

Step 4 Write(Y)

Step 5 Commit

Step 6 Commit

Step-by-step Conflict Analysis

Let's analyze the conflicting operations.

Conflict Rules:

Two operations conflict if:

• They are by different transactions,

• They access the same data item, and

• At least one of them is a write.

Conflicting Pairs in Schedule A:

1. T1: Write(Y) vs T2: Write(Y) → conflict on Y

⇒ Order: T1 before T2 (T1 writes Y before T2 writes Y)

2. T1: Read(X) and T2: Read(X) → no conflict (both reads are safe)

Precedence Graph (Serialization Graph)

• Nodes: T1, T2

• Edge from Ti to Tj if Ti has an operation that conflicts with a later operation of Tj

In our case:

• T1 → T2 (because T1 writes Y before T2 writes Y)

No cycle in the graph ⇒ Conflict Serializable

Final Answer:

Schedule A is serializable, and specifically conflict serializable, equivalent to the serial

order:
T1 → T2
Here are the SQL commands for the queries based on the given schema:

Table Structures

• Student(st_id, name, programme_code)

• Programme(programme_code, Prof_name, fee)

• Sample Data
• Student table

st_id name programme_code

101 Ayesha MCA
102 Bharat BCA
103 Charan MCA
104 Deepika BBA

• Programme table

programme_code Prof_name fee

MCA Dr. Verma 60000
BCA Dr. Sharma 50000
BBA Dr. Mehta 55000
•
• (i) List the name of all the students of the programme whose
programme_code is ‘MCA’:
• SELECT name
• FROM Student
• WHERE programme_code = 'MCA';
• Result:

Name
Ayesha
Charan
•
• (ii) List all the programmes in the increasing order of programme
fee:
• SELECT *
• FROM Programme
• ORDER BY fee ASC;
• Result:

programme_code Prof_name fee

BCA Dr. Sharma 50000
BBA Dr. Mehta 55000
programme_code Prof_name fee
MCA Dr. Verma 60000
•
• (iii) Find the total number of programmes of the university:
• SELECT COUNT(*) AS total_programmes
• FROM Programme;
• Result:

total_programmes
3
•
• (iv) List st_id, name, prof_name for all the students:
• SELECT s.st_id, [Link], p.Prof_name
• FROM Student s
• JOIN Programme p ON s.programme_code = p.programme_code;
• Explanation of Join:
• We’re using an INNER JOIN to combine students with the programme they are
enrolled in using the programme_code.
• Result:

st_id name Prof_name

101 Ayesha Dr. Verma
102 Bharat Dr. Sharma
103 Charan Dr. Verma
104 Deepika Dr. Mehta

When a transaction is executing in a database system, it can encounter several types of

failures, which may prevent it from completing successfully. These failures can lead to data
inconsistency if not handled properly. Below are the main types of transaction failures:

1. Transaction Failures

These failures occur within the transaction itself, due to logical or runtime errors. The DBMS
aborts and rolls back the transaction.

Causes:

• Logical errors (e.g., divide by zero, constraint violation)

• User-initiated aborts

• Deadlock detection

Example: A transaction tries to withdraw more money than available in the account →
Constraint fails → Transaction aborted.
 2. System (or Software) Failures

These failures occur when the operating system, DBMS software, or the system process
crashes. In such cases, RAM contents are lost, but the disk is intact.

Causes:

• OS crash

• DBMS process crash

• Power failure (affecting volatile memory)

Effect: All active transactions are lost from memory and must be recovered using logs.

3. Media (or Disk) Failures

These are hardware failures where the disk itself is damaged, resulting in loss of data stored
in the database.

Causes:

• Disk head crash

• Bad sectors or corrupt files

• Power surges damaging storage

Effect: The DBMS cannot access the database at all; recovery must be done from backups and
logs.

4. Communication Failures

These occur in distributed database systems, where transactions span multiple nodes.

Causes:

• Network disconnection

• Message loss

• Timeout between nodes

Effect: Distributed transactions may become incomplete or inconsistent across nodes.

5. Concurrency-Related Failures

These happen when multiple transactions interfere with each other inappropriately, despite
locking and isolation controls.

Causes:
 • Deadlocks (two transactions waiting on each other)

• Inconsistencies due to improper locking

• Lost update, dirty read, phantom read

Example: Two transactions update the same row simultaneously, violating consistency.

Difference Between Data Security and Data Integrity

Aspect Data Security Data Integrity

Protection of data from unauthorized Ensuring data is accurate, consistent,

Definition
access, theft, or damage and reliable over time

Prevent unauthorized access or Maintain correctness and

Goal
breaches trustworthiness of data

What is the correctness or validity of

Focus Area Who can access or modify the data
the data

- Authentication - Constraints (e.g., primary key,

Techniques - Authorization foreign key)
Used - Encryption - Transactions
- Firewalls - Checks

- Hacking - Data corruption

Threats
- Data leaks - Entry errors
Addressed
- Unauthorized changes - Inconsistencies

Covers confidentiality, integrity, and Focused specifically on validity and

Scope
availability (CIA triad) consistency

Only authorized HR staff can access Employee’s age must be a positive

Example
salary details number

In Simple Words:

• Data Security = Keep data safe from outsiders or attackers.

• Data Integrity = Keep data correct and consistent, even after updates or errors.

A Data Warehouse (DW) is a system used for storing, analyzing, and reporting large volumes
of historical data collected from different sources. It supports decision-making by transforming
raw data into meaningful information.

Components of Data Warehouse

Here are the main components along with their processes:

1. Data Sources
• Internal systems: OLTP databases, CRM, ERP

• External systems: Market feeds, third-party sources

• Formats: Databases, files, APIs

2. ETL Process (Extract, Transform, Load)

• Extract: Pull data from source systems

• Transform: Cleanse, aggregate, and reformat data

• Load: Store transformed data into the data warehouse

3. Data Staging Area

• Temporary storage for raw data

• Used for cleaning, transformation, and integration

• Not accessible by end users

4. Data Warehouse Repository

• Central database where cleaned, integrated, and structured data is stored

• Often designed using a star schema or snowflake schema

• Supports historical and subject-oriented storage

5. Metadata

• Data about the data

• Describes source, structure, transformations, load schedule, etc.

6. OLAP Engine (Online Analytical Processing)

• Enables multi-dimensional analysis

• Allows slicing, dicing, pivoting, roll-up, drill-down

7. Data Marts

• Subset of data warehouse

• Focused on specific business areas (e.g., sales, HR, finance)

 8. End-User Tools

• Reporting tools (e.g., Power BI, Tableau)

• Dashboards, query tools, and data mining applications

Diagram of Data Warehouse Architecture

+----------------+ +----------------+ +----------------+ +---------------------+

| Data Sources | ---> | Extract | ---> | Transform | ---> | Load |

+----------------+ +----------------+ +----------------+ +---------------------+

+----------------------+

| Staging Area |

+----------------------+

+----------------------+

| Data Warehouse |

| (Central Repository)|

+----------------------+

+----------------+---------------+------------------+

| | |

+----------+ +-------------+ +-----------------+

| Metadata | | OLAP | | Data Marts |

+----------+ +-------------+ +-----------------+

| |

+----------------+

+-------------------+

| End-User Tools |
 +-------------------+

(i) P ∪ Q (Union of P and Q)

Definition: Union includes all unique tuples from both relations (no duplicates).
Assumption: Both relations are union-compatible (same attributes and types).

Result: P ∪ Q

Pid Pname

001 abc

012 xyz

014 lmn

015 opq

016 sss

017 ssd

(ii) P × Q (Cartesian Product of P and Q)

Definition: Each row of P is combined with each row of Q.

Total rows = |P| × |Q| = 5 × 4 = 20 rows

Schema of Result:

([Link], [Link], [Link], [Link])

Sample of the result (only first 5 rows shown for space):

[Link] [Link] [Link] [Link]

001 abc 012 xyz

001 abc 014 lmn

001 abc 016 sss

001 abc 017 ssd

012 xyz 012 xyz

... ... ... ...

The result continues until all 20 combinations are listed.

Sequential File Organization

Sequential file organization is a method of storing records in a sequential order, usually
based on the value of a key field (like ID number, name, etc.). This means that the data is
physically stored on the storage medium in the same order as it is logically sorted.

Example Diagram of Sequential File Organization

Assume records are stored in ascending order of Student_ID:

+------------+-----------+-----------+

| Student_ID | Name | Marks |

+------------+-----------+-----------+

| 1001 | Aditi | 85 |

| 1002 | Bharat | 78 |

| 1003 | Charu | 92 |

| 1004 | Deepak | 88 |

| 1005 | Esha | 81 |

+------------+-----------+-----------+

• The file is sorted by Student_ID.

• New data must be inserted in the correct order (might involve rewriting).

Characteristics of Sequential File Organization

• Records are stored one after the other in sorted order.

• Access is done sequentially from the beginning.

• Mainly used in batch processing systems.

• Supports only sequential access (not random).

Advantages of Sequential File Organization

Advantage Explanation

Simple to Design Easy to implement and maintain.

Efficient for Sequential Best suited for applications that process all records (e.g.,
Access payroll, billing).

Works well where entire file is read or written (e.g., generating

Good for Batch Jobs
monthly reports).
Advantage Explanation

Less Storage Overhead No need for indexes or pointers.

Disadvantages of Sequential File Organization

Disadvantage Explanation

Searching a specific record requires reading from the

Slow Search
beginning.

Inserting in the middle or deleting a record may require

Difficult Insert/Delete
rewriting the entire file.

Not Suitable for Real-Time

Cannot support fast/interactive access.
Systems

Poor Random Access

You cannot jump directly to a specific record.
Performance

Use Cases

• Payroll processing

• Bank statement generation

• Utility billing systems

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1NF (First Normal Form)

Definition:

A relation is in 1NF if:

• It contains only atomic (indivisible) values.

• Each column contains only one value per row (no repeating groups or arrays).

Example (Not in 1NF):

Roll_No Name Subjects

101 Amit Math, Science

102 Rekha English, Computer

Here, Subjects contains multiple values (not atomic).

 Convert to 1NF:

Roll_No Name Subject

101 Amit Math

101 Amit Science

102 Rekha English

102 Rekha Computer

Now each attribute contains only atomic values, so it's in 1NF.

2NF (Second Normal Form)

Definition:

A relation is in 2NF if:

1. It is already in 1NF, and

2. No partial dependency exists (i.e., non-prime attributes are fully functionally

dependent on the entire primary key).

Example (Not in 2NF):

Consider a composite key: (Roll_No, Subject)

Roll_No Subject Student_Name Marks

101 Math Amit 80

101 Science Amit 75

102 English Rekha 85

Here, Student_Name depends only on Roll_No, not the full key (Roll_No, Subject) ⇒ Partial
dependency ⇒ Not in 2NF

Convert to 2NF:

Student Table:

Roll_No Student_Name

101 Amit
Roll_No Student_Name

102 Rekha

Marks Table:

Roll_No Subject Marks

101 Math 80

101 Science 75

102 English 85

Now all non-key attributes are fully dependent on the entire primary key.

3NF (Third Normal Form)

Definition:

A relation is in 3NF if:

1. It is in 2NF, and

2. No transitive dependency exists (i.e., a non-prime attribute should not depend on

another non-prime attribute).

Example (Not in 3NF):

Emp_ID Emp_Name Dept_ID Dept_Name

1 Ravi D01 HR

2 Sita D02 IT

Here:

• Dept_Name depends on Dept_ID (not on Emp_ID)

• Emp_ID → Dept_ID and Dept_ID → Dept_Name

⇒ Transitive dependency ⇒ Not in 3NF

Convert to 3NF:

Employee Table:

Emp_ID Emp_Name Dept_ID

1 Ravi D01
Emp_ID Emp_Name Dept_ID

2 Sita D02

Department Table:

Dept_ID Dept_Name

D01 HR

D02 IT

Now, each non-prime attribute depends only on the key, no transitive dependencies.

Why Do We Need Locks in DBMS?

In Database Management Systems (DBMS), multiple transactions often run concurrently to

improve system performance and resource utilization. However, this concurrency can lead to
problems such as:

• Lost updates

• Dirty reads

• Uncommitted data access

• Inconsistent reads

Locks are used to ensure:

• Data consistency

• Isolation (one of the ACID properties)

• Controlled concurrent access

Types of Locks in DBMS

1. Binary Locks

• Each data item is either locked (1) or unlocked (0)

• No distinction between read/write

2. Shared Lock (S Lock)

• A transaction can read the data.

• Multiple transactions can hold a shared lock simultaneously.

• Used for reading only.

3. Exclusive Lock (X Lock)

• A transaction can read and write the data.

• Only one transaction can hold an exclusive lock on a data item.

 • Prevents both read and write by others.

Example: Shared vs Exclusive Locks

Transaction Operation Lock Type Can Others Read? Can Others Write?

T1 Read(X) Shared Lock Yes No

T2 Write(X) Exclusive Lock No No

Two-Phase Locking (2PL)

Definition:

Two-Phase Locking Protocol ensures serializability by dividing the execution of a transaction

into two phases:

1. Growing Phase:

o Transaction acquires all the locks it needs.

o No release is allowed.

2. Shrinking Phase:

o Transaction releases locks.

o No new locks can be acquired.

Diagram: Two-Phase Locking (2PL)

Time →

+-------------------+-------------------+

| Growing Phase | Shrinking Phase |

| (acquire locks) | (release locks) |

+-------------------+-------------------+

Example Timeline:

T1: Lock(X)

T1: Lock(Y)

T1: Unlock(X)
T1: Unlock(Y)

The transaction must not acquire any new locks after it starts releasing.

Example of 2PL:

Transaction T1:

1. Lock(X) → Growing phase

2. Lock(Y)

3. Read(X)

4. Write(Y)

5. Unlock(X) → Shrinking phase

6. Unlock(Y)

→ T1 follows 2PL because it locks first, then unlocks.

Advantages of 2PL

• Guarantees conflict-serializability

• Prevents issues like dirty reads and lost updates

Disadvantages of 2PL

• Can cause deadlocks (e.g., two transactions waiting on each other's locks)

• May reduce concurrency and throughput

• What is the Need for Concurrent Transactions in DBMS?

• In a multi-user database system, many users might want to access or modify data
simultaneously. Executing concurrent transactions (i.e., running multiple
transactions at the same time) offers several benefits:
•
• Why Do We Need Concurrent Transactions?

Reason Explanation
Improved Multiple users can work simultaneously → better CPU &
Performance resource utilization.
Better Throughput More transactions completed per unit time.
Minimized Waiting
Users don't need to wait for others to finish.
Time
 Reason Explanation
Efficient Resource Disk I/O, memory, and processor can be used by multiple users
Use concurrently.
•
• Problems with Concurrent Transactions
• Without proper control (like locking or scheduling), concurrent transactions may lead
to inconsistencies. Let’s explore the key issues:
•
• 1. Lost Update Problem
• Occurs When: Two transactions read the same data and update it, but one update
is overwritten by the other.
• Example:
• Initial value of X = 100
• T1:

• Read X (100)
• X = X + 50 → 150
• Write X
• T2:

• Read X (100)
• X = X - 30 → 70
• Write X
• Final Value = 70, but should be 120
Problem: T1’s update was lost.
•
• 2. Dirty Read Problem (Uncommitted Dependency)
• Occurs When: One transaction reads data modified by another uncommitted
transaction.
• Example:
• T1:

• Update X = 500
• T2:

• Read X (500) — uses this value

• T1:

• Rollback → X = original
• Problem: T2 used a dirty (invalid) value.
•
• 3. Inconsistent Read (Non-repeatable Read)
• Occurs When: A transaction reads the same data twice and gets different values
because another transaction updated it in between.
• Example:
• T1:
• Read X → 200
• ... (some time passes)
 • Read X again → 300
• T2 (in between):

• Update X = 300
• Commit
• Problem: T1 sees inconsistent data.
•
• 4. Phantom Read
• Occurs When: A transaction reads a set of rows, and another transaction
inserts/deletes rows that would have matched the read criteria.
• Example:
• T1:

• SELECT * FROM Orders WHERE amount > 1000

• -- Sees 3 rows
• T2:

• INSERT INTO Orders VALUES (..., amount = 1500)

• COMMIT
• T1 (re-runs same query):

• -- Now sees 4 rows

• Problem: T1 experiences a phantom row.

Justification for Object-Oriented Databases (OODB) over Relational Databases

(RDBMS)

Relational databases (RDBMS) are powerful for structured, tabular data. However, they have
limitations when handling complex, interrelated, and multimedia data (e.g., CAD/CAM,
scientific data, multimedia, etc.).

Object-Oriented Databases (OODB) or Object-Relational DBMS (ORDBMS) were developed to

overcome these limitations by integrating object-oriented programming principles into
databases.

Why Prefer Object-Oriented/OR Databases?

Limitation of RDBMS How OODB/ORDBMS Solves It

Can't directly store objects Stores complex objects as-is

Poor support for complex data Supports complex/recursive data types (arrays, structs, etc.)

Limited data encapsulation Supports methods/functions with data

No inheritance or polymorphism Supports class hierarchies and inheritance

Identity by value only Supports object identity (OID)

 Key Concepts in Object-Relational Database Systems (ORDBMS)

1. Complex Data Types

Object-relational systems allow you to define and use user-defined data types such as:

• Nested records/tuples (row types)

• Arrays/lists

• Multimedia (BLOBs, CLOBs)

Example:

CREATE TYPE AddressType AS (

street VARCHAR,

city VARCHAR,

zip INT

);

CREATE TABLE Person (

id INT,

name VARCHAR,

address AddressType

);

2. Inheritance

Like in OOP, one type (or table) can inherit attributes and methods from another.

• Promotes reusability and extensibility

• Supports subclassing of types

Example:

CREATE TYPE PersonType AS (

name VARCHAR,

age INT

) NOT FINAL;

CREATE TYPE StudentType UNDER PersonType (

 roll_no INT,

course VARCHAR

);

Now, StudentType inherits fields from PersonType.

3. Object Identity (OID)

• In relational databases, identity = primary key (by value)

• In object databases, identity is system-assigned and immutable, even if values change

Advantage:

• Objects can be compared by identity, not just value.

• Multiple references can point to the same object (even if no common value)

4. Reference Types (REF)

• Used to create pointers/references to objects in another table

• Supports object navigation, like object references in programming languages

Example:

CREATE TYPE DepartmentType AS (

dept_id INT,

dept_name VARCHAR

);

CREATE TABLE Department OF DepartmentType;

CREATE TABLE Employee (

emp_id INT,

emp_name VARCHAR,

dept REF DepartmentType

);

Here, dept in Employee is a reference to a Department object.

What is Functional Dependency in DBMS?

A Functional Dependency (FD) is a constraint between two sets of attributes in a relation from
a database.

Definition:

In a relation R, an attribute Y is said to be functionally dependent on attribute X (written as X →

Y) if:

For each value of X, there is exactly one value of Y.

Example of Functional Dependency

Consider a table: Student

Roll_No Name Department

101 Aditi CS

102 Rahul IT

103 Aditi CS

In this table:

• Roll_No → Name means:

If you know the Roll_No, you can uniquely determine the Name.

• Roll_No → Department is also valid, as Roll_No is unique.

But:

• Name → Roll_No is not valid, because two students can have the same name.

Types of Functional Dependencies

Type Description

Trivial FD If Y ⊆ X, then X → Y is always true. Example: Roll_No, Name → Name

Non-Trivial FD X → Y where Y is not a subset of X

Full Functional Dep. Y depends on the whole of X, not part of it

Partial Dependency Y depends on part of a composite key

Transitive Dependency X → Y and Y → Z implies X → Z

Why is Functional Dependency Important?

• It is the basis for normalization (1NF, 2NF, 3NF, BCNF).

 • Helps to remove redundancy and ensure data integrity.

• Helps in database design by identifying primary keys, candidate keys, etc.

Real-Life Example

Consider an Employee table:

Emp_ID Name Dept_ID Dept_Name

1 Ravi D01 HR

2 Sita D02 IT

Here:

• Emp_ID → Name, Dept_ID, Dept_Name

• Dept_ID → Dept_Name (i.e., one department ID maps to one department name)

• Data Definition Language (DDL) in SQL

• DDL commands are used to define and modify the structure of database objects
such as tables, schemas, indexes, and views. These commands affect the schema
and do not manipulate data directly.
• Here are three important DDL commands:
•
• 1. CREATE
• Purpose:
• Used to create new database objects like tables, views, indexes, schemas, etc.
• Syntax:

• CREATE TABLE table_name (

• column1 datatype,
• column2 datatype,
• ...
• );
• Example:
• CREATE TABLE Student (
• Roll_No INT PRIMARY KEY,
• Name VARCHAR(50),
• Age INT
• );
• This creates a table named Student with three columns.
•
• 2. ALTER
• Purpose:
 • Used to modify the structure of an existing object.
You can add, modify, or delete columns, constraints, etc.
• Syntax:
• ALTER TABLE table_name
• ADD column_name datatype;
•
• ALTER TABLE table_name
• MODIFY column_name new_datatype;
•
• ALTER TABLE table_name
• DROP COLUMN column_name;
• Example:

• ALTER TABLE Student

• ADD Email VARCHAR(100);
• Adds a new column Email to the Student table.
•
• 3. DROP
• Purpose:
• Used to remove database objects permanently (like tables or views).
• Syntax:
• DROP TABLE table_name;
• Example:
• DROP TABLE Student;
• Deletes the Student table and all of its data and structure.

(i) Weak Entity and Strong Entity

Strong Entity:

• An entity that has a primary key.

• Its existence does not depend on any other entity.

Example:

Employee(Emp_ID, Name, Dept) → Emp_ID is the primary key.

Weak Entity:

• An entity that does not have a primary key of its own.

• Depends on a strong entity for its identification.

• Identified using a foreign key + partial key.

Example:

Dependent(Dep_Name, Relation, Emp_ID) → depends on Employee.

 Weak entities always have a total participation in the identifying relationship.

(ii) Multivalued and Dependency

Multivalued Attribute:

• An attribute that can have multiple values for a single entity.

Example:

Student(Name, Phone) → One student may have multiple phone numbers.

To normalize: Move to a new table:

StudentPhone(Student_ID, Phone)

Dependency:

• Functional Dependency (FD) is a constraint between two attributes.

X → Y means: if you know X, you can uniquely determine Y.

Example:

Roll_No → Name

Knowing Roll_No allows you to determine the student's name.

(iii) Data Dictionary

Definition:

A data dictionary is a centralized repository of metadata that stores information about

database objects like:

• Tables

• Columns

• Data types

• Constraints

• Relationships

Types:

• Active: Automatically updated by DBMS.

• Passive: Manually maintained.

Uses:

• Helps in database design and documentation.

 • Supports query optimization and validation.

• Assists users and developers in understanding database structure.

(iv) Query Processing

Definition:

Query processing is the series of steps taken by a DBMS to execute a SQL query efficiently.

Phases of Query Processing:

1. Parsing: Syntax and semantic check.

2. Translation: SQL → Relational algebra.

3. Optimization: Choose the best execution plan (using indexes, join orders, etc.).

4. Execution: The query is run and results returned.

Goal:

• Ensure correctness

• Achieve efficient access

• Minimize I/O and CPU cost

• Here is a comparison between the traditional file-based system and the database
approach, highlighting their key differences and similarities:
•
• 1. Definition

Aspect File-Based System Database Approach

A system where data is stored in A system where data is stored in a structured
Definition separate files and managed by format and managed using a Database
application programs. Management System (DBMS).
•
• 2. Data Redundancy

File-Based System Database Approach

High data redundancy (same data may be Redundancy is minimized through
stored in multiple files). normalization and centralized control.
•
• 3. Data Integrity

File-Based System Database Approach

Difficult to enforce data integrity rules Data integrity can be enforced using constraints
(e.g., valid age, unique ID). (e.g., PRIMARY KEY, CHECK).
•
 • 4. Data Consistency

File-Based System Database Approach

Inconsistencies may occur due to data Consistency is maintained due to centralized
duplication across files. control and integrity constraints.
•
• 5. Data Sharing

File-Based System Database Approach

Limited data sharing; each application Supports multi-user access with proper
accesses its own files. concurrency control.
•
• 6. Data Security

File-Based System Database Approach

Poor security; difficult to restrict access Strong security features like user roles,
at different levels. authentication, and permissions.
•
• 7. Data Access

File-Based System Database Approach

Data access is through custom application Data is accessed using high-level query
programs. language (SQL).
•
• 8. Flexibility

File-Based System Database Approach

Rigid structure; changes require Highly flexible; structure can be modified with
rewriting application programs. minimal changes to applications.
•
• 9. Backup and Recovery

File-Based System Database Approach

Backup and recovery must be handled DBMS provides built-in backup and recovery
manually. features.
•
• 10. Cost

File-Based System Database Approach

Lower initial cost but higher long-term Higher initial cost but more efficient and scalable
maintenance. in the long run.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Desirable Properties of Decomposition in DBMS

Decomposition is the process of breaking a relation (table) into two or more sub-relations to
eliminate redundancy, anomalies, and to achieve normalization.

However, for decomposition to be useful and valid, it must satisfy three key desirable
properties:

1. Lossless-Join Decomposition

Definition:
A decomposition is lossless if the original relation can be exactly reconstructed by joining the
decomposed relations.

Why it's important:

• To preserve all original data after decomposition.

• Prevents data loss during join operations.

Example:

Consider a relation:
R(EmployeeID, Name, Department, DepartmentLocation)

Suppose we decompose it into:

• R1(EmployeeID, Name, Department)

• R2(Department, DepartmentLocation)

We can join R1 and R2 using the common attribute Department.

This decomposition is lossless if Department is a key in either R1 or R2.

2. Dependency Preservation

Definition:
A decomposition is dependency-preserving if all functional dependencies (FDs) from the
original relation can still be enforced without having to join the decomposed relations.

Why it's important:

• Ensures that data integrity rules are not lost.

• Reduces computational cost (no need to re-join tables to enforce FDs).

Example:

Original relation:
R(StudentID, CourseID, Instructor)

FDs:

• StudentID, CourseID → Instructor

Decompose into:
 • R1(StudentID, CourseID)

• R2(CourseID, Instructor)

Now the original dependency StudentID, CourseID → Instructor is not preserved, because it
spans both R1 and R2.
This decomposition is not dependency-preserving.

3. No Redundancy / Avoidance of Anomalies

Definition:
Decomposition should eliminate redundancy, which helps avoid:

• Insertion anomaly

• Update anomaly

• Deletion anomaly

Example:

Original relation:
R(StudentID, StudentName, Department, DepartmentLocation)

If Department and DepartmentLocation are repeated for many students, this creates
redundancy.

Decompose into:

• R1(StudentID, StudentName, Department)

• R2(Department, DepartmentLocation)

Now each department and its location are stored only once, avoiding redundancy and
anomalies.

What is Query Processing?

Query processing is the series of steps a Database Management System (DBMS) follows to
interpret, optimize, and execute a query (usually written in SQL) to retrieve or manipulate data
efficiently.

In simpler terms, it is how the DBMS translates a user's SQL query into low-level operations
that access the physical data stored in the database.

Objectives of Query Processing:

• To produce correct results

• To perform operations in the most efficient way

• To minimize time and resource usage (CPU, disk I/O, memory)

 Basic Steps in Query Processing:

Step
Step Name Description
No.

- SQL query is parsed for syntax and semantic correctness.

1 Parsing and Translation - Converts SQL into an internal form (e.g., relational
algebra).

- Multiple equivalent strategies to execute the query are

considered.
2 Query Optimization
- The most efficient execution plan (based on cost) is
chosen.

- A detailed low-level plan is generated that includes

Query Evaluation Plan
3 algorithms (e.g., index scan, join methods).
Generation
- It shows the exact order and method to access tables.

- The DBMS executes the query evaluation plan.

4 Query Execution - Data is fetched from disk, processed, and returned to the
user.

Example:

SQL Query:

SELECT Name FROM Employee WHERE Department = 'IT';

Steps involved:

1. Parsing: Check SQL syntax and identify tokens (SELECT, FROM, WHERE, etc.).

2. Translation: Convert it to a relational algebra expression like:

σ_Department='IT'(Employee) → π_Name

3. Optimization: Choose between using an index scan or full table scan.

4. Execution: Retrieve matching records and return the Name column.

Summary Diagram:

SQL Query

[1] Parsing & Translation

[2] Optimization

↓
[3] Evaluation Plan Generation

[4] Execution

Result

Limitations of Relational Databases

Relational databases (RDBMS) are widely used, but they have several limitations, especially
when dealing with complex, modern data and applications. Here are the major limitations:

1. Inability to Handle Complex Data Types

• RDBMSs primarily deal with structured data like numbers, strings, and dates.

• They struggle with multimedia data (images, audio, video), complex objects (CAD files,
maps), and user-defined types.

2. Poor Support for Inheritance and Reusability

• RDBMSs do not support inheritance, which is a fundamental concept in modern

software development.

• There is no concept of encapsulation, making reusability and modularity difficult.

3. Impedance Mismatch

• There’s a mismatch between object-oriented application programs and the relational

data model.

• Programmers often face difficulty mapping objects in code to rows in tables,

increasing complexity and development time.

4. Limited Semantics

• Relationships in RDBMS are restricted to foreign keys and joins.

• They do not naturally support complex relationships, behaviors, and constraints

required in modern applications.

5. Scalability for Hierarchical/Networked Data

• RDBMS is not ideal for storing deeply nested or hierarchical structures, such as XML,
JSON, or social network graphs.
 Need for Object-Oriented Databases (OODBMS)

To overcome these limitations, Object-Oriented Databases were developed. They combine the
database capabilities with object-oriented programming principles.

Why Object-Oriented Databases?

Feature Explanation

OODBMS supports multimedia, spatial, and complex user-

1. Complex Data Handling
defined data types.

Every object has a unique object identifier (OID), independent

2. Object Identity
of its attribute values.

Supports class hierarchies, so subclasses can inherit

3. Inheritance
properties and methods from parent classes.

Data and behavior (methods) are stored together as objects,

4. Encapsulation
just like in object-oriented programming.

5. Closer Integration with Makes it easier to store and retrieve application objects
OOP Languages without mapping them to relational tables.

6. Support for Versioning and Useful for applications like CAD/CAM, where object states
Persistence change over time.

Example: Real-World Use Case

In a Relational DBMS:

To store a Shape object, you may need multiple tables for Circle, Rectangle, etc., and write joins
to retrieve them.

In an Object-Oriented DBMS:

You can have a base class Shape and subclasses like Circle, Rectangle with attributes and
methods stored as-is. No need for joins—objects are stored and retrieved directly.

(i) SELECTION (σ)

Purpose:
Retrieves specific rows (tuples) from a relation that satisfy a given condition.

Symbol: σ
Syntax: σ<condition>(Relation)
Example:
Relation: Employee

EmpID Name Dept Salary

101 Alice HR 50000

102 Bob IT 70000

103 Raj IT 60000

Query: Get employees from IT department.

Relational Algebra: σDept = 'IT'(Employee)

Result:

EmpID Name Dept Salary

102 Bob IT 70000

103 Raj IT 60000

(ii) PROJECTION (π)

Purpose:
Retrieves specific columns (attributes) from a relation.

Symbol: π
Syntax: π<attribute list>(Relation)

Example:
Query: List only the names of employees.
Relational Algebra: πName(Employee)

Result:

Name

Alice

Bob

Raj

(iii) CARTESIAN PRODUCT (×)

Purpose:
Combines all possible pairs of tuples from two relations.

Symbol: ×
Syntax: Relation1 × Relation2
Example:

Employee:

EmpID Name

1 Alice

2 Bob

Department:

DeptID DeptName

D1 HR

D2 IT

Query: Employee × Department

Result:

EmpID Name DeptID DeptName

1 Alice D1 HR

1 Alice D2 IT

2 Bob D1 HR

2 Bob D2 IT

(iv) JOIN (⨝)

Purpose:
Combines related tuples from two relations based on a common attribute.

Symbol: ⨝
Syntax: Relation1 ⨝<condition> Relation2

Example:

Employee:

EmpID Name DeptID

1 Alice D1

2 Bob D2

Department:
DeptID DeptName

D1 HR

D2 IT

Query: Join employee with department.

Relational Algebra: Employee ⨝ [Link] = [Link]

Result:

EmpID Name DeptID DeptName

1 Alice D1 HR

2 Bob D2 IT

(v) UNION (∪)

Purpose:
Combines distinct tuples from two relations.

Symbol: ∪
Syntax: Relation1 ∪ Relation2
(Requires same schema: same number & type of attributes)

Example:

A:

EmpID Name

101 Alice

102 Bob

B:

EmpID Name

102 Bob

103 Raj

Query: A ∪ B

Result:

EmpID Name

101 Alice

102 Bob
EmpID Name

103 Raj

(vi) SET DIFFERENCE (−)

Purpose:
Returns tuples that are in the first relation but not in the second.

Symbol: −
Syntax: Relation1 − Relation2

Example:

A:

EmpID Name

101 Alice

102 Bob

B:

EmpID Name

102 Bob

103 Raj

Query: A − B

Result:

EmpID Name

101 Alice

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1. Entities and Attributes

1.1. Patient

• Attributes:

o PatientID (Primary Key)

o Name

o Age

o Gender

o Address
 o PhoneNumber

o BloodGroup

1.2. Doctor

• Attributes:

o DoctorID (Primary Key)

o Name

o Specialization

o PhoneNumber

o Email

1.3. Department

• Attributes:

o DeptID (Primary Key)

o DeptName

o Location

1.4. Appointment

• Attributes:

o AppointmentID (Primary Key)

o Date

o Time

o PatientID (Foreign Key)

o DoctorID (Foreign Key)

1.5. Treatment

• Attributes:

o TreatmentID (Primary Key)

o Description

o Date

o Cost
 o PatientID (Foreign Key)

o DoctorID (Foreign Key)

1.6. Room

• Attributes:

o RoomID (Primary Key)

o RoomType

o ChargesPerDay

o AvailabilityStatus

1.7. Admission

• Attributes:

o AdmissionID (Primary Key)

o PatientID (Foreign Key)

o RoomID (Foreign Key)

o AdmissionDate

o DischargeDate

2. Relationships and Cardinalities

Patient —< Appoints >— Doctor

• Relation: A patient can make multiple appointments with doctors, and a doctor can
have multiple appointments with patients.

• Cardinality: Many-to-Many

• Resolved via: Appointment entity.

Doctor — Works_In — Department

• Relation: A doctor works in one department, but a department can have many
doctors.

• Cardinality: One-to-Many (Department → Doctor)

Patient — Receives — Treatment

 • Relation: A patient can receive multiple treatments; each treatment is administered by
one doctor.

• Cardinality: Many-to-Many (via Treatment with FK to both Patient and Doctor)

Patient — Admitted_To — Room

• Relation: A patient can be admitted to a room; a room can be assigned to one patient at
a time.

• Cardinality: Many-to-One (Patient → Room) via Admission

3. ER Diagram Overview (Textual Representation)

csharp

CopyEdit

[Patient] ——< Appoints >—— [Doctor] —— Works_In —— [Department]

| |

Receives Administers

| |

[Treatment] [Appointment]

[Patient] —— Admitted_To —— [Room]

[Admission]

4. Constraints

• Primary Keys are underlined.

• Foreign Keys:

o [Link] → [Link]

o [Link] → [Link]

o [Link] → [Link]

o [Link] → [Link]

o [Link] → [Link]

o [Link] → [Link]
 o [Link] → [Link]

• Room Availability Constraint: Only one active admission per room at a time.

Physical DBMS Architecture Explained with Diagram

The physical architecture of a Database Management System (DBMS) refers to the internal
working of the DBMS and how data is actually stored, retrieved, and managed on the storage
medium (usually a disk). It focuses on the hardware-level and storage-level components that
support database operations.

Diagram of Physical DBMS Architecture

+-------------------------+

| Query Processor |

+-------------------------+

+-------------------------+

| Execution Engine |

+-------------------------+

+--------------------+----------------------+

| |

v v

+-------------------------+ +----------------------------+

| Buffer Manager |<---------->| Transaction Manager |

+-------------------------+ +----------------------------+

| |

v v

+-------------------------+ +----------------------------+

| File Manager |<---------->| Recovery Manager |

+-------------------------+ +----------------------------+

v
+-------------------------+

| Disk Storage |

+-------------------------+

Explanation of Each Component

1. Query Processor

o Interprets SQL queries and converts them into low-level instructions.

o Passes the query to the execution engine for processing.

2. Execution Engine

o Executes the query plan (e.g., selecting rows, joining tables).

o Communicates with the buffer manager for accessing data blocks.

3. Buffer Manager

o Manages the buffer pool in main memory.

o Ensures frequently accessed data blocks are kept in memory to reduce disk I/O.

o Handles reading/writing between disk and memory.

4. Transaction Manager

o Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).

o Coordinates concurrent transactions and manages locks.

5. Recovery Manager

o Handles failure recovery.

o Uses logs to restore the database to a consistent state after a crash.

6. File Manager

o Manages the structure of files and pages on disk.

o Maps high-level records to low-level blocks and sectors.

7. Disk Storage

o The physical medium where data, logs, indexes, and metadata are stored.

o Managed in terms of blocks or pages.

Key Features of Physical Architecture

• Focuses on performance optimization, data storage, and resource management.

• Handles I/O operations, disk scheduling, and data buffering.

• Crucial for tasks like indexing, data clustering, and compression.

• 1. INSERT Command
• Used to add new records (rows) into a table.
• Syntax:

• INSERT INTO table_name (column1, column2, ...)

• VALUES (value1, value2, ...);
• Example:

• INSERT INTO Students (StudentID, Name, Age)

• VALUES (101, 'Amit', 21);
• This adds a new student with ID 101 to the Students table.
•
• 2. UPDATE Command
• Used to modify existing records in a table.
• Syntax:

• UPDATE table_name
• SET column1 = value1, column2 = value2, ...
• WHERE condition;
• Example:

• UPDATE Students
• SET Age = 22
• WHERE StudentID = 101;
• This updates the age of the student with ID 101 to 22.
•
• 3. DELETE Command
• Used to remove existing records from a table.
• Syntax:

• DELETE FROM table_name

• WHERE condition;
• Example:

• DELETE FROM Students

• WHERE StudentID = 101;
• This deletes the student with ID 101 from the table.
•
• 4. SELECT Command
• Used to retrieve data from one or more tables.
• Syntax:

• SELECT column1, column2, ...

• FROM table_name
• WHERE condition;
• Example:

• SELECT Name, Age

 • FROM Students
• WHERE Age > 20;
• This retrieves the names and ages of students who are older than 20.

Use of Sequential File Organization

Sequential File Organization is a method of storing records in a specific sequence, usually

based on the value of a key field (like EmployeeID, Roll Number, etc.).

Main Uses of Sequential File Organization

1. Efficient for Batch Processing

o Best suited for applications where large volumes of data need to be processed in
order (e.g., payroll systems, billing systems).

o Example: Generating monthly salary reports sorted by employee ID.

2. Simple to Design and Use

o The structure is easy to implement.

o Ideal for read-only or append-only operations where updates are rare.

3. Efficient for Sorted Data Access

o Accessing data in a sorted manner is very fast.

o Example: Reading all student records in roll number order for exam result
generation.

4. Good for Static or Historical Data

o Useful when records don’t change frequently.

o Example: Archived transaction logs, yearly account statements.

Limitations (for context)

• Slow for random access or frequent updates.

• Insertion or deletion requires shifting of records to maintain order.

(a) Entity Integrity and Referential Integrity Constraints in DBMS

These are two essential integrity constraints that ensure the accuracy and consistency of
data in a relational database.

1. Entity Integrity Constraint

Definition:
Entity integrity ensures that every table (relation) must have a primary key, and the primary
key value cannot be NULL.
It ensures that each row (tuple) in a table is uniquely identifiable.

Rule:

Primary key column(s) must have unique and NOT NULL values.

Example:

StudentID (PK) Name Age

101 Aman 20

102 Riya 21

NULL Mohit 19 → Invalid (violates entity integrity)

• Here, StudentID is the primary key.

• Inserting a NULL in the primary key column is not allowed.

2. Referential Integrity Constraint

Definition:

Referential integrity ensures that a foreign key value must either be NULL or match an
existing primary key value in the referenced table.
It maintains valid references between tables.

Rule:

Foreign key must refer to an existing value in the parent table.

Example:

Table: Departments

DeptID (PK) DeptName

10 HR

20 IT

Table: Employees

EmpID Name DeptID (FK)

1 Amit 10

2 Renu 20

3 Rakesh 30 → Invalid (DeptID 30 not found in Departments)

 • DeptID in Employees is a foreign key referencing DeptID in Departments.

• Value 30 does not exist in the parent table, so it violates referential integrity.

Explanation of Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF)

Normalization is the process of organizing data to minimize redundancy and dependency. Both
3NF and BCNF aim to achieve a better database design.

Third Normal Form (3NF)

Definition:

A relation is in 3NF if:

1. It is in Second Normal Form (2NF), and

2. It has no transitive dependency — i.e., no non-prime attribute should depend on

another non-prime attribute.

Non-prime attribute = attribute that is not part of any candidate key

Transitive dependency = A → B and B → C implies A → C (undesired if B and C are non-prime)

Example:

Consider this relation:

Student(StudentID, Name, DeptName, HOD)

• StudentID → Name, DeptName

• DeptName → HOD

So, StudentID → DeptName → HOD → This is a transitive dependency

Here:

• StudentID is the primary key.

• HOD is transitively dependent on StudentID through DeptName.

To convert into 3NF:

Break into two relations:

Student(StudentID, Name, DeptName)

Department(DeptName, HOD)

Now there’s no transitive dependency, and both tables are in 3NF.

Boyce-Codd Normal Form (BCNF)

 Definition:

A relation is in BCNF if:

1. It is in 3NF, and

2. For every non-trivial functional dependency X → Y,

X must be a super key.

In simpler terms: Left side of every functional dependency must be a super key.

Example:

Course(CourseID, Instructor, Room)

Functional Dependencies:

• CourseID → Instructor

• Room → Instructor

Candidate key: CourseID

Now see: Room → Instructor, but Room is not a super key.

This violates BCNF.

To convert into BCNF:

Break into:

RoomInstructor(Room, Instructor)

CourseRoom(CourseID, Room)

Now:

• Room → Instructor is isolated

• CourseID → Room maintains original dependency

• All FDs have left-hand side as a super key → BCNF is satisfied

Difference Between 3NF and BCNF

Feature 3NF BCNF

Based on Functional and transitive dependencies Functional dependencies

Handles Transitive dependencies Even stronger form of 3NF

Some anomalies if non-candidate keys

Allows No anomalies; stricter
determine other attributes
Feature 3NF BCNF

Example A non-prime attribute depending on another non- Left side of FD not being a
Violation prime super key

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Why Do We Need Indexes in DBMS?

Indexes in a database are used to speed up the retrieval of data. Without indexes, the
database must perform a full table scan, which is slow for large tables.

Benefits of Indexes:

• Faster search and retrieval

• Improved query performance

• Speeds up operations like SELECT, WHERE, JOIN, ORDER BY

Types of Indexes:

(i) Primary Index

Definition:

A primary index is built on the primary key of a table. The data is sorted based on the primary
key, and the index stores pointers to data blocks.

Key Points:

• One primary index per table

• Dense or sparse index possible

• Ordered on the primary key

Example:

For table:
Student(StudentID, Name, Age)
If StudentID is the primary key, the primary index is created on StudentID.

(ii) Clustering Index

Definition:

A clustering index is created on a non-primary key column where data is physically stored
together (clustered) based on that column’s values.

Key Points:
 • Only one clustering index per table

• Used when data is frequently retrieved by a non-primary attribute

Example:

For table:
Employee(EmpID, DeptID, Name)
If many queries use DeptID, a clustering index on DeptID makes sense.
All employees from the same department will be stored close together on disk.

(iii) Secondary Index

Definition:

A secondary index is created on non-primary key columns and does not affect the physical
order of the table.

Key Points:

• You can create multiple secondary indexes

• Good for frequent search queries on non-key columns

• Does not change the order of table data

Example:

For table:
Book(BookID, Title, Author, Price)
If users often search by Author, create a secondary index on Author.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

What is a Transaction in DBMS?

A transaction is a sequence of one or more SQL operations (such as INSERT, UPDATE,

DELETE, etc.) performed as a single logical unit of work.

A transaction must either complete entirely or have no effect at all — ensuring the
consistency and integrity of the database.

Example of a Transaction:

Consider a bank transfer from Account A to Account B:

-- Start Transaction

1. UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 'A';

2. UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 'B';

-- Commit Transaction
If one step fails, the transaction must be rolled back, so neither account is affected.

Properties of a Transaction (ACID Properties)

1. Atomicity

• All or nothing: Either all operations in the transaction are completed, or none.

• If a system crash occurs, incomplete operations are rolled back.

In the bank example: Deducting ₹1000 from A but failing to credit B would violate atomicity.

2. Consistency

• A transaction brings the database from one valid state to another.

• Ensures that data integrity constraints are not violated.

In the bank example: The total amount in the system before and after the transfer remains
the same.

3. Isolation

• Concurrent transactions are executed as if they were run one after the other.

• Prevents interference between transactions.

If two users transfer money at the same time, isolation ensures the operations don’t conflict
or corrupt data.

4. Durability

• Once a transaction is committed, its changes are permanently saved, even in case of
a crash.

If power fails after money is transferred and the transaction is committed, the changes
remain in the database.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Why Do We Need Database Recovery?

Database recovery is the process of restoring the database to a correct state after a failure
such as:

• System crash

• Power failure

• Disk failure
 • Transaction failure

The goal is to ensure data consistency, integrity, and reliability by recovering from
incomplete or incorrect transactions.

Types of Failures That Require Recovery

• Transaction failure (e.g., logical error, system error)

• System crash (e.g., OS crash, memory failure)

• Media failure (e.g., disk corruption)

• Application/software error

Comparison: Backward Recovery vs Forward Recovery

Feature Backward Recovery Forward Recovery

Also Known As Undo Redo

Undo the effects of failed or Redo the effects of committed

Purpose
incomplete transactions transactions after a crash

When It Happens When an error is detected After restoring data from a backup

Uses log files + backup to redo

Data Source Uses log files to undo changes
changes

Affected Transactions that were active at Transactions that were committed but
Transactions time of crash not yet written to disk

Example Use Power fails during money transfer Power fails after successful transfer →
Case → undo partial update redo the committed update

Example Scenario:

Let’s say a system crashed after the following events:

1. T1 starts → updates account A

2. T2 starts → updates account B

3. T1 commits

4. System crashes before T2 completes

• Backward Recovery will undo T2 (incomplete)

• Forward Recovery will redo T1 if it was committed but not flushed to disk
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

What is a Hash Join in DBMS?

A Hash Join is an efficient join algorithm used to perform equi-joins (joins using = condition)
between two large relations (tables), especially when no index is available.

How Hash Join Works:

Let’s say we are joining two relations:

SELECT *

FROM R JOIN S

ON R.A = S.A;

Steps of Hash Join:

1. Build Phase:

o Choose the smaller relation (say, R) and create an in-memory hash table on
the join attribute (R.A).

o Each tuple from R is hashed into appropriate buckets.

2. Probe Phase:

o Scan the larger relation (S).

o For each tuple in S, use the same hash function to find matching tuples in the
hash table built from R.

Cost Calculation for Simple Hash Join

Assumptions:

• Relation R has b(R) blocks

• Relation S has b(S) blocks

• Enough memory to hold the smaller relation R in memory

Total Cost:

Cost = b(R) + b(S)

Explanation:

• b(R): To read all blocks of relation R into memory and build the hash table

• b(S): To scan relation S and probe the hash table

Example:
 • Let’s say:

o Relation R = 500 blocks

o Relation S = 2000 blocks

Then:

Cost = 500 + 2000 = 2500 block I/Os

When is Hash Join Preferred?

• When no index is available on the join keys

• When relations are large

• When one relation fits in memory

Variants of Hash Join:

• Simple Hash Join – as explained above.

• Grace Hash Join – used when neither relation fits entirely in memory (uses partitioning).

• Hybrid Hash Join – optimizes memory usage during the build phase.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(i) Multi-list File Organization

Definition:
Multi-list file organization allows records in a file to be linked using more than one field (key).
It supports multiple linked lists, each sorted by a different field.

Features:

• Provides faster access to records through multiple paths (e.g., by department or by

project).

• Useful in many-to-many relationships.

• Maintains multiple pointers for each record.

Example:
In a student-course file, one list may be maintained by StudentID, and another by CourseID,
allowing fast access by either.

(ii) Second Normal Form (2NF)

Definition:
A relation is in 2NF if:

1. It is in First Normal Form (1NF), and

 2. No partial dependency exists — i.e., non-prime attributes must be fully functionally
dependent on the entire primary key.

Applicable to: Tables with composite primary keys.

Example:
Relation: Enrollment(StudentID, CourseID, CourseName)
If CourseName depends only on CourseID (not the full key), it's a partial dependency, violating
2NF.

(iii) Serializable Schedule

Definition:
A schedule is serializable if the outcome is the same as some serial execution of the same
transactions (i.e., one after the other with no interleaving).

Importance:

• Ensures consistency in concurrent transactions.

• Prevents problems like dirty read, lost update, etc.

Types:

• Conflict Serializable: Based on swapping non-conflicting operations.

• View Serializable: Based on preserving the view (final result).

(iv) Data Warehouse

Definition:
A data warehouse is a centralized repository that stores large volumes of historical,
integrated, and subject-oriented data for analysis and reporting.

Features:

• Supports OLAP (Online Analytical Processing)

• Used for decision-making

• Stores data from multiple sources (via ETL: Extract, Transform, Load)

Example:
A retail chain uses a data warehouse to analyze sales across locations over the past 5 years.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(i) List of Entities

1. Customer

2. Account

3. Transaction
 (ii) Attributes of Each Entity

Customer

• CustomerID (Primary Key)

• Name

• PhoneNumber

• Address

Account

• AccountNumber (Primary Key)

• CustomerID (Foreign Key)

• Balance

Transaction

• TransactionID (Primary Key)

• AccountNumber (Foreign Key)

• Date

• Type (Withdrawal / Deposit)

• Amount

(iii) Relationships Between Entities

1. Customer–Account

o One customer can have multiple accounts → 1:M (one-to-many)

o Each account belongs to one customer

2. Account–Transaction

o One account can have multiple transactions → 1:M

o Each transaction belongs to one account

(iv) E-R Diagram

Here's a textual representation of the E-R diagram:

Customer (CustomerID, Name, PhoneNumber, Address)

 |

|1:M

Account (AccountNumber, Balance, CustomerID)

|1:M

Transaction (TransactionID, Date, Type, Amount, AccountNumber)

• Arrows show 1:M relationships.

• Foreign keys shown in child entities.

(v) Convert E-R Diagram to Relations

Customer(

CustomerID PRIMARY KEY,

Name,

PhoneNumber,

Address

Account(

AccountNumber PRIMARY KEY,

Balance,

CustomerID FOREIGN KEY REFERENCES Customer(CustomerID)

Transaction(

TransactionID PRIMARY KEY,

Date,

Type,

Amount,

AccountNumber FOREIGN KEY REFERENCES Account(AccountNumber)

)

(vi) List All Constraints

Primary Keys:

• Customer(CustomerID)

• Account(AccountNumber)

• Transaction(TransactionID)

Foreign Keys:

• [Link] → references [Link]

• [Link] → references [Link]

Other Constraints:

• CustomerID, AccountNumber, TransactionID must be unique and NOT NULL

• Balance, Amount should be non-negative

• Type should be either "Withdrawal" or "Deposit" (check constraint)

• Referential integrity between entities (foreign key constraints)

+++++++++++++++++++++++++++++++++++++=======================================

Relations

Student(id, name, phone, p_code)

Programme(p_code, title, duration, credits)

(i) List the id and name of all students of Programme whose p_code is “MCA”

SELECT id, name

FROM Student

WHERE p_code = 'MCA';

(ii) Find the programmes which have more than 80 credits

SELECT *

FROM Programme

WHERE credits > 80;

 (iii) Find the number of students in each programme

SELECT p_code, COUNT(*) AS num_students

FROM Student

GROUP BY p_code;

(iv) List id, name, p_code, title of all students of the programme whose p_code is “CIT”

SELECT [Link], [Link], S.p_code, [Link]

FROM Student S

JOIN Programme P ON S.p_code = P.p_code

WHERE S.p_code = 'CIT';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(i) Transaction

Definition:

A transaction is a sequence of one or more SQL operations (e.g., INSERT, UPDATE, DELETE)
performed as a single logical unit of work.

It must follow the ACID properties — Atomicity, Consistency, Isolation, and Durability.

Example:

BEGIN;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 'A1';

UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 'A2';

COMMIT;

This is a money transfer between accounts A1 and A2. Either both operations succeed, or
none do.

(ii) Locking

Definition:

Locking is a concurrency control mechanism that prevents multiple users from modifying
the same data simultaneously, to ensure data integrity.
There are two types of locks:

• Shared Lock (Read)

• Exclusive Lock (Write)

Example:

If User A is updating a row in the Employee table, a write lock is applied.

If User B tries to read or write the same row, they must wait until User A finishes.

(iii) Checkpoint

Definition:

A checkpoint is a point in time at which the state of the database is saved to disk.
It reduces the amount of log records that must be reprocessed during recovery.

Example:

In a banking system, a checkpoint may occur every 10 minutes.

If the system crashes, recovery starts from the last checkpoint, not from the beginning.

(iv) Recovery

Definition:

Recovery is the process of restoring the database to a consistent state after a crash or failure
using logs and backups.

It involves:

• Undo (Backward Recovery) – for incomplete transactions

• Redo (Forward Recovery) – for committed but unsaved transactions

Example:

If a transaction was halfway through updating multiple records when power failed, recovery
uses logs to undo partial updates or redo committed ones.

(v) Query Cost

Definition:

Query cost refers to the resources (I/O, CPU, time) required to execute a query.
The query optimizer uses cost estimation to select the most efficient execution plan.

Example:

SELECT * FROM Employees WHERE Department = 'IT';

 • Using an index on Department reduces cost.

• A full table scan has a higher cost if there is no index.

Tools like EXPLAIN in SQL show estimated query cost.

==========================================+++++++++++++++++++++++++++

(i) Relational DBMS (RDBMS) vs Object-Oriented DBMS (OODBMS)

Feature RDBMS OODBMS

Based on objects (similar to OOP in

Data Model Based on tables (relations)
programming)

Stores data in rows and Stores data as objects, including attributes and
Data Storage
columns methods

Relationships Managed using foreign keys Managed using object references

Query Uses OQL (Object Query Language) or extended

Uses SQL
Language SQL

Schema Design Structured and tabular Includes inheritance, encapsulation, etc.

Example MySQL, PostgreSQL, Oracle db4o, ObjectDB, Versant

Example:

• In RDBMS, a Student and Course are separate tables linked by foreign key.

• In OODBMS, Student can contain objects of Course.

(ii) Data Mining vs Data Warehousing

Feature Data Mining Data Warehousing

Process of analyzing large data sets Process of storing integrated

Definition
to find patterns or knowledge historical data from various sources

Discover hidden patterns, trends, Organize and store data for reporting
Purpose
predictions and analysis

Techniques Classification, Clustering, Regression, ETL (Extract, Transform, Load), OLAP

Used Association Rules operations

Historical structured data ready for

Output New knowledge or insights
analysis

Snowflake, Amazon Redshift, Teradata,

Tools Weka, RapidMiner, SAS, R
Microsoft SSIS

Example:
 • Data Warehousing: Stores 5 years of sales data from different branches.

• Data Mining: Analyzes that data to predict future customer buying behavior.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

What is an Update Anomaly?

An update anomaly occurs when redundant data in a database causes inconsistencies

during an update operation.

This typically happens in un-normalized tables (or poorly normalized ones), where the same
piece of information is repeated in multiple rows.

Example of Update Anomaly

Let’s consider a relation (table) storing student and program information:

Table: StudentProgramme

StudentID Name ProgrammeCode ProgrammeName

101 Riya MCA Master of CS

102 Aman MCA Master of CS

103 Ankit MCA Masters in Comp. Sci

What happened?

• The programme name for "MCA" was updated for one student (Ankit) but not for the
others, causing inconsistent data.

Why It Happens

• The same information (like ProgrammeName) is repeated across multiple rows.

• An update to one row requires updating all duplicates — if not done, inconsistency
arises.

How to Avoid Update Anomaly

Use Normalization (at least 2NF or 3NF):

• Separate data into multiple related tables.

• Use foreign keys to link them.

Normalized Design:

1. Student(StudentID, Name, ProgrammeCode)

 2. Programme(ProgrammeCode, ProgrammeName)

Now, if you update the ProgrammeName in just one place (in the Programme table), all linked
students reflect the change.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

What is Data Independence in DBMS?

Data Independence refers to the ability to modify the schema at one level of a database
system without affecting the schema at the next higher level.

It helps in decoupling how data is stored from how it is used, enabling flexibility and
maintainability in database systems.

Types of Data Independence:

1. Logical Data Independence

• Ability to change the logical schema (like tables, views, relationships) without
changing application programs.

Example:
If you add a new column email to the Student table:

ALTER TABLE Student ADD email VARCHAR(100);

Old queries like:

SELECT name FROM Student;

will still work without modification.

2. Physical Data Independence

• Ability to change the physical storage (like indexing, file organization) without
affecting the logical schema.

Example:You reorganize the Student table to store data in a different file format or move it to
SSD instead of HDD.
Still, the user queries:

SELECT * FROM Student WHERE id = 101;

remain unchanged.

Three-Level DBMS Architecture Supporting Data Independence

1. External Level (View Level) – What the user sees

 2. Logical Level (Conceptual Schema) – What data is stored and how it’s related

3. Physical Level (Storage) – How the data is actually stored

External Schema ← (Logical Independence)

Logical Schema ← (Physical Independence)

Physical Schema

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++==============

Super Key, Candidate Key, and Primary Key in a Relational Database

In a relational database, keys are used to uniquely identify rows (tuples) in a table. Let's break
down the three main types of keys with definitions and examples.

(i) Super Key

Definition:

A super key is any combination of attributes that can uniquely identify a row in a table.

It may contain extra (redundant) attributes.

Example:

Consider a Student table:

StudentID Name Email Phone

101 Riya riya@[Link] 9876543210

102 Aman aman@[Link] 9988776655

Possible super keys:

• {StudentID}

• {Email}

• {StudentID, Name}

• {Phone, Name}

All these uniquely identify a student.

(ii) Candidate Key

 Definition:

A candidate key is a minimal super key — i.e., a super key with no redundant attributes.

There can be multiple candidate keys in a table.

From the above example, valid candidate keys:

• {StudentID}

• {Email}

• {Phone}
(Each is minimal and uniquely identifies a student)

(iii) Primary Key

Definition:

A primary key is one of the candidate keys chosen by the database designer to uniquely
identify records.

It must be:

• Unique

• Not NULL

Example:

If we choose StudentID as the primary key:

PRIMARY KEY (StudentID)

Then StudentID becomes the official way the DBMS uniquely identifies each row

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Generalization and Specialization in E-R Model

Both generalization and specialization are abstraction techniques used in the Entity-
Relationship (E-R) model to manage complexity in database design, especially when dealing
with hierarchies.

(i) Generalization

Definition:

Generalization is the process of combining two or more lower-level entities into a higher-
level (general) entity based on common features.

It’s a bottom-up approach.

Example:
Entities:

• Car(RegistrationNo, Model, EngineNo)

• Bike(RegistrationNo, Model, EngineNo)

These can be generalized into:

• Vehicle(RegistrationNo, Model, EngineNo)

Now, Car and Bike become subtypes of Vehicle.

Vehicle

/ \

Car Bike

This simplifies the schema by grouping shared attributes in a common entity.

(ii) Specialization

Definition:

Specialization is the process of creating sub-entities (subclasses) from a higher-level

(general) entity based on unique characteristics.

It’s a top-down approach.

Example:

Entity:

• Employee(EmpID, Name, Salary)

We can specialize into:

• Manager(EmpID, DeptManaged)

• Engineer(EmpID, SkillSet)

Here, Employee is the superclass, and Manager and Engineer are subclasses.

Employee

/ \

Manager Engineer

Each subclass inherits all attributes from Employee and adds its own.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Primary Index vs Secondary Index in DBMS

Indexes are used to speed up data retrieval in a database by providing faster access to records.
 (i) Primary Index

Definition:

A Primary Index is built on the primary key (or another unique key) of a table.
It is an index on an ordered file where the data is sorted based on the key.

Key Characteristics:

• Built on unique fields (like primary key)

• Table must be physically ordered on the index key

• Can be sparse (only one index entry per block)

Example:

Table: Student(StudentID, Name, Age)

If data is stored in order of StudentID, a primary index on StudentID would point to the starting
block of each group of StudentIDs.

Index Entry Pointer to Block

101 Block 1

104 Block 2

107 Block 3

So if you search for StudentID = 105, the DBMS goes directly to Block 2.

(ii) Secondary Index

Definition:

A Secondary Index is created on a non-primary key column.

It is used when the data is not ordered on the indexed column.

Key Characteristics:

• Can be non-unique

• Multiple entries may point to the same data block

• Does not affect physical order of data

Example:

Table: Student(StudentID, Name, Age)

If you create a secondary index on Age, and multiple students have the same age, the index
would look like:
Age Pointers to Records

18 [R1, R3]

19 [R2, R4, R5]

This index helps efficiently search all students aged 19, even though the table is not sorted on
Age.

Which is More Advantageous?

Criteria Primary Index Secondary Index

When queries are on primary key or When queries are on non-key or

Use Case
ordered field unordered fields

Search Speed Faster if on sorted files Good for random searches

Duplicates
No (on unique fields only) Yes (can be on duplicate values)
Allowed

Data Order
Yes (data must be sorted) No
Affected

Advantage:

• Primary Index is more efficient for searching by primary key, and requires less space
(because it's sparse).

• Secondary Index is more flexible, allowing indexing on any field, including non-unique
ones.

Which is better depends on the use case:

Use primary index for frequent lookups by primary key,
use secondary index when filtering by non-key columns (like age, department, etc.)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Given Relation:

Student(student_id, name, coursecode, coursename, marks)

(i) List all Functional Dependencies (FDs):

From the description:

• student_id → name
(Each student has a unique name)

• coursecode → coursename
(Each coursecode maps to one course name)
 • {student_id, coursecode} → marks
(Marks depend on both student and course combination)

So, the complete list of functional dependencies:

1. student_id → name

2. coursecode → coursename

3. {student_id, coursecode} → marks

(ii) Anomalies in the Relation

This relation is not normalized, which causes data anomalies:

1. Update Anomaly

• If the course name for a course changes, it must be updated in all rows where the
course is listed.

• Risk of inconsistent updates.

2. Insertion Anomaly

• You can’t insert a new course unless a student is enrolled in it.

• Because course info is tied to student records.

3. Deletion Anomaly

• If a student is deleted, and they were the only one enrolled in a course, the course
information is lost too.

(iii) Normalization into 2NF and 3NF

Step 1: First Normal Form (1NF)

The relation already appears to be in 1NF:

• Atomic attributes (no multi-valued or composite fields)

• Unique tuples

Step 2: Second Normal Form (2NF)

2NF Rule: Remove partial dependencies (i.e., when a non-prime attribute depends only on
part of the primary key).

Primary Key: {student_id, coursecode} (since a student can take many courses)

Partial Dependencies:
 • student_id → name

• coursecode → coursename

Decompose into 2NF Relations:

1. Student(student_id, name)

2. Course(coursecode, coursename)

3. Enrollment(student_id, coursecode, marks)

(Composite key: student_id + coursecode)

Step 3: Third Normal Form (3NF)

3NF Rule: No transitive dependencies – non-prime attributes must depend only on the key.

• All three relations from 2NF are already in 3NF:

o In Student, student_id → name

o In Course, coursecode → coursename

o In Enrollment, marks depends only on the full key

Final Relations in 3NF:

1. Student(student_id, name)

2. Course(coursecode, coursename)

3. Enrollment(student_id, coursecode, marks)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(3) Multi-Valued Dependency (MVD)

Definition:

A Multi-Valued Dependency (MVD) occurs in a relation when one attribute in a table

determines multiple independent values of another attribute, separately from other
attributes.

MVD is denoted as: A ↠ B

It means: If two rows have the same value of A, then they can be recombined with all
combinations of values of B.

Example:

Let’s consider a relation:

Student(StudentID, Hobby, Language)

 • A student can have multiple hobbies

• A student can know multiple languages

• But hobbies and languages are independent of each other

Sample Data:

StudentID Hobby Language

101 Cricket English

101 Painting English

101 Cricket Hindi

101 Painting Hindi

Here, we have redundancy because Hobby and Language are independent but are repeated in
combinations.

MVD Present:

• StudentID ↠ Hobby

• StudentID ↠ Language

To remove MVD (i.e., go to 4NF), we decompose:

1. Student_Hobby(StudentID, Hobby)

2. Student_Language(StudentID, Language)

(4a) ACID Properties of a Transaction

A – Atomicity:

All operations in a transaction are treated as a single unit — either all succeed or none.

C – Consistency:

The database must remain in a valid state before and after the transaction.

I – Isolation:

Concurrent transactions must execute as if they were run one after another, without
interference.

D – Durability:

Once a transaction is committed, its changes are permanent, even in case of a crash.
 Example: Money Transfer

BEGIN;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccID = 'A1';

UPDATE Accounts SET Balance = Balance + 500 WHERE AccID = 'A2';

COMMIT;

If the system crashes after the first update, Atomicity ensures both operations are rolled back.

(4b) Log-Based Recovery Technique

Definition:

Log-based recovery uses a transaction log that records:

• Transaction start/end

• Every write operation: old and new values

If a crash occurs, the log is used to:

• UNDO uncommitted transactions

• REDO committed but not saved changes

Example:

Log Entries:

<START T1>

<WRITE T1, A, 100, 150>

<WRITE T1, B, 200, 250>

<COMMIT T1>

• If the system crashes after commit but before writing to disk:

➤ The system will REDO T1.

• If crash occurs before commit:

➤ The system will UNDO T1 using old values.

(4c) Natural Join

Definition:
A Natural Join combines two tables based on common attributes (same name and domain),
automatically removing duplicate columns.

Example:

Tables:

Student(StudentID, Name, CourseID)

Course(CourseID, Title)

Query:

SELECT * FROM Student NATURAL JOIN Course;

The join happens automatically on CourseID.

Algorithm to Implement Join: Nested Loop Join

How It Works:

1. For each row in the first table (R)

2. Scan the second table (S)

3. Compare join attributes

4. If matched, output the combined tuple

Pseudo Code:

for each tuple r in R:

for each tuple s in S:

if [Link] = [Link]:

output (r ⋈ s)

Simple but slow for large datasets

Optimized versions: Indexed Nested Loop, Hash Join, Sort-Merge Join

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

5(a) Concepts in Object-Oriented and Object-Relational DBMS

(i) Complex Data Types

Definition:
In Object-Oriented DBMS (OODBMS) and Object-Relational DBMS (ORDBMS), complex data
types refer to user-defined and nested data types, unlike basic types (INT, VARCHAR) used in
RDBMS.

Examples of Complex Types:

• Tuples (records/objects)

• Arrays

• Lists

• Nested tables

• User-defined objects

Example (ORDBMS):

CREATE TYPE AddressType AS (

street VARCHAR(50),

city VARCHAR(50),

zip INT

);

CREATE TABLE Employee (

emp_id INT,

name VARCHAR(50),

address AddressType

);

(ii) Type Inheritance

Definition:

Type inheritance allows one object type to inherit attributes and methods from another, just
like in object-oriented programming.

Common in OODBMS and supported partially in ORDBMS.

Example:

-- Base type

CREATE TYPE Person AS (

name VARCHAR,

age INT
) NOT FINAL;

-- Subtype

CREATE TYPE Student UNDER Person (

course VARCHAR

);

Here, Student inherits name and age from Person.

(iii) Object Definition Language (ODL)

Definition:

ODL is used to define object types, attributes, methods, and relationships in Object-
Oriented DBMS.

It's a part of Object Data Management Group (ODMG) standard.

Example:

interface Student {

attribute string name;

attribute int age;

relationship Course takes;

};

ODL lets you define object schemas like how DDL is used in RDBMS.

5(b) Multi-Dimensional Data in Data Warehouse

Definition:

In a data warehouse, multi-dimensional data means data is modeled as a cube with

dimensions and measures.
Each dimension represents a perspective (e.g., time, product, region), and measures represent
numerical facts (e.g., sales).

Example:

Suppose you're analyzing sales data.

• Dimensions: Time, Product, Region

• Measure: Total Sales

You can organize this as a 3D cube:

┌──────────── Time ────────────┐

│ │

Product [Sales]

│ │

Region ─────────────────────────

You can perform OLAP operations like:

• Slice: Fix one dimension (e.g., only 2024)

• Dice: Select specific values in multiple dimensions

• Drill-down: Go from yearly → monthly → daily data

5(c) Classification and Clustering in Data Mining

(i) Classification

Definition:

Classification is a supervised learning technique used to predict categorical labels based on

input attributes.

• Requires labeled training data

• Common algorithms: Decision Trees, Naive Bayes, SVM

Example:

Classifying emails as "Spam" or "Not Spam" using attributes like subject, sender, frequency of
keywords.

(ii) Clustering

Definition:

Clustering is an unsupervised learning technique used to group similar data points without
predefined labels.

• Groups are based on similarity

• Common algorithms: K-Means, DBSCAN, Hierarchical clustering

Example:

A retail store clusters customers into groups:

 • Budget buyers

• Premium buyers

• Seasonal buyers
(based on spending habits, location, frequency)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(i) Entities:

1. Student

2. Programme

3. Fee_Payment

(ii) Attributes of Entities:

1. Student

• Student_ID (Primary Key)

• Name

• Contact_Phone

• Programme_Code (Foreign Key to Programme)

2. Programme

• Programme_Code (Primary Key)

• Programme_Name

• Duration

• Fee

3. Fee_Payment

• Payment_ID (Primary Key)

• Student_ID (Foreign Key to Student)

• Amount_Paid

• Payment_Date

(iii) Relationships:

1. Student–Enrolled–Programme

o Many students enroll in one programme.

o (Many-to-One) relationship.
 2. Student–Makes–Fee_Payment

o One student can make multiple payments.

o (One-to-Many) relationship.

(iv) ER Diagram:

+---------------+ +-------------------+ +--------------------+

| Programme |<---------| Student |--------->| Fee_Payment |

|---------------| |-------------------| |--------------------|

| Prog_Code (PK)| | Student_ID (PK) | | Payment_ID (PK) |

| Name | | Name | | Student_ID (FK) |

| Duration | | Contact_Phone | | Amount_Paid |

| Fee | | Prog_Code (FK) | | Payment_Date |

+---------------+ +-------------------+ +--------------------+

Note:

- Arrow from Student to Programme = Many students to one programme.

- Arrow from Student to Fee_Payment = One student makes many payments.

(v) Constraints (Primary & Foreign Keys):

Primary Keys (PK):

• Student: Student_ID

• Programme: Programme_Code

• Fee_Payment: Payment_ID

Foreign Keys (FK):

• Student.Programme_Code → Programme.Programme_Code

• Fee_Payment.Student_ID → Student.Student_ID

Other Constraints:

• Student_ID, Programme_Code must be unique and not null.

• Amount_Paid ≥ 0.

• Payment_Date must be a valid date.

(vi) Relational Schema (Conversion to Tables):

-- Programme Table

Programme(

Programme_Code PRIMARY KEY,

Programme_Name,

Duration,

Fee

);

-- Student Table

Student(

Student_ID PRIMARY KEY,

Name,

Contact_Phone,

Programme_Code,

FOREIGN KEY (Programme_Code) REFERENCES Programme(Programme_Code)

);

-- Fee_Payment Table

Fee_Payment(

Payment_ID PRIMARY KEY,

Student_ID,

Amount_Paid,

Payment_Date,

FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID)

);

Relations:

• Account(account_number, name, balance)

• Bank(branch_code, account_number, phone)

(i) List all the account numbers in the order of “name”.

SELECT account_number

FROM Account

ORDER BY name;

(ii) Find the account-number, which has the highest balance.

SELECT account_number

FROM Account

WHERE balance = (

SELECT MAX(balance)

FROM Account

);

If multiple accounts have the same highest balance, this will return all of them.

(iii) List the branch-code, account-number, name and balance of each account.

SELECT B.branch_code, A.account_number, [Link], [Link]

FROM Account A

JOIN Bank B ON A.account_number = B.account_number;

(iv) Find the number of accounts in each branch

SELECT branch_code, COUNT(account_number) AS num_accounts

FROM Bank

GROUP BY branch_code;

Transaction in DBMS (Database Management System)

A transaction is a single logical unit of work that may consist of one or more SQL operations
(like SELECT, INSERT, UPDATE, DELETE) that must either be fully completed or fully failed
(rolled back).

Key Properties of a Transaction (ACID):

1. Atomicity: All or nothing.

2. Consistency: Database moves from one consistent state to another.

 3. Isolation: Concurrent transactions should not interfere with each other.

4. Durability: Once committed, the transaction changes are permanent.

Example of a Transaction:

Suppose a banking application is transferring ₹1,000 from Account A to Account B.

BEGIN;

UPDATE Account SET balance = balance - 1000 WHERE account_number = 'A123';

UPDATE Account SET balance = balance + 1000 WHERE account_number = 'B456';

COMMIT;

This is a transaction because both operations must succeed together. If the system crashes
after debiting A but before crediting B, the database would be left in an inconsistent state.
Hence, transactions ensure either both updates happen or neither.

Problems with Concurrent Transactions:

When multiple transactions run at the same time (concurrently), data integrity issues may
arise if proper isolation is not maintained. These are called concurrency problems.

Common Concurrency Problems:

1. Lost Update

2. Dirty Read

3. Non-Repeatable Read

4. Phantom Read

Example: Lost Update Problem

Scenario: Two clerks updating the same account balance simultaneously.

• Initial Balance = ₹10,000

• Transaction T1: Adds ₹500

• Transaction T2: Deducts ₹1000

Steps (without proper isolation):

T1 reads balance: ₹10,000

T2 reads balance: ₹10,000

T1 updates to ₹10,500

T2 updates to ₹9,000

Final balance = ₹9,000 (T1's update is lost!)

Why is it a problem?
The ₹500 deposit by T1 is lost because T2 overwrote the balance without knowing T1's change.
This leads to data inconsistency.

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Relational Model in Database Management System (DBMS)

The relational model is a way of organizing data into tables (called relations). Each table
represents an entity and is made up of rows (records or tuples) and columns (attributes or
fields).

Key Concepts of Relational Model:

1. Relation (Table): A set of tuples (rows) with the same attributes.

2. Tuple (Row): A single record in a relation.

3. Attribute (Column): A named property or characteristic of the entity.

4. Domain: The set of valid values an attribute can take.

5. Primary Key: Uniquely identifies each tuple in a relation.

6. Foreign Key: Refers to the primary key of another table (used to define relationships).

Example:

Student Table

Student_ID Name Programme_Code

101 Anuj BCA

102 Riya MCA

Programme Table
Programme_Code Programme_Name Duration

BCA B.C.A. 3 Years

MCA M.C.A. 2 Years

Here:

• Student and Programme are relations.

• Student_ID is a primary key.

• Programme_Code in Student is a foreign key referencing Programme.

Difference: Relational DBMS vs Object-Oriented DBMS

Feature Relational DBMS (RDBMS) Object-Oriented DBMS (OODBMS)

Uses objects (like in object-oriented

Data Structure Uses tables (relations)
programming)

Model Based On Relational model (E.F. Codd) Object-oriented paradigm

Data
Rows and columns Objects with attributes and methods
Representation

Inheritance
Not supported Supported
Support

Complex Data Limited support (e.g., arrays in Full support for complex data (e.g.,
Types PostgreSQL) multimedia)

Query Language SQL OQL (Object Query Language)

MySQL, PostgreSQL, Oracle, SQL

Examples db4o, ObjectDB, Versant
Server

Business applications, reporting,

Usage CAD, multimedia, real-time systems
data analysis

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Define the First Normal Form (1NF) in RDBMS

First Normal Form (1NF) requires that:

• Each column in a table contains atomic (indivisible) values.

• Each row is unique.

• There are no repeating groups or arrays in any row.

 Example (Violating 1NF):

Student Table

| Student_ID | Name | Phone_Numbers |

|------------|--------|------------------------|

| 101 | Rahul | 9876543210, 8765432109 |

• Phone_Numbers contains multiple values, so it is not in 1NF.

Corrected (1NF) Table:

| Student_ID | Name | Phone_Number |

|------------|-------|---------------|

| 101 | Rahul | 9876543210 |

| 101 | Rahul | 8765432109 |

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Six Advantages of Database Management System (DBMS):

1. Data Redundancy Control: Prevents duplication of data by centralized control.

2. Data Integrity: Ensures accuracy and consistency of data over time.

3. Data Security: Provides controlled access through authentication and authorization.

4. Backup and Recovery: Automatically backs up data and restores it after failure.

5. Concurrent Access: Allows multiple users to access data simultaneously with

isolation.

6. Data Independence: Application programs remain unaffected by changes in data

structure.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Use of an Index in RDBMS:

An index is a data structure that improves the speed of data retrieval operations on a database
table.

Types of Indexes:

1. Primary Index – created on primary key, ensures uniqueness.

2. Secondary Index – created on non-primary attributes to improve query performance.

 Which is More Advantageous?

Primary Key Index is generally more advantageous because:

• It is automatically created when defining a primary key.

• It guarantees uniqueness and is often used for joins and lookups.

• It improves the performance of core database operations (search, sort, join).

Example:

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Department VARCHAR(30)

);

• Query: SELECT * FROM Employee WHERE Emp_ID = 101;

o Uses primary key index — fast access.

• Query: SELECT * FROM Employee WHERE Department = 'HR';

o Secondary index helps, but primary index is more efficient due to uniqueness
and better data organization.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

What is SQL?

SQL (Structured Query Language) is a standard language used to interact with relational
databases. It includes:

• DDL (Data Definition Language) – CREATE, ALTER, DROP

• DML (Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE

• DCL, TCL – for security and transactions

CREATE TABLE Example:

CREATE TABLE Student (

Student_ID INT PRIMARY KEY,

Name VARCHAR(50),
 Programme_Code VARCHAR(10),

Phone_Number VARCHAR(15)

);

This command creates a Student table with specified fields and data types. The PRIMARY KEY
ensures uniqueness of Student_ID.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Relationships in ER Diagram

One-to-One (1:1) Relationship:

Each entity in A is related to only one entity in B, and vice versa.

Example:

• Each student has one ID card, and each ID card belongs to only one student.

Diagram:

Student ----------- ID_Card

1 1

Many-to-Many (M:N) Relationship:

Multiple entities in A relate to multiple entities in B.

Example:

• Students can enroll in many courses, and each course can have many students.

Diagram:

Student --------< Enrolls >-------- Course

M N

This is usually implemented using a junction table (e.g., Enroll(Student_ID, Course_ID)).

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

*What is Lossless Join Decomposition?

Lossless Join Decomposition means breaking a relation into two or more sub-relations in such
a way that no data is lost when joining them back.

Definition:
A decomposition of relation R into R1 and R2 is lossless if:

R1 ⨝ R2 = R

(i.e., natural join of R1 and R2 gives the original relation)

Example:

Consider relation Student(Student_ID, Name, Department, HOD)

We decompose it into:

• R1(Student_ID, Name)

• R2(Student_ID, Department, HOD)

Here, Student_ID is common and is a key in R1, so the join is lossless.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

* Three-Level Architecture of DBMS

Three Levels:

1. External Level (View Level):

o User-specific views of data (only what's needed).

o Example: A student sees only his grades.

2. Conceptual Level (Logical Level):

o Entire database structure (tables, relationships).

o DBMS uses this to manage data logically.

3. Internal Level (Physical Level):

o How data is stored physically on disk (indexes, files).

Diagram:

+-------------------+

| External Level |

| (User Views) |

+-------------------+

+-------------------+

| Conceptual Level |
 | (Logical Schema) |

+-------------------+

+-------------------+

| Internal Level |

| (Physical Storage)|

+-------------------+

Data Independence:

• Logical Data Independence: Changes in conceptual level don’t affect external views.

• Physical Data Independence: Changes in physical storage don’t affect logical schema.

Example:

If we add an index to improve speed (physical level), user queries remain the same — that’s
physical data independence.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

* 2NF and 3NF with Example

2NF (Second Normal Form):

• A relation is in 2NF if it's in 1NF and has no partial dependency.

• Partial Dependency: A non-key attribute depends only on part of a composite key.

Example:

Course_Student(Course_ID, Student_ID, Student_Name, Course_Name)

PK = (Course_ID, Student_ID)

Problem: Student_Name depends on Student_ID only → Partial Dependency

2NF Decomposition:

1. Student(Student_ID, Student_Name)

2. Course_Student(Course_ID, Student_ID)

3. Course(Course_ID, Course_Name)
3NF (Third Normal Form):

• A relation is in 3NF if:

o It is in 2NF

o No transitive dependency: A non-key attribute should not depend on another

non-key attribute.

Example:

Employee(Emp_ID, Emp_Name, Dept_ID, Dept_Name)

PK = Emp_ID

Dept_Name depends on Dept_ID (which is not PK) → Transitive dependency

3NF Decomposition:

1. Employee(Emp_ID, Emp_Name, Dept_ID)

2. Department(Dept_ID, Dept_Name)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

* What is Data Recovery? How is it Performed?

Data Recovery is the process of restoring the database to a correct state after a failure.

Causes of Failure:

• System crash

• Transaction failure

• Disk crash

• Power failure

How it's Performed:

• Using logs (Write-Ahead Logging)

• Checkpoints

• Rollback and Redo operations

 Example:

Suppose a transaction debits ₹1000 but crashes before completion. Recovery uses the log to
rollback this partial update.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

* Query Optimization

Query Optimization is the process of choosing the most efficient query execution plan to
improve performance.

Example:

SELECT * FROM Orders WHERE Customer_ID = 101;

• Without index: full table scan

• With index on Customer_ID: fast lookup

The optimizer chooses the best plan using stats, indexes, etc.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

* Functional Dependencies and Normalization

Given:

• R = {A, B, C, D, E}

• FDs:

o A → BC

o B→E

o C→D

(i) What is the Key?

Start with A:

• A → BC

• B→E

• C→D

So, A → B, C → D, E
Hence, A+ = {A, B, C, D, E} = all attributes
 Candidate Key = A

(ii) Decompose R into 2NF:

2NF = No partial dependencies on part of primary key

Since A is the only key, and all dependencies are on A or its dependent attributes, it's already
in 2NF.

(iii) Decompose into 3NF:

Remove transitive dependencies

• A→B

• B→E (B → E is transitive via A → B)

• A→C

• C→D (C → D is also transitive via A → C)

3NF Decomposition:

1. R1(A, B, C) – from A → BC

2. R2(B, E) – from B → E

3. R3(C, D) – from C → D

All relations are in 3NF now.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(a) Data Mining

Definition:

Data Mining is the process of extracting useful patterns, knowledge, or insights from large
amounts of data using statistical, machine learning, or AI techniques.

Example:

An e-commerce company analyzes customer purchase history to:

• Predict what product a user might buy next

• Recommend products (Recommendation System)

 Diagram (Process):

Raw Data → Data Cleaning → Data Mining → Pattern Discovery → Decision Making

(b) Data Warehousing

Definition:

A Data Warehouse is a central repository of integrated data collected from multiple sources,
used for analysis and reporting.

Features:

• Subject-oriented

• Integrated

• Time-variant

• Non-volatile

Example:

A company collects sales data from different stores and stores it in a warehouse. Analysts can
then use this data to generate monthly or yearly sales reports.

Diagram:

[CRM] [ERP] [POS]

↓ ↓ ↓

→→→→→ Data Warehouse ←←←←←

Business Intelligence

(OLAP, Reports, Dashboards)

(c) NoSQL Databases

Definition:

NoSQL (Not Only SQL) databases are designed to handle unstructured or semi-structured
data, offering flexibility and scalability for big data and real-time applications.
 Types:

1. Document-based (e.g., MongoDB)

2. Key-Value store (e.g., Redis)

3. Column-based (e.g., Cassandra)

4. Graph-based (e.g., Neo4j)

Example:

A social media app stores posts with flexible fields using MongoDB:

"user": "Abhi",

"post": "Enjoying the trip!",

"likes": 102,

"tags": ["travel", "fun"]

(d) Locking in Transaction

Definition:

Locking is a concurrency control mechanism used to prevent inconsistency and conflict when
multiple transactions access the same data simultaneously.

Types of Locks:

• Shared Lock (S): Allows read

• Exclusive Lock (X): Allows read and write

Example:

• Transaction T1 reads account A (shared lock)

• Transaction T2 wants to update account A → has to wait (requires exclusive lock)

Diagram:

T1: S-Lock(A) → Read A → ... (holds lock)

T2: X-Lock(A) → WAIT (until T1 releases)

(e) Weak Entity

Definition:

A Weak Entity is an entity that cannot be uniquely identified by its own attributes alone and
needs a foreign key (from another entity) to be uniquely identified.

Example:

Consider Dependent of an Employee:

• Dependent(Name, Age) is not unique

• But Dependent(Employee_ID, Name) is

ER Diagram:

Employee --------- Dependent

| |

[Emp_ID] [Name, Age]

| |

|________(PK+FK)____|

Dependent is a weak entity; Employee is a strong entity.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(i) Entities:

1. Customer

2. Account

3. Ownership (To model many-to-many relationship between Customer and Account)

(ii) Attributes of the Entities:

1. Customer

• Customer_ID (Primary Key)

• Name

• Address

• Phone
2. Account

• Account_Number (Primary Key)

• Account_Type (e.g., savings, current)

• Balance

3. Ownership (Associative Entity)

• Customer_ID (Foreign Key → Customer)

• Account_Number (Foreign Key → Account)

Composite primary key: (Customer_ID, Account_Number)

(iii) Relationships Between Entities:

1. Customer–owns–Account

o Many-to-Many relationship

o One customer can own multiple accounts

o One account can belong to multiple customers (joint accounts)

(iv) Constraints:

Primary Keys:

• Customer(Customer_ID)

• Account(Account_Number)

• Ownership(Customer_ID, Account_Number)

Foreign Keys:

• Ownership.Customer_ID → Customer.Customer_ID

• Ownership.Account_Number → Account.Account_Number

Other Constraints:

• Customer_ID and Account_Number must be unique.

• Balance ≥ 0

• Contact Phone must follow a valid format.

(v) ER Diagram:

+-------------+ +--------------+ +--------------+

| Customer | | Ownership | | Account |

|-------------| |--------------| |--------------|

| Customer_ID |◄─────────────►| Customer_ID |─────────────►| Account_No |

| Name | | Account_No | | Account_Type |

| Address | +--------------+ | Balance |

| Phone |

+-------------+

• Customer ↔ Account is Many-to-Many, implemented via Ownership.

(vi) Conversion of ERD into Relations:

1. Customer Table:

CREATE TABLE Customer (

Customer_ID INT PRIMARY KEY,

Name VARCHAR(100),

Address VARCHAR(200),

Phone VARCHAR(15)

);

2. Account Table:

CREATE TABLE Account (

Account_Number INT PRIMARY KEY,

Account_Type VARCHAR(50),

Balance DECIMAL(10, 2)

);

3. Ownership Table (Associative Entity):

CREATE TABLE Ownership (

Customer_ID INT,

Account_Number INT,

PRIMARY KEY (Customer_ID, Account_Number),

FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),

FOREIGN KEY (Account_Number) REFERENCES Account(Account_Number)

);
 This structure:

• Maintains joint accounts (many-to-many)

• Preserves referential integrity

• Allows queries like “Find all accounts owned by a customer” and vice versa

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Relations Given:

• Student(enrol_no, name, course_code)

• Course(course_code, title, duration)

(i) Find the course_code and title of those courses whose duration is more than one
month:

SELECT course_code, title

FROM Course

WHERE duration > 1;

Assumes duration is measured in months (as typically expected).

(ii) List the title of the course taken by the student whose enrol_no is ‘S01’:

SELECT [Link]

FROM Student S

JOIN Course C ON S.course_code = C.course_code

WHERE S.enrol_no = 'S01';

(iii) Count the number of all the courses:

SELECT COUNT(*) AS total_courses

FROM Course;

(iv) List the enrol_no, name, title, duration for all the students:

SELECT S.enrol_no, [Link], [Link], [Link]

FROM Student S

JOIN Course C ON S.course_code = C.course_code;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(c) Problems of Concurrent Transactions

When multiple transactions are executed concurrently, they can interfere with each other and
cause data inconsistency if proper isolation is not maintained. The major problems of
concurrent transactions are:

1. Lost Update Problem

Occurs when two transactions read the same data and then update it, but the second update
overwrites the first one.

Example:

Initial balance = ₹10,000

• T1 reads balance = ₹10,000

• T2 reads balance = ₹10,000

• T1 adds ₹500 → balance = ₹10,500

• T2 deducts ₹1,000 → balance = ₹9,000

T1’s update is lost.

2. Dirty Read

Occurs when one transaction reads data written by another transaction that has not yet been
committed.

Example:

• T1 updates salary to ₹60,000 but has not committed.

• T2 reads the updated salary.

• T1 rolls back the change.

T2 read invalid/dirty data.

3. Non-repeatable Read

Occurs when a transaction reads the same data twice and gets different values because
another transaction updated the data in between.

Example:

• T1 reads balance = ₹5,000

• T2 updates balance to ₹6,000 and commits

 • T1 reads again and gets ₹6,000
T1 saw inconsistent results

4. Phantom Read

Occurs when a transaction re-executes a query and sees new rows added by another
committed transaction.

Example:

• T1 runs: SELECT * FROM Orders WHERE status='Pending'

• T2 inserts a new pending order and commits.

• T1 reruns the query and sees a new row.

Phantom row appeared during T1's execution.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(d) Data Mining and Data Warehousing

1. Data Mining

• Definition: The process of discovering patterns, trends, and useful information from
large datasets using AI/ML/statistics.

• Example: Predicting customer churn based on historical usage.

2. Data Warehousing

• Definition: A centralized repository that stores historical and integrated data from
multiple sources to support reporting and analysis.

• Example: A company stores all sales data from different branches in a data warehouse.

Relation to DBMS:

• A Data Warehouse uses a DBMS to store, manage, and query large volumes of data.

• Traditional DBMS is optimized for OLTP (transactional processing), while a data

warehouse is optimized for OLAP (analytical processing).

• Data is often extracted from DBMS, transformed, and loaded into a warehouse (ETL
process).

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(e) Referential Integrity

Definition:

Referential Integrity ensures that foreign key values in a table must match primary key
values in another (or the same) table — or be NULL (if allowed).

Example:

Tables:

Customer

Customer_ID | Name

------------|----

C001 | Riya

C002 | Aman

Order

Order_ID | Customer_ID | Amount

---------|-------------|-------

O101 | C001 | 5000

O102 | C003 | 4000 Invalid

Here:

• Order.Customer_ID is a foreign key referencing Customer.Customer_ID.

• C003 does not exist in Customer, violating referential integrity.

To Enforce Referential Integrity in SQL:

CREATE TABLE Order (

Order_ID INT PRIMARY KEY,

Customer_ID VARCHAR(10),

Amount DECIMAL,

FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)

);

This ensures that every order is linked to a valid customer.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Given:
 • Relation:
R={A,B,C,D,E,F}R = \{A, B, C, D, E, F\}R={A,B,C,D,E,F}

• Functional Dependencies (FDs):

1. A→BDA \rightarrow BDA→BD

2. B→EB \rightarrow EB→E

3. D→CFD \rightarrow CFD→CF

(i) What is the key of R?

We need to find the candidate key(s) of R — i.e., attribute(s) whose closure contains all
attributes in R.

Let’s compute the closure of A:

Compute A⁺:

Start with:
A⁺ = {A}

Apply FDs step-by-step:

• From A→BDA \rightarrow BDA→BD: A⁺ = {A, B, D}

• From B→EB \rightarrow EB→E: A⁺ = {A, B, D, E}

• From D→CFD \rightarrow CFD→CF: A⁺ = {A, B, D, E, C, F}

A⁺ = {A, B, C, D, E, F} = R

So, A is a candidate key.

(ii) Decompose R into 2NF and 3NF

We will first bring R into 2NF, then from 2NF to 3NF.

Step 1: Check for 1NF

1NF requires atomic values — this is assumed to be satisfied (as not specified otherwise).

Step 2: Decompose into 2NF

2NF:

• Must be in 1NF

• No partial dependencies (i.e., non-prime attribute depending on part of a composite

key)
But here:

• A is the only candidate key (single attribute)

• So there is no partial dependency (because there's no composite key)

So R is already in 2NF

Step 3: Decompose into 3NF

3NF condition:
A relation is in 3NF if for every functional dependency X → Y, one of the following holds:

1. X → Y is a trivial dependency (Y ⊆ X), or

2. X is a superkey, or

3. Every attribute in Y is a prime attribute (i.e., part of a candidate key)

Let’s evaluate each FD:

1. A→BDA \rightarrow BDA→BD

• A is a candidate key (superkey) → satisfies condition (2)

2. B→EB \rightarrow EB→E

• B is not a superkey

• E is not a prime attribute (not in any key)

→ Violates 3NF

3. D→CFD \rightarrow CFD→CF

• D is not a superkey

• C and F are not prime attributes

→ Violates 3NF

3NF Decomposition

We decompose based on violating FDs:

Create relations for each FD:

1. From A→BDA \rightarrow BDA→BD:

R1(A, B, D)

2. From B→EB \rightarrow EB→E:

R2(B, E)

3. From D→CFD \rightarrow CFD→CF:

R3(D, C, F)

Ensure key preservation:

• The original candidate key A must be present

• We already have A in R1, so key is preserved

Final 3NF Decomposed Relations:

1. R1(A, B, D) – from A → BD

2. R2(B, E) – from B → E

3. R3(D, C, F) – from D → CF

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(b) Dependency Preserving and Lossless Decomposition

1. Lossless Decomposition

A decomposition is lossless if joining the decomposed relations results in the original

relation — no data is lost.

Example (Lossless Decomposition):

Let’s say we have:

R(A, B, C) with FDs: A → B

Decompose into:

• R1(A, B)

• R2(A, C)

Now, join:
R1 ⨝ R2 = R (original relation)

This is lossless because attribute A (a key in R1) is common and functionally determines B
and C.

2. Dependency Preserving Decomposition

A decomposition is dependency preserving if all functional dependencies can still be

enforced without joining the decomposed relations.

Example (Dependency Preserving):

Original FDs:

• A→B

• B→C

Decompose R(A, B, C) into:

• R1(A, B)

• R2(B, C)

Each FD is present in one of the relations:

• A → B is in R1

• B → C is in R2

Hence, all dependencies are preserved.

Note:

It is possible for a decomposition to be lossless but not dependency-preserving, or vice versa.

Ideally, we want both.

(c) Four Properties of Transactions (ACID)

1. Atomicity

o All operations in a transaction are completed or none at all.

o Example: Transfer ₹1000 — debit & credit both must succeed or none.

2. Consistency

o Ensures the database moves from one valid state to another.

o Example: Constraints like balance ≥ 0 always hold.

3. Isolation
 o Concurrent transactions should not interfere with each other.

o Example: One user reading data should not see partial updates.

4. Durability

o Once a transaction is committed, its changes are permanent.

o Example: System crash after commit → data still saved.

3(a) Three Levels of Database Architecture

Defined by the ANSI/SPARC model:

1. External Level (View Level):

• Closest to the users

• Defines user-specific views

• Hides complexity

Example:
Student sees only their grades, not full tables.

2. Conceptual Level (Logical Level):

• Describes the structure of the whole database

• Includes entities, attributes, relationships

Example:
Tables like Student, Course, and their relationships

3. Internal Level (Physical Level):

• Defines how data is stored (indexes, files, blocks)

Example:
Indexing on Student_ID, storage in disk blocks

Diagram:

+-------------------+

| External Level |

| (User Views) |

+-------------------+
 ↓

+-------------------+

| Conceptual Level |

| (Logical Schema) |

+-------------------+

+-------------------+

| Internal Level |

| (Physical Storage)|

+-------------------+

3(b) Database Recovery & Logs

What is Recovery?

Database Recovery is the process of restoring the database to a consistent state after a
failure (crash, power loss, etc.).

Role of Logs in Recovery:

Logs are records of all database operations, especially before/after changes.

Example:

Transaction T1:

• Log:

[START T1]

[UPDATE Account SET balance = 5000 → 4000]

[COMMIT T1]

• If the system crashes after the update but before commit, recovery uses logs to
rollback.

• If commit is logged, recovery redoes the changes.

Recovery Uses Two Approaches:

1. Undo uncommitted transactions

 2. Redo committed ones

3(c) Features of Object-Oriented DBMS & Comparison with RDBMS

Features of Object-Oriented DBMS (OODBMS):

1. Complex Data Types: Can store objects, images, videos

2. Encapsulation: Data and methods stored together

3. Inheritance: Classes can inherit properties of other classes

4. Object Identity: Each object has a unique identity (OID)

5. Polymorphism: Same operation can behave differently based on object

6. Support for Relationships: Directly supports object references

OODBMS vs RDBMS

Feature OODBMS RDBMS

Data Model Object-oriented Relational (tables, rows)

Storage Stores objects directly Stores data in tabular format

Inheritance Supported Not supported

Query Language OQL (Object Query Language) SQL

Usage Multimedia, CAD/CAM, simulations Business apps, banking, e-commerce

Relationships Via object references Via foreign keys

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4(a) What is Query Optimisation? How is it different from Query Evaluation?

Query Optimisation:

• It is the process where the DBMS chooses the most efficient query execution plan
from among many alternatives, based on cost estimation (I/O, CPU time, etc.).

• The goal is to minimize execution time and resource usage.

Example:

SELECT * FROM Employee WHERE Dept_ID = 10;

 • Plan 1: Full table scan of Employee

• Plan 2: Use index on Dept_ID

The optimizer chooses Plan 2 if an index exists, improving performance.

Query Optimisation vs Query Evaluation

Aspect Query Optimisation Query Evaluation

When it occurs Before query execution (planning) During query execution

Goal Find most efficient execution plan Actually run the plan and get result

Type Logical/heuristic process Physical execution

4(b) Physical and Logical Data Independence

1. Logical Data Independence

• Ability to change the logical schema (tables, relationships) without affecting external
views.

Example:

• Add a new column email to the Student table.

• Existing apps using name, roll_no are unaffected.

2. Physical Data Independence

• Ability to change the internal storage without affecting logical schema.

Example:

• Changing file organization or adding indexes

• Queries and logical table structures stay the same

4(c) Locking in RDBMS & How It Solves Concurrency Problems

Locking:

Locking is a technique to ensure that multiple transactions accessing the same data do not
lead to inconsistency.
 Types of Locks:

• Shared Lock (S): Read-only access

• Exclusive Lock (X): Read/write access

How Locking Solves Concurrency Problems:

Problem: Two transactions T1 and T2 trying to update the same balance.

Without Locking:

• T1 reads balance = ₹10,000

• T2 reads balance = ₹10,000

• Both update and write different results → Lost Update

With Locking:

• T1 places exclusive lock on balance, updates, and commits

• T2 waits until T1 releases lock

Ensures serial execution effect and data consistency

5. Explanation of Terms with Examples

(a) Join Operation

A join combines rows from two or more tables based on a related column.

Example:

SELECT [Link], [Link]

FROM Student

JOIN Course ON Student.course_code = Course.course_code;

(b) Weak Entity

A weak entity:

• Cannot exist without a related strong entity

• Has a partial key and relies on a foreign key for identification

Example:

• Dependent(Name, Age, Emp_ID)

 • Emp_ID → foreign key from Employee

• Composite key: (Emp_ID, Name)

(c) Primary and Secondary Index

• Primary Index: Built on a primary key, ensures unique and sorted entries

• Secondary Index: Built on a non-primary attribute, may have duplicates

Example:

CREATE INDEX idx_dept ON Employee(Department);

• This is a secondary index if Department is not a primary key.

(d) Deadlock

A deadlock occurs when two or more transactions are waiting for each other’s resources,
resulting in an infinite wait.

Example:

• T1 locks A, waits for B

• T2 locks B, waits for A

Circular wait → Deadlock

(e) Database Security

Database security ensures confidentiality, integrity, and availability of data through:

• Authentication (who can log in)

• Authorization (who can access what)

• Encryption

• Auditing

Example:

• A user must have SELECT permission on the Payroll table to view salaries.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1(a)(i) List all the Entities

1. Member

2. Book

3. Issue (associative entity for the issue transaction between Member and Book)
 1(a)(ii) Attributes of Each Entity

1. Member

• Member_ID (Primary Key)

• Member_Name

2. Book

• Book_Code (Primary Key)

• Title

3. Issue

• Member_ID (Foreign Key from Member)

• Book_Code (Foreign Key from Book)

• Issue_Date

• Return_Date

Composite Primary Key: (Member_ID, Book_Code, Issue_Date)

1(a)(iii) Relationships Between Entities

• Member–Issues–Book
→ A many-to-many relationship with attributes (issue date, return date)
→ A member can issue multiple books, and a book can be issued by different
members at different times.

• This many-to-many with attributes is resolved using the Issue entity.

1(a)(iv) E-R Diagram

+------------+ +------------+ +------------+

| Member | | Issue | | Book |

|------------| |------------| |------------|

| Member_ID |◄────────────┤ Member_ID ├────────────►| Book_Code |

| Name | | Book_Code | | Title |

+------------+ | Issue_Date | +------------+

| Return_Date|

+------------+
 1(a)(v) List of Constraints

Primary Keys:

• Member(Member_ID)

• Book(Book_Code)

• Issue(Member_ID, Book_Code, Issue_Date)

Foreign Keys:

• Issue.Member_ID → Member.Member_ID

• Issue.Book_Code → Book.Book_Code

Other Constraints:

• A member can issue at most 5 books at a time.

o Can be enforced via application logic or a database trigger.

• Dates must follow logical constraints: Issue_Date < Return_Date (if not null)

1(a)(vi) Convert the E-R Diagram to Relations

1. Member Table

CREATE TABLE Member (

Member_ID INT PRIMARY KEY,

Member_Name VARCHAR(100)

);

2. Book Table

CREATE TABLE Book (

Book_Code INT PRIMARY KEY,

Title VARCHAR(200)

);

3. Issue Table

CREATE TABLE Issue (

Member_ID INT,

Book_Code INT,
 Issue_Date DATE,

Return_Date DATE,

PRIMARY KEY (Member_ID, Book_Code, Issue_Date),

FOREIGN KEY (Member_ID) REFERENCES Member(Member_ID),

FOREIGN KEY (Book_Code) REFERENCES Book(Book_Code)

);

This relational schema:

• Models all required information.

• Supports multiple issues of the same book by the same member (on different dates).

• Enforces proper referential integrity and transaction history.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Given Relations:

Hospital(Hospital_ID, Hospital_name, CEO_Name, Phone)

Doctor(Doctor_ID, Name, Specialisation, Hospital_ID)

• Hospital_ID → Primary key of Hospital

• Doctor_ID → Primary key of Doctor

• Foreign key: Doctor.Hospital_ID → Hospital.Hospital_ID

• A doctor can work in only one hospital

(i) List all names of all the hospitals in the alphabetical order of hospital name:

SELECT Hospital_name

FROM Hospital

ORDER BY Hospital_name ASC;

(ii) Find the number of doctors working in the hospital whose Hospital_ID = 'HO1':

SELECT COUNT(*) AS Num_Doctors

FROM Doctor

WHERE Hospital_ID = 'HO1';

 (iii) List the Hospital_ID, Hospital_name, Doctor_ID, Name, Specialisation of all the
hospitals

SELECT H.Hospital_ID, H.Hospital_name, D.Doctor_ID, [Link], [Link]

FROM Hospital H

JOIN Doctor D ON H.Hospital_ID = D.Hospital_ID;

(iv) Find the name of all the doctors whose specialization is "Physician":

SELECT Name

FROM Doctor

WHERE Specialisation = 'Physician';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Definition of Transaction in RDBMS:

In the context of a Relational Database Management System (RDBMS), a transaction is a

sequence of one or more SQL operations (like INSERT, UPDATE, DELETE, SELECT) that are
executed as a single logical unit of work.

A transaction must be completed entirely or not executed at all. If it fails at any point, the
database must roll back to its previous consistent state.

Example of a Transaction:

Suppose you're transferring ₹1,000 from Account A to Account B:

BEGIN;

UPDATE Accounts SET Balance = Balance - 1000 WHERE Account_ID = 'A';

UPDATE Accounts SET Balance = Balance + 1000 WHERE Account_ID = 'B';

COMMIT;

If there's a power failure after deducting from A but before adding to B, this could lead to data
inconsistency. That's where the properties of transactions come in.

ACID Properties of Transactions:

The four properties that ensure the reliability of transactions are ACID:
 1. Atomicity

• All operations in a transaction are treated as one indivisible unit.

• Either all succeed, or none are applied.

Example:
If money is deducted from A but not added to B, the system will roll back to the original state.

2. Consistency

• A transaction brings the database from one consistent state to another.

• All constraints (e.g., balance ≥ 0) must remain true.

Example:
If a rule says the total bank balance must remain ₹10,000, after the transfer, this must still be
true.

3. Isolation

• Concurrent transactions should not interfere with each other.

• It should appear as if transactions are executed serially.

Example:
If two users transfer money at the same time, they shouldn't see partial or conflicting data
during their operations.

4. Durability

• Once a transaction is committed, its changes are permanently stored, even in case of
system failure.

Example:
After transferring funds and committing, the new balances must persist even if there's a power
cut immediately afterward.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(i) Complex Data Types in DBMS

Definition:

Complex data types are data types that go beyond standard atomic types like INT, VARCHAR,
or DATE. They allow storage of non-atomic or structured data such as arrays, sets, lists,
objects, or even multimedia (images, videos).
 Features:

• Support for nested structures (e.g., rows inside rows)

• Can include arrays, collections, or user-defined types (UDTs)

• Found in object-relational databases or systems like PostgreSQL, Oracle

Example (in PostgreSQL):

CREATE TYPE Address AS (

street VARCHAR,

city VARCHAR,

pincode INT

);

CREATE TABLE Person (

name VARCHAR,

contact Address

);

(ii) Data Warehouse

Definition:

A Data Warehouse is a centralized repository designed for storing historical, integrated,

subject-oriented, and time-variant data from multiple sources for the purpose of analysis
and reporting.

Features:

• Optimized for OLAP (Online Analytical Processing)

• Supports large volumes of historical data

• Performs complex queries, aggregations, reporting

• Uses ETL process (Extract, Transform, Load)

Example Use Case:

An e-commerce company stores all past sales, customer behavior, and product data in a data
warehouse to analyze trends and generate business reports.

(iii) Classification in Data Mining

 Definition:

Classification is a supervised learning technique in data mining used to categorize data into
predefined classes or labels based on historical data.

Features:

• Requires labeled training data

• Uses algorithms like Decision Trees, Naive Bayes, SVM, Random Forest

• Useful in prediction problems

Example:

Predicting if an email is "Spam" or "Not Spam" based on features like subject, sender, and
content.

(iv) Clustering in Data Mining

Definition:

Clustering is an unsupervised learning technique used to group similar data points into
clusters based on similarity, without predefined labels.

Features:

• No need for labeled data

• Common algorithms: K-means, DBSCAN, Hierarchical Clustering

• Helps identify patterns or segments

Example:

Customer segmentation in marketing — grouping customers into clusters based on purchasing

behavior and demographics.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2 (a) Explanations in the Context of DBMS

(i) Conceptual Level in Three-Level DBMS Architecture

• Definition: The middle level in the ANSI/SPARC three-level architecture that

describes the logical structure of the entire database.

• It hides the physical details and shows entities, relationships, attributes, and
constraints.
 Example:
A conceptual schema might define a Student table with Roll_No, Name, and Course_ID —
without specifying how it's stored on disk.

(ii) Database Administrator (DBA)

• Definition: The person responsible for installing, configuring, securing, and

maintaining the database.

• Manages users, access control, performance tuning, and backup/recovery.

Example Tasks:

• Create users and assign roles

• Set up daily backup schedules

• Monitor performance of SQL queries

(iii) File Manager

• Definition: A component of DBMS that handles low-level storage details and

manages files on the storage device.

• It ensures data blocks are read from and written to disk correctly.

Example:
When a SELECT query is issued, the File Manager retrieves the appropriate blocks from disk via
the OS.

(iv) Data Dictionary

• Definition: A special read-only database that stores metadata (data about data) such
as table names, column types, user info, constraints, and indexes.

• Maintained by the DBMS internally.

Example:
The data dictionary may store:

Table Name: STUDENT

Columns: Roll_No (INT), Name (VARCHAR), Course_ID (CHAR)

Primary Key: Roll_No

(v) Hierarchical Model

• Definition: An older data model where data is organized in a tree-like (parent-child)

structure.
 • Each child can have only one parent, but a parent can have multiple children.

Example:

Company

├── Department

├── Employee

• Company → parent of Department, which is parent of Employee

2 (b) Relational Algebra Operations with Examples

Assume two tables:

Table: STUDENT

Roll_No Name Course_ID

1 Aditi C1

2 Rahul C2

3 Neha C1

Table: COURSE

Course_ID Title

C1 DBMS

C2 Java

C3 Python

(i) PROJECTION (π)

• Definition: Select specific columns (attributes) from a table.

Example: Get only names of students:

SELECT Name FROM STUDENT;

Result:

Name

Aditi

Rahul
Name

Neha

(ii) SELECTION (σ)

• Definition: Select rows that satisfy a condition.

Example: Students in Course C1:

SELECT * FROM STUDENT WHERE Course_ID = 'C1';

Result:

Roll_No Name Course_ID

1 Aditi C1

3 Neha C1

(iii) CARTESIAN PRODUCT (×)

• Definition: Combines every row of one table with every row of another.

Example:

SELECT * FROM STUDENT, COURSE;

Result (only partial):

Roll_No Name Course_ID Course_ID Title

1 Aditi C1 C1 DBMS

1 Aditi C1 C2 Java

1 Aditi C1 C3 Python

... ... ... ... ...

Not useful without a WHERE clause to join logically.

(iv) UNION (∪)

• Definition: Combines rows from two tables with same structure, removes
duplicates.

Example:
Suppose we have:

Table A:
Name

Aditi

Neha

Table B:

Name

Rahul

Neha

SELECT Name FROM A

UNION

SELECT Name FROM B;

Result:

Name

Aditi

Neha

Rahul

(v) SET DIFFERENCE (−)

• Definition: Returns rows that are in the first table but not in the second.

Example:

SELECT Name FROM A

EXCEPT

SELECT Name FROM B;

Result:

Name

Aditi

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Given Schema:

Relation: R(Customer_ID, Customer_name, Account_No, Balance, Type_of_Account)

Constraints:

- Customer_ID is unique for each customer.

- Account_No is unique for each account.

- A customer can open many accounts.

- An account belongs to only one customer.

(i) List all the Functional Dependencies (FDs)

From the given constraints, we can derive the following FDs:

1. Customer_ID → Customer_name
(Since each customer ID is unique and gives name)

2. Account_No → Customer_ID, Balance, Type_of_Account

(Since each account belongs to only one customer, and account has balance and type)

From above, we also get:

3. Account_No → Customer_name (By transitivity)

(ii) What is the Primary Key of the Schema?

Given:

• Account_No is unique for each account and identifies the customer as well.

So, Primary Key = Account_No

(iii) What are the Anomalies in the Relation?

Let's use a sample data:

Customer_ID Customer_name Account_No Balance Type_of_Account

C01 Aditi Sharma A1001 10000 Savings

C01 Aditi Sharma A1002 25000 Current

C02 Rahul Verma A1003 5000 Savings

Anomalies:

1. Update Anomaly:

o If Aditi Sharma changes her name, you need to update it in multiple rows
(A1001, A1002).
 2. Insertion Anomaly:

o You can’t insert a customer unless they have an account.

o For example, if a customer wants to register but hasn't opened an account yet,
you can't store their name.

3. Deletion Anomaly:

o If you delete the last account of a customer, their name and ID are lost too.

(iv) Normalize the Relation

Step 1: Convert to 2NF

2NF removes partial dependencies, but the current relation is in 1NF and also in 2NF since
the primary key Account_No is not composite. So no partial dependency is possible.

Step 2: Convert to 3NF

3NF removes transitive dependencies.

In this relation:

• Account_No → Customer_ID → Customer_name is a transitive dependency

• So we decompose to remove it

3NF Decomposition:

➤ R1: Customer Table

Customer(Customer_ID, Customer_name)

➤ R2: Account Table

Account(Account_No, Customer_ID, Balance, Type_of_Account)

Keys:

• Primary Key of Customer = Customer_ID

• Primary Key of Account = Account_No

• Foreign Key: Account.Customer_ID → Customer.Customer_ID

Final Normalized Schema:

1. Customer(Customer_ID PRIMARY KEY, Customer_name)

 2. Account(Account_No PRIMARY KEY, Customer_ID, Balance, Type_of_Account)

o FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

3 (b) What is Lossless Join Decomposition?

Definition:

A Lossless Join Decomposition ensures that when a relation is decomposed into two or more
sub-relations, and then joined back, it reconstructs the original relation exactly, without any
loss of data or creation of spurious tuples.

Example (Lossless Decomposition):

Let’s say we have a relation:

R(Student_ID, Name, Course_ID, Course_Name)

Let’s decompose into:

• R1(Student_ID, Name)

• R2(Student_ID, Course_ID, Course_Name)

We can join R1 and R2 on Student_ID to get back original relation without losing information,
so it's a lossless decomposition.

Lossy Decomposition Example:

If we decompose into:

• R1(Name, Course_ID)

• R2(Course_ID, Course_Name)

And try to join on Course_ID, we might get spurious Name–Course combinations (e.g.,
assigning wrong student names to courses).

This is lossy, because original relation cannot be reconstructed accurately.

Condition for Lossless Join (Theoretical Rule):

For a decomposition of R into R1 and R2, the decomposition is lossless if:

(R1 ∩ R2) → R1 or (R1 ∩ R2) → R2

4 (a) Concepts in RDBMS

(i) Serializable Schedule

• A schedule (sequence of operations) is serializable if it is equivalent to some serial

execution (i.e., one transaction at a time) and maintains database consistency.

Example:

Serial Schedule:

mathematica

CopyEdit

T1: Read A →Write A

T2: Read B → Write B

Concurrent Serializable Schedule:

T1: Read A

T2: Read B

T1: Write A

T2: Write B

Final result is same as serial → serializable

(ii) Two-Phase Locking (2PL)

• Protocol that ensures serializability using locks.

• Two phases:

1. Growing phase: Transaction acquires all locks, no release

2. Shrinking phase: Transaction releases locks, no acquiring new ones

Example:

T1 acquires lock on A and B → performs updates → releases both locks

No new locks are acquired after release → obeys 2PL

(iii) Backward Recovery (Undo)

• Used to undo the effects of uncommitted transactions after failure.

• System uses logs to rollback changes.

Example:
If T1 updated a row but didn’t commit and system crashed, its changes are undone during
recovery.
(iv) Checkpoint

• A checkpoint is a snapshot of the current state of the database (and log positions),
written periodically to help with faster recovery.

• During recovery, the system starts from the last checkpoint instead of scanning the
entire log.

Example:
Checkpoint at 12:00 PM → crash at 12:05 PM
Recovery starts from 12:00 PM onward only

(v) Authorization

• Authorization controls who can access what in the DBMS.

• Involves granting privileges like SELECT, INSERT, UPDATE, etc.

Example:

GRANT SELECT ON Employee TO UserA;

UserA can now only read data from Employee.

4 (b) Query Cost & Cost of Selection

What is a Measure of Query Cost?

• The query cost refers to the amount of resources (CPU, disk I/O, memory, network)
used to execute a query.

• Most important measure = Disk I/O operations, as disk access is slower than memory.

Cost of Selection (when data is unsorted)

Suppose:

• Table R has N records

• Data is unsorted on attribute A

• No index is available

Cost = Full Table Scan

• Every tuple must be checked → O(N) operations

Example:
SELECT * FROM Employee WHERE Age = 30;

• If there's no index on Age, and data is unsorted:

o DBMS must read all N blocks to check each record

Cost = Number of disk blocks to read entire relation

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

5 (a) OODBMS vs RDBMS

Aspect OODBMS (Object-Oriented DBMS) RDBMS (Relational DBMS)

Data Stores data in tables (rows &

Stores data as objects (similar to OOP)
Structure columns)

Supports inheritance, encapsulation,

Inheritance No support for object inheritance
and polymorphism

Tabular schema with keys and

Schema Complex, hierarchical schemas
relations

Used in CAD/CAM, multimedia, Used in banking, inventory, payroll,

Use Case
simulations ERP systems

Query
Uses OQL (Object Query Language) Uses SQL
Language

Object: Student {Name, Address, Table: STUDENT(Roll_No, Name,

Example
Marks[]} Marks)

5 (b) Classification vs Clustering (in Data Mining)

Aspect Classification Clustering

Supervised learning (requires labeled

Type Unsupervised learning (no labels)
data)

Goal Assign new data to predefined categories Group similar data points into clusters

Input Training data with class labels Unlabeled data

Grouping customers by purchase

Example Email → "Spam" or "Not Spam"
behavior

K-Means, DBSCAN, Hierarchical

Algorithms Decision Tree, Naive Bayes, SVM
Clustering

5 (c) Dimension Table vs Fact Table (in Data Warehousing)

Aspect Dimension Table Fact Table

Describes context (Who, What, When, Stores measurable facts or business

Purpose
Where) metrics

Data Type Textual or categorical Numeric (usually measures)

Primary Simple or surrogate key (joins with fact Composite key (made of foreign keys from
Key table) dimensions)

Size Small (low cardinality) Large (high volume of records)

Product_Dim(Product_ID, Name, Sales_Fact(Date_ID, Product_ID, Amount,

Example
Category) Quantity)

5 (d) Operational Data vs Data Warehouse Data

Aspect Operational Data Data Warehouse Data

Supports analysis and reporting

Usage Supports daily operations (OLTP)
(OLAP)

Data Type Current, real-time data Historical, aggregated data

Update Frequently updated

Periodically loaded (batch or ETL)
Frequency (insert/update/delete)

Denormalized (star/snowflake
Schema Design Highly normalized (3NF)
schema)

Monthly sales summary across

Example Live bank transactions
regions

5 (e) NoSQL vs Relational DBMS

Aspect NoSQL Database Relational DBMS

Flexible (document, key-value, graph,

Data Model Fixed schema (tables and relations)
column)

Schema Schema-less or dynamic Rigid schema (defined in advance)

Vertically scalable (scale-up

Scalability Horizontally scalable (distributed)
hardware)

ACID Limited (often BASE: Basically Available,

Full ACID compliance
Support Soft state...)
Aspect NoSQL Database Relational DBMS

Structured data, strong consistency

Best For Big data, real-time apps, unstructured data
required

Examples MongoDB, Cassandra, Redis MySQL, PostgreSQL, Oracle