DBMS 4-Mark Questions and Answers

1. Define DBMS. What are its advantages?

A Database Management System (DBMS) is software that allows users to store, manage,
retrieve, and organize data easily. It provides a systematic and efficient way of handling
large amounts of data.

Advantages of DBMS:
- Reduces redundancy: Avoids repeating the same data.
- Ensures data consistency: Same data is updated everywhere.
- Provides data security: Only authorized users can access or modify data.
- Supports multi-user access: Many users can access data at the same time without errors.
- Backup and recovery: Automatic backup options protect data from loss.

2. Explain the architecture of DBMS (1-tier, 2-tier, 3-tier).

DBMS architecture defines how the system is structured and how users interact with the
database.

- 1-tier Architecture: User interacts directly with the database. Mostly used in standalone
applications or for developers.
- 2-tier Architecture: Application (client) is separate from the database (server). The client
sends SQL queries to the server and receives results.
- 3-tier Architecture: Divided into:
- Presentation Layer (Client) – UI for the user
- Application Layer – Business logic (middleware)
- Database Layer – Actual database storage
This is used in web applications for better security and scalability.

3. What are different types of data models in DBMS?

A data model is a way of organizing and representing data.

Types:
1. Hierarchical Model: Data is organized like a tree with parent-child relationships.
2. Network Model: More flexible than hierarchical; each record can have multiple parents
and children.
3. Relational Model: Data is stored in tables (relations) with rows and columns. Most
commonly used.
4. Object-Oriented Model: Data is stored in the form of objects, similar to programming
languages like Java or C++.
4. What is the E-R Model? Explain its components with a diagram.
The Entity-Relationship (E-R) model is a diagrammatic way of representing real-world data.

Components:
- Entity: An object or thing (e.g., Student, Course)
- Attributes: Properties of entities (e.g., Name, Age)
- Relationships: Connections between entities (e.g., A student enrolls in a course)

Example Diagram:
[Student] —enrolls in—> [Course]
Attributes: RollNo, Name Attributes: CourseID, Title

5. What is normalization? Explain 1NF, 2NF, and 3NF.

Normalization is the process of organizing data to reduce redundancy and improve
integrity.

- 1NF (First Normal Form): All values in the table are atomic (no repeating groups or
arrays).
- 2NF (Second Normal Form): Table is in 1NF and all non-key attributes are fully dependent
on the primary key.
- 3NF (Third Normal Form): Table is in 2NF and there are no transitive dependencies (non-
key attributes depending on other non-key attributes).

6. Explain different types of JOINs in SQL with examples.

JOIN is used to combine rows from two or more tables based on a related column.

- INNER JOIN: Returns only the matching rows from both tables.
- LEFT JOIN: Returns all rows from the left table, and matched rows from the right table.
- RIGHT JOIN: Returns all rows from the right table, and matched from the left.
- FULL JOIN: Returns all rows when there is a match in one of the tables.

Example:
SELECT Students.Name, Courses.Title
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.ID;

7. What is a transaction? Explain ACID properties.

A transaction is a set of SQL operations that perform a single logical task. For example:
transferring money between two bank accounts.

ACID Properties:
- A – Atomicity: All operations of a transaction are completed, or none at all.
- C – Consistency: Data remains in a valid state before and after the transaction.
- I – Isolation: Multiple transactions can occur independently without affecting each other.
- D – Durability: Once completed, the transaction’s changes are permanent.

8. Differentiate between Primary Key and Foreign Key.

Primary Key vs Foreign Key:

Primary Key:
- Uniquely identifies each record
- Cannot be null
- Defined in the main table

Foreign Key:
- Refers to a primary key in another table
- Can be null
- Used to create relationships between tables

9. What are DDL, DML, DCL, and TCL commands in SQL?

- DDL (Data Definition Language): Defines database structure
- e.g., CREATE, ALTER, DROP
- DML (Data Manipulation Language): Manages data inside tables
- e.g., SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language): Controls access to data
- e.g., GRANT, REVOKE
- TCL (Transaction Control Language): Manages transactions
- e.g., COMMIT, ROLLBACK, SAVEPOINT

10. Write SQL queries for SELECT, INSERT, UPDATE, and DELETE.
SQL Queries:

-- SELECT
SELECT * FROM Students;

-- INSERT
INSERT INTO Students (RollNo, Name) VALUES (101, 'Raj');

-- UPDATE
UPDATE Students SET Name = 'Ravi' WHERE RollNo = 101;

-- DELETE
DELETE FROM Students WHERE RollNo = 101;

11. What are Integrity Constraints in DBMS?

Integrity constraints are rules applied on database columns to ensure the accuracy and
consistency of data.
Types:
- NOT NULL: Ensures a column cannot have a NULL value.
- UNIQUE: Ensures all values in a column are different.
- PRIMARY KEY: Uniquely identifies each record.
- FOREIGN KEY: Maintains referential integrity between tables.
- CHECK: Ensures values meet a specific condition.

12. What is a View in SQL? How is it created?

A view is a virtual table based on the result of a SELECT query. It does not store data itself
but displays data from one or more tables.

To create a view:
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

13. What is Indexing? What are its types?

Indexing is used to speed up the retrieval of data from a table.

Types of Indexes:
- Single-level index
- Multi-level index
- Clustered index: Data is stored in the same order as the index.
- Non-clustered index: Data is stored separately from the index.

14. What are Triggers in SQL? Give an example.

A trigger is a set of SQL instructions that automatically runs when a specific event occurs in
the database (INSERT, UPDATE, DELETE).

Example:
CREATE TRIGGER trg_before_insert
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
SET NEW.Name = UPPER(NEW.Name);
END;

15. What is the difference between DELETE, TRUNCATE, and DROP?

- DELETE: Removes specific rows. Can be rolled back.
- TRUNCATE: Removes all rows quickly. Cannot be rolled back.
- DROP: Deletes the entire table structure from the database.
16. Explain basic Relational Algebra operations.
Relational Algebra is a theoretical language used for database queries.

Basic operations:
- SELECT (σ): Filters rows
- PROJECT (π): Selects columns
- UNION (∪): Combines results of two relations
- SET DIFFERENCE (−): Returns tuples in one relation but not in the other
- CARTESIAN PRODUCT (×): Combines every row of one table with every row of another

17. What is Functional Dependency? Why is it important?

Functional dependency means that one attribute (column) uniquely determines another.

Example: Roll_No → Name

If two rows have the same Roll_No, they must have the same Name.

Importance:
- Helps in designing good database schemas
- Used in normalization to reduce redundancy

18. What is a Deadlock in DBMS? How is it handled?

A deadlock occurs when two or more transactions are waiting for each other to release
locks, causing a standstill.

Handling methods:
- Deadlock prevention (e.g., lock ordering)
- Deadlock detection (detect cycle in wait-for graph)
- Deadlock recovery (abort one transaction)

19. What is a Cursor in SQL? When is it used?

A cursor is a database object used to retrieve row-by-row results from a result set.

Used when:
- Row-by-row operations are needed
- More control over fetching and updating rows is required

20. What is a Subquery in SQL? Give an example.

A subquery is a query inside another query. It is used to return data to the main query.

Example:
SELECT Name FROM Students
WHERE RollNo IN (SELECT RollNo FROM Marks WHERE Marks > 80);