FIT2094 Chap.1 Database Foundations and Conceptual Models
Database Foundations and Conceptual Models
Database foundations and conceptual modelling connect an information problem to a defensible representation of the domain. A database management system coordinates shared data, access and change, but it can enforce only the rules that the design expresses. Conceptual modelling therefore begins before Oracle tables.
It identifies entities with independent identity, attributes that describe them, and relationships that capture associations between roles. Cardinality expresses the maximum participation; optionality expresses the minimum. Strong entities can be identified independently, while weak entities depend on an owner for identity.
The practical method is to translate source prose in passes.
First identify persistent things and candidate identifiers. Next state every relationship as a sentence in both directions. Then add non-key attributes to the entity or association they truly describe. Repeated pairings or facts about a pairing often signal an associative entity.
A model is complete only when a reviewer can connect every symbol to a business rule and can name an invalid state the diagram excludes.
A productive review uses counterexamples rather than visual preference. Give two legitimate instances the same proposed identifier and ask whether the rule still distinguishes them.
Create a new parent with no children to test optional participation, then create a child without an owner to test the mandatory side. Move an attribute between an entity and an association and ask which occurrence it actually describes. These probes reveal whether the diagram preserves repeatable events, independent lifecycles and minimum participation.
Record only narrow assumptions where the stated rules are silent, because an unsupported mandatory link can reject valid data as easily as a missing link can admit invalid data.
What this chapter covers
- 01
Why organisations use managed databases instead of repeated files
- 02
Entities, attributes and independently maintained identity
- 03
Relationships, roles and associative entities
- 04
Maximum cardinality and minimum participation
- 05
Strong entities, weak entities and identifying relationships
- 06
Reading an ER diagram back into business-rule sentences
- 07
Testing keys with legitimate duplicate scenarios
Worked example · free
Model a community tool-booking service
- +2Create MEMBER and TOOL as strong entities because each has its own identity and maintained facts. Choose source-supported candidate identifiers for both.
- +2Resolve the repeating member-tool association with BOOKING. Give BOOKING its own identifier when the same member may book the same tool more than once.
- +2Place start time, planned return time and collection status on BOOKING because they describe one occurrence of the association, not a member or tool in general.
- +1Read both relationships: each booking belongs to exactly one member and one tool; a member or tool may have no booking yet and may participate in many bookings over time.
- +1Test a repeat booking for the same member and tool. The model must keep both occurrences distinct without copying member or tool attributes into BOOKING.
Key terms
- Database management system
- Software that mediates controlled access to structured shared data, coordinating definition, retrieval, change, security and concurrent use.
- Entity
- A distinguishable thing or concept about which the database maintains facts and identity.
- Relationship
- A named association between entity roles whose cardinality and participation encode business rules.
- Cardinality
- The maximum number of relationship occurrences in which one entity instance may participate.
- Participation
- The minimum involvement of an entity in a relationship, commonly optional or mandatory under the stated rule.
- Associative entity
- An entity introduced for a repeating association, especially when the pairing has its own attributes or identity.
Database Foundations and Conceptual Models FAQ
How do I decide whether a noun should become an entity?
Ask whether instances require independent identity, retained facts and a lifecycle. A word that merely describes another entity is usually an attribute. A verb that links roles is usually a relationship. If a repeated pairing has its own facts, it often deserves an associative entity.
What is the difference between cardinality and participation?
Cardinality states the maximum, such as one or many. Participation states the minimum, such as zero or one. Read the rule in both directions because one end may be optional and many while the other is mandatory and exactly one.
Why is a relationship sometimes turned into an entity?
A many-to-many relationship cannot be implemented directly in the relational model, and the pairing may own attributes such as date, role or quantity. An associative entity gives each occurrence a place for identity and facts while linking the parent entities.
How can I test a proposed identifier?
Invent two legitimate instances that are as similar as the rules allow. If they can share the proposed value, the identifier is incomplete. Check repeat events over time, recycled codes and composite combinations before accepting a key.
Assessment move
Practise modelling with short, unfamiliar scenarios. Mark persistent nouns, identity statements, connecting verbs and quantity words before drawing. Build entities and keys first, then relationships, then non-key attributes. For each relationship, write a forward and reverse sentence and compare the minimum and maximum at both ends with the scenario.
Challenge every key with a legal duplicate, especially repeated events involving the same parents. Finish by tracing every required fact to one place and naming one invalid state the model prevents. When assumptions are unavoidable, state them narrowly and keep them separate from facts supplied by the problem. Redraw only after the rule inventory is stable; visual neatness should not drive the model.
Keep a correction log that states the rule behind every changed entity, key or cardinality so later revisions remain explainable rather than cosmetic.
