COMP5112 Chap.11 Relational Models and Database Design
Relational Models and Database Design
Design moves from meaning to access
The official syllabus links relational structure, algebra, SQL and constraints to entity-relationship modelling, functional dependencies, normalisation, file organisation, indexing, hashing and query processing. This chapter treats them as one pipeline.
Requirements identify entities and relationships, keys and dependencies shape relations, constraints preserve valid states, and workload evidence justifies physical access paths.
Conceptual design begins with the domain. Entities are distinguishable things, attributes describe them, and relationships state associations.
Cardinality and participation convert a diagram line into a rule: one customer may place many orders, each order may require one customer, and a relationship such as order product can carry quantity. Resolve these meanings before choosing table names or indexes because physical structures cannot repair an incorrect business model.
A relation contains tuples described by attributes.
Candidate keys uniquely and minimally identify tuples; a primary key is the selected candidate, and a foreign key links a value to a referenced relation under an integrity rule. Uniqueness observed in a small sample is not proof of a key.
Ask whether the property remains true for every legitimate future state and whether any attribute can be removed while identification remains.
Functional dependencies record semantic determination. If customer identifier determines customer address, repeating the address in every order stores one fact under the wrong key.
Normalisation decomposes such designs so each relation represents dependencies appropriate to its key, reducing insertion, deletion and update anomalies. A decomposition should preserve needed information through a lossless join and, where required, allow important dependencies to be enforced without reconstructing everything.
Relational operations describe results.
Selection filters rows, projection chooses attributes, and joins combine related tuples under a condition. Grouping and aggregation change the result grain, while ordering is a separate presentation request. Translate a query into these logical steps before considering execution.
Track expected row meaning after every join to catch accidental Cartesian products and distinguish row predicates from conditions on aggregate groups.
SQL expresses the same intent through clauses whose written and logical roles must be understood. Identify source relations and join conditions, restrict appropriate rows, form groups when aggregates are requested, filter groups, project outputs and order only if demanded.
Null handling, duplicates and constraint effects require explicit attention. A syntactically plausible statement is not enough; test it against a tiny instance with known expected rows.
Physical design follows workload evidence. A B-tree-style index can support selective equality lookup, ranges and useful order, while hashing is naturally suited to equality access under its design assumptions.
Composite-index attribute order determines which leading conditions can exploit it. Every index occupies space and adds maintenance to inserts, deletes and key updates, so recommendations should name the frequent query that benefits and the write load that pays.
Query processing connects logical requests to physical operators.
The optimizer may choose scans, index access and join strategies while preserving the same result. Data independence lets these choices change without rewriting the user query. A complete design argument therefore moves from requirements to entities, keys, dependencies, normalised relations, constraints, queries and finally access paths, verifying meaning before performance at every transition.
What this chapter covers
- 01
Relations, tuples and attributes
- 02
Primary and foreign keys
- 03
Entity-relationship design
- 04
Functional dependencies
- 05
Normalisation and anomalies
- 06
Indexes, hashing and query processing
Worked example · free
Separate customer facts from orders
- 3State the relevant dependencies.
- 3Create the two relations.
- 3Preserve the relationship with a key.
Key terms
- Relation
- A table-shaped set of tuples under a defined schema.
- Primary Key
- The selected attribute set that uniquely and stably identifies tuples.
- Foreign Key
- An attribute set referencing a key in another relation.
- Functional Dependency
- A domain rule stating that one attribute set determines another.
Relational Models and Database Design FAQ
Why is a sample with no duplicates not enough to prove a key?
A key follows the domain rules across all valid future states, not the accidental uniqueness of current rows. Ask whether two legitimate entities could share the value or whether it can change.
What problem does normalisation address?
It separates facts that depend on different keys so insert, update and delete operations do not create inconsistent or missing information through redundancy. The resulting relations give each fact one controlled place to be maintained.
How should I choose an index?
Start from frequent selection, join and ordering predicates, then weigh lookup benefit against storage and update maintenance. Equality and range predicates may favour different physical structures and key orders.
Exam move
Take one real system and move through the pipeline: requirements, ER model, keys, relations, dependencies, constraints, common queries and finally candidate indexes. At every transition, write the rule that justifies the next artifact. Test keys against plausible future states, verify decompositions by joining sample tuples, and predict result grain after each relational operation.
Recommend an access path only after recording query frequency, selectivity, ordering needs and the update cost it introduces. Recheck the final design against the original requirements to ensure that optimisation did not alter meaning.
Working through Relational Models and Database Design in COMP5112? Sia is AskSia’s AI Computer Science tutor — ask any COMP5112 Relational Models and Database Design question and get a clear, step-by-step explanation grounded in how COMP5112 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
