CS 494 - Database Programming with Oracle
Lynchburg College Computer Science

Jan. 13, 2003

Click on Textbook to see table of contents
for Enhanced Guide to Oracle 8i

Instructors :
Dr. Randy Ribler (ribler_r@lynchburg.edu) and
Dr. Constantine Roussos (roussos@lynchburg.edu)
Offices: Hobbs 103, Hobbs104
Office Phones: 544-8529, 544-8395

COURSE SYLLABUS

Databases may be the most widespread computer application today. Hardly a business exists that does not employ databases to store critical information. The data stored in these databases is accessed and updated using database management systems such as Oracle, Microsoft Access and Microsoft SQL Server. This course is designed to give the student an understanding of databases and database management systems. Emphasis is placed upon the proper design of databases while meeting users' special needs. The Oracle database management system will be used throughout the course. The student will complete numerous database projects to reinforce these concepts and gain a proficiency in the design and use of databases. Students will become proficient in programming databases using the PL/SQL language. The student will also learn how to access databases via a procedural/ object-oriented high-level programming language. The course strongly emphasizes a hands-on approach to database programming and administration.

The primary objectives of this course are the following:

1. Learn Relational Database Concepts
2. Install the Oracle Database
3. Create and Maintain Secure Databases.
4. Manage Oracle Databases
5. Program in Oracle PL/SQL
6. Access Oracle Databases through a Procedural/ Object-Oriented Programming Language

This course fulfills CS major and minor requirements for courses 300 level or above.

More information, supplementary materials, sample tests, study guides, etc. are located at http://cs-netlab-01.lynchburg.edu/courses/Oracle/index.htm

1. Your Computer Accounts at the College
1. Using your server account.

1) On-campus computing facilities.
2) Logging into your account.

a. Username, Password, UIC.
b. Directories and subdirectories.
c. Privileges and access.

2. Accessing course materials on the web.
3. Electronic MAIL in the course

1. Your email address(es)
2. Using MAIL commands.

a. Reading, sending, extracting, etc.
b. Sending MAIL to me.

3. Your homework assignments, programs and general communication.

2.  
3. Chapter 1 - Client/Server Databases and the Oracle8i Relational Database

Learning Objectives
After completing Chapter 1, you will be able to:

1. Understand the purpose of database systems
2. Understand the differences between client/server and personal databases
3. Understand the structure of relational database tables and be familiar with relational database terms
4. Be able to identify primary, foreign, composite, and surrogate key fields
5. Understand the Clearwater Traders, Northwoods University, and Software Experts case study databases

4. Chapter 2 - Creating and Modifying Database Objects

Learning Objectives
After completing this chapter, you will be able to:

0. Understand Oracle user accounts and system privileges
1. Identify the properties of an Oracle database table
2. Understand the different data types used in an Oracle database table
3. Understand how integrity and value constraints are specified in an Oracle database table
4. Create and modify database tables using SQL*Plus
5. Use the Oracle online help system
6. Understand how to modify existing database tables

5. Chapter 3 - Using Oracle to Add, View, and Update Data

Learning Objectives
After completing this Lesson, you will be able to:

0. Run scripts to automate SQL commands
1. Insert data values into database tables using SQL commands
2. Understand database transactions
3. Update and delete database records using SQL
4. Create sequences
5. Grant and revoke object privileges
6. Write SQL queries to retrieve data records from a single database table
7. Sort and group query output
8. Use mathematical calculations, number functions, and date arithmetic to manipulate data values in queries
9. Create queries with input variables
10. Format query output in SQL*Plus
11. Write SQL queries to join multiple database tables
12. Create nested queries with subqueries
13. Combine query results using set operators
14. Create database views and indexes
15. Understand private and public synonyms

6. Chapter 4 - Introduction to PL/SQL

Learning Objectives After completing this Lesson, you will be able to:

0. Understand PL/SQL variables and data types
1. Understand the structure of PL/SQL program blocks
2. Create a PL/SQL program
3. Manipulate variables and perform. number and character string operations
4. Become familiar with PL/SQL program control structures
5. Learn how to debug PL/SQL programs
6. Use SQL commands in PL/SQL programs
7. Create loops in PL/SQL programs
8. Create PL/SQL tables and tables of records
9. Use cursors to process database data
10. Understand how to use the EXCEPTION section in a PL/SQL program for error handling

7. Chapter 5 - Advanced PL/SQL Programming

Learning Objectives
After completing this Lesson, you will be able to:

