A field that uniquely identifies each record is the:

primary key. The primary key must be unique and never empty. A foreign key refers to another table's primary key.

ClassID appearing in the STUDENT table, referring to CLASS, is a:

foreign key. It holds the primary key value of another table, which is exactly what makes it a foreign key and what links the two tables.

A many-to-many relationship is implemented using:

a third linking table. A field holds a single value, so neither side can store a list of the other. The linking table turns it into two one-to-many relationships.

In a one-to-many relationship, the foreign key is placed:

on the "many" side. Each record on the many side refers to exactly one record on the one side, which is a single value and therefore fits in a field.

A student's grade in a course should be stored in:

the linking ENROLMENT table. The grade needs both keys to identify it — it belongs to that student in that course, so it is an attribute of the relationship.

Which is the best primary key for a student record?

An auto-generated roll number. It is guaranteed unique and has no reason ever to change. Names repeat, dates of birth repeat, and addresses change.

Concurrency control exists to:

stop simultaneous updates corrupting data. Without it, two users saving changes to the same record at the same moment can leave the data in an inconsistent state or lose one update entirely.

A composite primary key is needed when:

no single field is unique. In an ENROLMENT table neither StudentID nor CourseID is unique alone, but the pair together identifies exactly one enrolment.

Basic Concepts and Terminologies of Databases — Computer Science Notes, Worked Examples and Quiz

Basic Concepts and Terminologies of Databases

Keys, relationships and the software that holds it all together

This topic appears in:

The relational model

Definition

DBMS — A database management system is the software sitting between users and the stored data. Every read and write goes through it, which is how it can enforce data types and rules, control who may see what, keep the data consistent when two people change it at once, and recover after a failure.

A relational database stores data in tables, each row a record and each column a field. Tables are connected not by pointers or physical links but by shared values — the student's roll number appearing in both the STUDENT table and the ENROLMENT table is what joins them.

That indirection is the whole design. Because the connection is a value rather than a location, tables can be reorganised, rows can move, and the relationship still holds.

The DBMS provides	Which means
Data independence	programs need not know how data is physically stored
Integrity enforcement	invalid data is rejected at the door
Security	permissions per user, per table, even per field
Concurrency control	two simultaneous updates cannot corrupt each other
Backup and recovery	the database can be restored after a crash
A query language	new questions answered without new programs

Keys

Keys are how rows are identified and how tables are joined, and the exam distinguishes several kinds.

A primary key uniquely identifies each record in a table. It must be unique, must never be empty, and should never change. A foreign key is a field in one table holding the primary key of another — that is the link. A candidate key is any field that could serve as the primary key; one is chosen and the rest become alternate keys. A composite key is a primary key made of two or more fields together, needed when no single field is unique.

On One to many, the foreign key goes on the "many" side — the class's key is stored in the student table, not the reverse. On Many to many, notice it cannot be done with two tables at all.

Why a name is never a primary key

Names are not unique — two students called Ali Khan is not a hypothetical. They also change, on marriage or by correction, and a changing primary key breaks every foreign key referring to it. A good primary key is meaningless: a roll number or an auto-generated ID has no reason ever to change, which is exactly what makes it safe.

Relationships

Two entities are related in one of three ways, and identifying which decides the table design.

One to one: each record on one side matches at most one on the other. Genuinely rare — usually the two entities should be one table, unless there is a reason to separate them such as differing access permissions.

One to many: the common case. One class contains many students; each student is in one class. Implemented by putting the "one" side's primary key into the "many" side's table as a foreign key.

Many to many: a student studies many subjects and each subject has many students. This cannot be represented with two tables and must be broken into two one-to-many relationships using a third linking table.

Worked example

A library has BOOK and MEMBER entities. A member may borrow many books over time, and a book may be borrowed by many members over time. Design the tables.

Identify the relationship: many to many, because both sides are "many" over time.The phrase "over time" is what makes it many-to-many rather than one-to-many.
A many-to-many relationship needs a linking table, so create LOAN.Neither BOOK nor MEMBER can hold a list of the other.
BOOK(BookID, Title, Author) and MEMBER(MemberID, Name, Address).Each has a meaningless, stable primary key.
LOAN(LoanID, BookID, MemberID, DateOut, DateDue).BookID and MemberID are foreign keys; the extra fields belong to the loan itself, not to the book or the member.
BOOK to LOAN is one to many, and MEMBER to LOAN is one to many.The many-to-many has become two one-to-many relationships, which tables can express.

Three tables: BOOK, MEMBER, and a LOAN linking table holding both foreign keys plus the loan dates.

Where the extra fields go

