MCS-207 — Database Management Systems (IGNOU)

MCS-207 — Database Management Systems (IGNOU)
 75 Highly Important Exam Questions with Detailed Explanations & Examples
Covers full syllabus (IGNOU Pattern)
Ideal for Last-Minute Revision + Long Answer Writing

UNIT-1: DBMS Concepts, Architecture, Data Models (Q1–15)

1. Define database. Why do we need it?

A database is a structured and organized collection of data stored electronically for easy access, retrieval, and modification. Unlike random files, a database allows consistent, secure and efficient handling of large datasets.

Example: Student database storing roll_no, name, course, marks.

Why needed?

• To avoid data redundancy

• To maintain data consistency

• To support multi-user access

• To provide security and backup

• To store structured, related information efficiently

2. What is DBMS? Explain its functions.

A Database Management System (DBMS) is software used to store, manage, retrieve, update, and secure data efficiently.

Examples: Oracle, MySQL, PostgreSQL, DB2, SQL Server

Functions of DBMS:

1. Data storage, retrieval and update

2. Data dictionary management

3. Transaction management

4. Concurrency control

5. Backup and recovery

6. Security and authorization

7. Data integrity enforcement

8. Support for query languages (SQL)

3. Compare File System vs DBMS.

4. What is Metadata? Why is it important?

Metadata means “data about data.”
It describes structure, constraints, table names, data types, indexes etc.

 Example entry:

✔ Used for query compilation, optimization, & integrity checks.

5. Explain database system components.

1. Hardware — storage devices, servers

2. Software — DBMS, application layer

3. Data — actual stored information

4. Users — DBA, developers, end users

5. Procedures — rules, standards for database use

6. What is a Data Model? Explain types.

A data model defines structure, constraints, and relationships of data.

Types:

1. Hierarchical model → tree-based

2. Network model → graph-based

3. Relational model → table-based (most widely used)

4. Object-oriented model → based on OOP concept

5. Entity-Relationship model → conceptual modeling

7. Explain Conceptual, Logical & Physical schema.

8. What is Data Independence? Types?

Ability to change schema at one level without modifying higher levels.

Logical independence is harder to achieve.

9. DBMS Users

• Database Administrator (DBA)

• Database Designers

• End users (casual, naïve, expert)

• Application programmers

10. What is DBA? Mention responsibilities.

A DBA (Database Administrator) manages the database environment.

Responsibilities:

• Storage & schema design

• User authorization

• Backup & recovery

• Performance tuning

• Integrity enforcement

• Security policies

11. What is DBMS Architecture?

DBMS commonly uses three-tier architecture:

1. External Level → user views

2. Conceptual Level → logical model

3. Internal Level → physical storage details

✔ Supports data independence.

12. Define Constraints. Types?

Rules to maintain data correctness and integrity.

Types:

13. Explain Relational Model with features.

Proposed by E. F. Codd, relational model stores data in tables (relations).

Features:

• Simple tabular structure

• SQL for querying

• Integrity constraints

• High flexibility

14. What is Database Instance?

The actual stored data at a particular moment is called an instance.
Schema ≠ instance
(Schema is stable; instance changes frequently.)

15. What are DBMS Advantages?

✔ Reduces redundancy
✔ Easy backup & recovery
✔ Enforces integrity
✔ Supports concurrency
✔ Provides data security
✔ Standardized access using SQL

1️⃣6️⃣ What are Transaction States? Explain with a diagram.

A transaction moves through multiple stages during execution.

State Diagram:

   Active → Partially Committed → Committed
      ↓             ↑
    Failed → Aborted

1️⃣7️⃣ What is Relational Algebra? Name its operations.

Relational Algebra is a procedural query language that works on relations (tables).

Basic Operations:

• Selection (σ) → filter rows

• Projection (π) → select specific columns

• Union (∪)

• Set Difference (−)

• Cartesian Product (×)

Advanced Operations:

• Join

• Division

Example:

σ(SALARY > 50000) (EMPLOYEE)

Retrieves all employees with salary > 50,000.

1️⃣8️⃣ Differences between Relational Algebra and Relational Calculus.

1️⃣9️⃣ What is a View? Explain its advantages.

A View is a virtual table based on a query.

CREATE VIEW high_salary AS
SELECT name, salary FROM employee WHERE salary > 60000;

Advantages:

• Security (hides sensitive columns)

• Simplifies complex queries

• Data abstraction

• Provides logical independence

2️⃣0️⃣ What is a Stored Procedure?

A stored procedure is a compiled SQL program stored in DBMS.

