International Journal of Emerging Technology and Advanced Engineering Website: www.ijetae.com (ISSN 2250-2459, Volume 2, Issue 7, July 2012) 394 Study of Various Normal forms and Functional Dependency Savita B. Chavan 1 , Dr. B. B. Meshram 2 1 M.Tech Computer Engineering, V.J.T.I, Mumbai. 2 Head of Computer Technology Department, V.J.T.I, Mumbai. 1 savita.chavan08@yahoo.com 2 bbmeshram@vjti.org.in Abstract: Normalization and functional dependency are most fundamental part in relational database. The normalization rules are designed to prevent update anomalies and data inconsistencies. The normal form defines in relation database theory represent the guidelines for record design. The guidelines corresponding to first through fifth normal form are presented. Normalization process depends on the single analytical tool called as functional dependency. The concept of functional dependency is useful in design and analysis of relational database. By applying the functional dependencies to relational database we can represent the relation in various normal forms. Keywords: Normalization, functional dependency , 1NF, 2NF, 3NF, Boyce-Codd normal form, 4NF, 5NF, multivalued dependency , joint dependency, full functional dependency, partial functional dependency. I. INTRODUCTION Normal forms defined in relational database theory represent the guidelines for record design [1]. The guidelines corresponding to first through fifth normal form. The normalization first proposed by codd [2] takes a relational schema through the series of test to certify where it satisfied the certain normal form [3]. Codd proposed three normal form which he called 1NF, 2NF, 3NF [1][2][3][4]. The strongest normal form 3NF is then defining as Boyce codd normal form [1][2][3]. He gives the ways to convert the relational schema not in given normal form into one of that by using normalization. The all normal form are based on single analytical tool called functional dependency [1][2][3][4][5]. Later on fourth normal form (4NF)[1][2[3] and fifth normal form (5NF)[1][2][3] are dependent on the multivalued functional dependency[1][3] and joint functional dependency respectively[3][4]. The functional dependency is the properties of semantics. The database designer will use their understanding of semantics of attributes of relation i.e how they are related to one another to specify the functional dependency that should holds on all relation states of relational schema. II. RELATED WORK 2. 1 Functional Dependency We formalize the notation of FDs and their definition and also define the inference rules for functional dependency. A functional dependency is a constraint between two sets of attributes from the database. Definition: A functional dependency (FD) is a statement of the form X  Y, where X and Y are sets of attributes. The FD X  Y is said to hold for a relation R if every pair of tuples of R that agrees on each of the attributes in X also agrees on the attributes in Y. That is, the FD X Y holds for relation R if whenever s and t are tuples of R where s[X] = t[X], then s[Y] = t[Y]. Thus X functionally determines Y in the relation schema if and only if whenever two tuple of r(R) agrees on their x values, they must necessarily agree on their Y values. Note the following If the constraint on R states that there cannot be more than one tuple with given X values in any relation instance r(R)- that is X is the candidate key of R- this implies that XY for any subset of attribute Y of R . If XY in R, this does not say whether or not YX in R. This can be proved by using example given below Consider relation schema EMP_PROJ From semantic of attribute we know the following functional dependencies hold a) SsnEname b) Pnumber{Pname, Plocation} c) {Ssn,Pnumber}Hours 2.2 Inference Rules for Functional dependency Inference rules are means to construct these implied dependencies. A system of inference rules is said to be complete when every implied dependency can be derived by (repeated) applications of these rules. The following is a complete system of inference rules for functional dependencies: F1 (Reflexivity): If Y C X, then XY.