Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
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
© 2018 Pearson Education, Inc. Page 4 of 26
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
© 2018 Pearson Education, Inc. Page 8 of 26
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
© 2018 Pearson Education, Inc. Page 9 of 26
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
© 2018 Pearson Education, Inc. Page 10 of 26
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
© 2018 Pearson Education, Inc. Page 11 of 26
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.
