Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

is this third normal form

Posted on 2011-10-18
4
Medium Priority
?
277 Views
Last Modified: 2012-05-12
this is what i have at first
People:
Personal_ID, First Name, Last Name, Suffix

Address:
Address_ID, address1, address2, city, state, country

Phone:
Phone_ID, Phone_Number

Address_Book
Address_Book_ID, Personal_ID, Address_ID, Phone_ID

and it isn't in third normal form which surprised me because two other people in here said it is. so now i changed it to below. can you let me know if this is third normal form? thanks

People:
Personal_ID, First Name, Last Name, Suffix

Address:
Address_ID, Personal_ID, address1, address2, city, state, country

Phone:
Phone_ID, Address_ID, Phone_Number

Address_Book
Personal_ID, Address_ID, Phone_ID
0
Comment
Question by:StewSupport
  • 2
  • 2
4 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36990437
Okay. Let's start with what you originally had.
Third Normal form means that only items directly essential to the primary key are in the table.

People:
Personal_ID, First Name, Last Name, Suffix
(think that is fine)

Address:
Address_ID, address1, address2, city, state, country
(you may want to have a City table and possibly State and Country, most systems has Country table with full description then use three character code for example USA, CAN, MEX, etc., similarly OH, NY, etc. can be a key for states or provinces by country code as AL, CAN is different from AL, USA)

Phone:
Phone_ID, Phone_Number
(Are phone numbers really associated to addresses or people regardless of address? Answer that and you can decide how to move forward. May be a simple Person_ID, Phone_Number combination for example -- both would be key)

Address_Book
Address_Book_ID, Personal_ID, Address_ID, Phone_ID
(Personally, I would have an associative table with Personal_ID and Address_ID as the key -- other columns are unnecessary).

Hope that helps!
0
 

Author Comment

by:StewSupport
ID: 36990571
is this ok?
People:
Personal_ID, First Name, Last Name, Suffix, Address_ID
(think that is fine)

Address:
Address_ID, address1, address2, City_ID, State_ Code, Country_Code

City:
City_ID, City_Name, State_abbr

State:
State_abbr, State_Name, Country_Code

Country:
Country_Code, Country Name

Phone:
Person_ID, Phone_Number


0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36990628
What happens if you end up with more than one address per person?

PersonAddresses:
Person_ID, Address_ID

People:
Person_ID, First Name, Last Name, Suffix, Address_ID
-- remove the address_id and use the associative table above

Address:
Address_ID, address1, address2, City_ID, State_ Code, Country_Code
-- this is usually fine because not all City's go into a State outside the US.
-- the combination of the three fields, including City_ID, are the key to City table

City:
City_ID, City_Name, State_abbr
-- see above (and just ensure state_abbr is understood to be same ID code as state_code)

State:
State_abbr, State_Name, Country_Code
-- (ensure this is same ID code as state_code)
-- country_code and state_code are the key to this table per example on AL, CAN (Alberta) and AL, USA (Alabama).

Country:
Country_Code, Country Name

Phone:
Person_ID, Phone_Number
0
 

Author Closing Comment

by:StewSupport
ID: 36993364
thanks a million
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question