Database System

Q1) Define an Entity and Attribute. Explain the different types of attributes

that occur in ER diagram model with an example.

Ans: Definition:

1. Entity:
An Entity is an object or concept in the real world that can be identified and stored in a
database.
It represents a person, place, thing, or event about which data is collected.

 Example: Student, Employee, Car, Department.

2. Attribute:
An Attribute is a property or characteristic of an entity that describes it.
Each attribute holds some value for the entity.

 Example: For entity Student, attributes can be Student_ID, Name, Age, Address.
✅ In short:
 Entity = Real-world object.
 Attribute = Property of an entity.
 Types = Simple, Composite, Derived, Multi-valued, Key, and Optional.
Q2) Define and differentiate the following relational algebra operators with
suitable example
1. Cartesian product 2. Natural join
Ans: 1. Cartesian Product (×)
Definition:
The Cartesian Product in relational algebra is a binary operation that combines
every tuple of one relation with every tuple of another relation.
It is also known as the cross product.
Notation:
R×S

Result:
If relation R has m tuples and relation S has n tuples, then the result of R × Swill
have m × n tuples.
The resulting relation contains all attributes of both relations.
2. Natural Join (⋈)
Definition:
The Natural Join is a special type of join that combines two relations based on
all common attributes (attributes with the same name).
It automatically matches and merges the tuples having equal values in the
common attributes.
Notation:
R⋈S

Result:
It eliminates duplicate columns of the common attributes.
✅ In short:
  Cartesian Product (×) → combines all tuples without condition.
 Natural Join (⋈) → combines tuples with same values in common
attributes.

Q3) What is relational algebra? Explain basic operations along with symbols
of relational algebra.
Ans: Relational Algebra
Definition:
Relational Algebra is a procedural query language used in Database
Management Systems (DBMS) to retrieve data from relational databases.
It uses mathematical operations on relations (tables) to produce new relations
as results.
It tells how to obtain the result (unlike relational calculus which tells what to
obtain).
Example:
Let
Employee(EmpID, Name, Dept, Salary)
Department(Dept, Manager)
To find names of employees working in ‘CSE’ department:
π Name ( σ Dept ¿ CS E ( Employee ))
' '

✅ Summary:
Relational Algebra provides a foundation for SQL queries and ensures that
database operations are based on mathematical principles, making data
retrieval precise and structured

Q4) What is view what are its advantages explain views in SQL with example.
Ans: 🌐 What is a View in SQL?
A View in SQL is a virtual table that is based on the result of a SELECT query.
It does not store data physically — instead, it displays data stored in one or
more tables.
You can think of it as a saved SQL query that can be used like a table.
✅ Advantages of Views
1. Data Security:
Views can hide sensitive columns (like salary or password) from users.
Example: Only allow users to see names and departments, not salaries.
2. Simplifies Complex Queries:
You can save a complex query as a view and use it easily without
rewriting the SQL every time.
3. Logical Data Independence:
Changes in table structure (like adding columns) do not affect views
directly.
4. Data Consistency:
Since the view always shows the latest data from the underlying tables, it
stays up to date.
5. Reusability:
Once created, a view can be used in multiple queries just like a table.
Q5) What is normalization? Why we need to normalize the database tables.
Ans: What is Normalization?
Normalization is a process in database design used to organize data efficiently
by dividing large tables into smaller, related tables. It aims to eliminate data
redundancy (repetition of data) and ensure data integrity (accuracy and
consistency).
In simple terms, normalization restructures a database so that each piece of
data is stored only once, and relationships between data are maintained using
foreign keys.
Why We Need to Normalize Database Tables
Normalization is important for several reasons:
1. To Reduce Data Redundancy
o It prevents storing the same data in multiple places.
o Example: Storing a student's department name in every student
record is redundant — instead, we can store it in a separate
department table.
2. To Improve Data Integrity
o Ensures that data is consistent and accurate.
o Changes made in one place automatically reflect everywhere
(through relationships).
3. To Avoid Anomalies
o Insertion Anomaly: Difficulty adding new data due to missing
information.
o Updation Anomaly: Same data must be updated in multiple
places.
o Deletion Anomaly: Deleting one record unintentionally removes
related useful data.
4. To Simplify Queries and Maintenance
o A normalized database is easier to update, maintain, and expand.
5. To Improve Storage Efficiency
o Reduces unnecessary duplication, saving disk space.
✅ In summary:
Normalization is the process of organizing database tables to reduce
redundancy, avoid anomalies, and maintain data integrity.

Q6) Write the SQL syntax for the following with the example
1. Select 2. Alter 3. Update
Ans:
Q7) Explain 1st,2nd and3rd normal form with the help of suitable example
🔹 What is Normalization?

Normalization is a process of organizing data in a database to reduce

redundancy (repetition) and improve data integrity.
It divides large tables into smaller ones and establishes relationships between
them.

🧩 1st Normal Form (1NF)

📘 Definition:
A table is in 1NF if:
1. Each cell contains only a single (atomic) value.
2. Each record is unique (has a primary key).
Q8) What are the features of good relational Designs?
Ans: Features of a Good Relational Database Design
A good relational database design ensures that data is stored efficiently,
accurately, and can be retrieved easily without redundancy or inconsistency.
The main features are:

1. Data Consistency and Integrity

 The design should maintain data accuracy and integrity through proper
use of primary keys, foreign keys, and constraints (like NOT NULL,
UNIQUE, etc.).
 Example: Every order in an "Orders" table must have a valid customer ID
from the "Customers" table.

2. Minimal Data Redundancy

 Data should not be unnecessarily repeated in multiple tables.
 Redundant data causes update anomalies and wastes storage space.
 Achieved through Normalization.

3. Proper Normalization
 The design should follow normal forms (1NF, 2NF, 3NF, BCNF) to
eliminate redundancy and dependency problems.
 This ensures each attribute depends only on the key, the whole key, and
nothing but the key.

4. Data Independence
 The structure of data should be independent of how data is accessed or
stored.
  Changes in one part of the database should not affect other parts or
applications using it.

5. Use of Appropriate Keys

 Each table should have a primary key that uniquely identifies each
record.
 Foreign keys should establish relationships between tables properly.

6. Flexibility and Scalability

 The design should allow easy modification or expansion of the database
(e.g., adding new tables or columns) without affecting existing data or
relationships.

7. Efficiency in Query Performance

 Proper indexing and relationships should allow fast data retrieval and
updates.
 Avoid unnecessary joins or overly complex relationships.

8. Logical Relationships Between Tables

 Relationships (one-to-one, one-to-many, many-to-many) should be
clearly defined and represented.
 Use junction (bridge) tables where necessary (especially for many-to-
many relationships).

9. Security and Access Control

 The design should allow implementation of user access control to
protect sensitive data.
 Example: Only the HR department can access employee salary data.
10. Maintainability and Simplicity
 The schema should be easy to understand and maintain.
 Clear table names, consistent naming conventions, and well-defined
relationships help ensure maintainability.

✅ In summary:
A good relational design is normalized, consistent, flexible, secure, and
efficient, ensuring accurate data storage and easy retrieval without redundancy
or anomalies.

Q9) Explain The Static Hashing and Dynamic Hashing.

Ans: 🔹 Static Hashing
Definition:
In static hashing, the number of buckets (or data blocks) in the hash table
remains fixed throughout the operation of the database.

Problems:
 1. Overflow buckets:
When a bucket becomes full, extra records are placed in overflow
buckets. This can slow down search operations.
2. Wasted space:
If the number of records decreases, some buckets may remain
underfilled.
3. Not suitable for databases that grow or shrink frequently.

🔹 Dynamic Hashing
Definition:
In dynamic hashing, the number of buckets changes dynamically (increases or
decreases) as the number of records in the database changes.
How It Works:
 It uses a directory that grows or shrinks dynamically.
 The hash function produces a large binary number.
 Only a certain number of leading bits are used to determine the bucket.
 When a bucket overflows, the directory (and possibly the hash function
bits) is expanded.
Techniques Used:
 Extendible Hashing
 Linear Hashing

Advantages:
1. Efficiently handles growing and shrinking databases.
2. Reduces overflow problems.
3. Maintains good search performance even as data changes.
Q10) Define transaction and their properties with suitable example
Ans: Definition of Transaction in Database System:
A transaction in a database 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 ensures that either all operations are completed successfully, or
none are applied, maintaining the consistency of the database.
👉 In short:
Transaction = A logical unit of work performed on a database.
Example:
Suppose a bank transfer occurs from Account A to Account B for ₹1000.
This operation involves two steps:
1. Debit ₹1000 from Account A
2. Credit ₹1000 to Account B
If both steps succeed → Transaction is committed
If any step fails → Transaction is rolled back
SQL Example:

