Database Concepts and Data Management
This domain checks whether you can read a table design, pick a correct key, and write simple SQL - plus whether you understand why databases replaced loose files for storing organisational data.
Core rules
- A field is a column (attribute), a record is a row (one entity instance), a table is a relation.
- Primary key: uniquely identifies each row and cannot be null or duplicated. Candidate keys are all columns that could serve; one becomes primary. A composite key uses two or more columns.
- Foreign key refers to the primary key of another table and enforces referential integrity.
- Data types: text/varchar, number, date, boolean. Validation blocks impossible values.
- A DBMS gives controlled redundancy, concurrent access, backup and recovery, and security - advantages over separate files.
- SQL: SELECT columns FROM table WHERE condition ORDER BY column; INSERT, UPDATE, DELETE are DML; CREATE, ALTER, DROP are DDL.
- Normalisation: 1NF atomic values, 2NF no partial dependency on part of a composite key, 3NF no transitive dependency.
Worked example
Student(RollNo PK, Name, Class) and Marks(RollNo FK, Subject, Marks). RollNo alone cannot be the primary key of Marks, because one student has many subjects, so the key must be composite (RollNo + Subject). Inserting a Marks row with RollNo 99 when no Student 99 exists violates referential integrity and is rejected.
Traps that cost marks
- Allowing a primary key to hold NULL or duplicates.
- Confusing DELETE (removes rows, structure stays) with DROP (removes the table).
- Mixing up a query, a form and a report.
- Storing several phone numbers in one cell, which breaks 1NF.