Relational Database Concepts

Welcome, students 🌟 In this lesson, you will learn the core ideas behind relational databases, the kind of database model used in many real systems such as school record systems, online stores, hospitals, and library catalogues. By the end of this lesson, you should be able to explain important database terms, describe how tables connect, and apply simple reasoning to database problems in IB Computer Science SL.

What is a relational database?

A relational database stores data in tables. Each table is made of rows and columns. A row is one record, and a column stores one type of data. For example, a school might use a table called $Student$ with columns like $StudentID$, $Name$, and $YearGroup$.

The word “relational” does not mean “friendship” or “connection” in a social sense. It means the data is organized into relationships between tables. These relationships let one table link to another using a shared value. This makes it easier to avoid repeating the same information many times.

For example, imagine an online store 🛒. Instead of writing the customer’s address in every order record, the store can keep customer details in a $Customer$ table and order details in an $Order$ table. The order table can store a $CustomerID$ to show which customer made the order. This is more efficient and reduces mistakes.

Two key ideas in relational databases are:

• Data is stored in tables.
• Tables are linked using keys.

This model is widely used because it is organized, flexible, and works well for large amounts of structured data.

Tables, fields, records, and keys

To understand relational databases, students, you need the basic vocabulary.

A table is a collection of related data.

A field is a column in a table.

A record is a row in a table.

A primary key is a field that uniquely identifies each record in a table.

A foreign key is a field in one table that refers to the primary key in another table.

Here is a simple example.

Student table

| $StudentID$ | $Name$ | $YearGroup$ |

|---|---|---|

| $S01$ | Amina | 10 |

| $S02$ | Leo | 11 |

| $S03$ | Priya | 10 |

In this table, $StudentID$ is a good primary key because each student has a unique ID.

Enrollment table

| $EnrollmentID$ | $StudentID$ | $Course$ |

|---|---|---|

| $E100$ | $S01$ | Biology |

| $E101$ | $S02$ | Computer Science |

| $E102$ | $S01$ | Mathematics |

In the $Enrollment$ table, $StudentID$ is a foreign key because it links each enrollment back to the correct student in the $Student$ table.

This kind of structure lets the database represent relationships clearly. A single student can have many enrollments. This is called a one-to-many relationship because one record in one table can connect to many records in another table.

Why keys matter

Keys are essential because they make data accurate and easy to connect.

A primary key must be unique and should not be $NULL$. If two records had the same primary key, the database would not be able to tell them apart. That would cause confusion and errors.

A foreign key must match an existing primary key value in the related table, or it may be $NULL$ if the relationship is optional. This helps maintain referential integrity, which means the links between tables stay valid.

For example, if an enrollment record contains $StudentID = S99$, but there is no student with $StudentID = S99$ in the student table, then the database has an invalid relationship. A relational database system can prevent this kind of error.

Think of a school office 🏫. If a permission slip says a student is in Year 10, but the student ID does not exist in the student register, the office would not trust that record. Databases use keys in a similar way to keep information reliable.

Other useful key terms include:

• Candidate key: any field that could uniquely identify a record.
• Composite key: a primary key made from two or more fields.
• Surrogate key: a system-generated key, often used when no natural key is suitable.

Relationships between tables

Relational databases are all about connections. The main relationship types are:

One-to-one

One record in table A connects to one record in table B. Example: each passport number belongs to one person, and each person has one passport record.

One-to-many

One record in table A connects to many records in table B. Example: one customer can place many orders.

Many-to-many

Many records in table A connect to many records in table B. Example: students can take many subjects, and each subject can have many students.

A many-to-many relationship is usually not stored directly. Instead, the database uses a link table or junction table to split it into two one-to-many relationships.

Example:

• $Student$ table
• $Subject$ table
• $StudentSubject$ table

The $StudentSubject$ table might contain $StudentID$ and $SubjectID$ as foreign keys. This design keeps the database organized and avoids repeating information.

This process is part of normalization, which means organizing data to reduce duplication and update problems. A well-designed relational database reduces wasted storage and lowers the risk of inconsistent data.

For example, if the price of a product changes in an online store, it is better to update one product record than to change the price in every order record. That is a major advantage of using related tables.

Avoiding redundancy and update anomalies

Redundancy means storing the same data more than once. Some repetition is unavoidable, but too much repetition can cause problems.

Three common database problems are:

• Update anomaly: one piece of data must be changed in several places, and one place gets missed.
• Insert anomaly: you cannot add a fact without adding unrelated data.
• Delete anomaly: deleting one record accidentally removes important information.

Example: suppose a database stores student details and course details in one giant table. If a student enrolls in three courses, their name and year group may be repeated three times. If the student changes year group, every repeated row must be updated. If one row is missed, the database becomes inconsistent.

By splitting data into related tables, relational databases reduce these problems. This makes them especially useful in systems that need accuracy, such as banks, hospitals, and exam boards 💡

Using relational database reasoning in IB Computer Science

In IB Computer Science SL, you may be asked to look at a database scenario and decide how to design tables or identify relationships. To do this well, students, follow these steps:

1. Identify the entities, which are the main objects being stored.
2. Choose a primary key for each entity.
3. Look for relationships between entities.
4. Decide whether the relationship is one-to-one, one-to-many, or many-to-many.
5. Add foreign keys or a link table where needed.
6. Check for redundancy and possible anomalies.

Let’s apply this to a library 📚.

Possible tables:

• $Book(BookID, Title, Author)$
• $Member(MemberID, Name)$
• Loan(LoanID, BookID, MemberID, DateOut, DateDue)

Here, $Loan$ connects books and members. One member can borrow many books over time, and one book can be borrowed many times over time. The $Loan$ table uses foreign keys to link the records.

This is a good example of relational thinking because it shows how the database stores facts once and uses keys to connect them.

Conclusion

Relational database concepts are a central part of structured data management in computing. Tables store data, keys identify records, and relationships connect tables in a clear and reliable way. When you understand primary keys, foreign keys, referential integrity, and the main relationship types, you can analyze real database systems and design better solutions.

For IB Computer Science SL, these ideas matter because they appear in problem-solving, design questions, and application scenarios across the Option Topic Bank. Whether the context is a school, shop, hospital, or library, relational databases help computers manage large amounts of information accurately and efficiently ✅

Study Notes

• A relational database stores data in tables made of rows and columns.
• A row is a record, and a column is a field.
• A primary key uniquely identifies each record in a table.
• A foreign key links one table to another table.
• Referential integrity means foreign key values must match valid primary key values, or be $NULL$ where allowed.
• Common relationship types are one-to-one, one-to-many, and many-to-many.
• Many-to-many relationships are usually handled with a link table.
• Normalization reduces redundancy and helps prevent update, insert, and delete anomalies.
• Relational databases are useful in real systems such as schools, hospitals, libraries, and online stores.
• In IB Computer Science SL, you should be able to identify entities, keys, and relationships from a database scenario.