Database Concepts and Data Management
Database Concepts and Data Management
This domain introduces databases, the relational model, keys, data integrity and basic SQL. Expect questions on identifying fields and records, choosing a primary key, and writing simple queries.
Core rules
- A database is an organised collection of related data; a DBMS stores, retrieves and secures it.
- In a relational table, a row is a record or tuple; a column is a field or attribute. A relation should have atomic values and no repeating groups.
- 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.
- Data types constrain values: integer, decimal, char or varchar, date and Boolean.
- SQL subsets: DDL creates structures with CREATE, ALTER and DROP; DML changes data with INSERT, UPDATE and DELETE; DQL retrieves with SELECT.
- A basic query has the form SELECT columns FROM table WHERE condition ORDER BY column. Text values need single quotes.
Worked example
Student(RollNo, Name, Mark) with RollNo as the primary key. To list names of students with Mark above 75 in descending mark order: SELECT Name, Mark FROM Student WHERE Mark > 75 ORDER BY Mark DESC; Reasoning: SELECT chooses columns, FROM names the table, WHERE filters rows before ordering, and DESC sorts highest first. RollNo is not selected because the question asks for names and marks; it remains the unique identifier.
Traps
- A primary key must be unique and non-null; a name is usually a poor key.
- Using double quotes for SQL text may fail; use single quotes.
- WHERE filters rows; HAVING filters groups after aggregation.
- DELETE removes rows; DROP removes the table structure.
- Storing multiple values in one field violates atomicity and complicates queries.
- A foreign key must match a value in the referenced primary key, or it violates referential integrity.