MySQL, Java, JSP, Servlet, SSH, Tomcat Web Hosting Blog

Figure 4-42 shows the 5NF transformation from single three-field composite primary key table to three semi-related tables, each containing two-field composite primary keys. Why is the term semi-related used? Because the tool used in this book to draw ERDs does not actually allow creation of the ERD shown on the right side of the diagram in Figure 4-42. This is very interesting; however, all texts indicate that the 5NF transformation shown in Figure 4-42 as being true and correct. I still find this interesting, though, and harp back to my previous comments on anything beyond 3NF being commercially impractical and possibly overzealous in the application of design features. Figure 4-42: A 5NF transformation. Figure 4-43 shows the actual data structures that reflect 5NF structure shown at the lower-right of the diagram shown in Figure 4-42. 5th NF Transform 5th NF Transform Employee project employee manager Project_Employee project employee Project_Manager project employee Manager_Employee manager employee 118 Chapter 4
If you are in need for cheap and reliable webhost to host your website, we recommend http web server services.

5NF is also known as Projection Normal Form (PJNF). The term projection is used to describe new tables containing subsets of data from the original table. A cyclic dependency is a form of circular dependency where three pairs result as a combination of a single three-field composite primary key table, those three pairs being field 1 with field 2, field 2 with field 3, and field 1 with field 3. The cyclic dependency is that everything is related to everything else, including itself. In other words, there is a combination or a permutation excluding repetitions. If tables are joined again using a three-table join, the resulting records will be the same as that present in the original table. It is a stated requirement of the validity of 5NF that the post-transformation join must match records for a query on the pre-transformation table. 5NF is similar to 4NF in that both attempt to minimize the number of fields in composite keys. 5NF can be demonstrated as follows. You begin by creating a three-field composite primary key table: CREATE TABLE Employees ( project VARCHAR2(32) NOT NULL, employee VARCHAR2(32) NOT NULL, manager VARCHAR2(32) NOT NULL, PRIMARY KEY (project,employee,manager) ); Note the composite primary key on all three fields present in the table. Now, add some records to the table (shown also in the graphic in Figure 4-41): INSERT INTO Employees VALUES( Analysis , Brad , Joe ); INSERT INTO Employees VALUES( Analysis , Riffraff , Jim ); INSERT INTO Employees VALUES( Analysis , Magenta , Jim ); INSERT INTO Employees VALUES( DW , Janet , Larry ); INSERT INTO Employees VALUES( DW , Brad , Larry ); INSERT INTO Employees VALUES( DW , Columbia , Gemima ); INSERT INTO Employees VALUES( HTML , Columbia , Jackson ); INSERT INTO Employees VALUES( HTML , Riffraff , Jackson ); COMMIT; Figure 4-41: A pre-5NF three field composite primary key table. PROJECTALL Analysis Analysis Analysis DW DW DW HTML HTML EMPLOYEE Brad Riffraff Magenta Janet Brad Columbia Columbia Riffraff MANAGER Joe Jim Jim Larry Larry Gemima Jackson Jackson 117 Understanding Normalization
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

Figure 4-40: Data for the classic example 4NF as shown in Figure 4-36. Essentially, 4NF attempts to generate sets or arrays spread into separate records in separate tables, making each individual record perhaps easier to access when doing exact match searching. 5th Normal Form (5NF) 5NF does the following. . A table must be in 4NF. . Cyclic dependencies must be eliminated. A cyclic dependency is simply something that depends on one thing, where that one thing is either directly in indirectly dependent on itself. Employee employee skill Employee_Skill employee (FK) skill Employee_Certification employee (FK) certification NAME Brad Brad Janet RiffRaff RiffRaff RiffRaff Magenta Magenta Columbia Columbia Columbia Columbia SKILLS Programmer Sales Sales HTML Programmer Writing Analyst DBA DBA Analyst Programmer HTML NAME Brad Riffraff Riffraff Magenta Magenta Columbia Columbia Columbia CERTIFICATION MSCE MSCE BSc BSc OCP BSc OCP MSCE NAME Brad Columbia Janet Magenta Riffraff 116 Chapter 4
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

Figure 4-39 shows a more simplistic example where every FATHER table entry is unique because fathers can have multiple children, but each child has only one father. Figure 4-39: A 4NF example using a one-to-many relationship. Figure 4-40 resolves the many-to-many relationships into EMPLOYEE_SKILL and EMPLOYEE_CERTIFICATION tables, shown as many-to-many relationships in Figure 4-38. The many-to-many relationships resolved into one-to-many relationships in Figure 4-40 contain composites of employee names, original skills and certifications arrays, with arrays spread into separate records. 4th NF doesn t only apply to many-to-many but also one-to-many such as father->child1,child2,child3 4th NF Transform 4th NF Transform Father father Child father (FK) child FATHER Joe Jim Jake CHILDREN Jane, Janet Jenny, Janice, Judy July FATHER Joe Joe Jim Jim Jim Jake CHILD Jane Janet Jenny Janice Judy July 115 Understanding Normalization
If you are in need for cheap and reliable webhost to host your website, we recommend http web server services.

Figure 4-38 performs a partial 4NF transformation by splitting skills and certifications from the EMPLOYEE table into SKILL and CERTIFICATION tables. This is only a partial transformation, however, because there are still many-to-many relationships between EMPLOYEE to SKILL tables, and EMPLOYEE to CERTIFICATION tables. Figure 4-38: A classic example 4NF using many-to-many relationships. Partial 4th NF Transform Partial 4th NF Transform Employee employee skills certifications NAME Brad Janet Riffraff Magenta Columbia SKILLS Programmer, Sales Sales HTML, Programmer, Writing Analyst, DBA DBA, Analyst, Programmer, HTML CERTIFICATIONS MSCE MSCE, BSc BSc, OCP BSc, OCP, MSCE Produce many-tomany relationships Employee is a multivalued fact about a skill Many-to-many Skill is a multi-valued fact about an employee Skill Skill Certification certification Employee employee 4th NF requires resolution of these many-to-many relationships 114 Chapter 4
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

Figure 4-37 shows a non-4NF transformation essentially only spreading the comma-delimited lists into separate records, using the same existing EMPLOYEE table. Figure 4-37: An inelegant solution avoiding 4NF. NOT a 4th NF Transform NOT a 4th NF Transform Employee employee skills certifications NAME Brad Janet Riffraff Magenta Columbia SKILLS Programmer, Sales Sales HTML, Programmer, Writing Analyst, DBA DBA, Analyst, Programmer, HTML CERTIFICATIONS MSCE MSCE, BSc BSc, OCP BSc, OCP, MSCE NAME Brad Brad Janet Riffraff Riffraff Riffraff Magenta Magenta Columbia Columbia Columbia Columbia SKILLS Programer Sales Sales HTML Programmer Writing Analyst DBA DBA Analyst Programmer HTML CERTIFICATIONS MSCE MSCE BSc BSc OCP BSc OCP MSCE Spreading into multiple rows is a non-4th NF solution 113 Understanding Normalization
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

A multiple valued set is a field containing a comma-delimited list or collections of some kind. A collection could be an array of values of the same type. Those multiple values are dependent as a whole on the primary key (the whole meaning the entire collection in each record). 4NF is similar to 5NF in that both attempt to minimize the number of fields in composite keys. Figure 4-36 shows employees with variable numbers of both skills and certifications where those skills and certifications are not only unrelated to each other, but are stored as comma-delimited list arrays in single fields (take a breath) in the EMPLOYEE table. Figure 4-36: Multi-valued lists not in 4NF. Employee employee skills certifications NAME Brad Janet Riffraff Magenta Columbia SKILLS Programmer, Sales Sales HTML, Programmer, Writing Analyst, DBA DBA, Analyst, Programmer, HTML CERTIFICATIONS MSCE MSCE, BSc BSc, OCP BSc, OCP, MSCE Employee is a multivalued fact about a skill Many-to-many Skill is a multi-valued fact about an employee 112 Chapter 4
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

