BISM1201 Chap.9 Relational Databases and ERD Design
Relational Databases and ERD Design
Relational databases organise entity types into tables and connect records with keys. An entity is a kind of thing the organisation stores; an instance is one occurrence; attributes describe it; a record is one row; and a field is one value. A primary key uniquely identifies a record, while a foreign key references a related record. Business rules determine cardinality and modality in both directions.
Normalisation reduces repeated facts and the update, insertion or deletion anomalies they create. Many-to-many relationships require a linking entity so the relationship and its own attributes can be represented through two one-to-many connections.
What this chapter covers
- 01
Entities, instances, attributes, records and fields
- 02
Primary keys, foreign keys and integrity
- 03
Normalisation and data anomalies
- 04
Cardinality and optional or mandatory modality
- 05
Linking, lookup, domain and weak entities
Modelling community workshop bookings
- +1Identify Customer and Workshop as domain entities with their own primary keys.
- +1State the many-to-many business relationship in both directions.
- +1Create Booking as a linking entity between Customer and Workshop.
- +1Place customer_id and workshop_id as foreign keys in Booking.
- +1Store relationship facts such as booking status or attendance in Booking.
- +1Add referential and duplicate-booking controls that reflect the business rules.
Key terms
- Relational database
- A structured collection of related tables whose records connect through defined keys and integrity rules.
- Primary key
- An attribute or attribute combination that uniquely identifies every record in an entity and cannot be missing.
- Foreign key
- An attribute that references a related record’s primary key and supports referential integrity between entities.
- Data normalisation
- The analysis and decomposition of data structures to reduce redundancy, protect integrity and improve maintainability.
- Relationship cardinality
- The allowed number of instances on each side of a relationship, such as one-to-one or one-to-many.
- Linking entity
- An entity that resolves a many-to-many relationship and stores the foreign keys and relationship-specific facts.
Relational Databases and ERD Design FAQ
How do I choose an entity?
Choose a distinct business concept with multiple instances and facts the organisation needs to store. Avoid turning every report heading into an entity; derive candidates from business rules and required operations.
Why are primary and foreign keys different?
A primary key identifies a record inside its own entity. A foreign key stores the identifier of a related record, making the relationship operational and allowing the database to enforce referential integrity.
What problem does normalisation solve?
Normalisation reduces repeated facts that create inconsistent updates, blocked insertions or accidental loss during deletion. It gives each fact an appropriate home while preserving connections through keys.
How is a many-to-many relationship implemented?
Create a linking entity between the two domain entities. It carries foreign keys to both sides and can store facts about the relationship, turning the design into two implementable one-to-many relationships.
Exam move
Take five business-rule sentences and rewrite every relationship in both directions. Mark cardinality and modality before adding attributes. For one repeated spreadsheet, identify update, insertion and deletion anomalies, then decompose it and show keys. Validate the final ERD by walking through create, update and delete events rather than judging the diagram by appearance.
Use noun testing carefully.
A noun becomes an entity when the organisation needs multiple identifiable instances and facts about them, not merely because it appears in a sentence. For each candidate, propose a primary key and two meaningful attributes. Remove derived report labels and vague groupings that have no independent lifecycle.
Practise relationship validation with event questions. Can the parent exist before the child?
Can the child move to a different parent? May there be several related records at one time or across history? Does deletion remove evidence the organisation must retain? These questions reveal cardinality, modality and temporal needs more reliably than copying crow’s-foot marks from a memorised picture.
For normalisation, start with one deliberately messy table containing repeating facts and multiple values.
Demonstrate an update anomaly, an insertion anomaly and a deletion anomaly using specific rows. Decompose it into domain and linking entities, mark every primary and foreign key and prove that the original business event can still be reconstructed. Finish with integrity rules for missing parents, duplicate links and changes over time.
The goal is not the largest number of tables; it is a stable home for each fact and reliable relationships between them.
Populate the model with a minimal set of sample records. Attempt a valid parent creation, an invalid child reference, a duplicate link and a deletion that would remove needed history. Write the integrity response expected in each case. Then ask one historical question and one current-state question.
If the schema cannot answer both without overwriting facts, add the missing event, effective date or relationship entity before redrawing the ERD.
