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

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
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

. 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
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 3-25: A BTree index. Figure 3-26: A Bitmap index. Name Chris Laura Tina Lance Christina Craig Sex is M 1 0 0 1 0 1 Sex is F Bitmap 0 1 1 0 1 0 M for Male is set to 1 F for Female is set to 1 Ca Jo T A B Be Ca E Je Jo T Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Leaf Block Root node Branch nodes Indexed values + a pointer to each table row Values to left of Ca Values to right of Ca 67 Database Modeling Building Blocks
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

Because primary keys must be unique, a relational database should automatically create internal unique indexes on primary keys. Commands similar to the following commands could be used to create indexes on foreign key fields, for the CREATE TABLE command on the PUBLICATION table shown previously in this chapter: CREATE INDEX XFK_P_Author ON Publication(author_id); CREATE INDEX XFK_P_Publisher ON Publication(subject_id); Types of Indexes It is important to have a brief understanding of different types of indexing available in relational databases. Some of the smaller-scale database engines (such as dBase, Paradox, and MS Access) might offer little or no variation on index types allowed, generally using BTree type indexing. Types of indexes in various relational database engines are as follows: . BTree index BTree means binary tree and, if drawn out on a piece of paper, a BTree index looks like an upside down tree. The tree is called binary because binary implies two options under each branch node: branch left and branch right. The binary counting system of numbers contains two digits, namely 0 and 1. The result is that a binary tree only ever has two options as leafs within each branch at least that is the theory, not being precisely the case in all databases. BTree indexes are sometimes improperly named as they are not actually binary meaning two branches can have more than two leafs contained within them. Naming conventions are largely immaterial in this situation. Essentially, a BTree consists of a root node, branch nodes, and ultimately leaf nodes containing the indexed field values in the ending (or leaf) nodes of the tree. Some BTree construction and searching methods in some databases are highly efficient for both reading and changing of data, automatically changing the structure of the BTree index without any overflow. Overflow is bad for indexing because changes are placed outside of the optimized index structure. Enough changes and overflow can destroy the efficiency of an index, eventually rendering it useless and drastically deteriorating rather than generally improving table access performance. Figure 3-25 shows an example of what a typical relational database BTree index might look like. . Bitmap index A Bitmap index contains binary representations for each record using 0 s and 1 s. Bitmap indexes are often misused and are extremely vulnerable to overflow over long periods of time. Values cannot be slotted into the existing Bitmap index structure as readily as can be done when updating a BTree index. Figure 3-26 shows a graphical type structure of the internal machinations of a Bitmap index where two bitmaps are created for two values of M for male and F for female. When M is encountered, the M bitmap is set to 1 and the F bitmap is set to 0. In general, Bitmap indexes can be disappointing, even in environments where they are supposedly highly beneficial. 66 Chapter 3
Check Tomcat Web Hosting services for best quality webspace to host your web application.

What Is an Index? An index is usually and preferably a copy of a very small section of table, such as a single field, and preferably a short length field. The act of creating an index physically copies one or more fields to be indexed into a separate area of disk other than that of the table. In some databases, indexes can be stored in a file completely separated from that of the table. Different databases are structured differently on a physical level. The important factor is the underlying physical separation. When a table is accessed, a process usually called an Optimizer decides whether to access the table alone, scanning all the records in the table, or if it is faster to read the much smaller index in conjunction with a very small section of the table. All relational databases have some type of SQL execution optimization process. It is usually called the Optimizer. An index essentially behaves like an index in the back of a book or the table of contents at the front of a book. When searching for details on a specific topic, it is much easier to find the term in the index or table of contents first, and then use a page reference number to find the information within the pages of the text. Reading the entire book every time you want to find a definition for a single term would be far too time-consuming to be useful, probably making the book completely useless as a reference. Most technical books are used as reference guides in one form or another. Following are some things to be avoided when indexing: . Creating too many indexes Too many indexes on a table can result in very slow database change responses. This is because every change to a table updates every index attached to it, as well as the table. The more indexes created for a table, the more physical changes are required. . Indexing too many fields Indexing too many fields not only makes the use of the indexes by queries more complex, but also makes the indexes too large physically. An index must be relatively much smaller than a table, and should be created on as few fields from that table as is possible. Alternate Indexing Alternate indexing really comes from the terms alternate index, secondary index, tertiary index, or just plain indexing. Specific use of terminology depends on the database in use. These terms all mean the same thing. Alternate indexes are an alternate to the primary relational structure organized by primary and foreign key indexes. Alternate indexes are alternate because they are in addition to primary and foreign key indexes and exist as alternate sorting methods to those provided by primary and foreign keys. By definition, the unique key indexes described in a previous section of this chapter are essentially alternate indexes, as well as being unique constraints. Foreign Key Indexing Relationships between tables such as that between the AUTHOR and PUBLICATION tables shown in Figure 3-21 can allow the foreign key in the child table not only to be duplicated (one-to-many) but also to be NULL valued in the child table (one-to-zero, one or many). In other words, in Figure 3-21, each author can have multiple publications or an author does not have to have any publications at all. Because foreign keys are allowed to be NULL valued and do not have to be unique, indexes must be created on those foreign key fields manually. 65 Database Modeling Building Blocks
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

