Database Concepts and DBMS
The exam checks relational terminology, keys, integrity rules, the advantages of a DBMS over file systems, normalisation basics and simple SQL statements.
Terminology
Relation = table; tuple = row or record; attribute = column or field; degree = number of columns; cardinality = number of rows. A primary key uniquely identifies each row and can never be NULL; a candidate key is any column that could serve as primary; a foreign key refers to another table's primary key and enforces referential integrity. Domain integrity restricts allowed values, for example Marks between 0 and 100.
Why a DBMS
Less redundancy, controlled consistency, sharing, concurrent access, backup and recovery, security and a query language - all things a set of separate files cannot guarantee.
Normalisation
1NF: atomic values, no repeating groups. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependency, that is a non-key column determining another non-key column.
SQL
DDL (CREATE, ALTER, DROP) defines structure; DML (SELECT, INSERT, UPDATE, DELETE) works on data. Form: SELECT columns FROM table WHERE condition ORDER BY column ASC|DESC;
Worked example
Table Student(RollNo, Name, Marks). List names of students scoring above 75, highest first: SELECT Name FROM Student WHERE Marks > 75 ORDER BY Marks DESC; Reasoning: WHERE filters rows before output, ORDER BY sorts what is returned, DESC gives highest first. For students whose marks are missing, the condition is WHERE Marks IS NULL - never WHERE Marks = NULL, because NULL is unknown, not equal to anything.
Traps
- Declaring two primary keys in one table; use a composite key instead.
- Confusing DELETE (removes rows, structure stays) with DROP (table destroyed).
- WHERE filters rows, HAVING filters groups after GROUP BY.
- Forgetting quotes around text values, or writing = NULL.