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

The demands of the modern global economy and the Internet dictate that end user operational applications are required to be active 24/7, 365 days a year. There is no window for any type of batch activity because when people are asleep in Europe, others are awake down under in Australia. The global economy requires instant and acceptable servicing of the needs of a global user population. In reality, the most significant difference between OLTP databases and data warehouses extends all the way down to the hardware layer. OLTP databases need highly efficient sharing of critical resources such as onboard memory (RAM), and have very small I/O requirements. Data warehouses are completely opposite. Data warehouses can consume large portions of RAM by transferring between disk and memory, in detriment to an OLTP database running on the same machine. Where OLTP databases need resource sharing, data warehouses need to hog those resources for extended periods of time. So, a data warehouse hogs machine resources. An OLTP database attempts to share those same resources. It is likely to have unacceptable response times because of a lack of basic I/O resources for both database types. The result, therefore, is a requirement for a complete separation between operational (OLTP) and decision-support (data warehouse) activity. This is why data warehouses exist! The Relational Database Model and Data Warehouses The traditional OLTP (transactional) type of relational database model does not cater for data warehouse requirements. The relational database model is too granular. Granular implies too many little pieces. Processing through all those little-bitty pieces is too time consuming for large transactions, joining all those pieces together. Similar to the object database model, the relational database model removes duplication and creates granularity. This type of database model is efficient for front-end application performance, involving small amounts of data that are accessed frequently and concurrently by many users at once. This is what an OLTP database does. Data warehouses, on the other hand, need throughput of huge amounts of data by relatively very few users. Data warehouses process large quantities of data at once, mainly for reporting and analytical processing. Also, data warehouses are regularly updated, but usually in large batch operations. OLTP databases need lightning-quick response to many individual users. Data warehouses perform enormous amounts of I/O activity over copious quantities of data; therefore, the needs of OLTP and data warehouse databases are completely contrary to each other, down to the lowest layer of hardware resource usage. Hardware resource usage is the most critical consideration. Software rests quite squarely on the shoulders of your hardware. Proper use of memory (RAM), disk storage, and CPU time to manage everything is the critical layer for all activity. OLTP and data warehouse database differences extend all the way down to this most critical of layers. OLTP databases require intensely sharable hardware structures (commonly known as concurrency), needing highly efficient use of memory and processor time allocations. Data warehouses need huge amounts of disk space, processing power as well, but all dedicated to long-running programs (commonly known as batch operations or throughput). A data warehouse database simply cannot cope using a standard OLTP database relational database model. Something else is needed for a data warehouse. 173 Understanding Data Warehouse Database Modeling
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

In this chapter, you learn about the following: . The origin of data warehouses . How data warehouses require a specialized database model . Star and snowflake schemas . Facts and dimensions . The fact-dimensional database model . How to create a data warehouse database model . The contents of a data warehouse database model The Origin of Data Warehouses Data warehouses were originally devised because existing databases were being subjected to conflicting requirements. Conflict arose between operational use and decision-support requirements as follows: . Operational use Operational use of a database requires a precise, accurate, and instant picture of data in a database. This includes all day-to-day operations in the functional running of a business. When a customer comes through the door, asks for a specific part, for a specific automobile, for a specific year, the part is searched for in a database. After the customer makes the purchase, they are invoiced. When the customer pays, the transaction is processed through bank accounts, and otherwise. All of this is operational-type activity. Response is instantaneous (or as near to instantaneous as possible) and it is all company to customer-direct trading activity. The operations aspect of a company is the heart of its business. . Decision-support use Where operational use divides data based on business function, decisionsupport requires division of database data based on subject matter. Operational use requires access to specific items such as a specific part for a specific automobile, for a specific customer. Decision-support use requirements might be a summary of which parts were ordered on which dates, not necessarily by whom. A decision-support database presents reports, such as all parts in stock, plus all parts ordered, over the period of a whole year. The result could be a projection of when new parts should be ordered. The report allows company employees to cater for restocking of popular items. There is a big difference between requirements for operational and decision-support databases. Operational systems require instant responses on small amounts of information. Decision-support systems need access to large amounts of data (large portions of a database), allowing for good all-round estimates as to future prospects for a company. The invention of data warehouses was inevitable to reduce conflict between small transactional (OLTP databases) and large historical analytical reporting requirements (data warehouses). 172 Chapter 7
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

