DBMS VIVA QUES FOR PREP

M-1
1.1 Introduction
• A Database stores data in a well-organized way so it can be easily accessed,
managed, and updated.
• A DBMS (Database Management System) is software like MySQL, Oracle, or
MongoDB that helps users interact with the database.

Example: In a school, a database can store student names, roll numbers, marks,
etc., and a DBMS is used to access or update that info easily.

Characteristics of Databases
1. Real-world Representation – Stores real-world data like employees, products,
etc.
2. Less Redundancy – Avoids storing the same data again and again.
3. Data Consistency – Data stays the same across the system.
4. Data Integrity – Ensures accuracy and correctness.
5. Security – Only authorized users can access or change data.
6. Backup and Recovery – Helps recover data in case of system failure.
7. Multi-user Access – Many people can use it at the same time.

File System vs. Database System

Feature File System Database System

Data Redundancy High Low

Consistency Difficult Easy to maintain

Security Weak Strong with user roles

Data Sharing Limited Easy to share with multiple users

Backup & Recovery Manual Automatic

Example: In a file system, student info may be saved in many files (name.txt,
marks.txt), but in a DBMS, it’s all linked and stored properly in tables.

Data Abstraction
• Hides the technical details and shows only necessary parts to the user.
Levels of Abstraction:
1. Physical Level – How data is stored (hard disk, indexes, etc.)
2. Logical Level – What data is stored and relationships (tables, fields)
3. View Level – What the user sees (just a part of the data)

Example: You search for a song in Spotify. You see the name and artist (View
level), but don’t know how or where it’s stored (Physical level).

Data Independence
• Means you can change the database without affecting the whole system.
Types:
1. Logical Data Independence – You can change the table structure without
affecting user views.
2. Physical Data Independence – You can change how data is stored without
changing the table or app.

Example: You move a database from one hard drive to another — users still
access it the same way.

DBMS System Architecture

1. 1-tier architecture – User interacts directly with DBMS (rare).
2. 2-tier architecture – User (client) interacts with DB through an application.
3. 3-tier architecture – User interacts with app (frontend), which talks to a server,
which talks to the database.

Example: In a banking app:

• You see your account balance (Frontend)

• App processes it (Middle layer)
• Bank database gives your data (DB layer)

Database Administrator (DBA)

A DBA is a person responsible for managing the database system.
Key Roles:
• Install and configure DBMS software
• Create and manage user accounts
• Control access and permissions
• Take backups and restore data
 • Monitor performance and fix issues

Example: Like a school admin who keeps all student records safe and updated.

Types of Users in DBMS

There are different users who interact with the database in different ways:

1. Naive User (End User)

• Non-technical users who use a ready-made application.

• They don't write queries or interact with the database directly.

Example: A person using an ATM — just enters a PIN and withdraws money.
Behind the scenes, the app connects to the database.

2. Application Programmer

• Writes code to connect apps with the database using APIs or SQL.
• Handles the logic of forms, login systems, etc.

Example: A developer building the online shopping cart system in an e-commerce

site.

3. Sophisticated User

• Technical users who directly interact with the DB using SQL queries.
• They know how the database works internally.

Example: A data analyst writing SQL queries to get reports from a sales database.

4. Specialized User

• Works on complex applications like AI, ML, data mining, etc.

• Uses advanced tools and may not use standard queries.

Example: A scientist running a bioinformatics system storing genetic data.

5. System Analyst

• Understands what the users need and designs the system structure
accordingly.
 Example: Plans a university’s result management system (how students, marks,
and results are stored).

6. Database Designer

• Designs the database schema, tables, keys, relationships, etc.

• Creates the ER Diagram.

Example: Decides that a Student table should have RollNo, Name, Age, etc., and
links it with Course.

7. Database Administrator (DBA)

• Manages the whole database system.

• Controls users, permissions, backup, recovery, and performance.

Example: Keeps the hospital database secure, updated, and running smoothly.

What is File and DBMS sytem:

File System

• A file system is a way of storing and organizing data in files on a computer.

