Relational modelling

Vocabulário

English	Significado
relationship/rɪˈleɪʃənʃɪp/	An association between entities in a data model.
databases/ˈdeɪtəbeɪsɪz/	An organised collection of data that can be accessed and managed.
entity/ˈentɪti/	An identifiable thing represented in a data model, such as a student or course.
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/	Ensuring that a non-null foreign-key reference identifies an existing referenced key.

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}

Prática

A many-to-many relationship becomes —

Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje

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}

Prática

PRIMARY KEY (student, course, term) enforces —

Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje

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}

Prática

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

Dificuldade 1/5 · Moedas 100 + pequeno bônus na primeira conclusão hoje