Mr. Furxheir, R.M.W.
Group: admins
(1791 posts total)
(last post: November 24, 2007 - 15:07)
Citizen #20:
Martí- Paìr Furxhéir | First normal form
The first normal form is the very BASIC of normalization. It has 3 rules :
1 ) Eliminate duplicate information from the same table
2 ) Create seperate tables for each group of data
3 ) identify each row with a specific index ( primary key ).
In the first case, it is simple : If you have a field : FirstName and LastName, you cannot have a field "FullName".
This prevents redundancy and confusion.
The second is also simple.
For example, let's say I vote in a preferencial election, and assign 5 ranks, I will not have 1 table with :
--------SenateVote----------
CitizenNumber ( foreign key )
ElectionNumber ( foreign key )
VoteIndex ( primary key )
Rank1
Rank2
Rank3
Rank4
Rank5
-------------------------
I will instead have 1 table holding the master vote, and 1 table holding the ranks :
--------SenateVote----------
CitizenNumber ( foreign key )
ElectionNumber ( foreign key )
VoteIndex ( primary key )
-------------------------
--------SenateVoteRank-------------
VoteIndex ( foreign key ) PK
RankNumber PK
Vote
-------------------------
In this case, each vote will have multiple VoteRanks.
PK is for primary key. In this case, in the Vote Rank, the primary
key is composed of the VoteIndex AND the RankNumber.
You cannot uniquely identify a vote rank solely on the VoteIndex
or the RankNumber.
It is possible to have a primary key that is also a foreign key.
We will see that later.
Second normal form
The second normal form is there to eliminate duplicate information accross multiple rows.
We need to :
1 ) Remove data that apply to multiple rows and create new tables
2 ) Create a relationship with the new tables.
Let's say we have a Deputy seat table :
---- DeputySeat ----
CosaNumber (PK) Foreign Key
CitizenNumber (PK) Foreign Key
Party Initial
Party Name
Party Leader
---------------------
In this case, the Party Leader, Name and Initial is listed in EVERY seat of a party. This is not correct,
since once again we have duplication.
Instead, we must create a new table, PartyRegistration :
--- PartyRegistration ----
CosaNumber (PK) Foreign Key
Party Initials (PK)
Party Name
Party Leader
------------------------
And the deputy seat table will become :
---- DeputySeat ----
CosaNumber (PK) (FK)
CitizenNumber (PK) (FK)
Party Initial (FK)
---------------------
In this case, the Deputy seat has 3 foreign keys :
CosaNumber which links to the house of deputies election number
CitizenNumber, which links to the citizen
CosaNumber + Party Initial which links to the Party Registration.
Please note that the 3rd FK of Deputy seat has the SAME fields at the
Party Registration Primary Key. It is (almost) ALWAYS like that. The
Foreign Key of a table, is the primary key of another table.
Third normal form
This one goes even further : We must remove colums that are not dependant on the primary key.
The license plate number in the example is good. Unfortunatly, I cannot think of a good sample, because to be honest, I design my database to right away respect the Third Normal form rule ( but not alway the first 2 ).
Basically, to apply this form, you need to ask the question for every field : Is it tied to the primary key ?
In the case above :
---- DeputySeat ----
CosaNumber (PK) Foreign Key
CitizenNumber (PK) Foreign Key
Party Initial
Party Name
Party Leader
---------------------
Was the party name tied to a CosaNumber and citizen number ??? No,
it is tied to a party and a cosanumber.
Same for the party leader.
The only exception to the rule is for foreign keys, since in this case,
the Party Initial is the ID of a party, so we do not see it as caracterizing the party,
but rather as caracterizing the seat of the deputy...
I know I am not 100% clear. I know you are missing a lot of info to be able to grasp everything. I know I haven't explained fully what a primary and foreign are, or in fact, was a key is.
But I want you to understand the general concept of the normalization. As such, don't hesitate to ask questions, ESPECIALLY if they sound stupid. There is a good chance everyone else is too afraid to ask it !!!
You can always send it to me by e-mail, and I will answer it confidentially.
Why do you I ask you to understand normalization ?
1 ) It is what distinguish a good database from a bad one.
2 ) I what you to understand why I am doing certain things in a certain way.
I Met A Girl Named Ida
She Came Straight From Sweden
I Gave Her Cuba Libre
Now She Does Everything I Say - Eh
|