to many people and small chunks of data. Data warehouses perform enormous amounts of I/O activity, over millions (if not billions) of records. It is acceptable for data warehouse reports to take hours to run. . Data warehouses and the object model The object model is even more granular than the relational model, just in a different way, even if it does appear more realistic to the naked eye. Highly granular normalized relations (the relational model), or uniquely autonomous objects (the object model), can cause serious inefficiencies in a data warehouse. Data warehouses perform lots of big queries, with lots of data in many records in many tables. The fewer tables there are in a data warehouse, the better! Query joins on large sets of records can become completely unmanageable and even totally useless. The heart of the data warehouse database model, different to both the relational and object models, vaguely combines aspects of both relations and objects. A data warehouse database is effectively a highly denormalized structure, consisting of preferably only two distinct hierarchical layers. A central table contains highly denormalized transactional data. The second layer contains referential static data. This data warehouse database model is known as the dimensional model or the fact-dimensional model. A data warehouse consists of facts and dimensions. Facts and dimensions are types of tables. Each data warehouse structure consists of a single fact table, surrounded by multiple dimensions. It is possible to have more than a single fact table but essentially different fact tables are likely to be related to the same set of dimension tables. Therefore, different fact tables represent an entire new set of tables, or a new modular structure. That entire new set of tables is essentially another subset data warehouse, also known as a data mart. The term data mart is used to describe a self-contained, subsection of a data warehouse. Fact and dimension tables contain different types of data. Where dimensions contain static data, and facts contain transactional data. Transactional data is the record of a company s activities, such as invoices sent to its customers. The dimensions describe the facts, such as the customer s name and address. For example, an online retailer selling thousands of items per day could ship 20 items to each customer every year. Over the course of a number of years, each customer might be shipped hundreds of separate items. The detail of a customer (such as the address) is static information. Static information does not change very often. The customer is a dimension. The customer dimension describes the fact, or the transactions (the invoices or details of every item shipped over many years). The active database (OLTP database) would likely have all records of transactions deleted from its active files on a periodical basis (annually, for example). Annual historical data could be archived into a data warehouse. The data warehouse data can then be used for forecasting (making guesses as to what customers might purchase over the next 10 years). The result of all this mishmash of complicated activities and other wonderful stuff is a table structure looking similar to that shown in Figure 6-17. Figure 6-17 shows a pseudo-table structure, describing graphically what is known as a star schema (a single fact table surrounded by a group of dimensions). Data warehouse database models are ideally made up of data mart, subset star schemas. Effectively, different schemas are also known as data marts. Each data mart is a single fact table, all linked to shared dimension tables (not necessarily all the same dimensions, but it is possible). Each fact-dimensional structure is a star schema (a data mart). Each star schema is likely to contain data for a different department of a company, or a different region (however a company may decide to split its data). 168 Chapter 6
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

The object model and object methodologies are extreme. This section merely touches on it briefly, and is here only to give you a very short introduction to differences between relational and object modeling techniques (types and collections). If you really want to know more about the object model, there are a multitude of available texts. Introducing the Data Warehouse Database Model So far, this chapter has examined the topics of denormalization and the object model. The next logical step from these two subjects is the subject of data warehouses. A data warehouse vaguely combines some of the aspects of the relational model and the object model. The relational model breaks data into mathematically precise, detailed structures. These structures are often difficult for the uninitiated to decipher from a business operational perspective. The object model also breaks everything down, not for the purposes of granularity, but to reduce the complexity of the whole. The process of breaking things into their simplest parts is a conceptual thing. The result from the point of view of the uninitiated layman is startling, though. Where a relational database model appears cryptic and over-detailed, an object database model starts to look like a real-world picture of data, from the perspective of someone using databases and applications, as opposed to someone building the stuff. This perspective is apparent if you take another brief look at Figure 6-16. The names of structures in the object model (the left side of Figure 6-16) are much clearer than the names of structures in the equivalent relational model (the right side of Figure 6-16). The relational model in Figure 6-16 is abstract and cryptic. The object model, on the other hand, contains a structural break down of data, as an author, publisher, or a book retailer would understand books. The point is that a data warehouse database model combines some aspects of the relational database model in its application of normalization, and the more easily understandable visual aspects of the object database model. There is a big difference with data warehouses, though, in that they are most effective when severely denormalized. The reason why data warehouse metadata (table) structures look more real-world is essentially as a result of severe denormalization, and has nothing to do with the object database model. The comparison is interesting, though, because it also helps to explain one part of why application SDKs are now largely object-oriented in approach. Objects are easier to understand because they mimic the real world much more closely than the set hierarchical structures of the relational model of data. So, where does the data warehouse model really fit into the grand scheme of things? The truth is it doesn t. The data warehouse database model is essentially an animal unto itself, with very little relationship to either the relational or the object database models: . Data warehouses and the relational model The relational model is too granular. The relational model introduces granularity by removing duplication. The result is a database model nearly always highly effective for front-end application performance and OLTP databases. OLTP databases involve small amounts of data accessed frequently and concurrently by many users. On the other hand, data warehouses require throughput of huge amounts of data by a small user population. OLTP databases (the relational database model) need lightning-quick response 167 Advanced Relational Database Modeling
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

