Cambridge AS & A Level9608

Database and data modelling

Computer Science 9608 Chapter Notes

What this chapter covers

Database and data modelling
ShareWhatsAppPost
Database and data modelling notes

Unable to load PDF

The notes viewer could not load. Please refresh the page.

Read online free. Download a watermarked copy with a free account.

Read the notes

The full Database and data modelling notes as text: skim, search, and jump between subtopics.

~16 min read

In this chapter

  1. 1File-Based Systems vs. The Database Approach
  2. 2The Relational Database Model
  3. 3Entity-Relationship (E-R) Modelling
  4. 4Database Normalisation to 3NF
  5. 5SQL - Data Definition Language (DDL)
  6. 6SQL - Data Manipulation Language (DML)
  7. 7Features of a Database Management System (DBMS)

1. File-Based Systems vs. The Database Approach

Before databases, data was stored in separate files, often specific to one application or department. This is a 'file-based approach'. It caused major problems. For example, a student's address might be stored in a file for the admissions office and another for the finance office. If the address changes, it might only be updated in one file, leading to conflicting information. A database approach solves this by storing all data centrally and controlling access through a single software system, the Database Management System (DBMS). This eliminates redundant data and ensures everyone uses the same, up-to-date information.

Key term

Data Redundancy: The unnecessary repetition of data in multiple files or locations, which can lead to inconsistency and wasted storage space.

Examiner insight

Examiners look for a clear understanding of the *consequences* of data redundancy, not just a definition. Use terms like 'data integrity', 'data consistency', and 'data independence' to show a deeper understanding.

Common pitfall

Simply stating 'data is duplicated' is not enough. Students must explain the consequences, such as data inconsistency or a lack of data integrity.

Worked example 14 marks

A college uses a file-based approach. The 'Registry' department keeps a file of student names and addresses. The 'Library' department also keeps its own file of student names and addresses. A student, Maria, moves house and informs the Registry. The Library is not informed. Describe two problems that arise from this situation.

  1. 1
    1. Data Inconsistency: The college now holds two different addresses for Maria. The Registry's file is correct, but the Library's file is out of date. This is a data integrity failure, as the data is no longer reliable.
  2. 2
    1. Practical Issues: The Library might send important notices or fines to Maria's old address, which she will not receive. This demonstrates a real-world consequence of the data inconsistency.
  3. 3
    1. Wasted Storage: Storing Maria's name and address in two separate files is an inefficient use of storage. In a large college, this duplicated data can add up significantly.

Recap

  • A file-based approach stores data in separate, often application-specific, files.
  • This leads to data redundancy, where the same data is stored in multiple places.
  • Data redundancy can cause data inconsistency, where different copies of the data have different values.
  • File-based systems suffer from data dependency, where programs are tied to the specific structure of a file.
  • A database approach centralises data to improve integrity, reduce redundancy, and allow data sharing.

Quick check

  1. What is the term for when different versions of the same data are stored in different places?1 mark
  2. State one problem, other than redundancy, with a file-based approach.1 mark

2. The Relational Database Model

A relational database organises data into tables. Think of a table like a spreadsheet. Each table represents a single 'entity' (like 'Student' or 'Car'). Each row in a table is a 'record' (or 'tuple') and represents one instance of that entity (e.g., one specific student). Each column is a 'field' (or 'attribute') and describes a characteristic of the entity (e.g., 'FirstName', 'DateOfBirth'). To ensure every record is unique, each table must have a 'primary key' - a field (or combination of fields) that contains a unique value for each record. To link tables together, we use a 'foreign key'. A foreign key in one table is the primary key from another table, creating a relationship between them.

Key term

Primary Key: An attribute or set of attributes that uniquely identifies each record within a database table.

Common pitfall

Confusing a primary key with a foreign key. Remember: a primary key's job is to be unique within its *own* table; a foreign key's job is to *link* to another table.

Fun fact

The relational model was invented by Edgar F. Codd at IBM in 1970. His 12 rules (and a 13th 'rule zero') still define the theoretical basis for all modern relational database systems.

Worked example 13 marks

A database contains two tables: `Tutor` and `Student`. `Tutor` table: `TutorID` (Primary Key), `TutorName`, `OfficeNumber` `Student` table: `StudentID` (Primary Key), `StudentName`, `DateOfBirth`, `AssignedTutorID`

