Armstrong's axioms in DBMS Theory - Mini Project: Build & Apply

Understanding Armstrong's Axioms for Functional Dependencies

📖 Scenario: You are working with a database design team to understand how functional dependencies work. To ensure the database is well-structured, you need to apply Armstrong's axioms, which are rules used to infer all functional dependencies from a given set.

🎯 Goal: Build a simple representation of functional dependencies using SQL tables and apply Armstrong's axioms step-by-step to infer new dependencies.

📋 What You'll Learn

Create a table to store initial functional dependencies

Add a configuration variable to represent the attribute set

Use SQL queries to apply Armstrong's axioms: Reflexivity, Augmentation, and Transitivity

Complete the final step by listing all inferred functional dependencies

💡 Why This Matters

🌍 Real World

Database designers use Armstrong's axioms to understand and infer all functional dependencies, which helps in normalizing databases and avoiding redundancy.

💼 Career

Knowledge of Armstrong's axioms is essential for roles like database administrator, data analyst, and software engineer working with relational databases.

1

DATA SETUP: Create the functional_dependencies table

Create a table called functional_dependencies with two columns: determinant and dependent, both of type VARCHAR(10). Insert these exact rows: ('A', 'B'), ('B', 'C').

DBMS Theory

-- Create the functional_dependencies table and insert initial data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the two rows exactly as given.

2

CONFIGURATION: Define the attribute set variable

Create a variable called @attributes and set it to the string 'A,B,C' representing all attributes involved.

DBMS Theory

CREATE TABLE functional_dependencies (
  determinant VARCHAR(10),
  dependent VARCHAR(10)
);

INSERT INTO functional_dependencies (determinant, dependent) VALUES
('A', 'B'),
('B', 'C');

-- Define the attribute set variable
-- Your code here

Hint

Use DECLARE to create a variable and assign the string 'A,B,C' exactly.

3

CORE LOGIC: Apply Armstrong's axioms using SQL queries

Write SQL queries to apply the three Armstrong's axioms: Reflexivity (every attribute functionally determines itself), Augmentation (if X -> Y then XZ -> YZ), and Transitivity (if X -> Y and Y -> Z then X -> Z). Add the inferred dependencies to the functional_dependencies table.

DBMS Theory

CREATE TABLE functional_dependencies (
  determinant VARCHAR(10),
  dependent VARCHAR(10)
);

INSERT INTO functional_dependencies (determinant, dependent) VALUES
('A', 'B'),
('B', 'C');

DECLARE @attributes VARCHAR(10) = 'A,B,C';

-- Apply Armstrong's axioms and insert inferred dependencies
-- Your code here

Hint

Use INSERT INTO with VALUES for Reflexivity, SELECT CONCAT(...) for Augmentation, and a JOIN for Transitivity.

4

COMPLETION: List all functional dependencies including inferred ones

Write a SQL query to select all rows from functional_dependencies ordered by determinant and then dependent to show the complete set of functional dependencies after applying Armstrong's axioms.

DBMS Theory

Hint

Use a SELECT statement with ORDER BY determinant, dependent to list all dependencies.

Practice

1. Which of the following is NOT one of Armstrong's axioms used in database theory?

easy

A. Reflexivity

B. Augmentation

C. Transitivity

D. Normalization

Solution

1. Step 1: Recall Armstrong's axioms

   Armstrong's axioms include Reflexivity, Augmentation, and Transitivity.

2. Step 2: Identify the option not in the axioms

   Normalization is a database design process, not an axiom.

3. Final Answer:

   Normalization -> Option D

4. Quick Check:

   Armstrong's axioms exclude Normalization [OK]

Hint: Remember the three axioms: Reflexivity, Augmentation, Transitivity [OK]

Common Mistakes:

• Confusing normalization with axioms
• Thinking all database terms are axioms
• Mixing up augmentation with normalization

2. Which Armstrong's axiom states that if Y is a subset of X, then X functionally determines Y?

easy

A. Reflexivity

B. Transitivity

C. Augmentation

D. Decomposition

Solution

1. Step 1: Understand the axiom definitions

   Reflexivity states if Y is subset of X, then X determines Y.

2. Step 2: Match the statement to the axiom

   The given statement matches Reflexivity exactly.

3. Final Answer:

   Reflexivity -> Option A

4. Quick Check:

   Subset implies functional dependency by Reflexivity [OK]

Hint: Subset implies dependency = Reflexivity [OK]

Common Mistakes:

• Confusing reflexivity with transitivity
• Choosing decomposition which is not an axiom
• Mixing augmentation with reflexivity

3. Given the functional dependencies: A -> B and B -> C, what can we infer using Armstrong's axioms?

medium

A. A -> C

B. B -> A

C. C -> A

D. C -> B

Solution

1. Step 1: Identify the given dependencies

   We have A determines B, and B determines C.

2. Step 2: Apply Transitivity axiom

   Transitivity says if A -> B and B -> C, then A -> C.

3. Final Answer:

   A -> C -> Option A

4. Quick Check:

   Transitivity gives A -> C [OK]

Hint: Chain dependencies to find indirect ones using Transitivity [OK]

Common Mistakes:

• Reversing dependencies incorrectly
• Ignoring transitivity rule
• Assuming dependencies go backward

4. Identify the error in this application of Armstrong's axioms: Given A -> B, the conclusion is B -> A.

medium

A. Correct use of augmentation

B. Proper use of reflexivity

C. Mistakenly reversed dependency

D. Valid transitivity application

Solution

1. Step 1: Analyze the given dependency

   A -> B means A determines B, not the other way around.

2. Step 2: Check the conclusion

   Concluding B -> A reverses the dependency, which Armstrong's axioms do not allow.

3. Final Answer:

   Mistakenly reversed dependency -> Option C

4. Quick Check:

   Dependencies do not reverse automatically [OK]

Hint: Dependencies don't reverse unless explicitly stated [OK]

Common Mistakes:

• Assuming dependencies are symmetric
• Confusing augmentation with reversal
• Ignoring direction of functional dependency

5. If you have A -> B and A -> C, which Armstrong's axiom allows you to combine these into A -> BC?

hard

A. Transitivity

B. Union

C. Decomposition

D. Augmentation

Solution

1. Step 1: Understand the given dependencies

   A determines B and A determines C separately.

2. Step 2: Apply the Union axiom

   Union allows combining A -> B and A -> C into A -> BC.

3. Final Answer:

   Union -> Option B

4. Quick Check:

   Union combines dependencies into one [OK]

Hint: Combine dependencies with same left side using Union [OK]

Common Mistakes:

• Confusing union with augmentation
• Using decomposition instead of union
• Applying transitivity incorrectly here