MCS 023 Solved Guess Paper 2026: Introduction to Database Management Systems for IGNOU BCA and MCA

Three-level architecture and data independence

Question: Describe the three levels of DBMS architecture with a neat diagram. Explain the concept of data independence.

The logical architecture describes how data in the database is perceived by users. It is also called the ANSI/SPARC model.

The three levels

1. External or View Level: The highest level. Each user sees only the data of interest to him. Many external views can exist, and they can overlap.
2. Conceptual or Global Level: Shows the whole database as one unified whole. There is only one conceptual schema per database. It gives content, not storage details.
3. Internal or Physical Level: Closest to physical storage. It defines record types, storage methods and the physical order of stored records.

Mappings and data independence

1. Physical data independence: This comes from the conceptual/internal mapping. If the stored structure changes, only this mapping changes and the conceptual view stays the same. Example: the student file is broken into two files.
2. Logical data independence: This comes from the external/conceptual mapping. If the conceptual structure changes, user views stay the same. Example: new fields are added to the student relation.

Why three levels are needed

1. Each user gets his own view of the same data.
2. Users never deal with indexing or hashing.
3. The DBA can change storage structures without affecting users.

Conclusion: The three levels separate user views from physical storage. The two mappings give physical and logical data independence.

Relational algebra set operations

Question: Determine the output when these operators are applied on R1(A, B) = {(x,1), (x,2), (y,1)} and R2(A, B) = {(x,2), (y,3)}: (i) Union (ii) Intersection (iii) Difference R1 − R2 (iv) Cartesian Product.

Relational algebra is a set of basic operations used to manipulate data in the relational model. Union, intersection and difference need union-compatible relations, meaning the same degree and the same domains. R1 and R2 both have attributes A and B, so they are union compatible.

(i) Union (R1 ∪ R2)

Tuples in R1, in R2, or in both. Duplicates appear once.

(ii) Intersection (R1 ∩ R2)

Tuples present in both relations. Result: (x, 2).

(iii) Difference (R1 − R2)

Tuples in R1 but not in R2. Result: (x, 1) and (y, 1).

(iv) Cartesian Product (R1 × R2)

Every R1 tuple is paired with every R2 tuple. Degree = 2 + 2 = 4. Tuples = 3 × 2 = 6. Union compatibility is not needed.

Points to remember: Union and intersection are commutative and associative. Difference is neither, so R1 − R2 is not equal to R2 − R1.

Conclusion: Union gives 4 tuples, intersection 1, difference 2, and the Cartesian product gives 6 tuples of degree 4.

The need for normalisation: 1NF, 2NF and 3NF

Question: What is the need of normalisation? Explain 1NF, 2NF and 3NF with suitable examples. Also discuss insert, delete and update anomalies.

Normalisation decomposes a relation into smaller relations, based on functional dependence, to remove undesirable anomalies. Codd presented 1NF, 2NF and 3NF in 1972.

The need: three anomalies

Take STUDENT (Enrolmentno, Sname, Address, Cno, Cname, Instructor, Office) with key (Enrolmentno + Cno).

1. Update anomaly: Changing the instructor of one course needs many tuples updated. Miss one and the data becomes inconsistent.
2. Insertion anomaly: A new course cannot be added until a student enrols in it.
3. Deletion anomaly: Deleting a student's only tuple also loses the course and instructor details.

Functional dependencies: Enrolmentno → Sname, Address; Cno → Cname, Instructor; Instructor → Office.

The normal forms

1. 1NF: No duplicate tuples, single-valued attributes, one type per column. STUDENT is in 1NF but still has every anomaly.
2. 2NF: In 1NF, and every non-key attribute depends on the whole candidate key. Sname depends only on Enrolmentno, so the partial dependencies are split out.
3. 3NF: In 2NF, with no transitive dependency. Office depends on Instructor, not directly on Cno, so we split again.

Conclusion: Normalisation removes redundancy and the three anomalies. 3NF is usually enough for most database designs.

SQL queries on a table

Question: Consider STUDENT (Name, Class, Gender, Marks) with tuples: Nanda 10 M 551; Sonal 11 F 462; Trisha 12 F 400; Shruti 12 F 450; Nishant 10 M 367; Somya 11 F 250. Write SQL queries to: (i) display name and class of students with marks between 450 and 551 (ii) display records in alphabetical order of name (iii) find the highest marks (iv) find class-wise highest marks (v) find the number of students in each class.

DML is the set of commands used to query and modify data within existing schema objects. SELECT retrieves data and can use BETWEEN, ORDER BY, aggregate functions and GROUP BY.

-- (i) BETWEEN includes both limits. Output: Nanda 10, Sonal 11, Shruti 12
SELECT Name, Class FROM STUDENT WHERE Marks BETWEEN 450 AND 551;

-- (ii) Ascending by default: Nanda, Nishant, Shruti, Somya, Sonal, Trisha
SELECT * FROM STUDENT ORDER BY Name;

-- (iii) Output: 551
SELECT MAX(Marks) FROM STUDENT;

-- (iv)
SELECT Class, MAX(Marks) FROM STUDENT GROUP BY Class;

-- (v)
SELECT Class, COUNT(*) FROM STUDENT GROUP BY Class;

Conclusion: WHERE filters rows, ORDER BY sorts them, and GROUP BY with aggregates gives group-wise results. With GROUP BY, only grouped columns and aggregate functions can appear in SELECT.

Serialisability and the precedence graph

Question: Differentiate between serial and serialisable schedules. Given the schedule T1: R(A), W(A); T2: R(A), W(A); T1: R(B), W(B); T2: R(B), W(B), determine whether it is serialisable using a precedence graph.

A schedule of n transactions is serialisable if it is equivalent to some serial schedule of the same n transactions.

Conflicting operations: Two operations of different transactions conflict if they access the same data item and at least one is a write.

Steps to build the precedence graph

1. Create a node for every transaction.
2. Draw Ti → Tj if Ti reads A and Tj writes A later, Ti writes A and Tj reads A later, or Ti writes A and Tj writes A later.
3. If there is a cycle, the schedule is not serialisable. Otherwise, start from the node with no incoming edge to get the serial order.

Solution: The conflicts are T1 W(A) before T2 R(A), T1 R(A) before T2 W(A), T1 W(A) before T2 W(A), and the same three pairs on B. Every one gives the edge T1 → T2. There is no edge from T2 to T1, so there is no cycle.

Conclusion: No cycle in the precedence graph, so the schedule is serialisable and equals the serial order T1 → T2.