✅ Summary:
 Transaction: A group of operations executed as a single unit.
 ACID properties: Ensure reliability and correctness of transactions.
 Example: Bank transfer (Debit + Credit) demonstrates all four ACID
properties.

Q11) Explain ACID properties in details.

Ans: In Database Management Systems (DBMS), a transaction is a sequence of
operations performed as a single logical unit of work.
To ensure data integrity and reliability, transactions must satisfy the ACID
properties.
Let’s understand each property in detail:
🔹 A – Atomicity
Meaning:
A transaction is an all-or-nothing operation.
Either all operations of the transaction are completed successfully, or none are.
Example:
Suppose you are transferring ₹1000 from Account A to Account B.
The transaction involves two operations:
1. Debit ₹1000 from Account A
2. Credit ₹1000 to Account B
If the system crashes after step 1 (money deducted from A) but before step 2,
atomicity ensures that the entire transaction is rolled back — i.e., the ₹1000
will be returned to Account A.
In short:
If one part of a transaction fails, the whole transaction fails, and the database
remains unchanged.
🔹 C – Consistency
Meaning:
A transaction must take the database from one consistent state to another
consistent state.
This means that all rules, constraints, and relationships in the database are
maintained after the transaction.
Example:
If the total money in the system before the transaction was ₹10,000, it should
remain ₹10,000 after the transfer (₹1000 moved from A to B doesn’t change
the total amount).
In short:
Data integrity is preserved before and after the transaction.
🔹 I – Isolation
Meaning:
Each transaction executes independently of other transactions.
Even if multiple transactions occur at the same time, the final result should be
the same as if they were executed one after another.
Example:
If two users transfer money at the same time, isolation ensures that the
transactions do not interfere with each other and produce accurate results.
In short:
Concurrent transactions are isolated so that intermediate results of one
transaction are not visible to others.
🔹 D – Durability
Meaning:
Once a transaction is successfully completed (committed), its changes are
permanent, even if the system crashes immediately afterward.
Example:
If ₹1000 is transferred successfully and committed, the updated balances will
remain in the database even after a power failure or restart.
In short:
The results of a committed transaction are permanently stored in the database.
Q12) Explain Time Stamp-Based Protocols.
Ans: Time Stamp–Based Protocols (in Database Management System)
Definition:
A timestamp-based protocol is a concurrency control technique used in
databases to ensure serializability (i.e., the correct order of transactions) by
assigning a unique timestamp to each transaction when it starts.
The timestamp represents the transaction’s start time, and the protocol
ensures that transactions execute in timestamp order, preventing conflicts like
dirty reads or lost updates.

Basic Idea:
 Each transaction T gets a unique timestamp (TS(T)) when it begins.
 Each data item Q in the database keeps two timestamps:
1. W-timestamp(Q): The largest timestamp of any transaction that
successfully wrote Q.
2. R-timestamp(Q): The largest timestamp of any transaction that
successfully read Q.
The protocol uses these timestamps to decide whether a transaction can read
or write a data item or must abort (rollback).
Rules of Timestamp Ordering Protocol:
1. For Read Operation (T tries to read Q):
o If TS(T) < W-timestamp(Q) →
T is too late (a newer transaction has already written Q).
➤ Reject (abort) T and rollback.
o Else →
Allow T to read Q, and
Set R-timestamp(Q) = max(R-timestamp(Q), TS(T)).
 2. For Write Operation (T tries to write Q):
o If TS(T) < R-timestamp(Q) →
A newer transaction has already read Q, so writing now would
violate order.
➤ Reject (abort) T.
o If TS(T) < W-timestamp(Q) →
A newer value of Q already exists, so this write is outdated.
➤ Reject (abort) T.
o Else →
Allow T to write Q, and
Set W-timestamp(Q) = TS(T).

Advantages:
✅ Ensures serializability automatically.
✅ No deadlocks, since no transaction waits; instead, older ones are simply
rolled back.
✅ Simple to implement using system clock timestamps.

Disadvantages:
❌ High rollback rate, especially if older transactions frequently conflict with
newer ones.
❌ Starvation possible (old transactions may keep aborting).
❌ Requires precise and unique timestamps.
Types of Timestamp Protocols:
1. Basic Timestamp Ordering (BTO) – follows the rules above strictly.
2. Strict Timestamp Ordering (STO) – a transaction delays writes until all
conflicting transactions commit or abort.
3. Thomas’s Write Rule – a relaxation of BTO that ignores outdated writes
instead of aborting the transaction.

In Short:
Timestamp-based protocol ensures transactions execute in chronological
order of their timestamps to maintain database consistency and serializability
without causing deadlocks.

Q13) Draw and explain the detailed system architecture of DBMS.

Ans: DBMS System Architecture
A Database Management System (DBMS) architecture shows how data is
managed, stored, and accessed by users through different layers.
It is mainly divided into three levels (as per the ANSI/SPARC model):
1. External Level (View Level)
2. Conceptual Level (Logical Level)
3. Internal Level (Physical Level)
Explanation of Each Level
1. External Level (View Level)
 The highest level of abstraction.
  It defines how individual users view the data.
 Each user may have a different view of the same database (for security
and simplicity).
 Example:
o A student sees only his marks and attendance.
o A teacher sees marks of all students in his class.
Key Points:
 Provides user-specific views.
 Ensures data security and abstraction.
 Hides details of physical storage.
2. Conceptual Level (Logical Level)
 Represents the logical structure of the entire database.
 Describes what data is stored and relationships among data.
 It acts as a bridge between the external and internal levels.
Key Points:
 Describes entities, attributes, constraints, and relationships.
 Independent of physical storage.
 Only one conceptual schema exists for the whole database.
Example:
A conceptual schema may define entities like Student, Course, Teacher and
relationships like Enrolls.
3. Internal Level (Physical Level)
 Describes how data is physically stored in the database.
 Defines file organization, indexes, access paths, and data storage
structures.
Key Points:
  Concerned with storage efficiency and performance.
 Manages data compression, encryption, and access methods.
 Only database administrators (DBAs) interact with this level.
Example:
It defines how a Student table is stored in files, how indexes are built on
Roll_No, etc.

Data Independence
 Logical Data Independence:
Changes in the conceptual schema do not affect the external schemas or
user views.
(e.g., adding a new attribute in a table should not affect user views.)
 Physical Data Independence:
Changes in internal schema (storage structure, indexing, etc.) do not
affect conceptual schema.
(e.g., changing file organization from heap to hash should not affect
logical schema.)

Q14) Explain in detail about various key constraints used in database system.
Ans: 🔑 Key Constraints in Database Systems
In a Database Management System (DBMS), key constraints are rules that
ensure uniqueness and identification of records in a table. These constraints
help maintain the integrity and consistency of data.
Q15) Discuss the main characteristics of the database approach and specify
how it differs from traditional file system?
Ans: ChatGPT said:
Main Characteristics of the Database Approach
The database approach is a modern way of managing data using a Database
Management System (DBMS). It organizes data in an integrated and structured
manner, allowing easy access, sharing, and manipulation.
Here are the main characteristics:
1. Data Integration and Centralization:
o All organizational data is stored in a single database.
o Eliminates data scattered in separate files.
2. Data Independence:
o The structure of the data can change without affecting the
application programs.
o Two types:
  Logical data independence: Change in schema doesn’t
affect applications.
 Physical data independence: Change in physical storage
doesn’t affect logical structure.
3. Data Sharing:
o Multiple users and applications can access the same data
concurrently.
o DBMS manages concurrent access and ensures consistency.
4. Reduced Data Redundancy:
o Common data is stored only once and shared, reducing
duplication.
5. Data Consistency:
o Since data redundancy is reduced, updates are consistent across
all users.
6. Data Security and Authorization:
o Access control mechanisms restrict unauthorized access.
o Users can be assigned specific privileges.
7. Data Integrity:
o Constraints (like primary key, foreign key) ensure accuracy and
validity of data.
8. Backup and Recovery:
o Automatic recovery and backup features protect data from
failures.
9. Query Processing and Data Manipulation:
o DBMS provides high-level languages like SQL for data retrieval and
manipulation
Q16) Write a short notes on
i) Foreign Key ii) Relation state iii) Database schema.
Ans:
Q17) account(account_number, branch_name, balance)
branch (branch_name, branch_city, assets)
customer (customer_name ,customer_street, customer_city)
loan (loan_number, branch_name, amount)
depositor((customer_name, account_number)
borrower(customer_name, loan_number)
Write the following queries in SQL:
1)For all customers who have a loan from the bank, find their names, loan
numbers, and loan amount.
2) Find the customer names, loan numbers, and loan amounts, for all
loans at the Panvel branch.
3)Find the names of all branches that have assets greater than those of at
least one branch located in Mumbai.
4)Find the average account balance of those branches where the account
balance is greater than Rs. 1500.
5) Find the maximum across all branches of the total balance at each
branch.
Ans:
Q18) Write the SQL syntax for the following with example:
1)SELECT 2) ALTER 3)UPDATE
Ans:
Q19) Write an SQL query for the following:
a)To create a table of Hospital database with minimum 4 fields
b) To insert two records
c) To add new field
d)To display all records

