Databases and Data Management
What the exam tests
Databases and Data Management tests the relational model, keys, integrity, SQL, normalization and basic database design. Expect queries, table definitions and identification of keys.
Core rules
- A DBMS stores and manages data; an RDBMS stores data in relations (tables) with rows (records) and columns (fields).
- A primary key uniquely identifies each row and cannot be NULL. A foreign key references a primary key in another table and enforces referential integrity.
- Candidate keys are possible primary keys; an alternate key is a candidate not chosen. A composite key uses two or more columns.
- SQL categories: DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT).
- SELECT columns FROM table WHERE condition GROUP BY columns HAVING condition ORDER BY columns;
- Normalization reduces redundancy: 1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies.
Worked example
Table Students(RollNo PRIMARY KEY, Name, Marks). Query: SELECT Name FROM Students WHERE Marks >= 60 ORDER BY Name; This returns names of students scoring at least 60, sorted alphabetically. The WHERE clause filters rows before sorting. To count them: SELECT COUNT(*) FROM Students WHERE Marks >= 60;
Traps
NULL is not zero and not equal to anything; test with IS NULL, not = NULL. DELETE removes rows; DROP removes the table structure. Text values in SQL need single quotes. WHERE filters rows; HAVING filters groups after GROUP BY. A primary key cannot contain duplicates or NULLs.