Relational Schema Design and Database Construction

The Auto Sales Tracking application is based on a hypothetical car dealership that repairs used cars and then resells these cars to the public. The sale price of the completed car is based on the original cost of the car plus all the time and repairs. This application tracks all the information necessary: where the car came from, where the parts came from, who the car was sold to, plus all the costs associated with each individual car.

I have used a naming convention here 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. Having a column name that directly refers to one table or another really assists the programmer or end user when working with the SQL language.

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.

An Overview of Tables and Indexes Including Referential Integrity Constraints

The Sales Tracking database consists of 11 relational tables, 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 4.1 shows the major and minor tables of the Sales Tracking application. The central table is the ST_Inventory table. This is 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 the ST_Inventory to provide such useful information as what the car originally cost, total cost of repairing the car, and 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 the ST_Inventory and the 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 pictured here. 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 it 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 (see Figure 4.2) 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 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 pictured in Figure 4.1. There are a couple of 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 are 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 are enforced by the ST_MAIN program that will be 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 contains the information necessary to track parts purchased for the cars in ST_Inventory (see Figure 4.3). There is the price of the part, the date it was purchased, a brief description as well as two foreign keys. The first foreign key, PARTS_Inv_Id, is related to ST_Inventory INV_Id to ensure that all parts acquired are associated with a particular automobile. The other foreign key, PARTS_Vendor_Id, ensures that all parts purchased can be traced back to their origin, tracked in the ST_Vendors table.

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 4.4 maintains the ST_Inventory maintenance fields), or some code that executes each time a record is inserted or updated in these tables.

The ST_INVENTORY table is the main table of this application. This table tracks the vehicles: their initial cost, their sales cost, where they were purchased and to whom sold. The INV_ID is identified as a primary key, so Oracle8i will build an index on this column to ensure that its values are always unique and to provide fast access to the data. There are also two foreign keys on the INV_VENDOR_ID and INV_CUSTOMER_ID to guarantee that the related VENDOR_ID and CUSTOMER_ID exist in the ST_VENDOR table and the ST_CUSTOMER table prior to any INVENTORY activity. The business rule that applies here is that one cannot purchase a vehicle from a vendor that is not in the Sales Tracking application, nor can one sell a vehicle to a customer who is not in the Sales Tracking application.

The ST_INVENTORY table also has three reference tables, used by the ST_INVENTORY application to assist the data entry operator in filling in columns with valid data: INV_TYPE, INV_MAKE, and INV_MODEL. These three tables are used to load ST_INVENTORY columns with data or to verify that valid information is entered.

When vehicles require repairs, the software will need to track associated labor costs and required parts costs to correctly arrive at a cost of each vehicle. This information will help determine a sale price for the vehicle to ensure that there is a profit made on each vehicle. The ST_PARTS table is used to track parts used on the vehicles. The business rule that applies here is that parts must be associated with an individual vehicle, so there is a foreign key constraint linking this table to the ST_INVENTORY table. Another business rule that applies to ST_PARTS is that they must be purchased from a valid vendor in the ST_VENDOR table, so there is also a foreign key constraint linking this table to the ST_VENDOR table.

The other table used to track the total cost of a vehicle is the ST_BILL_TIME table, used to track labor costs associated with each vehicle. There are two business rules that govern this table: that time must be recorded against valid inventory items and that the person doing the work is a valid staff member. There is a foreign key between this table and the inventory table to ensure that valid vehicles are being worked on. The ST_STAFF table contains information about the employee, including a picture, billing rate, hourly rate, and contact information. In our example, the billing rate and hourly rate will be the same. There is a foreign key linking the ST_BILL_ TIME table with the ST_STAFF table to ensure that only valid employees are performing the work on the vehicles.

The ST_STAFF table has two reference type tables: ST_DEPARTMENTS and ST_ JOB_DESCRIPTIONS. The business rule that applies here is that each employee must be associated with a valid department and that each employee be associated with a valid job description. There are foreign key constraints between the ST_STAFF and ST_DEPARTMENTS/ST_JOB_DESCRIPTIONS to ensure that each employee contains correct department and job description.

The ST_STAFF department/job description will be handled with foreign key constraints. This ensures proper data in the ST_STAFF fields. This concept differs from that of the ST_INVENTORY model/make/type where the Oracle Form-based application will perform the integrity check. There are two reasons for the differences: first, to introduce you to a variety of ways of performing similar tasks; and second, because the data in the model/make/type might be subject to ad-hoc entries where the fields of ST_STAFF are not.