UNIT-I: DBMS Basics

a) Define the terms: Data, Database, and DBMS. (CO1, L1)

 Data: Raw facts or figures without context (e.g., numbers, text).

 Database: Organized collection of data stored electronically.
 DBMS (Database Management System): Software that manages and facilitates access
to databases.

b) Distinguish between a file-processing system and a DBMS. (CO1, L2)

 File Processing System:

o Data stored in separate files.
o Limited scalability and redundancy issues.
 DBMS:
o Centralized storage.
o Reduces redundancy and ensures consistency.

c) What are the differences between Super key, Candidate key, and Primary key? Explain
with a suitable example. (CO1, L2)

 Super key: Any set of attributes uniquely identifying a row in a table.

(Example: {Student_ID, Name} for a Student table)
 Candidate key: Minimal subset of a super key that uniquely identifies a row.
(Example: {Student_ID})
 Primary key: A chosen candidate key that uniquely identifies rows.
(Example: {Student_ID} as the primary key)

d) Explain three-level architecture of DBMS. (CO1, L1)

1. Physical Level: Describes how data is stored (e.g., files, indices).

2. Logical Level: Defines schema and relationships.
3. View Level: Abstracts data presentation for users.

e) What do you mean by Data Abstraction? (CO1, L1)

 Data abstraction hides database complexity and provides a user-friendly interface by

dividing data into physical, logical, and view levels.

f) Explain Entity, Attribute, and Tuple with suitable example. (CO1, L1)

 Entity: Real-world object (e.g., Student).

 Attribute: Property of an entity (e.g., Name, Age).
 Tuple: Row in a table representing an entity instance.
(Example: (John, 20, B.Tech)).
g) Explain Logical Data Independence. (CO1, L1)

 Ability to change the logical schema without altering the external schema or application
programs.

h) Give an example of simple & composite attributes of an entity. (CO1, L1)

 Simple Attribute: Cannot be divided further (e.g., Age).

 Composite Attribute: Consists of multiple components (e.g., Full Name = First Name +
Last Name).

i) What is Data Independence? (CO1, L1)

 Ability to modify schema definitions without affecting application programs.

(Types: Logical and Physical).

j) What are the advantages of DBMS? (CO1, L1)

 Data redundancy control.

 Enhanced data sharing and consistency.
 Improved data security and recovery.

UNIT-II: SQL and Relational Model

a) What is SQL? (CO2, L1)

 SQL (Structured Query Language): A standard language for querying and managing
relational databases.

b) What are stored procedures, and how do they differ from triggers? (CO2, L2)

 Stored Procedures: Predefined SQL code executed on demand.

 Triggers: Automatically executed SQL code when specific database events occur.

c) What is meant by cursors? (CO2, L1)

 Cursor: A database object that retrieves and manipulates query results row by row.

d) What do you mean by Referential Integrity? Define Foreign Key and discuss the concept
behind the declaration of foreign keys. (CO2, L2)

 Referential Integrity: Ensures relationships between tables remain consistent.

 Foreign Key: Attribute in one table referencing the primary key in another.
(Concept: Prevents orphaned records).
e) What are different aggregate functions used in SQL? (CO2, L2)

 Aggregate Functions:
o SUM(): Calculates total.
o AVG(): Finds average.
o COUNT(): Counts rows.
o MAX() / MIN(): Finds highest/lowest values.

f) Explain Relational Algebra operations with a suitable example. (CO2, L2)

 Operations:
o Select (σ): Filters rows (e.g., σ Age > 20).
o Project (π): Selects columns (e.g., π Name).
o Join (⨝): Combines tables based on conditions.

g) Mention different types of SQL Commands. (CO2, L2)

 Types:
o DDL: Create, Alter, Drop.
o DML: Select, Insert, Update, Delete.
o DCL: Grant, Revoke.
o TCL: Commit, Rollback.

h) What is Relational Algebra? (CO2, L1)

 Relational Algebra: A formal system of operations on relations (tables) in RDBMS.

i) What is RDBMS? Write down the advantages of RDBMS. (CO2, L1)

 RDBMS (Relational Database Management System): Stores data in tables with

relationships.
o Advantages:
 Scalability.
 Data Integrity.
 Supports complex queries.

j) Write an SQL SELECT statement to display all columns of the STUDENT table but only
those rows where the Grade column is greater than or equal to 90. (CO2, L3)

SELECT *
FROM STUDENT
WHERE Grade >= 90;

UNIT-III: Normalization

a) What are Normal Forms? (CO3, L1)

  Normal Forms: Guidelines to organize data in a relational database to reduce
redundancy and improve data integrity. Includes 1NF, 2NF, 3NF, BCNF, and 4NF.

b) Define 1NF with example. (CO3, L2)

 1NF (First Normal Form): A table is in 1NF if all its columns contain atomic
(indivisible) values. Example:

StudentID Subjects
101 Math, Science
Convert to:
StudentID Subject
----------- -----------------
101 Math
101 Science

