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

Figure 9-28: Adjusting the data warehouse database model ERD for an online auction house. Figure 9-24 described the analysis and design process of database modeling as being an iterative one. Steps can be repeated, in any order, and adjustments can be made during the entire process. It is better to make adjustments, particularly at the database modeling stage, during analysis and design. Make changes before any application code is written, preferably before any reworking with methodologies (such as normalization and denormalization) have been applied to database models. Seller Category Hierarchy Time Location Buyer Product Listing -Bids -History Seller seller popularity_rating join_date address return_policy international payment_methods Location region country state city Time month quarter year Buyer buyer popularity_rating join_date address Listing_Bids_History listing# listing_description listing_image listing_start_date listing_days listing_currency listing_starting_price listing_reserve_price listing_buy_now_price listing_number_of_bids listing_winning_price listing_winner_buyer luyer_bidder seller bidder_price bidder_date history_buyer history_buyer_comment_date history_buyer_comments history_seller history_seller_comment_date history_seller_comments Product product price Category_Hierarchy parent category 251 Planning and Preparation Through Analysis
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

Most of the fields in the tables shown in Figure 9-25 have already been discussed for the OLTP database model analysis. The only fields not covered are the additional locations, time stamp, and product content dimensions: . Locations Locations are a hierarchy of region, country, state, and city, as shown ion Figure 9-26. Figure 9-26: Some example locations records. . Times Times are month, quarter, and year, as shown in Figure 9-27. Time stamp dimensions can contain days, hours, and possibly even minutes; however, that level of detail can generate a very large time dimension, and is probably not worth it in general. There would be too many records to maintain SQL code join query efficiency. Figure 9-27: Some example time stamp records. . Products Products are a bit of a misfit in the online auction house data warehouse database model. Products are essentially the same as the online auction house categories. Also, the price PRODUCT table PRICE field is irrelevant because there are no fixed prices for each listing category. Prices are flexible, determined by a multitude of different sellers, and ultimately the buyers making the bids. The PRODUCT table is, therefore, irrelevant to the data warehouse database model for the online auction house. The ERD in Figure 9-25 would be adjusted as shown in Figure 9-28. Time Stamp Elements MONTH 1 1 1 1 1 1 1 1 1 1 1 1 2 2 QUARTER 1 1 1 1 1 1 1 1 1 1 1 1 1 1 YEAR 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 1995 1996 Locations REGION North America North America North America North America North America North America North America North America North America North America North America North America North America North America COUNTRY Canada Canada Canada Canada Canada Canada United States United States United States United States United States United States United States United States STATE NS QB ON QB ON BC NY NM IA AK NC GA ME TX CITY Halifax Montreal Ottawa Quebec City Toronto Vancouver Albany Albuquerque Ames Anchorage Asheville Atlanta Augusta Austin 250 Chapter 9
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Figure 9-25: A data warehouse database model ERD for an online auction house. Seller seller popularity_rating join_date address return_policy international payment_methods Location region country state city Time month quarter year Product product price Buyer buyer popularity_rating join_date address Listing_Bids_History listing# listing_description listing_image listing_start_date listing_days listing_currency listing_starting_price listing_reserve_price listing_buy_now_price listing_number_of_bids listing_winning_price listing_winner_buyer luyer_bidder seller bidder_price bidder_date history_buyer history_buyer_comment_date history_buyer_comments history_seller history_seller_comment_date history_seller_comments Category_Hierarchy parent category 249 Planning and Preparation Through Analysis
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

Data warehouse database models can have more than one star schema (more than one fact table). Fact tables are not linked together. If multiple fact tables need to be joined by SQL code queries, multiple fact tables should be constructed as a single fact table (single star schema), as shown in Figure 9-24. Figure 9-24: Multiple star schemas (fact tables) can all be merged into a single fact table. Discovering Business Rules At this stage, the data warehouse model is ready for business rule analysis and application. As previously described in this chapter, business rule application entails the building of tables, establishing the most basic of relationships between those tables, and adding sketchy ideas of table field content. Previously in this chapter, the online auction house OLTP database model already went through the basic business rules application, ERD construction process. All that is needed for the data warehouse model is a simple ERD to begin the process of representing that data warehouse database model in a mathematical fashion. Figure 9-25 shows such an ERD. Seller Category Hierarchy Time Location Buyer Product Listing -Bids -History 248 Chapter 9
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

Note in Figure 9-22 how LISTING and BID tables can be merged into a single LISTING_BIDS table. Also, the two SELLER_HISTORY and BIDDER_HISTORY tables can be merged into a single HISTORY table. Figure 9-23 shows the resulting structure of a data warehouse database model for the online auction company. The data warehouse model shown in Figure 9-23 actually contains two star schemas. Figure 9-23: A data warehouse can have multiple star schemas (multiple fact tables, connected to the same dimensions). Seller Category Hierarchy Time Location Buyer Product Listing - Bids Seller Category Hierarchy Time Location Buyer Product History Data warehouse fact tables are relatively much larger than dimension tables (in record numbers). This is the objective of star schemas in making an efficient data warehouse database model. SQL code join queries between very small tables (dimensions with few records) and very large tables (facts with gazillions of records) are the most efficient types of joins. Joining two large tables, or even equally sized tables (assuming both tables contain more than thousands of records) is much less efficient. 247 Planning and Preparation Through Analysis
We recommend high quality webhost to host and run your jsp application: christian web host services.

