Database Storage & Design Appendix B Database Concepts Edition David Kroenke David Auer Scott Vandenberg Robert Yoder Instructors

Unlock access to all the studying documents.

View Full Document

Database Concepts

8th Edition

David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder

Instructor’s Manual

Prepared by Scott L. Vandenberg

Appendix B

Getting Started with Oracle Database XE

in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior

written permission of the publisher. Printed in the United States of America.

Instructor’s Manual to accompany:

Database Concepts (8th Edition)

David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder

Appendix B – Getting Started with Oracle Database XE

 APPENDIX OBJECTIVES

• Learn how to install Oracle Database XE

• Learn how to install Oracle SQL Developer

• Learn how to create a database in Oracle Database XE

• Learn how to submit SQL commands to create table structures

• Learn how to submit SQL commands to insert database data

• Learn how to submit SQL commands to query a database

• Learn how to install the Oracle ODBC Client software

• Learn how to use Microsoft Access as a front end to an Oracle Database XE database

• Learn how to import Microsoft Excel worksheet data into a database

 APPENDIX ERRATA

There are no known errors at this time. Any errors that are discovered in the future will

be reported and corrected in the online DBC e08 Errata document, which will be

available at http://www.pearsonhighered.com/kroenke.

 THE ACCESS WORKBENCH

Solutions to the Access Workbench exercises may be found in Solutions to all Sections:

The Access Workbench, which is a separate document within the Instructor’s Manual.

There is no section of The Access Workbench associated with this appendix.

 NOTES ON MICROSOFT WINDOWS 10

This book uses the Microsoft Windows 10 operating system as the basis for screenshots

and step-by-step instructions. However, with Windows 10, Microsoft has introduced a

continuous update system that has already resulted in some fundamental differences in

how different versions of Windows 10 look and operate.

For example, in the original version of Microsoft Windows 10, clicking the Windows

Start button (or pressing the Windows key on the keyboard) displayed the menu shown

in Figure 1. In this menu, we need to click the All apps button in order to see the All

apps menu shown in Figure 2.

Appendix B – Getting Started with Oracle Database XE

Figure 1 – Windows 10 Main Menu

The All apps button

The File Explorer button

The File Explorer icon

The All apps menu

Appendix B – Getting Started with Oracle Database XE

Figure 2 – Windows 10 All Apps Menu

