Previous | Table of Contents | Next

Page 529

Installment 2—Creating the Database Objects
Figure 20.1 contains the employee system table relationship. The chart indicates the primary and foreign keys to the various tables.

FIG. 20.1
The Employee System
Table Relationship
diagram.


Installment 2—Creating the Database Objects

This installment creates the Oracle7 database objects that will be used in the employee system. This installment also creates the tables and table constraints identified in the previous installment. The design in Figure 20.1 consists of seven tables. Before these objects can be created, however, a tablespace must be created. A tablespace consists of a file(s) that has a predetermined size. It holds the database objects and is the first object the database administrator needs to create. The script to create the employee system is contained in Listing 20.1. The script is executed in SQL *Plus from the database administrator's id.

Listing 20.1 L_20_01.SQL—The Employee System's Create Tablespace Script

SQL> create tablespace emp datafile `emp_sys.tab' size 3 m
 2  default storage (pctincrease 1);

Tablespace created.
SQL>
NOTE
If you are using a trial copy of Personal Oracle7, the default database administrator id is system and password is manager.

Page 530

The next step is to create a user account or id. This must also be done from the data administrator's id. The user account determines the ownership of the object. Objects are created from the user id. Oracle will then subscribe ownership of the object to that account. The script to create the employee system user id is contained in Listing 20.2. This script creates a user account called emp_sys_user with a password of welcome.

Listing 20.2 L_20_02.SQL—Creating the User Id Emp_sys_user for the Employee System

SQL> create user emp_sys_user identified by welcome
 2  default tablespace emp
 3  temporary tablespace temporary_data
 4  quota unlimited on emp;

User created.

SQL>

After the user acccount is created, it must be granted privileges. Without privileges, the account cannot log on to Oracle. The emp_sys_user account is the main account that will be used for the employee system. It has the ability to modify the systems data. The system administrator will likely be the only person outside the DBA who knows the password. This account is given a broader range of privileges than an ordinary user. The granting of privileges to emp_sys_user account must also be done by the DBA. Listing 20.3 contains the script used to grant privileges to the id.

Listing 20.3 L_20_03.SQL—Granting Privileges to the "Emp_sys_user" Id

SQL> grant create session, alter session, create table,
 2  create any table, drop any table, create view, drop any view,
 3  create synonym, drop any synonym, create any index, drop any index,
 4  create public synonym, drop public synonym, create any sequence,
 5  drop any sequence
 6 to emp_sys_user;

Grant succeeded.

SQL>

The system's tables can be created after the emp_sys_user has been granted privileges. These tables should be created by using the new user id. Seven tables need to be created. Because there are constraints between the tables, they must be created in a specific order. The parent table must be created before the children tables, which means that the Departments table must be created first. The storage clause is used to specify the initial size of the file. Listing 20.4 contains the script used to create the Departments table. The Department field is the primary key to the table. The field contains a Primary Key column constraint used to ensure that the field always has a value and the value is unique.

Page 531

Listing 20.4 L_20_04.TXT—Creating the Departments Table

SQL> create table departments
 2  (department char(4) primary key,
 3   department_name varchar(15))
 4  storage (initial 10k next 10k pctincrease 1);

Table created.

SQL>

The next table created is the Employees table (see Listing 20.5). The primary key to this table is the Payroll_Number field. The Department field is a foreign key to the same field on the Departments table. Column constraints will be placed on both of these fields. A Check constraint will be placed on the Gender field. The constraint will ensure that the values in the field will be either M or F. The Last name, First name, Employment Date, Classification, and Classification Date fields must always contain a value that is not a null. A Not Null constraint will be placed on each of these fields. The Default constraint is placed on the Absences and Wages fields. This will ensure that these numeric fields contain a value of 0 rather than a null value. This means the value can be used in calculations without the worry of nulls. The letters fk precede the Department field name to denote that the field is a foreign key. Figure 20.5 illustrates the create table statement for the Employees table.

Listing 20.5 L_20_05.SQL—Creating the Employees table

SQL> create table employees
 2  (payroll_number number primary key, last_name varchar(15) not null,
 3   first_name varchar(15) not null,
 4   absences number(2) default (0), wages number(8) default (0),
 5   street varchar(20), city varchar(15), state char(2), phone varchar(13),
 6   social_security_number char(11), employment_date date not null,
 7   birth_date date, classification varchar(15) not null,
 8   classification_date date not null, class_comments varchar2(20),
 9   gender char(1) check (gender in (`M', `F')),
 10   fk_department char (4) references departments)
 11  storage (initial 100k next 10k pctincrease 1);

Table created.

SQL>

The remainder of the tables can be created once the Employees table is created. Each of these tables contains the Payroll Number field. This field is a foreign key that relates the tables to the Employees table. This field will have a foreign key constraint placed upon it in each of the tables. The tables also have composite primary keys. This means the primary key constraint must be specified as a table constraint. The tables use the same types of constraints as used on the Employees table. They have one additional constraint option. The tables will use the on delete cascade option. When this option is specified, the child records in the table will be deleted when the parent record is deleted in the Employees table.

Previous | Table of Contents | Next