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

Figure 10-38: Denormalized musicians, bands, their online advertisements, and some other goodies. Figure 10-38 is a partially complete data warehouse database model, with all the facts rolled into a single table. Figure 10-39 shows a finalized, much more sensible star schema, based purely on relative record numbers in various tables from Figure 10-39. Larger record numbers tend to warrant tables as being factual rather than dimensional in nature. Artists artist_id musician_id (FK) musician_name musician_phone musician_email band_name band_founding_date discography_cd_name discography_release_date discography_price show_date show_time venue_name venue_address venue_directions venue_phone advertisment_date advertisement_text Musician musician_id instruments skills Merchandise merchandise_id type price Band genre 315 Creating and Refining Tables During the Design Phase
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

314 Chapter 10 Figure 10-37: Musicians, bands, their online advertisements, and some other goodies. How It Works Figure 10-37 shows the analyzed data warehouse database model, for online musician and band advertisements. The most significant requirement is to ultimately produce a single star schema, if a single star schema is possible. Also add any dimensional and fact information shown as additional in Figure 10-32. Figure 10-37 shows that the SHOW table is actually fact information, not dimensional. Examine Figure 10-37 once more. Think about the records in the tables. Yes, many advertisements are possible. However, a simple search of the Internet on Web sites such as www.themode.com and www.ticketmaster.com will reveal to you the sheer volume of advertisements, musicians, bands, shows, discography (released CDs), and venues. Figure 10-38 takes another slant on this data warehouse database model by rolling all of these tables into a single fact table. Musician musician_id musician phone email instruments skills Advertisement advertisement_id band_id (FK) musician_id (FK) ad_date ad_text Band band_id band founding_date genre Discography discography_id band_id (FK) cd_name release_date price Merchandise merchandise_id band_id (FK) type price Show show_id venue_id (FK) band_id (FK) venue date time Venue venue_id venue address directions phone
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

History fact_id tertiary_id (FK) secondary_id (FK) location_id (FK) time_id (FK) buyer_id (FK) seller_id (FK) history_buyer history_buyer_comment_date history_buyer_comments history_seller history_seller_comment_date history_seller_comments Location location_id region_id (FK) country_id (FK) state_id (FK) city_id (FK) City city_id state_id (FK) city State state_id country_id (FK) state Country country_id region_id (FK) country Seller seller_id popularity_rating join_date Category_Secondary secondary_id primary_id (FK) secondary Category_Tertiary tertiary_id secondary_id (FK) tertiary Buyer buyer_id popularity_rating join_date Time time_id year_id (FK) quarter_id (FK) month_id (FK) Quarter quarter_id year_id (FK) quarter Month month_id quarter_id (FK) month Year year_id year Buyer_Name buyer_id (FK) name Buyer_Address buyer_id (FK) address Category_Primary primary_id primary Seller_Nulls seller_id (FK) return_policy international Seller_Payment Methods seller_id (FK) payment_method Seller_Name seller_id (FK) name Seller_Address seller_id (FK) address Region region_id region Figure 10-36: A data warehouse HISTORY fact table snowflake schema (a history data mart).
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.

312 Chapter 10 Essentially, Figure 10-34 and Figure 10-35 represent the best, most effective, most easily understandable, and usable database model for the online auction house data warehouse. It is possible to normalize further by normalizing the heck out of the dimensions just don t normalize the facts. Normalizing facts (other than making operational fact table splits into multiple star schemas, as shown in Figure 10-34) defeats the purpose of the data warehouse dimensional-fact model. Figure 10-36 shows an ERD of the HISTORY fact table snowflake schema, with dimensions normalized up to gazoo! A snowflake schema is a star schema, where dimensions have been normalized. There is no need to detail query examples for the data warehouse database model, as the same concepts apply for SQL coding of query joins for both OLTP and data warehouse databases: . The fewer tables in a join, the better. . It is more efficient to join between a small table and a large table, compared with equally sized tables. Obviously, joining two small tables is most efficient because there isn t much data (which should be logical by now). Using the snowflake schema shown in Figure 10-36 is not only completely nuts, it will also drive your programmers completely nuts trying to figure it all out. And end-users simply won t know what the heck is going on in there. End-users, typically in-house type end-users, often write (or at least specify) data warehouse reporting requirements. The obsessively over-normalized data warehouse database model shown in Figure 10-36 is quite simply impractical. The end-users it will probably think it is just scary, and they will probably avoid it. Try It Out Designing a Data Warehouse Database Model Create a simple design level data warehouse database model, for a Web site. This Web site allows creation of free classified ads for musicians and bands. Use the not-so-well-refined data warehouse database model presented in Figure 10-37 (copied from Figure 9-29, in Chapter 9). Here s a basic approach: 1. Refine dimensions and facts, making sure that dimensions are dimensions and facts are facts. 2. Divide facts into multiple star schemas if multiple, unrelated fact sets exist. 3. Normalize dimensions into a snowflake schema. This can help to identify and quantify dimensions more precisely. 4. Denormalize dimensions into a star schema. The primary purpose of the data warehouse fact-dimensional model is to allow the fastest possible join method between two tables: one large table, and one or more very small tables.
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

