Normalisation

Banking Sector Data Normalization Assignment: 1NF, 2NF, 3NF

Background:

In a banking system, data is stored about customers, accounts, transactions, and employees. As the amount of data grows, it is important to normalize this data to ensure that it is organized efficiently, minimizing redundancy and ensuring consistency. This assignment will guide you through normalizing banking data into 1NF, 2NF, and 3NF.

Scenario:

You are working for a banking system that manages customer information, account details, and transaction records. The current data model is a bit disorganized and suffers from redundancy and other normalization issues. Your task is to analyze and normalize the data into the appropriate normal forms: 1NF, 2NF, and 3NF.

---

1. Banking Customer Information Table (1NF)

Given Table:

CustomerID	CustomerName	Address	Phone	AccountID	AccountType	AccountBalance	TransactionDetails
001	John Doe	123 Elm St, NY	555-1234	1001	Checking	5000	Deposit 500, Withdrawal 1000
002	Jane Smith	456 Oak St, LA	555-5678	1002	Savings	2000	Deposit 2000
003	Alice Brown	789 Pine St, TX	555-9876	1003	Checking	3000	Deposit 1500, Withdrawal 500
001	John Doe	123 Elm St, NY	555-1234	1004	Savings	1500	Withdrawal 200

Question:

1. Is the table in 1NF?
   • The table violates 1NF because it contains multiple values in the “TransactionDetails” column (a repeating group). According to 1NF, each column should contain atomic values (no repeating groups).
2. How would you bring this table into 1NF?
   • You need to split the “TransactionDetails” column into individual transactions. This means that each transaction should be recorded in a separate row.

---

Normalized 1NF Table:

CustomerID	CustomerName	Address	Phone	AccountID	AccountType	AccountBalance	TransactionID	TransactionType	TransactionAmount
001	John Doe	123 Elm St, NY	555-1234	1001	Checking	5000	T1	Deposit	500
001	John Doe	123 Elm St, NY	555-1234	1001	Checking	5000	T2	Withdrawal	1000
002	Jane Smith	456 Oak St, LA	555-5678	1002	Savings	2000	T3	Deposit	2000
003	Alice Brown	789 Pine St, TX	555-9876	1003	Checking	3000	T4	Deposit	1500
003	Alice Brown	789 Pine St, TX	555-9876	1003	Checking	3000	T5	Withdrawal	500
001	John Doe	123 Elm St, NY	555-1234	1004	Savings	1500	T6	Withdrawal	200

---

2. Banking Customer and Account Table (2NF)

Given Table (After 1NF):

CustomerID	CustomerName	Address	Phone	AccountID	AccountType	AccountBalance	TransactionID	TransactionType	TransactionAmount
001	John Doe	123 Elm St, NY	555-1234	1001	Checking	5000	T1	Deposit	500
001	John Doe	123 Elm St, NY	555-1234	1001	Checking	5000	T2	Withdrawal	1000
002	Jane Smith	456 Oak St, LA	555-5678	1002	Savings	2000	T3	Deposit	2000
003	Alice Brown	789 Pine St, TX	555-9876	1003	Checking	3000	T4	Deposit	1500
003	Alice Brown	789 Pine St, TX	555-9876	1003	Checking	3000	T5	Withdrawal	500
001	John Doe	123 Elm St, NY	555-1234	1004	Savings	1500	T6	Withdrawal	200

Question:

1. Is the table in 2NF?
   • The table is not in 2NF because it contains partial dependency. Customer information like CustomerName, Address, and Phone is dependent on CustomerID, while account details like AccountID, AccountType, and AccountBalance are dependent on AccountID. This violates 2NF, which requires that all non-key attributes are fully functionally dependent on the primary key.
2. How would you normalize this table into 2NF?
   • Separate the customer information and account information into different tables, ensuring that each table has a primary key that fully determines all non-key attributes.

---

Normalized 2NF Tables:

Customer Table:

CustomerID	CustomerName	Address	Phone
001	John Doe	123 Elm St, NY	555-1234
002	Jane Smith	456 Oak St, LA	555-5678
003	Alice Brown	789 Pine St, TX	555-9876

Account Table:

AccountID	CustomerID	AccountType	AccountBalance
1001	001	Checking	5000
1002	002	Savings	2000
1003	003	Checking	3000
1004	001	Savings	1500

Transaction Table:

TransactionID	AccountID	TransactionType	TransactionAmount
T1	1001	Deposit	500
T2	1001	Withdrawal	1000
T3	1002	Deposit	2000
T4	1003	Deposit	1500
T5	1003	Withdrawal	500
T6	1004	Withdrawal	200

---

3. Banking Employee and Branch Table (3NF)

Given Table (After 2NF):

EmployeeID	EmployeeName	BranchID	BranchName	BranchLocation	AccountID	AccountType	AccountBalance	TransactionID	TransactionType	TransactionAmount
E001	David	B001	Main Branch	New York	1001	Checking	5000	T1	Deposit	500
E002	Emma	B002	West Branch	Los Angeles	1002	Savings	2000	T3	Deposit	2000
E003	Michael	B001	Main Branch	New York	1003	Checking	3000	T5	Withdrawal	500

Question:

1. Is the table in 3NF?
   • The table is not in 3NF because it contains transitive dependency. The BranchName and BranchLocation are dependent on BranchID, which is not a primary key in this table. This violates 3NF because non-prime attributes (attributes that are not part of the primary key) should not depend on other non-prime attributes.
2. How would you normalize this table into 3NF?
   • You need to remove the transitive dependency by creating a separate table for Branch and linking it to the other tables using a foreign key.

---

Normalized 3NF Tables:

Employee Table:

EmployeeID	EmployeeName	BranchID
E001	David	B001
E002	Emma	B002
E003	Michael	B001

Branch Table:

BranchID	BranchName	BranchLocation
B001	Main Branch	New York
B002	West Branch	Los Angeles

Account Table:

AccountID	CustomerID	AccountType	AccountBalance
1001	001	Checking	5000
1002	002	Savings	2000
1003	003	Checking	3000
1004	001	Savings	1500

Transaction Table:

TransactionID	AccountID	TransactionType	TransactionAmount
T1	1001	Deposit	500
T2	1001	Withdrawal	1000
T3	1002	Deposit	2000
T4	1003	Deposit	1500
T5	1003	Withdrawal	500
T6	1004	Withdrawal	200