Database Normalization: Closure, Minimal Cover, and the 3NF Synthesis Algorithm

5.1 Why Database Systems Exist — Recap of the Four Properties of a Good Database

5.1.1 The Reason Database Systems Exist

Every application we build — desktop, enterprise, mobile — has one central component: managing data. Data management exists to solve a problem and to give a better, more personal experience. We build applications in the first place to reach more people: a class of a hundred people can become a hundred times that through an application, and the same idea carries to agriculture, healthcare, and every domain we want to impact. Through an application you can reach far more people, solve more problems, and add value to many lives.

Data management needs its own discipline because traditional programming languages share the same limits. Whether you use functional languages (Scala, Haskell), imperative languages (Java, C), object-oriented languages, or logic programming languages (Prolog), they are all weak at four things:

• Storing data. A program's variables exist only while the program runs; a language alone gives you no way to keep data safe after the program stops.
• Storing constraints. You can write if checks, but the language does not guarantee, as a rule, that every phone number is unique or that every employee belongs to a department.
• Retrieving data efficiently. Finding "all students enrolled in Database Design" means writing a loop over everything; there is no built-in, optimized way to fetch a subset.
• Retrieving data concurrently. Two users reading and updating the same data at the same time need careful coordination; a language alone leaves this to you, badly.

That gap is what database systems fill.

We have already walked one path in this course: a user gives you a requirement; as the database designer, administrator, and team manager, you create a diagram (an entity-relationship diagram), then you convert the diagram into a relational schema. That is a good way to store data, but it is not a proof. We have no mathematical certainty that the relation we drew is a good relation and will behave as expected. Normalization is the machinery that supplies that certainty.

5.1.2 The Four Properties of a Good Database

A good database design is judged on four properties.

Least redundancy and least update anomalies. Redundancy (the same data stored in more than one place) is the enemy. Consider a tiny table where the same person's ID and name repeat once per phone number:

If Ana changes her name to Anya, a change of name forces an update at every occurrence — here two places, in a real table dozens. That is an update anomaly — hazardous and troublesome, because if we update only one of the two rows, the data contradicts itself. The two related problems are the insert anomaly (you cannot record a new phone number for Ana without repeating her name again, and you cannot record a person with no phone number at all) and the delete anomaly (deleting Ana's last phone number would silently delete Ana herself). The primary cause of all three — insert, update, delete — is redundancy.

Least null entries. Nulls (missing values) block joins, block proper explanation of the database, and waste a lot of space. If half the rows in a relation have an empty grade column, any query that joins on grade simply drops those rows, and the schema no longer "explains itself" cleanly.

Least spurious tuples. When normalization splits one relation into several, joining them back must not produce new false tuples — junk rows that did not exist in the data. A spurious tuple (the professor's spoken phrase for these false rows) is a row created by joining that matches no real-world record, and such junk rows would block strong decision making: a report that counts employees could silently count rows that no employee corresponds to.

5.1.3 The Normal Forms So Far — 1NF, 2NF, 3NF, BCNF

We have already covered four normal forms. Each is a rule about the functional dependencies allowed inside a relation, and each stricter rule removes one class of redundancy.

First normal form (1NF): every record is unique, and every cell entry is a distinct, atomic value. No repeating groups of phones inside one cell, no two identical rows.

Second normal form (2NF): full functional dependency — no non-prime attribute may depend on only part of a candidate key. The professor's compressed phrasing was "the left-hand side alpha should be a complete candidate key, or beta should be a prime attribute"; the standard definition is that every non-prime attribute must be fully dependent on every candidate key, meaning no partial dependency on a proper subset of a key. Both versions say the same thing: the only allowed dependencies run from the whole key, never from a slice of it.

Third normal form (3NF): for every functional dependency $\alpha \to \beta$, either $\alpha$ is a superkey or every attribute in $\beta$ is a prime attribute. The instructor compressed this to "alpha is a superkey and beta is a prime attribute"; the standard form is the or-version above — only one of the two conditions needs to hold, and that subtle "or" is what lets 3NF always be achievable while keeping the original dependencies (Section 5.8).

$$\text{3NF: for every FD }\alpha \to \beta \text{ in }R,(\alpha \text{ is a superkey of }R)\vee (\text{every attribute in }\beta \text{ is prime}).$$

Boyce-Codd normal form (BCNF): for every functional dependency $\alpha \to \beta$, $\alpha$ must be a superkey — nothing else is allowed.

$$\text{BCNF: for every nontrivial FD }\alpha \to \beta \text{ in }R,\alpha \text{ is a superkey of }R.$$

5.1.4 Key Terminology — Superkey, Candidate Key, Primary Key, Prime Attributes

• Superkey (also just called a key): a set of attributes whose values are distinct in different tuples — no two record entries share the same combination. It is what makes rows unique. Formally, a set $K$ is a superkey of relation $R$ if $K\to R$ — the set determines every attribute.
• Candidate key: when a relation has more than one superkey, each minimal superkey (one from which no attribute can be removed without losing uniqueness) is a candidate key. In other words: when a relation has more than one key, each of them is a candidate key.
• Primary key: the one candidate key we choose to identify rows in practice. The others remain candidate keys — they are still unique, we simply did not crown them.
• Prime attribute: any attribute that belongs to some candidate key. Every attribute that is part of any candidate key is prime; every attribute outside all candidate keys is non-prime.

5.1.5 Two Ways to Learn a Theorem

A student asked whether we must implement all the normal forms to make the database better. The answer set the teaching method for the day. There are two ways to learn anything. One: give a practical example first, then reveal the theory that explains why the practice works. Two: give the theorem and its explanation first, then show how it applies in real life. This session follows the second path — theorems first.

5.2 Functional Dependencies Create Redundancy — Why Higher Normal Forms Are Better

5.2.1 The Core Mechanism — Whenever an Attribute Repeats, Its Determined Attributes Repeat

If there is a functional dependency from B to C, written $B\to C$ — read "B determines C" — the meaning is simple: whenever the value of B repeats, the value of C repeats too. Suppose B1 appears in two rows and each row carries C1. Then the same C1 is stored twice — that is redundancy. Change C at one place, and every place where B repeats must change as well.

Two everyday examples were worked through, and the instructor checked repeatedly that this point landed, because this small mechanism is the engine behind everything that follows. Course → textbook: wherever the course "Database Design" appears, the textbook name appears; change the textbook, and every occurrence must change.

The chain in full: functional dependencies → redundancies → uncertainty and anomalies. If the same data sits at twenty places and one place differs from the other nineteen, which one is correct? You cannot decide with confidence. Redundancy also creates resistance to insertion, update, delete, and insert anomalies. The bold statement of the session: the more functional dependencies a relation has, the more redundancies it carries. The fewer functional dependencies exist in a relation, the easier it is to update and the less redundant it is.

5.2.2 Second Normal Form Versus First

Why is 2NF better than 1NF? Consider a relation whose candidate key is a pair of attributes, and suppose a functional dependency runs from part of that key to some other attribute. Because the pair is a candidate key, the pair itself cannot repeat — by definition, rows are distinct on the key. But one member of the pair is not a key by itself, so it can repeat, and everything it determines repeats with it.

Second normal form forbids exactly this: no dependency on a proper part of a candidate key. Satisfying 2NF removes one whole class of redundancy — the partial dependency (a dependency whose left side is a strict subset of a key). Every removed redundancy means less resistance to insertion, easier updates, and more confidence that the stored data is correct.

There is a natural objection: don't we remove even more by never repeating the primary key at all? Yes — a primary key appears only once in the relation, and that is by definition. The problem case is a part of a candidate key: it is not a key, so it can appear many times, and everything it determines repeats with it. That is the specific redundancy 2NF eliminates.

5.2.3 Third Normal Form Versus Second

3NF removes one more kind of dependency. Under 2NF, it is still allowed for a non-prime attribute (one outside every candidate key) to determine another non-prime attribute. Example: an email ID that is not part of any candidate key determines an address. Whenever the email ID repeats, the address repeats — still redundancy. 3NF says no: any non-prime attribute determining any non-prime attribute is not allowed either.

The fundamental logic is unchanged: functional dependencies lead to redundancy; redundancy leads to uncertainty and to update, delete, and insert anomalies. So the fewer functional dependencies exist in a relation, the better — easier to update, less redundancy. That submission is what carries the argument from 2NF to 3NF.

5.2.4 BCNF Versus 3NF and the Four-Property Test

Why is BCNF better than 3NF? BCNF allows only one kind of functional dependency: $\alpha \to \beta$ where $\alpha$ is a superkey. The only functional dependencies that exist in a BCNF relation are those whose left side is a candidate key — nothing else may exist. When no other dependency exists, there is hardly any repetition left in the database. So BCNF gives the best result on all four properties of a good relation: least redundancy, least null entries, least spurious tuples, and the fewest anomalies.

Students attempted the answer. One student started well — when a row is duplicated, higher normal forms apply stricter rules and we make sure the duplication caused by functional dependencies is removed — but then digressed. Another pointed at 3NF: non-prime attributes are not dependent on non-prime attributes, so updating one non-prime attribute does not force updates in many places. The instructor's verdict: ninety percent correct. The missing step was mapping the answer to the definition of a good relation: fewer functional dependencies mean less redundancy, and less redundancy better satisfies the four properties. That mapping is precisely what formal education trains — processing a thought, articulating it, and presenting it as a logical argument — which is why written examinations exist.

5.2.5 The Class Debate — Student Questions and Answers

5.3 The Cost of Normalization — Why Not Everything Is in BCNF

5.3.1 Every Split Adds a Join

If higher normal forms are so good, why does the real world contain so many 1NF and 2NF relations? Because normalization splits relations, and every split has a price. To convert a relation that violates 3NF, we create two relations: one holds $\alpha$ and $\beta$, the other holds everything including $\alpha$. From then on, retrieval requires a join almost every time. Retrieval takes time; addition takes effort; maintaining the split is painstaking. So even though 3NF has less redundancy than 2NF, and BCNF less than 3NF, retrieving and maintaining them cost more.

Because of exactly this relation between splits and joins, some database schemas stay in 1NF or 2NF on purpose. When queries are light and no heavy join loads arrive, the team just stores everything together, does not worry about 1NF, 2NF, 3NF, or null entries, and enjoys fewer joins at retrieval time.

5.3.2 Denormalization as a Deliberate Choice

• When updates are the priority, prefer 3NF — fewer places to change, so each edit is cheap and safe.
• When fast retrieval dominates, accept 2NF with its redundancy: "it's okay that we will update it multiple times, but retrieval is faster; it's okay that we will have some redundancy, but it is easier for us to enter, easier to delete, easier to retrieve."

Indexes and other provisions also exist to make retrieval faster without abandoning normalization. An index is a pre-built lookup structure on a column — the database equivalent of a book's index — so the retrieval cost is cut at the storage layer rather than by storing extra data copies.

5.3.3 NoSQL and the Redundancy Trade-off

Real-world: many modern systems accept redundancy in exchange for retrieval speed. More and more databases that handle very large data volumes with heavy retrieval load are moving toward NoSQL systems such as MongoDB, Cassandra, and Neo4j. They store data together in JSON, convert it to BSON (Binary JSON — MongoDB's compact binary format for storage and transfer, pronounced "bee-son"; the professor's spoken term sounded like "BASAN"), and replication is easier. They may compromise somewhat on redundancy because retrieval is so heavily required and the data size is so large. Traditional relational systems, in contrast, exist to give mathematical certainty about the schema — which is exactly what normalization provides.

5.3.4 Student Questions on the Trade-off

5.4 The Closure of a Functional Dependency Set (F+)

5.4.1 What F+ Means

When we split a relation, two things must hold: every functional dependency we were given must be preserved, and no lossy relations may be created (joining the pieces must return exactly the original rows, with no spurious tuples). To guarantee preservation we need the complete set of functional dependencies derivable from the given set F. That set is the closure of F, written $F^{+}$ — all dependencies that follow logically from the ones we were given. All of them matter for building the final normal form.

A few given dependencies produce many derivable ones: given a set of three or four functional dependencies, applying the rules can create around thirty functional dependencies. Using all thirty to build a normal form is a troublesome task — creation is difficult and takes time. Two properties make the closure trustworthy: the rules produce all dependencies that exist (nothing is missing — the set is complete), and everything they produce is correct (nothing false is invented — the set is sound). Then, to work with a manageable set, we later build a minimal cover (Section 5.6).

5.4.2 Rule 1 — Reflexivity

This rule needs no "knowledge": it says only that a set of attributes determines its own parts. For example, (course_number, instructor_name) → course_number: if two rows agree on both the course number and the instructor, they certainly agree on the course number alone. Reflexivity is how a composite left side always determines each of its own attributes. A dependency $X\to Y$ with $Y\subseteq X$ is called trivial — it holds in every relation, no matter what the data says.

5.4.3 Rule 2 — Augmentation

The name says it: we augment (grow) both sides by the same set Z. Why must it be the same Z on both sides? Because adding Z only to the left would be a stronger claim we cannot justify. Example: from $\text{course\_number}\to \text{credit\_hours}$, augmentation with instructor_name gives (course_number, instructor_name) → (credit_hours, instructor_name) — true by definition, since the same instructor_name sits on both sides, and the stepping stone for the transitivity chains in Section 5.5.

5.4.4 Rule 3 — Transitivity

This is the rule that actually creates new knowledge. Transitivity shows up constantly in the examples: because course_number → credit_hours, any superset containing course_number also determines credit_hours; because text → publisher, anything that determines text also determines publisher.

The three rules above are called Armstrong's axioms. Two facts make them the complete toolbox: they are sound (every dependency they produce genuinely holds) and complete (every dependency that genuinely holds can be produced by them). Three more rules are often listed, but each is derivable from the axioms and saves steps: the union rule $X\to Y$ and $X\to Z$ together imply $X\to YZ$; the decomposition rule $X\to YZ$ implies $X\to Y$ and $X\to Z$; and the pseudotransitivity rule $X\to Y$ and $YZ\to W$ imply $XZ\to W$.

5.4.5 The Attribute Closure X+ and Its Algorithm

The workhorse of everything that follows is the attribute closure. $X^{+}$ is the set of every attribute derivable from X using the given dependencies. The algorithm, in the professor's words: "Initially start with X plus is equal to X... for every attribute that can be derived from X plus... X plus is a superset of Y... add that here in this particular group... and repeat."

1. Start with $X^{+}=X$.
2. For every functional dependency $Y\to Z$ in F with $Y\subseteq X^{+}$, add all of Z to $X^{+}$.
3. Repeat step 2 until $X^{+}$ stops growing.

5.5 Attribute Closure Worked in a University Schema

5.5.1 The Given Functional Dependencies

The working example is a university mini-world with the attributes class_id, course_number, credit_hours, section_id, instructor_name, text, classroom, capacity, and publisher. The given functional dependencies (reconstructed from the professor's spoken list; the set is internally consistent, and every example below checks out against it):

1. $\text{course\_number}\to \text{credit\_hours}$
2. $\text{course\_number},\text{instructor\_name}\to \text{text}$
3. $\text{course\_number},\text{instructor\_name}\to \text{classroom}$
4. $\text{text}\to \text{publisher}$
5. $\text{classroom}\to \text{capacity}$
6. $\text{section\_id}\to \text{course\_number}$
7. $\text{section\_id}\to \text{instructor\_name}$

Read each dependency exactly as written. In particular, dependencies 2 and 3 say that the pair (course_number, instructor_name) — the two taken together — determines text, and the same pair determines classroom. Neither course_number alone nor instructor_name alone determines either of them (see 5.5.6). The pair (course_number, instructor_name) jointly determining classroom, plus dependency 5, is the textbook pattern: in the standard university schema, the pair (building, room_number) determines capacity, and here the pair behaves like the room identifier.

5.5.2 Worked Example — Closure of class_id

5.5.3 Worked Example — Closure of course_number

Find ${\text{course\_number}}^{+}$.

Start with course_number. Dependency 1 applies: course_number → credit_hours, so credit_hours enters. Do any other dependencies apply? The pair course_number + instructor_name → text does not apply, because instructor_name is not in the closure — and, critically, a composite left side does not decompose into its members (see 5.5.6). So:

$${\text{course\_number}}^{+}=\{\text{course\_number},\text{credit\_hours}\}$$

A student suggested that from the course number we can read the subject, since the course belongs to a subject. The answer: a closure contains only what follows from the given functional dependencies, and none of them says course_number → subject. The closure answers a strict question: what all can we determine from this attribute, using only the functional dependencies we were given? Sense-check: two attributes inside — the starting one plus exactly what dependency 1 promises — and no chain can begin because the only dependency with course_number's new friend (credit_hours) on the left is none.

5.5.4 Worked Example — Closure of section_id

5.5.5 Worked Example — Closure of (course_number, instructor_name)

Find the closure of the pair.

Start with both attributes. The pair contains course_number, so dependency 1 fires: credit_hours enters. Dependencies 2 and 3 fire directly: text and classroom enter. Text brings publisher (4); classroom brings capacity (5). Nothing in the set brings section_id or class_id. So:

$$(\text{course\_number},\text{instructor\_name}{)}^{+}=\{\text{course\_number},\text{instructor\_name},\text{credit\_hours},\text{text},\text{classroom},\text{publisher},\text{capacity}\}$$

The instructor's remark: "so it does not have class ID definitely." Since the closure misses class_id (and section_id), the pair is not a candidate key — yet it determines everything else in the relation. Whether the pair is a candidate key is a separate question; what the computation shows is that the pair determines all of these attributes. Sense-check: compare with 5.5.4 — the pair's closure is exactly section_id's closure minus section_id itself, which is consistent: section_id determines the pair, and the pair determines all the rest.

5.5.6 Student Questions on Closure

5.6 The Minimal Cover of a Functional Dependency Set

5.6.1 Why We Need a Minimal Set

Functional dependencies reveal all the redundancies, and redundancies are the parameter of good database design. But the full closure $F^{+}$ is huge — tens of dependencies from a handful — and building a normal form from all of them is a troublesome task. The minimal cover (also called the minimal set or canonical cover) is the smallest set of functional dependencies with the same power as the original set, and we use that minimal set to create the good relation. This is what will be required: a minimal set of functional dependencies out of the given set, used to create a relational schema in 3NF or BCNF.

A minimal cover has three formal properties (matching the textbook definition): every dependency has a single attribute on its right-hand side; no dependency can be removed and still leave an equivalent set; and no attribute can be removed from any left-hand side and still leave an equivalent set. Nothing is smaller without losing power — that is what "minimal" means.

5.6.2 Equivalence of Functional Dependency Sets

Two sets F and F' are equivalent when whatever can be derived from the original set can be derived from the new set — in symbols, $F^{+}=F^{\prime +}$. That equality of closures is the test. The definition of redundancy follows directly: a functional dependency is redundant exactly when removing it leaves an equivalent set. The same applies to a single attribute on a left-hand side: if $XY\to A$ can be replaced by $X\to A$ and the two sets stay equivalent, then Y is redundant. The instructor's words: "if this set and that set are equivalent... their closure are same, then this functional dependency is redundant."

5.6.3 The Reduction Procedure

To build a minimal cover from a given set F:

1. Split right-hand sides. Turn every dependency $X\to A_1{A}_2$ into $X\to A_1$ and $X\to A_2$. The instructor's words: "if we have a functional dependency from A to C, D... what we write is A to C and A to D. That is the initial step." Note this decomposition works on right sides only — a composite left side does not decompose (see 5.5.6).

1. Remove redundant functional dependencies. Test each dependency $X\to A$: compute $X^{+}$ with all of F, then $X^{+}$ without this dependency. If the two closures are the same, the dependency is redundant — remove it.

1. Remove redundant attributes from left-hand sides. For each left side XY in a dependency $XY\to A$, test each member: compute $X^{+}$ (the closure with Y removed) and compare with the original closure of XY. If they are the same, Y is redundant — remove it and use $X\to A$.

After any removal, re-run the checks: a dependency that was not redundant can become redundant after another removal. The instructor emphasized the test in the abstract: either the complete functional dependency is redundant, or an attribute on the left-hand side is redundant — we find out which one by comparing closures.

5.7 Worked Example — Minimal Cover of {B → A, D → A, AB → D}

5.7.1 Step 1 — Split Right-Hand Sides into Singletons

Given $F=\{B\to A,D\to A,AB\to D\}$. Every right-hand side is already a single attribute, so there is nothing to split in this step. The set stays:

$$F=\{B\to A,D\to A,AB\to D\}$$

5.7.2 Step 2 — Check Each Functional Dependency for Redundancy

Compute the closure of each left side with all of F, then without the dependency under test. The instructor made the test explicit: "if with this functional dependency and without this functional dependency everything is same, then we can say that this functional dependency is redundant."

Test $B\to A$. With all of F:

$$B^{+}=\{B\}\stackrel{B\to A}{\to }\{A,B\}\stackrel{AB\to D}{\to }\{A,B,D\}$$

So $B^{+}$ with F is $\{A,B,D\}$. Without $B\to A$, using only $\{D\to A,AB\to D\}$:

$$B^{+}=\{B\}$$

(AB → D needs A, which is unreachable without $B\to A$; $D\to A$ needs D.) The closures differ, so $B\to A$ is not redundant — keep it.

Test $D\to A$. With F: $D^{+}=\{A,D\}$. Without $D\to A$, nothing fires from D: $D^{+}=\{D\}$. The closures differ — keep $D\to A$.

Test $AB\to D$. With F: $A{B}^{+}=\{A,B,D\}$ ($B\to A$ adds A, then $AB\to D$ adds D). Without $AB\to D$, using only $\{B\to A,D\to A\}$: $A{B}^{+}=\{A,B\}$ (D is unreachable — $D\to A$ needs D). The closures differ — keep $AB\to D$.

5.7.3 Step 3 — Remove Redundant Attributes from Left-Hand Sides

The dependency $AB\to D$ survives, but one of the two attributes on its left side may be redundant. Test each.

Test removing B — is B redundant on the left of $AB\to D$? Replace $AB\to D$ by $A\to D$ and compute $A^{+}$ with the remaining set $\{B\to A,D\to A,A\to D\}$:

$$A^{+}=\{A\}\stackrel{A\to D}{\to }\{A,D\}\stackrel{D\to A}{\to }\{A,D\}$$

so $A^{+}=\{A,D\}$. Compare with the original $A{B}^{+}=\{A,B,D\}$: not the same — with A alone we cannot reach B. So B is not redundant — keep it. The instructor: "Had it been the same, then we would have said that b is redundant."

Test removing A — is A redundant on the left of $AB\to D$? Replace $AB\to D$ by $B\to D$ and compute $B^{+}$ with $\{B\to A,D\to A,B\to D\}$:

$$B^{+}=\{B\}\stackrel{B\to A}{\to }\{A,B\}\stackrel{B\to D}{\to }\{A,B,D\}$$

This equals the original $A{B}^{+}=\{A,B,D\}$. Having A and not having A makes no difference — A is redundant on the left side. The instructor: "so having a and not having a does not make any difference at all... so here I can say that attribute a is redundant here."

The new set is:

$$F^{\prime }=\{B\to A,D\to A,B\to D\}$$

5.7.4 Step 4 — Recheck the New Set

After any removal, the earlier checks must be repeated — the instructor applied the same rule to the new set.

Recheck $B\to A$ in F'. With all of F': $B^{+}=\{A,B,D\}$ (via $B\to A$ and $B\to D$). Without $B\to A$, using only $\{D\to A,B\to D\}$:

$$B^{+}=\{B\}\stackrel{B\to D}{\to }\{B,D\}\stackrel{D\to A}{\to }\{A,B,D\}$$

Same closure! $B\to A$ is now redundant — remove it. The instructor's observation: "having this functional dependency and not having this functional dependency, the rule remains the same. So this is redundant now." This is the recheck rule in action: $B\to A$ survived step 2, but after $AB\to D$ became $B\to D$, the path $B\to D\to A$ reproduces it.

Recheck $D\to A$. With the current set $\{D\to A,B\to D\}$: $D^{+}=\{A,D\}$. Without $D\to A$: $D^{+}=\{D\}$. Not the same — keep it.

Recheck $B\to D$. With: $B^{+}=\{A,B,D\}$. Without $B\to D$, using only $D\to A$: $B^{+}=\{B\}$. Not the same — keep it.

5.7.5 The Final Minimal Cover and the Candidate Key

The minimal cover is:

$$F_c=\{B\to D,D\to A\}$$

No further minimal form can be formed: both dependencies have single-attribute left and right sides, neither is derivable from the other, and neither left side can be shrunk. This matches the textbook result for the same input set exactly. The candidate key: B, because

$$B^{+}=\{A,B,D\}$$

covers every attribute in the relation. The instructor: "candidate key we consider it. B is a candidate key because B plus is equal to A, B, D."

5.8 The 3NF Synthesis Algorithm

5.8.1 The Three-Step Algorithm

The recipe, in the instructor's words:

1. Find the minimal cover. Compute the minimal set of functional dependencies from the given set (Section 5.7).
2. Create one relation per dependency. Whatever is on the left-hand side of a dependency $X\to A$ in the minimal cover, create a new relation containing X and A.
3. Add the key if it is missing. If no created relation contains a candidate key of the whole schema, create one more relation with the key attributes.

The result is a relational schema at least in 3NF. The instructor also recalled the older decomposition rule for a single violation: if $\alpha \to \beta$ does not satisfy 3NF, create a new relation R2 holding $\alpha$ and $\beta$, and keep a relation holding everything including $\alpha$. The synthesis algorithm generalizes that one-off repair into a systematic recipe for the whole set.

5.8.2 Worked Application — From {B → D, D → A} to Two Relations

From Section 5.7 we have the minimal cover $F_c=\{B\to D,D\to A\}$ and the candidate key B. Apply the algorithm:

• $B\to D$ creates the relation $R_1(B,D)$. B is a candidate key of R1.
• $D\to A$ creates the relation $R_2(D,A)$. D is a candidate key of R2.
• Key check: B already appears in R1, so no third relation is needed.

The final schema:

$$R_1(B,D),R_2(D,A)$$

Both relations are in 3NF. The instructor's words: "create two relations. B, d — in which b is a candidate key. And d, a — where d is a candidate key. Is there any relation in which candidate key is there? Yes. There is one relation. Candidate key."

5.8.3 Where This Fits — Chapter Roadmap

The agenda in one paragraph, as the instructor summarized it: we start with a set of functional dependencies that carry redundancy, and we want least redundancy. Normal forms exist to achieve that, and our agenda is a relational schema that satisfies a normal form — the mathematical certainty of a good relation. Functional dependencies are the basis of redundancies, so we find the minimum set of functional dependencies: if F has four dependencies, remove the redundant ones and we are left with three or two. We find the candidate key, and we run the synthesis algorithm to create the relational schema. Once created, that is a good relation in 3NF or BCNF — a very good database design schema.

Exam Guidance Summary

The mid-semester exam (closed book, about five weeks away). This is likely the only closed-book examination in the course, and that choice matters for how you prepare. Expect a mix: some theoretical questions — the instructor wants to evaluate how many of you have really grasped the concept — plus practical implementation questions. The stated question patterns:

• Given a customer specification (one paragraph, not two or three pages), create an ER diagram.
• Given an ER diagram, convert it into a relational schema.
• Given some functional dependencies, find out which normal form the relation is in, then convert it to 3NF or BCNF (the instructor said "third or fourth normal form" — the fix in either case is the machinery of this session: closures, minimal covers, synthesis).
• Practical scenarios like creating a table.

The final exam (open book). The implementation questions sit at a different level. For any given scenario and problem, you must answer on four levels: (1) specify the implication of the problem — if the schema is in a certain state, what follows? For example, the user cannot access concurrently, so performance degrades; (2) propose a solution — two or three options; (3) articulate in a plain way which option satisfies which requirement, and state the implication of the solution: what effort, time, and resources the solution needs; (4) give your verdict — based on the analysis, the problem is this, it has this implication, these solutions exist, they take this much effort and solve that much of the problem, so we follow this option.

Study tips given explicitly. Make notes, revise the previous material before entering, and revise the notes again after the session — this first basic habit helps you recollect things so that even in a closed-book exam the theory is at your fingertips, and you can score freely in those marks. Practice very diligently during the sessions — the sessions exist primarily for practice. Use the recorded materials, approach the instructor one-to-one (half-hour time slots are reserved for help), and ask questions among classmates — when a doubt appears, put it in the group so whoever is available can reply, or call and use a voice note; the answer, right or wrong, gets corrected there. Asking in a portal can feel like resistance, but the group is open.

Assignment guidance. The assignment is individual. The requirements document shared as a sample is only a sample — you create your own assignment by choice. Expect twenty to fifty sample scenarios across supply chain, healthcare, finance, and insurance; pick any one (or your own real-world application), and use your assumptions properly. It is not required that you follow the same template — any template works, or even simple plain English, as long as you are satisfied that whatever the customer is specifying is captured in the requirement. The due date is written in the handout, which already describes what you need to do.

Key Industry Applications

• Real-world: large-scale systems increasingly accept redundancy for retrieval speed. More and more databases that face very large data sizes and heavy retrieval loads move toward NoSQL systems — MongoDB, Cassandra, and Neo4j were named. They store data together in JSON, convert to BSON (binary JSON) for storage, and benefit from easier replication. Redundancy is a deliberate compromise there; mathematical certainty about the schema is the relational world's answer, and normalization is how it is achieved.
• Real-world: denormalization is standard industry practice. When the effort to convert to and maintain 3NF exceeds the benefit, teams deliberately keep 2NF (or 1NF) relations: faster retrieval, easier entry, easier deletion, at the cost of updating the same value in several places. Indexes and other provisions speed up retrieval without giving up normalization.
• Real-world: the motivation for applications is impact — an application reaches a hundred times more people than a class session can. Agriculture, healthcare, and any other domain gain scale through software, and every such application, desktop, enterprise, or mobile, is built around managing data — which is why database design shows up in every industry.
• Real-world: programming-language ecosystems (functional languages such as Scala, plus imperative, object-oriented, and logic languages) all hit the same wall — storing data, storing constraints, retrieving efficiently, and retrieving concurrently — which is why database systems are a separate discipline rather than a library feature.
• Real-world: the assignment domains — supply chain, healthcare, finance, and insurance — each produce long, detailed customer requirements; the discipline of capturing a requirement completely (any template, or plain English) is the professional skill being trained.
• Real-world: the university schema (class_id, course_number, credit_hours, section_id, instructor_name, text, classroom, capacity, publisher) is a miniature of how real registries are modeled — functional dependencies among real attributes, candidate keys found by closure, and schemas normalized from them. The same closure-and-synthesis machinery that turned $\{B\to D,D\to A\}$ into two clean relations is what turns a real-world registry's dependency list into its normalized tables.