DBMS - FIRST NORMAL FORM

Normalization , First Normal Form, Database Management System

1. DATABASE MANAGEMENT SYSTEM Normalization

2. It was defined to disallow multi valued attributes, composite attributes and their combinations (Complex Attributes). Attribute values permitted by normal form are single atomic (or indivisible) It disallows “ relations within relations” or relations as attribute values within tuples ”. FIRST NORMAL FORM

3. Example DNAME DNUMBER DHEAD CSE 101 Raju IT 102 Ram ECE 103 Rahul DEPARTMENT Relation Schema Primary Key DNUMBER DNAME DNUMBER DHEAD DLOCATION CSE 101 Raju Thr , Tvm IT 102 Ram Thr ECE 103 Rahul Thr , Tvm DNUMBER is functionally dependent with DNAME, DHEAD Now we extend with the attribute DLOCATION DLOCATION contains atomic values but some tuples can have a set of values. DLOCATION is not functionally dependent on the primary key DNUMBER. The Domain of DLOCATION contain set of values and hence it is non-atomic.

4. Converting to First Normal Form Solution I :- Remove the attribute DLOCATION that violates 1NF and place it in a separate relation DEPT_LOCATION along with the primary key DNUMBER of the DEPARTMENT Schema DNAME DNUMBER DHEAD CSE 101 Raju IT 102 Ram ECE 103 Rahul DNUMBER DLOCATION 101 Thr 101 Tvm 102 Thr 103 Thr 103 Tvm DEPARTMENT DEPT_LOCATION Decomposes the non - 1nf relation into two 1nf relations

5. Solution II :- Expand the key so that there will be a separate tuple in the original DEPARTMENT relation for each location of a DEPARTMENT. Using this method redundancy occurs in data DNAME DNUMBER DHEAD DLOCATION CSE 101 Raju Thr CSE 101 Raju Tvm IT 102 Ram Thr ECE 103 Rahul Thr , ECE 103 Rahul Tvm Here DNUMBER and DLOCATION is Primary key of DEPARTMENT schema . DNUMBER , DLOCATION is the Partial Key

6. Solution III :- If maximum number of values is known for the attribute. For example at most three locations can exist for a department. Replace the DLOCATION attribute by three atomic attributes DLOCATION1, DLOCATION2, DLOCATION3. The disadvantage of introducing NULL values if most department have fewer than three locations. DNAME DNUMBER DHEAD DLOCATION1 DLOCATION2 DLOCATION3 CSE 101 Raju Thr Tvm NULL IT 102 Ram Thr NULL NULL ECE 103 Rahul Thr Tvm NULL

7. If queries contact : Manish T I E-mail: [email protected] [email protected] Mob: [phone] Reference:- Elmasri , Navathe . Fundamentals of Database Systems, Third Edition, Pearson Education , 2000 .