Relational Database Concepts

Welcome, students! 🌟 In this lesson, you will learn how relational databases organize data, why they are widely used in real systems, and how to reason about them in IB Computer Science HL. By the end, you should be able to explain key terms, describe how tables are related, and apply basic database ideas to solve problems such as storing student records, library books, or online orders.

Objectives

Explain the main ideas and terminology behind relational database concepts.
Apply reasoning about keys, relationships, and normalization.
Connect relational databases to practical systems used in school and industry.
Summarize how relational databases fit into the Option Topic Bank.
Use examples to support your understanding in IB Computer Science HL.

Relational databases are important because they help computers store large amounts of information in a structured way. Instead of keeping everything in one huge list, data is split into tables that are linked together. This makes information easier to search, update, and protect from errors. 📊

What a Relational Database Is

A relational database stores data in tables. Each table has rows and columns. A row is one record, and a column is one field or attribute. For example, a school database might have a Students table with columns like StudentID, Name, and Class.

The word relational means the tables are connected by shared values. A table can relate to another table using a key field. This is much better than repeating the same information many times. For example, instead of writing a teacher’s name in every student record, the database can store teacher details once in a separate table and link them.

This design is useful because it reduces duplication, improves consistency, and makes updates easier. If a teacher changes classroom, you update one record instead of many. That saves time and reduces mistakes. ✅

A relational database system usually includes a DBMS (database management system). This is the software that lets users create, read, update, and delete data. Examples include MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.

Core Terminology You Must Know

To understand relational databases well, students, you need the main terms.

A table is a set of related data.

A record is one complete row in a table.

A field is one column in a table.

An attribute is another word for field.

A domain is the set of valid values for a field. For example, a field for age might only allow whole numbers from $0$ to $120$.

A primary key is a field, or combination of fields, that uniquely identifies each record in a table. For instance, StudentID can be a primary key because no two students should have the same ID.

A foreign key is a field in one table that refers to the primary key in another table. It creates a link between tables. For example, an Orders table may contain CustomerID, which refers to a customer’s record in a Customers table.

A candidate key is any field that could uniquely identify a record. One of the candidate keys is chosen as the primary key.

A composite key uses more than one field to form a unique identifier. This is useful when one field alone is not enough.

A relationship describes how records in one table are connected to records in another. Common relationship types include one-to-one, one-to-many, and many-to-many.

Tables and Relationships in Real Life

Imagine a school library system. There could be a Books table and a Loans table.

Books might include BookID, Title, and Author.
Loans might include LoanID, BookID, StudentID, and DueDate.

Here, BookID in Loans is a foreign key pointing to BookID in Books. This tells the system which book was borrowed. The same idea works for StudentID linking to a Students table.

A one-to-many relationship is common. One student can borrow many books over time, but each loan record belongs to one student. One customer can place many orders, but each order belongs to one customer.

A many-to-many relationship happens when multiple records in one table are linked to multiple records in another. For example, students and subjects often have a many-to-many relationship: one student studies several subjects, and one subject has many students. In a relational database, this is usually handled with a third table, often called a junction table or link table.

For example:

Students(StudentID, Name)
Subjects(SubjectID, SubjectName)
Enrollments(StudentID, SubjectID)

The Enrollments table stores pairs of IDs so each student-subject link is represented clearly. This is a neat solution because it avoids repeating subject names or student details many times. 🎯

Why Normalization Matters

Normalization is the process of organizing data to reduce duplication and prevent update problems. It helps make databases more reliable.

Without normalization, the same information may appear in many rows. That can cause three classic problems:

Insertion anomaly: you cannot add a fact without adding extra unrelated data.
Update anomaly: you must change the same fact in many places.
Deletion anomaly: deleting one record accidentally removes important information.

For example, if a table stores student names, class names, and teacher names all together, then the teacher’s name may appear in many rows. If the teacher changes, every row must be updated. If one row is missed, the database becomes inconsistent.