Creating and Refining Tables During the Design Phase A more easily readable form of the star schemas shown in Figure 10-34 is shown in Figure 10-35. If you go back briefly to Chapter 9 and examine Figure 9-23, you can see that this structure was already devised, even in the analysis stage. Figure 10-35: The data warehouse fact table star schemas, for the online auction house. Category Hierarchy Time Seller Location Buyer Listing -Bids Listing -Bids Buyer Category Hierarchy Time Seller Location History 311
Check Tomcat Web Hosting services for best quality webspace to host your web application.

The problem as shown in Figure 10-33 is that there is essentially an operational requirement for a one-tomany (master-detail) relationship between listing and bid facts. Why? Think about how this data would be used. If you wanted to track listing information alone, then there is no problem. If you wanted to analyze the pattern of bids against particular types of listings, then that LISTING to BID relationship would be required. Establishing a relationship between multiple fact tables causes serious problems. The reason why goes back to the existence of the fact-dimensional data warehouse database model itself. Data warehouse database models were devised to split very small tables, linked in a single-layer hierarchy of dimensions (a star schema), all linked to a single fact table. Fact tables are very large. The most efficient types of query joins are those between one or more small tables (the dimensions) and only a single large table. That great big humungous table is the fact table. And there should also be only one fact table in a star schema. Never relate or join fact tables. You might wait a week for queries to execute! Linking more than one fact table together results in a join between two very large tables, which can be frighteningly inefficient defeating the very existence of the fact-dimensional data warehouse database model. Don t do it! A solution is to merge and denormalize the fact tables as shown in Figure 10-34. The HISTORY fact table is not a problem because histories apply to sellers and buyers. In the real world, histories are used by sellers and buyers to assess whether they are dealing with an honest trader or a complete shyster! Figure 10-34: Reducing the three fact tables in Figure 10-33 to two fact tables, based on operational requirements. Listing-Bid Facts Seller seller_id seller popularity_rating join_date address return_policy international payment_methods Location location_id region country state city Time time_id month quarter year Category_Hierarchy category_id parent_id (FK) category Listing_Bids_History fact_id time_id (FK) buyer_id (FK) location_id (FK) seller_id (FK) category_id (FK) 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 bidder bidder_price bidder_date Buyer buyer_id buyer popularity_rating join_date address Seller seller_id seller popularity_rating join_date address return_policy international payment_methods Location location_id region country state city Time time_id month quarter year Category_Hierarchy category_id parent_id (FK) category History fact_id time_id (FK) buyer_id (FK) location_id (FK) seller_id (FK) category_id (FK) history_buyer history_buyer_comment_date history_buyer_comments history_seller history_seller_comment_date history_seller_comments Buyer buyer_id buyer popularity_rating join_date address History Facts Combining the listing and bid facts eliminates the inappropriate relationship 310 Chapter 10
You want to have a cheap webhost for your apache application, then check apache web hosting services.

One-to-many relationship Listing FactsListing Facts Seller seller_id seller popularity_rating join_date address return_policy international payment_methods Location location_id region country state city Time time_id month quarter year Category_Hierarchy category_id parent_id (FK) category Listing fact_id time_id (FK) buyer_id (FK) location_id (FK) seller_id (FK) category_id (FK) 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 Buyer buyer_id buyer popularity_rating join_date address Seller seller_id seller popularity_rating join_date address return_policy international payment_methods Location location_id region country state city Time time_id month quarter year Category_Hierarchy category_id parent_id (FK) category Bid fact_id time_id (FK) buyer_id (FK) location_id (FK) seller_id (FK) category_id (FK) bidder bidder_price bidder_date Buyer buyer_id buyer popularity_rating join_date address Bid FactsBid Facts Seller seller_id seller popularity_rating join_date address return_policy international payment_methods Location location_id region country state city Time time_id month quarter year Category_Hierarchy category_id parent_id (FK) category History fact_id time_id (FK) buyer_id (FK) location_id (FK) seller_id (FK) category_id (FK) history_buyer history_buyer_comment_date history_buyer_comments history_seller history_seller_comment_date history_seller_comments Buyer buyer_id buyer popularity_rating join_date address History Facts History Facts Histories pertain to sellers and buyers only Figure 10-33: Dividing the data warehouse database model into separate facts.
We recommend high quality webhost to host and run your jsp application: christian web host services.