• Each file holds data, and you need to write your own program to
read/write/update the files.

Example:

• You save student names in a text file like students.txt.

• If you want to search for a student or update marks, you manually open and
edit the file or write a program to do it.
Problems with File System:
• Data can be repeated (redundancy)
• Hard to search, update, and maintain
• No security or backup
• No easy way to manage multiple users

Database System (DBMS)

• A DBMS is software that lets you store, manage, and retrieve data easily using
queries (like SQL).
• Data is stored in tables, and the DBMS handles searching, updating, security,
and more.
 Example:

• You store student info in a table inside a database.

• You can easily use a command like:
• SELECT * FROM students WHERE roll_no = 5;
Advantages of DBMS:
• Less redundancy
• Fast searching and updating
• Data security and backup
• Multiple users can work at the same time

Main Difference:

Feature File System Database System (DBMS)

Data Storage In separate files In organized tables

Data Redundancy High Low

Security Not secure Very secure

Data Handling Manual/Custom code Automatic via SQL

Backup/Recovery Manual Built-in

Multi-user Access Difficult Easy

Module 2 - The Entity–Relationship (ER) Model

What is ER Model?

• A visual way to design a database using diagrams.

• It shows entities (things), their attributes (properties), and relationships
(connections).

Example: In a school database:

• Entity: Student, Teacher

• Relationship: Teaches

Entity

• Any real-world object with data stored about it in the database.

Example:
Student, Teacher, Book

Entity Set

• A group of similar entities.

Example:
All students in a college form the Student entity set.

Types of Entities

Strong Entity Set

• Has a primary key.

• Can exist independently.

Example:
Student(RollNo, Name, Age) → RollNo is a key → strong entity

Weak Entity Set

• No primary key.
• Exists only with a strong entity.
• Identified using a foreign key + discriminator.
 Example:
Dependent(Name, Age) depends on Employee(EmpID)
→ EmpID + Name used to identify Dependent

Types of Attributes

1. Composite: Can be divided

➤ Name → First Name + Last Name

2. Derived: Calculated from other values

➤ Age from DOB

3. Multivalued: Has multiple values

➤ Phone Numbers, Skills

4. Key: To identify uniquely

➤ Roll No.

Types of Keys

1. Primary Key
➤ Unique identifier for each entity
Example: Roll No in Student

2. Candidate Key
➤ All possible keys that can be primary
Example: Both Roll No and Email can uniquely identify a student

3. Super Key
➤ Any combination that uniquely identifies
Example: RollNo, RollNo + Name

4. Foreign Key
➤ Refers to the key of another table
Example: Student.DepartmentID refers to Department.ID

5. Composite Key
➤ Key made of more than one attribute
Example: CourseID + StudentID in Enrollment table

Relationship Constraints

Cardinality (Mapping Constraints)

Type Meaning Example

1:1 One entity ↔ One entity One person has one passport
Type Meaning Example

1:N One ↔ Many One teacher teaches many students

M:N Many ↔ Many Many students enroll in many courses

M:1 Many ↔ One Many students belong to one department

Quick Tip to Remember:

• 1:1 → Unique match both sides

• 1:N → One controls many
• M:N → Both sides have multiple connections
• M:1 → Many pointing to one (common in foreign key relationships)

Participation Constraints

1. Total Participation:
➤ Entity must participate (double line)
Example: Every Employee must have an ID Card.

2. Partial Participation:
➤ Entity may or may not participate (single line)
Example: Not all Employees are Project Managers.

Extended ER (EER) Model

EER adds advanced concepts like inheritance and complex relationships.

Generalization

• Combining multiple lower-level entities into one higher-level entity.

Example:
Car, Bike → become Vehicle

Specialization

• Dividing a high-level entity into multiple sub-entities.

Example:
Employee → Manager, Developer

Aggregation
 • When a relationship itself becomes an entity, and can participate in another
relationship.
• Used to represent a relationship between a relationship and an entity

Example:
Employee works on Project → the relationship "works on" becomes an entity and is
related to Client.

