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

Bear in mind that field settings for the OLTP database model discussed in the previous section, are input mask restrictions (MASK), CHECK constraints, and output display formats (FORMAT). Data warehouse tables are rarely, if ever, manually entered into. Data warehouse tables are fully generated. using coding and scripting, all generated from source databases, such as an OLTP database. Therefore, no MASK settings are required. This approach applies to CHECK constraints as well because CHECK constraints are intended to check specific values on field input. FORMAT settings are useful for output only and thus do apply for a data warehouse, where those fields may someday be output in reports. In short, there are very few changes for the data warehouse model shown in the following scripts. Additionally, for any CHECK constraints across multiple fields, you should assume them to be correct, because the code generating data warehouse entries should be correct. First up are the CATEGORY and SELLER tables: CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER PRIMARY KEY NOT NULL, PARENT_ID INTEGER FOREIGN KEY REFERENCES CATEGORY WITH NULL, CATEGORY CHAR VARYING(32) UNIQUE NOT NULL ); CREATE TABLE SELLER ( SELLER_ID INTEGER PRIMARY KEY NOT NULL, SELLER CHAR VARYING(32) UNIQUE NOT NULL, COMPANY CHAR VARYING(32) UNIQUE NOT NULL, COMPANY_URL CHAR VARYING(64) FORMAT WWW.[X…].XXX UNIQUE NOT NULL, POPULARITY_RATING SMALLINT NULL, FEEDBACK_POSITIVE SMALLINT FORMAT 99990 NULL, FEEDBACK_NEUTRAL SMALLINT FORMAT 99990 NULL, FEEDBACK_NEGATIVE SMALLINT FORMAT 99990 NULL ); The FORMAT setting for the COMPANY_URL field is the same as for the SELLER table in the OLTP database model, shown in the previous section. All UNIQUE settings could be deleted. There is no reason why UNIQUE key restrictions should be retained within data warehouse tables because data warehouse table data is supposed to be automatically generated. The generation code could take uniqueness into account. The FORMAT 99990 settings for the feedback aggregations means that the number 0 is returned as 0 , 456 is returned as 456 not 00456 . Obviously, 12345 is returned as 12345 . Now look at the BIDDER and LOCATION tables: CREATE TABLE BIDDER ( BIDDER_ID INTEGER PRIMARY KEY NOT NULL, BIDDER CHAR VARYING(32) UNIQUE NOT NULL, 371 Business Rules and Field Settings
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

Finally, consider the HISTORY table: CREATE TABLE HISTORY ( HISTORY_ID INTEGER PRIMARY KEY NOT NULL, SELLER_ID INTEGER FOREIGN KEY REFERENCES SELLER WITH NULL, BUYER_ID INTEGER FOREIGN KEY REFERENCES BUYER WITH NULL, COMMENT_DATE DATE FORMAT DD MON, YEAR NOT NULL, FEEDBACK_POSITIVE SMALLINT NULL, FEEDBACK_NEUTRAL SMALLINT NULL, FEEDBACK_NEGATIVE SMALLINT NULL ); There is nothing new for the HISTORY table other than the COMMENT_DATE field, with the same function and restriction as the BID table BID_DATE field. Field Level Business Rules for the Data warehouse Database Model Figure 12-7 shows the most recent version of the ERD for the data warehouse database model for the online auction house. Figure 12-7: The online auction house data warehouse database model. Bidder bidder_id bidder popularity_rating feedback_positive feedback_neutral feedback_negative Category_Hierarchy category_id parent_id (FK) category Seller seller_id seller company company_url popularity_rating feedback_positive feedback_neutrals feedback_negatives Location location_id region country state city currency_ticker currency exchange_rate decimals Time time_id year quarter month Listing_Bids bid_id buyer_id (FK) bidder_id (FK) seller_id (FK) time_id (FK) location_id (FK) category_id (FK) listing# listing_start_date listing_days listing_starting_price listing_bid_increment listing_reserve_price listing_buy_now_price listing_number_of_bids listing_winning_price bid_price 370 Chapter 12
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

IMAGE BINARY NULL, START_DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL, LISTING_DAYS SMALLINT FORMAT 9 MASK 9 DEFAULT 7 NOT NULL, STARTING_PRICE MONEY NOT NULL, BID_INCREMENT MONEY NULL, RESERVE_PRICE MONEY NULL, BUY_NOW_PRICE MONEY NULL, NUMBER_OF_BIDS SMALLINT FORMAT 999 NULL, WINNING_PRICE MONEY NULL, CURRENT_PRICE MONEY NULL, CONSTRAINT CHECK_RESERVE CHECK(RESERVE_PRICE IS NULL OR (RESERVE_PRICE > STARTING_PRICE AND RESERVE_PRICE <= BUY_NOW_PRICE)), CONSTRAINT CHECK_BUY_NOW CHECK(BUY_NOW_PRICE IS NULL OR (BUY_NOW_PRICE > STARTING_PRICE AND BUY_NOW_PRICE >= RESERVE_PRICE)) ); The LISTING_DAYS field is output formatted and input masked as a single numerical character. It is also defaulted to seven days because most listings last seven days. Sellers can pay extra to have shorter threeand five-day listings, or even longer nine-day listings. The NUMBER_OF_BIDS field is output formatted as three numeric digits. Personally, I have never seen an online auction with more than 99 bids, even the longer nine-day auctions. The CURRENT_PRICE field is added, as already stated. The two CHECK_RESERVE and CHECK_BUY_NOW constraints verify prices against the STARTING_PRICE field, and each other. Monetary amounts can t be output formatted because in these database models, they are based on currency decimal places. Currency decimal places can be 0, 2, or 3 decimal places. There is no point placing an output display format of FORMAT 9999990.99 on one of the monetary amount fields because this particular format specifies two decimal places only which would be incorrect for these database models. Take a look at the BID table: CREATE TABLE BID ( LISTING# CHAR(10) NOT NULL, BUYER_ID INTEGER FOREIGN KEY REFERENCES BUYER NOT NULL, BID_PRICE MONEY NOT NULL, PROXY_BID MONEY NULL, BID_DATE DATE FORMAT DD MON, YEAR NOT NULL, CONSTRAINT PRIMARY KEY (LISTING#, BUYER_ID) ); There is nothing new for the BID table except that the BID_DATE has only a FORMAT setting and no MASK setting. This is because the BID_DATE is inserted into a new bid record automatically, when a bidder clicks a mouse button. In other words, the bidders do not enter the data on which they make a bid the computer system does that for them. 369 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.

PAYMENT_METHOD_WIRE_TRANSFER BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_CASH BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_VISA BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_MASTERCARD BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_AMERICAN_EXPRESS BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL ); In the COMPANY_URL field, the FORMAT and MASK settings are slightly different in that the MASK setting requires the prefix HTTP://. Both format and mask settings will force a URL to be WWW.[X…].XXX where the X character represents an alphanumeric string (not alphabetic as for A ), and the [X…] implies that a string of one or more alphanumeric characters is allowed. XXX at the end of the string restricts to three characters, such as COM or BIZ. There is also TV, which is two characters but, for the purposes of simplicity, this format sticks to three characters. Dates, ZIP code and COUNTRY default values are set as before. RETURN_POLICY is defaulted as No Returns , implying that most sellers do not want things returned. In reality this is unlikely, of course. All BOOLEAN datatype fields are set as CHECK( IN ( Y , N )) and DEFAULT N . This is pseudo code and the field names are monstrously long. Thus, should be replaced with the actual field name. The CHECK constraint forces a Y or N entry, as opposed to T or F , 1 or 0 . Technically, this is only relevant to entry, and display as BOOLEAN datatypes are generally stored internally as 1 or 0 anyway. Also the default is set to N, implying that if a field is not entered, it is set to N, rather than left as NULL valued. Now tackle the LISTING table: CREATE TABLE LISTING ( LISTING# CHAR(10) PRIMARY KEY NOT NULL, CATEGORY_ID INTEGER FOREIGN KEY REFERENCES CATEGORY NOT NULL, BUYER_ID INTEGER FOREIGN KEY REFERENCES BUYER WITH NULL, SELLER_ID INTEGER FOREIGN KEY REFERENCES SELLER WITH NULL, TICKER CHAR(3) FOREIGN KEY REFERENCES CURRENCY WITH NULL, DESCRIPTION CHAR VARYING(32) NULL, 368 Chapter 12
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER PRIMARY KEY NOT NULL, PARENT_ID INTEGER FOREIGN KEY REFERENCES CATEGORY WITH NULL, CATEGORY CHAR VARYING(32) UNIQUE NOT NULL ); Next up is the SELLER table: CREATE TABLE SELLER ( SELLER_ID INTEGER PRIMARY KEY NOT NULL, SELLER CHAR VARYING(32) UNIQUE NOT NULL, COMPANY CHAR VARYING(32) UNIQUE NOT NULL, COMPANY_URL CHAR VARYING(64) UNIQUE FORMAT WWW.[X…].XXX MASK HTTP://WWW.[X…].XXX NOT NULL, POPULARITY_RATING SMALLINT NULL, JOIN_DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL, ADDRESS_LINE_1 CHAR VARYING(32) NULL, ADDRESS_LINE_2 CHAR VARYING(32) NULL, TOWN CHAR VARYING (32) NULL, ZIP NUMERIC(5) MASK 99999 NULL, POSTAL_CODE CHAR VARYING (32) NULL, COUNTRY CHAR VARYING(32) DEFAULT United States of America NULL, RETURN_POLICY CHAR VARYING(256) DEFAULT No Returns NULL, INTERNATIONAL_SHIPPING BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_PERSONAL_CHECK BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_CASHIERS_CHECK BOOLEAN CHECK( IN ( Y , N )) DEFAULT Y NULL, PAYMENT_METHOD_PAYPAL BOOLEAN CHECK( IN ( Y , N )) DEFAULT Y NULL, PAYMENT_METHOD_WESTERN_UNION BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, PAYMENT_METHOD_USPS_POSTAL_ORDER BOOLEAN CHECK( IN ( Y , N )) DEFAULT Y NULL, PAYMENT_METHOD_INTERNATIONAL_POSTAL_ORDER BOOLEAN CHECK( IN ( Y , N )) DEFAULT N NULL, 367 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.

The TICKER field has a FORMAT display setting which forces all output to be returned as three alphabetic characters. The A character is used to represent a single alphabetic a character. An alphabetic character is any character from A to Z. A ticker is a currency code for a country s currency, such as GBP for British Pounds or USD for US Dollars. MASK AAA forces the entry of at least three alphabetic characters so no currency ticker can be less than three characters in length. The exchange rate is set to a default value of 1.0 because most transactions will be national. The rate of exchange between USD and USD is quite obviously 1 because there is no currency conversion to be made. The number of decimals in the currency code makes the decimal point for any amounts in the currency flexible at 0, 2 or 3. Some weaker currencies have no decimal place such as Italian Lire. USD has 2 decimals, which is the default, some very strong currencies, such as some Middle Eastern currencies have three decimals. Now take a look at the BUYER table: CREATE TABLE BUYER ( BUYER_ID INTEGER PRIMARY KEY NOT NULL, BUYER CHAR VARYING(32) UNIQUE NOT NULL, POPULARITY_RATING SMALLINT NULL, JOIN_DATE DATE FORMAT DD MON, YEAR MASK MM/DD/YYYY NOT NULL, ADDRESS_LINE_1 CHAR VARYING(32) NULL, ADDRESS_LINE_2 CHAR VARYINGR(32) NULL, TOWN CHAR VARYING(32) NULL, ZIP NUMERIC(5) MASK 99999 NULL, POSTAL_CODE CHAR VARYING(16) NULL, COUNTRY CHAR VARYING(32) DEFAULT United States of America NULL ); The JOIN_DATE field is entry formatted for the United States of America as MM/DD/YYYY with MASK MM/DD/YYYY . For example, 6/6/2005 for 6th June, 2005. JOIN_DATE is also display formatted, but with DD MON, YEAR, such that this date when output will appear as 06 JUN, 2005. All dates in these scripts are set this way both for FORMAT and MASK settings. The ZIP code field is set to force a five-character numeric entry. In other words, 1234 will be invalid and 12345 will be valid. No output format setting is required because five numeric characters will always print as five numeric characters. None of the digits are zero. If there are any ZIP codes with leading zeroes, a FORMAT 99999 can be added such that a ZIP code of 01234 will be returned as 01234 and not 1234 (the leading 0 is likely trimmed by default, for most database engines). The default country is set as the United States of America. This is because our example online auction house is based in San Francisco, and most auction sellers and bidders live in the United States of America. There are no changes for the CATEGORY table: 366 Chapter 12
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

contain the highest current bid price. If a listing has ended, CURRENT_PRICE will be equal to WINNING_ PRICE. If a listing has no bids, CURRENT_PRICE will be NULL. As mentioned, NULL and UNIQUE key settings have already been covered in previous chapters. Previous chapters have also covered the lengths of fields, be they differences between SMALLINT and INTEGERS, or varying lengths of string fields, such as CHAR(5) and CHAR(10). Go through each table and examine default values, check constraints, plus any formatting and input masking requirements. Field setting options will be added using scripting only, interspersed with appropriate notes to elucidate. First, examine the CURRENCY table: CREATE TABLE CURRENCY ( TICKER CHAR(3) PRIMARY KEY FORMAT AAA MASK AAA NOT NULL, CURRENCY CHAR VARYING(32) UNIQUE NOT NULL, EXCHANGE_RATE FLOAT DEFAULT 1.0 NOT NULL, DECIMALS SMALLINT DEFAULT 2 FORMAT 9 MASK 9 CHECK(DECIMALS IN(0,2,3)) NULL ); Figure 12-6: The online auction house OLTP database model. Listing listing# buyer_id (FK) seller_id (FK) category_id (FK) ticker (FK) description image start_date listing_days starting_price bid_increment reserve_price buy_now_price number_of_bids winning_price current_price History history_id seller_id (FK) buyer_id (FK) comment_date feedback_positive feedback_neutral feedback_negative Seller seller_id seller company company_url popularity_rating join_date address_line_1 address_line_2 town zip postal_code country return_policy international_shipping payment_method_personal_check payment_method_cashiers_check payment_method_paypal payment_method_western_union payment_method_USPS_postal_order payment_method_international_postal_order payment_method_wire_transfer payment_method_cash payment_method_visa payment_method_mastercard payment_method_american_express Buyer buyer_id buyer popularity_rating join_date address_line_1 address_line_2 town zip postal_code country Currency ticker currency exchange_rate decimals Category_Hierarchy category_id parent_id (FK) category Bid listing# (FK) buyer_id (FK) bid_price proxy_bid bid_date 365 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.

External Procedure External procedures are very similar to stored procedures, except they are written a non-databasespecific programming language. External procedures are a chunk of code written in a language not native to the database engine (such as Java or C++). However, external procedures are still executed from within the database engine itself, perhaps on data within the database. Macro A macro is a little like a pseudo-type series of commands, typically not really a programming language, and sometimes a sequence of commands built from GUI-based actions (such as those seen on the File menu in Microsoft Access). In other words, a highly computer-literate person, who is not a programmer, stores a sequence of mouse clicks and software tool executed steps, stores them in a file, to be executed at some other time. Now get back to the case study, and continue with more in depth business model development, for the OLTP and data warehouse database models using the online auction house. Case Study: Implementing Field Level Business Rules in a Database Model The whole objective of this chapter is to show very detailed application of business rules into a database model. This can be placing too much detail into a database model. It might be too much detail because this type of business rules implementation is nearly always much more effectively and efficiently handled by application SDK programming languages. Typically, this level of business rules implementation into a database model can overcomplicate a database model, and quite often lead to serious performance problems, and serious difficulty in the day-to-day maintenance of production level databases. Table and Relation Level Business Rules In the case study so far in previous chapters, all factors for both OLTP and data warehouse database models, for all table and relational level business rules have already been covered in detail. This means that for both OLTP and data warehouse database models, for the online auction house, all the tables and relationships between them have been analyzed and designed. There is nothing more to do in this respect, as it s already been done. In other words, everything to do with normalization, Normal Forms, relations, relation types classification, referential integrity, primary and foreign keys, is already completed. No more need be said at this point. Individual Field Business Rules Individual field business rules for case study data modeling in the previous chapter has been partially covered. NULL settings and UNIQUE keys are catered for already. Default values, validation checks, and other settings (such as formatting and input maskings) are not yet covered. Field Level Business Rules for the OLTP Database Model Figure 12-6 shows the most recent version of the ERD for the OLTP database model, for the online auction house. Figure 12-6 shows that a CURRENT_PRICE field has been added to the LISTING table to 364 Chapter 12
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

Event Trigger Event triggers are used to automatically trigger events, generally in response to something that happens inside a database. Triggers typically are executed automatically based on specific database change commands (such as INSERT, UPDATE, and DELETE commands). The easiest way to demonstrate use of triggers is that of log file generation, as in the trigger shown here: CREATE TRIGGER LOG_ENTRIES AFTER INSERT ON HOUR BEGIN INSERT INTO LOG (DRIVER_ID, LOAD_ID, HOURS, MINUTES) VALUES (:NEW.DRIVER_ID, :NEW.LOAD_ID, TO_CHAR(END_TIME START_TIME, HH ), TO_CHAR(END_TIME START_TIME, MI ) ); COMMIT; EXCEPTION TRAP WHEN ERROR THEN PRINT( ERROR IN TRIGGER LOG_ENTRIES ); ROLLBACK; END; Formatting of dates into HH and MI is a pseudo-like interpretation of what a database engine may allow. TO_CHAR converts a time stamp date value (the subtraction) to a string, where the HH and MM format specify extraction of hours and minutes from the timestamp, respectively. Some database engines call event triggers just triggers, some call them rules (used for expert system construction), and some call them database events (implying something happens in a database, to trigger the database event to occur). The most significant factor to note about triggers is that they are not allowed to contain COMMIT or ROLLBACK commands. Triggers are automatically executed either before or after the execution of a database change command. Database change commands are INSERT, UPDATE, and DELETE commands. Transactional control is essentially the control of where and when a transaction is terminated. Transactions are terminated either by direct or indirect execution of a COMMIT or ROLLBACK command. A trigger is contained within a parent transaction. If the trigger doesn t change any database data, then you don t want the calling procedure or change command to execute a COMMIT command (storing all as-of-yet uncommitted changes). Transactional control must be retained outside of a trigger. Even if a trigger does change database data, transactional control must still remain within the purview of the calling command or procedure. Triggers can t directly call each other, but if a trigger includes a database change command, it can also execute other triggers, or even itself. One of the biggest dangers with profligate use of triggers is recursive activity. Quite often, a trigger-generated recursive execution (of the same trigger) is inadvertent and can result in a database slowing down, tying itself up in great big granny knots, and even appearing to halt. 363 Business Rules and Field Settings
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

SELECT MILEAGE, WEIGHT FROM ( SELECT DRIVER_ID, SUM(TOTAL_MILEAGE_SINCE_LAST_MAINTENANCE) MILEAGE , SUM(TOTAL_WEIGHT_HAULED_SINCE_LAST_MAINTENANCE) WEIGHT FROM TRUCK ) S WHERE S.DRIVER_ID = TRUCK.DRIVER_ID ); COMMIT; EXCEPTION TRAP WHEN ERROR THEN PRINT( ERROR IN PROCEDURE AGGREGATE_TOTALS ); ROLLBACK; END; The preceding procedure would be executed with a comment like this: EXECUTE AGGREGATE_TOTALS; The preceding procedure is nice and neat and tidy; however, this type of coding can cause serious performance problems because it can lock of all three tables. Stored Function A stored function is precisely the same as a stored procedure, except that it returns a single value. In programming parlance, a procedure accepts parameter values, but does not return anything (unless resetting dynamic parameters). By definition, in programming terms, a function (much like a mathematical function) always returns a value, even if the return value is NULL. Additionally, because a function always returns a value, a function is always executed as an expression, or embedded within another expression. A function is actually an expression itself. The following function directly accesses data in the LOAD table, producing a calculation based on the MILEAGE and WEIGHT fields. All fields are input with zero-valued defaults, and a COST_FACTOR is defaulted to 1.304 (arbitrary) for the purposes of making a calculation. The function accepts and returns simple values. CREATE FUNCTION LOAD_COST ( MILEAGE INTEGER DEFAULT 0, WEIGHT INTEGER DEFAULT 0, COST_FACTOR FLOAT DEFAILT 1.304 ) RETURN INTEGER BEGIN RETURN (MILEAGE * WEIGHT) * COST_FACTOR; END; The preceding function would be executed similar to this: SELECT LOAD_TYPE, LOAD_CONTENT, MILEAGE, WEIGHT, LOAD_COST(MILEAGE, WEIGHT) FROM LOAD; 362 Chapter 12
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.