Skip to content
VJ Classes

One place to learn science, commerce, arts, computers and life skills

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.

RelationshipExampleHow it is stored
One-to-manyOne class has many students; each student is in one classForeign key on the “many” side: students.class_id
Many-to-manyA student studies many subjects; a subject is studied by many studentsA third table, called a junction table, holds both foreign keys
One-to-oneEach student has at most one library cardForeign 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.

TablePrimary keyForeign keys
classesclass_idnone
studentsstudent_idclass_id points to classes
subjectssubject_idnone
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.