Attribute Inheritance

• In specialization/generalization, the subclasses inherit attributes from the

superclass.

Example:
Employee(EmpID, Name)
→ Manager inherits EmpID, Name, and may add Department
So subclasses have both inherited and new attributes.

Cardinality Constraints

• Tells how many instances of an entity can be related to instances of another.

Type Meaning Example

1:1 One-to-One One person ↔ One passport

1:N One-to-Many One teacher ↔ Many students

M:N Many-to-Many Many students ↔ Many courses

M:1 Many-to-One Many students → One department

Participation Constraints

Defines whether all entities must take part in a relationship.

Type Symbol Meaning Example

Total Double All entities must Every Student must enroll in a

Participation Line participate Course

Partial Single Some entities may Some Employees may manage

Participation Line participate Projects

Integrity Constraints

They are rules to make sure the database has correct and consistent data.
 Types of Integrity Constraints:

Type Meaning Example

Domain Values must come from a defined

Age must be between 1–120
Constraint domain/type

Each entity must have a unique Each student must have a unique
Key Constraint
identity RollNo

Every employee must have a non-

Entity Integrity Primary key cannot be NULL
null EmpID

Referential Foreign key must match an Student.DeptID must exist in the

Integrity existing value or be NULL Department table

1. Relationship
• A relationship shows how two or more entities are connected.

Example:
Student enrolls in Course
Here, "enrolls in" is the relationship between two entities.

• In an ER diagram, relationships are shown as diamonds

2. Recursive Relationship (Unary Relationship)

• When an entity has a relationship with itself.

Example:
In a company:
• Employee manages Employee
→ One employee (Manager) manages another employee
• In ER Diagram: One entity connected to itself through a relationship.

3. Degree of a Relationship

• The number of entity sets that participate in a relationship.

Degree Meaning Example

Unary (1) One entity involved Employee → manages → Employee

Binary (2) Two entities involved Student → enrolled in → Course

Ternary (3) Three entities involved Doctor → prescribes → Medicine → to Patient

Module 3 – Relational Model & Relational Algebra

1. Relational Model

• A database model where data is organized in tables (called relations).

• Each table has:
o Rows → called tuples
o Columns → called attributes

Example:

RollNo Name Age

1 Raj 20

2 Neha 21

Relational Schema

• Describes the structure of a table.

• Includes table name, attribute names, and types.

Example:
Student(RollNo INT, Name VARCHAR, Age INT)

2. Keys in Relational Model

Key Type Meaning Example

Primary Key Unique for every row RollNo in Student table

Candidate
Multiple options to be primary key RollNo, Email
Key

Any combo that uniquely identifies

Super Key (RollNo, Name)
a row

Points to primary key of another DeptID in Student → Department

Foreign Key
table table

3. Mapping ER/EER to Relational Model

ER Concept Relational Model

Entity Table

Attribute Column
ER Concept Relational Model

Key Primary Key

1:N Relationship Add Foreign Key to N-side table

M:N Relationship Create new table with two foreign keys

Generalization One table with type attribute or separate tables

Aggregation Make the relationship a new table

Example:
Employee manages Department → Add DeptID (FK) in Employee table

Relational Algebra Operators – Detailed by Type

1) Unary Operations

These operators work on a single relation.

a) Selection (σ)

• Used to filter rows that match a condition

• Only rows satisfying the condition will be returned
• Doesn’t change the number of columns

Syntax:

σ condition (Relation)

Example:

σ Age > 18 (Student)

→ Returns only students older than 18

b) Projection (π)

• Used to choose specific columns

• Removes duplicate rows automatically
• Doesn’t change the number of rows unless there are duplicates

Syntax:

π column1, column2 (Relation)

Example:
π Name, Age (Student)
→ Returns only the Name and Age columns

c) Rename (ρ)

• Used to rename a relation or attributes

• Helpful when using the same table multiple times (joins or nested queries)

Syntax:

ρ(NewName ← Relation)

Example:

ρ(S ← Student)
→ Renames Student table to S

2) Set Theory Operations