Ans:
Q20) What are JOINS? Explain INNER JOIN and OUTER JOIN.
Ans: 💡 JOINS in SQL
Definition:
A JOIN in SQL is used to combine data from two or more tables based on a
related column between them (usually a primary key–foreign key
relationship).
It helps retrieve meaningful information spread across multiple tables.
🔹 Types of Joins
1. INNER JOIN
2. OUTER JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN
o FULL OUTER JOIN
3. CROSS JOIN
4. SELF JOIN

🔸 1. INNER JOIN
Definition:
The INNER JOIN returns only the rows that have matching values in both tables.

🔸 2. OUTER JOIN
Definition:
The OUTER JOIN returns all rows from one or both tables, even if there is no matching
record in the other table.
➤ a) LEFT OUTER JOIN
Returns all rows from the left table and the matching rows from the right table.
If no match, the result from the right table is NULL.

➤ b) RIGHT OUTER JOIN

Returns all rows from the right table and the matching rows from the left table.
If no match, the left table columns show NULL.
Q21) Explain different types of trigger.
Ans: Triggers in DBMS are special procedures that are automatically executed (or
“fired”) in response to certain events on a particular table or view — such as INSERT,
UPDATE, or DELETE operations.

🔹 Types of Triggers
Triggers can be classified based on when and how they are executed:
1. BEFORE Trigger
 Executes before the triggering event (INSERT, UPDATE, DELETE) occurs.
 Used to validate or modify data before it is inserted or updated in the table.
Q22) State BCNF. How does it differ from 3NF?
Ans: BCNF (Boyce-Codd Normal Form):
A relation (table) is in Boyce-Codd Normal Form (BCNF) if for every functional
dependency (FD) A → B, the determinant A is a super key.
In other words, no non-superkey attribute can determine another attribute.
✅ In short:
BCNF is a stricter version of 3NF.
Every BCNF relation is in 3NF, but not every 3NF relation is in BCNF.

Q23) Explain about dynamic multilevel indexing using B+ trees.

Ans: ChatGPT said:
Dynamic Multilevel Indexing using B+ Trees

1. Introduction
In databases, indexing is used to speed up data retrieval.
When data grows very large, single-level indexing becomes inefficient because searching
still takes time proportional to the number of index entries.
To solve this, multilevel indexing is used — where multiple levels of indexes are built to
reduce search time.
However, as databases change (insertions, deletions), maintaining these indexes
efficiently requires a dynamic structure — that’s where B+ Trees come in.

2. Concept of Multilevel Indexing

 In multilevel indexing, instead of storing all index entries in one large index file, we
create multiple levels:
o Level 1: Index to locate blocks of Level 0 (main data file)
o Level 2: Index to locate blocks of Level 1
o and so on...
 The topmost level is small enough to fit in main memory, which allows faster access.

3. Problem with Static Indexes

 Static multilevel indexes (like simple ISAM) are fixed in size.
 When new records are added:
o The index might overflow.
o Rebuilding the index becomes expensive.
Hence, static structures are not suitable for dynamic databases.

4. Dynamic Multilevel Indexing using B+ Trees

A B+ Tree is a balanced tree structure that allows dynamic insertion, deletion, and
searching efficiently.
 It is a self-balancing multilevel index.
 Each node in the tree corresponds to a disk block.
 The root, intermediate nodes, and leaf nodes form multiple index levels
dynamically.

5. Structure of B+ Tree
 Each internal node contains keys and pointers to child nodes.
 Each leaf node contains keys and pointers to actual data records (or to data blocks).
 All leaf nodes are linked in a sequential (linked list) manner, allowing efficient
range queries
7. Advantages of B+ Tree (Dynamic Indexing)
1. Balanced structure – search, insert, and delete take O(log n) time.
2. Dynamic growth/shrinking – automatically reorganizes when data changes.
3. Efficient disk access – minimizes I/O by keeping tree height small.
4. Supports range queries easily due to linked leaf nodes.
5. Sequential access – all records can be traversed in sorted order.

8. Operations
 Search: Start from root and follow index pointers until reaching the correct leaf node.
 Insertion: Insert key in appropriate leaf; if overflow occurs, split nodes and possibly
grow the tree upward.
 Deletion: Remove key; if underflow occurs, merge nodes and possibly shrink the
tree.

Q24) Define Multi-valued dependency. Explain the Fourth normal form with an
example.
Ans: Definition: Multi-Valued Dependency (MVD)
A Multi-Valued Dependency (MVD) occurs in a relation when one attribute in a table
uniquely determines another attribute, independently of all other attributes.
It is denoted as:
👉 X →→ Y
This means:
If two tuples (rows) have the same value for attribute X, then they must have the same set
of values for attribute Y, regardless of the values of other attributes.
Q25) Explain in detail about timestamp based concurrency control techniques.
Ans:

1. Introduction
In a multi-user database system, several transactions may execute concurrently. To
maintain data consistency and isolation, we need concurrency control techniques.
Timestamp-Based Concurrency Control (TBCC) is one such technique that uses
timestamps to order transactions in a serializable manner — i.e., as if they were
executed one after another.

2. Basic Idea
Each transaction is assigned a unique timestamp (TS) when it starts.
This timestamp determines the serialization order —
If TS(T1) < TS(T2), then T1 should appear to execute before T2 in the schedule.

3. Timestamp Assignment
A timestamp can be generated in two ways:
1. System Clock: The current time when the transaction starts.
2. Logical Counter: A simple counter incremented for every new transaction.

4. Data Item Timestamps

For each data item Q, two timestamps are maintained:
Timestamp Meaning
WTS(Q) The timestamp of the last transaction that successfully wrote Q.
RTS(Q) The timestamp of the last transaction that successfully read Q.
These are used to decide whether a read or write operation is valid or must be rejected
(rolled back) to maintain order.

5. Rules for Operations

(a) Read Operation:
When transaction T wants to read(Q):
 If TS(T) < WTS(Q)
→ Too late (a newer transaction has already written Q)
→ The read is rejected, and T is rolled back.
 Else
→ The read is allowed, and
→ RTS(Q) = max(RTS(Q), TS(T))

(b) Write Operation:

When transaction T wants to write(Q):
 If TS(T) < RTS(Q)
→ Some newer transaction has already read Q.
→ Writing now would violate serial order.
→ Write rejected, and T is rolled back.
 If TS(T) < WTS(Q)
→ A newer transaction already wrote Q.
→ Write rejected, and T is rolled back.
 Else
→ Write allowed, and
→ WTS(Q) = TS(T)

Transactions:
 T1 (TS=25) → read(X) → Allowed (since 25 > WTS(X)=20)
Update RTS(X)=25.
 T2 (TS=15) → write(X) → Rejected (since 15 < RTS(X)=25).
→ T2 must be rolled back.
Thus, the order based on timestamps is maintained.
7. Advantages
✅ No Deadlocks:
No waiting — transactions are rolled back instead of waiting, so deadlocks never occur.
✅ Ensures Serializability:
Transactions appear in the order of their timestamps.
✅ Simple to Implement:
Only timestamp comparisons are required.

8. Disadvantages
❌ Starvation:
A transaction may be rolled back repeatedly if its timestamp is small.
❌ More Rollbacks:
Frequent rollbacks may occur under heavy concurrency.
❌ Storage Overhead:
Maintaining RTS and WTS for each data item increases storage needs.
9. Variants
1. Basic Timestamp Ordering (BTO):
o Simple version as explained above.
2. Strict Timestamp Ordering (STO):
o Writes are delayed until the transaction commits → ensures recoverability.
3. Multiversion Timestamp Ordering (MVTO):
o Keeps multiple versions of each data item, allowing more concurrency.
Q26) Explain ACID properties of a transaction.
Ans: n DBMS (Database Management System), every transaction must satisfy certain
properties to ensure data integrity, consistency, and reliability — even in case of
system failures.
These are called ACID properties, where ACID stands for:

1. Atomicity
 Meaning: A transaction is treated as a single, indivisible unit of work — it either
completes fully or not at all.
 If any part fails, the entire transaction is rolled back.
 Example:
Suppose you transfer ₹1000 from Account A to Account B.
o Step 1: Deduct ₹1000 from A.
o Step 2: Add ₹1000 to B.
If the system fails after Step 1 but before Step 2, the entire transaction must be
undone, ensuring no money is lost.
2. Consistency
 Meaning: A transaction must bring the database from one valid state to another
valid state.
 It should maintain all database rules, constraints, and relationships.
 Example:
If total bank balance before transaction = ₹5000,
it should remain ₹5000 after the transfer (even if amounts move between accounts).

3. Isolation
 Meaning: Each transaction should execute independently of other transactions.
 The intermediate results of a transaction must not be visible to other transactions until
it is committed.
 Example:
If two people transfer money at the same time, their operations shouldn’t interfere or
read incomplete results from each other.
4. Durability
 Meaning: Once a transaction is committed, its results are permanent in the
database, even if the system crashes afterward.
 Example:
After money is successfully transferred and the transaction is committed, the change
remains in the database even if there is a power failure immediately after.

Q27) Explain how Concurrency control can be achieved with locking methods?
Ans: ChatGPT said:
Concurrency Control Using Locking Methods
Concurrency control ensures that multiple transactions can occur simultaneously in a
database without causing inconsistencies. One of the most common techniques to
achieve this is locking.

🔹 What is Locking?
A lock is a mechanism used to control concurrent access to a data item.
When a transaction places a lock on a data item, it restricts other transactions from
accessing that item in a conflicting way until the lock is released.

🔹 Types of Locks
1. Shared Lock (S-lock)
o Placed when a transaction wants to read a data item.
o Multiple transactions can hold shared locks on the same data item
simultaneously.
o No transaction can modify the data while shared locks exist.
Example:
o T1 and T2 can both read item A with shared locks.
2. Exclusive Lock (X-lock)
o Placed when a transaction wants to write (update) a data item.
o No other transaction can read or write the data item until the lock is released.
Example:
o If T1 holds an exclusive lock on A, no other transaction can access A until T1
releases the lock.
🔹 Two-Phase Locking Protocol (2PL)

This is the most widely used locking protocol to ensure serializability.

Phases:
1. Growing Phase:
o Transaction may acquire locks but cannot release any.
o Continues until the first lock is released.
2. Shrinking Phase:
o Transaction may release locks but cannot acquire new ones.
Rule:
Once a transaction releases a lock, it cannot request any new lock.
✅ Ensures conflict-serializable schedules.
🔹 Drawbacks of Locking
 Deadlock: Two or more transactions wait for each other’s locks.
 Starvation: Some transactions may never get a lock if others keep holding them.
 Reduced concurrency: Too many locks reduce system performance.

🔹 Conclusion
Concurrency control using locking methods ensures that:
 Database integrity and consistency are maintained.
 Transactions appear to execute serially (serializability).
 Issues like lost updates, dirty reads, and uncommitted data are prevented.

In short:
Locking-based concurrency control uses shared and exclusive locks, often governed by
the Two-Phase Locking protocol, to synchronize concurrent transactions and maintain
data consistency.

Q28) Explain the difference between two-tier and three-tier architectures. Which is better
suited for Web applications? Why?
Ans:

Which is better suited for Web Applications?

👉 Three-Tier Architecture is better suited for web applications.
Reasons:
1. Separation of Concerns: The application logic is separated from the user interface
and data storage, making the system more organized.
2. Scalability: Supports a large number of users and can easily be expanded across
multiple servers.
3. Security: Database is protected since users interact only through the web
(application) server.
4. Maintainability: Easier to update or modify the application layer without affecting
clients.
5. Flexibility: Allows integration with other web services and APIs.

✅ Conclusion:
Three-tier architecture is preferred for web-based systems because it offers better
scalability, security, and maintainability compared to the simpler two-tier model.
Q29) Define ordered indices. Differentiate between Dense and Sparse indices with
suitable example.
Ans: ChatGPT said:
Ordered Indices – Definition
An ordered index (also called a sequential index) is an index file where the search key
values are stored in sorted (ascending or descending) order. Each entry in the index
contains a key value and a pointer (address) to the corresponding record(s) in the data
file.
👉 Purpose: Ordered indices help in fast searching, sequential access, and efficient range
queries.
👉 Example: If the data file stores student records ordered by Roll_No, an ordered index
on Roll_No allows direct and fast access to records based on roll numbers.

Types of Ordered Indices

There are two main types:
1. Dense Index
2. Sparse Index

1. Dense Index
 In a dense index, there is an index entry for every record in the data file.
 Each index entry contains a key value and a pointer to the actual record in the file.
Characteristics:
 Faster to search since every key has an index entry.
 Requires more storage space.
 Index file is large

2. Sparse Index
 In a sparse index, index entries exist only for some records — typically the first
record of each block in the data file.
 To locate a record, we find the nearest smaller key in the index, then search
sequentially in the data block.
Characteristics:
 Requires less space.
 Slightly slower than dense index.
 Efficient for large files that are stored sequentially.
 ✅ Summary:
 Dense Index → Entry for every record → Fast access, more space.
 Sparse Index → Entry for some records → Less space, slower lookup.

Q30) Write short note on following:

1) ACID properties of transaction
2) View serializable schedule
Ans:

1) ACID Properties of Transaction

A transaction in a database is a set of operations that performs a single logical unit of work.
To ensure reliability and correctness of data, every transaction must satisfy the ACID
properties.
ACID stands for:
 A – Atomicity:
Ensures that all operations of a transaction are completed successfully.
If any operation fails, the entire transaction is rolled back.
Example: If a money transfer from A to B fails midway, the amount should not be deducted
from A.
 C – Consistency:
Ensures that the database moves from one consistent state to another after the
 transaction.
No rules, constraints, or data integrity should be violated.
 I – Isolation:
Ensures that transactions are executed independently.
The intermediate state of one transaction should not be visible to other transactions.
Example: Two users transferring money simultaneously should not interfere with each other.
 D – Durability:
Once a transaction is committed, its changes are permanent — even if the system
crashes.
Changes are stored safely in non-volatile memory.

2) View Serializable Schedule

A schedule is an order in which multiple transactions are executed.
A schedule is said to be View Serializable if it produces the same final result (database
state) as a serial schedule (where transactions execute one after another).
Conditions for View Serializability:
1. Initial Reads: If a transaction reads a data item first in one schedule, it must also do
so in the equivalent serial schedule.
2. Read–Write Dependencies: If a transaction reads a data item written by another
transaction, the same order should be maintained.
3. Final Writes: The transaction performing the final write on a data item must be the
same in both schedules.

Q31) Explain data abstraction and different data models in DBMS.

Ans: Data Abstraction and Data Models in DBMS

1. Data Abstraction
Definition:
Data abstraction in DBMS is the process of hiding the complex details of data storage
and maintenance and showing only the necessary information to the user. It simplifies
database interaction by dividing data representation into different levels.
 Levels of Data Abstraction
There are three levels of data abstraction in DBMS:
1. Physical Level (Lowest Level)
o Describes how the data is actually stored in the database.
o It deals with data storage structures, file organization, and access methods.
o Example:
How a record is stored using hashing or indexing, or how pointers are used to
access records.
Example:
Data stored in blocks, indexes, and pointers on the disk.
2. Logical Level (Middle Level)
o Describes what data is stored in the database and the relationships among
the data.
o It hides physical storage details from users.
o Example:
A table "Student" with fields: (Roll_no, Name, Course, Marks)
Example:
STUDENT (Roll_no, Name, Course, Marks)
[Link] Level (Highest Level)
 Describes only part of the entire database that a user needs to see.
 Different users can have different views of the same database.
 It provides security by restricting access to certain data.
Example:
A teacher’s view may show only (Name, Marks), while the admin’s view may show
all fields.

2. Data Models in DBMS

A Data Model defines the structure of the database — how data is stored, connected,
and manipulated.

Types of Data Models

1. Hierarchical Model
o Data is organized in a tree-like structure (parent-child relationship).
o Each child has only one parent.
o Uses pointers to link records.
 1.

Advantages:

o Simple and easy to understand.

Disadvantages:
o Difficult to modify; child can’t have multiple parents.

[Link] Model
 Data is represented by graphs with many-to-many relationships.
 Uses pointers and sets to show relationships.
Example:
A student can enroll in multiple courses, and each course can have multiple students.
Advantages:
 Flexible relationships.
Disadvantages:
 Complex to design and maintain.

3. Relational Model
 Data is stored in tables (relations) with rows and columns.
 Each row represents a record (tuple), and each column represents a field (attribute).
 Uses keys to link tables.

