Term Definition Example
Database A persistent, organised A school register
collection of data, typically containing student names,
stored electronically in a grades, and attendance.
computer system.
DBMS Database Management Microsoft Access, MySQL,
System. Software used to Oracle.
create, manage, and
maintain the data in a
database.
Table (Entity) A collection of related data Students table, Courses
organised into rows and table.
columns.
Record (Row/Tuple) A complete set of data All the information about a
about one item or entity. single student.
Field (Column/Attribute) A single piece of data (data StudentID, StudentName,
item) that describes a DateOfBirth.
characteristic of the entity.
Unit 2: Database Structures and Relationships
Flat File vs. Relational Database
Feature Flat File Database Relational Database
Structure All data stored in a single Data is stored across
table. multiple linked tables.
Data Redundancy High. Data must be Low. Data is stored once
duplicated many times and linked, saving space
(e.g., teacher name copied and improving consistency.
for every student in their
class).
Complexity Simple to set up and query. More complex setup
required.
Data Integrity Poor. Prone to update Good. Maintains
anomalies and consistency via links (keys).
inconsistencies.
Database Relationships
Relationships define how tables are linked using keys to prevent redundancy.
1. One-to-One (1:1): One record in Table A relates to exactly one record in Table B.
2. One-to-Many (1:M): One record in Table A relates to multiple records in Table B. (This is
the most common relationship).
3. Many-to-Many (M:N): Many records in Table A relate to many records in Table B. (This
must be resolved using a bridge/joining table).
Unit 3: Primary and Foreign Keys
Keys are essential for linking tables and ensuring data integrity.
Primary Key (PK)
● Purpose: Uniquely identifies each record (row) within its own table.
● Requirements:
○ Must be unique (no duplicates allowed).
○ Must always have a value (cannot be null/empty).
○ Should not change over time (e.g., use StudentID, not Address).
● Example: BookID in a Books table.
Foreign Key (FK)
● Purpose: A field in one table that refers to the Primary Key in another table. This
creates the link (relationship).
● Requirements:
○ It does not have to be unique in its own table.
○ It can sometimes be null (depending on the relationship).
● Example: BookID in a Loans table, linking back to the Books table.
Referential Integrity
● The rule that ensures that every Foreign Key value in the linking table matches a valid
Primary Key value in the original table.
● In plain terms: You cannot create a loan record for a book ID that doesn't actually exist in
the Books table.
Unit 4: Data Integrity and Quality
Data Validation
Definition: Checks performed at the point of data entry to ensure the data is reasonable,
sensible, and correctly formatted. It checks for errors in input.
Validation Check Description Example
Range Check Data is within a specific, Age must be between 18
sensible range. and 65.
Type Check Data entered is of the Expecting a number, but
correct data type. text is entered.
Format Check Data adheres to a required Postcode must be 'LLNN
pattern or structure. NLL'.
Presence Check Ensures a field is not left The LastName field is
empty (must be present). mandatory.
Length Check Data contains the required Password must be at least
number of characters (min 8 characters long.
or max).
Data Verification
Definition: Checks performed to ensure that the data entered into the system matches the
original source document or the correct input. It checks for human errors during
transcription.
● Double Entry (or Keying): Data is typed in twice by two different people (or the same
person). The two inputs are compared, and if they do not match, the user is prompted to
check and re-enter.
● Proofreading/Visual Check: An operator visually compares the data entered on the
screen against the original paper or source document.
Unit 5: Querying Data (Search)
A query is a request for data or information from a database table or combination of tables.
Basic Query Principles
● Selection: Choosing specific records (rows) based on defined criteria (e.g., selecting all
customers from London).
● Projection: Choosing specific fields (columns) from a table (e.g., only displaying the
Name and Email fields).
Common Query Operators
Operator Description Example
AND Records must satisfy both City = 'London' AND Age >
conditions. 30
OR Records must satisfy at Course = 'Physics' OR
least one of the conditions. Course = 'Chemistry'
NOT Records must not satisfy NOT Country = 'USA'
the condition.
LIKE Used for pattern matching LastName LIKE 'Smi%'
(wildcards). (finds records starting with
'Smi').