GATE DA / Section 5

Database Management and Warehousing

The ER model, the relational model with relational algebra and tuple relational calculus, SQL, integrity constraints, functional dependencies and normal forms, file organisation and indexing (B+ trees and hashing), data types and data transformation (normalisation, discretisation, sampling, compression), and data warehouse modelling — star and snowflake schemas, data cubes, concept hierarchies, OLAP operations and measures.

8 topics79 practice questions≈ 8–12 marksPractise this section →

This section mixes classic database theory with the data-preparation and warehousing ideas a data scientist uses daily. Questions are typically: output of a relational algebra or SQL query on a small table, the number of tuples a join can produce, candidate keys and normal forms from functional dependencies, B+ tree order and index sizes, the result of min-max or z-score normalisation or equal-width binning, and counting cuboids or classifying measures in a data cube.

What the syllabus lists: ER-model; relational model — relational algebra, tuple calculus, SQL, integrity constraints, normal form; file organisation, indexing; data types, data transformation such as normalisation, discretisation, sampling, compression; data warehouse modelling — schema for multidimensional data models, concept hierarchies, measures: categorisation and computations.

Topics

Read in order — later topics build on earlier ones

01The Entity–Relationship ModelEntities, entity sets and attributes (simple, composite, multivalued, derived, key), relationships with degree, cardinality ratios and participation constraints, weak entity sets, specialisation, generalisation and aggregation, ER notation, and the rules for converting an ER diagram into relational tables (including the minimum number of tables).0/9 solved 02The Relational Model and Relational AlgebraRelations, tuples, attributes and domains, schema vs instance, superkeys, candidate, primary and foreign keys, and the relational algebra — selection, projection, set operations, Cartesian product, renaming, joins (theta, equi, natural, outer), intersection and division — with set semantics, result-size bounds, algebraic equivalences and query trees.0/10 solved 03Tuple Relational CalculusDeclarative queries in tuple relational calculus — tuple variables, atoms, connectives and quantifiers, free and bound variables, translating between calculus and algebra (selection, projection, join, difference, division), expressing "for all" with negated existentials, safety, domain relational calculus in brief, and Codd's theorem on expressive equivalence.0/8 solved 04SQLData definition (CREATE, constraints, ALTER) and data manipulation (INSERT, UPDATE, DELETE), the SELECT statement and its logical evaluation order, filtering, joins (inner, self, outer), aggregation with GROUP BY and HAVING, NULLs and three-valued logic, nested and correlated subqueries (IN, EXISTS, ANY, ALL), set operations, views, and window functions — with hand-evaluation practice.0/11 solved 05Integrity Constraints, Functional Dependencies and Normal FormsDomain, key, entity and referential integrity constraints with cascading actions, update anomalies, functional dependencies and Armstrong's axioms, attribute closure and finding all candidate keys, minimal covers, the normal forms 1NF, 2NF, 3NF and BCNF (and 4NF), lossless-join and dependency-preserving decomposition, and the 3NF synthesis and BCNF decomposition algorithms.0/11 solved 06File Organization and IndexingRecords, blocks and the blocking factor, heap, sorted and hashed file organisations with their access costs, primary, clustering and secondary indexes (dense vs sparse) and their sizes, multilevel indexes, B-trees and B+ trees (order from block size, height, search, insertion with splits, range queries), hash indexes (static and extendible), and how to choose an index.0/9 solved 07Data Types and Data Transformation — Normalization, Discretization, Sampling, CompressionAttribute types (nominal, binary, ordinal, interval, ratio; discrete vs continuous), data quality issues, and the preprocessing steps the syllabus names — normalisation (min-max, z-score, decimal scaling), smoothing and binning, discretisation (equal-width, equal-frequency, entropy-based, concept hierarchies), sampling (simple random, stratified, cluster, systematic, reservoir) and data reduction and compression (lossless vs lossy, dimensionality and numerosity reduction).0/11 solved 08Data Warehouse Modelling — Cubes, Schemas, Concept Hierarchies and MeasuresWhat a data warehouse is and how it differs from an operational database (OLTP vs OLAP), ETL and architecture, the multidimensional data model (facts, dimensions, measures, the data cube and its lattice of cuboids), star, snowflake and fact-constellation schemas, concept hierarchies, OLAP operations (roll-up, drill-down, slice, dice, pivot), the categorisation of measures into distributive, algebraic and holistic, cube computation and materialisation, and ROLAP/MOLAP/HOLAP.0/10 solved