4. Entity-Relationship (ER) Model

o Represents data as entities, attributes, and relationships.
o Commonly used in database design before implementation.
Example:
Entity: Student
 Attributes: Roll_no, Name
Relationship: Enrolls in → Course
5. Object-Oriented Model
 Data is stored as objects, similar to object-oriented programming.
 Each object contains data (attributes) and methods (functions).
Example:
Student object with properties (Roll_no, Name) and method (displayDetails()).

Q32) Explain different advantages of DBMS over file processing sysytem.

Ans:

Advantages of DBMS over File Processing System

A Database Management System (DBMS) offers several advantages compared to the
traditional File Processing System (FPS) used to store and manage data.

1. Data Redundancy and Inconsistency Control

 File System: Same data may be stored in multiple files leading to duplication and
inconsistency.
 DBMS: Data is stored centrally and managed by the DBMS, reducing redundancy and
ensuring consistency.

2. Data Sharing
 File System: Data is isolated in separate files, making sharing difficult.
 DBMS: Multiple users and applications can share the same data concurrently.

3. Data Integrity
 File System: No mechanism to enforce data rules and constraints.
 DBMS: Integrity constraints (like primary key, foreign key, etc.) ensure correctness
and accuracy of data.

4. Data Security
 File System: Security is handled by the operating system, which is limited.
 DBMS: Provides strong security features like user authentication, access control, and
privilege management.

5. Data Independence
 File System: Any change in file structure requires changes in application programs.
 DBMS: Data is independent of application programs due to data abstraction and
schema levels (physical, logical, and view).

6. Efficient Query Processing

 File System: Requires writing complex programs to access or modify data.
 DBMS: Uses SQL and query optimization for easy and efficient data retrieval.

7. Backup and Recovery

 File System: Manual backup and recovery are required.
 DBMS: Provides automatic backup and recovery mechanisms to restore data after
failure.

8. Concurrency Control
 File System: Multiple users accessing data simultaneously may cause inconsistency.
 DBMS: Manages concurrent access using transaction control techniques (like locking
and timestamp).

9. Improved Data Access

 File System: Accessing data requires custom code for each application.
 DBMS: Allows fast and flexible data retrieval through queries.

10. Reduced Application Development Time

 File System: Each application must handle file handling logic.
 DBMS: Standard interfaces and data manipulation languages reduce the effort and
time to build applications
Q33) Explain unary and binary operations in relational algebra.
Ans: Unary and Binary Operations in Relational Algebra
In Relational Algebra, operations are performed on relations (tables) to retrieve or
manipulate data.
These operations are classified into unary and binary operations based on the
number of relations they operate on.
Q34) Explain DDl, DML and DCL commands with suitable example.
ANS: In SQL (Structured Query Language), commands are grouped into DDL, DML,
and DCL based on their purpose in managing and manipulating data in a database.
Let’s understand each one with examples 👇
1. DDL (Data Definition Language)
Purpose:
DDL commands are used to define and manage the structure of database objects like
tables, schemas, indexes, etc.
They affect the database schema.
2. DML (Data Manipulation Language)
Purpose:
DML commands are used to manipulate the data stored in the database (insert,
update, delete, retrieve).

3. DCL (Data Control Language)

Purpose:
DCL commands are used to control access and permissions to data in the database.
 Q35) Explain RAID in detail.
Ans: RAID (Redundant Array of Independent Disks)
Definition:
RAID is a data storage technology that combines multiple physical disk drives into a
single logical unit to achieve data redundancy, improved performance, or both.
The main goal of RAID is to ensure data reliability and faster data access.

⚙️Need for RAID

 To improve performance of disk storage.
 To increase data availability and fault tolerance.
 To store large amounts of data efficiently.
 To recover data in case of disk failure.
RAID Architecture
RAID uses a controller (hardware or software) that manages how data is distributed
across the disks.
 Hardware RAID: Managed by a dedicated RAID controller card.
 Software RAID: Managed by the operating system.

RAID Techniques
RAID achieves performance and reliability through the following methods:
1. Disk Mirroring (Duplication): Copying the same data on two or more disks.
2. Disk Striping (Splitting): Breaking data into blocks and spreading it across multiple
disks.
3. Parity: Storing error-checking information to recover data in case of disk failure.

Advantages of RAID
 Improved performance (especially read/write speed).
 High data reliability through redundancy.
 Fault tolerance — data can be recovered even if one disk fails.
 Increased storage capacity by combining disks.

Disadvantages of RAID
 High cost (especially RAID 1, 10 due to redundancy).
 Complexity in setup and management.
 Parity calculation overhead in some levels (like RAID 5, 6).
Summary
 RAID provides a balance between:
 Performance
 Reliability
 Cost
Different RAID levels are used depending on the application needs —
for example:
 RAID 0 → For speed (gaming, graphics workstations).
 RAID 1 → For reliability (servers).
 RAID 5/6 → For enterprise databases and file servers.

Q36) Explain normalization with different normal forms with suitable example.
Ans: Normalization is a process of organizing data in a database to reduce
redundancy (repetition of data) and improve data integrity.
It divides large tables into smaller ones and defines relationships among them.

🔹 Objectives of Normalization
1. To eliminate data redundancy.
2. To ensure data dependencies are logical.
3. To simplify data maintenance.
4. To make the database more efficient.
Different Normal Forms
1️⃣ First Normal Form (1NF)
A relation is in 1NF if:
 Each cell contains only atomic (indivisible) values.
 Each record is unique (has a unique key).
 2️⃣ Second Normal Form (2NF)
A relation is in 2NF if:
 It is already in 1NF, and
 All non-key attributes are fully functionally dependent on the primary key (no
partial dependency).
Partial dependency means a non-key attribute depends only on part of a composite
key.
 3️⃣ Third Normal Form (3NF)
A relation is in 3NF if:
 It is already in 2NF, and
 There is no transitive dependency (non-key attributes should not depend on other
non-key attributes).

4️⃣ Boyce–Codd Normal Form (BCNF)

A relation is in BCNF if:
 It is in 3NF, and
 For every functional dependency (X → Y), X should be a super key.
BCNF is a stronger version of 3NF — handles anomalies not covered by 3NF.
Q37) Explain different aggregator functions in SQL with suitable example.
ANS: Aggregator (Aggregate) Functions in SQL
Aggregate functions in SQL are used to perform calculations on a set of values and
return a single value as the result.
They are commonly used with the GROUP BY clause to summarize data.
 Summary:
Aggregate functions are used to summarize data — such as counting rows, finding
totals, averages, minimums, and maximums.
They work on multiple rows and return one summarized result per group or entire
table.

Q38) Explain different concurrency control protocols.

ANS: Concurrency Control Protocols are techniques used in a DBMS (Database
Management System) to ensure that multiple transactions can execute concurrently
without interfering with each other, thereby maintaining the consistency and
isolation of the database.
These protocols prevent problems like:
 Lost updates
 Temporary (dirty) reads
 Uncommitted data reads
 Inconsistent retrievals

Types of Concurrency Control Protocols

1. Lock-Based Protocols
These protocols use locks to control access to data items.
a) Two-Phase Locking (2PL) Protocol
 Transaction follows two phases:
1. Growing Phase – Can acquire locks but cannot release any.
2. Shrinking Phase – Can release locks but cannot acquire new ones.
 Ensures conflict serializability.
 2. Timestamp-Based Protocol
Each transaction is given a unique timestamp when it starts.
 Ensures transactions execute in timestamp order.
 Each data item keeps:
o read_TS(X) → largest timestamp of transaction that read X
o write_TS(X) → largest timestamp of transaction that wrote X
Rules:
 If a transaction tries to read/write an item that violates timestamp order, it is rolled
back.
Example:
If T1 (TS=5) and T2 (TS=10):
 T2 cannot read X if write_TS(X) > 10 (i.e., written by a newer transaction).
Advantage: No deadlock.
Disadvantage: May cause many rollbacks.
3. Validation-Based (Optimistic) Protocol
Used when conflicts are rare.
Transactions execute in three phases:
1. Read Phase: Read and perform updates on local copies.
2. Validation Phase: Check if transaction conflicts with others.
3. Write Phase: If validation succeeds, changes are applied to the database.
Advantage: High performance when conflicts are rare.
Disadvantage: Rollback if validation fails.
4. Multiversion Concurrency Control (MVCC)
 Maintains multiple versions of data items.
 Transactions read the most recent committed version valid for their timestamp.
 Writers create new versions instead of overwriting old data.
Used in: PostgreSQL, Oracle, and many modern DBMSs.
Advantage: Readers don’t block writers and vice versa.
Disadvantage: Storage overhead due to multiple versions.

