Link to home
Start Free TrialLog in
Avatar of hugonieto
hugonietoFlag 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,
SOLUTION
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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...

User generated image
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial