Creating the Database

only for RuBoard - do not distribute or recompile

The KBI database has to be created (see Listing 11.1). It keeps its own user -login permissions, as well as the data for Reports 2 and 3. (Reports 1 and 4 will use existing data from the WCA application s commish database.)

First, create the database by launching mysql and issuing the following command at the mysql> prompt:

 create database kbi;

Listing 11.1 Creating the KBI Database

 use kbi;  /* The first thing to do is to create the table of authorized users.   * This table is just the user name; no password is required   * for the KBI application.   *   * usage:   *   *  % mysql -t < listing.11.1  */  drop table if exists tbl_authorized_users;  create table tbl_authorized_users  (user_name varchar(20));  insert into tbl_authorized_users     values('root'), ('jsmith'),('mstucky');

The data for Report 1 ”Salesperson Ranking, is already in the commish database. Because this new application is a user of the commish database, a new user login will be created that will have access to only those tables that are required for this report. For Report 2 ”Pie Chart/Sales by Division and Report 3 ”Bar Chart/Year Trend Versus Quota, the data will have to be entered into the KBI database (see Listing 11.2).

Listing 11.2 Entering the Data for Report 2 (Pie Chart) and Report 3

  (Bar/Line Chart).  use kbi;  /  * This set of commands will create the tables necessary for Report 2Pie   * Chart and Report 3Bar/Line Chart. It also fills each with the   * required data.   *   * usage:   *   * % mysql -t < listing.11.2   *   * The first table is  tbl_sales_by_division  . It is the data for   * Report 2Pie Chart. Note that if you want to run the application   * as built in Chapter 12, you need to either insert five   * rows of your own data or change the month number on one of the   * months inserted below. The month number must be the previous   * completed month; for example, in July there must be some rows in the   * data with a  sales_month  of 6 (June). The following update query will   * work; substitute your most recently completed month for 13. 13 is a   * dummy value here. If you insert 13 into the target rows, your   * application will never work!   *   * update sales_by_division set sales_month = 13 where sales_month = 2   */  drop table if exists tbl_sales_by_division;  create table tbl_sales_by_division  (sales_year          smallint,     sales_month         tinyint,     sales_division      varchar(35),     sales_amount        decimal(9,2));  insert into tbl_sales_by_division     values (2001, 1, 'Widgets', 4098.56),            (2001, 1, 'Components', 5099.37),            (2001, 1, 'Peripherals', 10358.69),            (2001, 1, 'Hardware', 3045.21),            (2001, 1, 'Connections', 998.04),            (2001, 2, 'Widgets', 4378.21),            (2001, 2, 'Components', 4875.32),            (2001, 2, 'Peripherals', 12875.34),            (2001, 2, 'Hardware', 3007.98),            (2001, 2, 'Connections', 1093.88);  /* The next table is  tbl_sales_vs_quota  . It is the data source   * for Report 3Bar/Line Chart. The reason for the zeros in months 8   * through 12 is that this chart shows year-to-date sales information,   * and those month's sales haven't occurred yet.   */  drop table if exists tbl_sales_vs_quota;  create table tbl_sales_vs_quota  (sales_year         smallint,     sales_month        tinyint,     sales_amount       int,     sales_quota        int);  insert into tbl_sales_vs_quota     values (2001, 1, 304567, 300000),            (2001, 2, 300234, 300900),            (2001, 3, 299345, 300900),            (2001, 4, 310456, 300900),            (2001, 5, 309234, 303000),            (2001, 6, 315098, 320000),            (2001, 7, 303870, 325000),            (2001, 8,      0, 325000),            (2001, 9,      0, 350000),            (2001, 10,     0, 360000),            (2001, 11,     0, 390000),            (2001, 12,     0, 400000);

Lastly, Report 4 ”Scatter Plot/Finding Correlations in Numeric Data will use tbl_revenue in the WCA database. It will also be able to use the login set up in Report 1.

only for RuBoard - do not distribute or recompile