So, what is Referential Integrity? Referential Integrity ensures the integrity of relationships between primary and foreign key values in related tables. Most relational database engines use what are often called constraints. Primary and foreign keys are both constraints. Remember, a constraint is a piece of metadata defined for a table defining restrictions on values. A primary key constraint forces the primary key field to be unique. A primary key constraint is also forced to make checks against any foreign key constraints referenced back to that primary key constraint. Referencing (or referential) foreign key constraints can be in any table, including the same table as the primary key constrained field referenced by the foreign key (a self join). A foreign key constraint uses its reference to refer back to a referenced table, containing the primary key constraint, to ensure that the two values in the primary key field and foreign key field match. Simply put, primary and foreign keys automatically verify against each other. Primary and foreign key references are the connections establishing and enforcing Referential Integrity between tables. There are some specific circumstances to consider in terms of how Referential Integrity is generally enforced: A primary key table is assumed to be a parent table and a foreign key table a child table. . When adding a new record to a child table, if a foreign key value is entered, it must exist in the related primary key field of the parent table. Foreign key fields can contain NULL values. Primary key field values can never contain NULL values as they are required to be unique. . When changing a record in a parent table if the primary key is changed, the change must be cascaded to all foreign key valued records in any related child tables. Otherwise, the change to the parent table must be prohibited. The term cascade implies that changes to data in parent tables are propagated to all child tables containing foreign key field copies of a primary key from a parent table. . When changing a record in a child table, a change to a foreign key requires that a related primary key must be checked for existence, or changed first. If a foreign key is changed to NULL, no primary key is required. If the foreign key is changed to a non-NULL value, the foreign key value must exist as a primary key value in the related parent table. . When deleting a parent table record then related foreign key records in child tables must either be cascade deleted or deleted from child tables first. Understanding Indexes Indexes are not really part and parcel of the relational database model itself; however, indexes are so important to performance and overall database usability that they simply have to be introduced without going into the nitty-gritty of how each different type of index functions internally. It is important to understand the fundamentals of indexes and their different types and attributes to get a basic understanding as to why exactly indexing is so important for relational databases in general. 64 Chapter 3
Check Tomcat Web Hosting services for best quality webspace to host your web application.

Figure 3-24: Band names, tracks, and silly descriptions represented as an ERD. Understanding Referential Integrity Referential Integrity functions just as its name states: It ensures the integrity of referential relationships between tables as defined by primary and foreign keys. In a relation between two tables, one table has a primary key and the other a foreign key. The primary key uniquely identifies each record in the first table. In other words, there can be only one record in the first table with the same primary key value. The foreign key is placed into the second table in the relationship such that the foreign key contains a copy of the primary key value from the record in the related table. Band band_name Track band_name (FK) track_name description Option 1 Band band_name Track band_name (FK) description track_name Option 2 Band band_id band_name Track band_id (FK) track_name description track_id Option 3 Primary key is a composite - inefficient Primary keys are names large key values - inefficient Surrogate keys used - efficient 63 Database Modeling Building Blocks
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

