3.7.1 - Relational databases, tables and keys
A database stores related information in a structure that can be searched and updated. Learn how tables, records, fields and keys connect data without repeatedly storing the same facts.
Tables, records, fields and types
A database is an organised collection of related data; its structure gives meaning to the values being stored.
Relational database
A database that organises data into tables related through key fields.
A library stores members and their loans. A table holds records of one kind, such as members. Each row is a record, grouping the facts about one member. Each column is a field, holding one kind of fact, such as a member's name. A field's data type determines the kind of value stored, such as integer, text or date.
Member table
| MemberID (integer) | MemberName (text) | JoinDate (date) |
|---|---|---|
| 11 | Maya | 2027/01/08 |
| 12 | Idris | 2027/02/10 |
| 13 | Maya | 2027/03/02 |
The middle row is one record about Idris. MemberName is a field, while Idris is one value in that field. These are different levels of description. An integer identifier supports numeric equality checks; a name is text, even when it happens to contain digits. The dates here are displayed as year/month/day to avoid ambiguous day/month order.
This course uses table, record, field, data type and key when discussing both a planned database and an implemented one. You do not need to substitute abstract-model terminology to explain this example.
Primary and foreign keys
Keys let the database identify the intended record and relate records in different tables.
A primary key uniquely identifies each record in its table. In Member, MemberID is the primary key: 11, 12 and 13 identify different members. MemberName would be a poor key because two members are called Maya. A key must remain unique even when names or other ordinary facts match.
Loan table
| LoanID (integer, primary key) | MemberID (integer, foreign key) | BookTitle (text) |
|---|---|---|
| 101 | 11 | Coastal Life |
| 102 | 12 | Digital Worlds |
| 103 | 11 | Forest Walks |
A foreign key holds a value referring to a primary key in a related table. Loan.MemberID refers to Member.MemberID. Loan 103 belongs to member 11, so it is Maya who joined on 2027/01/08. It does not belong to member 13 simply because that member has the same name.
One member can have several loans, so a foreign-key value can repeat: member 11 appears in loans 101 and 103. LoanID cannot repeat because it is this table's primary key. The key's role depends on its table; the field named MemberID is primary in Member and foreign in Loan.
Continue this lesson.
You’re previewing NovaLearn without an account. Create a free account to read the rest.
Create free account