Figure 10-32 refines the database model with denormalization. All the nasty detailed Normal Forms are removed. The VENUE is retained since venues are static (dimensional) and shows are dynamic (fact data). Figure 10-32: Refining the database model with denormalization. Case Study: Refining a Data Warehouse Database Model Figure 10-12 shows the most recent version of the data warehouse database model for the online auction house. From an operational perspective, you identified categories, sellers, buyers, locations, times, and a large fact table. Where do you go from here? What you need to do is probably to normalize the fact table somewhat, into separate fact tables. Separate fact tables become separate star schemas, and thus separate data marts. Figure 10-33 shows an initial split of facts into three separate fact tables (see Figure 10-12). Instrument section_id (FK) instrument instrument_id Genre parent_id (FK) genre genre_id Musician instrument_id (FK) band_id (FK) musician phone email skills musician_id Band genre_id (FK) band founding_date band_id Discography band_id (FK) cd_name release_date price discography_id Merchandise band_id (FK) type price merchandise_id Venue location address directions phone venue_id Show venue_id (FK) band_id (FK) date time show_id Advertisement band_id (FK) musician_id (FK) ad_date ad_text advertisement_id 308 Chapter 10
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

307 Creating and Refining Tables During the Design Phase Figure 10-31 refines the database model with normalization. This is about as far as this database model can be normalized. The INSTRUMENT and GENRE tables could be normalized if the number of layers in the hierarchies of the two tables is known. For the purposes of this example, it is assumed this is not the case. Some fields have been removed from various tables for being inappropriate. For example, the PHONE, EMAIL, and REQUIREMENTS fields have been removed from the ADVERTISEMENT table, assuming they are included in the AD_TEXT field. Some fields have been renamed. Figure 10-31: Refining the database model with normalization. Skill musician_id (FK) skill Musician_Phone musician_id (FK) phone Venue_Phone venue_id (FK) phone Venue_Directions venue_id (FK) directions Venue_Address venue_id (FK) address Musician_Name musician_id (FK) musician Band_Name band_id (FK) band Musician musician_id instrument_id (FK) band_id (FK) Band band_id genre_id (FK) founding_date Instrument instrument_id section_id (FK) instrument Genre genre_id parent_id (FK) genre Venue venue_id venue Type type_id description Advertisement advertisement_id band_id (FK) musician_id (FK) ad_date ad_text Discography discography_id band_id (FK) cd_name release_date price Merchandise merchandise_id type_id (FK) band_id (FK) price Show show_id venue_id (FK) band_id (FK) date time Musician_Email musician_id (FK) email
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

306 Chapter 10 Figure 10-30 refines relationships between tables as identifying, non-identifying, and NULL valued. The INSTRUMENT table contains a hierarchy, with instruments divided into sections (such as a strings section, or a percussion section). The GENRE table also contains a hierarchy of genres and sub-genres. For example, hard rock music is part of rock music, and rock music is part of modern music. Hard rock music could contain sub-genres as well, such as alternative rock. The INSTRUMENT and GENRE table has self join or fishhook join relationships added, to represent the hierarchies. A musician must play at least one instrument (voice is an instrument). Skills cannot exist without a musician. Figure 10-30: Refining relationships as identifying, non-identifying, and NULL valued. Instrument section_id (FK) instrument instrument_id Skill musician_id (FK) skill Genre parent_id (FK) genre genre_id Musician instrument_id (FK) band_id (FK) name phone email musician_id Band genre_id (FK) name members founding_date band_id Discography band_id (FK) cd_name release_date price discography_id Merchandise band_id (FK) type price merchandise_id Show band_id (FK) location address directions phone show_date show_times show_id Advertisement band_id (FK) musician_id (FK) ad_date ad_text phone email requirements advertisement_id
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.