Figure 3-23: Band names, tracks, and silly descriptions. How It Works You are asked for two tables from three fields. One table has one field and the other table has two fields. The three fields are conveniently arranged. Look for a one-to-many relationship by finding duplicated values. The data is inconveniently and deliberately unsorted. 1. The first column contains the names of numerous different bands (musical groups) and the second column a track or song name. Typically, different bands or musical groups create many tracks. A one-to-many relationship exists between the band names and track names. 2. Band names in the first column are duplicated. Track names and descriptions are not. This supports the solution already derived in step 1. 3. Band names are the only duplicated values, so they make up the table on the parent side of the one-to-many relationship. The other two columns make up the table on the child side of the relationship. 4. The track name must identify the track uniquely. The description is just silly. Figure 3-24 shows three viable solutions with Option 3 being the better of all of the three options because surrogate keys are used for the primary and foreign keys. Option 2 is better than Option 1 because in Option 2 the one-to-many relationship is a non-identifying relationship, where the primary key on the TRACK table is not composite key. Nirvana Nirvana Nirvana Nirvana Stone Temple Pilots Greetings From Limbo Greetings From Limbo Pearl Jam Pearl Jam Pearl Jam Foo Fighters Greetings From Limbo Red Hot Chili Peppers Red Hot Chili Peppers Red Hot Chili Peppers Red Hot Chili Peppers Soundgarden Red Hot Chili Peppers Red Hot Chili Peppers Come As You Are About A Girl The Man Who Sold The World Polly The Right Line Greetings From Limbo Fatal Immortality Around The Bend Ashes My Friends Suck My Kiss University Speaking Under The Bridge Otherside Californication Bass reverb Lots of lovely bass Sell out! Who s that? Country groove The Wizard of Oz Deadly Just imagine Nuts! Heavy Hmmm No thanks OK Where s that confounded bridge? Hmmm again Hot and dry BAND NAME TRACK DESCRIPTION 62 Chapter 3
Check Tomcat Web Hosting services for best quality webspace to host your web application.

Figure 3-22: A foreign key is used to link back to the primary key of a parent table. There will be more explanation of the how and why of primary and foreign keys Chapter 4. At this point, simply remember that a primary key uniquely identifies each record in a table. A foreign key is a copy of the primary key copied from a parent table, establishing a relationship between parent and child tables. A unique key simply ensures the uniqueness of a value within a table. Try It Out Creating Some Simple Tables Figure 3-23 shows some data. Do the following exercise: 1. Create two related tables linked by a one-to-many relationship. 2. Assign a primary key field in each table. 3. Assign a foreign key field in one table. 1 2 3 4 5 6 7 Orson Scott Card James Blish Isaac Azimov Larry Niven Jerry Pournelle William Shakespeare Kurt Vonnegut AUTHOR_ID NAME 1 2 3 4 5 6 7 AUTHOR_ID 1 2 3 4 5 6 7 Cities in Flight A case of Conscience Foundation Second Foundation Foundation and Empire Foundation s Edge Prelude to Foundation 4 7 Lucifer s Hammer Footfall Ringworld The Complete Works of Shakespeare PUBLICATION_ID NAME 11 12 Jerry Pournelle Jerry Pournelle COAUTHOR 5 5 COAUTHOR_ID PUBLICATION_ID Footfall Lucifer s Hammer TITLE 4 4 9 10 9 10 Author author_id name Publication publication_id subject_id (FK) author_id (FK) title CoAuthor coauthor_id (FK) publication_id (FK) 61 Database Modeling Building Blocks
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

The AUTHOR table could be created with a simple script such as the following: CREATE TABLE Author ( author_id INTEGER NOT NULL, name VARCHAR(32) NULL, CONSTRAINT XPK_Author PRIMARY KEY (author_id), CONSTRAINT XUK_A_Name UNIQUE (name) ); In this script, the primary key is set to the AUTHOR_ID field and the name of the author is set to be unique to ensure that the same author is not added twice, or that two authors do not use the same pseudonym. Foreign Keys Foreign keys are the copies of primary keys created into child tables to form the opposite side of the link in an inter-table relationship establishing a relational database relation. Aforeign key defines the reference for each record in the child table, referencing back to the primary key in the parent table. Figure 3-22 shows that the PUBLICATION table has a foreign key called AUTHOR_ID (FK). This means that each record in the PUBLICATION table has a copy of the parent table s AUTHOR_ID field value, the AUTHOR table primary key value, in the AUTHOR_ID foreign key field on the PUBLICATION table. In other words, an author can have many books published and available for sale at once. Similarly, in Figure 3-22, the COAUTHOR table has a primary key made up of two fields, which also happens to comprise the combination or composite of a two foreign key relationship back to both the AUTHOR table and the PUBLICATION table. The PUBLICATION table could be created with a simple script such as the following: CREATE TABLE Publication ( publication_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, author_id INTEGER NOT NULL, title VARCHAR(64) NULL, CONSTRAINT XPK_Publication PRIMARY KEY (publication_id), CONSTRAINT FK_P_Subject FOREIGN KEY (subject_id) REFERENCES Subject, CONSTRAINT FK_P_Author FOREIGN KEY (author_id) REFERENCES Author, CONSTRAINT XUK_P_Title UNIQUE (title) ); In this script, the primary key is set to the PUBLICATION_ID field. The fields SUBJECT_ID and AUTHOR_ID are set as two foreign key reference fields to the SUBJECT and AUTHOR tables, respectively. A unique key constraint is applied to the title of the publication, ensuring copyright compliance. 60 Chapter 3
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.