Building the Sample Application Database
Chapter 1, “Introducing Oracle9i,” discussed the Oracle9i architecture—the relationship of physical computer files being created and assigned to Oracle9i tablespaces. Chapter 2, “Fundamentals of the SQL Language,” illustrated how to create these tablespaces and assign a computer file to them.
The tablespaces of an Oracle database are much like the folders or directories found on a PC in that they are storage areas for information. These tablespaces, folders, and directories are designed to help locate information or to locate information on different parts of the computer. For example, the 'bin' folder on a computer is typically used to store programs. Folders or directories such as data would be for information files. The Oracle tablespace is similar in that different database objects such as tables and indexes can be assigned to various tablespaces for both convenience (allows the users and administrators to relate a tablespace name easily with a particular application or parts of an application) and performance.
Tablespace Layout
Chapter 13, “Oracle9i Indexing Options,” utilizes the tablespace arrangement built in this chapter to adequately separate the Sales Tracking database objects by their disk-related activity. Figure 4.5 shows all the tablespaces assigned to the ORCL Oracle9i database. Notice all the tablespaces that begin with an ST_ belong to the Sales Tracking application and were created with the script in Listing 4.1.
Figure 4.5. Tablespaces assigned to Oracle9i Database ORCL.
NOTE
Each tablespace has its own computer file or files, and these files are not shared by other tablespaces. This method of creating files on the computer system is a great way to physically separate database objects. On larger computer systems with many disk drives, these tablespace files would be created on separate physical disk drives to help with data retrieval performance. The author's single disk drive Windows NT 4.0 system will still create multiple tablespaces. This technique would greatly aid the administrator who had to move this application from a smaller computer to a larger one. The database administrator would only have to adjust the filenames on the DATAFILE lines in Listing 4.1 to accommodate most any computer system.
Listing 4.1. Install Sales Tracking Database
rem
rem Sales Tracking Application Oracle8i Initial Database Setup
rem Oracle9i By Example
rem by Dan Hotka
rem Que Publications May 2001
rem All Rights Reserved
rem
spool INSTALL_sales_tracking_database.log
DROP TABLESPACE st_data01 INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE st_data02 INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE st_index01 INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE st_refdata01 INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE st_iot_overflow01 INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE st_lob01 INCLUDING CONTENTS CASCADE CONSTRAINTS;
CREATE TABLESPACE st_data01
DATAFILE 'd:OracleOradataORCLst_data01.dbf' SIZE 10M REUSE
DEFAULT STORAGE (INITIAL 10K
NEXT 10K
MINEXTENTS 5
MAXEXTENTS 100
)
ONLINE;
CREATE TABLESPACE st_data02
DATAFILE 'd:OracleOradataORCLst_data02.dbf' SIZE 10M REUSE
DEFAULT STORAGE (INITIAL 5K
NEXT 5K
MINEXTENTS 5
MAXEXTENTS 100
)
ONLINE;
CREATE TABLESPACE st_refdata01
DATAFILE 'd:OracleOradataORCLst_refdata01.dbf' SIZE 1M REUSE
DEFAULT STORAGE (INITIAL 1K
NEXT 1K
MINEXTENTS 1
MAXEXTENTS 100
)
ONLINE;
CREATE TABLESPACE st_index01
DATAFILE 'd:OracleOradataORCLst_index01.dbf' SIZE 5M REUSE
DEFAULT STORAGE (INITIAL 5K
NEXT 5K
MINEXTENTS 5
MAXEXTENTS 100
)
ONLINE;
CREATE TABLESPACE st_iot_overflow01
DATAFILE 'd:OracleOradataORCLst_iot_overflow01.dbf' SIZE 10M REUSE
DEFAULT STORAGE (INITIAL 5K
NEXT 5K
MINEXTENTS 5
MAXEXTENTS 100
)
ONLINE;
CREATE TABLESPACE st_lob01
DATAFILE 'd:OracleOradataORCLst_lob01.dbf' SIZE 10M REUSE
DEFAULT STORAGE (INITIAL 10K
NEXT 10K
MINEXTENTS 1
MAXEXTENTS 100
)
ONLINE;
CREATE USER sales_tracking
IDENTIFIED BY sales_tracking
DEFAULT TABLESPACE st_data01
TEMPORARY TABLESPACE temp;
GRANT CONNECT, DBA TO sales_tracking;
spool off
exit
|
Listing 4.1 is designed to be run from SQL*Plus, and the SYSTEM password must be used. The author's Windows NT system has one physical hard drive with four logical partitions: C:, D:, E:, and G:. The Oracle9i ORCL database is installed on the D. Notice the file path in the DATAFILE lines in Listing 4.1 corresponds to the directory path of the Oracle8i ORCL installation on the D: partition.
WARNING
Make sure to adjust the operating-system directory path to that of your computer prior to running this script.
WARNING
Notice the DROP TABLESPACE commands at the beginning of Listing 4.1. This script should only be used for initial installation on a computer.
Each tablespace has its own assigned default storage parameters. These parameters will become the default for any object being created in this tablespace that does not have its own storage clause. It is more efficient for Oracle8i if all the extents (or units of storage) are the same size. This will be discussed more in detail in Chapter 7, “Using Advanced SQL Techniques and SQL*Plus Reporting Features.”
This script also creates the Sales Tracking DBA account. All the objects will be created by a single user: sales_tracking. This greatly aids administration, backup, and recovery.
Figure 4.6 shows the newly created tablespaces.
Figure 4.6. Oracle9i ORCL tablespaces.
Creating the Database (Tables, Indexes, and Constraints)
The Sales Tracking database objects have many relationships. Many times, these relationships or constraints cannot be created until all the objects or tables have first been created. Oracle9i would not allow for a constraint or relationship to be created on an object that did not exist. Listing 4.2 is only a partial listing of the INSTALL_sales_tracking_database_objects.sql; Appendix C, “Web Sites and Product Codes,” contains the whole listing. This script is also intended to be run only once per computer system. Listing 4.2 begins with creating a log file to capture the status of each drop and create statement of the script. Notice the DROP commands to clean up any database objects and prevent Object already exists errors in the event that this script has to be run more than once on a particular computer system.
NOTE
The SQL*Plus spool command in Listing 4.2 shows a way to capture all the competed and error messages that might have been displayed. These longer scripts more than fill a computer screen. Using a log file to capture all the messages is not only a good idea but a necessity to see if there were any problems, and of so, what the problem was.
Notice the CREATE TABLE st_inventory statement. The primary key constraint is defined inline and the index that will be created is also assigned to its own tablespace. Notice that all the objects in this script have their own storage parameters and tablespace assignments. This st_inventory object contains two LOBs (large objects such as pictures, video, sound files, and so on), both being pictures, and both will be stored in the tablespace ST_LOB01 as noted by the syntax in the storage clause. The st_inv_seq sequence is then created. This will be used by the ST_Inventory form to always create a unique number for the primary key st_inv_id. The next object to be created is the st_vendor table. Notice the out-of-line constraint in the ALTER TABLE command near the end of this listing. The foreign key constraint (that will ensure that any vendor_id being inserted in the st_inventory table first exists in the st_vendor table) could not be created until after the st_vendor table was created. Review the entire listing in Appendix C. Figure 4.10 illustrates the sqlplus syntax needed to run this script.
NOTE
An inline constraint is one that is defined where the field that it applies to is defined. An out-of-line constraint is one that is added with separate syntax at a later time. In Listing 4.2, the INV_ID column in the ST_INVENTORY table has an inline constraint. The primary key constraint is defined at the same time as the INV_ID column. The last command in Listing 4.2 is an out-of-line constraint where the ALTER TABLE syntax is adding a foreign key constraint.
NOTE
There is no reason that these objects could not be created in SQL*Plus as shown in Chapter 2 or by tools such as SQL*Navigator. The author finds it convenient to use INSTALL_xxx.sql files in this method to ensure that all objects are initially created in the correct order and without error.
Listing 4.2. Install Sales Tracking Database Objects (Partial Listing)
rem
rem Sales Tracking Application Oracle9i Objects
rem Oracle9i By Example
rem by Dan Hotka
rem Que Publications May 2001
rem All Rights Reserved
rem
spool INSTALL_sales_tracking_objects.log
DROP TABLE st_inventory CASCADE CONSTRAINTS;
DROP SEQUENCE st_inv_seq;
DROP OBJECT address_field;
DROP TABLE st_parts CASCADE CONSTRAINTS;
DROP TABLE st_inv_type CASCADE CONSTRAINTS;
.
.
.
DROP TRIGGER st_bill_time_trg;
CREATE TABLE st_inventory
(inv_id NUMBER(6) CONSTRAINT pk_inv_id PRIMARY KEY
USING INDEX TABLESPACE st_index01,
inv_type VARCHAR2(10),
inv_make VARCHAR2(10),
inv model VARCHAR2(10),
inv_color VARCHAR(10),
inv_year NUMBER(4),
inv_purchase_vendor_id NUMBER(6),
inv_purchase_amt NUMBER(9,2) NOT NULL,
inv_purchase_date DATE NOT NULL,
inv_purchase_photo BLOB,
inv_sale_customer_id NUMBER(6),
inv_sale_amt NUMBER(9,2),
inv_sale_date DATE,
inv_sale_photo BLOB,
inv_description VARCHAR2(20),
inv_insert_user VARCHAR2(20),
inv_insert_date DATE,
inv_update_user VARCHAR2(20),
inv_update_date DATE)
TABLESPACE st_data01
PCTFREE 30
PCTUSED 50
STORAGE (INITIAL 10K
NEXT 10K
MINEXTENTS 5
MAXEXTENTS 10)
LOB (inv_purchase_photo, inv_sale_photo) STORE AS
(TABLESPACE st_lob01
STORAGE (INITIAL 10K
NEXT 10K
MINEXTENTS 5
MAXEXTENTS 100)
CHUNK 500
NOCACHE
NOLOGGING);
CREATE SEQUENCE st_inv_seq
START WITH 1
INCREMENT BY 1
CACHE 10;
.
.
.
CREATE TABLE st_vendor
(vendor_id NUMBER(6) PRIMARY KEY,
vendor_name VARCHAR2(30) NOT NULL,
.
.
.
.
ALTER TABLE st_inventory ADD CONSTRAINT fk_inv_purchase_vendor_id FOREIGN KEY (inv_purchase_vendor_id)
REFERENCES sales_tracking.st_vendor(vendor_id);
.
.
.
.
/
spool off
exit
|
Creating Database Triggers
Notice the final four fields of the ST_INVENTORY table (inv_insert_user, inv_insert_date, inv_update_user, and inv_update_date). These are fields that track who inserted the record into the table and who made the last change to the table. SYSDATE is used for each of the date fields. These fields are important for applications that contain data critical to the needs of the business. This is a method of tracking who did what and when to the database. If erroneous data appears in the tables, this gives the database administrator an idea of where to begin to look for problems in programs or with any training issues with end users.
A database trigger is a piece of PL/SQL code that is run by the Oracle9i database based on certain types of DML activity on any table the trigger is created for. Listing 4.3 illustrates a database trigger that will execute before an insert or update to the st_inventory table, assigning the correct values to the four audit fields. A database trigger is useful in this instance to ensure that these fields are maintained, no matter what program was used to perform the DML (such as Oracle Forms, SQL*Plus, or a third-party program).
Notice the new. and old. prefixes on the fields. In database triggers, the prior value and the new value of any field are accessible with these two prefixes. The use of these prefixes to reset any of the values ensures that these fields accurately reflect the activity of the users.
Listing 4.3. Sales Tracking Database Triggers (Partial Listing)
rem
rem Sales Tracking Application Oracle8i Objects
rem Oracle9i By Example
rem by Dan Hotka
rem Que Publications May 2001
rem All Rights Reserved
rem
spool INSTALL_sales_tracking_objects.log
.
.
.
CREATE TABLE st_inventory
(.
.
.
.
.
CREATE TRIGGER st_inventory_trg BEFORE INSERT OR UPDATE ON st_inventory
FOR EACH ROW
BEGIN
IF :old.inv_insert_user IS NULL THEN
:new.inv_insert_user := USER;
:new.inv_insert_date := SYSDATE;
:new.inv_update_user := NULL;
:new.inv_update_date := NULL;
ELSE
:new.inv_insert_user := :old.inv_insert_user;
:new.inv_insert_date := :old.inv_insert_date;
:new.inv_update_user := USER;
:new.inv_update_date := SYSDATE;
END IF;
END;
/
.
.
.
/
spool off
exit
|