Loading...
4.10.1, 4.10.2DatabasesCore
Entity Models and Relational Keys
A database model describes the entities and relationships required by a problem. Choose identifiers and cardinalities from stated rules rather than from the sample rows alone.
What you'll be able to do
- Construct entity descriptions and relationship models
- Use primary, composite and foreign keys
- Resolve a many-to-many relationship with a linking entity
1
Identify entities and attributes
An entity represents a relevant kind of thing, such as Student or Course. Attributes describe instances, such as a student’s name. An entity description lists these attributes and identifies the key; a relational implementation stores corresponding rows and columns under an agreed schema.
A name is not automatically a suitable unique identifier. Two students can share a name and a person’s name can change. Choose a stable key from the requirements or introduce an identifier. A candidate key is a minimal unique identifier; one is selected as the primary key.
2
Express the relationship rules
Cardinality describes how many instances may participate, such as one course having many enrolments. A many-to-many relationship between students and courses can be represented by Enrolment, with one row for each allowed student/course combination.
A composite primary key can combine StudentID and CourseID when the combination uniquely identifies an enrolment under the stated rules. A foreign key references a suitable key in another relation, helping enforce valid relationships. A foreign key need not itself be unique: many enrolments can reference one student.
3
Model constraints beyond sample data
An entity relationship diagram makes relationship multiplicity visible. Optionality distinguishes whether a relationship is required or may be absent. A small sample containing only one enrolment per student does not prove that the real rule is one-to-one.
Translate the model into relations and check insertion/deletion cases. Does a new student require an enrolment immediately? Can deleting a course leave invalid enrolment references? Constraint and deletion policies should reflect the requirements rather than be guessed from the most convenient screen design.
| Relation | Attributes | Key role |
|---|---|---|
| Student | StudentID, Name | StudentID primary key |
| Course | CourseID, Title | CourseID primary key |
| Enrolment | StudentID, CourseID, Score | Composite primary key; both IDs also foreign keys |
4
Work through the problem
Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.
Example 1Why is StudentID alone not a sufficient enrolment key if a student can take several courses?
1Several enrolment rows can legitimately share the same StudentID.
2The student/course combination identifies the distinct relationship under the stated rule.
AnswerUse a suitable composite key or another key with an equivalent uniqueness constraint.
Example 2Can several Enrolment rows reference the same CourseID?
1Yes: a course can have several enrolled students.
2The referenced course key is unique, while repeated foreign-key values express the many side.
AnswerYes, provided the references identify a valid course.
Common mistakes to avoid
Assuming a familiar name is unique.
Check stability and uniqueness requirements.
Confusing a referenced key’s uniqueness with the foreign-key column.
Identify the one and many sides.
Key takeaways
- Entities and attributes model the domain.
- Keys identify rows and connect relations.
- Cardinality comes from stated requirements.
Test yourself
Answer a few questions, then review anything you missed.
Next step
Browse practice topicsKeep practising
Start with the quiz above to check the key ideas. You can also choose another topic from the practice library.