0. Understand named PL/SQL program units
1. Create server-side stored procedures and functions in SQL*Plus
2. Use Procedure Builder to create client-side PL/SQL program units
3. Use the Procedure Builder debugging environment
4. Create libraries of program units
5. Call procedures from other procedures and pass parameter values
6. Create server-side stored program units and libraries in Procedure Builder
7. Understand program unit dependencies
8. Create packages
9. Create database triggers
10. Understand the Oracle built-in packages
11. Understand how to use the DBMS_JOB built-in package
12. Use the DBMS_PIPE and DBMS_DDL built-in packages
13. Understand Dynamic SQL
14. Use Dynamic SQL to create programs with query parameters that are defined when the program is executed

8. Chapter 6 - Creating Oracle Data Block Forms

Learning Objectives
After completing this Lesson, you will be able to:

0. View, insert, update, and delete data records using a data block form.
1. Create a single-table data block form.
2. Understand form. components
3. Understand how to navigate and access form. objects using the Object Navigator
4. Create a single-table data block form. that displays multiple records
5. Create a data block form. based on a view
6. Modify form. properties to improve form. appearance and function
7. Create a master-detail form. that contains multiple data blocks
8. Format form. text items using format masks
9. Delete data blocks from forms
10. Use sequences to automatically generate primary keys in a form.
11. Create single and multiple table lists of values (LOVs) to provide lists for foreign key values in forms
12. Create editors to aid entering and editing blocks of text
13. Become familiar with different form. item types that you can use to enter and modify data values

9. Chapter 7 - Creating Custom Forms

Learning Objectives
After completing this Lesson, you will be able to:

0. Design and create a custom form.
1. Write PL/SQL triggers to process database records
2. Create program units that are called by a trigger
3. Use the Form. Builder Debugger
4. Understand navigational triggers
5. Control user navigation in a form.
6. Understand and control default system messages
7. Create alerts and messages to provide system feedback
8. Create applications that avoid user errors
9. Trap common runtime errors
10. Validate form. data
11. Link data blocks and control blocks
12. Create a form. with multiple canvases
13. Create tabbed and stacked canvases

10. Chapter 8 - Advanced Form. Builder Topics

Learning Objectives
After completing this Lesson, you will be able to:

0. Create and use non-input data items that display images and sounds
1. Create boilerplate objects and calculated form. text items
2. Create static and dynamic image items that display graphics
3. Understand alternate data block sources
4. Program form. function keys
5. Understand how forms handle transaction processing
6. Understand how to develop forms that handle large data sets
7. Understand mouse events
8. Control the appearance of the mouse pointer
9. Work with record groups
10. Control data block relationships

11. Chapter 9 - Using Report Builder

Learning Objectives
After completing this Lesson, you will be able to:

0. Understand Report Builder report styles
1. Use the Report Wizard to create a single-table report
2. Understand the Report Builder Object Navigator
3. Create a report that shows master-detail relationships
4. Create a report template
5. Describe the components of a report
6. Modify report components
7. Modify the format of master-detail reports
8. Create a user parameter to allow users to customize report data
9. Create multipanel reports that span multiple horizontal pages
10. Display image, sound, and video data in a report
11. Describe different report file types
12. Created calculated report columns
13. Understand report triggers
14. Manually create report queries and data links
15. Create a link file object to display the contents of a file in a report
16. Run a report from a form.

12. Chapter 10 - Using Graphics Builder

Learning Objectives
After completing this Chapter, you will be able to:

0. Use Graphics Builder to create charts based on database data
1. Create a form. that passes user inputs to a chart
2. Create a form. that displays a chart
3. Create a report that displays a chart

13. Chapter 11 - Creating an Integrated Database Application

Learning Objectives
After completing this Lesson, you will be able to:

0. Understand the steps for developing a database application
1. Design an interface for a database application
2. Create a global path variable to specify the location of application files
3. Use timers to create a splash screen
4. Create template forms and visual attribute groups to ensure consistency across form. modules
5. Understand how to open, navigate among, and close form. modules in an integrated database application
6. Use Project Builder to create an integrated database application project
7. Use the Project Builder environment
8. Manage project components
9. Deliver an Oracle project
10. Customize the Project Builder environment
11. Describe form. menu components
12. Create and display custom pull-down menus on forms
13. Create a menu toolbar
14. Create context-sensitive pop-up menus
15. Specify menu selections programmatically
16. Use menus to enforce form. security

14. Chapter 12 - Form. Builder Objects and Flexible Code

Learning Objectives
After completing Lesson A, you will be able to:

0. Understand object-oriented principles
1. Use property classes to share object properties
2. Store reusable objects in object groups
3. Create object libraries to store reusable objects
4. Use PL/SQL libraries to share PL/SQL program units
5. Use the built-in calendar class to create a form. calendar window
6. Understand flexible code
7. Use system variables to create flexible code
8. Use built-in subprograms to create flexible code
9. Use indirect referencing to write flexible code