i. Identify the foreign key in this design. [1] ii. Explain the purpose of this foreign key. [2]

  1. 1

    i. The foreign key is `AssignedTutorID` in the `Student` table.

  2. 2

    ii. Its purpose is to link a student record in the `Student` table to a specific tutor record in the `Tutor` table. The value in `AssignedTutorID` for a given student must match a valid `TutorID` in the `Tutor` table. This establishes a one-to-many relationship: one tutor can have many students.

Recap

  • A relational database consists of tables, also known as relations.
  • A table row is a record or tuple.
  • A table column is a field or attribute.
  • A primary key uniquely identifies each record in a table.
  • A foreign key is a field in one table that is the primary key of another, used to link the tables.
  • A composite primary key is a primary key made up of two or more attributes.

Quick check

  1. What is the formal term for a row in a relational database table?1 mark
  2. What is the name for a key that links two tables together?1 mark

3. Entity-Relationship (E-R) Modelling

Before building a database, you must design it. Entity-Relationship (E-R) modelling is a top-down approach to do this. It involves identifying the main 'entities', their 'attributes', and the 'relationships' between them. An entity is a real-world object about which data is stored, like 'Student' or 'Course'. Attributes are the properties of an entity, like 'StudentName' or 'CourseTitle'. A relationship shows how two entities are associated. The 'cardinality' of a relationship describes the numbers involved, using notations like 1:1 (one-to-one), 1:M (one-to-many), or M:N (many-to-many). For example, a 'Student' can enrol in 'many' 'Courses', and a 'Course' can have 'many' 'Students'. This is a many-to-many relationship.

Key term

Cardinality: In database design, cardinality describes the numerical relationship between two entities, such as one-to-one, one-to-many, or many-to-many.

Examiner insight

When asked to justify cardinality, explain it from both directions. For example, 'One Teacher teaches many Classes, and one Class is taught by one Teacher', clearly justifying a 1:M relationship.

Worked example 13 marks

A vet clinic needs a database. The system must store details about Pets and their Owners. Each pet is owned by only one owner, but an owner can have several pets. Draw an E-R diagram showing the relationship between 'Owner' and 'Pet', including the cardinality.

  1. 1
    1. Identify the entities: The entities are 'Owner' and 'Pet'. These are drawn as rectangles.
  2. 2
    1. Identify the relationship: An 'Owner' *owns* a 'Pet'. The relationship is 'owns'.
  3. 3
    1. Determine the cardinality: Read the description carefully. 'Each pet is owned by only one owner' means the 'Owner' side of the relationship is '1'. 'An owner can have several pets' means the 'Pet' side of the relationship is 'Many' (M).
  4. 4
    1. Draw the diagram: The final diagram shows two boxes, [Owner] and [Pet], linked by a line. Using crow's foot notation, the line has a single vertical bar on the Owner side and a 'crow's foot' (three short lines) on the Pet side, representing a 1-to-Many relationship.

Recap

  • E-R modelling is a technique for designing databases.
  • An entity is an object or concept about which you want to store data.
  • A relationship is the association between two entities.
  • Cardinality defines the number of instances of one entity that can be related to instances of another entity.
  • The main cardinalities are one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N).
  • Many-to-many relationships must be resolved into two one-to-many relationships using a linking table.

Quick check

  1. In an E-R diagram for a school, what is the likely cardinality between the 'Teacher' and 'Class' entities?1 mark

4. Database Normalisation to 3NF

Normalisation is a formal process for organising the attributes and tables of a relational database to minimise data redundancy and improve data integrity. It involves a series of steps or 'normal forms'. For your exam, you need to know the first three.

First Normal Form (1NF): The table must have a primary key and no repeating groups. This means every cell holds a single, atomic value, and you can't have columns like 'Child1, Child2, Child3'. Instead, you'd have a separate related table for children.

Second Normal Form (2NF): The table must first be in 1NF. Additionally, all non-key attributes must be fully dependent on the *entire* primary key. This rule is only relevant for tables with a composite primary key (a key made of two or more fields). You remove partial dependencies by splitting them into a new table.

Third Normal Form (3NF): The table must be in 2NF. Additionally, there must be no transitive dependencies. A transitive dependency is when a non-key attribute depends on another non-key attribute, rather than on the primary key. You remove these by splitting the dependent attributes into a new table.

Key term

Third Normal Form (3NF): A database is in 3NF if it is in 2NF and all attributes are dependent only on the primary key, with no transitive dependencies.