These operators work on two relations with same structure (same number and
type of columns)

a) Union ( ∪ )

• Combines all unique tuples from both relations

• Removes duplicates automatically

Syntax:

Relation1 ∪ Relation2

Example:

π Name (Student) ∪ π Name (Alumni)

→ Returns all distinct names from both Student and Alumni

b) Difference ( – )

• Returns tuples that exist in first relation only, not in second

Syntax:

Relation1 – Relation2

Example:

Student – Passed
→ Students who did not pass

c) Intersection ( ∩ )

• Returns only the common tuples in both relations

Syntax:

Relation1 ∩ Relation2

Example:

Student ∩ TopperList
→ Students who are also in the topper list

3) Binary Operations

These involve two relations, often with different schemas.

a) Join (⨝)

• Combines tuples from two relations based on a condition

Types of Join:

Join Type Description

Theta Join Combines rows based on any condition (>, <, =, etc.)

Inner (Equi)
Combines rows where attributes are equal (= only)
Join

Natural Join Joins using all common attributes and removes duplicate columns

Returns all rows from left table, matched + unmatched (NULLs) from
Left Outer Join
right

Right Outer Returns all rows from right table, matched + unmatched (NULLs)
Join from left

Full Outer Join Returns all rows from both tables; fills unmatched with NULLs

b) Cartesian Product ( × )

• Combines every row of the first relation with every row of the second
• Used in joins before applying a filter condition
 Syntax:

Relation1 × Relation2

Example:

Student × Course
→ Every student paired with every course

c) Division ( ÷ )

• Used when we need tuples from one relation that are related to all tuples in
another relation
• Often used for “for all” type queries

Example:
Let:
• A(Student, Course)
• B(Course)
A÷B
→ Find students who are enrolled in all courses listed in B

Real-life Example:
"Which students are enrolled in all mandatory courses?"

Summary Table

Category Operator Symbol Purpose

Unary Selection σ Filter rows by condition

Unary Projection π Select specific columns

Unary Rename ρ Rename table or attributes

Set Theory Union ∪ Combine rows from both relations

Set Theory Difference – Rows in one but not in another

Set Theory Intersection ∩ Common rows in both relations

Binary Join ⨝ Combine related rows using a condition

Binary Cartesian Product × All combinations of rows

Binary Division ÷ “For all” queries

Module 4: Structured Query Language (SQL)
4.1 Overview of SQL
• SQL (Structured Query Language) is used to interact with databases: create,
modify, query, and control access to data.

1) Data Definition Language (DDL):

DDL (Data Definition Language) is a part of SQL used to define and manage the
structure of database objects like tables, schemas, views, and indexes.
It deals with the creation, modification, and deletion of database structures — not the
data itself.

Command Description
CREATE - Creates a new table, database, or other object
ALTER - Modifies structure of an existing table (add/drop column, change datatype)
MODIFY - Changes the datatype or size of an existing column
DROP - Deletes a table, database, or object permanently
RENAME - Changes the name of a table or column
TRUNCATE - Removes all data from a table, but keeps its structure

2) DML (Data Manipulation Language):

DML (Data Manipulation Language) is a part of SQL used to manage and manipulate
data stored in database tables.
It allows you to insert new data, retrieve existing data, update existing data, or delete
data.

DML Commands

Command Description

INSERT Adds new records (rows) into a table

SELECT Retrieves data from one or more tables

UPDATE Modifies existing data in a table

DELETE Removes one or more rows from a table

3) DCL (Data Control Language):
DCL (Data Control Language) is used to control access to data in a database.
It deals with permissions — who can read, write, or modify the data or structure.

DCL Commands

Command Description

GRANT Gives permissions to users (e.g., SELECT, INSERT, UPDATE rights)

REVOKE Takes back permissions from users

4) TCL (Transaction Control Language):

TCL (Transaction Control Language) is used to manage transactions in a database —
a transaction is a sequence of operations performed as a single logical unit of work.
It helps maintain data integrity by allowing operations to be committed (saved) or
rolled back (undone).

