DEPARTMENT OF COMPUTER STUDIES AND ADVANCED
TECHNOLOGY
Course
Syllabus: ITSE1345-Introduction to
Oracle SQL and PL/SQL
Office Telephone: 512-223-3184
Office: PSO-02J
Office Hours: Determined by semester
E-mail: rsmith@austincc.edu
Class Meetings:
As defined in the Semester Catalogue
Course Description: An introduction to the design and creation of
relational databases. Topics
include storing, retrieving,
Updating and displaying data using Structured
Query Language (SQL)
integrated into Stored Procedures, Functions,
Packages and Triggers
(PL/SQL Programming).
Pre-requisite: ITSE2309 (CIS2153)
Approved Course Text: Set by taskforce
Oracle 9i: SQL with an
introduction to PL/SQL
By Lannes L.
Morris-Murphy – Thompson/Course Technology
ISBN 0-619-06475-7
Oracle 9i PL/SQL Programming
By Scott Urman Oracle Press – McGraw-Hill/Osborne
ISBN 0-07-219147-3
Instructional Methodology: The course will have both
lab and lecture. The student will be
required to do assigned readings
from the text and handouts
as well as scheduled
individual labs to reinforce the material
covered in class. Scheduled tests
will be used to assess the
progress of the student toward
achievement of the course
objectives.
Course Rationale: This course is designed to teach students how to create programmed solutions using the PL/SQL procedural language. As each student is required to have a working knowledge of the programming process and understanding of the functional constructs in programming, the focus of this course is on database problems and potential solutions.
This course is part of the Computer Information Technology Database Certificate plan. Completion of this course and the prerequisite courses prepares the student for a position as an entry level Database Programmer and acts as preparation for Oracle Certification test 2.
Course Objectives/ Learning
Outcomes:
Enhance the knowledge and understanding of
Database analysis and design.
Enhance the knowledge of
the processes of Database Development and Administration
using
SQL and PL/SQL.
Enhance
Programming and Software Engineering skills and techniques using SQL
and
PL/SQL.
Preparation
of background materials and documentation needed for Technical
Support
using SQL and PL/SQL.
Use the Relational model and how it
is supported by SQL and PL/SQL.
Use the PL/SQL code constructs of
IF-THEN-ELSE and LOOP types as well
as syntax and command
functions.
Solve Database problems using Oracle 9i SQL and PL/SQL. This will include the
use of Procedures, Functions, Packages, and Triggers.
Instructor may add to but not delete any
course objectives/Learning Outcomes
Grade Policy: Grade will be based both on
concepts and practical application.
Grade Scale:
90% - 100% A
80% - 89%
B
70% - 79%
C
60% - 69%
D
0% - 59% F
Method of Determining Final Course Grade:
Examination Average 60% = 60 Points
Laboratory Assignment Average 35% = 35 Points
Daily (attendance, participation, etc.) 5% = 5 Points
100 total possible points
Classroom Conduct:
Food
and drinks are not allowed in the classroom.
Cellular
phones and pagers are to be turned off during class and labs.
Examinations:
Two (2) examinations will be given (see the schedule
at the end of this hand out). Examinations must be taken on scheduled dates.
The examination dates may be changed due to unforeseen circumstances. Any
changes will be announced in class. Students will be allowed to bring 1 page
(both sides) of notes and a calculator to the exams.
If an Exam is missed, you
will have made arrangements with the Instructor in advance and have 1
week to take the make up Exam at the
Laboratory/Homework Assignments:
Laboratory/Homework assignments will be made
throughout the class. Laboratory facilities are available on Campus and we will
meet in the Lab. regularly to discuss assignments. Assignments are due two
weeks from the date assigned unless otherwise specified by the Instructor.
Students may turn in late assignments for partial credit.
Attendance:
You are expected to attend and actively participate
in every class meeting (lecture and Lab). A student is subject to dismissal by
the Instructor if He or She misses 10% of the class meetings. It is the
responsibility of the student to ensure that this does not become a problem.
A student is expected to
complete his or her own projects and tests.
Students are responsible for observing the policy on academic integrity
described in the Current ACC Student Handbook, under “Student Discipline Policy, Section
C”.
“Acts prohibited by the college for which discipline may be administered include scholastic dishonesty, including but not limited to cheating on an exam or quiz, plagiarizing, and unauthorized collaboration with another in preparing outside work. Academic work submitted by students shall be the result of their own thought, research or self-expression. Academic work is defined as, but not limited to tests, quizzes, whether taken electronically or on paper; projects, either individual or group; classroom presentations, and homework”.
A student may receive a temporary grade of “I”
(Incomplete) at the end of the semester only if ALL of the following
conditions are satisfied:
1. The student is unable to
complete the course during the semester due to circumstances beyond their
control.
2. The student must have earned
at least half of the grade points needed for a “C” by the end of the semester.
3. The request for the grade
must be made in person at the instructor’s office and necessary documents
completed.
4. To remove an “I”, the
student must complete the course by two weeks before the end of the following
semester. Failure to do so will result
in the grade automatically reverting to an “F”.
Freedom of Expression Policy:
It is expected that faculty
and students will respect the views of others when expressed in classroom
discussions.
Attendance / Withdrawal Policy:
Students are expected to attend classes and will be
held responsible for all material covered in class. Regular attendance helps ensure satisfactory
progress towards completion of the course. It is the student’s responsibility
to complete a Withdrawal Form in the Admissions Office if they wish to withdraw
from this class. The instructor may
withdraw students from this class if their absences exceed 10% of the total
number of class meetings. The last date
to withdraw for this semester is defined in the semester catalogue.It
is not the responsibility of the instructor to withdraw the students from their
class even though the instructor has the prerogative to do so under the above
listed circumstances.
Students with Disabilities Policy:
“Each ACC campus offers support
services for students with documented physical or psychological
disabilities. Students with disabilities
must request reasonable accommodations through the Office for Students with
Disabilities on the campus where they expect to take the majority of their
classes. Students are encouraged to make
this request three weeks before the start of the semester. (Refer to the Current ACC Student Handbook)”
Testing Center Policy (Open Campus Sections Only)
Course Topics:
SQL Commands
SELECT INTO
DML –
Select, Insert, Update, Delete
DDL –
Create, Drop, Grant, Alter
Transaction
– Commit, Rollback, Savepoint
PL/SQL Concepts and
Constructs
Block Structure
Declare,
Begin, Exception, End
Procedures,
Functions, Packages and Triggers
Input and
Output Parameters and Return values
PL/SQL Code
Implementations
Variables
and Constants
Attributes
(Char, Varchar2, Number, Binary, Integer, Date)
IF – THEN – ELSIF – ELSE
Simple
(EXIT WHEN
FOR
WHILE
CASE
CURSOR
TYPES - %TYPE,
&ROWTYPE, RECORD
DBMS_OUTPUT.PUT_LINE
SCANS
ITEMS COMPETENCIES:
|
CODE |
DESCRIPTION |
ITSE2356
LEVEL |
|
F1 |
|
2 |
|
F3 |
ARITHEMETIC:
Performs basic computations; uses basic numerical concepts such as whole numbers, etc. |
2 |
|
F4 |
MATHEMATICS:
Approaches practical problems by choosing appropriately from a variety of
mathematical techniques. |
2 |
|
F5 |
LISTENING:
Receives, Attends to, interprets, and responds to verbal messages and other
cues. |
2 |
|
F7 |
CREATIVE
THINKING: Generates new ideas |
2 |
|
F8 |
DECISION
MAKING: Specifies goals and constraints, generates alternatives, considers
risks, and evaluates and chooses best. |
2 |
|
F9 |
PROBLEM
SOLVING: Recognizes problems and devises and implements plan of action |
2 |
|
F10 |
SEEING
THINGS IN THE MINDS EYE: Organizes and processes symbols, pictures, graphs,
objects, and other information. |
2 |
|
F12 |
REASONING:
Discovers a rule or principle underlying the relationship between two or more
objects and applies it when |
2 |
|
F13 |
RESPONSIBILITY:
Exerts a high level of effort and perseveres toward goal attainment |
2 |
|
C8 |
Uses
computers to process information |
2 |
|
C11 |
Serves
Clients/Customers: Works to satisfy customer’s expectations |
2 |
|
C19 |
APPLIES
TECHNOLOGY TO TASK: Understands overall intent and proper procedures for
setup and operation of equipment. |
2 |
Tentative Lecture Schedule:
|
Week of |
Chapter |
Topic |
Lab Schedule |
|
1 |
Handout #1 – SQL Review Urman, Chapter 1 Urman, Chapter 2 |
Review of SQL & the Relational Model Basic PL/SQL Features Development & Execution Environments |
Lab 1 - Start |
|
2 |
Urman, Chapter 3 |
Writing Declarations and Blocks |
Lab 1 - Complete Lab 2 – Start |
|
3 |
Urman, Chapter 4 Urman, Chapter 5 |
SQL within PL/SQL Built-in SQL Functions |
|
|
4 |
Urman, Chapter 7 |
Error Handling and Exceptions |
Lab 2 - Complete |
|
5 |
Exam #1 – Review Exam #1 |
Urman Chapters (1,2,3,4,5,7) Plus Handouts |
|
|
6 |
Urman, Chapter 9 Urman, Chapter 10 |
Procedures, Functions and Packages |
Lab 3 – Start |
|
7 |
Urman, Chapter 6 Urman, Chapter 11 |
Manipulating Data with Cursors Database Triggers |
Lab 3 - Complete Lab 4 – Start |
|
8 |
Urman, Chapter 8 Urman, Chapter 12 |
Collections Advanced Features |
Lab 4 - Complete |
|
9 |
Exam #2 – Review Exam #2 |
Chapters (6,8,9,10,11,12) Plus Handouts |
|
Tentative Laboratory/Homework Assignments:
1. SQL Initial exercise
2. PL/SQL Programming – Constructs and Built-in Functions
3. PL/SQL Programming – Procedures and Functions
4. Advanced PL/SQL Programs, Packages, and Triggers