Dependency Example: Explain functional dependency and how it affects normalization.Normalization Task: Demonstrate how to normalize a sample table into 1NF, 2NF, and 3NF using MySQL.
Introduction to Database Concepts
What is a Database?
A database is an organized collection of data that allows for easy access, management, and
updating. It stores data in a structured format, usually as tables, to ensure consistency and
efficiency.
Example:
A library database may store information about books:
1 | Harry Potter | J.K. Rowling | Fantasy
2 | The Alchemist | Paulo Coelho | Fiction
Importance of Data Organization
Efficiency: Organized data reduces redundancy and increases retrieval speed.
Accuracy: Ensures data integrity.
Scalability: Structured data can handle growth without performance degradation.
Brief Overview of MySQL
MySQL is an open-source relational database management system (RDBMS) that uses SQL
(Structured Query Language) for managing data. It supports features like:
Creating and managing tables.
Querying and modifying data.
Supporting data constraints like primary keys and foreign keys.
Example MySQL Query:CREATE TABLE Books (BookID INT PRIMARY KEY,Title VARCHAR(100),Author VARCHAR(50),Genre VARCHAR(30));
NSERT INTO Books (BookID, Title, Author, Genre)VALUES (1, ‘Harry Potter’, ‘J.K. Rowling’, ‘Fantasy’);
2. Understanding DependenciesFunctional DependencyA functional dependency occurs when the value of one attribute (column) uniquelydetermines another attribute.Example:If BookID uniquely determines Title, then:BookID -> TitlePartial DependencyPartial dependency exists when a non-prime attribute is dependent on part of a compositeprimary key.Example:Table: BookOrders(BookID, OrderID, BookTitle)BookTitle depends only on BookID (part of the key), not the entire composite key (BookID,OrderID).Transitive DependencyA transitive dependency exists when a non-prime attribute depends on another non-primeattribute.Example:Table: Books(BookID, Title, Publisher, PublisherAddress)PublisherAddress depends on Publisher, not directly on BookID.
3. Normalization Stages
1NF (First Normal Form)Goal: Eliminate repeating groups or arrays; each cell contains a single value.
Example:Table before 1NF:StudentID | Name | Subjects——————————–1
| John | Math, Science2 | Jane | English
Table after 1NF:StudentID | Name | Subject——————————-1 |
John | Math1 | John | Science2 | Jane | English
MySQL Query:CREATE TABLE Students1NF (StudentID INT,Name VARCHAR(50),Subject VARCHAR(50));
INSERT INTO Students1NF (StudentID, Name, Subject)VALUES (1, ‘John’, ‘Math’),(1, ‘John’, ‘Science’),(2, ‘Jane’, ‘English’);
2NF (Second Normal Form)Goal: Remove partial dependencies.Conditions:Must be in 1NF.
Every non-key attribute must depend on the whole primary key.Example:Table before 2NF:StudentID | CourseID | StudentName | CourseName———————————————–1
| 101 | John | Math1 | 102 | John | Science
Issue: StudentName depends only on StudentID, not on the entire composite key(StudentID, CourseID).
Table after 2NF:Students Table:—————-StudentID | StudentName————————1 | JohnCourses Table:—————-CourseID | CourseName————————101 | Math102 | ScienceEnrollments Table:——————–StudentID | CourseID———————1 | 1011 | 102 MySQL Query:
CREATE TABLE Students (StudentID INT PRIMARY KEY, StudentName VARCHAR(50));
CREATE TABLE Courses (CourseID INT PRIMARY KEY,CourseName VARCHAR(50) );
CREATE TABLE Enrollments (StudentID INT,CourseID INT,PRIMARY KEY (StudentID, CourseID),FOREIGN KEY (StudentID) REFERENCES Students(StudentID),FOREIGN KEY (CourseID) REFERENCES Courses(CourseID));
INSERT INTO Students (StudentID, StudentName)VALUES (1, ‘John’);
INSERT INTO Courses (CourseID, CourseName)VALUES (101, ‘Math’), (102, ‘Science’);
INSERT INTO Enrollments (StudentID, CourseID)VALUES (1, 101), (1, 102);
3NF (Third Normal Form)Goal: Eliminate transitive dependencies.Conditions:Must be in 2NF.Non-key attributes must not depend on other non-key attributes.Example:Table before 3NF:StudentID | StudentName | CourseID | CourseName———————————————–1 | John | 101 | MathIssue: CourseName depends on CourseID, not directly on StudentID.
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
INSERT INTO Students (StudentID, StudentName)
VALUES (1, ‘John’);
INSERT INTO Courses (CourseID, CourseName)
VALUES (101, ‘Math’), (102, ‘Science’);
INSERT INTO Enrollments (StudentID, CourseID) VALUES (1, 101), (1, 102);
3NF (Third Normal Form)
Goal: Eliminate transitive dependencies.
Conditions:
Must be in 2NF.
Non-key attributes must not depend on other non-key attributes.
Example:
Table before 3NF:
StudentID | StudentName | CourseID | CourseName
1 | John | 101 | Math
Issue: CourseName depends on CourseID, not directly on StudentID.Table after 3NF:Students Table:
StudentID | StudentName————————1 | JohnCourses Table:—————-CourseID | CourseName————————101 |
MathMySQL Query:
CREATE TABLE Students3NF (StudentID INT PRIMARY KEY,StudentName VARCHAR(50));CREATE TABLE Courses3NF (CourseID INT PRIMARY KEY,CourseName VARCHAR(50));
INSERT INTO Students3NF (StudentID, StudentName)VALUES (1, ‘John’);INSERT INTO Courses3NF (CourseID, CourseName)VALUES (101, ‘Math’);
—Summary1. Normalize to 1NF to remove repeating groups.2. Normalize to 2NF to remove partial dependencies.3. Normalize to 3NF to eliminate transitive dependencies.Would you like additional examples or queries to practice?
Thank you