classes are specializations of the PUBLICATION class. The SCIFI, ACTION, and FANTASY classes are specializations of the FICTION class. . Inheritance is all about types and not collections. In computer jargon, the undoing of types is known as type casting where one type is made to contain the behavior of another type. . The previous point discussed types and how types are represented by the structure of an object model. Now let s briefly examine collections. The operational relationship between publications and editions is actually one publication containing a collection of many editions (of the same publication). Now, if you consider types, fiction and non-fiction are types of publications, and sci-fi (science fiction), action and fantasy are all specialized types of fiction publications. Editions multiple inherit from all publication specialized (inherited) types, including all the fiction types, plus the non-fiction type (non-fiction has no subtypes in this model). Effectively, there is no need for a relationship between publications and editions because it is inherent in the structure. Take note of the little circle on top of the edition class, indicating the many side of a collection. . The relationships between the different classes in the object model are represented in the class structure itself, those relationships being both collection inclusion and inheritance between classes. During run-time, objects instantiated from classes contain objects containing pointers to other contained objects. . An invisible difference is the power of self-contained (black-box) processing using methods in an object database. Relational database stored procedures can perform a similar function to methods, but always inadequately. Method processing can act on each class, within the scope of each class alone. Figure 6-16: Comparing object and relational database models. Publisher publisher_id name Author author_id name Review review_id publication_id (FK) review_date text Subject subject_id parent_id name Publication publication_id subject_id (FK) author_id (FK) title Edition ISBN publisher_id (FK) publication_id (FK) subject_id print_date pages list_price format CoAuthor coauthor_id (FK) publication_id (FK) Rank rank ingram_units ISBN (FK) Author Publication Non-Fiction Review Fiction Action Fantasy Edition Rank SciFi Publisher Collection Inheritance 166 Chapter 6
If you are in need for cheap and reliable webhost to host your website, we recommend http web server services.

Understanding the Object Model Many modern relational databases are, in fact, object-relational databases. An object-relational database is by definition a relational database allowing certain object characteristics. To get a good understanding of what an object-relational database is, you must have a basic understanding of the object model. The object database model is different from the relational model. An object database is more capable at handling complex issues. Following are the parts making up the object model: . Class A class is the equivalent of a table in a relational database. . Object An object is the iteration or copy of a class at run-time, such that multiple object instances can be created from a class. The computer jargon term for the creation of an object from a class is instantiation or to instantiate. . Attribute An attribute is the equivalent of a relational database field. . Method A method is equivalent to a relational database stored procedure, except that it executes on the data contents of an object, within the bounds of that object. In the relational database model, relationships are established using both table structures (metadata) and data values in fields, such as those between primary and foreign key values. On the contrary, in an object database, relationships are established solely through the structure of objects and the metadata relationships between objects, declared by the classes defining those objects. Class collections and inheritance define object database structure. Classes are defined as containing collections of pointers to other classes, as being inherited from other classes above in a hierarchy, or as being abstractions of other classes below in a hierarchy. Aclass can be specialized and abstracted. Aspecialized class is a specific form of a class, inheriting everything from its parent class, allowing local overriding changes and additions. An abstracted class is a generalized or generic form of a class, containing common aspects of inherited classes. Figure 6-16 shows an object database class structure on the left and a relational database entity structure on the right. Note a number of differences: . The object model has no types (SUBJECT). Subjects in the relational model are represented by both parent and child SUBJECT table records. The FICTION and NON-FICTION classes are representative of the SUBJECT.PARENT_ID fields. The SCIFI, ACTION, and FANTASY classes are genres, representing three different fiction type subjects. All of these new classes are specializations of the PUBLICATION class. More specifically, the FICTION and NON-FICTION It is important to reiterate a distinct difference between a class and an object. An object exists at run-time. A class is a metadata structure. Objects are created from classes at run-time. As a result, the structure of classes can often be different from the structure of objects created from a class hierarchy. Object database models are often designed incorrectly because the difference between a class and an object is misunderstood. In general, class structure never looks like an object structure. Class structure is an abstraction of object structure. Additionally, a class structure does not look anything like a relational database model table structure. If it does, you might be attempting to build a relational database structure into an object database model. 165 Advanced Relational Database Modeling
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

. Summary fields in parent tables This can help to avoid costly grouping joins, but real-time updates can cause serious problems with hot blocks. Examples are shown in Figure 6-15. Again, the objective is to minimize the size of subsequent SQL code joins, and to provide summary values without having to constantly add all the details, from multiple records in a detail table. A hot block is a very busy part of the database accessed much too often by many different sessions. Figure 6-15: Denormalization using summary fields in parent tables. . Separate heavily and lightly accessed fields Much like separating inactive and active data at the table level, tables containing fields with vastly different rates of access can be separated. This avoids continual physical scanning of rarely used data field values, especially when those values do not contain NULLs. This is one potentially sensible use of 4NF in terms of separation of tables into two tables, related by one-to-one relationships. Publisher publisher_id name Author author_id name total_publications Review review_id publication_id (FK) review_date text Subject subject_id parent_id name Publication publication_id subject_id (FK) author_id (FK) title average_price total_editions Edition ISBN publisher_id (FK) publication_id (FK) subject_id print_date pages list_price format rank ingram_units CoAuthor coauthor_id (FK) publication_id (FK) Average 164 Chapter 6
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

