GATE CS & IT Engineering Databases Syllabus

Databases syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Databases in GATE CS & IT Engineering, not a summary of it.

1. 1

   ER-model

   1 topic

   • Concepts of ER-model
2. 2

   Relational model

   3 topics

   • Relational algebra
   • Tuple calculus
   • SQL
3. 3

   Integrity constraints

   1 topic

   • Types of Integrity constraints
4. 4

   Normal forms

   3 topics

   • First Normal Form (1NF)
   • Second Normal Form (2NF)
   • Third Normal Form (3NF)
5. 5

   File organization

   1 topic

   • Types of File organization
6. 6

   Indexing

   2 topics

   • B trees
   • B+ trees
7. 7

   Transactions and concurrency control

   2 topics

   • Concepts of Transactions
   • Concurrency control

Databases flashcards for GATE CS & IT Engineering

25 of 51 cards from the Databases deck — real questions with worked answers.

1. In the ER model, what is the difference between an entity and an entity set?

   An entity is a real-world object distinguishable from others (e.g., one student). An entity set is a collection of entities of the same type that share the same attributes (e.g., all students).

2. List the four types of attributes in the ER model.

   1) Simple (atomic) vs Composite (divisible, e.g., Name -> First/Last); 2) Single-valued vs Multi-valued (e.g., phone numbers); 3) Stored vs Derived (e.g., Age derived from DOB); 4) Key attribute (uniquely identifies an entity).

3. What is a weak entity set and how is it identified in an ER diagram?

   A weak entity set has no key attribute of its own; it depends on an identifying (owner) strong entity through an identifying relationship. It is drawn with a double rectangle, the identifying relationship with a double diamond, and is identified by a partial key plus the owner's key.

4. What do the four cardinality ratios (1:1, 1:N, N:1, M:N) express in an ER relationship?

   They express the maximum number of relationship instances an entity can participate in: one-to-one, one-to-many, many-to-one, and many-to-many.

5. Distinguish total participation from partial participation in the ER model.

   Total participation (double line) means every entity in the set must participate in the relationship. Partial participation (single line) means some entities may not participate.

6. Name the six fundamental (primitive) operators of relational algebra.

   Selection $\sigma$, Projection $\pi$, Union $\cup$, Set difference $-$, Cartesian product $\times$, and Rename $\rho$.

7. What does the selection operator $\sigma_{C}(R)$ do, and how does it differ from projection $\pi_{L}(R)$?

   Selection $\sigma_{C}(R)$ chooses rows (tuples) of $R$ satisfying condition $C$ (horizontal subset). Projection $\pi_{L}(R)$ chooses columns in list $L$ and removes duplicates (vertical subset).

8. Define the natural join $R \bowtie S$ in relational algebra.

   $R \bowtie S$ combines tuples of $R$ and $S$ that have equal values on all common attributes, then keeps only one copy of each shared attribute. It equals $\pi(\sigma_{equality\ on\ common\ attrs}(R \times S))$.

9. Express the division operation $R \div S$ in words.

   $R \div S$ returns the tuples (over the attributes of $R$ not in $S$) that are associated with every tuple in $S$. It is used for queries like "find students who took all courses."

10. How is a left outer join different from a natural (inner) join?

    An inner join keeps only matching tuples. A left outer join keeps all tuples of the left relation, padding unmatched ones with NULLs for the right relation's attributes.

11. What is the difference between tuple relational calculus (TRC) and domain relational calculus (DRC)?

    TRC uses variables that range over tuples, written $\{ t \mid P(t) \}$. DRC uses variables that range over individual attribute domain values, written $\{ \langle x_1,\dots,x_n \rangle \mid P(x_1,\dots,x_n) \}$. Both are nonprocedural (declarative).

12. What does it mean for a relational calculus expression to be "safe"?

    A safe expression guarantees a finite result whose values come only from the active domain (values appearing in the database or query), preventing infinite results such as $\{ t \mid \neg R(t) \}$.

13. State the form of a tuple relational calculus query and give an example.

    Form: $\{ t \mid P(t) \}$ where $P$ is a formula. Example (students with marks > 80): $\{ t \mid Student(t) \wedge t.marks > 80 \}$.

14. Which two quantifiers are used in relational calculus and what do they mean?

    The existential quantifier $\exists$ ('there exists') and the universal quantifier $\forall$ ('for all'). They are related by $\forall t\, P(t) \equiv \neg \exists t\, \neg P(t)$.

15. What is the relationship between relational algebra and relational calculus (Codd's theorem)?

    Codd's theorem states that relational algebra and safe relational calculus (tuple and domain) are equivalent in expressive power; they are all relationally complete.

16. In SQL, what is the difference between WHERE and HAVING clauses?

    WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregate functions like COUNT or SUM.

17. What is the logical order of evaluation of clauses in an SQL SELECT query?

    FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY (with DISTINCT and LIMIT applied near the end).

18. How does SQL handle NULL in comparisons and aggregate functions?

    Comparisons with NULL yield UNKNOWN (three-valued logic), so use IS NULL / IS NOT NULL. Aggregate functions (except COUNT(*)) ignore NULLs; COUNT(*) counts all rows including NULLs.

19. Distinguish the SQL set operators UNION, INTERSECT, and EXCEPT from UNION ALL.

    UNION, INTERSECT, EXCEPT perform set operations and eliminate duplicate rows. UNION ALL keeps duplicates and does not sort/deduplicate, making it faster.

20. What is the difference between a correlated and a non-correlated (nested) subquery?

    A non-correlated subquery is evaluated once independently of the outer query. A correlated subquery references columns from the outer query and is re-evaluated for each outer row.

21. Name the four types of integrity constraints in the relational model.

    1) Domain constraint (attribute values come from a defined domain); 2) Entity integrity (primary key cannot be NULL); 3) Referential integrity (foreign key matches an existing primary key or is NULL); 4) Key constraint (uniqueness of candidate/primary keys).

22. State the entity integrity constraint.

    No attribute that is part of a primary key may have a NULL value, ensuring every tuple is uniquely identifiable.

23. State the referential integrity constraint.

    A foreign key value in a referencing relation must either match some primary key value in the referenced relation or be entirely NULL; it cannot reference a non-existent tuple.

24. What are the referential triggered actions ON DELETE / ON UPDATE in SQL?

    CASCADE (propagate the delete/update), SET NULL (set foreign key to NULL), SET DEFAULT (set to a default value), and NO ACTION / RESTRICT (reject the operation if it violates integrity).

25. Differentiate a candidate key, primary key, and super key.

    A super key is any attribute set that uniquely identifies tuples. A candidate key is a minimal super key (no proper subset is a super key). A primary key is one candidate key chosen as the main identifier; remaining candidate keys are alternate keys.

See more Databases flashcards →

Planning Databases for GATE CS & IT Engineering

Databases (GATE CS & IT Engineering) FAQ