# Database concepts

> CIE A-Level Computer Science · 9618
> Source: https://www.owlsprep.com/study/cie-9618-u8-database-concepts/

This sub-topic introduces core foundational concepts for all database work in CIE A-Level Computer Science. We cover key terminology, compare common database models, explain poor design problems, and outline the role of database management systems.

**Prerequisites:** Basic understanding of data storage and file systems

## Learning objectives

- Define core database terminology including entities, attributes and relationships
- Distinguish between flat-file and relational database models
- Explain the impacts of data redundancy and inconsistency
- Outline the key functions of a Database Management System (DBMS)

## Core Database Terminology

**Entity** — A distinct, identifiable object or concept that we want to store data about in a database

*Example:* Common examples include students, books, orders and products

All entities have characteristics that we record, called attributes. Each attribute holds a single piece of data about an entity. A relationship describes how two or more entities are connected to each other.

**Worked example:** Identify the entities, attributes, and one relationship for a library database storing data about books and borrowers

1. First, identify distinct objects the database stores data about:
2. Two clear entities are: 1. Book 2. Borrower
3. Next, list attributes for each entity:
4. Attributes for Book: `book_id`, title, author, publication year, availability. Attributes for Borrower: `borrower_id`, name, contact number, join date.
5. Finally, identify a relationship between the entities:
6. One relationship is: A borrower can borrow many books, and one book can be borrowed by one borrower at a time.

> **Exam tip:** Always name entities as nouns (not verbs) in exam answers to avoid losing marks

## Flat-file vs Relational Databases

Two core database structures tested in CIE 9618 are flat-file and relational databases. A flat-file database stores all data in a single table or file, while a relational database splits data across multiple linked tables, one per major entity.

**Flat-file Database** — A database structure that stores all data in a single table or unstructured file, with no separation of related data entities

*Example:* A single CSV file storing both student details and course enrollment data is a flat-file

> **info**
>
> Flat-file databases are simple for small datasets but suffer from critical design issues when scaled for multiple users or large volumes of data.

**Worked example:** Explain one major problem with storing all student and course data in a single flat-file table

1. First, notice that the same course details (e.g. course name, teacher name) will be repeated for every student enrolled in the course.
2. This creates redundant data: the same information is stored multiple times, which wastes storage space.
3. It also risks data inconsistency: if the course name changes, you have to update it in every row, and it’s easy to miss some rows, leading to conflicting data.
4. A relational database fixes this by splitting data into two linked tables: one for students and one for courses.

## Data Redundancy and Consistency

Two of the most frequently tested concepts in CIE database questions are redundancy and consistency. Poor database design almost always leads to issues with both, and they are regularly asked for definition questions.

**Data Redundancy** — The unnecessary repetition of the same data across multiple locations in a database

*Example:* Storing the same customer address for every order a customer places

Data consistency means all copies of the same data in a database match each other. When data is updated, all instances must be updated to maintain consistency. Redundancy makes maintaining consistency much harder, as multiple updates are required instead of one.

**Worked example:** A retail store stores customer order data in a flat-file where each row includes the full customer address. A customer changes their address after moving. Explain how redundancy leads to inconsistency here.

1. The customer’s address is stored once for every order they have placed, so it is redundantly repeated.
2. If the team updating the address only changes it in the most recent order row, all older order rows will still hold the old address.
3. This creates inconsistency: the database stores two different addresses for the same customer.
4. In a well-designed relational database, the address is stored once in a customer table, so only one update is needed, eliminating the risk of inconsistency.

> **Exam tip:** When asked to distinguish the two terms: redundancy is repeated data, inconsistency is conflicting data. Redundancy causes inconsistency

## Role of a Database Management System (DBMS)

A DBMS is the software that manages access, organization, and security of a database. It acts as an interface between end-users, applications, and the underlying database files.

**Database Management System (DBMS)** — Software that provides tools for creating, modifying, querying, and securing a database, while managing interactions between users and stored data

*Example:* Common examples include MySQL, PostgreSQL, Microsoft Access and Oracle

- Providing a data definition language (DDL) to create and modify database structure
- Providing a data manipulation language (DML) for querying and updating data
- Managing concurrent access by multiple users to prevent conflicting changes
- Enforcing access controls and security rules for sensitive data
- Creating backups and recovering data after system failure
- Enforcing data integrity constraints to ensure stored data is valid

**Worked example:** Describe one way a DBMS improves security for a hospital patient database

1. A DBMS allows administrators to set granular access permissions for different user groups.
2. For example, a receptionist can access a patient’s contact details but not their full confidential medical history.
3. A treating doctor can access all of a patient’s data, while administrative billing staff can only access payment related details.
4. This enforced access control prevents unauthorized access to sensitive data, which is impossible to implement reliably with unmanaged flat files.

## Common pitfalls

- **Wrong:** Confusing entities and attributes: calling 'student name' an entity instead of an attribute
  - Why it fails: Entities are the objects we store data about, while attributes are the properties of those objects
  - Correct: Check: if it is a thing you store multiple entries for, it is an entity. If it is a property of that thing, it is an attribute
- **Wrong:** Claiming all flat-file databases are bad for all use cases
  - Why it fails: CIE examiners test that flat-files are appropriate for small, simple datasets
  - Correct: Acknowledge flat-files are simple and low-overhead for small use cases, but have critical issues for large, multi-user datasets
- **Wrong:** Mixing up data redundancy and data inconsistency
  - Why it fails: The concepts are related but distinct, and exam questions regularly ask for separate definitions
  - Correct: Remember: Redundancy = unnecessary repeated data. Inconsistency = conflicting versions of the same data. Redundancy causes inconsistency
- **Wrong:** Confusing the DBMS with the database itself
  - Why it fails: Many candidates mix up the software and the stored data
  - Correct: The database is the collection of stored data. The DBMS is the software that manages the database
- **Wrong:** Drawing relationships between attributes instead of entities in schemas
  - Why it fails: Relationships are associations between whole entities, not individual attributes
  - Correct: Always connect entities, not individual attributes, in entity relationship diagrams

## Cheatsheet

| Concept | Core Definition |
| --- | --- |
| Entity | Distinct object/concept to store data about |
| Attribute | Property of an entity storing a single data item |
| Relationship | Association between two or more entities |
| Flat-file Database | All data stored in one single table/file |
| Relational Database | Data split across linked tables of entities |
| Data Redundancy | Unnecessary repetition of data |
| Data Inconsistency | Conflicting versions of the same data |
| DBMS | Software that manages database access and structure |

## What's next

Now that you understand the core foundational concepts of databases, you’re ready to build on this knowledge to learn more advanced database design and management topics. Understanding these fundamentals is critical for all subsequent database work, from entity relationship diagrams to SQL query writing. Almost all CIE 9618 database exam questions build on these core terms and concepts, and you will use these ideas when identifying design problems, normalizing databases, and writing queries to extract meaningful information from structured data.

- [Relational database model](https://www.owlsprep.com/study/cie-9618-u8-relational-database-model/)
- [Structured Query Language (SQL)](https://www.owlsprep.com/study/cie-9618-u8-structured-query-language/)
- [Normalisation](https://www.owlsprep.com/study/cie-9618-u8-normalisation/)

---

From [OwlsPrep](https://www.owlsprep.com) — free study guides for A-Level, IB, AP and IGCSE, written against the official syllabus. Canonical page: https://www.owlsprep.com/study/cie-9618-u8-database-concepts/