Bihar STET Computer Science DBMS Notes & MCQs 2026

Part 1: High-Yield Revision Notes (Quick Theory)

1. Database System Concepts & Architecture

• DBMS vs. File System: DBMS reduces data redundancy, eliminates data inconsistency, provides data isolation, ensures security, and supports concurrent access and ACID properties.

• Three-Schema Architecture (ANSI-SPARC):

  • External Level (View Level): Highest level; defines how end-users view the data.

  • Conceptual Level (Logical Level): Defines what data is stored in the database and the relationships among them (e.g., tables, constraints).

  • Internal Level (Physical Level): Lowest level; describes how data is physically stored on disk (e.g., file structures, indexes).

• Data Independence:

  • Logical Data Independence: Ability to modify the conceptual schema without altering external schemas or application programs.

  • Physical Data Independence: Ability to modify the physical schema (e.g., adding indexes) without altering the conceptual schema.

2. Relational Data Model & Keys

• Relational Terminology:

  • Relation: Table

  • Tuple: Row / Record

  • Attribute: Column / Field

  • Cardinality: Total number of tuples (rows) in a relation.

  • Degree: Total number of attributes (columns) in a relation.

• Database Keys:

  • Super Key: A set of one or more attributes that uniquely identifies a tuple.

  • Candidate Key: A minimal Super Key (no redundant attributes).

  • Primary Key: A candidate key selected by the database designer to uniquely identify tuples (Cannot contain NULL values).

  • Alternate Key: Candidate keys that were not chosen as the Primary Key.

  • Foreign Key: An attribute in a relation that refers to the Primary Key of another relation (Enforces Referential Integrity).

3. Database Normalization

Normalization minimizes data redundancy and prevents update/insertion/deletion anomalies.

4. Transaction Processing & ACID Properties

A Transaction is a logical unit of database processing.

• ACID Properties:

  • Atomicity: All-or-nothing execution. Handled by the Transaction Manager / Recovery Manager using log files (COMMIT / ROLLBACK).

  • Consistency: Database moves from one valid state to another. Enforced by database constraints and application programmers.

  • Isolation: Concurrent transactions execute without interfering with each other. Managed by the Concurrency Control Manager (e.g., Locking protocols, Timestamping).

  • Durability: Changes made by committed transactions persist permanently in non-volatile memory even after system crashes. Handled by Recovery Manager.

5. SQL Commands Classification

                              SQL Commands
                                   |
    +-----------------+------------+------------+-----------------+
    |                 |                         |                 |
   DDL               DML                       DCL               TCL
(Data Definition)  (Data Manipulation)      (Data Control)    (Transaction Control)
- CREATE           - INSERT                 - GRANT           - COMMIT
- ALTER            - UPDATE                 - REVOKE          - ROLLBACK
- DROP             - DELETE                                   - SAVEPOINT
- TRUNCATE         - SELECT

Important Difference:

• DELETE is a DML command (Deletes specific rows, can be rolled back, fires triggers).

• TRUNCATE is a DDL command (Deletes all rows instantly, resets identity counter, cannot be rolled back in most systems, faster than DELETE).

• DROP is a DDL command (Removes table structure along with its data permanently).

Part 2: Top 35 Important MCQs (With Solutions)

1. Which level of the ANSI-SPARC architecture describes how data is physically stored in the storage device?

   • Answer: Internal / Physical Level

2. The number of tuples (rows) in a relation is known as its:

   • Answer: Cardinality

3. The number of attributes (columns) in a relation is known as its:

   • Answer: Degree

4. Which key uniquely identifies a row in a table and cannot contain NULL values?

   • Answer: Primary Key

5. A minimal super key is formally defined as a:

   • Answer: Candidate Key

6. The constraint that requires a Foreign Key value to match an existing Primary Key value in a referenced table is called:

   • Answer: Referential Integrity Constraint

7. In a relational model, an attribute that can be divided into sub-parts is called a:

   • Answer: Composite Attribute

8. Which normal form eliminates partial dependency?

   • Answer: Second Normal Form (2NF)

9. Transitive dependency is completely removed in which normal form?

   • Answer: Third Normal Form (3NF)

10. A relation is in BCNF if for every non-trivial functional dependency $X \rightarrow Y$:

    • Answer: $X$ is a Super Key

11. Which component of the ACID properties ensures that either all operations of a transaction execute or none do?

    • Answer: Atomicity

12. Which component of DBMS manages concurrent execution of transactions to ensure Isolation?

    • Answer: Concurrency Control Manager

13. Which SQL command category does TRUNCATE belong to?

    • Answer: DDL (Data Definition Language)

14. What is the main difference between DELETE and TRUNCATE commands?

    • Answer: DELETE is DML (can delete specific rows and be rolled back), whereas TRUNCATE is DDL (removes all rows and cannot be easily rolled back).

15. Which SQL clause is used to filter groups created by the GROUP BY clause?

    • Answer: HAVING clause (Note: WHERE filters rows before grouping, HAVING filters groups after grouping).

16. Which aggregate function in SQL ignores NULL values except when used as COUNT(*)?

    • Answer: COUNT(), SUM(), AVG(), MIN(), MAX() (all ignore NULL except COUNT(*))

17. Which key command is used to give permissions to database users?

    • Answer: GRANT (DCL Command)

18. Which keyword is used to remove duplicate values from a SQL SELECT query result?

    • Answer: DISTINCT

19. What type of JOIN returns all records when there is a match in either left or right table?

    • Answer: FULL OUTER JOIN

20. In an Entity-Relationship (ER) Diagram, a Weak Entity Set is represented by:

    • Answer: Double Rectangle

21. In an ER Diagram, a Multivalued Attribute is represented by a:

    • Answer: Double Ellipse

22. Which constraint prevents NULL values from being inserted into a column?

    • Answer: NOT NULL constraint

23. Which TCL command creates a point within a transaction to which you can later roll back?

    • Answer: SAVEPOINT

24. In Relational Algebra, which operator is used for the Projection operation (selecting specific columns)?

    • Answer: Pi ($\pi$) operator

25. In Relational Algebra, which operator is used for the Selection operation (filtering rows based on conditions)?

    • Answer: Sigma ($\sigma$) operator

26. Which locking protocol guarantees Serializability in transaction execution?

    • Answer: Two-Phase Locking (2PL) Protocol

27. A situation where two or more transactions are waiting indefinitely for each other to release locks is called:

    • Answer: Deadlock

28. Which normal form requires that all attributes contain atomic values only?

    • Answer: First Normal Form (1NF)

29. Which command is used to modify the structure of an existing database table (e.g., add a new column)?

    • Answer: ALTER TABLE

30. Which SQL statement is used to insert new rows into a database table?

    • Answer: INSERT INTO

31. In ER Modeling, a Relationship Set among three entity types is called a:

    • Answer: Ternary Relationship

32. In SQL, pattern matching using wildcard characters (% and _) is performed using which operator?

    • Answer: LIKE operator

33. What does the wildcard character _ (underscore) represent in a SQL LIKE condition?

    • Answer: Exactly one single character

34. What does the wildcard character % (percent) represent in a SQL LIKE condition?

    • Answer: Zero or more characters

35. The logical structure and organization of a database is called its:

    • Answer: Database Schema