Examiner insight

When normalising, you must clearly state the final tables, and for each table, you must underline the primary key and identify any foreign keys. Marks are lost for not showing the final structure clearly.

Common pitfall

Confusing 2NF and 3NF. Remember: 2NF is about dependencies on *parts* of a composite primary key. 3NF is about non-key fields depending on *other non-key fields*.

Worked example 16 marks

A table `OrderDetails(OrderID, CustomerID, CustomerName, ProductID, ProductPrice, Quantity)` contains data about customer orders. The primary key is a composite of (OrderID, ProductID). Normalise this table to Third Normal Form (3NF).

  1. 1
    1. Check 1NF: The table has a primary key and appears to have atomic values. It is in 1NF.
  2. 2
    1. Check 2NF (Remove Partial Dependencies): The primary key is (OrderID, ProductID).
    • `Quantity` depends on both OrderID and ProductID (fully dependent).
    • `CustomerName` depends only on `CustomerID`, which is not part of the key. This is a transitive dependency.
    • `ProductPrice` depends only on `ProductID` (a partial dependency).
    • `CustomerID` depends only on `OrderID` (a partial dependency).

    To reach 2NF, we remove partial dependencies. We create new tables for the parts that don't depend on the whole key.

    • `Order(OrderID, CustomerID)` - PK: OrderID
    • `Product(ProductID, ProductPrice)` - PK: ProductID
    • `OrderLine(OrderID, ProductID, Quantity)` - PK: (OrderID, ProductID)

    Our original table is now split into three.

  3. 3
    1. Check 3NF (Remove Transitive Dependencies): Now we look at the new tables. In our new `Order` table, we have `Order(OrderID, CustomerID)`. But what about `CustomerName`? In the original table, `CustomerName` depends on `CustomerID`, which in turn depends on `OrderID`. This is a transitive dependency. To fix this, we create a separate `Customer` table.
    • `Customer(CustomerID, CustomerName)` - PK: CustomerID
  4. 4
    1. Final 3NF Structure:
    • `Customer(CustomerID, CustomerName)`
    • `Product(ProductID, ProductPrice)`
    • `Order(OrderID, CustomerID)` where `CustomerID` is a foreign key to the Customer table.
    • `OrderLine(OrderID, ProductID, Quantity)` where `OrderID` is a foreign key to the Order table and `ProductID` is a foreign key to the Product table.

