Multiple choiceQuestion 1 · Recall
Loading…
By the end you can use the terminology of a relational database correctly, and choose appropriate primary, candidate, secondary and foreign keys for a table.
Database books borrow words from mathematics, so most ideas have two names. Examiners accept either, but you should recognise both.
| Everyday word | Relational word | What it is |
|---|---|---|
| Table | Relation | All the data about one kind of thing |
| Row | Record or tuple | One complete item in the table |
| Column | Field or attribute | One property shared by every item |
An entity is the real-world thing the table describes: a student, a book, a loan. The table is how that entity is represented in the database.
Here is a small Book table.
| BookID | Title | Author | Year |
|---|---|---|---|
| B1042 | The Cartographer's Apprentice | R. Okafor | 2019 |
| B1088 | Salt and Semaphore | M. Lindqvist | 2021 |
| B1103 | A Short History of Rain | J. Adeyemi | 2015 |
BookID, Title, Author, Year.BookID is the primary key: every value is different, so it identifies exactly one book.Notice that Title could also be unique in this tiny table. Uniqueness in the data you happen to have is not enough; a key must be guaranteed unique by the rules of the system.
Primary key
A field (or combination of fields) that uniquely identifies each record in a table. It cannot be null, and no two records may share the same value.
Candidate key. Any field or combination of fields that could serve as the primary key, because it is unique and not null. A table may have several candidate keys; one is chosen as the primary key and the others are called alternate keys.
Secondary key. A field used to search for records, which is indexed to make that search fast. It does not have to be unique: a Surname secondary key might match many students. It is a search aid, not an identifier.
Foreign key. A field in one table that is the primary key in another table. It creates the link between the tables. In the diagram, Loan.StudentID is a foreign key referring to Student.StudentID.
A foreign key may repeat. One student can have many loans, so their StudentID appears in many Loan records. That repetition is the whole point: each row is a different loan by the same student.
Referential integrity
The rule that every foreign key value must match an existing primary key value in the related table, or be null. A database that enforces this cannot contain a loan for a student who does not exist.
An index is a separate structure that keeps field values in order with pointers to their records, much like the index at the back of a book.
Without an index, finding every book by a particular author means checking every row. With an index on Author, the DBMS jumps close to the right place and follows the pointers.
Indexes make searching faster but writing slower, because every insert, update or delete must also update the index, and they take extra storage. That is why you index the fields you search often, not every field.
A primary key must be a single field.
A primary key can be a composite key, made of two or more fields together. For example, a Loan table might use (StudentID, BookID, DueDate) if a student can borrow the same book more than once.
A foreign key must be unique.
A foreign key often repeats, because one parent record can be referenced by many child records. Uniqueness is the job of the primary key.
Any field with no repeated values in the current data is a good primary key.
A primary key must stay unique as the database grows. A phone number looks unique until two students share a household, or someone changes number. A meaningless identifier such as StudentID is safer.
An index changes the data stored in the table.
BookID, because no two books share a value and it cannot be empty."An index is additional structure pointing into the table. The rows themselves are unchanged.