Q1.What is Transaction? Draw and explain Transaction State diagram.

:A transaction in a
database management system (DBMS) is a single logical unit of work that consists of one or
more operations (such as read, write, update, delete) performed on database.A transaction
must satisfy the ACID properties:
 Atomicity: All operations must complete successfully or none at all.
 Consistency: The database must remain in a consistent state before and after the trans.
 Isolation: Transactions should not interfere with each other.
 Durability: Once committed, the changes are permanent.

A transaction begins in the Active state, where it performs read/write operations. If

operations complete successfully, it moves to the Partially Committed state, meaning it’s
ready to save changes but hasn’t yet. After passing system checks, it enters the Committed
state, where changes are permanently saved, ensuring durability.
If any error occurs before committing (e.g., crash or constraint failure), it transitions to the
Failed state. The system then rolls back all changes, and the transaction moves to the
Aborted state, meaning it's terminated. Some systems may allow the transaction to restart
after aborting.
Q2.Acid Properties : 1. Atomicity
Definition: A transaction is treated as a single unit, which either completes entirely or not at
Explanation: If any part of the transaction fails, the whole transaction is rolled back, and the
database remains unchanged.
Example: In a money transfer, if money is debited from one account but not credited to
another due to an error, the debit is also undone.
2. Consistency:Definition: A transaction must bring the database from one valid state to
another valid state, maintaining all rules and constraints.
Explanation: It ensures that the integrity of the database is preserved before and after the tran
Example: If a rule says "account balance cannot be negative", no transaction should violate .
3. Isolation:Definition: Transactions should occur independently of each other.
Explanation: Intermediate results of a transaction should not be visible to other transactions
until it completes.Example: Two users transferring money simultaneously shouldn’t interfere
with each other’s transactions. 4. Durability:Definition: Once a transaction is committed, its
changes are permanent, even in the case of a system crash.Explanation: The system
guarantees that committed changes will survive power loss or failure.
Example: If money is transferred and committed, it won’t be lost even if the server crashes
right after.
Q3 No-Sql and OlAP & SQL
NoSQL (Not Only SQL) databases are non-relational databases designed to handle large
volumes of unstructured, semi-structured, or structured data. They are schema-less, highly
scalable, and ideal for big data and real-time web apps.
Types of NoSQL Databases
1.Document-Based
Stores data in JSON-like documents (key-value pairs).
Flexible schema: each document can have different fields. Example: MongoDB, CouchDB
2.Key-Value StoresData is stored as a collection of key-value pairs.Best for fast lookups.
Example: Redis, DynamoDB
3.Column-Based::Stores data in columns rather than rows.Efficient for queries on large datas.
Cassandra, HBase
4.Graph-Based:Stores data as nodes and edges (relationships).
Great for social networks, recommendation engines.
Advantages of NoSQL::High performance and scalability Handles diverse data types
Suitable for real-time applications and big data
Q.OLAP (Online Analytical Processing) supports multidimensional analysis of business data.
It allows users to analyze data from multiple perspectives using the following operations:
1.Roll-Up (Aggregation)::Moves from detailed data to summarized data.
Example: From “sales by city” to “sales by state”.
2. Drill-Down (Decomposition)::Opposite of Roll-Up. Moves from summarized data to
detailed data.
Example: From “sales by year” to “sales by quarter or month”.
3. Slice:Selects a single dimension from a cube.
4. Dice:Selects data based on multiple dimensions.
5. Pivot (Rotate):Reorients the data to provide a different view.
Example: Swapping rows and columns in a report to see “Products” as columns and
“Regions” as rows.
Q5 SQL: SQL databases are relational database management systems (RDBMS) that store
data in tables (rows and columns) and use Structured Query Language (SQL) to query and
manage the data.
Key Features of SQL Databases:
 Structured schema (fixed columns and types)
 Enforce ACID properties (Atomicity, Consistency, Isolation, Durability)
 Use primary keys and foreign keys for relationships