5. Graph-Based (Precedence) Protocol

 Maintains a serialization graph to ensure transactions follow a partial order.
 No cycles are allowed in the graph — ensures serializability.
 Suitable for systems where transaction ordering is known.
 ✅ In summary:
Concurrency control protocols are essential for ensuring serializability and isolation
in multi-user database environments.
Each protocol has trade-offs between performance, complexity, and safety, and the
choice depends on the workload and DBMS design.

Q39) Write a short note on backup and recovery system.

ANS: Backup and Recovery System – Short Note
Backup and Recovery are essential mechanisms in a Database Management
System (DBMS) to protect data from loss or corruption due to hardware failures,
software errors, or human mistakes.

1. Backup System:
A backup is a copy of the database that can be used to restore data in case of failure.
It ensures data availability, integrity, and durability.
Types of Backups:
 Full Backup: A complete copy of the entire database.
 Incremental Backup: Copies only the data that has changed since the last backup.
 Differential Backup: Copies all data changed since the last full backup.

2. Recovery System:
Recovery is the process of restoring the database to a correct state after a failure.
Types of Failures:
 Transaction failure: Due to logical errors or system crash during a transaction.
 System crash: Loss of data in volatile memory (RAM).
 Disk failure: Physical damage or corruption of storage.
Recovery Techniques:
 Log-based recovery: Uses transaction logs to redo or undo operations.
 Checkpointing: Saves the current state of the database periodically to speed up
recovery.
 Shadow paging: Maintains two copies of data pages—one current and one shadow—
to ensure consistency.

Conclusion:
 Backup and recovery systems are vital to maintain data reliability, consistency, and
business continuity in case of unexpected failures.

Q40) Illustrate database characteristics.

ANS: ChatGPT said:
Here’s a concise explanation of Database Characteristics with clear points —

Characteristics of a Database System

A Database Management System (DBMS) has several important characteristics that
distinguish it from a traditional file processing system.

1. Data Abstraction
DBMS provides different levels of data abstraction:
 Physical level: How data is stored.
 Logical level: What data is stored and relationships among data.
 View level: How data is viewed by users.

2. Data Independence
 Changes in storage structure or physical schema do not affect the logical structure of
the database or application programs.
 It allows flexibility and easier maintenance.

3. Data Consistency
 DBMS ensures data accuracy and consistency through integrity constraints.
 Reduces data redundancy by using a centralized database.

4. Data Security
 DBMS provides controlled access through authentication and authorization.
 Only authorized users can access or modify specific parts of the database.

5. Data Integrity
 Maintains correctness of data using rules such as primary key, foreign key, and
unique constraints.

6. Data Sharing and Multi-user Access

 Supports concurrent access by multiple users while maintaining data consistency.
 Ensures concurrency control using locking or timestamp methods.

7. Backup and Recovery

 Provides automatic backup and recovery mechanisms to restore data after system
failure.

8. Reduced Data Redundancy

 Centralized data storage minimizes duplication of data across applications.

9. Data Relationships
 Data is stored in the form of tables (relations) and relationships can be defined using
keys and foreign keys

In Short:
A DBMS ensures efficient, consistent, secure, and reliable management of data by
providing features like data independence, abstraction, integrity, and controlled
access.

Q41) Illustrate about integrity and key constraints with suitable examples?
ANS: Integrity and Key Constraints in DBMS
Integrity constraints are rules that ensure the accuracy, consistency, and validity of
data in a database. These constraints restrict the type of data that can be stored in a
table to maintain data correctness.

1. Integrity Constraints
Integrity constraints are broadly divided into three types:
a) Domain Integrity
 Ensures that the values in a column are within a specific domain (data type or range).
 Example:
 c) Referential Integrity
 Ensures that the relationship between two tables remains consistent.
 Enforced using Foreign Key constraints.
 Example:
Q42) Explain the importance of Null values in Relational Model.
ANS: Importance of NULL Values in Relational Model
In the Relational Model, a NULL value represents missing, unknown, or
inapplicable information in a database table. It is not the same as zero (0) or an
empty string (‘ ’); rather, it indicates that the actual value is not available or
undefined.
✅ In short:
NULL values are essential in the relational model because they allow databases to
represent incomplete or inapplicable information realistically while maintaining data
integrity and flexibility.

Q43) Explain in detail, the form of a basic SQL query with a suitable
example.
ANS: Form of a Basic SQL Query
A SQL query is used to retrieve specific data from one or more tables in a database.
The basic form (syntax) of an SQL SELECT query is as follows:
 Explanation of the Example
 SELECT Department, AVG(Salary) → Retrieves department name and average
salary.
 FROM Employee → Data is fetched from the Employee table.
 WHERE Salary > 40000 → Only considers employees earning more than ₹40,000.
 GROUP BY Department → Groups data by department.
 HAVING AVG(Salary) > 45000 → Displays only departments with an average
salary above ₹45,000.
 ORDER BY Avg_Salary DESC → Sorts the results by average salary in descending
order.

✅ In short:
A basic SQL query follows a structured format that allows filtering, grouping, and
sorting of data efficiently to extract meaningful information from databases.

Q44) List out various SET comparison operator in SQL and also write about
its use in writing SQL queries.

ANS: SET Comparison Operators in SQL

In SQL, SET comparison operators are used to compare a value or set of values with
another set of values returned by a subquery.
These operators are particularly useful in queries that involve checking if a value exists,
matches, or satisfies a condition relative to a group of results.
Q45) Explain in detail about internal hashing Techniques.
ANS: Internal Hashing Techniques (Detailed Explanation)
Definition:
Internal hashing is a hashing technique used for data stored in main memory (RAM)
rather than on external storage like disk. It is mainly used for quick searching, insertion,
and deletion of records within memory-resident tables or files.

1. Concept of Hashing
Hashing is a process that converts a search key (K) into a hash address (h(K)) using a hash
function.
This hash address indicates the location (index) in the hash table where the record is stored.
👉 Formula:
h( K)=K mod m
Where:
 K = search key value
 m = size of hash table
 h( K) = hash address
2. Structure of Hash Table
A hash table consists of:
 Buckets or Slots: Locations where data records are stored.
 Hash Function: Used to compute the index for storing or searching.
  Collision Resolution Mechanism: Used when two keys hash to the same address.

3. Example of Basic Hashing

Suppose we have a hash table of size m = 10,
and we want to store employee IDs: 25, 35, 15, 10, 5

4. Collision Resolution Techniques in Internal Hashing

When two or more keys generate the same hash address, collisions occur.
The following techniques are used to handle collisions:

a) Open Addressing
All records are stored in the same hash table.
When a collision occurs, the system searches for the next empty slot according to a probing
sequence.
Types of Open Addressing:
1. Linear Probing:
o If a collision occurs at position h(K), try (h(K) + 1), (h(K) + 2), … until an
empty slot is found.
o Problem: Causes Primary Clustering (long blocks of filled slots).
Example:
Table size = 10, keys = 25, 35, 45
h( K)=K mod 10
All map to 5 → stored at 5, 6, 7 respectively.

2. Quadratic Probing:
o Avoids clustering by using quadratic increments:
2
hi (K )=(h( K)+i )mod m
o Tries positions: h( K) , h( K )+12 ,h (K )+2 2 , h( K )+32 ,.. .
 3. Double Hashing:
o Uses a second hash function to determine the probe step size.
hi (K )=(h1 (K )+i× h2 (K ))mod m
o Reduces clustering further and improves performance.

6. Advantages of Internal Hashing

 Fast data access (average case O(1) time).
 Efficient for in-memory lookup tables.
 Suitable for applications like symbol tables, compiler design, and caching.

7. Limitations
 Collisions reduce performance.
 Inefficient if load factor (ratio of entries to table size) is high.
 Requires good hash function to avoid clustering.

8. Applications
 Symbol tables in compilers.
 Caching systems.
 Database index structures (in-memory).
  Search engines and hash-based data structures (like Python dictionaries, Java
HashMaps).

✅ In summary:
Internal hashing stores records in main memory using a hash function to compute storage
addresses. Efficient collision resolution (linear, quadratic, double hashing, or chaining)
ensures quick access and minimal search time.

Q46) Discuss in detail about cluster and Multilevel indexes.

ANS: Let’s discuss Cluster Index and Multilevel Index in detail — both are important
indexing techniques in database systems used to improve data retrieval efficiency.

1. Cluster Index
Definition:
A Clustered Index determines the physical order of data in a table. In other words, the data
rows in the table are stored in the same order as the index. Therefore, there can be only one
clustered index per table, because data rows themselves can only be stored in one order.