TCL Commands

Command Description

COMMIT Saves all changes made in the current transaction permanently

ROLLBACK Undoes changes made in the current transaction

SAVEPOINT Sets a point to which you can rollback later

SET TRANSACTION Sets properties for a transaction (e.g., isolation level)

READ / WRITE Access modes in some DBMSs to allow only read or write ops

# What are Privileges in SQL?

Privileges are permissions given to users to perform specific actions on database
objects (like tables, views, etc.).
They are controlled using DCL commands like GRANT and REVOKE.
 Aggregate Functions in SQL

Aggregate functions perform calculations on a set of values and return a single result
— commonly used with GROUP BY.

List of Aggregate Functions

Function Description

COUNT(*) Counts all rows, including duplicates and NULLs

COUNT(DISTINCT col) Counts unique non-null values in a column

COUNT(ALL col) Counts all non-null values (default behavior)

SUM(column) Returns the total sum of numeric values

AVG(column) Calculates the average of numeric values

MIN(column) Returns the smallest value

MAX(column) Returns the largest value

1. WHERE Clause

• Used to filter individual rows from a table before any grouping or

aggregation is done.

• Cannot be used with aggregate functions (SUM(), COUNT(), etc.)

2. GROUP BY Clause

• Used to group rows that have the same value in one or more columns.

• Often used with aggregate functions like SUM, COUNT, AVG, etc.

3. HAVING Clause

• Used to filter groups created by GROUP BY.

• Can be used with aggregate functions.

• It’s like WHERE, but for groups instead of individual rows.

 Summary Table

Clause Used For When It Applies Supports Aggregates?

WHERE Filter rows Before grouping No

GROUP BY Group rows Before aggregate functions Yes

HAVING Filter groups After aggregation Yes

# What is a Trigger in SQL?

A trigger is a special kind of stored procedure that automatically runs (fires) when a
certain event happens in a table — like INSERT, UPDATE, or DELETE.

Think of it like an "automatic response" to changes in the table.

Why use Triggers?

• To enforce business rules

• To automatically log changes
• To validate data before or after changes

Types of Triggers

Type Description

BEFORE Trigger Executes before the triggering action

AFTER Trigger Executes after the triggering action

INSTEAD OF Replaces the triggering action (used with views)

ROW-level Executes once for each row affected

STATEMENT-level Executes once per SQL statement

Module 5: Relational Database Design
1. Pitfalls in Relational Design
In a poor database design, problems like data redundancy, update anomalies,
insertion anomalies, and deletion anomalies occur. These issues affect data
consistency and increase storage space.

To avoid these, normalization is used.

2. Functional Dependency (FD)

A functional dependency occurs when one attribute uniquely determines another.

If A → B, then knowing A helps you find B.

Example: If StudentID → Name, then one student ID has one specific name.

3. Normalization

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

and avoid anomalies. It involves applying rules called normal forms.

4. First Normal Form (1NF)

A table is in 1NF if all columns have atomic values (no multiple values in a single
column).
Ensures the table has a proper structure.

5. Second Normal Form (2NF)

A table is in 2NF if:

• It is in 1NF
• And no non-prime attribute is partially dependent on a composite key.

Removes partial dependency.

6. Third Normal Form (3NF)

A table is in 3NF if:

• It is in 2NF
• And all non-key attributes are only dependent on the primary key, not on other
non-key attributes.

Removes transitive dependency.

 7. Boyce-Codd Normal Form (BCNF)

BCNF is a stronger version of 3NF.

A table is in BCNF if for every functional dependency A → B, A is a super key.

Used when 3NF still allows anomalies.

4 NF

It’s remove multiple values dependency

5 NF

It’s remove complex join dependency

Module 6: Transaction Management, Concurrency and Recovery

1. Transaction Concept

A transaction is a group of one or more SQL operations (like INSERT, UPDATE, etc.)
that are treated as a single unit.
Example: Transferring money from one bank account to another (debit + credit)

2. Transaction States

State Meaning

Active Transaction is running

