Avatar of hugonieto
hugonieto
Flag for United States of America asked on

Database - Normalization

Hi guys!

I trying to figure out how 1NF, 2NF, and 3NF works!!!! I kind of understand it with a small raw data like: one table -

Orders(OrderNum, OrderDate, PartNum, Description, NumOrdered, QuatedPrice)

But I don't know how to go from 1st to 2nd to 3rd with more data!! For example:

Employee: Employee id., SSN, First name, Last name, Middle Init., Street address, City, State, Zip, Home Telephone, Cell Telephone number, Job Code, Job description, Salary code, Salary rate, Sex, age, hire date, birth date, Marital Status, Number of dependents, names of dependents, dependents ssn, insurance code(s), insurance description(s), y-t-d gross, y-t-d net, 401k, fed. tax, state tax, ss tax, emergency contact name, emergency telephone number, emergency contact relationship

Item : Item code, item description, size, shelve location code, shelve location description, Manufacturer code, manufacturer name, distributor code, distributor name, retail price, wholesale price, sale price, vendor id, vendor name, type code, type description

Manufacturer: manufacturer code, manufacturer name, street address, city, sate, zip, telephone number, contact name, contact telephone number,

Distributor: distributor code, distributor name, street address, city, state, zip, distributor telephone number, contact name, contact telephone number

Vendor: vendor code, vendor name, street address, city, state, zip, vendor telephone number, contact name, contact telephone number  

Customer Sales Sales id number, sales date, sales time, employee id who took the order, employee id who prepared order, food item(s) id, name(s), individual item cost, tax, payment type (cash or credit card), credit card number


These are 5 different tables but they are not in any form!!! It is giving me a headache to understand how to go from form to form with these data!! specially going from 2NF to 3NF!!! Can you give me a hand? I would appreciate it!!!  


Thanks,
MySQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ioannis Paraskevopoulos

And now time for the employees. It is the biggest one and the reason i started from the bottom!!!

Employee: Employee id., SSN, First name, Last name, Middle Init., Street address, City, State, Zip, Home Telephone, Cell Telephone number, Job Code, Job description, Salary code, Salary rate, Sex, age, hire date, birth date, Marital Status, Number of dependents, names of dependents, dependents ssn, insurance code(s), insurance description(s), y-t-d gross, y-t-d net, 401k, fed. tax, state tax, ss tax, emergency contact name, emergency telephone number, emergency contact relationship

Employees
(EmployeeID,SSN,FirstName,LastName,MiddleInit,StreetAddress,CityId,ZIPID, HomeTelephone,CellPhone,WorkID,SalaryId,SEX,Age,HireDate,BirthDate,MaritalStatus,YTDGross, YTDNET,401k,FEDTAX,StateTAX,SSTAX)

Cities, States, Works(jobs) are described on a previous post.

Salaries
(SalaryID,SalaryRate)

Dependents
(DependentID,EmployeeID,DependentFirstName,DependentLastName,DependentSSN)

EmployeeInsurance
(EmployeeId,InsuranceId)

Insurance
(InsuranceId,Description)

EmployeeContacts
(ContactId,EmployeeID,ContactName,ContactPhone,RelationID)

Relations
(RelationId,Description)

Giannis
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
hugonieto

ASKER
THANKS A LOT GUYS!! Thanks to all of you!!! This is great help!!!!!!!!! I tried something on my own..... how do think I did? this is the UNF table...

UNF table
1NF

Orders (OrderNum , OrderDate, PartNum, PartDesc, NumberOrdered, WareHuseCode, WareHouseCity, QuotedPrice)

Here I got rid of the reaping group by assigning a primary key to OrderNum and PartNum.

2NF

Orders (OrderNum , OrderDate)
Parts (PartNum, PartDesc)
OrderLine (OrderNum, PartNum, NumberOrdered, WareHuseCode, WareHouseCity, QuotedPrice )

Here I made sure that all non-key attributes are dependent of all parts of the primary key. I separated the ORDERS tables into different tables.

3NF


Orders(OrderNum , OrderDate)
Parts (PartNum, PartDesc)
WareHouse (WareHuseCode, WareHouseCity)
OrderLine (OrderNum, PartNum, NumberOrdered, QuotedPrice, WareHuseCode )

Here I made sure that all non-keys attributes are not dependent on any other non-key attributes.

ORDERS and PARTS tables are already in 3NF. I broke down the OrderLine to create a WareHouse table but I left the WareHouseCode on the OrderLine as a FK.

What do you think? Does it look right? or I did something wrong?


Thanks guys!
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy