The Sales Tracking Application

I have used a naming convention so that the relational objects associated with this application can be easily identified when looking at any data-dictionary view such as DBA_Tables, or even TAB. Because this is a Sales Tracking application, I chose ST to depict the initial prefix to every table, index, or object (tables, indexes, triggers, and so on) that is part of this application. I also like to use part of the table as a prefix to each column name. This really assists the programmer or end user when working with the SQL language because a column name directly refers to one table or another. You will notice that I have prefixed all the application objects with ST_; all the column attributes have part or all of the table name in them, such as the ST_Inventory table; and all the entities begin with INV_. Your data center may have its own standards. Please consult your database administrator if you need assistance in the naming of application objects and/or programs.

The Database Layout

The Sales Tracking database consists of 11 relational tables (see Figure 19.1), 3 sequence generators, and 5 database triggers. Three major tables that track the inventory (used automobiles in this case) support the application: ST_Inventory, ST_Parts, and ST_Bill_Time. Three minor tables are related to the major tables: ST_Vendor, ST_Customer, and ST_Staff. Finally, there are five reference tables that contain consistent data used to ensure that valid data is being stored in the five major and minor tables as well as give descriptions to this same data when displaying information on a screen or in a report. These reference tables are ST_Departments, ST_Job_Code, ST_Model, ST_Make, and ST_Type.

The Entity Relationship Diagram (ERD) in Figure 19.2 shows the major and minor tables of the Sales Tracking application. The central table is the ST_Inventory table, the central repository for the main business focus; the inventory of the automobiles that have been purchased, are in various stages of repair, are ready for sale, or have been sold.

The two other major tables are the ST_Parts and the ST_Bill_Tie tables. These tables are used in conjunction with ST_Inventory to provide such useful information as what the car originally cost, total cost of repairing the car, as well as the profit/loss of each automobile sold. Notice the many-to-one relationship from ST_Parts to ST_Inventory. This indicates from this picture that there can be one or more parts associated with each car in the ST_Inventory table. A part can be a fender, a tire, or a complete motor. Likewise, with ST_Bill_Time, there can be one or more mechanics working on each car, especially through several stages of repairs. There is the welder, who might fix any physical damage, a mechanic, who might have installed a new motor or transmission, and the painter if the car required painting.

Notice that ST_Inventory and ST_Parts share a table used for reference, the ST_Vendor table. This table contains information about who is supplying the dealership with both cars and parts. A salvage yard, for example, could be supplying repairable cars as well as fenders and motors.

The ST_Inventory table has three supporting reference (look up/editing) tables associated with it that are not illustrated here, but are included in the installation scripts (see appendix B). These tables are ST_Type, or the type of automobile such as a SUV, sedan, and so on; the ST_Make, or name of the car such as Intrepid, Camry, Corolla, and so on; and the ST_Model, 4-door, hatchback, automatic, and so on. Each of these tables will be used by the forms programs to ensure only valid information is entered into the ST_Inventory table. The ST_Customer table records the buyers of automobiles. The one-to-one relationship indicates that there is only one record in the ST_Inventory that is associated with a single record in the ST_Customer table. For simplicity's sake, this application will make the assumption that only one person can purchase a single automobile from this dealership.

The ST_Bill_Time table has a many-to-one relationship to the ST_Inventory table. Several staff members could be involved in the various stages of preparation of a single automobile for final sale. The ST_Bill_Time and the ST_Staff table have a many-to-many relationship in that staff members would be working on more than one automobile and possibly even more than one automobile in a single day.

The ST_Staff table is supported by ST_Departments and ST_Job_ Description. Each staff member is associated with different departments, such as collision repair, mechanic, detailing (cleanup), painting, sales, or management. This information could be useful to see what percentage of an automobile is handled by each type of process required. The ST_Job_ Description is a reference table to the ST_Staff (note the one-to-one relationship) to ensure the correct job code is assigned to each staff member recorded in ST_Staff. The ST_Departments is another reference table to the ST_Staff (note the one-to-one relationship) to ensure the correct department code is assigned to each staff member recorded in ST_Staff.

The ST_Inventory table contains the necessary entities or columns to store a unique identifier for each automobile, in other words, the ST_INV entity. This field is associated with one of the Oracle sequence generators, ST_INV_SEQ. This sequence is used to ensure that a unique number is associated with each automobile, no matter how many people may be entering cars into the ST_Inventory table. This field is also the primary key so that referential integrity constraints can be established, enforcing the relationships between the tables (as shown in Figure 19.3).

There are several foreign keys, or fields that will have relationships to other tables as well. These fields include Inv_Purchase_Vendor_Id and Inv_Sale_Customer_Id. These fields will be respectively related to ST_Vendor and ST_Customer. The referential integrity rule will ensure that there is a valid record in ST_Vendor and ST_Customer before the ST_Inv record will be recorded (or committed in relational terms) to the database. The Inv_Model, Inv_Type, and Inv_Make will be enforced by the ST_MAIN program used to maintain the ST_Inv table. The remainder of the fields are used to store pertinent information that relates to a particular car.

