Relational modelling
導入済み
語彙
| English | 意味 |
|---|---|
| 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}
実践
A many-to-many relationship becomes —
難易度 1/5 · 100 コイン + 今日初回達成時の小ボーナス
The junction table holds the foreign-key pairs; neither end can hold the other.
English
Many-to-many needs a table
- 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}
実践
PRIMARY KEY (student, course, term) enforces —
難易度 1/5 · 100 コイン + 今日初回達成時の小ボーナス
The composite key rejects duplicate triples at the engine level.
English
The join path
- Which students take a department's courses? Student joins Enrollment on
student_id, joins Course oncourse_id, joins Department ondept_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}
実践
The join path Student-Enrollment-Course-Department yields — rows on the sample data
難易度 1/5 · 100 コイン + 今日初回達成時の小ボーナス
14 enrollments carry the path; each enrollment is one joined row.