Fourth Normal Form (4NF) in DBMS Theory - Mini Project: Build & Apply

Understanding Fourth Normal Form (4NF) in Databases

📖 Scenario: You are designing a database for a university to store information about students, their hobbies, and the languages they speak. You want to organize the data to avoid redundancy and anomalies.

🎯 Goal: Build a simple table structure and apply Fourth Normal Form (4NF) principles to separate independent multi-valued facts into different tables.

📋 What You'll Learn

Create a table called StudentHobbiesLanguages with columns StudentID, Hobby, and Language.

Add a configuration variable called multi_valued_attributes listing the columns with multiple values.

Separate the multi-valued attributes into two tables: StudentHobbies and StudentLanguages.

Add primary keys to the new tables to complete the 4NF design.

💡 Why This Matters

🌍 Real World

Database designers use 4NF to organize data efficiently when multiple independent multi-valued facts exist, such as hobbies and languages for students.

💼 Career

Understanding 4NF helps database administrators and developers design normalized databases that avoid redundancy and maintain data consistency.

1

Create the initial table with multi-valued attributes

Create a table called StudentHobbiesLanguages with columns StudentID (integer), Hobby (text), and Language (text). Use SQL CREATE TABLE syntax exactly as shown.

DBMS Theory

-- Create the StudentHobbiesLanguages table
-- Your code here

Need a hint?

Use CREATE TABLE StudentHobbiesLanguages (StudentID INT, Hobby TEXT, Language TEXT);

2

Identify multi-valued attributes

Create a variable called multi_valued_attributes and set it to a list containing the strings 'Hobby' and 'Language' to represent columns with multiple values.

DBMS Theory

CREATE TABLE StudentHobbiesLanguages (
    StudentID INT,
    Hobby TEXT,
    Language TEXT
);

-- Define multi_valued_attributes list
-- Your code here

Need a hint?

Define multi_valued_attributes = ['Hobby', 'Language']

3

Separate multi-valued attributes into two tables

Create two new tables: StudentHobbies with columns StudentID and Hobby, and StudentLanguages with columns StudentID and Language. Use SQL CREATE TABLE statements.

DBMS Theory

CREATE TABLE StudentHobbiesLanguages (
    StudentID INT,
    Hobby TEXT,
    Language TEXT
);

multi_valued_attributes = ['Hobby', 'Language']

-- Create StudentHobbies and StudentLanguages tables
-- Your code here

Need a hint?

Use two CREATE TABLE statements to separate hobbies and languages.

4

Add primary keys to complete 4NF design

Alter the tables StudentHobbies and StudentLanguages to add primary keys on the combination of StudentID and Hobby for StudentHobbies, and StudentID and Language for StudentLanguages.

DBMS Theory

CREATE TABLE StudentHobbiesLanguages (
    StudentID INT,
    Hobby TEXT,
    Language TEXT
);

multi_valued_attributes = ['Hobby', 'Language']

CREATE TABLE StudentHobbies (
    StudentID INT,
    Hobby TEXT
);

CREATE TABLE StudentLanguages (
    StudentID INT,
    Language TEXT
);

-- Add primary keys to StudentHobbies and StudentLanguages
-- Your code here

Need a hint?

Add PRIMARY KEY (StudentID, Hobby) to StudentHobbies and PRIMARY KEY (StudentID, Language) to StudentLanguages.