Computer Science (9210) | Relational databases

👋 Welcome to the World of Relational Databases!

Hello future Computer Scientists! This chapter is incredibly important because databases are the backbone of the digital world—think Google, Amazon, your school’s student records system. If it involves storing lots of organised data, it uses a database!

Don't worry if this seems tricky at first. We will break down how these super-efficient data storage systems work, focusing on the concepts you need to master before we start using the Structured Query Language (SQL).

---

1. What is a Database?

A Simple Definition

A Database is simply an organised collection of related data. It allows users to store, manage, and retrieve information quickly and efficiently.

Imagine your local library. If all the books were scattered everywhere, finding a specific novel would be impossible. The library catalogue—which neatly organises books by title, author, and location—is a perfect real-world analogy for a database.

The Relational Model

In Computer Science, we focus on a specific type of database called a Relational Database.

The core idea of the relational model is that data is stored in separate, simple structures (called tables) that are linked together (related) using special keys. This is much more efficient than keeping all data in one giant list.

---

2. The Components of a Relational Database

A relational database is built from three main components. If you can understand a spreadsheet, you can understand these parts!

2.1. Tables (Relations)

A Table (sometimes called a Relation) is the fundamental building block of a database. It holds data about a single type of entity (like Students, Products, or Orders).

• Analogy: A single spreadsheet file.

2.2. Fields (Attributes/Columns)

A Field is a single piece of information about the entity in the table. Fields are the vertical parts of the table.

• Examples: Student_ID, First_Name, Date_of_Birth.
• Fields define the structure of the data.

Did you know? Fields are also known as Attributes in database theory!

2.3. Records (Tuples/Rows)

A Record is a complete set of fields for one specific item or person. Records are the horizontal parts of the table.

• Example: All the information about one specific student (their ID, their name, their date of birth, etc.).
• A record is also sometimes called a Tuple or simply a Row.

---

3. Ensuring Data Quality: Keys and Integrity

For a database to work reliably, every single record must be easily identifiable and unique. This is achieved using special fields called Keys.

3.1. The Primary Key (PK)

The Primary Key (PK) is a field (or a combination of fields) that uniquely identifies every record in a table. It is the most important concept in data integrity!

Rules for a Primary Key:

1. Must be Unique: No two records can have the same Primary Key value. (E.g., No two students can have the same Student ID).
2. Cannot be Null: It must always have a value (it cannot be empty).

Analogy: Your Fingerprint or Passport Number

No matter how many people in the world share your name, only you have your specific passport number. The Primary Key is the table's "passport number"—it guarantees individuality.

3.2. The Foreign Key (FK)

The Foreign Key (FK) is a field in one table that refers to (matches) the Primary Key of another table. It is the link that connects two tables together.

Foreign Keys are essential because they allow us to build relationships without repeating large amounts of data.

How to Spot a Foreign Key:

If you see a field in Table A that is named after the Primary Key of Table B, it's likely a Foreign Key.

• Example: The "Customers" table has a Primary Key called Customer_ID.
• The "Orders" table needs to know which customer placed which order. So, the "Orders" table includes a field called Customer_ID.
• In the "Orders" table, Customer_ID is the Foreign Key.

This allows us to link an order back to the customer's full name and address without storing that name and address details repeatedly for every single order!

---

4. Relationships Between Tables (The Links)

The power of the Relational Database comes from the relationships established using Primary and Foreign Keys.

4.1. Avoiding Redundancy

If you had one massive table for all sales data, and a customer changed their address, you would have to update potentially hundreds of records. By splitting the data into a Customer table and an Order table, you only update the address once in the Customer table. This is how relationships reduce data redundancy (data duplication).

4.2. Understanding the One-to-Many Relationship (1:M)

The most common and important relationship you need to understand is One-to-Many (1:M).

This means that one record in the first table (the "one" side, which holds the Primary Key) can link to many records in the second table (the "many" side, which holds the Foreign Key).

Step-by-Step Example (1:M)

1. Table 1: Teachers (The 'One' side)
   • Fields: Teacher_ID (PK), Name, Department.
2. Table 2: Classes (The 'Many' side)
   • Fields: Class_ID (PK), Class_Name, Room_Number, Teacher_ID (FK).

The Connection:

• One teacher (from Table 1) can teach many different classes (in Table 2).
• We link them by placing the Teacher_ID (PK) from Table 1 into Table 2 as a Foreign Key (FK).

This setup correctly models the real world and prevents us from typing the teacher's full name and department into the record for every single class they teach!

You have successfully mastered the theoretical foundation of databases! You are now ready to learn how to communicate with them using SQL!