c) Define BCNF with example. (CO3, L1)

 BCNF (Boyce-Codd Normal Form): A stronger version of 3NF. A table is in BCNF if

every determinant is a candidate key. Example:
o If Course → Professor and Professor → Course, break into:
 Table 1: Course → Professor
 Table 2: Professor → Specialization.

d) Define 4NF with example. (CO3, L2)

 4NF (Fourth Normal Form): A table is in 4NF if it is in BCNF and has no multi-valued
dependencies. Example:
If a table has two independent attributes (e.g., Teacher → Subject, Teacher → Club),
separate them into two tables.

e) Discuss the First and Second Normal Forms. (CO3, L2)

 1NF: Eliminates repeating groups, ensures atomicity.

 2NF: Achieved when the table is in 1NF and all non-key attributes depend on the entire
primary key.

f) Define 3NF with example. (CO3, L3)

 3NF (Third Normal Form): A table is in 3NF if it is in 2NF and no non-prime attribute
depends transitively on the primary key. Example:
If StudentID → Advisor and Advisor → Department, split into:
o Table 1: StudentID → Advisor
o Table 2: Advisor → Department.
g) Define 2NF with example. (CO3, L1)

 2NF (Second Normal Form): A table is in 2NF if it is in 1NF and all non-key attributes
are fully functionally dependent on the primary key.
Example: Split StudentID, Course → Grade into:
o Table 1: StudentID → StudentDetails
o Table 2: Course → Grade.

h) What is Functional Dependency? (CO3, L1)

 Functional Dependency: Relationship where one attribute uniquely determines another.

(Example: RollNumber → Name).

i) What is 4NF? (CO3, L1)

 4NF: A table with no multi-valued dependencies, maintaining data independence and

avoiding redundancy.

j) What are insertion and deletion anomalies? (CO3, L1)

 Insertion Anomaly: Inability to add data due to absence of related data.

(Example: Cannot add a new course without assigning it to a student.)
 Deletion Anomaly: Loss of additional data when deleting a record.
(Example: Deleting a student also removes course details.)

UNIT-IV: Transactions and Recovery

a) What do you mean by Transaction System? (CO4, L1)

 Transaction System: A DBMS system ensuring data consistency during operations like
insert, update, delete.

b) What is a recoverable schedule? (CO4, L1)

 Recoverable Schedule: A schedule where committed transactions can roll back to

maintain consistency in case of failure.

c) List the ACID properties of transactions. (CO4, L1)

1. Atomicity: Transactions are all-or-nothing.

2. Consistency: Maintains database rules.
3. Isolation: Transactions do not interfere.
4. Durability: Changes persist after commit.
d) Discuss the recovery from transaction failures. (CO4, L2)

 Recovery mechanisms:
1. Undo/Redo Logging.
2. Checkpoints.
3. Shadow Paging.

e) What is Log? (CO4, L1)

 Log: A sequential record of all database modifications, used for recovery.

f) What is durability in DBMS? (CO4, L1)

 Durability: Ensures committed transactions persist even after a crash.

g) What do you mean by atomicity? (CO4, L1)

 Atomicity: Ensures that a transaction is fully completed or not performed at all.

h) What is a checkpoint and when does it occur? (CO4, L2)

 Checkpoint: A saved database state to reduce recovery time, occurring periodically

during transactions.

i) What is Serializability? (CO4, L1)

 Serializability: Ensures concurrent transactions result in the same outcome as serial

execution.

j) What is Aggregation? (CO4, L2)

 Aggregation: Combines data from multiple entities to represent a higher-level

relationship.
(Example: A project with multiple employees and managers.)

UNIT-V: Concurrency Control and Distributed Systems

a) What are Locks? (CO5, L1)

 Locks: Mechanisms to control access to database resources during concurrent

transactions.

b) What is multi-version schemes of concurrency control? (CO5, L1)

  Multi-version Concurrency Control: Allows multiple versions of data for read
consistency during updates.

c) What is Two-phase Locking Protocol? (CO5, L1)

 Two-phase Locking:
1. Growing Phase: Transaction acquires locks.
2. Shrinking Phase: Transaction releases locks.

d) What benefit does rigorous two-phase locking provide? (CO5, L2)

 Ensures strict serializability, preventing deadlocks and cascading rollbacks.

e) What do you mean by Multiple Granularity? (CO5, L2)

 Multiple Granularity: Locking at different levels (e.g., rows, tables) to improve

concurrency control.

f) Why is concurrency control needed? (CO5, L2)

 To prevent:
1. Dirty reads.
2. Lost updates.
3. Inconsistent analysis.

g) What is an exclusive lock? (CO5, L2)

 Exclusive Lock: Allows only one transaction to write or modify a resource.

h) Describe concurrency control. (CO5, L1)

 Concurrency Control: Techniques to manage simultaneous transaction execution while

ensuring correctness.

i) What is directory system? (CO5, L2)

 Directory System: A centralized file catalog system ensuring file location and access
consistency.

j) What is distributed system? (CO5, L1)

 Distributed System: A system where database resources are spread across multiple
locations connected via a network.