Figure 4-35: Data for the common type of BCNF transformation example shown in Figure 4-34. 4th Normal Form (4NF) 4NF does the following. . A table must be in 3NF or BCNF with 3NF. . Multi-valued dependencies must be transformed into functional dependencies. This implies that one value and not multiple values are dependent on a primary key. . Eliminate multiple sets of multiple valued or multi-valued dependencies, sometimes described as non-trivial multi-valued dependencies. Project Employee Manager PROJECTALL Analysis Analysis DW DW HTML MANAGER Joe Jim Larry Gemima Jackson EMPLOYEE Brad Riffraff Magenta Janet Brad Columbia Columbia Riffraff MANAGER Joe Jim Jim Larry Larry Gemima Jackson Jackson PROJECTALL Analysis Analysis Analysis DW DW DW HTML HTML EMPLOYEE Brad Riffraff Magenta Janet Brad Columbia Columbia Riffraff BCNF Transform BCNF Transform PROJECTALL Analysis Analysis Analysis DW DW DW HTML HTML EMPLOYEE Brad Riffraff Magenta Janet Brad Columbia Columbia Riffraff MANAGER Joe Jim Jim Larry Larry Gemima Jackson Jackson 111 Understanding Normalization
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Figure 4-34: A common type of BCNF transformation example. Data for Figure 4-34 is shown in Figure 4-35 to attempt to make this picture clearer. The point about BCNF is that a candidate key is, by definition, a unique key, and thus a potential primary key. If a table contains more than one candidate key (primary key), it has a problem according to BCNF. I disagree! BCNF divides a table up into multiple tables to ensure that no single table has more than one potential primary key. This is my understanding of BCNF. In my opinion, BCNF is overzealous for a commercial environment. Essentially, BCNF prohibits a table from having two possible primary keys. Why? And so what! This is not a hard-and-fast rule commercially, but more of a purist requirement from a mathematical perspective. In other words, it s nice, but it s not cool commercially. Manager manager Projects project manager employee Project project manager (FK) Employee project manager (FK) Manager manager Project project manager (FK) Project_Employee project (FK) manager (FK) employee (FK) Employee project manager (FK) ERWin does not allow MANAGER and PROJECT columns The many-to-many relationship above could be resolved BCNF Transform BCNF Transform 110 Chapter 4
We recommend high quality webhost to host and run your jsp application: christian web host services.

Figure 4-33: Using BCNF to separate all candidate keys into separate tables. Unique key tables created on the right side of the diagram in Figure 4-33 all show quite conceivable possibilities of separating tables into individual units, accessible by the single primary key field CUSTOMER_ID, simply because the separated fields are unique. Dividing tables up like that shown in Figure 4-33 can result in some serious inefficiency as a result of too many tables in SQL code join queries. Is BCNF folly in the case of Figure 4-33? That s an indefatigable yes! The usual type of example used to describe BCNF and other beyond 3NF transformations is often similar to the example shown in Figure 4-34, which is much more complicated than that shown in Figure 4-33; however, the usefulness of the transformation shown in Figure 4-33 is dubious to say the least. Customer customer_id balance_outstanding last_activity_date days_credit Customer customer_id customer_name address phone fax email exchange ticker balance_outstanding last_activity_date days_credit Customer_Stock_Ticker customer_id (FK) exchange ticker Customer_Name customer_id (FK) customer_name Customer_Email customer_id (FK) email Customer_Address customer_id (FK) address Customer_Fax customer_id (FK) fax Customer_Phone customer_id (FK) phone BCNF Transform BCNF Transform Names of customers have to be unique Email addresses have to be unique on a global scale Ticker symbols are unique to exchanges Global postal services cannot cater for duplicated address. Who would they send the Christmas cards to? Phone and fax numbers are unique when country and area codes are included 109 Understanding Normalization
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.