The ST_Parts table (see Figure 19.4) contains the information necessary to track parts purchased for the cars in ST_Inventory. There is the price of the part, the date it was purchased, and a brief description as well as two foreign keys PARTS_INV_ID and PARTS_VENDOR_ID. PARTS_Inv_Id is related to ST_Inventory INV_Id to ensure that all parts acquired are associated with a particular automobile. The PARTS_Vendor_Id is associated with the ST_Vendors table to ensure that all parts purchased can be traced back to their origin.

There are four fields that do not appear in this list. These fields are Inv_Insert_User, Inv_Insert_Date, Inv_Update_User, and Inv_Update_Date. These same fields appear in all the major and minor tables of this application (ST_Parts, ST_Vendor, ST_Bill_Time, and ST_Staff). These four fields track which user inserted the record to the table and which user was the last to update the table. This information could be useful if the wrong information was entered to see who might need additional training on how to use the application.

These fields are automatically maintained by database triggers (Figure 19.5 maintains the proper data in the ST_Inventory maintenance fields: INSERT_USER, INSERT_DATE, and so on) or some code that executes each time a record is inserted or updated in these tables.

The Programs

The Sales Tracking application consists of 9 screen-based programs that add or maintain the data in the 11 database tables. Each of these programs will have an icon that will start the program. ST_Inventory is the main program used to enter and track important information as it relates to a particular vehicle. Figure 19.6 shows the main entry screen. Each of these tabs represents a different part of the process of a vehicle. The final tab (see Figure 19.7) shows the profitability of the vehicle, allowing the sales person to see how much the firm has invested in this particular vehicle.

Figure 19.8 shows the main icons where the Sales Tracking programs can be easily accessed. Clicking the icon buttons will access the most important programs. These programs include ST_TIME_CLOCK, ST_PARTS, ST_INVENTORY, ST_SALES, ST_PROFIT/LOSS, and ST_STAFF_UTILIZATION. These are the programs used to enter and maintain data in the underlying relational tables.

The name implies the table associated with each program—for instance, ST_TIME_CLOCK will add records to the ST_BILL_TIME table. Likewise, ST_ INVENTORY will add and maintain records in the ST_INVENTORY table (refer to Figure 19.6). This program will also make use of the three look-up type tables: ST_TYPE, ST_MAKE, and ST_MODEL. These three tables will appear in this application as a pull-down menu that is easily accessed with the mouse.

ST_PROFIT/LOSS and ST_STAFF_UTILIZATION are screen-based programs that display or report on data from one or more tables. ST_PROFIT/LOSS displays the profit or loss on each vehicle in ST_INVENTORY. This calculation will include information from ST_PARTS and ST_BILL_TIME as well as ST_INVENTORY. As the name implies, ST_PROFIT/LOSS will help management charge appropriately for each automobile. This screen can be consulted prior to a sale or in a sales situation to permit the salesperson to easily see the total investment in any particular car at any time. ST_STAFF_UTILIZATION will display summary information, by week, for all employees or just selected employees. In an auto sales environment, it is important for overall profitability to keep all the mechanics, painters, and body-repair people busy. This program will help management track who has plenty to do and who might need more work.

There will be a series of reports associated with this application as well. Reports are needed to display data from the underlying relational tables in a meaningful manner. Some of these reports will be GUI-based and some will be character-mode based. There is no reason why reports cannot be all one environment or the other. Typically, character-mode printers are much less expensive (inkjet printers) than printers that can handle images, such as laser printers.

Each item on this report menu will also be a separate program or SQL*Plus script. Each program may access individual or multiple Sales Tracking relational tables. Each SQL*Plus script will be handled with a batch file on the Windows NT environment and by a Unix shell script in a Unix environment. This batch file or shell script is useful to run the actual SQL*Plus syntax as well as print and/or delete the output from the script.

There are also icons for the two reports (Inventory Status Report, see Figure 19.9, and Profit/Loss Detail, see Figure 19.10), icons for Vendor and Customer entry and maintenance, and icons for Staff, Departments, Jobs, and Type/Make/Model entry and maintenance. Each of these entry programs relates directly to one of the relational table objects, as the name would imply. Notice that the icon picture depicts the type of program that is being assigned to the icon: the Oracle Forms-based icon is used for the Oracle Developer forms-based programs, the Oracle Reports for the Oracle Developer reports-based programs, and the SQL*Plus icon for the SQL*Plus-based reports.

Reports are needed to display data from the underlying relational tables in a meaningful manner. One of these reports will be GUI-based and the other will be character-mode based. There is no reason why they cannot be all one environment or the other, but for learning purposes, we will create both kinds. Figure 19.9 shows the Oracle Developer-based report and Figure 19.10 shows the character-mode SQL*Plus-based report.

This book also built a Web site based on this application. Figure 19.11 shows the main Web page used to purchase one of these vehicles over the Web and Figure 19.12 shows a SQL*Plus Web-based report shown in Figure 19.10.