CREATE PROCEDURE getEmployee()
AS
SELECT * FROM employee;

Benefits:

• Faster execution

• Reusability

• Reduced network traffic

• Improved security

2️⃣1️⃣ What are Triggers? Give an example.

A trigger automatically executes when an event occurs (INSERT, UPDATE, DELETE).

CREATE TRIGGER check_salary
BEFORE INSERT ON employee
FOR EACH ROW
IF NEW.salary < 10000 THEN
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Salary too low';
END IF;

2️⃣2️⃣ Explain Data Warehouse and OLAP.

• A Data Warehouse stores large historical data for analysis and decision-making.

• OLAP (Online Analytical Processing) supports complex analytical queries.

2️⃣3️⃣ What is Functional Dependency?

A functional dependency exists when attribute B is functionally dependent on A.

A → B

Means A uniquely determines B.

Example:

Roll_No → Name, Address, Phone

2️⃣4️⃣ Explain BCNF with an example.

BCNF (Boyce-Codd Normal Form) is stricter than 3NF.

Rule:
For every functional dependency X → Y, X must be a super key.

Example (Not BCNF):

Teacher(ID, Subject, Room)
FD: Subject → Room (but Subject is not a key)

To normalize:

TEACHER(ID, Subject)
SUBJECT_ROOM(Subject, Room)

2️⃣5️⃣ What is Multivalued Dependency?

Occurs when one attribute determines multiple independent values.

Example:

Employee →→ Skill

Means employee can have multiple skills independent of other attributes.

2️⃣6️⃣ Explain 4NF with example.

A relation is in 4NF if:

• It is in BCNF

• No Multivalued Dependency exists except key

Example Table (Not 4NF):

Split into:

EMP_SKILL(Emp, Skill)
EMP_LANGUAGE(Emp, Language)

2️⃣7️⃣ What is a Cursor in SQL?

Cursor is used to process row-by-row results in SQL.

DECLARE cur CURSOR FOR SELECT name FROM student;
OPEN cur;
FETCH NEXT FROM cur;
CLOSE cur;

Used in stored procedures.

2️⃣8️⃣ Explain Hashing and its types.

Hashing maps keys to location using a hash function.

Used in hashing indexes.

2️⃣9️⃣ What is RAID? Explain RAID Levels.

RAID = Redundant Array of Independent Disks

Used for performance and fault tolerance.

3️⃣0️⃣ Explain Concurrency Control.

Concurrency control ensures correct results during simultaneous transactions.

Techniques:

• Locking (Shared, Exclusive)

• Timestamp ordering

• Multiversion concurrency control (MVCC)

3️⃣1️⃣ What is Locking? Explain types.

Locks prevent conflicts during concurrent access.

Multiple shared locks allowed, but exclusive lock allows only one user.

3️⃣2️⃣ What are Check Constraints with example?

Constraints ensure data validity.

CREATE TABLE employee(
salary INT CHECK(salary > 5000)
);

3️⃣3️⃣ What is Savepoint?

A savepoint temporarily marks the transaction to rollback partially.

SAVEPOINT sp1;
UPDATE employee SET salary = 10000;
ROLLBACK TO sp1;

3️⃣4️⃣ What is a Distributed Database?

A database stored across multiple locations but appears as a single system to users.

Benefits:

• Faster processing

• Reliability

• Scalability

3️⃣5️⃣ Explain Replication.

Replication means creating and maintaining copies of data across multiple locations.

Used in:

• Cloud systems

• Disaster recovery

• Data sharing across branches

Types:

• Snapshot replication

• Transactional replication

• Merge replication

3️⃣6️⃣ What is Metadata? Give examples.

Metadata is data about data. It describes structure, format, and meaning of stored data.

Example from DBMS catalog:

Table: STUDENT
Columns: Roll_No INT PRIMARY KEY, Name VARCHAR(50), Age INT

3️⃣7️⃣ What is a Weak Entity? How is it represented?

A weak entity depends on another entity for its existence and lacks a primary key.

Representation:

• Rectangle with double border

• Relationship diamond with double border

• Uses partial key

Example:

Room number alone is not unique across buildings.

3️⃣8️⃣ Explain Cardinality in ER Model.

Cardinality defines the number of occurrences in a relationship.

3️⃣9️⃣ What is Tuple, Attribute, Degree, and Cardinality?

4️⃣0️⃣ What is SQL Injection? How is it prevented?

SQL Injection is a security attack where unauthorized queries are inserted into input fields.

Example attack:

SELECT * FROM users WHERE username='' OR '1'='1';

Prevention:

• Use parameterized queries

• Input validation

• Stored procedures