A linking table is not merely a pair of keys. Any attribute belonging to the relationship itself rather than to either entity lives there.

The date a book was borrowed is not a property of the book, which exists before and after the loan, and not a property of the member. It is a property of that particular loan. Likewise a grade belongs to a student's enrolment in a subject, not to the student and not to the subject. Recognising which attributes belong to the relationship is a standard exam question, and putting them in the wrong table is the standard error.

Before you leave this chapter

Tables are joined by shared values, not by physical links.
A DBMS gives independence, integrity, security, concurrency control, recovery and a query language.
Primary key: unique, never empty, never changing. A name is never suitable.
A foreign key holds another table's primary key, and lives on the "many" side.
Many to many needs a linking table, which also holds any attribute of the relationship itself.

Practice questions

6 questions · 20 marks · full working on every one

Try each one on paper first, then open the working. The marks are shown where they are actually awarded, because that is where they are actually lost.

Short questions

3 · 6 marks

Two marks each, in the style of the short-question section of the paper. Answer in two or three lines.

SQ1[2 marks]

Define a primary key and state two properties it must have.

Model answer

A primary key is a field, or combination of fields, that uniquely identifies each record in a table. It must be unique across all records and must never be empty. It should also never change.

Examiner tip. Uniqueness and not-null are the two the mark scheme wants. Stability is worth adding as it explains why names are unsuitable.

SQ2[2 marks]

What is a foreign key?

Model answer

A field in one table that holds the primary key value of another table, creating the link between them. For example, ClassID stored in the STUDENT table refers to the primary key of the CLASS table.

Examiner tip. Give an example naming both tables. The abstract definition alone often scores one of the two marks.

SQ3[2 marks]

Why is a student's name unsuitable as a primary key?

Model answer

Names are not unique — two students may share one — and they can change, which would break every foreign key referring to that record. A primary key should be a meaningless, stable identifier such as a roll number.

Examiner tip. Both reasons earn credit: not unique, and not stable. The consequence for foreign keys is what makes the second reason serious.

Solved numericals

2 · 8 marks

Full working, one step per line, with the marks shown where they are awarded.

N1[4 marks]

State four services provided by a database management system.

Full working

Data integrity — validation rules and constraints reject invalid data[1]
Security — access permissions per user, per table, or per field[1]
Concurrency control — simultaneous updates by different users cannot corrupt the data[1]
Backup and recovery, and a query language for answering unanticipated questionseither earns the mark[1]

Integrity, security, concurrency control, backup and recovery, and a query language.

Examiner tip. Concurrency control is the one candidates omit, and it is the reason a database beats a shared spreadsheet for more than one user.

N2[4 marks]

A driving school records instructors and students. Each student is taught by one instructor; each instructor teaches many students. Design the tables, identifying keys.

Full working

Relationship identified as one to many, from instructor to student[1]
INSTRUCTOR(InstructorID, Name, Phone)primary key underlined or stated[1]
STUDENT(StudentID, Name, Address, InstructorID)[1]
InstructorID in STUDENT is a foreign key referring to INSTRUCTOR — the key goes on the "many" sideplacing it on the wrong side is the standard error[1]

Two tables, with InstructorID as a foreign key in STUDENT.

Examiner tip. The foreign key always goes on the "many" side. Putting a list of students into the instructor table is impossible, since a field holds one value.

Long questions

1 · 6 marks

Theory and numerical together, as they appear in the long-question section.

LQ1[6 marks]

A college needs a database recording which students are enrolled in which courses, and the grade each student achieved in each course.

Identify the relationship between STUDENT and COURSE and explain why it cannot be stored in two tables.
Design the tables, showing primary and foreign keys.
Explain where the grade must be stored and why.

Mark scheme

Many to many — a student takes many courses and a course has many students[1]
A field holds a single value, so neither table can hold a list of the other; a third table is requiredthe reason, not just the assertion[1]
STUDENT(StudentID, Name) and COURSE(CourseID, Title)[1]
ENROLMENT(StudentID, CourseID, Grade) with a composite primary key, both fields also being foreign keysaccept a separate EnrolmentID[1]
The grade belongs in ENROLMENT[1]
Because it is a property of that particular student's enrolment in that particular course — not of the student, who has many grades, nor of the course, which awards manythe reasoning is the mark[1]

(a) many to many — a field cannot hold a list (b) STUDENT, COURSE and an ENROLMENT linking table (c) the grade goes in ENROLMENT, since it belongs to the relationship

Examiner tip. The rule for part (c): if an attribute needs BOTH keys to identify it, it belongs in the linking table. A grade means nothing without knowing which student and which course.