Relational modelling

Từ vựng

English	Ý nghĩa
relationship/rɪˈleɪʃənʃɪp/	relationship
databases/ˈdeɪtəbeɪsɪz/	An organised collection of data that can be accessed and managed.
entity/ˈentɪti/	A thing we count (a Student); a relationship connects entities (enrolls).
cardinality/ˌkɑːdɪˈnælɪti/	The number of elements in a collection; in database relationships, the permitted numbers of records associated with a record of another entity.
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.
referential integrity/ˌrefəˈrenʃl ɪnˈteɡrɪti/	referential integrity

English

Entities, relationships, keys

A databases model is a set of true sentences about the domain. An entity is a thing we count (a Student); a relationship connects entities (enrolls). Cardinality at each end — one or many — becomes keys and tables.
A primary key identifies a row uniquely; a foreign key references it, and the engine enforces referential integrity: no enrollment can name a missing student.

{exercise:uni-cs0801-model}

Luyện tập

A many-to-many relationship becomes —

Độ khó 1/5 · 100 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay

English

A student enrolls in many courses; a course holds many students: the relationship becomes its own junction table of foreign-key pairs.
Rules live in keys: PRIMARY KEY (student_id, course_id, term) makes a second identical enrollment impossible — rejected by the engine, not the application.

{exercise:uni-cs0801-junction}

Luyện tập

PRIMARY KEY (student, course, term) enforces —

Độ khó 1/5 · 100 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay

English

Which students take a department's courses? Student joins Enrollment on student_id, joins Course on course_id, joins Department on dept_id — three joins, each following a foreign key.
The sample data: 8 students, 6 courses, 14 enrollments — the join path yields 14 rows before filtering. Never store a student's name inside Enrollment: it duplicates and drifts.

Example

Surrogate IDs over emails as keys: emails move, surrogate keys never do.

{exercise:uni-cs0801-join}

Luyện tập

The join path Student-Enrollment-Course-Department yields — rows on the sample data

Độ khó 1/5 · 100 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay