Use the links below to download the completed and normalized Oracle Northwind script and the image files.

• Click here to download the normalized Oracle Northwind relational database script, including table data.
• Click here to download the images for the Categories and Employees table.

How the fields in MS Access Northwind database are converted to Oracle Northwind database.

Here is a brief overview about the conversion between Access table properties and data types in Oracle tables.

1. Autonumber => Oracle sequence

In Access Northwind database, the primary key in a table is normally is an Autonumber field. In Oracle, autonumber field is called sequence.

A sequence is an Oracle object that is used to automatically generate a series of unique numbers such as the autonumber used for Shipper_ID in Access Northwind database. A sequence is normally used as a primary key in Oracle.

Here is the script used to define the sequence for Shippers table.

CREATE SEQUENCE SEQ_NW_SHIPPERS
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 4
    INCREMENT BY 1
    NOCYCLE
    NOCACHE
    NOORDER;

If we omit the MAXVALUE option, the sequence will automatically default to 999999999999999999999999999.

When creating the sequence, we can specify a cache value. For example, if we create the sequence with a cache value of 100, it enables the database to store 100 values in memory for faster access. But a system failure can cause these values to be lost and therefore results in a gap in the sequence values when the next insert statement is executed. To avoid this problem, we use NOCACHE.

Note that ALTER SEQUENCE command can be executed to recover the lost sequence values.

We can drop a sequence with DROP SEQUENCE command.

DROP SEQUENCE SEQ_NW_SHIPPERS;

To easily get the next sequence value, use the following command. This is particular useful when coding a client application and we need to know what next sequence value will be so that it can be used as a foreign key value for a child table, eg. when inserting data to parent and child table in one transaction.

SELECT SEQ_NW_SHIPPERS.nextval FROM dual;

2. Text => VARCHAR2

The Text field in Access Northwind database is defined as VARCHAR2 in Oracle with the same length but in bytes.

3. Date/Time => Date

Oracle DATE data type stores information about Year, Month, Day, Hour, Minute and Second. This is sufficient to use for Oracle Northwind database. Oracle TIMESTAMP data type is overkill because it uses more bytes (accurate up to a fraction of milliseconds) than the DATE type.

4. Currency => NUMBER(10,2)

There is no CURRENCY data type in Oracle. We need to use the NUMBER data type in Oracle and specify the precision and scale to create a decimal number. The syntax for this data type is NUMBER(precision, scale), where precision is the total number of digits (including the digits after the decimal point). Scale is the number of digits to the right of the decimal point.

For example, NUMBER(10,2) states that the number has 8 digits to the left of the decimal point and 2 to the right. So it can hold a number up to 99999999.99

5. Number => NUMBER(9)

NUMBER(9) omits the scale part, so the scale is default to zero and as such it stores whole number. It can accept a number with a whole number part of 7 digits, and 2 for the decimal part (after the dot). When data is inserted to a column data type NUMBER(9), it's wrapped to a whole number. So NUMBER(9) denotes that we can store a number like 1234567.99 in the column as 1234568.

6. Memo => VARCHAR2(2000 BYTE)

Memo in Access is a long text data type which can be defined as VARCHAR2(2000 BYTE) or more bytes in Oracle.

7. OLE Object => VARCHAR2(255 BYTE)

OLE Object in Access can be used to store images such as Employee photos. We don't adopt this approach in our Oracle Northwind database. We'll simply make it a VARCHAR2 type for the Photo column and store the image filename such as seafood.gif. The actual image file will be stored in file system, so in our client application that uses the Oracle Northwind database, we need to retrieve the image from the file system.

8. Yes/No => CHAR(1 BYTE) in ('Y','N')

Yes/No data type in Access is converted to a CHAR(1) data type in Oracle. We'll then define a check constraint in the table definition to restrict values in this column to be 'Y' or 'N' only.

CONSTRAINT CK_PRODUCTS_DISCONTINUED CHECK (Discontinued in ('Y','N'))

9. Hyperlink => VARCHAR2(500 BYTE)

Hyperlink data type in Access is converted to a VARCHAR2 data type in Oracle Northwind database. We will store the website URL in this column.

How to validate Employees birthdate

In Access Northwind database, the data validation rule for the Birthdate column in Employees table is <Date(), which states that birth date can't be in the future. In Oracle, it's invalid to define a constraint like birthdate<sysdate or we'll get an Oracle error message "ORA-02436: date or system variable wrongly specified in CHECK constraint", so we implemented a trigger for BirthDate column to achieve the same effect.

Each time we add or update a birthdate value, the trigger checks the value and if it is a date in the future, the error message from the trigger will be sent to the caller application.

-- Create a trigger to validate employee birthdate.
CREATE OR REPLACE TRIGGER TRG_EMP_BIRTHDATE
BEFORE INSERT OR UPDATE
OF BIRTHDATE
ON EMPLOYEES 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
    if :New.birthdate > trunc(sysdate) then
          RAISE_APPLICATION_ERROR (num => -20000, msg => 'Birthdate cannot be in the future');
    end if;
end;

So far we have explained most of the techniques we used to convert MS Access Northwind to Oracle Northwind. Part 2 of this article series shows the actual scripts for creating tables, sequences, indexes, constraints, etc.


Copyright© GeeksEngine.com