Designing a database
Introduced| English | Meaning |
|---|---|
| relational database/rɪˈleɪʃənl ˈdeɪtəbeɪs/ | A database that organises data into tables connected through matching key values. |
| tables/ˈteɪblz/ | A collection of data organised into rows and columns. |
| entities/ˈentɪtiz/ | Distinct objects or concepts represented in a data model, such as customers or products. |
| primary key/ˈpraɪməri kiː/ | A field or combination of fields that uniquely identifies each record in a database table. |
| foreign key/ˈfɒrən kiː/ | A field or group of fields that refers to a candidate key in another or the same database table. |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | Organising relational database tables to reduce unnecessary duplication and avoid update, insertion and deletion anomalies. |
One renamed course now has two titles
- A school copies a course title into every enrolment row. Renaming some copies leaves two conflicting titles for the same course.
- A relational database 关系数据库 stores rows in tables 表. First identify the entities 实体, such as students and courses, and the relationships between them.
Separate identity from a changeable label
- A primary key 主键 uniquely identifies a row and cannot be null. A stable student ID lets a name change without changing the student’s identity.
- A natural key can work when its uniqueness and stability are guaranteed. A name is a poor key here because different students can share it; an email may also change.
Students can share names and change email addresses. Which is the best key for this design?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
Choose a stable identity rather than a label that repeats or changes.
Represent the many-to-many relationship
- One student can take many courses; one course can have many students. Use an enrolments table with one row for each student–course pair.
- A foreign key 外键 links each enrolment to a valid student or course key. A composite primary key
(student_id, course_id)prevents the same pair appearing twice.
Which design represents students taking many courses and courses having many students?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
Each enrolment records one student–course pair.
What kind of key links an enrolment to an existing course key?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
A foreign key represents and constrains this reference when enforcement is enabled.
Which constraint prevents the same student–course pair appearing twice?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
The composite primary key identifies the pair.
Store a course fact with the course
- Normalisation 规范化 organizes tables using their dependencies to reduce inconsistent repetition. In this design, a course ID determines its title, so store the title in courses.
- Repeated foreign-key values are expected: many enrolments can refer to one course. “Never repeat any value” is not a useful database design rule.
Repeating the same course ID in several enrolment rows is always a normalisation error.
Difficulty 1/5 · 100 coins + a small bonus on first completion today
Repeated references are expected. Copying the course title into each relationship row creates the update problem here.
Enable and test the constraints
- In SQLite, enable foreign-key enforcement on each connection with
PRAGMA foreign_keys = ONbefore the work begins. Declaring a relationship alone is not enough. - Test a valid enrolment, a duplicate pair and an enrolment referring to a missing course. Constraints should reject the last two; they do not decide which caller has permission.
PRAGMA foreign_keys = ON;
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (
student_id INTEGER REFERENCES students(id),
course_id INTEGER REFERENCES courses(id),
PRIMARY KEY (student_id, course_id),
CHECK (student_id IS NOT NULL AND course_id IS NOT NULL)
);
Declaring SQLite foreign keys means they are enforced on every connection without enabling them.
Difficulty 1/5 · 100 coins + a small bonus on first completion today
Enable PRAGMA foreign_keys = ON on each connection and verify the behaviour.
Check the design against a new case
- For library loans, a book title is not necessarily a unique physical copy. Identify the borrower, the copy and the loan event before choosing keys.
- Decide whether repeat events are allowed. A student–course pair can identify one enrolment here, but a borrower–copy pair cannot distinguish repeated loans on different dates.
Choose keys for the identity you need. Separate entity facts from relationship rows, then verify uniqueness and reference constraints with concrete cases.
Suggest entities and keys for a library where a borrower may borrow the same physical copy more than once.
Difficulty 3/5 · 200 coins + a small bonus on first completion today
Use borrower ID, copy ID and a separate loan-event identity with the required event details.
Why is borrower–copy alone insufficient as a key for repeated loans?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
The key must distinguish the actual events you want to record.