Database Normalization and Decomposition: 1NF, 2NF, and 3NF Solution

This assignment solution details the process of database normalization, transforming a relation into 3NF form. It begins with a 1NF representation, ensuring no repeating data and identifying the candidate key. The solution then progresses to 2NF, eliminating partial dependencies, and subsequently to 3NF, removing transitive dependencies. The solution provides the breakdown of tables into CLIENT, TREATMENT, PAYMENT, and PAYMENT_TREATMENT tables. Each step is clearly explained, demonstrating the decomposition process. The relational schema is provided at the end. References to relevant database design books are included.

Dependency diagram
Normalization
Following steps are taken to decompose the relation into 3NF form:
Step 1: 1NF – In this step, there is no repeating data i.e. no multiple columns accessing same
row. Here the candidate key is {cl_id, pmt_ref).
Cl_id Cl_name Pmt_ref Pmt_amt Tr_type Tr_Ses_Price Tr_date
C304 Jane Cooper 20167900 $155 Hair Removal $155 20/5/2016
C502 Tom Packer 20167910 $140 Spray tanning $245 11/10/2016
C421 Lisa New 20167841 $89 Facial treatments $89 3/8/2016
C502 Tom Packer 20167230 $90 Deep pore purifying $195 20/10/2016
C405 Ned Nelly 20168720 $92 Microhydrabrasion $185 7/9/2016
C421 Lisa New 20169531 $89 Facial treatments $89 5/9/2016
C309 Kelly
Toppak
20166951 $90 Skin Rejuvenation $360 15/11/2016
C304 Jane Cooper 20166500 $100 Hair Removal $155 5/6/2016
Step 2: 2NF – In this step, there is no partial dependency of any column on the primary key.
CLIENT
Cl_id Cl_name
C304 Jane Cooper

C502 Tom Packer
C421 Lisa New
C502 Tom Packer
C405 Ned Nelly
C421 Lisa New
C309 Kelly Toppak
C304 Jane Cooper
TREATMENT
Tr_type
Hair Removal
Spray tanning
Facial treatments
Deep pore
purifying
Microhydrabrasio
n
Facial treatments
Skin Rejuvenatio
n
Hair Removal
PAYMENT
Cl_id Pmt_ref Pmt_amt
C304 20167900 $155
C502 20167910 $140
C421 20167841 $89
C502 20167230 $90
C405 20168720 $92
C421 20169531 $89

C309 20166951 $90
C304 20166500 $100
PAYMENT_TREATMENT
Pmt_ref Tr_type Tr_Ses_Price Tr_date
20167900 Hair Removal $155 20/5/2016
20167910 Spray tanning $245 11/10/2016
20167841 Facial treatments $89 3/8/2016
20167230 Deep pore purifying $195 20/10/2016
20168720 Microhydrabrasion $185 7/9/2016
20169531 Facial treatments $89 5/9/2016
20166951 Skin Rejuvenation $360 15/11/2016
20166500 Hair Removal $155 5/6/2016
Step 3: 3NF – All transitive dependencies are removed.
All above mentioned tables are in 3NF form.
Relational Schema
CLIENT(CL_ID, CL_NAME)
TREATMENT(TR_TYPE)
PAYMENT(PMT_REF, PMT_AMT)
PAYMENT_TREATMENT(PMT_REF, TR_TYPE, TR_SEC_PRICE, TR_DATE)
References
Churcher, C. (2007). Beginning database design. Berkeley, CA: Apress.
Harrington, J. (2002). Relational database design clearly explained. New York: Morgan
Kaufmann Publishers.