Database Storage & Design Appendix B Appendix Getting Started With Oracle Database Will First Import The Table Then

Appendix B – Getting Started with Oracle Database XE

We will first import the table then create foreign keys in the new table linking it to COMPUTER

and to EMPLOYEE. We will use the following column characteristics, which reflect Access data

types and are copied from Figure A-65:

To Import the COMPUTER_ASSIGNMENT table:

1. In Oracle SQL Developer, expand the WP Database.

Appendix B – Getting Started with Oracle Database XE

4.

4. The Data Preview dialog box is redisplayed, this time with information from the Excel

workbook. Select the COMPUTER_ASSIGNMENT worksheet from the Worksheet drop-down

list as shown below. Also, make sure the Header checkbox is checked and that the Excel

2003+ (xlsx) format is selected from the Format drop-down list.

5. Click the Next button as shown above. The Data Import Wizard – Step 2 of 5 (Import

Method) dialog box is displayed (and mislabeled as Step 2 of 4). Type in the Table Name

COMPUTER_ASSIGNMENT and leave the rest of the settings as they are:

The Import Data File

dialog box

Data Import

Wizard – Step 1 of

5 dialog box

The Next button

Select the

COMPUTER_ASSI

GNMENT

worksheet from this

drop-down list

Appendix B – Getting Started with Oracle Database XE

6. Click the Next button as shown above. The Data Import Wizard – Step 3 of 5 (Choose

Columns) dialog box is displayed as shown below. This step allows us to choose which

spreadsheet columns to import. Note that all are currently selected, and that is what we

want, so no changes are made.

Data Import Wizard

– Step 2 of 5 dialog

box (note the mislabel

“2 of 4”)

Type in the table

name

COMPUTER_ASSIG

NMENT

The Next button

Data Import Wizard

– Step 3 of 5 dialog

box

All columns in the

worksheet are

selected by default

The Next button

Use these buttons to

modify the list of

selected columns

Use these buttons to

reorder the selected

columns

Appendix B – Getting Started with Oracle Database XE

7. Click the Next button as shown above to display the Data Import Wizard – Step 4 of 5

(Column Definition) dialog box as shown below. Here we can select column characteristics

8. Complete the specifications for the EmployeeNumber and DateAssigned columns as follows:

EmployeeNumber, like SerialNumber, should be type INTEGER and not nullable.

DateAssigned should be imported as VARCHAR2(11). If desired, it can be transformed after

9. The Data Import Wizard – Step 5 of 5 (Finish) dialog box is displayed (not shown here). This

window allows you to examine the choices made during the import dialogs and to save the

settings to a file for future use (perhaps with another, similar table or spreadsheet). Click

the Finish button.

Appendix B – Getting Started with Oracle Database XE

11. In the WP SQL Worksheet window, type and run the following SQL query to verify the

proper importing of the COMPUTER_ASSIGNMENT data:

12. Next we need to create the foreign keys using SQL ALTER TABLE statements. Note that we

link to both the EMPLOYEE table using EmployeeNumber and to the COMPUTER table using

SerialNumber. What referential integrity constraints should we use? For EMPLOYEE,

EmployeeNumber is a surrogate key and is never updated. Further, WP never drops

employee records because of record keeping requirements. Therefore, we will never

cascade updates or deletes for this primary key. FOR COMPUTER, SerialNumber never

changes. However, when we remove a computer from the WP computer inventory, we do

not need to keep historical records of having had that computer. Therefore, while we do not

cascade updates, we will cascade deletions for this primary key. The SQL for this is covered

in Appendix E in more detail:

13. The one remaining task for the COMPUTER_ASSIGNMENT table is to set the primary key.

The ALTER TABLE syntax for accomplishing this will be covered in Appendix E, but here is the

solution:

14. We can verify correct importing by running an SQL query, whose results are shown below:

Appendix B – Getting Started with Oracle Database XE

B.30 Create a Microsoft Access 2016 database named WPIS_CO.accdb where CO stands

for “computer–only.” This database will be an application for the Oracle Database XE WP

database which will allow users to read, query, and alter the data in the WP database

COMPUTER table but will provide no access to other WP database tables. Once you

have created the database:

1. If you have not completed exercise B.28, do so now.

2. Set the WPIS_CO.accdb database to use SQL Server Compatible Syntax

(ANSI 92).

Appendix B – Getting Started with Oracle Database XE

3. Link the WPIS_CO.accdb database to the Oracle Database XE WP database

using ODBC. When you create your File Data Source DSN, name it WPCO, and

use the same WP_USER account for Oracle Database XE authentication.

Select the This

database radio

button

Appendix B – Getting Started with Oracle Database XE

4. Click the OK button.

Select the Link to

the data source

by creating a

linked table radio

button

Appendix B – Getting Started with Oracle Database XE

6. Click the New button to display the Create New Data Source dialog box.

7. In the Create New Data Source dialog box, scroll down through the list of drivers

until you can see the driver named Oracle in XE. Click this driver name to select

it.

8. Click the Next button.

The Next button

Type in the name WP_CO

10. Click the Next button.

The Create New Data

Source dialog box

The Next button

Select the Oracle in XE

driver

Appendix B – Getting Started with Oracle Database XE

11. The next page of the Create New Data Source dialog box provides a summary of

the settings that will be used to create the new DSN.

10. Click the Finish button.

11. The Oracle ODBC Driver Connect dialog box is displayed, as shown below. Enter

the Service Name, User Name, and Password as shown in the figure (the User

Name and Password were created earlier in this appendix). Click the OK button.

12. The WP DSN file data source is created and displayed in the Select Data Source

dialog box as shown on the next page. Now that the DSN is completed, we can

finish linking the Microsoft Access 2016 database to the Oracle Database XE

database.

The Finish button

The Oracle ODBC Driver

Connect Dialog Box

Service Name for the local

machine

User Name created earlier

Password created earlier:

WP_USER+password

Appendix B – Getting Started with Oracle Database XE

13. In the Select Data Source dialog box, click the OK button. The Oracle ODBC

4. Import the COMPUTER table. You may be asked by Access to specify a primary

key; if so, use SERIALNUMBER.

1. The Link Tables dialog box is displayed, as shown below. Scroll down until you

find the WP_USER.COMPUTER table. To select this table, click on it.

The Select Data Source

Dialog Box

The WP_CO.dsn DSN file

The OK button

The Link Tables dialog box

The OK button

The selected table

Appendix B – Getting Started with Oracle Database XE

2. Click the OK button. The ODBC links between the WP.accdb Microsoft Access

2016 database and the WP database in Oracle Database XE are completed, as

shown below.

5. Create a form to show all the data in the COMPUTER table named WP

Computer Form.

The linked Oracle Database

XE table

Appendix B – Getting Started with Oracle Database XE

6. Create a report to show all the data in the COMPUTER table named WP

Computer Report.

This is done using Microsoft Access 2016 techniques as discussed in the Chapter 4

section of “The Access Workbench.” An image of the report is shown below.