Part IV Advanced Topics In this Part: Chapter 13: Advanced Database Structures and Hardware Resources
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.
This chapter ends the case study of the OLTP and data warehouse database models using the online auction house. This chapter has refined the implementation of business rules to the hilt by applying fieldlevel attributes and settings as being part of table field structure. Additionally, very advanced business rules can be applied using stored database coding (encoding). The next chapter discusses hardware resources, as applied to database modeling. 380 Chapter 12
In case you need quality webspace to host and run your web applications, try our personal web hosting services.
PRICE MONEY FORMAT $9,999,990.99 NOT NULL ); CREATE TABLE SHOW_VENUE ( SHOW_ID INTEGER PRIMARY KEY NOT NULL, LOCATION CHAR VARYING(32) NOT NULL, ADDRESS_LINE_1 CHAR VARYING(32) NOT NULL, ADDRESS_LINE_2 CHAR VARYING(32) NULL, TOWN CHAR VARYING(32) NOT NULL, ZIP NUMBER(5) FORMAT 99999 NULL, POSTAL_CODE CHAR VARYING(32) NULL, COUNTRY CHAR VARYING(32) NULL, DIRECTIONS MEMO NULL, PHONE CHAR VARYING(32) NULL SHOW_DATE DATE FORMAT $9,999,990.99 NOT NULL, SHOW_TIME CHAR VARYING(16) FORMAT 90:90:90 NOT NULL ); CREATE TABLE FACT ( FACT_ID INTEGER NOT NULL, SHOW_ID INTEGER FOREIGN KEY REFERENCES SHOW WITH NULL, MUSICIAN_ID INTEGER FOREIGN KEY REFERENCES MUSICIAN WITH NULL, BAND_ID INTEGER FOREIGN KEY REFERENCES BAND WITH NULL, ADVERTISEMENT_ID INTEGER FOREIGN KEY REFERENCES ADVERTISEMENT WITH NULL, DISCOGRAPHY_ID INTEGER FOREIGN KEY REFERENCES DISCOGRAPHY WITH NULL, MERCHANDISE_ID INTEGER FOREIGN KEY REFERENCES MERCHANDISE WITH NULL, GENRE_ID INTEGER FOREIGN KEY REFERENCES GENRE WITH NULL, INSTRUMENT_ID INTEGER FOREIGN KEY REFERENCES INSTRUMENT WITH NULL, CD_SALE_AMOUNT MONEY FORMAT $9,999,990.99 NULL, MERCHANDISE_SALE_AMOUNT MONEY FORMAT $9,999,990.99 NULL, ADVERTISING_COST_AMOUNT MONEY FORMAT $9,999,990.99 NULL, SHOW_TICKET_SALES_AMOUNT MONEY FORMAT $9,999,990.99 NULL ); Summary In this chapter, you learned about: . How basic database model business rules classifications are normalization, Normal Forms, tables, and relations . How more complex business rules can be implemented in a database model using field settings, such as display FORMATs, input MASKings, CHECK constraints, UNIQUE key constraints, and DEFAULT settings . How stored database code can be used to implement highly complex business rules, using stored procedures, stored functions and event triggers 379 Business Rules and Field Settings
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.
How It Works There are no appropriate CHECK constraints and no appropriate stored encoding. Remember that input to a data warehouse is unlikely to be manual input, so input MASK settings and CHECK constraints generally do not apply. Additionally, UNIQUE constraints are not needed either (they are overhead) when data is application generated anyway. This script contains appropriate changes: CREATE TABLE INSTRUMENT ( INSTRUMENT_ID INTEGER PRIMARY KEY NOT NULL, SECTION_ID INTEGER FOREIGN KEY REFERENCES INSTRUMENT WITH NULL, INSTRUMENT CHAR VARYING(32) NOT NULL ); CREATE TABLE MUSICIAN ( MUSICIAN_ID INTEGER PRIMARY KEY NOT NULL, MUSICIAN CHAR VARYING(32) NOT NULL, PHONE CHAR VARYING(32) NULL, EMAIL CHAR VARYING(32) NULL ); CREATE TABLE GENRE ( GENRE_ID INTEGER PRIMARY KEY NOT NULL, PARENT_ID INTEGER FOREIGN KEY REFERENCES GENRE WITH NULL, GENRE CHAR VARYING(32) NOT NULL ); CREATE TABLE BAND ( BAND_ID INTEGER PRIMARY KEY NOT NULL, BAND CHAR VARYING(32) NOT NULL, FOUNDING_DATE DATE FORMAT DD MON, YEAR NOT NULL ); CREATE TABLE ADVERTISEMENT ( ADVERTISEMENT_ID INTEGER PRIMARY KEY NOT NULL, DATE DATE FORMAT DD MON, YEAR NOT NULL, TEXT MEMO NOT NULL ); CREATE TABLE DISCOGRAPHY ( DISCOGRAPHY_ID INTEGER PRIMARY KEY NOT NULL, CD_NAME CHAR VARYING(32) NOT NULL, RELEASE_DATE DATE FORMAT DD MON, YEAR NULL, PRICE MONEY FORMAT $9,999,990.99 NULL ); CREATE TABLE MERCHANDISE ( MERCHANDISE_ID INTEGER PRIMARY KEY NOT NULL, TYPE CHAR VARYING(32) NOT NULL, 378 Chapter 12
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.
EMAIL CHAR VARYING(32) NULL, SKILLS CHAR VARYING(256) NULL ); CREATE TABLE ADVERTISEMENT ( ADVERTISEMENT_ID INTEGER PRIMARY KEY NOT NULL, BAND_ID INTEGER FOREIGN KEY REFERENCES BAND WITH NULL, MUSICIAN_ID INTEGER FOREIGN KEY REFERENCES MUSICIAN WITH NULL, DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL, TEXT MEMO NOT NULL ); Try It Out Field Level Business Rules for a Data Warehouse Database Model Figure 12-9 shows an ERD for the musician s data warehouse database model. Here s a basic approach to business rules field settings: 1. Individual field business rules (including defaults, CHECK constraints, display formats, and input masks) 2. Encoding business rules using some kind of stored database coding, if and where appropriate (usually CHECK constraint functions). Figure 12-9: Musicians, bands, and their online advertisements data warehouse database model. Artists artist_id merchandise_id (FK) genre_id (FK) instrument_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 advertisement_date advertisement_text Genre genre_id parent_id (FK) genre Merchandise merchandise_id type price Instrument instrument_id section_id (FK) instrument 377 Business Rules and Field Settings
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.
( VENUE_ID INTEGER PRIMARY KEY NOT NULL, LOCATION CHAR VARYING(32) UNIQUE NOT NULL, ADDRESS_LINE_1 CHAR VARYING(32) NOT NULL, ADDRESS_LINE_2 CHAR VARYING(32) NULL, TOWN CHAR VARYING(32) NOT NULL, ZIP NUMBER(5) FORMAT 99999 MASK 99999 NULL, POSTAL_CODE CHAR VARYING(32) NULL, COUNTRY CHAR VARYING(32) NULL, DIRECTIONS MEMO NULL, PHONE CHAR VARYING(32) NULL ); CREATE TABLE MERCHANDISE ( MERCHANDISE_ID INTEGER PRIMARY KEY NOT NULL, BAND_ID INTEGER FOREIGN KEY REFERENCES BAND NOT NULL, TYPE CHAR VARYING(32) UNIQUE NOT NULL, PRICE MONEY FORMAT $9,999,990.99 MASK 9999990.00 NOT NULL ); CREATE TABLE DISCOGRAPHY ( DISCOGRAPHY_ID INTEGER PRIMARY KEY NOT NULL, BAND_ID INTEGER FOREIGN REFERENCES BAND NOT NULL, CD_NAME CHAR VARYING(32) NOT NULL, RELEASE_DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL, PRICE MONEY FORMAT $9,999,990.99 MASK 9999990.00 NOT NULL ); CREATE TABLE SHOW ( SHOW_ID INTEGER PRIMARY KEY NOT NULL, BAND_ID INTEGER FOREIGN KEY REFERENCES BAND NOT NULL, VENUE_ID INTEGER FOREIGN KEY REFERENES VENUE NOT NULL, DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL, TIME CHAR VARYING(16) FORMAT 90:90:90 MASK 90:90:90 NOT NULL ); CREATE TABLE BAND ( BAND_ID INTEGER PRIMARY KEY NOT NULL, GENRE_ID INTEGER FOREIGN KEY REFERENCES GENRE NOT NULL, BAND CHAR VARYING(32) UNIQUE NOT NULL, FOUNDING_DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL ); CREATE TABLE MUSICIAN ( MUSICIAN_ID INTEGER PRIMARY KEY NOT NULL, INSTRUMENT_ID INTEGER FOREIGN KEY REFERENCES INSTRUMENT NOT NULL, BAND_ID INTEGER FOREIGN KEY REFERENCES BAND WITH NULL, MUSICIAN CHAR VARYING(32) UNIQUE NOT NULL, PHONE CHAR VARYING(32) NULL, 376 Chapter 12
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.
Figure 12-8: Musicians, bands, online advertisements OLTP database model. How It Works There are no appropriate CHECK constraints and no appropriate stored encoding. This script contains appropriate changes: CREATE TABLE INSTRUMENT ( INSTRUMENT_ID INTEGER PRIMARY KEY NOT NULL, SECTION_ID INTEGER FOREIGN KEY REFERENCES INSTRUMENT WITH NULL, INSTRUMENT CHAR VARYING(32) UNIQUE NOT NULL ); CREATE TABLE GENRE ( GENRE_ID INTEGER PRIMARY KEY NOT NULL, PARENT_ID INTEGER FOREIGN KEY REFERENCES GENRE WITH NULL, GENRE CHAR VARYING(32) UNIQUE NOT NULL ); CREATE TABLE VENUE Musician musician_id instrument_id (FK) band_id (FK) musician phone email skills Advertisement advertisement_id band_id (FK) musician_id (FK) date text Band band_id genre_id (FK) band founding_date Venue venue_id location address_line_1 address_line_2 town zip postal_code country directions phone Merchandise merchandise_id band_id (FK) type price Show show_id band_id (FK) venue_id (FK) date time Genre genre_id parent_id (FK) genre Instrument instrument_id section_id (FK) instrument Discography discography_id band_id (FK) cd_name release_date price 375 Business Rules and Field Settings
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.
CURR_PRICE MONEY; BEGIN REMARK — Throw out bids that are incorrectly passed in IF LISTING# IS NULL OR BID IS NULL OR BID <= 0 THEN RETURN FALSE; REMARK -- bidding price (including proxy bids) must exceed starting price REMARK -- if current price is NULL, otherwise must exceed current price SELECT STARTING_PRICE, CURRENT_PRICE INTO START_PRICE, CURR_PRICE FROM LISTING WHERE LISTING# = LISTNUM; IF CURR_PRICE IS NULL THEN IF BID <= START_PRICE THEN RETURN FALSE; ELSE IF BID <= CURR_PRICE THEN RETURN FALSE; END IF; RETURN TRUE; END; The preceding script applies to both normal (regular) bids and to proxy bids as well. Aproxy bid must always be greater than the current bid price; otherwise, the proxy bid is just a normal bid. Aproxy bid is where a buyer sets a maximum price that they will bid to. Whenever other bidders place bids, the system automatically increments the highest bid price with the current increment, until the proxy bid is reached. If the competing bidder exceeds the proxy bid, the other buyer becomes the current highest bidder. Encoding Business Rules for the Data Warehouse Database Model In short, there is nothing that you would want to attach to a data warehouse database model in the form of database model embedded coding, acting on the database model structures. It would simply be contrary to the existence of a data warehouse. Try It Out Field Level Business Rules for an OLTP Database Model Figure 12-8 shows an ERD for the musicians OLTP database model. Here s a basic approach to business rules field settings: 1. Individual field business rules (including defaults, CHECK constraints, display formats, and input masks) 2. Encoding business rules using some kind of stored database coding, if and where appropriate (usually CHECK constraint functions) 374 Chapter 12
We recommend high quality webhost to host and run your jsp application: christian web host services.
LISTING_NUMBER_OF_BIDS INTEGER FORMAT 999 NULL, LISTING_WINNING_PRICE MONEY NULL, LISTING_BID_INCREMENT MONEY NULL, BID_PRICE MONEY NULL ); The LISTING_NUMBER_OF_BIDS is output formatted as for number of bids in the OLTP database model LISTING table. Encoding Business Rules As with the previous section covering individual field business rules, this section covers the case study online auction house database models, but this time attempting to encode a few things into a pseudo database programming language. The intention here is to demonstrate what can be done. It is a matter for some debate among computer professionals as to whether business rules should be written into stored procedures. Some think implementing business rules in stored procedures is good for some reasons. Others consider that applications handle this type of complexity more effectively and efficiently. Encoding Business Rules for the OLTP Database Model You already know the difference between a stored procedure, a stored function, and an event-based trigger. What can be done to the OLTP database model for this case study, to utilize some type of database stored coding? The BID table is a good candidate for some basic stored functions: CREATE TABLE BID ( LISTING# CHAR(10) NOT NULL, BUYER_ID INTEGER FOREIGN KEY REFERENCES BUYER NOT NULL, BID_PRICE MONEY CHECK(VERIFY_BID(LISTING#, BID_PRICE)) NOT NULL, PROXY_BID MONEY CHECK(PROXY_BID > BID_PRICE AND VERIFY_BID(LISTING#, PROXY_BID)) NULL, BID_DATE DATE FORMAT DD MON, YEAR NOT NULL, CONSTRAINT PRIMARY KEY (LISTING#, BUYER_ID) ); The preceding script has a CHECK constraint on the BID_PRICE and PROXY_BID fields. Both of these CHECK constraints execute a function. A bid price is entered by a bidder. That bid price must exceed the starting and current prices, both of which are stored in the listing table. This can be encoded using a stored function as follows: CREATE FUNCTION VERIFY_BID(LISTNUM CHAR(10), BID MONEY DEFAULT NULL) RETURN BOOLEAN DECLARE START_PRICE MONEY; 373 Business Rules and Field Settings
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.
POPULARITY_RATING SMALLINT NULL ); CREATE TABLE LOCATION ( LOCATION_ID INTEGER PRIMARY KEY NOT NULL, REGION CHAR VARYING(32) NOT NULL, COUNTRY CHAR VARYING(32) NOT NULL, STATE CHAR(2) NULL, CITY CHAR VARYING(32) NOT NULL, CURRENCY_TICKER CHAR(3) NOT NULL, CURRENCY CHAR VARYING(32) UNIQUE NOT NULL, EXCHANGE_RATE FLOAT NOT NULL, DECIMALS SMALLINT FORMAT 9 NULL ); The CURRENCY_TICKER field is not UNIQUE in the LOCATION table because locations are duplicated within countries by both states and cities. Next, consider the TIME table: CREATE TABLE TIME ( TIME_ID INTEGER PRIMARY KEY NOT NULL, YEAR INTEGER FORMAT 9999 NOT NULL, QUARTER INTEGER FORMAT 9 NOT NULL, MONTH INTEGER FORMAT 99 NOT NULL ); Years are output formatted to ensure all four digits are always returned. Quarters are formatted to show only 1, 2, 3, or 4. Months are formatted to show 01 for January and 12 for December. Finally, look at the LISTING_BIDS table: CREATE TABLE LISTING_BIDS ( LISTING# CHAR(10) PRIMARY KEY NOT NULL, BID_ID INTEGER FOREIGN KEY REFERENCES BID NOT NULL, BUYER_ID INTEGER FOREIGN KEY REFERENCES BUYER WITH NULL, BIDDER_ID INTEGER FOREIGN KEY REFERENCES BUYER WITH NULL, SELLER_ID INTEGER FOREIGN KEY REFERENCES SELLER WITH NULL, TIME_ID INTEGER FOREIGN KEY REFERENCES TIME WITH NULL, LOCATION_ID INTEGER FOREIGN KEY REFERENCES LOCATION WITH NULL, CATEGORY_ID INTEGER FOREIG KEY REFERENCES CATEGORY WITH NULL, LISTING_STARTING_PRICE MONEY NOT NULL, LISTING_RESERVE_PRICE MONEY NULL, LISTING_BUY_NOW_PRICE MONEY NULL, LISTING_START_DATE DATE FORMAT DD MON, YEAR NOT NULL, LISTING_DAYS SMALLINT NOT NULL, 372 Chapter 12
In case you need quality webspace to host and run your web applications, try our personal web hosting services.