GATE CSE DBMS: Securing Absolute Marks in SQL and Normalization

The Most Predictable Subject in GATE CSE

Database Management Systems (DBMS) consistently yields around 8 marks in the GATE Computer Science paper. Unlike some highly theoretical or mathematically complex subjects, DBMS questions follow established, mechanical rules. If you know the algorithms for normalization and the syntax of SQL/Relational Algebra, you will almost certainly arrive at the correct answer.

Here is an analysis of the core topics that dominate the GATE DBMS section.

Part 1: ER Model and Relational Model

This is the foundation. You must know how to translate a conceptual ER diagram into relational tables.

Key Concepts to Master:

• Entity and Attributes: Simple, composite, multi-valued, and derived attributes.
• Relationships: 1:1, 1:N, N:1, M:N.
• Total vs Partial Participation.
• Weak Entity Sets: Depend on a strong entity through an identifying relationship.

The Classic GATE Question: "What is the minimum number of tables required to represent this ER diagram?" Rules of Thumb:

1. Weak entity sets always require a separate table.
2. Multi-valued attributes always require a separate table.
3. For M:N relationships, a separate table mapping the primary keys of participating entities is strictly required.
4. For 1:N relationships, the relationship can be merged with the "N" side table. (Saves a table).

Part 2: Relational Algebra and Tuple Calculus

GATE tests your ability to query data using mathematical notations before writing SQL.

Relational Algebra Operators:

• Selection (σ): Filters rows.
• Projection (π): Filters columns. (Automatically removes duplicates unlike SQL SELECT).
• Cross Product (×): Cartesian product of two relations.
• Joins (⨝): Natural Join (equates identically named attributes and removes one copy), Outer Joins (Left, Right, Full).
• Set Operations: Union (∪), Intersection (∩), Set Difference (-). Relations must be union-compatible.

Division Operator (/): Crucial for queries containing the word "ALL". E.g., "Find the names of sailors who reserved all boats."

Tuple Relational Calculus (TRC): Understand the syntax { t | P(t) }. Know how universal (∀) and existential (∃) quantifiers map to relational algebra.

Part 3: SQL (Structured Query Language)

GATE SQL questions generally test complex nested queries, aggregations, and join behavior.

Focus Areas:

• GROUP BY and HAVING: 'WHERE' filters rows before grouping; 'HAVING' filters groups after aggregation. You cannot use aggregate functions (MAX, SUM) in a WHERE clause.
• Correlated Nested Queries: The inner query depends on values from the outer query. These execute once for every row of the outer query.
• EXISTS / NOT EXISTS: Often used to simulate the relational algebra division operation (finding subsets/suparsets).
• NULL Values: Comparisons with NULL (e.g., Salary > NULL) yield UNKNOWN, not TRUE or FALSE. Aggregate functions (except COUNT(*)) ignore NULLs.

Part 4: Functional Dependencies and Normalization

This is the undisputed king of GATE DBMS. You will absolutely face a 2-mark question asking you to identify the highest normal form of a given relation.

Functional Dependency (FD): X → Y means if two tuples agree on X, they must agree on Y.

Finding Candidate Keys: You must be flawless at computing closures (X⁺).

1. Any attribute not present on the right-hand side (RHS) of any FD must be part of every candidate key.
2. Compute the closure of this essential attribute set. If it yields all attributes, it is the sole key.
3. If not, systematically combine it with other attributes until the closure yields all attributes. Prime attribute: An attribute belonging to any candidate key.

Normalization Hierarchy:

1. 1NF: Atomic values only.
2. 2NF: Prevents Partial Dependency. No non-prime attribute should be functionally dependent on a part of a candidate key. (Applies only when the key is composite).
3. 3NF: Prevents Transitive Dependency. For every non-trivial FD X → Y, either:
   • X is a superkey, OR
   • Y is a prime attribute.
4. BCNF (Boyce-Codd Normal Form): Stricter than 3NF. For every non-trivial FD X → Y:
   • X must be a superkey. (No exceptions).

GATE Question Strategy: Always test BCNF first. If an FD violates BCNF, check if the RHS is a prime attribute. If yes, it's in 3NF. If no, check for partial dependency to determine if it's in 2NF.

Decomposition Properties:

• Lossless Join: R1 ∩ R2 must be a superkey of either R1 or R2.
• Dependency Preservation: The closure of dependencies in decomposed tables must equal the closure of original dependencies. (BCNF decomposition guarantees lossless join but may fail to preserve dependencies; 3NF can always achieve both).

Part 5: Transactions and Concurrency Control

ACID Properties: Atomicity (entirely or not at all), Consistency, Isolation, Durability.

Schedules and Serializability:

• Conflict Serializability: Draw a precedence graph (Transactions are nodes, conflicts are directed edges). If the graph has a cycle, it is not conflict serializable.
• Conflicts: Read-Write, Write-Read, Write-Write on the same data item by different transactions. Read-Read is NOT a conflict.

Protocols:

• 2-Phase Locking (2PL): Growing phase (acquire locks) and Shrinking phase (release locks). Ensures conflict serializability, but can cause deadlocks.
• Strict 2PL: Release exclusive locks only after commit/abort. Solves cascading aborts.