Database Management System
Database Management System
This domain tests database concepts, keys, relationships, normalization and SQL. Expect questions on tables, records, fields, primary and foreign keys, data integrity, and basic query commands.
Core rules
- A table is a relation made of records and fields. A primary key uniquely identifies each record and cannot be null.
- A foreign key refers to a primary key in another table and enforces referential integrity.
- SQL is divided into DDL for defining structures and DML for manipulating data. CREATE and ALTER are DDL; SELECT, INSERT, UPDATE and DELETE are DML.
- SELECT chooses columns, FROM names tables, WHERE filters rows, GROUP BY groups rows, HAVING filters groups, and ORDER BY sorts output.
- Normalization reduces redundancy. First normal form requires atomic values; second removes partial dependencies; third removes transitive dependencies.
Worked example
Query: SELECT Name, Marks FROM Student WHERE Marks >= 60 ORDER BY Marks DESC. The FROM clause identifies the Student table. WHERE keeps only rows with Marks at least 60. SELECT returns the Name and Marks columns. ORDER BY Marks DESC lists the highest marks first. The reasoning is that row filtering happens before sorting and output. To count students per class, you would add GROUP BY Class and use an aggregate function.
Traps
NULL is not equal to zero or an empty string; comparisons with NULL need IS NULL or IS NOT NULL. DELETE removes rows, while DROP removes the table structure. WHERE filters rows; HAVING filters grouped results. Text values in SQL normally need single quotes, and a primary key must be unique and non-null.