. Temporary tables Temporary tables can be used on a temporary basis, either for a connected session or for a period of time. Typically, temporary tables perform intermediary functions, helping to eliminate duplication or processing, and reducing repetitive I/O activities. Denormalization Tricks There are many tricks to denormalizing data, not reversals of the steps of normalization. These are some ideas to consider: . Separate active and inactive data Data can be separated into separate physical tables, namely active and inactive tables. This is a factor often missed where inactive (historical) data can occupy sometimes as much as thousands of times more space than active data. This can drastically decrease performance to the most frequently needed data, the active data. Separation of active and inactive data is the purpose of a data warehouse, the data warehouse being the inactive data. . Copy fields between tables Make copies of fields between tables not directly related to each other. This can help to avoid multiple table joins between two tables where other tables must be passed through to join the two desired tables. An example is shown in Figure 6-14 where the SUBJECT_ID field is duplicated into the EDITION table. The objective is to minimize the size of subsequent SQL code joins. Figure 6-14: Denormalization by copying fields between tables. Publisher publisher_id name Author author_id name Review review_id publication_id (FK) review_date text Subject subject_id parent_id name Publication publication_id subject_id (FK) author_id (FK) title Edition ISBN publisher_id (FK) publication_id (FK) subject_id print_date pages list_price format rank ingram_units CoAuthor coauthor_id (FK) publication_id (FK) Duplication 163 Advanced Relational Database Modeling
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

How It Works Figure 6-13 shows what the tables should look like in 2NF. Figure 6-13: Denormalized chart toppers. Denormalization Using Specialized Database Objects Many databases have specialized database objects for certain types of tasks. Some specialized objects allow for physical copies of data, copying data into a denormalized form. . Materialized views Materialized views are allowed in many larger relational databases. These objects are commonly used in data warehouses for pre-calculated aggregation queries. Queries can be automatically switched to direct access of materialized views. The result is less I/O activity by direct access to aggregated data stored in materialized views. Typically, aggregated materialized views contain far fewer records than underlying tables, reducing I/O activity and thus increasing performance. Views are not the same thing as materialized views. Views are overlays and not duplications of data and interfere with underlying source tables. Views often cause far more in the way of performance problems than application design issues they might ease. . Clusters These objects allow physical copies of heavily accessed fields and tables in join queries, allowing for faster access to data with more precise I/O. . Index-organized tables A table can be constructed, including both index and data fields in the same physical space. The table itself becomes both the index and the data because the table is constructed as a sorted index (usually as a BTree index), rather than just a heap or pile of unorganized bits and pieces. CD cd_id band_id (FK) title length tracks Track track_id cd_id (FK) track length rank region genre chart Band band_id name address phone email 162 Chapter 6
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Denormalizing 1NF Just don t do it! Data warehouse fact tables can be interpreted as being in 0th Normal Form, but the connections to dimensions are 2NF. So, denormalization of 1NF is not advisable. Try It Out Denormalize to 2NF Figure 6-12 shows a highly normalized table structure representing bands, their released CDs, tracks on the CDs, ranks of tracks, charts the tracks are listed on, plus the genres and regions of the country those charts are located in. 1. The RANK and TRACK tables are one-to-one related (TRACK to RANK: one-to-zero or one). This implies a BCNF or 4NF transformation, zero or one meaning a track does not have to be ranked. Thus, a track s rank can be NULL valued. Push the RANK column back into the TRACK table and remove the RANK table. 2. The three tables BAND_ADDRESS, BAND_PHONE, and BAND_EMAIL were created because of each prospective band attribute being a candidate primary key in itself. Reverse the BCNF transformation, pushing address, phone, and email details back into the BAND table. 3. The CHART, GENRE, and REGION tables are an absurd application of multiple layers of 2NF transformation, separating static information, from what is effectively parent static information. Chart, genre, and region details can all be pushed back into the TRACK table. Figure 6-12: Normalized chart toppers. Chart chart genre (FK) Region region Genre genre region (FK) Track track_id chart (FK) cd_id (FK) track length Band band_id name CD listing classification exchange Band_Address band_id (FK) address Band_Phone band_id (FK) phone Band_Email band_id (FK) email Rank track_id (FK) rank 161 Advanced Relational Database Modeling
If you are in need for cheap and reliable webhost to host your website, we recommend http web server services.