‹header›
‹date/time›
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
‹footer›
‹#›
Functional dependence is essential for understanding the rest of the material in this section.
= a formal name for a simple idea
= the value of one column depends on another
= i.e. if you know the value of one column you can determine the value of the other column.
= other e.g. from the table:
Last name is functionally dependent on repNum.  i.e. if you are given the value of rep 20 you will find a single Last name associated with it.
What about First Name being functionally dependent on Last Name?
NO..because can have more than one occurrence of first name for a particular last name.
The main question to ask when determining functional dependence is-
Given one value of a particular column (A) can you find a single value for another column (B) upon which first column A is functionally dependent.
Or put another way
colA = f(colB)
So if have value of col B can determine A
On which columns is NumOrdered functionally dependent?
So you are asking what value/values do you need to know in order to arrive at a single value for NumOrdered.
Clearly, OrderNum is not enough – you can have different Number of orders for different parts  in a single order number.
How about OrderNum and PartNum?
Yes a combination of the two gives a single answer for number ordered – you can determine the Number Ordered of a particular part if you know the OrderNumber and the Part Number.
So how do you determine functional dependence?
Can really only doing it by examining user policies, discussions with users and documentation.
Be wary of using only samples of data as these can be misleading.
In the Part table..
Is class a primary Key?
i.e. Can you determine the value of all other columns in the table if you know the Class?
NO..Note the class can repeat with different PartNumbers.
What would be a suitable primary Key?
PartNum.
In some cases there may be more than one candidate for a primary key.
The other possibilities for a primary key are called candidate keys.
You simply choose one candidate key to be a primary key.  The remaining keys are called alternate keys.
In plainer english:
First normal form  - each field in the database table must contain different data – or rather single piece of data.
Note on notation:
The primary key is underlined.
Bracket use.
Note also once the table rows for an order were split the OrderNum alone could no Longer serve as a primary key since there would be more than one occurance and it fails to uniquely identify a row.
So need part Number as well.
General rule:
When  converting an unnormalised table to a first normal form,
The primary key will usually be the original primary key together with the key to the repeating group.
2NF only really applies when the primary key of the 1NF table contains more than one column.
In a second Normal form:
We have a table that is in First normal form (i.e. no repeating rows) PLUS
No field of data may be derived from another field.
If primary key of table contains only a single column = automatic 2NF
Note on primary key:
Need orderNum and PartNum both in order to determine the value of other columns (recall a column(s) is a primary key if the value of all other columns can be determined form it.
Order alone is no good because can occur more than once.
PartNum alone no good for same reason
But
Both together are fine.
3NF says that duplicate information is not allowed in the database.
First Normal Form - removal of repeating groups.
Second Normal Form - no non-key attributes must depend on a portion of the primary key.
Third Normal Form - no attributes must depend on other non-key attributes.? all attributes depend on key attributes????
Third Normal Form eliminates columns not dependant on a table's primary key.
First Normal Form
By definition, First Normal Form eliminates repeating groups. What this means is that a separate table must be made for each set of related attributes and give each table its own primary key. The best way to explain this is by example.
Let us say we have a customer database where customers make orders. In a spreadsheet we would have the customer name and address repeated for every order. Application of first normal form would divide the customers and orders into two separate entities or tables. The customer table would contain customer details without order details and the order table would contain order details with only a reference to the appropriate customer.
Second Normal Form
Second Normal Form eliminates redundant data. Thus if an attribute relies on only part of a multi-valued key, that attribute must be removed to a separate table. More specifically the Second normal form requires that no non-key attributes are dependant upon a portion of the primary key. A primary key uniquely identifies a instance within an entity. Second normal form applies to tables where constituents of the primary key effectively refer to repeating groups within the same table. These duplications should be removed to an additional table. For instance, if a student takes a course then the course that the student is taking is uniquely identifed by the student and the course as the primary key. Within this entitiy we also have descriptions of the course. Therefore the course decriptions will make up the contents of the additional entity and the descriptive details of the course would be removed from the table containing the student details. The result would be two tables, one with student details and the other with course details.
Third Normal Form
Third Normal Form eliminates columns not dependant on a table's primary key. This translates to the fact that if an attribute does not contribute to the description of a key then it must be moved to another table. Third normal form requires that all columns in a table contain data about the entity that is defined by the primary key. In other words each entity should have attributes applicable to itself only.
Recall: All columns in Table are functionally dependent on Primary key.  Other possibilities for primary key are candidate keys.
CustomerNum is the primary key in this case.
As expected all columns are functionally dependent on the primary key.
Or
Put another way all columns are determinants of the primary key customerNum.
However, RepNum is a determinant of LastName and FirstName only –not of all the other columns
Hence, RepNum is not a candidate key (if it was there wouldn’t be a problem)—
since remember All columns in Table are functionally dependent on Primary key/candidate keys.
The table is therefore not in 3NF.
Since Third Normal Form eliminates columns not dependant on a table's primary key.
First Normal Form
By definition, First Normal Form eliminates repeating groups. What this means is that a separate table must be made for each set of related attributes and give each table its own primary key. The best way to explain this is by example.
Let us say we have a customer database where customers make orders. In a spreadsheet we would have the customer name and address repeated for every order. Application of first normal form would divide the customers and orders into two separate entities or tables. The customer table would contain customer details without order details and the order table would contain order details with only a reference to the appropriate customer.
Second Normal Form
Second Normal Form eliminates redundant data. Thus if an attribute relies on only part of a multi-valued key, that attribute must be removed to a separate table. More specifically the Second normal form requires that no non-key attributes are dependant upon a portion of the primary key. A primary key uniquely identifies a instance within an entity. Second normal form applies to tables where constituents of the primary key effectively refer to repeating groups within the same table. These duplications should be removed to an additional table. For instance, if a student takes a course then the course that the student is taking is uniquely identifed by the student and the course as the primary key. Within this entitiy we also have descriptions of the course. Therefore the course decriptions will make up the contents of the additional entity and the descriptive details of the course would be removed from the table containing the student details. The result would be two tables, one with student details and the other with course details.
Third Normal Form
Third Normal Form eliminates columns not dependant on a table's primary key. This translates to the fact that if an attribute does not contribute to the description of a key then it must be moved to another table. Third normal form requires that all columns in a table contain data about the entity that is defined by the primary key. In other words each entity should have attributes applicable to itself only.