GATE DA & AI Engineering Database Management and Warehousing Syllabus

Database Management and Warehousing syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Database Management and Warehousing in GATE DA & AI Engineering, not a summary of it.

1. 1

   ER-model

   overview

   Examined as a single unit within Database Management and Warehousing — no further topic split in the official outline.

2. 2

   Relational Model

   5 topics

   • Relational Algebra
   • Tuple Calculus
   • SQL
   • Integrity Constraints
   • Normal Form
3. 3

   File Organization

   overview

   Examined as a single unit within Database Management and Warehousing — no further topic split in the official outline.

4. 4

   Indexing

   overview

   Examined as a single unit within Database Management and Warehousing — no further topic split in the official outline.

5. 5

   Data Types

   overview

   Examined as a single unit within Database Management and Warehousing — no further topic split in the official outline.

6. 6

   Data Transformation

   4 topics

   • Normalization
   • Discretization
   • Sampling
   • Compression
7. 7

   Data Warehouse Modelling

   3 topics

   • Schema for Multidimensional Data Models
   • Concept Hierarchies
   • Measures: Categorization and Computations

Database Management and Warehousing flashcards for GATE DA & AI Engineering

25 of 53 cards from the Database Management and Warehousing deck — real questions with worked answers.

1. In relational algebra, what does the SELECT operation ($\sigma$) do, and what is its general form?

   It filters rows (tuples) that satisfy a given predicate. Form: $\sigma_{\text{condition}}(R)$, returning every tuple of relation $R$ for which the condition is true. It is horizontal (row-wise) selection.

2. In relational algebra, what does the PROJECT operation ($\pi$) do, and what side-effect does it have?

   It selects specified columns: $\pi_{A_1, A_2, \dots, A_n}(R)$. It is vertical (column-wise) selection and automatically removes duplicate tuples, since a relation is a set.

3. Define the relational algebra natural join ($\bowtie$).

   $R \bowtie S$ combines tuples of $R$ and $S$ that have equal values on all common attributes, keeping each shared attribute only once. It is equivalent to a theta-join on equality of common attributes followed by a projection removing duplicate columns.

4. What is the difference between a theta-join and an equi-join in relational algebra?

   A theta-join $R \bowtie_{\theta} S$ uses any comparison condition $\theta$ (e.g. $<, >, \leq, \neq$). An equi-join is the special case where $\theta$ uses only equality ($=$) comparisons.

5. What does the relational algebra division operation ($\div$) compute?

   $R \div S$ returns tuples from $R$ that are associated with every tuple in $S$. It answers 'for all' queries, e.g. 'find students who took all required courses.'

6. What conditions must two relations satisfy to be union-compatible for set operations ($\cup, \cap, -$)?

   They must have the same number of attributes (same arity / degree), and corresponding attributes must be drawn from the same (compatible) domains.

7. List the fundamental (primitive) operations of relational algebra from which all others can be derived.

   The six primitives are: select ($\sigma$), project ($\pi$), union ($\cup$), set difference ($-$), Cartesian product ($\times$), and rename ($\rho$). Operations like join, intersection, and division are derived from these.

8. How is intersection expressed using the primitive relational algebra operators?

   $R \cap S = R - (R - S)$. Equivalently $R \cap S = S - (S - R)$.

9. What is the purpose of the rename operator ($\rho$) in relational algebra?

   It renames a relation and/or its attributes, e.g. $\rho_{S(B_1,\dots,B_n)}(R)$. It is essential for self-joins and Cartesian products where attribute names would otherwise clash.

10. What is tuple relational calculus, and what is the general form of an expression?

    A non-procedural (declarative) query language specifying what to retrieve, not how. General form: $\{ t \mid P(t) \}$, the set of all tuples $t$ such that predicate $P(t)$ is true.

11. Which quantifiers are used in tuple relational calculus, and what do they mean?

    The existential quantifier $\exists$ ('there exists') and the universal quantifier $\forall$ ('for all'). Example: $\exists s \in S\,(\dots)$ is true if at least one tuple $s$ satisfies the condition.

12. What does it mean for a tuple calculus expression to be 'safe'?

    A safe expression guarantees a finite result and contains only values from the domains of the database relations (the active domain). Safety prevents infinite results such as $\{ t \mid \neg(t \in R) \}$.

13. State Codd's theorem regarding the expressive power of relational query languages.

    Relational algebra, safe tuple relational calculus, and safe domain relational calculus are equivalent in expressive power (relationally complete). Any query in one can be expressed in the others.

14. What is the key difference between tuple relational calculus and domain relational calculus?

    Tuple calculus variables range over whole tuples ($\{ t \mid P(t) \}$), while domain calculus variables range over individual attribute domain values ($\{ \langle x_1, \dots, x_n \rangle \mid P(x_1, \dots, x_n) \}$).

15. In SQL, what is the difference between the WHERE clause and the HAVING clause?

    WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregate functions like $\text{COUNT}$, $\text{SUM}$, etc.

16. What is the logical order of evaluation of the main SQL SELECT clauses?

    FROM $\to$ WHERE $\to$ GROUP BY $\to$ HAVING $\to$ SELECT $\to$ ORDER BY. (SELECT projection logically occurs after grouping/filtering and before final ordering.)

17. List the five standard aggregate functions in SQL and note how they treat NULLs.

    $\text{COUNT}$, $\text{SUM}$, $\text{AVG}$, $\text{MIN}$, $\text{MAX}$. All except $\text{COUNT}(*)$ ignore NULL values. $\text{COUNT}(*)$ counts all rows including those with NULLs.

18. What is the difference between INNER JOIN and LEFT OUTER JOIN in SQL?

    INNER JOIN returns only rows with matching keys in both tables. LEFT OUTER JOIN returns all rows from the left table plus matched right rows, filling unmatched right-side columns with NULL.

19. In SQL, what does the three-valued logic produce, and how does NULL compare?

    SQL uses three truth values: TRUE, FALSE, and UNKNOWN. Any comparison with NULL (e.g. $\text{NULL} = \text{NULL}$) yields UNKNOWN; to test for NULL you must use IS NULL / IS NOT NULL.

20. What is the difference between UNION and UNION ALL in SQL?

    UNION combines result sets and removes duplicate rows (requiring a sort/hash). UNION ALL concatenates all rows including duplicates and is therefore faster.

21. What distinguishes a correlated subquery from 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.

22. What are the four categories of integrity constraints in the relational model?

    Domain constraints (valid attribute values), entity integrity (primary key not NULL), referential integrity (foreign key matches a referenced key or is NULL), and key constraints (uniqueness of candidate keys).

23. State the entity integrity constraint.

    No attribute of a primary key may have a NULL value. This ensures every tuple in a relation can be uniquely identified.

24. State the referential integrity constraint.

    A foreign key value in a referencing relation must either match an existing primary/candidate key value in the referenced relation or be entirely NULL. It prevents 'dangling' references.

25. Define a superkey, a candidate key, and a primary key.

    A superkey is any set of attributes that uniquely identifies a tuple. A candidate key is a minimal superkey (no proper subset is a superkey). A primary key is the candidate key chosen to identify tuples.

See more Database Management and Warehousing flashcards →

Planning Database Management and Warehousing for GATE DA & AI Engineering

Database Management and Warehousing (GATE DA & AI Engineering) FAQ