INFS2200 Chap.1 Relational Foundations and Functional Dependencies
Relational Foundations and Functional Dependencies
Relational design becomes coherent when redundancy, anomalies, functional dependencies and keys are treated as one reasoning chain. This chapter tests dependencies as domain rules, computes key closure and minimality, distinguishes candidate, primary and foreign keys, and uses grain statements to keep facts at the right level.
Its original examples show why repeated facts create insertion, update and deletion problems, then repair the schema without losing the relationship between entities.
A dependency is stronger than a pattern in a sample. It describes every valid state allowed by the domain, so a legitimate counterexample is the fastest validity test.
If two possible tuples can share the proposed determinant while differing on the dependent value, the dependency does not hold. Key reasoning builds on that foundation. Attribute closure proves whether a set determines the full schema, and minimality removes every attribute that does not contribute to identity.
The chapter keeps key roles separate.
A superkey may contain unnecessary columns, a candidate key is minimal, a primary key is the candidate selected for implementation, and a foreign key represents a relationship to a key elsewhere. The design move is to state one row's grain in plain language, store each independent fact at that grain and preserve links through enforceable references.
This makes the reason for a decomposition visible instead of treating extra tables as an automatic improvement.
Worked examples use fresh enrolment, supplier and library-copy schemas. They show the full answer route: name the repeated fact, write the dependency, compute closure, test minimality, identify the anomaly and describe the repaired relation grains.
Each practice answer closes with the database consequence, because notation earns its value by protecting identity, reducing contradictory copies or preserving a relationship.
Closure is a proof procedure rather than a guess about likely identifiers. Begin with the proposed attribute set, repeatedly add every dependent attribute whose determinant is already available, and stop only when no dependency adds anything.
Reaching the full schema proves a superkey under the stated dependencies. Minimality is a separate test: remove each starting attribute in turn and recompute enough closure to show that the full schema is no longer determined.
Several candidate keys can coexist. Choosing one as the primary key does not make the others cease to identify tuples; they remain alternate candidate keys and may require uniqueness enforcement.
A surrogate identifier can simplify references, but it does not erase a meaningful uniqueness rule such as one enrolment per student, course and term. Preserve that domain constraint or duplicate facts can enter under different surrogate values.
Decomposition should be explained through facts and reconstruction.
If a relation repeats a course title once per enrolment, moving course details to a COURSE relation stores the course fact once while ENROLMENT retains the relationship. Foreign keys make the intended reconstruction explicit. A decomposition that loses necessary associations or invents spurious combinations has not solved the design problem merely because its tables are smaller.
Anomalies provide concrete tests of grain.
Ask whether a fact can be inserted before an unrelated event exists, whether changing one real-world fact requires several row updates, and whether deleting the final event accidentally removes an independent fact. Link each symptom back to a dependency whose determinant is not the intended row identifier. This creates a traceable argument from domain rule to redundancy, repaired relations and enforceable keys.
What this chapter covers
- 01
Relation grain and tuple meaning
- 02
Redundancy and update anomalies
- 03
Functional dependency tests
- 04
Attribute closure and candidate keys
- 05
Primary keys, foreign keys and decomposition
Worked example · free
Find a composite enrolment key
- +1Write the dependencies supplied by the domain.
- +2Compute the closure of the proposed attribute set.
- +1Remove each attribute in turn to test minimality.
- +1State the identity consequence in words.
Key terms
- Relation grain
- The real-world fact represented by one tuple in a relation.
- Functional dependency
- A domain rule in which one attribute set determines another.
- Candidate key
- A minimal superkey that determines the whole relation.
- Update anomaly
- An inconsistency or information loss caused by storing one fact repeatedly.
Relational Foundations and Functional Dependencies FAQ
How do I know whether a dependency is valid?
Try to construct two legitimate tuples with the same determinant and different dependent values. If the domain permits that pair, the dependency is false even when the current sample contains no counterexample. Explain the business rule that would force agreement before using the dependency.
What makes a superkey a candidate key?
A candidate key must determine every attribute and be minimal. Compute closure for full determination, then remove attributes one at a time. Any removable attribute proves the original set is only a superkey. Keep the closure steps visible so the conclusion is auditable.
Why does relation grain matter?
Grain states what one row represents. It exposes which attributes belong together, clarifies composite identity and prevents facts about another entity from being repeated at the wrong level. Writing the grain as a sentence is a fast check before decomposition.
Exam move
Practise from the domain sentence toward notation. State one row's grain, list the dependencies, compute closure, test minimality and name the anomaly removed by the resulting design. Challenge every inferred dependency with a counterexample rather than trusting observed uniqueness. Build a small closure table in which each row records the attributes gained and the dependency used.
After reaching the full schema, remove proposed key attributes one at a time to demonstrate minimality. For anomaly questions, use one concrete operation: update one repeated value, insert an entity with no related event, or delete the final event row. Finish by drawing the repaired relations with their primary and foreign keys, then read the design back as plain sentences.
If every relation describes one fact at one grain and the references reconstruct the intended relationships, the reasoning is likely sound.