FIT2094 Chap.4 Logical Models and Database Definition
Logical Models and Database Definition
Logical modelling maps conceptual meaning and normalised relations into a design suitable for an RDBMS. Entities become relations, identifiers become candidate keys, one-to-many relationships place a foreign key on the many side, many-to-many relationships become associative relations, and weak identity includes the owner's key.
The normalisation results must be integrated so that the same relation and attribute names carry through the design.
Database definition makes the design executable. Oracle data types represent values, while PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL and CHECK constraints reject invalid states. A surrogate key may simplify references when justified, but alternate natural identity still requires uniqueness.
Generated schema code should be tested from an empty environment and compared with the model rather than trusted because generation completed.
Use a rule inventory to compare the diagram and the executable definition.
Each primary and alternate key should have a corresponding constraint, each relationship should yield the intended foreign key and nullability, and every bounded status or range should have an enforceable domain rule where practical. Test the script in an empty schema so old objects cannot hide missing statements.
Insert one valid parent-child pair, attempt an orphan, attempt a duplicate alternate key and try each boundary value. When a test behaves unexpectedly, repair the earliest design artefact that lost the rule and then regenerate or reconcile the downstream schema.
What this chapter covers
- 01
Mapping strong and weak entities to relations
- 02
Mapping one-to-many and many-to-many relationships
- 03
Primary keys, alternate keys and surrogate-key justification
- 04
Oracle data types and column nullability
- 05
CREATE TABLE, ALTER and DROP
- 06
Foreign keys, checks and attribute comments
- 07
Schema-generation order and reproducible testing
Worked example · free
Map and constrain a room-booking design
- +1Use room_id as the MEETING_ROOM primary key and preserve room_code with UNIQUE because it remains a candidate key.
- +2Use booking_id as the BOOKING primary key and add room_id as a NOT NULL foreign key because every booking names one room.
- +2Protect the rule that one room cannot have two bookings starting together with UNIQUE(room_id, start_time), subject to the stated scheduling rule.
- +2Use a positive-value CHECK for attendee_count and an appropriate rule for end_time being later than start_time when enforceable in the chosen design.
- +1Create the room table before the booking table, then test a duplicate room code, missing room reference and invalid attendee count.
Key terms
- Logical model
- A platform-independent relational design showing relations, attributes, keys and relationships in implementation-ready form.
- Surrogate key
- A system-generated identifier introduced for technical stability or reference simplicity while natural candidate-key rules remain enforced.
- Unique constraint
- A database rule preventing duplicate values or value combinations, commonly used to protect alternate candidate keys.
- Check constraint
- A database rule requiring each stored row to satisfy a Boolean condition over its values.
- Data definition language
- SQL statements that create, alter or remove database structures such as tables and constraints.
- Schema reproducibility
- The ability to create the intended database structures from the submitted definition in a clean environment with predictable results.
Logical Models and Database Definition FAQ
How does a conceptual model become a logical model?
Map entities to relations, identifiers to candidate keys and relationships according to cardinality. Place foreign keys on the many side, create associative relations for many-to-many cases and integrate every relation discovered through normalisation.
When is a surrogate key appropriate?
It can help when a natural key is long, unstable or widely referenced, but the decision needs a stated operational reason. Retain a UNIQUE constraint for the natural candidate key so duplicate business instances remain impossible.
Which constraints should appear in the schema?
Use primary and unique constraints for identity, foreign keys for references, NOT NULL for mandatory values and CHECK conditions for stable row-level domains. Each constraint should trace to a source-backed business rule.
Why test generated schema code?
Generation reproduces the model, including its mistakes. Running the schema in a clean context exposes dependency order, missing objects and unsupported definitions. Negative inserts then show whether the intended constraints actually reject invalid states.
Assessment move
Create a rule inventory before opening the modelling tool. For every relation, list the primary key, alternate candidate keys, mandatory attributes, foreign keys and bounded domains. Map relationships systematically and integrate the final normalised relations using stable names. Explain each surrogate key and preserve the natural uniqueness it replaces as a primary reference.
Generate the Oracle schema, compare every table and constraint with the model, and run it in a clean environment. Test one valid row and one invalid row for uniqueness, references and domains. Review the exact submitted model archive by extracting it elsewhere and opening both logical and relational designs.
Maintain a model-to-DDL matrix with one row per rule, then mark the exact constraint and negative test that proves each rule survives implementation.
