IFMG481/581---Database Application with Oracle 9i

 Spring 2006

Indiana University of Pennsylvania
Department of MIS/Decision Sciences

Lab111, Eberly

Lecture:  T/TH 11:30-1:00 pm

Instructor: Dr. Jianfeng Wang

Office: ECOB 207J  

Email: jwang@iup.edu       Tel: 724-357-5750

Class homepage: http://www.eberly.iup.edu/jwang/oracle.htm

 

Required Textbook:        

Oracle9i: SQL (with an Introduction to PL/SQL) by Lannes Morris-Murphy,

ISBN: 0-619-06475-7.

 

Oracle9i Developer: PL/SQL Programming by Joan Casteel,

ISBN: 0-619-15900-6.

 

Both cover material for several OCP exams. Supplementary information for the course is available from the publisher at http://www.course.com.

 

Book Descriptions: Oracte9i SQL not only prepares students for the first exam in both the Oracle Database Administrator (DBA) and Internet Application Developer Certification Tracks; it offers them a solid understanding of Oracle9i SQL and how to use it effectively. This textbook is not simply a study guide; it is written for individuals who have just a basic knowledge of databases and can be utilized in a course on this latest implementation of SQL from Oracle. A business case that follows the progress of an online book retailer, JustLee Books, runs through every chapter, and many of the examples in each chapter build on this case. In addition, hands-on activities at the end of each chapter, such as review questions, assignments, and a case project, help to reinforce Oracle9i SQL skills. PL/SQL Programming offers complete coverage of the subject of programming with PL/SQL, including an introduction to PL/SQL at the beginning up through coverage of advanced topics. Also helps to prepare students for the Oracle9i PL/SQL Programming certification exam.

 

Course Description: Oracle is a widely used database management system. Course covers basic relational database concepts, the SQL query language, PL/SQL, object creation, including indexes, tables, triggers, and stored procedures, Oracle Forms, SQL Loader in the transition of legacy systems and web-enabled applications. Students will work with real-life projects.

 

Prerequisites:  IFMG210 with a grade of B or above, or IFMG 450 with a grade of C or above, or professor consent.

 

Grading  Components:

Homework and Quizzes

25%

Programming Assignments

25%

Exam 1

25%

Exam 2

25%

 

Grading Scale:

90 - 100%

A

80 - 89%

B

70 - 79%

C

60 - 69%

D

59% and below

F

 

 

General Course Information:

 

The course will be based on the textbooks. The material for the OCP (Oracle Certified Professional) Exam# 1Z0-001 and 1Z0-147 will be provided. Most of the tracks use 1Z0-001. Look on pp. 11-12 of this Oracle Developer Candidate Guide for details on Exam# 1Z0-001 and p.13 for details on Exam# 1Z0-101.

 

 

Course Objectives:

1. Offers complete coverage of Oracle9i SQL and how to use this technology on the job

 

2. Maps to the Oracles Certification Exam #IZ0-007, Introduction to Oracle9i: SQL, as well as Exam #lZ0-001, Introduction to Oracle: SQL and PL/SQL

 

3 Uses hundreds of examples to help illustrate key concepts and            techniques, and offers extensive end-of-chapter work that provides valuable practice

 

4. Offers full coverage of PL/SQL, from the basics for those new to the language, up through advanced topics, and is perfectly suited for a course on the subject.

 

5. Helps prepare individuals for the new Oracle Certification exam, Oracle9i: Program with PL/SQL (#1Z0-147)

 

7. Offers hundreds of opportunities for valuable hands-on practice, including step-by-step tutorials as well as review questions, hands-on assignments, and case projects in every chapter

 

8. Uses SQL*Plus as the tool of choice for hands-on tasks.

 

 

Course Outline:

  1. Basic DDL (Data Definition Language): Chapter 1, 2, 3, 8, 9, 11 of Oracle 9i SQL
  2. Basic DML (Data Manipulation Language): Chapter 4, 5, 6, 7, 10 of Oracle 9i SQL
  3. Additional Database Objects: Chapter 13 of Oracle 9i SQL
  4. Formatting Readable Output: Chapter 14 of Oracle 9i SQL
  5. Introduction to PL/SQL: Chapter 1 of Oracle 9i Developer
  6. Handling Data in PL/SQL blocks: Chapter 2 of Oracle 9i Developer
  7. PL/SQL Processing: Chapter 3 of Oracle 9i Developer
  8. Procedures: Chapter 4 of Oracle 9i Developer
  9. Functions: Chapter 5 of Oracle 9i Developer
  10. Packages: Chapter 6 of Oracle 9i Developer
  11. Program Unit Dependencies: Chapter 7 of Oracle 9i Developer
  12. Database Triggers: Chapter 8 of Oracle 9i Developer
  13. Oracle-Supplied Packages: Chapter 9 of Oracle 9i Developer
  14. Introduction to Dynamic SQL and Object Technology: Chapter 10 of Oracle 9i Developer

 

Attendance Policy

The course is going to be quite intensive. Thought there will be no credits for attending, attendance is mandatory and essential for you to succeed in learning Oracle 9i PL/SQL application programming.

 

Temporary Schedule: (this is just a temporal schedule)

Week 1-3: Basic DDL

Week 4-6: Basic DML

Week 7: Additional Database Objects and Formatting Readable Output

Exam 1

Week 8: Introduction to PL/SQL: Chapter 1 of Oracle 9i Developer

: Handling Data in PL/SQL blocks: Chapter 2 of Oracle 9i Developer

Week 9: PL/SQL Processing: Chapter 3 of Oracle 9i Developer

Week 9-10: Procedures: Chapter 4 of Oracle 9i Developer

             Functions: Chapter 5 of Oracle 9i Developer

Week 10-11: Packages: Chapter 6 of Oracle 9i Developer

Week 11-12: Program Unit Dependencies: Chapter 7 of Oracle 9i Developer

Week 13 (Thanksgiving): Database Triggers: Chapter 8 of Oracle 9i Developer

Week 14: Oracle-Supplied Packages: Chapter 9 of Oracle 9i Developer

Week 15:  Introduction to Dynamic SQL and Object Technology:

Chapter 10 of Oracle 9i Developer

 

Final Week: Exam 2

 

 

 

Important notes:

       This is a lecture-based course utilizing overhead projector, handouts, and online resources. Participation in class discussion (questions, answers and other activities),       which enables students to develop concepts learned in class is encouraged.

 

  1. In-class exercises and Exams will be announced in class and online.
  2. In-class exercise must be done during a class.
  3. There will be no make-ups for lecture, class-works, cases, and paper presentations.
  4. Exams can be made up under special circumstances and only if cleared with the instructor prior to the exam. Written medical or personal documentation is required. Otherwise a zero will be recorded. It is your responsibility to make the appointment to do so.
  5. Your presence is required and desired. It is only this way that you can benefit maximally from the course. Excuses for class are not needed. You are either there or not there. Lectures will not be repeated in my office or on an individual basis.
  6. Academy dishonesty: Section 7324 of the Crimes Code of PA Makes it a misdemeanor of the 3rd degree to see or offer for distribution any dissertation, thesis, term paper, essay, report or other written assignment, or to sell or offer for distribution assistance in the preparation of such assignments, for submission to any educational institution to meet the requirements for any degree, diploma, certificate, or course of study.( “Assignment” means a written recorded, pictorial, artistic, or other academic task. “Prepare” means to create, write or in any way produce in whole or in substance any part of such assignment.)

 

 

Note: I reserve the rights to make changes.

For detailed schedule info, often visit: http://www.eberly.iup.edu/jwang/oracle.htm.