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

Figure 4-4: A table with five and possibly more candidate keys. . Full functional dependence This situation occurs where X determines Y, but X combined with Z does not determine Y. In other words, Y depends on X and X alone. If Y depends on X with anything else, there is not full functional dependence. Essentially X, the determinant, cannot be a composite key. A composite key contains more than one field (the equivalent of X with Z). Figure 4-5 shows that POPULATION is dependent on COUNTRY but not on the combination of RATE and COUNTRY. Therefore, there is full functional dependency between POPULATION and COUNTRY because RATE is irrelevant to POPULATION. Conversely, there is not full functional dependence between POPULATION and the combination of COUNTRY and RATE. Customer customer_id customer currency_code currency exchange_rate address Customer currency_code customer customer_id currency exchange_rate address Customer currency customer_id address customer currency_code exchange_rate Customer address customer_id customer currency_code currency exchange_rate Customer customer customer_id currency_code currency exchange_rate address Customer customer_id customer currency_code currency exchange_rate address 78 Chapter 4
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision shared web hosting services

Figure 4-3: Functional dependency and the determinant. A determinant is the inversion or opposite of functional dependency. . Transitive dependence Z is transitively dependent on X when X determines Y and Y determines Z. Transitive dependence thus describes that Z is indirectly dependent on X through its relationship with Y. In Figure 4-3, the foreign exchange rates in the RATE field (against the US Dollar) are dependent on CURRENCY. The currency in turn is dependent on COUNTRY. Thus, the rate is dependent on the currency, which is in turn dependent on the country; therefore, RATE is transitively dependent on COUNTRY. . Candidate key A candidate key (potential or permissible key) is a field or combination of fields that can act as a primary key field for a table thus uniquely identifying each record in the table. Figure 4-4 shows five different variations of one table, all of which have valid primary keys, both of one field and more than one field. The number of options displayed in Figure 4-4 is a little ridiculous, but demonstrates the concept. FXCODE ALL BGN CYP CZK DKK DM HUF ISK MTL NOK PLN ROL SEK CHE GBP CURRENCY Leke Leva Pounds Koruny Krener Deutsche Marks Forint Kronur Liri Krone Zlotych Lei Kronor Francs Pounds COUNTRY Albania Public Denmark Germany Hungary Iceland Malta Norway Poland Romania RATE 5.8157 1.5 6.5412 7.000 10.00 0.538 RATE DM determines that the currency is Deutsche Marks Pounds is dependant on the code being GBP 77 Understanding Normalization
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision ftp web hosting services

Figure 4-2: DELETE anomaly occurs when detail records removed without deleting master record first. . Update anomaly This anomaly is similar to deletion in that both master and detail records must be updated to avoid orphaned detail records. When cascading, ensure that any primary key updates are propagated to related child table foreign keys. Dependency, Determinants, and Other Jargon The following are some simple mathematical terms you should understand. . Functional dependency Y is functionally dependent on X if the value of Y is determined by X. In other words, if Y = X +1, the value of X will determine the resultant value of Y. Thus, Y is dependent on X as a function of the value of X. Figure 4-3 demonstrates functional dependency by showing that the currency being Pounds depends on the FXCODE value being GBP. . Determinant The determinant in the description of functional dependency in the previous point is X because X determines the value Y, at least partially because 1 is added to X as well. In Figure 4-3 the determinant of the currency being Deutsche Marks is that the value of FXCODE be DM. The determinant is thus FXCODE. Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov James Blish James Blish Larry Niven Larry Niven Larry Niven Foundation Foundation Foundation Foundation Foundation Foundation Foundation Foundation and Empire Foundation s Edge Prelude to Foundation Second Foundation A Case of Conscience Cities in Flight Footfall Lucifer s Hammer Ringworld 893402095 345308999 345336275 5557076654 246118318 345334787 5553673224 553293370 553293389 553298398 553293362 345438353 1585670081 345323440 449208133 345333926 435 285 234 320 480 480 304 256 590 608 640 352 AUTHOR TITLE ISBN PAGES Isaac Azimov James Blish Larry Niven AUTHOR Master table Detail table Delete detail records first to avoid an anomaly 76 Chapter 4
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision j2ee hosting services

Figure 4-1: Insert anomaly occurs when detail record added with no master record. . Delete anomaly Caused when a record is deleted from a master table, without first deleting all sibling records, in a detail table. The exception is a cascade deletion, occurring when deletion of a master record automatically deletes all child records in all related detail tables, before deleting the parent record in the master table. For example, referring to Figure 4-2, deleting an author requires initial deletion of any books that an author might already have published. If an author was deleted and books were left in the database without corresponding parent authors, the BOOK table records would become known as orphaned records. The books become logically inaccessible within the bounds of the AUTHOR and BOOK table relationship. Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov James Blish James Blish Larry Niven Larry Niven Larry Niven J.K. Rowling J.K. Rowling Harry Potter INSERT Book Add the book once the author added Add new author first Foundation Foundation Foundation Foundation Foundation Foundation Foundation Foundation and Empire Foundation s Edge Prelude to Foundation Second Foundation A Case of Conscience Cities in Flight Footfall Lucifer s Hammer Ringworld 893402095 345308999 345336275 5557076654 246118318 345334787 5553673224 553293370 553293389 553298398 553293362 345438353 1585670081 345323440 449208133 345333926 435 285 234 320 480 480 304 256 590 608 640 352 AUTHOR TITLE ISBN PAGES Isaac Azimov James Blish Larry Niven AUTHOR INSERT Author Master table Detail table 75 Understanding Normalization
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check Vision professional web hosting services

. A purist, academic definition of normalization . 1st, 2nd, 3rd, Boyce-Codd, 4th, 5th, and Domain Key Normal Forms . Normalization and referential integrity as expressed by primary and foreign keys What Is Normalization? The academic definition of normalization is the accepted format of Normal Forms definition. I like to label normalization as academic because the precise definitions of Normal Forms are often misunderstood in a commercial environment. In fact, the truth is that language use in the exact definitions for Normal Forms is so very precise and carefully worded that problems are caused. Many database designers do not understand all facets of normalization in other words, how it all really works. Alot of this is a result of such precise use of language. After all, we are now in a global economy. There are a multitude of database architects who do not speak English, have a limited command of the English language, and should not be expected to be well-versed in either respect. In general, normalization removes duplication and minimizes redundant chunks of data. The result is better organization and more effective use of physical space, among other factors. Normalization is not always the best solution. For example, in data warehouses, there is a completely different approach. In short, normalization is not the be-all and end-all of relational database model design. This chapter also describes a brief user-friendly interpretation of Normal Forms. It is just as important to understand Normal Forms from a more academic, more precise but possibly less commercially viable perspective. The problem with the academic approach to normalization is that it seems to insist on always expecting a designer to apply every Normal Form layer in every situation. In my experience, in a commercial environment this is nearly always a mistake. The trouble with the deeper and more precisely refined aspects of normalization is that normalization tends to over-define itself for the sake of simply defining itself further. Before going into the details of normalization, some specifics should be covered briefly, including the concept of anomalies and some rather technical mathematical jargon. The Concept of Anomalies The intention of relational database theory is to eliminate anomalies from occurring in a database. Anomalies can potentially occur during changes to a database. An anomaly is a bad thing because data can become logically corrupted. An anomaly with respect to relational database design is essentially an erroneous change to data, more specifically to a single record. To put this into perspective, data warehouses can add and change millions of records in single transactions, making accounting for anomalies over zealous. In the interests of mathematical precision, explicit definition is required. Why? Mathematics is very precise and anomalies always should be accounted for. That is just the way it is. Consider the following: . Insert anomaly Caused when a record is added to a detail table, with no related record existing in a master table. In other words, adding a new book in Figure 4-1 requires that the author be added first, assuming, of course, that the author does not already exist. 74 Chapter 4
Note: If you are looking for cheap webhost to host and run your apache application check Vision jboss web hosting services

4 Understanding Normalization There are two rules in life: Rule #1: Don t sweat the small stuff. Rule #2: Everything is small stuff. (Finn Taylor) Life is as complicated as we make it normalization can be simplified. This chapter examines the detail of the normalization process. Normalization is the sequence of steps by which a relational database model is both created and improved upon. The sequence of steps involved in the normalization process is called Normal Forms. Essentially, Normal Forms applied during a process of normalization allow creation of a relational database model as a step-by-step progression. Previous chapters have examined history and applications, plus various other factors involved in database model design. Chapter 3 introduced all the parts and pieces involved in a relational database model. This chapter now uses the terminology covered in Chapter 3 and explains how to build a relational database model. Subsequent chapters examine more advanced details of relational database modeling such as denormalization and SQL, both of which depend on a good understanding of normalization. This chapter describes the precise steps involved in creation of relational database models. These steps are the 1st, 2nd, and 3rd Normal Forms, plus the rarely commercially implemented Boyce-Codd, 4th, 5th, and Domain Key Normal Forms. The Normal Forms steps are the progressive steps in the normalization process. In this chapter, you learn about the following: . Anomalies . Dependency and determinants . Normalization . A layman s method of understanding normalization
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision servlet hosting services

Part II Designing Relational Database Models In this Part: Chapter 4: Understanding Normalization Chapter 5: Reading and Writing Data with SQL Chapter 6: Advanced Relational Database Modeling Chapter 7: Understanding Data Warehouse Database Modeling Chapter 8: Building Fast-Performing Database Models
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision shared web hosting services

. Clusters Clusters are used in very few databases and have been somewhat superceded by materialized views. In the past, clusters were used to pre-create physical copies of entire field level sections of heavily accessed tables, especially in SQL joins. Unlike materialized views, clusters do not allow for automatic refresh and are normally manually maintained. . Sequences and auto counters An auto counter field is a special datatype, sometimes called a non-static internal function, allowing automated generation of sequential number values (thus the term sequence ). Typically, auto counters are used for primary key surrogate key generation on insertion of new records into a table. . Partitioning and parallel processing Some databases allow physical splitting of tables into separate partitions, including parallel processing on multiple partitions and individual operations on individual partitions. One particularly efficient aspect of partitioning is the capability when querying a table to read fewer than all the partitions making up a table, perhaps even a single partition. Summary In this chapter, you learned about: . Building tables containing fields, datatypes, and simple validation . The different types of relationships between tables . Representing relations in ERDs . Defining referential integrity relationships between tables using primary and foreign keys . The types and uses of indexes . The types and uses of specialized objects such as views, materialized views, and auto counters The next chapter discusses the very heart of the relational database model by examining the process of normalization through the application of normal forms. Exercises 1. Write two CREATE TABLE commands for the tables in Option 3 of Figure 3-24. Make sure that all primary key, foreign key, and any potentially necessary unique keys are included. 2. Write CREATE INDEX commands to create all indexes on any foreign keys indicated in the CREATE TABLE command written for the previous question. 70 Chapter 3
Note: In case you are looking for affordable webhost to host and run your web application check Vision http web server services

call hot blocking. The result is the same gridlock! Reverse keys make the index values not sequential in terms of where they are physically written to disk. The result is no locking, no hot blocking, no gridlock, and, thus, much better performance. Other than tables, keys, and indexes, there are other types of objects. These other object types are more easily defined as data management objects and only loosely definable as data modeling objects. Management of data is the administration process occurring on a production system, long after completion of the data modeling process. Introducing Views and Other Specialized Objects So far in this chapter, topics covered have included tables, relationships between tables, and indexes attached to tables. You should understand the basic structure of a table, and that the relationships between tables are determined by primary keys in parent tables linked to foreign keys in child tables. Foreign keys are copies of primary key field values from parent tables. Indexing is important to understand not directly from a modeling perspective, but that indexes are used to superimpose a different order on top of the order created by the very structure of the relationships between tables, imposed by primary and foreign keys. Other than all these wonderful indexing things, there are further possibilities within relational databases that some database engines allow and some do not. It is important to know that specialized objects exist as options for expansion to a relational database model, as extensions to both the underlying physical structure of a database and the overlying logical structure (the tables and indexes). Following are a few examples: . Views A view is essentially a query definition and does not contain any data. A view is not a physical copy of data and does not contain any data itself. A view is merely a logical overlay of existing tables. Every execution against a view executes the query contained within the view against all underlying tables. The danger with using views is filtering a query against a view, expecting to read a very small portion of a very large table. Any filtering should be done within the view because any filtering against the view itself is applied after the query in the view has completed execution. Views are typically useful for speeding up the development process but in the long run can completely kill database performance. . Materialized views Materialized views are available in some very large capacity type relational databases. Amaterialized view materializes underlying physical data by making a physical copy of data from tables. So, unlike a view as described previously, when a query is executed against a materialized view, the materialized view is physically accessed rather than the underlying tables. The objective is to free the underlying tables for other uses, effectively creating two separate physical copies. Materialized views are often used to aggregate large data sets down to smaller sized data sets, in data warehouses and data marts. The biggest potential problem with materialized views is how often they are refreshed and brought up to date with any changes to their underlying tables. Another attribute of materialized views is the ability of some database engines to allow a query directed at an underlying table to be automatically redirected to a physically much smaller materialized view, sometimes called automated query rewrite. Queries can be automatically rewritten by the query Optimizer if the query rewrite can help to increase query performance. 69 Database Modeling Building Blocks
Note: In case you are looking for affordable webhost to host and run your web application check Vision cheap hosting services

. ISAM index Indexed Sequential Access Method (ISAM) uses a simple structure with a list of record numbers. ISAM indexes are used in various database engines. ISAM indexes are best used for static data as their internal list structure prohibits easy changes, making them extremely vulnerable to index overflow. . Hash table A hash table is a copy of data but rearranged into a different and more efficiently accessed order depending on a hashing algorithm. For example, a hashing algorithm takes a string and creates a number from that string. The number created by a specific string is always the same and is thus placed in a position in an index, sorted based on the hash-calculated value. Hash indexes can be highly efficient for read access, but are best avoided when subjected to any kind of data changes. Hash tables are likely to overflow worse than Bitmap indexes because there is absolutely no scope whatsoever for changes. The only way to push record changes from table to index is by regenerating the entire hash table index. . Index Organized Table An Index Organized Table (IOT) builds a table in the sorted order of an index, typically using a BTree index. IOTs can actually work fairly well in many types of databases, but you must remember that index record length is much longer than normal because index leaf blocks contain all fields in the entire record length of a table. Also, if the IOT is not read in indexed order, obviously all records in the table are read, and thus the index is ignored. Because the table is built in the structure of an index, however, not reading the table in IOT indexed order could be seriously problematic for performance. Different Ways to Build Indexes Indexes can usually be built in various different ways to accommodate however they might be used. Once again, some relational databases allow all of these options, some allow some, and some allow none. . Ascending or descending index An index can be built sorted in a normally ascending order (such as A, B, C) or in descending order (such as C, B, A). . Unique index Indexes values must be unique (can t contain duplicate values). . Non-unique index Non-unique indexes contain duplicated or repeated values in the index. It is normal to create both unique indexes and non-unique indexes. . Composite index Indexes can be built on more than a single field and are known as composite field indexes, multiple field indexes, or just plain old composite indexes. The most efficient type of index is a single field index containing an integer. . Compressed indexes Some databases allow compression of composite indexes where repeated prefix values are effectively indexed within the index, removing duplications within prefixed indexed fields. In other words, a composite index containing three fields can be accessed using a single value of the first field. . Reverse key indexes This is a really weird and unusual one. Only a very select few databases allow building of indexes such that indexed field values are stored as reverse strings. When adding gazillions of records at once to the same index in a very busy database, adding sequential index values (not reversed) adds many records all at once to the same physical space in the index. The result is what some relational databases call locking and other relational databases 68 Chapter 3
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision servlet hosting services