Using Base Types

Base types are created for use inside other types. They are well-planned types that will make future development easier. Some examples of base types include the following:

•  First Name

•  Last Name

•  Age

•  Marital Status

•  Social Security Number

•  Zip code

•  Address

•  Phone number

•  Customer id

•  Sales id

If a designer wanted to create a customer table, he or she would look at the list of base types and use those that are appropriate. For example, a designer creating a customer table would create it with the following CREATE TABLE statement:

CREATE TABLE customer(

cust_first_name first_name_type,

cust_last_name last_name_type,

cust_address address_type.

cust_hair_color varchar(15)

);

The designer has used three user-defined base types that were specified in the database. This ensures that a well-thought-out standard has been used inside the application. If the designer had decided to create new definitions for these columns, it is likely that they would not have been able to interact easily with other applications. Listing 15.9 shows table definitions created for two different applications.

Listing 15.9. Table definitions for different applications.

CREATE TABLE customer (

cust_first_name varchar(20),

cust_last_name varchar(20),

cust_street varchar(20),

cust_city varchar(20),

cust_state char(2),

cust_zip number(5),

cust_hair_color varchar(15)

);



CREATE TABLE employee (

emp_first_name varchar(25),

emp_last_name varchar(25),

emp_street varchar(255),

emp_city varchar(30),

emp_state varchar(15),

emp_country varchar(30),

emp_other_addr_info varchar(255)

); 

Notice that the columns to hold first_name and last_name are different sizes. In most cases, the designer will simply guess at the correct size. These guesses may be appropriate for each specific application, but make it difficult to merge this data at another time. The designers also chose different datatypes to hold address information. One decided that the state field will hold the two-character abbreviation for each state; the other decided to put the full state name in the field. Finally, one added a field to hold the country information as well as a miscellaneous field for other address data. Both might have designed a better application if base object types had been designed for them. The base types might be defined as follows.

CREATE TYPE first_name_base_type AS OBJECT(

first_name varchar2(50)

);



CREATE TYPE last_name_base_type AS OBJECT(

last_name varchar2(50)

);



CREATE TYPE address_base_type AS OBJECT(

street varchar2(100),

city varchar2(100),

state char(2),

country varchar2(100),

misc_mail_info varchar2(2000),

misc_non_mail_info varchar2(2000)

);

The first two types consist of only one standard datatype. The address_base_type contains several standard datatypes. Hopefully, it holds all the information that the company would like to record about addresses. The misc_mail_info column is information that will be printed on mailings sent to this address such as “Attn: Robin McRae, 10^th floor, office 35.” The misc_non_mail_info will not be put on mailing labels, but will show up on a clerk’s screen. An example is “Mr. Smith has told us that he will be moving in the next six months. Ask him for his new address when he calls back!”

Generally, implementing base types will promote better application development. Data will be formatted in objects that can easily migrate from one application to the next.

Object Views

The Oracle8 database is an object-relational database management system (ORDBMS). An ORDBMS allows the designer to use object-oriented techniques, relational techniques, or a combination of both. The designer has the flexibility to use each technology where it is best fitted. An ORDBMS also allows a slow migration from a relational to object-oriented design. One of the reasons Oracle has provided object views is to help this conversion process.

Object views make relational tables appear to be objects.

There are four steps to creating a object view.

1.  Determine the query the view will represent.

2.  Determine the datatypes the query will return.

3.  Create a type that has the same structures as the query.

4.  Create the view based on the type.

First, the user determines the columns from each table that should be included in the view. In this case, the view should display the first and last names from the patient table in a hospital database.

Next, the user determines the datatypes of those fields.

SQL> describe patient

patient_id number,

first_name varchar2(30),

last_name varchar2(40),

and many other columns...

Then the user creates a type that contains datatypes for each column to be returned in the view.

SQL> CREATE TYPE patient_name_type AS OBJECT (

patient_id number,    —This field is needed although the user did

not ask

 for it in the view.

—Notice that the field names may be different from the field names

in the table,

—however the data types are the same

f_name varchar2(30),

l_name varchar2(40)

);

Finally, the user creates the view.

SQL> CREATE VIEW patient_names OF patient_name_type

WITH OBJECT OID (patient_id) AS

SELECT patient_id, first_name, last_name FROM patient;

The employee id field is used to uniquely identify the rows returned from the relational table. This field will be used instead of a true Object Identifier because the objects are not stored in the database as objects. They are dynamically created from the relational tables when they are needed.

Once the view has been created, the data can be treated as normal objects.