Figure 9-21 also shows three newly added dimensions: LOCATION, TIME, and PRODUCT. Many data warehouse database models include extra dimensions containing information gleaned from the fact tables themselves, and sometimes the dimensions as well. These extra data warehouse-specific type dimensions allow for a better and more efficient structure when separating information in a data warehouse database model: . Locations Locations are usually built from address details, establishing locations for bidders and sellers (for the online auction house) as being in a specific city, state, country, continent, planet, star system, galaxy, and universe. That may seem silly, but you get the point. A location dimension allows analysis of data warehouse information into reports, based on regions. For example, what sells well, in what city, what region, and so on. . Time stamps Time stamp information allows dimensional division of information into time periods. The result is data warehouse reporting that can assess how a company performed in specific periods. For example, reporting could compare profitability in different years, across the same months or quarters. This type of reporting can help to assess the health of a business, among many other things (such as when business should be expected to pick up). If a company has a Christmas rush of trading, they might be able to prepare better for what types of products sell at Christmas, how much they need to manufacture, and where specific products need to be distributed to. . Products Product dimensional information allows division of reporting based on different products, similar to how information and reporting can be divided up into locations and time stamps. There are numerous other data warehouse-specific static information structures. Locations, times, and products are probably the most commonly additional dimensions. Essentially, commonly used dimensions in data warehouse database models (such as locations, times, and products) are generally used all at the same time, in the same reports. Also, data warehouse databases can be become so humongous that serious performance gains can be found by applying reporting filtering using information such as locations, times, and products. Figure 9-22 shows a more detailed star schema for the online auction company data warehouse database model. In Figure 9-22, you can see that the dimensions are still pointing directly, and individually, at all the facts (transactional information). Typically, in a data warehouse, database dimension tables have very few records, relative to the size of fact tables. Fact tables can often rise into the millions, and even billions and trillions of records. Dimension tables containing tens, hundreds, or even thousands of records, and contain relatively much smaller numbers of records than fact tables. This is the idea! 246 Chapter 9
Check Tomcat Web Hosting services for best quality webspace to host your web application.

Figure 9-22: Analyzing the facts in a data warehouse database model. In Figure 9-20, the three categories in the OLTP database model can be amalgamated into a single hierarchical category table. The merge of the three tables is done by including a parent reference in the new category table, allowing a direct link from tertiary to secondary, and from secondary to primary. A primary category has no parent category. Also, a secondary category may contain no tertiary categories (it has no child tertiary categories). Those tertiary category records simply will not exist if they are not required. Figure 9-21 shows a developing star schema for the online auction company data warehouse database model. All the dimensions (static information containers) surround the facts (transactional information) in the form of a star schema. Seller Category Hierarchy Time Location Buyer Product Listing Bids Listing - Bids Buyer History Seller History History 245 Planning and Preparation Through Analysis
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

A data warehouse is used to contain archived information, separated from the OLTP database, thus not causing performance problems with the OLTP database. Establishing Company Operations Company operations have already been established when analyzing the database model for the OLTP database structure. All that needs to be done for a data warehouse database model is to establish what are the facts (transactional information), and what are the dimensions (static data). This can be done in a number of stages, as shown in Figure 9-20, Figure 9-21, and Figure 9-22. Figure 9-20: Data warehouse data modeling denormalizes multiple hierarchical static tables into single static structures. Figure 9-21: A data warehouse star schema database model for the online auction house. Seller Category Hierarchy Time Location Buyer Product Facts All the dynamic information (the facts) Locations, times (dates) and products are common to many data warehouse database models Primary Category Secondary Category Tertiary Category Category Hierarchy 244 Chapter 9
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

Figure 9-19: Creating a basic analytical ERD of business rules Case Study: The Data Warehouse Model Planning and analyzing a data warehouse database model is all about separation of static and transactional information. The static information consists of the little bits and pieces describing the facts. The facts are the variable or dynamic details that are commonly destroyed or archived when no longer useful. For the online auction house all the buyer and seller details, such as their addresses, is all static information, in that it does not change much. The transactional information consists of what is added to the database and then removed from the database after a period of time. Transactional information is removed from a database to ensure that the database simply doesn t get too large to manage; however, all that historical information (such as past bids, and past listings) is valuable when trying to perform forecasting reporting. For example, the online auction house may want to promote and advertise. If auctions selling toys is 100 times more prevalent than selling old LP records from the 1950s, perhaps marketing to toy sellers is a better use of advertising funds. Executing forecasting reports, extrapolating from information over the last five years, could be very useful indeed in discovering which markets to target specifically. Old, out-of-date, and archived data can be extremely useful. Instrument instrument musician name phone email skill skill Advertisement ad_date ad_text phone email requirements band name members founding_date genre genre shows location address directions phone show_date show_times merchandise type price discography cd_name release_date price 243 Planning and Preparation Through Analysis
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

Figure 9-17: Identifying basic operations. Figure 9-18 goes just a little further by establishing relationships between the different operations described in Figure 9-17. In other words, musicians play instruments and have skills. Bands are usually of a specific genre. Both musicians and bands can place classified ads to advertise themselves. Figure 9-18: Linking the basic operations. Figure 9-19 shows a briefly constructed ERD as an application of business rules to the operational diagram shown in Figure 9-18. There are a number of important points to note: . Musicians can play multiple instruments. . Musicians can be multi-skilled. . A band can have multiple genres. . The MEMBERS field in the band table takes into account the one-to-many relationship between BAND and MUSICIAN. In other words, there is usually more than one musician in a band; however, a musician doesn t necessarily have to be in a band, a band may be broken up and have no musicians, and both bands and musicians can advertise. . Musicians and bands can place advertisements. Instrument Genre Musician Classified Ad Band Skill Instrument Musician Skill Genre Band Classified Ad 242 Chapter 9
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.