Normalizing Database Tables

The assignment content discusses database normalization techniques, specifically First Normal Form (1st NF), Second Normal Form (2nd NF), Third Normal Form (3rd NF), and Boyce-Codd Normal Form. The content provides examples of how to normalize tables, including the use of primary keys, candidate keys, and non-primetime attributes.

Part 1:
C
h
a
r
i
t
y
I
D
C
h
a
r
i
t
y
N
a
m
e
C
h
a
ri
t
y
L
o
c
a
ti
o
n
P
O
C
N
a
m
e
P
O
C
I
D
T
e
l
E
x
t
n
.
C
u
st
o
m
e
r
I
D
C
u
st
o
m
e
r
N
a
m
e
D
at
e
C
on
tri
bu
tio
n
St
art
ed
N
o
o
f
M
o
n
t
h
D
a
t
e
P
l
a
c
e
s
Ex
pe
ct
ed
C
on
tri
bu
tio
n
En
d
1st NF:
Database normalization technique is used to maintain minimum number of attributes with minimum
data redundancy. First normal form demands that there should be atomic value in each cell of each
column. It also ensures there are no repeating groups.
CharityDonation (CharityID, CharityName, CharityLocation, POCName, POCID, TelExtn,
CustomerID, CustomerFirstName, CustomerLastName, StartDate, NumberOfMonths, DatePlaced,
ExpectedEndDate)
2nd NF:
According to Second Normal Form there must not be any partial dependency of any column on
primary key. In other words no non-prime attribute is dependent on the proper subset of any
candidate key of table.
Charity (CharityID, CharityName, CharityLocation)
POC (POCID, POCName, TelExtn)
Customer (CustomerID, FirstName, LastName)
Donations (CustomerID, DatePlaced, StartDate, NumberOfMonths, ExpectedEndDate)

3rd NF:
There are two rules followed in Third Normal Form
Table must be in Second Normal Form
Transitive functional dependency of non- pirme attribute on any super key should be removed.
An attribute that is not part of any candidate key is known as non-prime attribute.
So according to these rules, the tables are already in 3rd NF because each of the attributes is
dependent on the primary key and here we just need to relate tables to one another.
Contributions (DonationID, CustomerID, CharityID, POCID)
Charity (CharityID, CharityName, CharityLocation)
POC (POCID, POCName, TelExtn)
Customer (CustomerID, FirstName, LastName)
Donations (DonationID, DatePlaced, StartDate, NumberOfMonths, ExpectedEndDate)
Boyce- Codd Normal Form
Here is DatePlaced in the donations table, the datePlaced column is depend on Contribution table.
Contributions (DonationID, CustomerID, CharityID, POCID,DatePlaced )
Charity (CharityID, CharityName, CharityLocation)
POC (POCID, POCName, TelExtn)
Customer (CustomerID, FirstName, LastName)
Donations (DonationID, StartDate, NumberOfMonths, ExpectedEndDate)

Part 2:
1st NF:
CustomerSupply(CustomerId,customerName,EmployeeId,phone,email,address,placedDate);
2nd NF:
Customer(customerID,firstName,lastNmae,phone,email,addressID,empID)
Employee(empID,firstName,lastName,phone,email,salary,addressID,startDate,endDate)
Address(addressID,address,city,state,zip)
Orders(orderID,purchaseID,supplierID,productID,date,total,resale)
Product(productID,name,color,size,price,quantity,description)
3rd NF:
Customer(customerID,firstName,lastNmae,phone,email,addressID,empID)
Employee(empID,firstName,lastName,phone,email,addressID,startDate,endDate)
Address(addressID,address,city,state,zip)
CustomerFacing(empID,productSpeciality,trainingHours,commissionRate)
InternalSupport(empID,salary,supportArea)
Suppliers_Purchasers(purchaseID,supplierID,customerId)
Orders(orderID,purchaseID,supplierID,productID,date,total,resale)
Product(productID,name,color,size,price,quantity,description)
Boyce- Codd Normal Form
Customer(customerID,firstName,lastNmae,phone,email,addressID,empID)
Employee(empID,firstName,lastName,phone,email,addressID,startDate,endDate)
Address(addressID,address,city,state,zip)

CustomerFacing(empID,productSpeciality,trainingHours,commissionRate)
InternalSupport(empID,salary,supportArea)
Purchasers(purchaseID,customerId)
Suppliers(supplierID,customerID)
Orders(orderID,purchaseID,supplierID,productID,date,total,resale)
Product(productID,name,color,size,price,quantity,description)
 Each table is in 3rd normal form, the attributes in the Employee table are all dependent on
the EmloyeeID where empID is primary key that is used not only fro employee table but
alsog the customer table. Employees help to the customer table.
 In customer table all non-prime column depends on the customerId that is a primary key
column.
 This is assumed that the customer can be a purchaser, supplier or both. There will be
unique ids are declared for purchaserID and/or supplierId so that vendors and customers
can be recognized in easily. The purchaser and supplier table both has customerID as a
foreign key.