Key Features:
 The table’s data is physically arranged based on the index key.
 The leaf nodes of the index tree contain the actual data rows.
 Improves performance for range queries (e.g., BETWEEN, <, >, etc.).
 Only one clustered index per table, but multiple non-clustered indexes can exist.

Advantages:
1. Faster data retrieval for range-based queries.
 2. Efficient sorting as data is already ordered.
3. Improves I/O performance since related records are stored close together.

Disadvantages:
1. Slower insert/update operations, since maintaining order requires rearranging rows.
2. Only one clustered index per table.
3. Rebuilding the clustered index can be time-consuming for large datasets.

2. Multilevel Index
Definition:
A Multilevel Index is an indexing method where indexes are built on top of other indexes
to reduce the number of disk accesses required for searching.
It is a hierarchical indexing structure—similar to a tree (like B-tree or B+ tree).
Need for Multilevel Indexing:
If a file is large, the primary index itself may not fit in memory.
So, we create a second-level index on the first-level index.
If needed, we can continue creating higher levels until the top-level index fits entirely in main
memory.

E
xample:
Suppose we have 1,000,000 records and use a primary index with 1000 entries per block.
 Level 1 index: 1,000,000 / 1000 = 1000 entries
 If each block can store 100 entries, we need 10 blocks for level 1 index.
 So, we build a Level 2 index (of 10 entries) to locate the Level 1 index blocks.
Thus, the search process reduces from thousands of block reads to just a few levels of index
traversal.

Advantages:
1. Efficient searching: Reduces disk I/O by limiting search path.
2. Scalable: Works well for very large databases.
3. Memory optimization: Higher levels can fit into main memory.
Disadvantages:
1. Complexity: Managing multiple levels increases maintenance overhead.
2. Update cost: Insertion or deletion may require updates at multiple levels.
3. Storage overhead: Additional storage is needed for multiple index levels.

✅ In summary:
 A Clustered Index organizes actual data based on the index key.
 A Multilevel Index is a hierarchical index structure that improves access speed for
large datasets.

Q47) Explain purpose of database system.

ANS: Purpose of Database System
The main purpose of a Database System (DBMS) is to store, manage, and retrieve data
efficiently while ensuring data consistency, security, and integrity. It helps organizations
handle large amounts of data systematically and avoids the problems of traditional file-based
systems.

Key Purposes of a Database System

1. Data Storage and Retrieval
o The primary purpose is to store large volumes of data in a structured way.
o It allows users to easily insert, update, delete, and retrieve information using
queries (e.g., SQL).
Example: Retrieving all customer details from a banking database.

2. Data Sharing
o Multiple users and applications can access the same data concurrently without
conflicts.
o This supports multi-user environments and collaboration.
Example: In a university database, both the administration and students can access course
information simultaneously.

3. Data Integrity and Consistency

o Ensures that data remains accurate and consistent even after multiple updates
or failures.
o Uses constraints (like primary key, foreign key) to maintain correctness.
Example: Preventing duplicate roll numbers for students.

4. Data Security
o Protects data from unauthorized access through authentication, authorization,
and encryption.
o Different users can have different access privileges.
Example: A student can view grades, but only a teacher can update them.

5. Data Independence
o The structure of the database can be changed without affecting how
applications access data.
o Separates the logical view of data from the physical storage details.
Example: Changing how data is stored on disk without affecting SQL queries.

6. Reduced Data Redundancy and Inconsistency

o Eliminates duplication of data that was common in file-based systems.
o Data is stored only once and can be linked through relationships.
Example: Customer details stored once and referenced by orders and payments tables.

7. Backup and Recovery

o Provides automatic backup and recovery mechanisms to protect data from
system crashes or failures.
Example: Restoring database state after a power failure.

Conclusion
A Database Management System (DBMS) serves as an organized framework for handling
data efficiently.
It ensures that data is accurate, secure, consistent, and easily accessible, making it an
essential component of modern information systems.

In short:
The purpose of a database system is to provide a systematic, secure, and efficient way to
store, manage, and retrieve data while ensuring data integrity, consistency, and accessibility
for multiple users.

Q48) Explain Data Abstraction.

ANS: ChatGPT said:
Data Abstraction in DBMS refers to the process of hiding the complex details of how data
is stored and maintained, while showing only the essential features to the users. It simplifies
the interaction between the user and the database system by providing different levels of data
visibility.

🔹 Purpose of Data Abstraction

 To reduce complexity for users.
 To protect data independence (changes in one level don’t affect others).
 To improve data security and integrity.
 To simplify user interaction with data.

🔹 Levels of Data Abstraction

There are three levels of abstraction in a database system:
1. Physical Level (Lowest Level)
 Describes how data is actually stored in memory or storage.
 Deals with complex structures like files, indexes, and access paths.
 Example: Storing a “Student” record in a file as binary data with pointers.
2. Logical Level (Conceptual Level)
 Describes what data is stored in the database and relationships among data.
 Defines tables, fields, and constraints.
 Example: The database has a Student table with attributes (RollNo, Name, Course,
Age).
3. View Level (Highest Level)
 Describes how users interact with the database.
 Provides different views for different users, hiding unnecessary details.
 Example: A student sees only Name and Course, while the admin sees all fields.

🔹 Example
Suppose a database has student records.
  Physical Level: Data stored as binary records in hard disk files.
 Logical Level: STUDENT(RollNo, Name, Course, Age)
 View Level:
o Student view: shows Name, Course
o Admin view: shows all attributes.
 ✅ In Summary
 Data Abstraction = Hiding data storage details → showing only relevant
information.
It provides simplicity, security, and data independence in a database system.

Q48) Explain data storage & Querying.

ANS: Data Storage and Querying
In a Database Management System (DBMS), data storage and querying are two
fundamental operations that determine how data is stored, organized, and retrieved
efficiently.

1. Data Storage
Data storage refers to how data is physically stored and managed in a database system.
a) Storage Hierarchy
Data in a database is stored across several layers of memory:
 Primary Storage: Main memory (RAM) – used for temporary data while processing
queries.
 Secondary Storage: Hard disks or SSDs – used for permanent storage of database
files.
 Tertiary Storage: Backup devices like tapes or cloud storage – used for archival and
recovery.
b) Storage Structures
DBMS uses various file structures to manage data efficiently:
 Heap Files: Records are stored in no particular order.
 Sequential Files: Records are stored in sorted order (based on key field).
 Indexed Files: Uses indexes (like B+ trees, hash indexes) for fast retrieval.
 Clustered Files: Related records are stored together physically for faster access.
c) Data Storage Formats
Data is stored in:
 Tables (Relations): Rows and columns structure.
 Pages/Blocks: Each table is divided into fixed-size blocks stored on disk.
 Records and Fields: Each block contains records (tuples) with multiple fields
(attributes).
2. Querying
Querying is the process of retrieving or manipulating data stored in the database using a
query language, typically SQL (Structured Query Language).
a) Types of Queries
 Data Retrieval: Using SELECT statements to extract data.
 Data Modification: Using INSERT, UPDATE, DELETE commands.
 Schema Definition: Using CREATE and ALTER to define database structure.
b) Query Processing Steps
1. Parsing: The DBMS checks query syntax and semantics.
2. Optimization: The query optimizer finds the most efficient way to execute the query
(chooses indexes, joins, etc.).
3. Execution: The optimized query plan is executed to retrieve results from stored data.

d) Query Optimization
The DBMS uses indexes, joins, and access paths to minimize I/O operations and improve
speed.

In short:
👉 Data Storage ensures data is efficiently saved and organized.
👉 Querying allows users to extract and manipulate that data effectively through SQL
commands.

Q49) Explain Function and procedure with example.

ANS: Function and Procedure in DBMS / SQL
In SQL (or PL/SQL), functions and procedures are subprograms that perform specific
tasks.
They help in modular programming, code reusability, and simplify complex operations.
 Explanation
:
 GetEmployeesByDept is a stored procedure.
 Takes dept_id as input.
 Displays all employees in that department.

 2. FUNCTION
 A Function is similar to a procedure but it must return a single value.
It is often used in SQL expressions (like SELECT statements).
 ✅ Summary:
 Function: returns a value and can be used in queries.
 Procedure: performs operations and may not return a value.

Q50) Explain serializability.

ANS: ChatGPT said:
Serializability is a key concept in database concurrency control, ensuring that even
when multiple transactions are executed concurrently, the final result of the
database remains consistent — just as if the transactions had been executed one after
another (serially).

🔹 Definition:
A schedule (sequence of read/write operations from multiple transactions) is said to
be serializable if its outcome is the same as that of some serial schedule — i.e., a
schedule where transactions are executed one after the other without overlapping.