Microsoft then released the Windows 10 Anniversary Update (Feature update to

Windows 10, version 1607) (see the blog discussion at

https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10–

anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the

Anniversary Update was a major change to the menu system. Now, as shown in

Figure 3, the All apps menu is immediately available when the Start button is used (or

the keyboard Windows key is pressed).

Therefore, note that the step by step instructions in this book may need to be altered for

your use depending upon which version of Microsoft Windows 10 you or your students

are using!

We recommend that you update Windows 10 to the Windows 10 Anniversary Update

(Feature update to Windows 10, version 1607), and make sure it is patched with all

updates to that version (at a minimum patched to Windows 10 Version 1607 update for

August 23, 2016 (KB3176936), and the Windows 10 Version 1607 cumulative update for

September 29, 2016 (KB3194496). We also recommend using the 32-bit version of

Microsoft Office. This insures that all the examples discussed in this book will function

properly.

The All apps menu

The File Explorer button

The File Explorer icon

Appendix B – Getting Started with Oracle Database XE

 TEACHING SUGGESTIONS

• For individual student personal use on their own computer, we recommend (and use

in this book) Oracle Database Express Edition 11g Release 2. Oracle Database 12c

can be used instead, but we recommend that only if the student does not have to

install or configure the software.

• When you are using Oracle Database XE, the best text editor to use is the text editor

built into Oracle SQL Developer. Take some time to show your students how to use

it.

• It may be helpful to head off some confusion and frustration by listing some of the

differences between SQL Server and Oracle early on, specifically:

• The DATE format (default, TO_DATE, etc.)

• Use of sequences to achieve autonumber fields

• Lack of “ON UPDATE CASCADE” option

• Disallowing of “AS” keyword in range variable declarations

• Padding of CHAR fields with blanks and its effect on LIKE semantics (trailing

blanks are not ignored)

• Make sure that your students work through Appendix B in conjunction with other

material presented in the text by working through Appendix B in the following

sequence:

• Before starting Chapter 3 on SQL, install Oracle Database XE and Oracle SQL

Developer by working through this appendix up to and including “Installing Oracle

SQL Developer.”

• When studying the Chapter 3 sections on how to create and populate database

tables, work up to and including “How Do I Use SQL Statements to Insert

Database Data?” Both Chapter 3 and this appendix use the same WP database,

and this work will show you how to create your own copy of the WP database.

• When studying the Chapter 3 sections on how to use SQL Data Manipulation

Language (DML) and SQL Data Definition Language (DDL), work through the

section named “How Do I Work with SQL Queries in Oracle Database XE?” Both

Chapter 3 and this appendix use the same WP database, and you can run the

SQL Statements shown in Chapter 3 yourself, and see the results.

• When studying the Appendix E material on SQL Views, you can run the SQL

Statements shown in Appendix E yourself and see the results.

• When studying the Appendix E material on SQL Persistent Stored Modules

(SQL/PSM), you can run the Oracle versions of the SQL Statements, which can

be found in the IRC Oracle files for Appendix E, yourself and see the results.

• Work through the section “How Do I Import Microsoft Excel Data into an Oracle

Database XE Table?” in this appendix to understand how to import Microsoft

Excel data into a database table.

Appendix B – Getting Started with Oracle Database XE

• Work through the section “How Do I Create an ODBC Connection from Microsoft

Access 2016 to an Oracle Database XE Database?” in this appendix to

understand how to use Microsoft Access as a development environment for an

Oracle Database XE database.

 ANSWERS TO REVIEW QUESTIONS

B.1 What is Oracle Database XE?

Oracle Database Express Edition 11g Release 2 (Oracle Database XE) is the latest version of the

B.2 What is the primary advantage of using Oracle Database XE instead of Microsoft

Access?

B.3 What are the two Oracle programs that are recommended as a necessary set of Oracle

Database XE software products? In what order should you install these products?

B.4 How do you install Oracle Database XE?

Install Oracle Database XE as follows (this is an outline – we will not repeat the full set of

instructions contained in the appendix):

Appendix B – Getting Started with Oracle Database XE

4. Run the setup.exe program and follow the instructions in the installation wizard, as

shown in the text of the appendix on pages B-8 through B-13.

B.5 What is the purpose of the Oracle Database XE Web utility?

B.6 How do you install Oracle SQL Developer?

Install Oracle SQL Developer as follows (this is an outline – we will not repeat the full set of

instructions contained in the appendix on pages B-14 and B-15):

B.7 What is the purpose of the Oracle SQL Developer utility?

B.8 How do you create a new database in Oracle Database XE?

B.9 How do you connect to a database in Oracle Database XE?

B.10 What is an SQL script? What types of SQL statements and commands can you run more

efficiently as scripts?

Appendix B – Getting Started with Oracle Database XE

2. A set of INSERT commands when data needs to be added to a table.

B.11 What tool(s) can be used to create a script?

B.12 What file extension should you use for SQL scripts?

B.13 How do you open and run a script in Oracle SQL Developer?

To open a script in Oracle SQL Developer, click the Open File button and follow its prompts to

B.14 How do you create database tables in Oracle SQL Developer?

B.15 What is a sequence? How are sequences used in Oracle Database XE?

Sequences are used primarily to create surrogate keys in Oracle Database XE. Two sequence

methods are important to us. The method NextVal provides the next value in a sequence and

Appendix B – Getting Started with Oracle Database XE

An ARTIST row will be created with the next value in the sequence as the value for ArtistID.

Once this statement has been executed, you can retrieve the row just created with the CurrVal

method, as follows:

SELECT *

FROM ARTIST

WHERE ArtistID = seqAID.CurrVal;

Here, seqAID.CurrVal returns the current value of the sequence, which is the value just used.

B.16 How do you populate database tables in Oracle SQL Developer?

B.17 How do you create and run an SQL query in Oracle SQL Developer?

To run a query, first connect to the database you wish to query if it does not already have a

connection in the Connections list (use the SQL Developer connections manager tool to do this).

B.18 How do you import Microsoft Excel data into an Oracle Database XE database?

The first step is to normalize that data in the Microsoft Excel worksheet into one or more

B.19 Why would you want to create an ODBC connection to link a Microsoft Access 2016 to

an Oracle Database XE Database?

While Oracle Database XE is an excellent enterprise-class DBMS, the SQL Developer

Appendix B – Getting Started with Oracle Database XE

quickly prototype and implement simple forms and reports using commonly-installed software.

Microsoft Access 2016 fills this role and provides a set of application development tools such as

forms, reports, stored queries, and menu systems (see Appendix H, “The Access Workbench—

Section H—Microsoft Access 2016 Switchboards”). Thus, it would be useful to have a way to use

Microsoft Access 2016 as the application development frontend for an Oracle Database XE

database. We can connect Microsoft Access 2106 to an Oracle Database XE database via an

Open Database Connectivity (ODBC) link between the two.

B.20 What is an ODBC DSN? Why is one needed?

An ODBC DSN is a data source, and DSN stands for data source name. The DSN stores the actual

B.21 How do you create an ODBC connection to link a Microsoft Access 2016 database to an

Oracle Database XE database?

1. Create an empty Access database and ensure that it is using ANSI standard SQL (ANSI

92).

 ANSWERS TO EXERCISES

B.22 If you haven’t already done so, download and install Oracle Database XE as described

in the text. Use the default settings for the installation. Be sure that Oracle SQL

Developer is also correctly installed.

B.23 If you haven’t already done so, work through the steps described in this appendix to

create and populate the WP database.

B.24 Using Oracle Database XE and SQL Developer, run the following SQL queries from

Chapter 3:

Appendix B – Getting Started with Oracle Database XE

SQL-Query-CH03-01 through SQL-Query-CH03-32

SQL-Query-CH03-35 through SQL-Query-CH03-53 (remember that the “AS” keyword is

not allowed by Oracle with the JOIN syntax)

Save each query as follows:

• Create and run each query in Oracle SQL Developer.

• After you have run each query, save the query. By default, Oracle Database XE

saves each file as an SQL file with the file extension *.sql. Use this default setting

unless your instructor tells you to use a different extension. Name your queries in

numerical sequence, starting with the file name ODB-SQL-Query-01.sql.

The solutions are in the script file:

DBC-e08-ODB-WP-SQL-Queries-CH03-Text-AppB-Exercises.sql

DO NOT RUN THIS FILE AS A SCRIPT! You can run individual queries from the script file by

highlighting them or placing the cursor in them and then clicking the Run Statement button, as

shown in the following screen shot.

This is the

selected query

and only it will be

run when you click

the Run

Statement button

The Run

Statement button

Appendix B – Getting Started with Oracle Database XE

B.25 Use Oracle SQL Developer to run one or more of the saved SQL queries you created in

the previous question:

B.26 Complete exercise 3.59 using Oracle Database XE and Oracle SQL Developer. Start

each saved query name with ODB- and use the default .sql file extension. (The first

saved query name should be ODB-SQL-Query-AWE-3-1-A.sql.)

B.27 Complete Exercise 3.60 using Oracle Database XE and Oracle SQL Developer. Start

each saved script or query name with ODB– and use the default .sql file extension.

Create as many scripts as you need for Parts A-D. The saved query name will be ODB-

SQL-Query-AWE-3-3-E.sql.

B.28 If you have not already done so, import the COMPUTER table from Microsoft Excel into

the Oracle Database XE WP database as explained in the text.

B.29 Import the COMPUTER_ASSIGNMENT table from Microsoft Excel into the Oracle

Database XE WP database as explained in the text. How should this table be linked to

the EMPLOYEE table and the COMPUTER table by foreign keys? Be sure to include

these foreign keys in your final COMPUTER_ASSIGNMENT table structure when you

create it in Oracle Database XE. Note that this may require using some ALTER TABLE

statements as discussed in Appendix E.