Partially Committed All operations completed, waiting to be saved

Committed Changes are saved permanently

Failed Error occurred during transaction

Aborted Changes are rolled back

3. ACID Properties

ACID is a set of four properties that ensure reliability and integrity of transactions in a
database system.

1. Atomicity – "All or Nothing"

• Ensures that either all operations of a transaction are completed, or none are.
• If any one part fails, the entire transaction is rolled back.

Example:
Transferring ₹1000 from A to B
• Debit ₹1000 from A
• Credit ₹1000 to B
If credit fails, debit is also canceled.

If even one step fails → everything is undone → no partial transaction happens.

2. Consistency – "Valid to Valid State"

• A transaction should maintain the rules and constraints of the database.

• Database must move from one consistent state to another.
 Example:
A student record should not allow age = -5.
Even after insert/update, the rule is enforced → DB remains consistent.

If a constraint is violated → transaction is rejected → data stays valid.

3. Isolation – "Transactions run independently"

• Each transaction should act as if it's the only one running.

• No interference from other transactions while it's executing.

Example:
Two users booking the last movie ticket at the same time.
Only one should succeed → handled by isolation.

Prevents dirty reads, lost updates, etc.

4. Durability – "Changes are Permanent"

• Once a transaction is committed, the changes must survive system crashes.

• Data is saved in logs or stable storage.

Example:
If a bank transaction is successful and the system crashes after that,
the changes (money transfer) should still exist when the system restarts.

Commit = Permanent — crash won’t undo it.

Quick Tip to Remember ACID:

• All or none (Atomicity)

• Correct rules (Consistency)
• Independent (Isolation)
• Doesn't vanish (Durability)

4. Transaction Control Commands (TCL)

Command Use

BEGIN Starts a transaction

COMMIT Saves changes permanently

ROLLBACK Undoes changes since the last commit

Command Use

SAVEPOINT Sets a checkpoint to roll back to

5. Concurrent Execution

Multiple transactions running at the same time is called concurrent execution.

Goal: Improve performance without causing data errors.

6. Serializability

It checks if the outcome of concurrent transactions is the same as if they ran one by
one.
Types:
• Conflict Serializability: Based on order of conflicting operations.
• View Serializability: Based on how data is viewed/used.

7. Concurrency Control Protocols

Used to control access to data when multiple transactions run at the same time.

Protocol Description

Lock-based Uses locks (Shared or Exclusive) to control access

Timestamp-based Each transaction has a timestamp; order decided by timestamps

8. Recovery System

Used to restore the database to a consistent state after a crash.

• Log-based Recovery: Stores actions in a log file. After crash → redo or undo.

✔ UNDO if transaction failed

✔ REDO if transaction was committed

9. Deadlock

A deadlock happens when two or more transactions wait for each other forever — and
none of them can continue.
A deadlock occurs when two or more transactions wait for each other forever
 Conditions for Deadlock (when it can happen)

1. Mutual Exclusion: One resource used by one transaction at a time

2. Hold and Wait: Holding one, waiting for another
3. No Preemption: Can’t force a transaction to release its lock
4. Circular Wait: T1 waits for T2, T2 waits for T3... and last waits for T1
If all 4 happen → deadlock occurs

Deadlock handling methods:

• Deadlock Prevention: Avoid situations that lead to deadlock

• Deadlock Detection: Detect and resolve if deadlock happens
• Deadlock Recovery: Abort and restart one or more transactions

Deadlock Handling (How to deal with it)

1. Prevention

• Don’t let all 4 conditions happen together

• Example: Don’t allow hold and wait

2. Detection and Recovery

• Let deadlock happen → then detect it

• Use wait-for graph to find cycles
• Break the cycle by aborting one transaction

3. Avoidance

• Use safe sequence

• Allow only those transactions which don’t lead to deadlock

Easy way to remember:

"Deadlock = stuck forever because everyone is waiting."

"Made by Megh. If you have any doubts about whether this is worth it or not, or if this
is the perfect answer, I highly recommend you go and find the answer by yourself."