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

Need a 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

Need a 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

Need a 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

Need a hint?

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