Relational databases, step by step
A school’s marks table goes wrong, is split into linked tables and is joined up again with keys. This is lesson 9 of Grade 10 ICT, and the last part works through the exam questions that ask which tables to use. There is nothing to type: press Next and watch.
Part 1 of 7: One big table · step 1 of 7
One table for everything
A school keeps its first-term marks in one table. Each row is a record: everything about one student’s result. Each column is a field, such as Name or Marks.
| Admission No. | Name | Date of birth | Marks | Term | Year |
|---|---|---|---|---|---|
| 1426 | Kavindu | 2005.05.23 | 69 | 1 | 2014 |
| 1427 | Meenadevi | 2005.08.12 | 82 | 1 | 2014 |
| 1428 | Mohomed | 2005.02.07 | 47 | 1 | 2014 |
- PK (primary key) primary key, field name underlined
- FK (foreign key) foreign key
- 1 one record
- ∞ many records
Why one big table causes trouble
When everything is kept in one table, the same data is typed again and again. This is data duplication, and it brings these problems.
- No field is safe to use as the primary key, because the values repeat.
- Counting gives wrong answers: five records are over 60, but there are three students.
- Entering data is slower, because the name and date of birth are typed every term.
- The same name can end up spelled two ways in different records.
- Updating is harder: one change has to be made in every copy.
- Deleting is risky: a student has several records, and all of them must be found.
The same data in two tables
A relational database keeps the data in separate tables and links them. Here the student details are stored once, and each exam result is one short record.
| Admission No. | Name | Date of birth |
|---|---|---|
| 1426 | Kavindu | 2005.05.23 |
| 1427 | Meenadevi | 2005.08.12 |
| 1428 | Mohomed | 2005.02.07 |
| Admission No. | Marks | Term | Year |
|---|---|---|---|
| 1426 | 69 | 1 | 2014 |
| 1427 | 82 | 1 | 2014 |
| 1428 | 47 | 1 | 2014 |
| 1426 | 79 | 2 | 2014 |
| 1427 | 68 | 2 | 2014 |
| 1428 | 66 | 2 | 2014 |
Inserting, updating and deleting are all easier now. A new term adds records to the Marks table only, and a correction to a name is made in one cell.
Primary key and foreign key
| Question | Primary key | Foreign key |
|---|---|---|
| What it is | A field that identifies each record of its table uniquely. | A field that refers to the primary key of another table. |
| Can it repeat? | No. Every value appears once. | Yes. One value can appear in many records. |
| Can it be empty? | No. It can never be null. | Its values come from the primary key it refers to. |
| Example | Admission No. in the Student table. | Admission No. in the Marks table. |
| Its use | Finds exactly one record. | Builds the relationship between two tables. |
When no single field is unique, two or more fields can be the primary key together. That is a composite primary key, such as Admission No. and Sport No. in the Student_Sport table.
The three kinds of relationship
| Relationship | Example | How the records match |
|---|---|---|
| One-to-one | A student and their scholarship exam result. | One record in each table matches one record in the other. |
| One-to-many | A student and their fee payments. | One student has none, one or many payments. Each payment belongs to one student. |
| Many-to-many | Students and sports. | Many on both sides. A third table turns it into two one-to-many relationships. |
Which tables does a question need?
O/L papers give two or three linked tables and ask which of them a task uses. The last part of the lesson works through these questions with the Student, Student_Sport and Sport tables. Each question is shown on its own first. Work out your answer, then press Show answer to check it.
| The question asks | How to work it out | Example |
|---|---|---|
| Which tables are needed to show something? | Find the tables that hold the fields to be shown and the values the question gives. If those tables are not joined to each other, add the table that links them. | The names of the students who play cricket: Student, Student_Sport and Sport. |
| Write the new records. | Write one record for each new fact. If a new record refers to another new record, add that one first. | Student → (1430, Sanduni, 2005.11.30), then Student_Sport → (1430, S002, A). |
| Can this record be added? | Its primary key must not be in the table already, and each foreign key value must be in the table it refers to. | (1426, S001, B) cannot go into Student_Sport, because 1426 with S001 is already there. |
| Which table gets the new field? | Ask what the field describes. It goes in the table whose records are that thing. | Class describes a student: Student. Date joined describes one student in one sport: Student_Sport. |
Use no more tables than the question needs. To show the sports that student 1426 plays, Student_Sport and Sport are enough, because Student_Sport already holds the admission number.
Key terms
- Table
- වගුව
- Relational database
- සම්බන්ධක දත්ත සමුදාය
- Primary key
- ප්රාථමික යතුර / මුල් යතුර
- Composite key
- සංයුක්ත යතුර
- Foreign key
- ආගන්තුක යතුර
- Relationship
- සම්බන්ධතාවය
- Redundancy
- සමතිරික්තතාව
More to try
- Cover the text, press Next, and say what the highlight shows before you read it.
- Write down the two rules every primary key must follow.
- In the Marks table no single field is unique. Which fields together could be its primary key?
- A library has a Book table and a Member table. A member can borrow many books, and a book can be borrowed by many members over the year. Which kind of relationship is this, and what would the third table hold?
- True or false: the foreign key of one table is the primary key of another table.
- In the Fees table, which field is the primary key and which is the foreign key?
- The dates of birth of the volleyball players must be shown. Which tables are needed?
- Rajeev (1429) joins the elle B team. Write the new record to be added.
- Each sport gets one teacher in charge, and a Teacher table is added. Which table gets the foreign key Teacher No., so that no teacher’s name is typed twice?
This lesson is part of the Grade 10 unit on databases. Practise with the Grade 10 ICT past papers.