A school has two students named Rohan Singh, both from Jind, both born in 2010. A teacher says, “Update Rohan’s marks.” Which Rohan? If people cannot tell rows apart, a database cannot either. How does a database pick out exactly one row, and how does it connect rows that live in different tables? Keys and relationships are the answer.
Tables, rows and columns
A table stores facts about one kind of thing, such as students. Each row describes one item. Each column holds one property and has a data type, such as whole number, text, date or decimal number. A well-formed table follows four rules:
- every column has its own name and one data type;
- every cell holds a single value, never a list;
- the order of rows and columns carries no meaning;
- no two rows are completely identical.
A cell with no value holds NULL. NULL means “unknown or not applicable”. It is not zero, and it is not an empty piece of text.
Keys: how rows are identified
A key is a column, or a group of columns, used to identify rows. Learn these words:
- Super key: any set of columns whose values are different for every row.
- Candidate key: a super key with nothing extra in it; remove any column and it stops being unique.
- Primary key: the one candidate key you choose as the official identifier. It must be unique and can never be NULL. A table has only one.
- Alternate key: a candidate key you did not choose.
- Composite key: a key made of two or more columns.
- Foreign key: a column whose values must match the primary key of another table. It is the link between tables.
A natural key comes from real life, like an admission number. A surrogate key is a number the database invents, like student_id 1, 2, 3, with no meaning outside the table. Surrogate keys never need to change, so other tables can safely point to them.
Choosing a key for students
Suppose a students table has admission_no, name, phone and email. The column name fails as a key because two students can share a name. The column phone is risky because brothers and sisters often share a parent’s number. But admission_no and email are different for every student, so both are candidate keys. The school picks admission_no as the primary key, and email becomes an alternate key. A designer may instead add a surrogate student_id and use that.
Relationships between tables
Tables are linked through foreign keys. There are three kinds of relationship.
| Relationship | Example | How it is stored |
|---|---|---|
| One-to-many | One class has many students; each student is in one class | Foreign key on the “many” side: students.class_id |
| Many-to-many | A student studies many subjects; a subject is studied by many students | A third table, called a junction table, holds both foreign keys |
| One-to-one | Each student has at most one library card | Foreign key that is also unique, or both sets of columns in one table |
The junction table for students and subjects is marks. Its primary key is the pair (student_id, subject_id), and it also records something about the relationship itself: the score. The crow’s foot < in this sketch marks the “many” side.
classes 1 ----< students 1 ----< marks >---- 1 subjects
The school database on paper
These four tables are used for the rest of the course.
| Table | Primary key | Foreign keys |
|---|---|---|
| classes | class_id | none |
| students | student_id | class_id points to classes |
| subjects | subject_id | none |
| marks | (student_id, subject_id) | student_id points to students; subject_id points to subjects |
Referential integrity is the rule that every foreign key value must exist in the table it points to. With it, MySQL refuses to add a student whose class_id is 9 when there is no class 9. It also refuses to delete a class while students still belong to it.
Design a library
A library lends books to members. Which tables do you need, and where do the foreign keys go?
Answer: Three tables: books, members and loans. A member borrows many books over time and a book is borrowed by many members, so the relationship is many-to-many and loans is the junction table. It holds member_id and book_id as foreign keys, plus issue and return dates. Because a member may borrow the same book twice, loans should have its own loan_id as the primary key.
Key points
- A table holds one kind of thing: rows are items, columns are properties with data types, and NULL means no value.
- A primary key is unique and never NULL; a foreign key points to the primary key of another table.
- Use a foreign key on the “many” side for one-to-many, and a junction table for many-to-many.
- Referential integrity stops rows from pointing at something that does not exist.