Q6.DBMS : Definition of DBMS (Database Management System):
A Database Management System (DBMS) is a software system that allows users to create,
store, manage, and manipulate databases easily and efficiently.
Key Functions of a DBMS:
1. Data Storage – Stores data in structured formats like tables.
2. Data Retrieval – Allows users to query and fetch data using languages like SQL.
3. Data Manipulation – Supports insertion, deletion, and updating of data.
4. Access Control – Provides security by managing user permissions
Q.7Parallel Database System & Its Architectural Models
A Parallel Database System is designed to improve performance by executing multiple
database operations simultaneously using multiple processors or machines. It enables
faster query processing, high throughput, and efficient handling of large datasets.
Architectural Models of Parallel DBMS:
1. Shared Memory: All processors share a common memory and disk. Fast but less
2. Shared Disk: Processors have private memory but access shared disks. Balanced and
fault-tolerant.
3. Shared Nothing: Each processor has its own memory and disk. Highly scalable (used
in big data systems).4.Hybrid: Combines features of the above architectures.
Q8. Intra-query Parallelism:
In this type, a single complex query is divided into smaller operations, and these are
executed in parallel across multiple processors. It improves the response time for large or
complex queries.
Example: A big SELECT query that involves scanning, filtering, and joining large tables is
split into multiple tasks. Each task is processed simultaneously by different processors to
speed up the execution.
Used for: Large data analysis, data mining, and real-time reporting where one query needs to
be completed faster.
Inter-query Parallelism:
In this approach, multiple separate queries from different users or applications are executed
concurrently, each on different processors. It increases the system’s throughput and is
useful in multi-user environments.
Example: Two users submit different queries at the same time—one asking for customer
data, and another for sales data. Both are processed independently and in parallel.
Used for: Transactional systems or servers with high user query loads.
Q9. Two-Phase Locking Protocol (2PL):
The Two-Phase Locking Protocol is a concurrency control method used in database systems
to ensure serializability — that is, the result of concurrent transactions is the same as if they
were executed one after another (serially).
1.Growing Phase:Transaction acquires all required locks (read or write). No locks are
released during this phase.
2.Shrinking Phase:Transaction releases locks. No new locks can be acquired after this
phase starts.
Q10 DDL (Data Definition Language):
DDL is used to define and modify the structure of database objects like tables, schemas,
etc. Common DDL Commands: CREATE, ALTER, DROP, TRUNCATE
CREATE TABLE Students (ID INT, Name VARCHAR(50),Age INT);
DML (Data Manipulation Language):
DML is used to manage data within the tables (insert, update, delete, retrieve).
DML Commands: INSERT, UPDATE, DELETE, SELECT
🔹 Example:INSERT INTO Students (ID, Name, Age)VALUES (1, 'Reema', 20);

Q11.DATA Where housing : Architecture and Components of Data Warehouse

A Data Warehouse is a centralized system that stores historical and current data from
multiple sources to support decision-making and business intelligence.
1. Data Warehouse Architecture:
Typically follows a three-tier architecture:
1. Bottom Tier – Data Source Layer:
o Contains databases, flat files, ERP/CRM systems.
o Data is extracted from these sources.
2. Middle Tier – Data Staging & Storage Layer:
o ETL Process: Extract, Transform, Load.
o Data is cleaned, transformed, and loaded into the warehouse.
o Data is stored in multidimensional schemas like star/snowflake.
3. Top Tier – Front-End / Presentation Layer:
o Tools used for reporting, querying, OLAP, and visualization.
o Helps users analyze and generate insights.
Main Components of a Data Warehouse:
1. Data Sources: External/internal data (e.g., sales, HR).
2. ETL Tools: For data extraction, transformation, and loading.
3. Data Warehouse Storage: Central repository to hold data. 4..BIG Data
Q12 .Trigger: A trigger is a special stored procedure that automatically executes in
response to certain events on a table (like insert, update, delete).
Used for:Auditing,Enforcing business rules,Automatic data validation or logging
CREATE TRIGGER LogInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO LogTable (Action, Time)
VALUES ('Insert into Students', CURRENT_TIMESTAMP);
END;