What is functional dependency? What is the need for normalization? Explain 2nd and 3rd normal form with example. Consider the following relation:
EMP_PROJ { SSn, Pnumber, Hours, Ename, Pname, Plocation}
Assume { SSn, Pnumber} as primary key. The dependencies are
{SSn, Pnumber}→ Hours; SSn →Ename; Pnumber → {Pname, Plocation}.
Normalize the above relation into 2NF.
Functional Dependency- Let Χ and Υ be the arbitrary subsets of a set of attributes of a relation schema R, then an instance r of R satisfies FD X →Y , if and only if for any two tuples t1 and t2 in r that have 
, they must also have 
That means, If R is a relation with attributes X and Y, a functional dependency between the attributes is represented as X → Y, which specifies Y is functionally dependent on X. Here X is a determinant set and Y is a dependent attribute. Each value of X is associated precisely with one Y value.
Objectives of Normalization
Second normal form (2NF)
A table is said to be in 2NF if both the following conditions hold:
Consider the Table1 in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, Student → Age, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys.
Table1
|
Student |
Age |
Subject |
|
Adam |
15 |
Biology |
|
Adam |
15 |
Maths |
|
Alex |
14 |
Maths |
|
Stuart |
17 |
Maths |
After splitting, there are two tables,
Table2 Table3
|
Student |
Subject |
|
Adam |
Biology |
|
Adam |
Maths |
|
Alex |
Maths |
|
Stuart |
Maths |
|
Student |
Age |
|
Adam |
15 |
|
Alex |
14 |
|
Stuart |
17 |
Third Normal Form (3NF)
Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.
Student_Detail Table:
In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.
New Student_Detail Table:
|
Student_id |
Student_name |
DOB |
Zip |
Address Table:
|
Zip |
Street |
city |
state |
EMP_PROJ
So, after removing the partial dependencies, there will be three tables,
Table1
Table2
Table3
State the steps involved in query processing. Why is the query optimization needed?
Write the rules of DBA.
What is Weak entity set? Explain with suitable example.