🔹 Types of Serializability:
There are mainly two types:
1. Conflict Serializability
A schedule is conflict serializable if it can be transformed into a serial schedule by
swapping non-conflicting operations.
Two operations conflict if:
 They belong to different transactions, and
 They operate on the same data item, and
 At least one of them is a write operation.
Example:

Schedule Explanation
T1: Read(A), Both access the same data item A; order of writes
Write(A) matters.
T2: Read(A),
 Schedule Explanation
Write(A)
If we can reorder operations of T1 and T2 without changing the result, it is conflict
serializable.

2. View Serializability
A schedule is view serializable if it is view equivalent to a serial schedule —
meaning:
 Each transaction reads the same values as in the serial schedule, and
 The final writes on each data item are the same.
Note:
All conflict-serializable schedules are view-serializable,
but not all view-serializable schedules are conflict-serializable.

🔹 Why Serializability is Important

 Ensures data consistency in concurrent execution.
 Prevents problems like dirty reads, lost updates, and inconsistent retrievals.
 Provides a formal criterion for correctness of concurrent transaction execution.

Q51) Explain deadlock handling.

ANS: Deadlock Handling in DBMS
A deadlock occurs in a database system when two or more transactions are waiting
indefinitely for one another to release locks on resources.
 It’s a situation where no progress is possible, because each transaction holds a
resource the other needs.

Here,
 T1 holds X and waits for Y (locked by T2),
 T2 holds Y and waits for X (locked by T1).
Both transactions wait forever — this is a deadlock.

Deadlock Handling Methods

There are three main approaches to handle deadlocks:
1. Deadlock Prevention
This approach prevents deadlocks by ensuring at least one of the necessary
conditions for deadlock never occurs.
Methods:
 Resource ordering: Assign a unique order to resources; transactions must request
resources in that order.
 Wait-die scheme (Non-preemptive):
o If an older transaction requests a resource held by a younger one, it waits.
o If a younger one requests a resource held by an older one, it dies (rolls back
and restarts).
 Wound-wait scheme (Preemptive):
o If an older transaction requests a resource held by a younger one, it wounds
(forces rollback of) the younger one.
o If a younger requests from an older, it waits.
✅ Advantage: Prevents deadlocks before they occur.
❌ Disadvantage: May cause frequent rollbacks.
2. Deadlock Avoidance
Here, the system dynamically examines whether a transaction could lead to a
deadlock before granting a resource.
Common technique:
 Wait-for graph or Banker’s Algorithm
o The system keeps track of all resource allocations and waits.
 o If granting a request keeps the system in a safe state, it proceeds; otherwise,
the transaction waits.
✅ Advantage: Avoids deadlock efficiently.
❌ Disadvantage: Requires knowledge of future requests and maximum resource
needs.
3. Deadlock Detection and Recovery
Deadlocks are allowed to occur, but the system detects and resolves them.
Steps:
1. Detection:
o Use a Wait-for Graph (WFG) where:
 Each node = transaction
 An edge T1 → T2 means T1 is waiting for T2
o If a cycle exists → deadlock detected.
2. Recovery:
o Abort one or more transactions to break the cycle.
o Selection criteria for victim transaction:
 Least cost of rollback
 Fewest resources used
 Shortest remaining time
o Rollback: Undo partial work of aborted transaction.
✅ Advantage: Simple to implement.
❌ Disadvantage: Performance overhead due to detection and rollbacks.

In short:
Deadlock handling ensures that transactions do not get stuck waiting forever for
resources.
It can be achieved through prevention, avoidance, or detection & recovery
techniques depending on the DBMS strategy.
 Q52) Why would you choose a database system instead of simple storing data in
file processing system? Compare file processing system and DBMS.
ANS: A Database Management System (DBMS) is preferred over a File Processing
System (FPS) because it offers better data management, security, consistency, and
efficiency.
Let’s understand the reasons and comparison clearly 👇

🌟 Why choose a Database System instead of a File Processing System?

File Processing System stores data in separate files and each application program
manages its own data. This leads to problems such as:
 Data redundancy (same data stored multiple times),
 Data inconsistency (different copies of data not matching),
 Difficulty in accessing data,
 Poor security and data sharing.
A DBMS solves these problems by providing a centralized, structured, and controlled
environment for data storage and manipulation.

✅ Conclusion
A DBMS is preferred because it provides:
 Centralized control of data,
 Better data integrity, security, and consistency,
 Easier data access and management using SQL,
 Support for multiple users and recovery features.
Hence, for large-scale, multi-user, and secure data management — DBMS is the best
choice.

Q53) Describe the components of a a) Query Processor b) Storage Processor

ANS: Here’s a clear and complete explanation 👇

a) Query Processor
The Query Processor is the component of a DBMS responsible for interpreting and
executing user queries.
It transforms high-level queries (like SQL) into a low-level execution plan that the
database can perform efficiently.
Components of Query Processor:
1. DDL Interpreter
o Interprets the Data Definition Language (DDL) statements such as CREATE,
ALTER, and DROP.
o Passes the schema definitions to the Metadata Manager.
2. DML Compiler
o Translates Data Manipulation Language (DML) statements (like SELECT,
INSERT, UPDATE, DELETE) into a query execution plan.
o Breaks down complex queries into a series of low-level operations.
3. Query Optimizer
o Finds the most efficient way to execute a query by considering different query
plans.
o Uses optimization techniques to minimize cost (time, CPU, I/O).
o Example: Choosing the best index or join method.
4. Query Evaluator / Executor
o Executes the optimized query plan.
o Interacts with the storage manager to retrieve or modify data.
o Returns the results to the user or application.

b) Storage Processor (Storage Manager)

The Storage Processor (also called Storage Manager) is responsible for storing,
retrieving, and managing data in the database.
It acts as the interface between the low-level data stored on disk and the higher-level
query processor.
Components of Storage Processor:
1. Transaction Manager
o Ensures that all database transactions follow ACID properties (Atomicity,
Consistency, Isolation, Durability).
o Handles commit and rollback operations.
2. Buffer Manager
o Manages the transfer of data between main memory (RAM) and disk storage.
o Keeps frequently accessed data in memory to improve performance.
3. File Manager
o Handles allocation of space on disk for data files.
o Manages the structure and organization of files storing database records.
4. Authorization and Integrity Manager
o Checks for user permissions and ensures data integrity constraints (like
primary key, foreign key, etc.) are not violated.
5. Data Files and Index Manager
o Manages the physical files where actual data and indexes are stored.
o Helps in quick access and retrieval of records

Q53) Describe shortly

a) Database Recovery Techniques
b) Causes of Database failures

ANS: a) Database Recovery Techniques:

Database recovery techniques are methods used to restore a database to a correct
state after a failure. Common techniques include:
1. Backup and Restore: Restoring the database from the last consistent backup.
2. Log-based Recovery: Using transaction logs (UNDO and REDO logs) to reverse or
reapply transactions after a crash.
3. Checkpointing: Periodically saving the database state so recovery can start from the
last checkpoint.
4. Shadow Paging: Maintaining two copies (shadow and current) of pages to ensure
atomic updates.

b) Causes of Database Failures:

1. Transaction Failure: Errors like invalid input or logical errors cause transaction
termination.
2. System Crash: Hardware or software failures (e.g., power loss, OS crash).
3. Media Failure: Disk damage or corruption of storage media.
4. Natural or Human Errors: Accidental deletion, malicious activities, or disasters.
 Q54) What is DBMS? Explain the need of DBMS. Write any 3 application of DBMS.
ANS: DBMS (Database Management System)
A Database Management System (DBMS) is a software system that is used to store,
organize, manage, and retrieve data efficiently. It provides an interface between the
user and the database, ensuring that data is consistent, secure, and easily accessible.

Need for DBMS:

1. Data Redundancy Control:
DBMS minimizes duplication of data by maintaining a centralized database.
2. Data Consistency:
Since data is stored in one place, any update made is automatically available to all
users.
3. Data Security:
DBMS provides access control and authentication to protect data from unauthorized
access.
4. Data Integrity:
Ensures accuracy and reliability of data using integrity constraints.
5. Efficient Data Access:
DBMS uses powerful query languages like SQL to quickly retrieve and manipulate
data.
6. Backup and Recovery:
It provides automatic backup and recovery mechanisms to protect data from failures.

Applications of DBMS:
1. Banking Systems:
Used for managing customer accounts, transactions, and loans.
2. Airline Reservation Systems:
Used for booking, scheduling, and tracking flight information.
3. Hospital Management Systems:
Used for maintaining patient records, doctor schedules, and billing information.

✅ In short:
DBMS is a system that manages data efficiently and provides features like data
security, consistency, and recovery — making it essential for modern data-driven
applications.