@ Infosys Technologies Limited Relational Database Management System 3. Normalization lo human investigation can be called real science if it can not be demonstrated mathematically.” -Leonardo da Vinei | 3.1. Introduction Usually in the software industry designers use E-R modeling as a requirement analysis tool. Database design using E-R diagram is a by-product. Database designed based on the E-R model may have some amount of inconsistency, ambiguity*® and redundancy. To resolve these issues we have to do some amount of refinement is required. This refinement processis called as Normalization ‘As normalization involves building structures (like table/ tables), starting from the stage of identifying the columns (attributes) associated in the table, it is also called “Bottom-Up” approach. This normalization technique is based on a strong mathematical foundation. Basically normalization eliminates the duplicate data and makes insert, update and delete operations much more efficient in terms of performance and space requirement to store the data In Infosys, almost all the database designs are initially based on E-R modeling and later refined using normalization techniques before they are physically created. | 3.2. The need for Normaliz: Consider a university scenario, where in the data associated with the students, courses and their results are maintained in a table called “Student_Course Result”. ® Ambiguity: Uncertainty. ERY CORP) CRS’ DBO7/ 002 Version No. 1.0 Page 57@ Infosys Technologies Limited Relational Database Management System Student_Course Result Table ‘Student Details Course Details Result Details tor Davis 1114/1986 | M4 Applied Mathematics Basic Mathomatics 7| tirv2008 82 A 102 Daniel 1116/1987 | M4 Applied Mathomaties Basic Mathematics 7| aiv2004 62 ¢ tor Davis 1114/1886 | HE American History 4| 122004 73 B 103 Sandra 1012/1988 | C3__Bio Chemistry Basic Chemistry | ti62008 6s B 108 Evelyn 222/198 | BS Botany | 11262008 7B 102 Daniel 116/987 | P Nuclear Physics Basic Physics 13| 1122008 ot 8 105 Susan aiatiises | PS Nuclear Physics Basic Physics 13| 122008 a8 A 102 Sandra toznee8 | 84 Zoology | 1a7n004 54 D 105 susan 9/31/1985 | H6 American History | rez2004 87 A 106 Evelyn 2/22/1986 | Ma_Applied Mathematics Basic Mathematics 7] wiv2004 656 Figure 3-1: Data file in table format If we observe the table shown in Figure 3-1 closely, we would find that the table has many anomalies”. They are Insert Anomaly In some cases, Insertion of new datais difficult. Example: We cannot insert prospective course which does not have any registered student or we can not insert student details who is yet to register for any course. Update Anomaly In some cases, Updation of existing data is difficult Example: If we want to update the course M4’s name we need to do this operation three times. Smilarly we may have to update student 103's name twice if it changes. Delete Anomaly In some cases, deletion of existing data isnot possible Example: if we want to delete a course M4, in addition to M4 course details, other critical details of student also will be deleted. This kind of deletion is harmful to business. Moreover, M4 appears thrice in above table and needs to be deleted thrice. Duplicate Data The table has lots of duplicate data. Example: Course M4's data is stored thrice and student 102's data stored twice. This redundancy will increase as the number of course offering and students increases. Hence we need to refine our design so that we make an efficient database in terms of storage space and Inserts, Updates and Deletes operations. This refining technique is called as normalization. * Anomalie: Irregularities. ERY CORP/ CRS’ DBN7/ 002 Version No. 1.0 Page 58© Intosys Technologies Limited Felational Database Management System 3.3. Process of Normalization ‘As mentioned previously, normalization technique is based on strong mathematical foundation. Basically in software industry four normal forms are used to design the database. Before getting to know the normalization techniques in detail, let us define a few building blocks which are used to define normal forms. 3.3.1. Determinant Attribute X can be defined as determinant if it uniquely defines the attribute value Y in a given relationship or entity. To qualify as determinant attribute need NOT be a key attribute. Usually dependency of an attribute is represented as X > Y, which means attribute X decides attribute Y. Example: In PESULT relation, Marks attribute may decide the grade attribute. This is represented as Marks > Grade and read as Marks decides Grade. Marks Grade Figure 3-2: Determinant In the RESULT relation, Marks attribute is not a key attribute. Hence it can be concluded that key attributes are determinants but not all the determinants are key attributes. ERS Functional Dependency Consider the following Relation REPORT (Student #, Course#, CourseName, IName, Foom#, Marks, Grade) Where: * Sudent#- Sudent Number Course# - Course Number CourseName - Course Name IName - Name of the instructor who delivered the course Foom# - Room number which is assigned to respective instructor Marks - Scored in course Course# by student [Link]# Grade - Obtained by student Student# in course Course# ER CORP/ CAS DBO7/ 002 Version No. 1.0 Page 59© Intosys Technologies Limited Felational Database Management System Student# Course# together (called composite attribute) defines EXACTLY ONE value of marks. This can be symbolically represented as Student# Course# > Marks This type of dependency is called as functional dependency. In above example marks is functionally dependent on Student Course#. Other functional dependencies in above examples are: * Course > CourseName, * Course# > IName (Assuming one course is taught by one and only one instructor) + IName > Poomi# (Assuming each instructor has his/her own and non-shared room) * Marks > Grade. Formally we can define functional dependency as: In a given relation R, X and Y are attributes. Attribute Y is functionally dependent on attribute X if each value of X determines EXACTLY ONE value of Y. Thisis represented as: X>Y However X may be composite in nature. 3.3.3. Full Functional Dependency In above example Marks is fully functionally dependent on Sudent# Course# and not ‘on sub set of Sudent# Course#. This means Marks can not be determined either by Student# OR Course alone. It can be determined only using Student# AND Course# together. Hence Marks is fully functionally dependent on Student# Course#. CourseName is not fully functionally dependent on Sudent# Course# because one of the subset Course# determines the CourseName and Sudent# does not have any role in deciding CourseName. Hence CourseName is not fully functionally dependent on Suudent# Course#. ‘Student y Marks 4 Course# Figure 3-3: Full Functional Dependency ER CORP/ CAS DBO7/ 002 Version No. 1.0 Page 60© Intosys Technologies Limited Felational Database Management System Formal definition of full functional dependency is: In a given relation R, X and Y are attributes. Yisfully functionally dependent on attribute X only if it is not functionally dependent on sub-set of X However X may be composite in nature. 3.3.4. Partial Dependency In the above relationship CourseName, IName, Room# are partially dependent on composite attributes Sudent# Course# because Course# alone defines the CourseName, IName, Room#. studentt | cotesname | ~, P=S a c— 5) Figure 3-4: Partial Dependency Formal definition of partial dependency is: In a given relation RX and Y are attributes. attribute Yis partially dependent on the attribute X only if it is dependent on subset of attribute X. However X may be composite in nature. 3.3.5. Transitive Dependency In above example, Room# depends on IName and in turn IName depends on Course#t Hence Foom# transitively depends on Course#t. Catt + Ra > Figure 3-5: Transitive Dependency Smilarly Grade depends on Marks, in turn Marks depends on Sudent# Course# hence Grade fully transitively ® depends on Sudent# Course#. 3.3.6. Key attributes In a given relationship Rif the attribute X uniquely defines all other attributes, then the attribute X is a Key attribute which is nothing but the candidate key which is defined in Chapter One. * Transitive: In-direct, ER CORP/ CAS DBO7/ 002 Version No. 1.0 Page 61@ Infosys Technologies Limited Relational Database Management System Example1: Student# Course# together is a composite key attribute which determines all attributes in relationship REPORT (Qudent#,Qourse#, CourseName, IName, Roomé#, Marks, Grade) uniquely. Hence Student# and Course# are key attributes. Example2: Student and EMaillD also can be considered as candidate keys for entity student STUDENT(Student#, StudentName, DateofBirth, EMailD). Student# or EMailID uniquely defines all other attributes of student entity. [3.3.7. Non key attributes In a given relationship Rall the attributes which are not key attributes are considered as non-key attributes. | 3.4. Types of Normal Forms { 3.4.1. First Normal Form (1 NF) Arelation Ris said to be in the first normal form (1NF) if and only if all the attributes of the relation Rare atomic® in nature. Consider the Sudent_Course_Result table which is reproduced from the Section 3.2 The need for Normalization. Student _Course Result Table ‘Student Details Course Details Results OT Davis 11/4/1986 | M4 Applied Mathematics Basic Mathomatics 7 vv2000 a2 A 102 Daniel 11161987 | M4 Applied Mathematics Basic Mathomaties 7| aiv200a 62 ¢ tor Davis 1114/1986 | H6 American History 4 re22004 73 B 103 Sandra 1012/1988 | C3 Bio Chemistry Basie Chemistry | ri162008 65 B 108 Evelyn 222/198 | BS Botany | 11262000 7B 102 Daniel 116r1987 | PS Nuclear Physics Basic Physics 13| 1122000 68 B 105 susan aatiees | P3 Nuclear Physics Basie Physics 13| rr22000 988 102 Sandra roznee8 | 84 Zoology | 1a7n004 54D 105 Susan 91/1985 | H6 American History | rezm004 87 A 108 Evelyn 2/22/1986 | Ma_Applled Mathematics Basic Mathematics 7] wiv2004 658 Figure 3-6: Data file in table format Table shown in Figure 3-6, Student_Details, Course_Details and Results attributes can be further divided. Sudent_Details attribute is divided into Sudent# (Sudent Number), StudentName (Student Name) and DateofBirth (Date of Birth). Course_Details attribute is divided into Course# (Course Number), CourseName, ® Atomic: the smallest level to which data may be broken down and remain meaningful ERY CORP) CRS’ DBN7/ 002 Version No. 1.0 Page 62@ Infosys Technologies Limited Relational Database Management System Prerequisites and Duration. Similarly Results attribute is divided into DateofExam, Marks and Grade. To make above table 1NF compliant, it is re-designed as shown below. Student_Course_Result Table SOE STE Er ‘Applied Basic TTNov- 101 | Davis Ma___| Mathematics | Mathematics 7 | 2008 ala ‘Applied Basic New 102 | Daniel Ma___| Mathematics | Mathematics 7 | 2008 ic ‘American 22Now- 101 | Davis Hs History. | 2004" ple Basie T6Nev- 108 | Sandra cs Bio Chemistry | Chemistry 1 | 2004) 65/8 26-Nov- 108 | Evelyn Ba Botany 3 | 008 mle ‘Nuclear Basie 2Nov- 102 | Daniel Ps Physics Physies 13 | 2004) sole Nuclear Basle TaNew 105 | susan Pa Physies Physics 13 | 2004 ala 27Nov- 103 | Sandra Bs Zoology s | 2008 sa|o ‘American [Link]= 105 | susan He History. | 2004" ala ‘Applied Basie Nev 108 | Evelyn Ma___| Mathematics _| Mathematics 7 | 2008 ole Figure 3-7: First Normal Form In the new form, all the attributes are atomic, meaning they are not further decomposable*’. You can not divide Sudent#, SudentName etc further into smaller attributes. Hence this table isin 1NF. Let us re-visit the issues we had with un-normalized table, Even at this stage, it is difficult to add prospective course or student information. Sill it is difficult to update or delete either Course or Qudent information. Hence anomalies in inserts, updates and deletes are still to be resolved. Unfortunately first normal form has all the problems which we faced in un-normalized table. 3.4.2. Second Normal Form (2 NF) ‘A Relation is said to be in Second Normal Form if and only if + Itisin the First normal form, and * No partial dependency exists between non-key attributes and key attributes. Let us re-visit 1NF table structure. * Decomposable: further split or reduce. ERY CORP/ CRS’ DBO7/ 002 Version No. 1.0 Page 63@ Infosys Technologies Limited Relational Database Management Systern * Sudent# is key attribute for Sudent, © Course# is key attribute for Course * Sudent# Course# together form the composite key attributes for Result relationship * Other attributes like SudentName (Sudent Name), DateofBirth, CourseName, PreRequisite, DurationinDays, DateofExam, Marks and Grade are non-key attributes. To make this table 2NF compliant, we have to remove all the partial dependencies. + SudentName and DateofBirth depends only on Student# + CourseName, PreRequisite and DurationInDays depends only on Course# + DateofExam depends only on Course# To remove this partial dependency we need to split Sudent_Course_Result table into four separate tables, STUDENT, COURSE, RESULT and EXAM_DATE tables as shown in Figure 3-8, STUDENT TABLE ‘COURSE TABLE SC Tro Ceti 101 | Davis ou Nov-1986 Basie 102 | Daniel 06-Nov- mt " 103 | Sanda ua a + 108 | Evelyn Ft 105 | Susan 31-Aug-1985 6 History 4 106 | mike ow-Fob-1907 Basie 107 | Juliet 09-Ne 986 cal Chemistry | s 108 | Tom or oct 1886 cx___| Chemisty | ot " 109 06-Jun-1908 83 Botany 8 P Physics 2 Nuclear Pa Physics Pt 2 Ba Zoology 5 RESULT Tabi AS EXAM_DATE Table 1 Me mA sat ae Em Cem ‘ToT | HE 73/8 Ma 2008 103 | C3 Ca He 2008 wa me) es nara 102 | Ps ol 105 | PS a fA P3 T2Nov-2008 zie ate | a FRev-2008 705 FS wR oa |e ou Figure 3-8: Second Normal Form ERY CORP/ CRS’ DBN7/ 002 Version No. 1.0 Page 64@ Infosys Technologies Limited Relational Database Management Systern In the first table (STUDENT), the key attribute is Student# and all other non- key attributes, SudentName and DateofBirth are fully functionally dependant on the key attribute In the second table (COURSE), Course# is the key attribute and all the non-key attributes, CourseName, PreRequisite and DurationlnDays are fully functionally dependant on the key attribute In third table (RESULT) Student# Course# together are key attributes and all other non key attributes, Marks and Grade are fully functionally dependant on the key attributes In the fourth table (EXAM_DATE) Course# is the key attribute and the non-key attribute, DateOtExam is fully functionally dependant on the key attribute These four tables are also compliant with the First Normal Form definition. Hence these four tables are in Second Normal Form (2NF) At first look it appears like all our anomalies are taken away! Now we are storing Student 103 and M4 record only once. We can insert prospective students and courses at our will. We will update only once if we need to change any data in STUDENT, COURSE tables. We can get rid of any course or student details by deleting just one row. Let us analyze the following table. EE EE eee] 101 | ma aia 102 | ma ic 101 | He 7318 103 | es eis 04 | Bs 718 102 | bs als 105 | Ps. ao) A 103 | Ba 540 105 | He aia 104 [ ma 16 Figure 3-9: RESULT Table We already concluded that All the attributes are atomic in nature No partial dependency exists between the key attributes and non-key attributes. RESULT table isin Second Normal form (2NF) Assume, at present, as per the university evaluation policy, Students who score more than or equal to 80 marks are awarded with “A” grade ‘Students who score more than or equal to 65 up till 79 gets“B" grade ‘Sudents who score marks more than or equal to 50 up till 64 fetches “C’ grade ‘Students who score marks less than 50 is only “D" grade ERY CORP/ CRS’ DBN7/ 002 Version No. 1.0 Page 65@ Infosys Technologies Limited Relational Database Management System The university management which is committed to improve the quality of education, wants to change the existing grading system to a new grading system as given below. * “As” grade for 95 and above grade for 85 to 94 B’ grade for 70 to 84 8" grade tor 65 to 69 “C" grade for 55 to 64 D’ grade for 45 to 54 “E" grade for less than 40 In the present RESULT table structure, + We do not have an option to introduce new grades like A+, B- and E * We need to do multiple updates on the existing records to bring them to the new grading definition. + We will not be able to take away "D’ grade if we want to. + 2NF does not take care of all the anomalies and inconsistencies. 3.4.3. Third Normal Form (3 NF) Arelation Ris said to be in the Third Normal Form (3NF) if and only if © It isin 2NF and + No transitive dependency exists between non-key attributes and key attributes In the above RESULT table Sudent# and Course# are the key attributes. All other attributes, except grade are non-partially, non-transitively dependent on key attributes. The “Grade” attribute is dependant on “Marks” and in turn “Marks” is dependent on Sudent# Course#. To bring this table to third normal form we need to take off this transitive dependency After taking this transitive dependency we can infer the following table structures which are in 3NF. | Susensi Core] es rz Ey eee rer 2 sor | ma 102 [ua 62 100 95 | Ae 101 | He 72 9 asta 103 | es 5 a4 7 108 [es 7 8 a 02 | Ps se 64 55 sosjes | __so| 54 as [0 soa}e4 | _ sa} 4 ole 10s [He 108 [ua 5 Figure 3-10: Third Normal Form ERY CORP) CRS’ DBO7/ 002 Version No. 1.0 Page 66@ Infosys Technologies Limited Relational Database Management System After normalizing tables to Third Normal Form (3NF), we got rid off all the anomalies and inconsistencies, Now we can add new grade systems, update the existing one and delete the unwanted ones. Hence the Third Normal Form is the most optimal normal form and 99% of the databases which require efficiency in + INSERT + UPDATE and © DELETE Operations are designed in this normal form: {3.4.4. Boyce Codd Normal Form (BCNF) A relation is said to be in Boyce Codd Normal Form (BONF) if and only if all the determinants are candidate keys. BONF relation is a strong 3NF, but not every 3NF relation is BONF, Let us understand this concept using slightly different Result table structure. EE 101 | Davis@[Link] | ma Davel@muni eds | ue oe 101 | Davis@myuniedu | W6 79 vo [Sendie@mun. edd] 2 = toe | Everio ot | 3 7 vor [Dasist@mvusiaas rs tos [Susan@myutia ws vo Sandg@mn od oe ra ‘os Susangomur aay | ra soe Evelrtomn od [ut s Figure 3-11: RESULT Table Figure 3-12: Overlapping Candidate Key ERY CORP) CRS’ DBO7/ 002 Version No. 1.0 Page 67@ Infosys Technologies Limited Relational Database Management Systern In the Figure 3-11 RESULT table, we have two candidate keys namely Student# Course# and Course# Emailld. Course# is overlapping among those candidate keys. Hence these candidate keys are called as “overlapping candidate keys” which is shown in Figure 3-12. The non-key attribute, Marks is non-transitively and fully functionally dependant on key attributes. Hence this is in 3NF. But this is not in BCNF because there are four determinants in this relation namely: + Sudent# (Sudent# decides EmailiD) * EvailID (EmaillD decides Sudent#) * Sudent# Course# (decides rest of the attributes in RESULT table) * Courset# EvaillD (decides rest of the attributes in RESULT table) All above determinants are not candidate keys. EMailID decides Sudent# but EVaillD ‘on its own is not a candidate key. Smilarly Sudent# decides BvaillD of a student but Student# alone is not a candidate key. Only combination of Student# Course# and Course# EvaillD are candidate keys. To make this table BONF, we need to split this table into the following structure’ sre tame P suudente || Course [Marks] tot | Davis@omwur edu see ws 2 102 | Daniel@[Link] 101 He 7 tas | Sanda@mwin edu ‘os 3 ‘s tot | Evelgn@[Link] oe] 3 7 105 | Susan@[Link] soe rn sos a 2 70s ie Figure 3-13: Boyce Codd Normal Form Now both the tables are not only in 3NF, but also in BCNF because all the determinants are candidate keys. In the first table, Sudent# decides EVaillD and EMaillD decides Sudent# and both are candidate keys. In second table, Sudent# Course# is only determinant and candidate key. Hence it qualifies BCNF definition that every determinant must be a candidate key. Note: If the table has only one non-composte candidate key C and if it isin 3NF, then the table will also be in BCNF. Basically 2NF and 3NF takes away the redundancy, anomalies which exist among the key and non-key attributes on other hand BONF takes away the redundancy, anomalies ERY CORP/ CRS’ DBN7/ 002 Version No. 1.0 Page 68@ Infosys Technologies Limited Relational Database Management System which exist among the key attributes. At Infosys, we rarely (around 1%of database design) normalize the databases to BONF. 3.5. Merits and Demerits of Normalization The following sections discuss merits and demerits of normalization, 3.5.1. Merits 1) Normalization is based on mathematical foundation. 2) Removes the redundancy to the greater extent. After 3NF, data redundancy is minimized to the extent of foreign keys. 3) Removes the anomalies present in Inserts, Updates and Deletes. 3.5.2. Demerits 1) Data retrieval (Select) operation performance will be severely affected. Example: Let us assume that the university management wants to have the report of students performance in the following format ur ERSITY REPORT rrr CT Gen Daniel Applied Mathematics | 11-Novoa | ¢ Danie! Nuclear Physics 12Nov0s | 8 Davis ‘Applied Mathematics [11-Novioa | A Davis ‘American History | 22-Nov04 | & Evelyn Botany 26Novos |B Evelyn ‘Applied Mathematics | 11-Nov0a | B ‘Sandra Blo Chemistry 16Novos | B ‘Sandra Zoology 2rNov04 | D ‘Susan Nuclear Physies 12Nowos | A ‘Susan Americantistory | 22-Novoa | A Figure 3-14: Proposed University Report Aiter applying SNF normalization technique for database design, a single table will not contain all the information as desired by the college management We need to select Sudent Name from STUDENT table, Course Name from COURSE table, Date of Examination trom EXAM_DATE table and Grade from MarksGrade table. In an un-normalized format we would have retrieved all these columns just from one table. Hence normalization will definitely slow down the Select operations. It is better to restrict normalization process to 2NF, if application has more data retrieval operations than insert or update or delete operations. ERY CORP) CRS’ DBO7/ 002 Version No. 1.0 Page 69@ Infosys Technologies Limited Relational Database Management System If the application is used for querying a database, it is called as“ Reporting System”. Let us take the example of a Railway enquiry system. This enquiry system is used to enquire about reservation availability and not used to book the tickets. On the other hand a Railway reservation system is called as “On-line application’ because this system is used for booking tickets (inserts), changing travel plans (updates) and canceling tickets (deletes). Hence one may normalize only up to 2NF for Reporting System and 3NF for Online applications, 2) Normalization may not always represent real world scenarios. It should be borne in mind however that full normalization may not always be desirable and the database designer may take advantage of his/her intimate knowledge of the real world and choose not to normalize in some particular instance. Example: consider the following relation: CUSTOMER (Name, Street, City, Postcode. Grictly speaking, the attribute Postcode uniquely identities City, hence transitive dependency exists in the above scenario. Postcode -> City Thus CUSTOMER table is not in 3NF. However in practice the attributes City and Postcode are always used together as a unit and decomposing the relation would not be advisable in this case. Note: Some time to increase the performance of select operations for reporting application, database design is taken back from higher normal form to lower normal form (ex: 3NF to 2NF). This processis called as de-normalization or Second Level Design (SLD). 3.6. Summary * Normalization is a refinement process. It helps in removing anomalies present in insert, update and delete operations. * Normalization is also called “Bottom-up approach”, because this technique requires minute details like every participating attribute and how it is dependant on the key attributes is crucial. If you add new attributes after normalization, it may change the normal form itself. There are four normal forms that were defined being commonly used ANF makes sure that all the attributes are atomic in nature. 2NF removes the partial dependency. 3NF removes the transitive dependency. BCNF removes dependency among key attributes Excessive normalization adversely affects select or retrieval operations. ERY CORP) CRS’ DBO7/ 002 Version No. 1.0 Page 70@ Infosys Technologies Limited * It is always better to normalize to SNF for insert, update and delete intensive Relational Database Management Systern (online transaction) systems. * It is always better to restrict to 2NF for select intensive (reporting) systems. While normalizing, use common sense and don't use the normal forms as absolute measures, Points to Remember: Test Remedy (Normalization) Relation should have atomic attributes. The domain of an attribute must include only atomic (simple, indivisible) values, Form new relations for each non-atomic attribute For relations where primary key contains multiple attributes (composite primary key), non-key attribute should not be functionally dependent on a part of the primary key. Decompose and form a new relation for each partial key with its dependent attribute(s). Retain the relation with the original primary key and any attributes that are fully functionally dependent on it Relation should not have a non-key attribute functionally determined by another non-key attribute (or by a set of non-key attributes). In other words there should be no transitive dependency of a non-key attribute on the primary key. Decompose and form a relation that includes the non-key attribute(s) that functionally determine(s) other non-key attribute(s). * | | Normal Form * * TNF ‘2NF SNF ERY CORPY CRS/DB07; 002 Version No. 1.0 Page 71@ Infosys Technologies Limited Relational Database Management System 3.7. Case study Given below is the data in an un-normalized table. Normalize it to 1F. Identify the problems encountered when the table is in 1NF but not in 2NF, Subsequently normalize to 2NF and 3NF, explaining the problems faced and the solution to it [Proj_No | Proj_Name | Emp_No | Emp_Name | Rate_Category | Hourly Rate _in_dollars 1023 | Amsterdam | 101 Vincent R_[A 60 travel site | 102 Pauline |B 50 103 Charlesc | C 40 7056 | Real Estate 101 Vincent R_[A 60 Agency 107 DavidR = |B 50 Solution: Table (1NF) Proj_No | Proj_Name | Emp_No | Emp_Name | Rate_Category | Hourly Rate_ in_dollars 1023 Amsterdam | 101 Vincent R [A 60 travel ste 1023 | Amsterdam | 102 Pauline J |B 50 travel ste 1023 Amsterdam | 103 Charles C [o 40 travel ste 1056 | Real Estate | 101 Vincent R_ [A 60 Agency 1056 | Real Estate 107 DavidR [8 50 Agency Problems encountered when the table isin 1NF but not in 2NF: (i) Wastage of space: Information that code 1023 refers to the Amsterdam travel site appears three (3) times. (ii) “Update Anomaly: If the project name has to be changed, it has to be done in all the rows that the project name appears in. If it has not been changed in just one row, this may lead to inconsistency problems. (iii) Insert Anomaly: The information about a new employee cannot be inserted into the table unless the employee is assigned to a project. ERY CORP) CRS’ DBO7/ 002 Version No. 1.0 Page 72@ Infosys Technologies Limited Relational Database Management Systern (iv) Delete Anomaly: if there is only one employee working on a project, it is not possible to delete information about the employee without losing information about the project. In other words it is not possible to delete a subset of a record. Solution: Normalize to 2NF (a) Take out the duplication (b) Look for partial dependencies i.e. fields that are dependent on a part of a key and not on the entire key. In the above table, the key is (Proj_No, Emp_No) The functional dependencies are as follows: Proj_No > Proj_Name Emp_No > Emp_Name, Rate_Category, Hourly Rate_in_Dollars Rate_Category > Hourly Rate_in_Dollars The above table should be decomposed as follows: Employee_Project Table Proj_No Emp_No 7023 101 1028 102 [to2s «108 1056 101 1056 107 Employee Table Emp_No | Emp_Name Rate_Category | Hourly Rate_in_Dollars 101 Vincent R 60 102 Pauline J 50 103 Charles C 40 [107 David R 50 Project Table Proj_No Proj_Name 1023 ‘Amsterdam Travel site 1056 Real Estate Agency Problems faced with the table in 2NF (i) Sores data redundantly: The Hourly Rate_in_Dollars and Rate_Category are being stored in its entirety for each employee. (i) Update Anomaly: If the hourly rate in dollars has to be changed for a particular rate category, it has to be done in all the rows that the rate category appears in. If it has not been changed in just one row, this may lead to inconsistency problems. ERY CORP/ CRS’ DBN7/ 002 Version No. 1.0 Page 73@ Infosys Technologies Limited Relational Database Management Systern (iil) Insert Anomaly: It is not possible to insert information about a new rate category and the corresponding hourly rate in dollars unless there is an employee in that rate category. (iV) Delete Anomaly: If there is only one employee in a particular rate category, it is not possible to delete information about the employee without losing information about that rate category and the corresponding hourly rate in dollars, Solution: Normalize to 3NF (i) Remove this excess data into its own table. (ii) Look for transitive relationships or relationships where a non-key attribute is dependent on another non-key attribute. In the above table (Employee table), Hourly_Rate_in_Dollars is actually dependent on Rate_Category according to the functional dependency Rate_Category > Hourly Rate_in_Dollars The above table (Employee) should be decomposed as follows: Employee table Emp_No Emp_Name Rate_Category foi Vincent R A 102 Pauline J B 103 Charles C Cc 107 David R B Rate Table Rate_Category A B c ERY CORP/ CRS’ DBN7/ 002 Version No. 1.0 Page 74