Concept of a Relational Database

A table has rows and columns. A record is a row containing related information about one item. A field is a named column holding a particular kind of information, such as FirstName or TestScore. These give the data a clear structure: a student's first name and last name have separate, labelled places.

Why separate the data?

Suppose a school needs to store students' details and their test results. If it puts everything into one table, it may repeat a student's name and date of birth every time that student takes a test. Repeated entries can become inconsistent: a name might be entered in the wrong order, or a date might be entered differently.

Instead, the school can organise its relational database into three tables:

• Student stores each student's details.
• Test stores information about each test, including its subject and maximum mark.
• StudentTest stores students' scores and identifies which student took which test.

This separates a student's details from their individual results. The student's details can be stored once, while several results link back to that student. It reduces unnecessary repetition and means those details do not have to be changed separately in every result record.

How the tables are linked

Separating information is only useful if the database can connect it again. A primary key uniquely identifies a record in a table. In the Student table, StudentID identifies a particular student; in the Test table, TestID identifies a particular test. Names are less suitable for identifying students because different students can share a name.

A foreign key refers to a primary key in another table. In StudentTest, StudentID links a result to the correct student, while TestID links it to the correct test.

The links depend on matching values. A result's StudentID must match the StudentID of the student who earned that result. Its TestID must match the identifier of the test they took. Merely giving columns the same name does not connect their records.

Several result records can contain the same student identifier because one student can take several tests. Similarly, several results can refer to the same test because several students can take it.

Bringing the information together

Using these links, the database can retrieve information from several tables together. For example, it can combine a student's first and last names from Student, their score from StudentTest, and the subject and maximum mark from Test.

The information remains organised in separate tables, but the matching keys allow it to be used together. That combination of structured tables and links between related records is the central idea of a relational database.