Normalization splits data into well-designed tables. A common goal in school-level database design is to make sure each table stores one type of thing, such as students, subjects, or teachers, and then connect them using keys.

In IB Computer Science HL, you should understand the purpose of normalization even if you do not need to perform advanced formal proofs. The main idea is that a good database design should reduce repeated data and keep information accurate. 📚

Integrity, Accuracy, and Security

Relational databases support data integrity, which means data is correct and consistent.

Entity integrity means that every table has a primary key and that primary key values are unique and not null. A null primary key would fail to identify a record.

Referential integrity means foreign key values must match an existing primary key in the related table, or be null if allowed. This prevents broken links. For example, a loan should not refer to a student ID that does not exist.

These rules are important because they protect the database from logical errors. If a table contains invalid links, queries may return wrong results or crash the system.

Security is also important. A DBMS may use user permissions so that not everyone can edit every table. For example, a librarian may update book loans, while a student can only view their own borrowed items. Databases may also use authentication, backups, and encryption to protect information.

This matters in real life because databases often store sensitive data such as medical records, exam results, or personal addresses. 🔒

Queries and Simple Problem Solving

A big advantage of relational databases is that users can ask questions using queries. A query is a request for data.

For example, you might ask:

Which students are in Year 12?
Which books are overdue?
Which products have low stock?

The standard language for relational databases is SQL, which stands for Structured Query Language. SQL allows users to select data, insert new records, update records, and delete records.

A simple query may look like this:

$$\text{SELECT Name FROM Students WHERE Class = '12A';}$$

This means: show the names of students whose class is $12A$.

In IB questions, you may need to reason about how a database schema supports a query. For example, if a school wants to list all students taking Mathematics, the Students, Subjects, and Enrollments tables must be linked properly. Without correct relationships, the query would not work efficiently or accurately.

You may also be asked to identify which key should be used in a table. A good rule is: choose a field that is unique, stable, and meaningful enough to identify each record. If a field may change often, it is usually a poor choice for a primary key.

How This Fits the Option Topic Bank

Relational database concepts belong in the Option Topic Bank because they extend the core database ideas from the main course into more specialized reasoning. This topic helps you move from simple definitions to deeper understanding of design, relationships, and practical system behavior.

In the broader context of Option Topic Bank, relational database knowledge connects to:

data modeling and system design,
file and data organization,
application development,
data security and integrity,
and real-world information systems used in business, education, and healthcare.

This means relational databases are not isolated facts to memorize. They are part of a bigger skill set: analyzing data needs, designing efficient structures, and explaining why one design is better than another. That is exactly the kind of reasoning IB Computer Science HL values.

When you study this topic, focus on both the technical terms and the practical purpose. For example, knowing what a foreign key is matters, but so does understanding why it prevents duplicate information and invalid records.

Conclusion

Relational databases organize data into linked tables so information can be stored efficiently, accessed quickly, and kept accurate. The most important ideas are tables, records, fields, primary keys, foreign keys, relationships, normalization, and integrity. These concepts are widely used in real-world systems such as school databases, shopping websites, and hospital records.

For IB Computer Science HL, you should be able to explain these ideas clearly, apply them to examples, and reason about why a relational design is better than a poorly structured one. If you understand how tables connect and why keys matter, you are well prepared for questions in the Option Topic Bank. 🚀

Study Notes

A relational database stores data in tables made of rows and columns.
A row is a record; a column is a field or attribute.
A primary key uniquely identifies each record and cannot be null.
A foreign key links one table to another and supports relationships.
One-to-many and many-to-many relationships are common in database design.
Many-to-many relationships are often solved using a junction table.
Normalization reduces duplication and helps avoid insertion, update, and deletion anomalies.
Entity integrity protects primary keys; referential integrity protects links between tables.
SQL is used to query and manage relational databases.
Relational database concepts are an important part of the Option Topic Bank because they support real-world data modeling and problem solving.