# Database Normalization Assignment
## 1. Introduction to Normalization
\documentclass[12pt]{article}
\usepackage{geometry}
\geometry{a4paper, margin=1in}
\usepackage{graphicx}
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{booktabs}
\usepackage{array}
\usepackage{multirow}
\usepackage{hyperref}
\hypersetup{ colorlinks=tru
e, linkcolor=blue,
filecolor=magenta,
urlcolor=cyan,
\title{Database Normalization Assignment}
\author{Database Management Systems Course}
\date{}
\begin{document}
\maketitle
\section{Introduction to Normalization}
Normalization is a systematic process of organizing data in a database to reduce redundancy and
improve data integrity. The primary goals are to:
\begin{itemize}
\item Eliminate redundant data.
\item Ensure logical data dependencies.
\item Minimize insertion, update, and deletion anomalies.
\end{itemize}
The process involves decomposing a table into smaller, related tables while ensuring that the data
remains consistent and retrievable via relational operations like joins. Normalization is based on the
concept of \textbf{normal forms} (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), each addressing specific types of
redundancies and dependencies.
\section{First Normal Form (1NF)}
A relation is in \textbf{1NF} if:
\begin{itemize}
\item All attributes contain atomic (indivisible) values.
\item There are no repeating groups.
\end{itemize}
\subsection*{Example: Unnormalized Table}
Consider the following \texttt{STUDENT} table with repeating groups:
\begin{center}
\begin{tabular}{|c|c|c|c|c|c|c|c|}
\hline
Course\_Code & Course\_Name & Teacher\_Name & RollNo & Name & System\_Used &
Hourly\_Rate & Total\_Hrs \\
\hline
C1 & Visual Basic & ABC & 100,101,102,103 & A1,A2,A3,A4 & P-I,P-II,... & 20,30,... & 7,3,6,1 \\
\hline
\end{tabular}
\end{center}
This table is \textbf{not in 1NF} because multiple values exist in single fields.
\subsection*{Conversion to 1NF}
We apply \textbf{flattening} to remove repeating groups:
\begin{center}
\begin{tabular}{|c|c|c|c|c|c|c|c|}
\hline
Course\_Code & Course\_Name & Teacher\_Name & RollNo & Name & System\_Used &
Hourly\_Rate & Total\_Hrs \\
\hline
C1 & Visual Basic & ABC & 100 & A1 & PentiumI & 20 & 7 \\
C1 & Visual Basic & ABC & 101 & A2 & PentiumII & 30 & 3 \\
C1 & Visual Basic & ABC & 102 & A3 & Celeron & 10 & 6 \\
C1 & Visual Basic & ABC & 103 & A4 & PentiumIV & 40 & 1 \\
\hline
\end{tabular}
\end{center}
\textbf{Primary Key:} (Course\_Code, RollNo)
\subsection*{Anomalies in 1NF}
\begin{itemize}
\item \textbf{Insertion Anomaly:} Cannot add a course without a student, or a student without a
course.
\item \textbf{Update Anomaly:} Changing a teacher's name requires updating multiple rows.
\item \textbf{Deletion Anomaly:} Deleting a student may also delete course information.
\end{itemize}
\section{Second Normal Form (2NF)}
A relation is in \textbf{2NF} if:
\begin{itemize}
\item It is in 1NF.
\item Every non-key attribute is \textbf{fully functionally dependent} on the primary key.
\end{itemize}
\subsection*{Problem in 1NF Table}
In the 1NF \texttt{STUDENT} table:
\begin{itemize}
\item \texttt{(Course\_Code, RollNo) $\rightarrow$ Total\_Hrs} $\checkmark$
\item But \texttt{RollNo $\rightarrow$ Name, System\_Used, Hourly\_Rate} $\times$ (partial
dependency)
\end{itemize}
\subsection*{Decomposition into 2NF}
We decompose into three tables:
\textbf{Table: COURSE}
\begin{center}
\begin{tabular}{|c|c|c|}
\hline
Course\_Code & Course\_Name & Teacher\_Name \\
\hline
C1 & Visual Basic & ABC \\
C2 & Oracle\&Dev & DEF \\
\hline
\end{tabular}
\end{center}
\textbf{Table: STUDENT\_SYSTEM\_CHARGE}
\begin{center}
\begin{tabular}{|c|c|c|c|}
\hline
RollNo & Name & System\_Used & Hourly\_Rate \\
\hline
100 & A1 & PentiumI & 20 \\
101 & A2 & PentiumII & 30 \\
\hline
\end{tabular}
\end{center}
\textbf{Table: HOURS\_ASSIGNED}
\begin{center}
\begin{tabular}{|c|c|c|}
\hline
Course\_Code & RollNo & Total\_Hrs \\
\hline
C1 & 100 & 7 \\
C1 & 101 & 3 \\
\hline
\end{tabular}
\end{center}
\subsection*{Anomalies Removed in 2NF}
\begin{itemize}
\item \textbf{Insertion:} Can add a student without a course.
\item \textbf{Update:} Change teacher name in one row.
\item \textbf{Deletion:} Delete student without losing course info.
\end{itemize}
\section{Third Normal Form (3NF)}
A relation is in \textbf{3NF} if:
\begin{itemize}
\item It is in 2NF.
\item No \textbf{transitive dependency} exists (non-key attribute depends on another non-key
attribute).
\end{itemize}
\subsection*{Problem in 2NF Table}
In \texttt{STUDENT\_SYSTEM\_CHARGE}:
\begin{itemize}
\item \texttt{RollNo $\rightarrow$ System\_Used}
\item \texttt{System\_Used $\rightarrow$ Hourly\_Rate} (transitive dependency)
\end{itemize}
\subsection*{Decomposition into 3NF}
\textbf{Table: STUDENT\_SYSTEM}
\begin{center}
\begin{tabular}{|c|c|c|}
\hline
RollNo & Name & System\_Used \\
\hline
100 & A1 & PentiumI \\
101 & A2 & PentiumII \\
\hline
\end{tabular}
\end{center}
\textbf{Table: CHARGES}
\begin{center}
\begin{tabular}{|c|c|}
\hline
System\_Used & Hourly\_Rate \\
\hline
PentiumI & 20 \\
PentiumII & 30 \\
\hline
\end{tabular}
\end{center}
\subsection*{Anomalies Removed in 3NF}
\begin{itemize}
\item \textbf{Insertion:} Can add a system rate without a student.
\item \textbf{Update:} Change rate in one row.
\item \textbf{Deletion:} Delete last student using a system without losing rate info.
\end{itemize}
\section{Boyce-Codd Normal Form (BCNF)}
A relation is in \textbf{BCNF} if:
\begin{itemize}
\item Every determinant is a candidate key.
\end{itemize}
\subsection*{Example: Manufacturer Table}
\textbf{Table: MANUFACTURER}
\begin{center}
\begin{tabular}{|c|c|c|c|}
\hline
Id\_No & Name & Item\_No & Quantity \\
\hline
M101 & Electronics USA & H3772 & 1000 \\
M101 & Electronics USA & J08732 & 700 \\
\hline
\end{tabular}
\end{center}
\textbf{Functional Dependencies:}
\begin{itemize}
\item \texttt{(Id\_No, Item\_No) $\rightarrow$ Quantity}
\item \texttt{(Name, Item\_No) $\rightarrow$ Quantity}
\item \texttt{Id\_No $\rightarrow$ Name}
\item \texttt{Name $\rightarrow$ Id\_No}
\end{itemize}
\textbf{Problem:} \texttt{Id\_No} and \texttt{Name} are determinants but not candidate keys.
\subsection*{Decomposition into BCNF}
\textbf{Table: ID\_NAME}
\begin{center}
\begin{tabular}{|c|c|}
\hline
Id\_No & Name \\
\hline
M101 & Electronics USA \\
\hline
\end{tabular}
\end{center}
\textbf{Table: ID\_QTY}
\begin{center}
\begin{tabular}{|c|c|c|}
\hline
Id\_No & Item\_No & Quantity \\
\hline
M101 & H3772 & 1000 \\
M101 & J08732 & 700 \\
\hline
\end{tabular}
\end{center}
\section{Higher Normal Forms (4NF \& 5NF)}
\subsection*{Fourth Normal Form (4NF)}
\begin{itemize}
\item Removes \textbf{multi-valued dependencies (MVD)}.
\item Example: \texttt{COURSE\_STUDENT\_BOOK} with MVD: \texttt{Course
$\rightarrow$$\rightarrow$ Student\_Name} and \texttt{Course
$\rightarrow$$\rightarrow$ Text\_Book}
\item Decompose into:
\begin{itemize}
\item \texttt{COURSE\_STUDENT(Course, Student\_Name)}
\item \texttt{COURSE\_BOOK(Course, Text\_Book)}
\end{itemize}
\end{itemize}
\subsection*{Fifth Normal Form (5NF)}
\begin{itemize}
\item Deals with \textbf{join dependencies}.
\item A relation is in 5NF if it cannot be decomposed further without loss of information.
\item Mostly theoretical; used when a table must be split into three or more projections to avoid
redundancy.
\end{itemize}
\section{Summary of Normalization Steps}
\begin{center}
\begin{tabular}{|c|l|l|}
\hline
Step & Normal Form & Action \\
\hline
1 & 1NF & Remove repeating groups \\
2 & 2NF & Remove partial dependencies \\
3 & 3NF & Remove transitive dependencies \\
4 & BCNF & Ensure every determinant is a candidate key \\
5 & 4NF & Remove multi-valued dependencies \\
6 & 5NF & Decompose based on join dependencies \\
\hline
\end{tabular}
\end{center}
\section{Conclusion}
Normalization is a critical database design technique that ensures:
\begin{itemize}
\item \textbf{Data consistency}
\item \textbf{Efficient storage}
\item \textbf{Ease of maintenance}
\end{itemize}
By progressively applying normal forms, we can eliminate anomalies and create a robust, scalable
database structure. While higher normal forms like 4NF and 5NF are less common in practice,
understanding them is essential for handling complex data relationships.
\section*{Exercises}
1. Normalize the following table to 3NF: \\
\texttt{SALES(OrderID, CustomerID, CustomerName, ProductID, ProductName, Quantity, Price)}
2. Identify anomalies in the given table and suggest normalization steps: \\
\texttt{EMPLOYEE(EmpID, EmpName, DeptID, DeptName, ProjectID, ProjectName, HoursWorked)}
3. Explain why a table might be in 3NF but not in BCNF, with an example.
\end{document}
[Full assignment content from previous response]