Recap

  • Normalisation reduces data redundancy and prevents update, insertion, and deletion anomalies.
  • 1NF requires a primary key and no repeating groups (atomic values).
  • 2NF requires being in 1NF and having no partial dependencies (all fields depend on the whole composite key).
  • 3NF requires being in 2NF and having no transitive dependencies (non-key fields don't depend on other non-key fields).
  • The process involves splitting large, problematic tables into smaller, well-structured ones linked by foreign keys.

Quick check

  1. Which normal form deals with removing transitive dependencies?1 mark
  2. A table has a single-field primary key. Why is it automatically in 2NF (assuming it's in 1NF)?2 marks

5. SQL - Data Definition Language (DDL)

Structured Query Language (SQL) is the standard language for interacting with relational databases. It has two main parts: DDL and DML. Data Definition Language (DDL) is used to define and manage the database's structure. Think of it as the blueprint language. With DDL, you can create the database itself, define tables and their columns, specify data types (like `INT`, `VARCHAR`, `DATE`), set primary and foreign keys, and modify the table structure later if needed.

CREATE DATABASE database_name;

CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(other_column);

Key term

Data Definition Language (DDL): The subset of SQL commands used to create, modify, and delete database structures like tables, but not the data itself.

Common pitfall

Using DML commands like `INSERT` when asked for DDL. DDL builds the container; DML fills it with water. Don't mix them up.

Worked example 14 marks

Write the SQL DDL commands to create a table named `Student`. It should have the following fields:

  • `StudentID` (integer, primary key)
  • `FirstName` (text, max 50 chars)
  • `LastName` (text, max 50 chars)
  • `DateOfBirth` (date)
  1. 1
    1. Start with the `CREATE TABLE` command followed by the table name: `CREATE TABLE Student (`
  2. 2
    1. Define each column with its name and data type on a new line for clarity. `StudentID INT,`, `FirstName VARCHAR(50),`, `LastName VARCHAR(50),`, `DateOfBirth DATE,`
  3. 3
    1. Define the primary key using the `PRIMARY KEY` constraint: `PRIMARY KEY (StudentID)`
  4. 4
    1. Close the parentheses and add a semicolon: `);`
  5. 5
    1. Final command: `CREATE TABLE Student (StudentID INT, FirstName VARCHAR(50), LastName VARCHAR(50), DateOfBirth DATE, PRIMARY KEY (StudentID));`

Recap

  • DDL commands define the database structure.
  • `CREATE DATABASE` makes a new database.
  • `CREATE TABLE` defines a new table with its columns and data types.
  • `ALTER TABLE` is used to modify an existing table, such as adding a column or a key.
  • Constraints like `PRIMARY KEY` and `FOREIGN KEY` are part of the DDL.
  • DDL is about the 'what' and 'how' of storage, not the data itself.

Quick check

  1. Which SQL command would you use to add a new column 'Email' to the `Student` table?1 mark
  2. Is `CREATE TABLE` a DDL or DML command?1 mark

6. SQL - Data Manipulation Language (DML)

If DDL builds the database structure, Data Manipulation Language (DML) is used to manage the data within it. DML commands allow you to perform the four fundamental data operations, often called CRUD (Create, Read, Update, Delete).

  • `SELECT`: Reads or retrieves data from one or more tables. This is the most commonly used SQL command.
  • `INSERT`: Adds new rows of data into a table.
  • `UPDATE`: Modifies existing data in a table.
  • `DELETE`: Removes rows of data from a table.

These commands are almost always used with a `WHERE` clause to specify exactly which rows to affect.

SELECT column_list FROM table_name WHERE condition;

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE table_name SET column1 = value1 WHERE condition;

DELETE FROM table_name WHERE condition;

SELECT ... FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

Key term

Data Manipulation Language (DML): The subset of SQL commands used to query, insert, update, and delete data within existing database tables.

Examiner insight

For queries involving multiple tables, examiners expect you to use a JOIN. Explicitly stating the tables and the join condition (e.g., `... ON Student.TutorID = Tutor.TutorID`) is required for full marks.

Fun fact

The original name for SQL was SEQUEL (Structured English Query Language), but it had to be shortened because 'SEQUEL' was already trademarked by an aircraft company.

Worked example 14 marks

You have two tables: `Student(StudentID, StudentName, TutorID)` and `Tutor(TutorID, TutorName)`. Write a SQL query to find the names of all students who are taught by a tutor named 'Mr Smith'.

  1. 1
    1. We need to retrieve the `StudentName` from the `Student` table.
  2. 2
    1. The condition is based on the `TutorName`, which is in the `Tutor` table.
  3. 3
    1. This means we must join the two tables together on their common key: `Student.TutorID = Tutor.TutorID`.
  4. 4
    1. The `SELECT` part will be `SELECT Student.StudentName`.
  5. 5
    1. The `FROM` and `JOIN` part will be `FROM Student JOIN Tutor ON Student.TutorID = Tutor.TutorID`.
  6. 6
    1. The `WHERE` clause will filter for the specific tutor: `WHERE Tutor.TutorName = 'Mr Smith'`.
  7. 7
    1. Final query: `SELECT Student.StudentName FROM Student JOIN Tutor ON Student.TutorID = Tutor.TutorID WHERE Tutor.TutorName = 'Mr Smith';`

Recap

  • DML commands manage the data inside tables.
  • `SELECT` retrieves data.
  • `INSERT` adds new data.
  • `UPDATE` modifies existing data.
  • `DELETE` removes data.
  • The `WHERE` clause is crucial for specifying which records to affect.
  • The `JOIN` clause is used to combine rows from two or more tables based on a related column.

Quick check

  1. Which DML command is used to add a new student record to a `Student` table?1 mark
  2. What is the purpose of the `WHERE` clause in an `UPDATE` statement?1 mark

7. Features of a Database Management System (DBMS)

A Database Management System (DBMS) is the complex software that sits between the users/applications and the database itself. It's the engine that makes everything work. A DBMS provides several critical features to manage the database effectively and securely.

  • Data Dictionary: A central repository of metadata (data about data). It stores the definitions of all database objects, like table names, column names, data types, and relationships. It's the database's own instruction manual.
  • Data Security and User Access Control: The DBMS controls who can do what. A Database Administrator (DBA) can grant or revoke privileges (like SELECT, INSERT, UPDATE) for specific users on specific tables.
  • Backup and Recovery: The DBMS provides utilities to create regular backups of the database. If a failure occurs (e.g., hardware crash), the DBA can use these backups to restore the database to a consistent state, preventing data loss.
  • Indexing: To speed up data retrieval, a DBMS can create indexes on one or more columns. An index is like the index in a book; it allows the database to find data quickly without scanning the entire table.

Key term

Data Dictionary: A centralized repository of information about a database's data, including the names, definitions, and attributes of data elements.

Common pitfall

Confusing the DBMS with the database. The database is the data; the DBMS is the software you use to manage it (e.g., MySQL, Oracle, SQL Server).

Worked example 14 marks

A school's database contains sensitive student information. Explain how a DBMS helps ensure data security and privacy.

  1. 1
    1. User Authentication: The DBMS first ensures that only authorized users can access the database, typically through usernames and passwords.
  2. 2
    1. Access Control/Privileges: The DBMS allows the Database Administrator (DBA) to set up different user roles (e.g., 'teacher', 'student', 'admin').
  3. 3
    1. Granular Permissions: For each role, the DBA can grant specific permissions. For example, a teacher might have SELECT access on student grades but not UPDATE or DELETE access. A student might only be able to view their own records.
  4. 4
    1. Data Encryption: Many DBMSs also provide features to encrypt sensitive data both in storage (on the disk) and in transit (over the network), making it unreadable even if accessed illegitimately.

Recap

  • A DBMS is the software used to manage a database.
  • The Data Dictionary stores metadata about the database structure.
  • A DBMS provides security by controlling user access with usernames, passwords, and permissions.
  • Backup and recovery features are essential for protecting against data loss.
  • Indexing is a key feature for improving query performance.
  • The DBMS ensures data integrity by enforcing rules like primary and foreign key constraints.

Quick check

  1. What DBMS feature would a database administrator use to improve the speed of a `SELECT` query on a very large table?1 mark
  2. Where does a DBMS store information about table structures and data types?1 mark

End-of-chapter exercise

Test yourself on the whole chapter. Work through these before moving on.

  1. Explain three specific limitations of a file-based approach to data management that are addressed by a relational database.6 marks
  2. A table `Car(RegistrationNumber, Make, Model, OwnerID, OwnerName)` is used to store car details. `RegistrationNumber` is the primary key. Identify why this table is not in Third Normal Form (3NF) and describe the steps to normalise it.5 marks
  3. Distinguish between a primary key and a foreign key, using an example to illustrate your answer.4 marks
  4. Write a SQL DDL statement to create a table called `Book` with the fields `BookID` (integer, primary key), `Title` (text, up to 100 characters), and `Author` (text, up to 50 characters).3 marks
  5. Given the tables `Book(BookID, Title, AuthorID)` and `Author(AuthorID, AuthorName)`, write a SQL DML query to select the `Title` of all books written by 'Jane Austen'.4 marks
  6. A database designer is creating a model for a hospital. The entities are `Patient`, `Doctor`, and `Ward`. A patient is assigned to one ward, but a ward has many patients. A doctor can treat many patients, and a patient can be treated by many doctors. Draw an E-R diagram to represent these entities and relationships, showing the correct cardinalities.6 marks
  7. Describe two features a Database Management System (DBMS) provides to ensure data integrity.4 marks
  8. Explain the purpose of the Data Dictionary within a DBMS.2 marks
  9. A table `ProjectAssignments` has a composite primary key consisting of `EmployeeID` and `ProjectID`. The other field is `HoursWorked`. Explain why this table is, by definition, in Second Normal Form (2NF), assuming it is already in 1NF.3 marks
  10. A company wants to give all employees in the 'Sales' department a 5% pay rise. The table is `Employee(EmployeeID, Name, Department, Salary)`. Write the SQL DML statement to apply this change.3 marks

Go deeper

Practise and revise with member-only material for this chapter.

Workbook

Exam-style practice questions with full worked solutions.

Unlock with a plan

Chapter at a Glance

A single-page summary of every key idea for fast revision.

Unlock with a plan

Free notes are just the start.

Unlock every Workbook and Chapter at a Glance, and generate your own worksheets and predicted papers.

Explore plans

Related chapters

Data representation (1.1)Communication and Internet technologiesHardwareProcessor fundamentalsSystem software (1.5)Security, privacy and data integrity