CS405 : Database Programming using Oracle 11g

I like this Course

Course Info

Course Category

Computer Science/Information Technology

Course Level

Undergraduate

Credit Hours

3

Pre-requisites

N/A

Course Contents

Detailed Session Plan by Topic

Recommended Books

         i.            Database Management Systems” by Raghu Ramakrishnan and Gehrke

        ii.            Data Modeling and Relational Database Design By Jan Speelpenning, Patrice Daux, Jeff Gallus

      iii.            Oracle Database 11g: A Beginners Guide By Ian Abramson, Michael Abbey, Michael J Corey and Michelle Malcher

      iv.            Oracle Database 11g: SQL Fundamentals by Salome Clement, Brian Pottle, Puja Singh

        v.            Oracle PL/SQL  by Example by Benjamin Rosenzweig and Elena Silvestrova Rakhimov

 

Topic Name & Description

(This part represents the depth of topic you intend to cover.)

Topic Learning Outcomes

(Spell out the learning outcomes, in bulleted form, for the students.)

Primary/Secondary

Resource/Book; Course Notes for the Topic

(Select all content/resources that you want students to consult/read)

 

Page/Section/URL of the Resource

Conducting the Session

 (This part describes the nuts and bolts of conducting a session. What kind of prominent examples will you use in teaching the topic? Whether you will use outdoor recordings, and if so, where and of what.)

 

No. of Video Minutes Dedicated to this Topic

Conceptual Data Modeling and Entity Relationship Diagram Overview

 

After completing this topic, students will be able to:

 

Ø  Understand basic structure of ERD and derive it from small scenarios/ case study to apply further for database generation

 

Book Reference  (1)

 

 

 

 

 

 

 

 

 

 

 

Ch: 2 and 3

 

 

 

a)       Concept of ERD

b)       Components of ERD

c)       Entities

d)       Attributes

e)       Relationships

f)        One-to-Many

g)       Many-to-Many

h)       One-to-One

i)        Process of creating ERD to be discussed

j)        Implementing ERD using Scenario – 1

k)      Implementing ERD using Scenario – II

 

5  min

5 min

5 min

5 min

5 min

5 min

5 min

5 min

5 min

5 min

5 min

 

5  min

 

 

Introduction to Oracle 11g on Cloud

 

After completing this topic, students will be able to:

 

Ø  Login to Oracle 11g cloud.

Ø  Create profile and able to perform basic functionalities as per architecture of  Oracle 11g

Oracle Database Architecture

 

 

 

 

 

 

 

Book Reference (3)

 

Ch:1

Page:1-7 to 1-12

 

http://docs.oracle.com/cd/E11882_01/server.112/e40540.pdf

 

 

Ch:1

Page: 3-11

 

https://books.google.com.pk/books?id=1dl3I_PkdXMC&pg=PA9&lpg=PA9&dq=architecture+of+oracle+11g+database+with+diagram&source=bl&ots=BDZezpN67E&sig=UZwhfacbu7CU8ck99M8rPv99bKU&hl=en&sa=X&ei=-UiZVNOYI4nyUtGjg6AL&ved=0CEUQ6AEwBzgU#v=onepage&q=architecture%20of%20oracle%2011g%20database%20with%20diagram&f=false

 

a.        Registering on  Oracle 11g cloud

b.       Login creation

c.        Accessing different repositories

d.       Assigning privileges and access rights

 

5 min

5 min

5 min

5 min

5 min

5 min

 

 

 

Structured Query Language basics - Recap

After completing this topic, students will be able to:

 

Ø  Translate business requirements into technical transformations to fetch required data

Book Reference  (4)

 

 

 

 

Practice Questions to be discussed, how to write query from Business requirement

Ch. 1-5

a)       Select Statement

b)       Where Clause

c)       Group by Clause

d)       Having Clause

e)       Order by

f)        Group functions

g)       Implementing SQL using Select and Where with Examples –I

h)       Implementing SQL using Select and Where with Examples -II

 

 

5 min

5 min

5 min

5 min

5 min

5 min

5 min

 

 

PL/SQL Concepts

After completing this topic, students will be able to:

 

Ø  Basic building block of PL/SQL

Ø  Difference between SQL and PL/SQL.

 

Book Reference (5)

Ch: 1

a)       PL/SQL Client Server Architecture

b)       PL/SQL Blocks

c)       Execution of PL/SQL block

d)       Substitution Variable

e)       DBMS_OUTPUT.PUT_LINE Statement

5 min

5 min

5 min

5 min

5 min

5 min

5 min

5 min

 

 

General Programming Language Fundamentals of PL/SQL

After completing this topic, students will be able to:

 

Ø  Declare variables in PL/SQL blocks

Ø  Scoping of variables within different access areas.

Book Reference (5)

Ch: 2

a)       Declaration of variables in PL/SQL

b)       Handling of reserve words

c)       Using Identifiers in PL/SQL

d)       Anchored Datatypes

e)       Declaring and initialize variables

f)        Scope of block, nested blocks and labels

g)       Handling comments in PL/SQL

h)       Executing Blocks

5 min

 

5 min

 

5 min

 

5 min

 

5 min

 

5 min

 

 

5 min

 

5 min

 

 

SQL in PL/SQL

After completing this topic, students will be able to:

 

Ø  Use DML within scope of PL/SQL

Ø  Restriction of using DML in PL/SQL

 

Book Reference (5)

Ch: 3

a)       Select INTO Syntax

b)       DML in PL/SQL

c)       Using Sequence in DML

d)       Using Commit, Rollback in PL/SQL

e)       Implementing Select and DML in single block

5 min

5 min

5 min

 

5 min

 

5min

 

 

Conditional Control – I

After completing this topic, students will be able to:

 

Ø  Use and apply different varitions of IF statements

Book Reference (5)

 

 

PL/SQL Control Statements

 

Ch: 4

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS004

a)       IF Statements

b)       IF-THEN –ELSE Statements

c)       ELSIF statements

d)       Nested IF statements

e)       Implementing IF statements using SQL Statement in PL/SQL –I

f)        Implementing IF statements using SQL Statement in PL/SQL –II

 

 

5 min

5 min

 

5 min

5 min

 

5 min

 

 

 

5 min

 

 

 

Conditional Control – II

After completing this topic, students will be able to:

 

Ø  Use and apply different varitions of CASE statements

Ø  Using different equivalent functions for CASE

 

Book Reference (5)

 

 

 

PL/SQL Control Statements

 

Ch: 5

 

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS004

a)       Case Statements

b)       Searched Case Statement

c)       Case Expression

d)       NULL Function

e)       COALESCE Function

f)        Implementing CASE statement with SQL in PL/SQL

g)       Implementing CASE statement with SQL / Substition Varible  in PL/SQL

5 min

5 min

 

5 min

5 min

5 min

 

 

 

5 min

 

 

Iterative Control-I

After completing this topic, students will be able to:

Ø  Apply appropriate type of loop as per requirement

Ø  Differentiate between working of different iterative controls

 

Book Reference (5)

 

 

 

PL/SQL Control Statement

 

Ch: 6

 

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS004

a)       Simple Loops

b)       Simple Loops with Exit Conditions

c)       Simple Loop with Exit When Condition

d)       While Loops

e)       Numeric FOR Loops

f)        Numeric FOR Loop with IN Option

g)       Numeric FOR Loop with Reverse Option

h)       Implementing Simple Loops with DML and SQL

i)         Implementing While Loop with DML and SQL

j)         Implementing FOR loop with DML and SQL

5 min

5 min

 

5 min

 

 

5 min

5 min

5 min

 

5 min

 

 

5 min

 

 

5 min

 

 

5 min

 

 

Iterative Control-II

After completing this topic, students will be able to:

Ø  Understand rationale for CONTINUE statement

Ø  Working of Nested Loops

Ø  Usability of using DML/ SQL with Loops

 

Book Reference (5)

 

 

PL/SQL Control Statement

 

Ch: 7

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS004

a)       CONTINUE Statement

b)       CONTINUE WHEN Condition

c)       Nested Loops

d)       SQL &DML and Select using Loops

e)       DML and CONTINUE Statement

f)        CONTINUE with DML and SQL

5 min

 

5 min

 

5 min

5 min

5 min

5 min

 

 

5 min

 

 

Workshop

After the workshop students will be able:

 

Ø  Have deep understanding of how loops  and control structure work

Ø  Decide when to apply what control structure

 

 

 

Practice Scenarios / Questions will be dicsussed

 

a.        Example: Practice Questions will be discussed DML / SQL and Control Structures

b.       Example: Practice Questions will be discussed DML / SQL and Interative Structures

 

10 min

 

 

 

 

10 min

Error Handling and Build-in Exceptions

After completing this topic, students will be able to:

Ø  Understand difference between Error and Exception

Ø  Identify use of build-in exceptions in PL/SQL blocks

Book Reference (5)

 

 

 

PL/SQL Error Handling

Ch: 8

 

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS007

a)       Handling Errors

b)       Difference between Error and Exception

c)       Handling exceptions

d)       Build-in Exception handling

e)       Implementing Build-In Exception with DML

f)        Implementing Build-In Exception with SQL and DML

5 min

5 min

5 min

5 min

5 min

 

5 min

 

 

5 min

 

 

Exceptions

After completing this topic, students will be able to:

Ø  Define User exception

Ø  Scope and raising user-defined exceptions

Book Reference (5)

Ch: 9

a)       Scope of exception

b)       Use of user-defined exceptions

c)       Understanding propagation of Exception

d)       Reraise Exceptions

e)       Implementing User-Defined exceptions with DML

f)        Implementing User-Defined exceptions with DML and SQL

 

5 min

5 min

 

5 min

 

 

5 min

5 min

 

 

5 min

 

 

 

Advanced Exceptions

After completing this topic, students will be able to:

Ø  Understand and use advanced Exceptions concepts

Ø  Using Advanced Exceptions concepts to handle generic exception

Book Reference (5)

Ch: 10

a)       Using Raise_Application_Error

b)       Using EXCEPTION_INIT Pragma

c)       Using SQLCODE and SQLERRM

d)       Implementing SQLCODE and SQLERRM with DML

e)       Implementing SQLCODE and SQLERRM with SQL

f)        Implementing SQLCODE and SQLERRM with DML

And SQL

5 min

 

 

5 min

 

 

5 min

 

5 min

 

 

5 min

 

 

5 min

 

 

 

 

Cursors

After completing this topic, students will be able to:

Ø  Limitations without cursors

Ø  Use of Cursors

Ø  Handle built-in cursors

Ø  Handling user-defined cursors

 

 

 

a)       Cursor Introduction

b)       Cursor and SQL

c)       Record Types

d)       Process an Explicit Cursor

e)       Steps to use Explicit Cursor

f)        Cursor Attributes

g)       Using Cursor FOR Loops

h)       Using Loop for Cursor Processing

i)         Implementing SQL and Cursors

j)         Implementing DML and Cursors

k)       Implementing Exceptions and SQL with Cursor using Loop

l)         Implementing Exceptions and SQL with Cursor using FOR Loop

 

 

5 min

5 min

5 min

5 min

 

5 min

 

5 min

5 min

 

5 min

 

5 min

 

5 min

 

5 min

 

 

 

5 min

 

 

 

Advanced Cursors

After completing this topic, students will be able to:

Ø  Define and Use parameterized Cursors

Ø  Handle Current row references in cursors

Book Reference (5)

 

 

Introduction to Oracle 11g Cursors

 

Ch: 12

 

 

http://www.dbanotes.com/database-development/introduction-to-oracle-11g-cursors/

a)       Parameter using in Cursor

b)       Implementing Paramerized Cursors

c)       Complex Nested Cursor

d)       For Update Cursors

e)       Where Current Cursors

f)        Implementing Nested For loops in Cursors

g)       Implementing Cursor with Complex SQL Queries

h)       Implementing Cursors with Nested Loop Statement

5 min

 

5 min

 

 

5 min

 

5 min

5 min

 

5 min

 

 

5 min

 

 

 

5 min

 

 

 

Workshop on Cursors

After completing the topic, students will be able to :

Ø  Define Cursors as per business requirement

Ø  Define and decide which control structure to apply

 

Practice Scenarios / Questions will be dicsussed

i)         Example-1: Implementing Cursors using Control Structures using complex SQL Queries

j)         Example-2: Implementing Cursors using Control Structures  and Interative Controls  using complex SQL Queries  and DML

 

10 min

 

 

 

 

 

10 min

Triggers

After completing this topic, students will be able to:

 

Ø  Identify need of Triggers

Ø  Defining and Using different type of triggers

 

Book Reference (5)

 

 

PL/SQL Triggers

 

Ch: 13

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS020

a.        Understanding use of trigger

b.       Using Before and After Trigger

c.        Debugging  Triggers

 

d.       Different Types of Triggers

e.       Row and Statement Triggers

f.         Instead of Trigger

g.        Implementing Triggers using Cursor and SQL

h.       Implementing Triggers using DML

i.         Data Dictionary of Trigger

 

5 min

 

5 min

 

5 min

 

5 min

 

5 min

 

5 min

5 min

 

 

5 min

 

5 min

 

 

Compound Triggers

After completing this topic, students will be able to:

 

Ø  Dependencies among triggers

Ø  Understand and handle mutation issues

 

Book Reference (5)

Ch: 14

a.        Compound Triggers

b.       Restrictions in Compound Triggers

c.        Implementing Compound Triggers with DML

d.       Implementing Compound Trigger with SQL

e.       Mutating Table

f.         Implementing Mutating Table base Triggers

g.        Updating Mutating Tables

 

5 min

5 min

 

5 min

 

 

5  min

 

 

5 min

5 min

 

 

5 min

 

 

Collections

After completing this topic, students will be able to:

 

Ø  Understand difference between arrays and PL/SQL tables

Ø  Identify need to use PL/SQL tables

Ø  Understand different levels of collections

 

Book Reference (5)

 

 

 

PL/SQL Collections and Records

Ch: 15

 

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

a.        PL/SQL Tables

b.       Associated Arrays

c.        Associated Array attributes

d.       Using Associate Arrays with Cursor

e.       Using Associate Arrays with complex SQL queries

f.         Use Associated Arrays with DML

g.        Nested Tables

h.       Using Nested Tables complex Cursors

i.         Using Nested Tables complex SQL Queries

j.         Varrays

k.        Using Varrays with Cursors

l.         Using Varrays with  DML

m.      Multilevel Collections

5 min

5 min

5 min

 

5  min

 

5 min

 

 

 

5 min

 

5 min

5 min

 

5 min

 

 

5 min

5 min

 

5 min

 

5  min

 

 

Records

 

After completing this topic, students will be able to:

 

Ø  Understand difference between collection and record

Ø  Understand different level of Records

 

Book Reference (5)

 

 

PL/SQL Collections and Records

Ch: 16

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

a.        Record Types

b.       Table base records

c.        Cursor base records

d.       Implementing Table Base Cursors

e.       Implementing Cursor base Records

f.         User Defined Records

g.        Implementing User-Defined Records

h.       Implementing User-define records using Cursor

i.         Implementing User-define records using SQL

 

5 min

5 min

5 min

5 min

5 min

5 min

5 min

 

5 min

 

5 min

 

5 min

 

 

5 min

 

 

 

 

Procedures

After completing this topic, students will be able to:

 

Ø  Create  and define use define procedures

 

Ø  Understand different methods to call procedure

 

Ø  Browse Data Dictionary regarding procedure

 

Book Reference (5)

 

 

Create Procedure Statement

Ch: 19

 

 

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_procedure.htm

a.        Creating procedures

b.       Procedures and Data Dictionary

c.        Debugging Procedures

d.        Implementing Procedure using Cursor

e.       Implementing Procedure using DML

f.         Implementing Procedure using Cursor / DML / SQL and Tables

g.        Parameter passing in Procedures

h.       Parameter  types in Procedures

i.         Implementing Parameterized Procedure using Cursors

j.         Implementing Parameterized Cursors using DML and SQL only

5 min

 

5 min

 

5 min

 

5 min

 

 

5 min

 

 

5 min

 

 

 

5 min

 

5 min

 

5 min

 

 

 

5 min

 

 

5 min

 

 

Functions

After completing this topic, students will be able to:

 

Ø  Create  and define use define function

 

Ø  Understand different methods to call function

 

Ø  Understand different mechanism to debug

Book Reference (5)

 

 

 

Create Function Statement

Ch: 20

 

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_function.htm#LNPLS01370

a.        Create Store Functions

b.       Function VS Procedure

c.        Debugging Functions

d.       Implementing Functions using Cursors

e.       Implementing Functions Using DML

f.         Calling Functions alone

g.        Calling Functions within procedure

h.       Calling Function within Trigger

i.         Parameter passing in Functions

j.         Implementing Parameterized Functions using Cursor

k.        Implementing Parameterized Functions with DML

5 min

 

5 min

 

5 min

 

5 min

 

 

5 min

 

 

5 min

 

5 min

 

5 min

 

5 min

 

5 min

 

 

 

5 min

 

 

 

Packages

After completing this topic, students will be able to:

 

Ø  Create  and define use define packages

 

Ø  Understand different methods to call function /procedure within Package

 

Ø  Understand different mechanism to debug

Book Reference (5)

 

 

PL/SQL Packages

Ch: 21

 

 

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS009

a.        Package Introduction

b.       Create package specification

c.        Create Package Body

d.       Implementing Package using Functions

e.       Implementing Package using Procedures

f.         Implementing Package using Functions and Procedure

g.        Call Stored Package

h.       Create private objects

i.         Create Package variable and Cursors

j.         Cursor Variables

k.        Extending the Package

l.         Debugging packages

m.      Performance Tuning

5 min

 

5 min

 

5 min

 

5 min

5 min

 

5 min

 

 

5 min

5 min

 

 

5 min

5  min

5 min

5 min

 

 

5 min

5 min

 

5 min

 

5 min

 

 

 

 

Workshop on Procedure and Triggers

After completing this topic, students will be able to :

Ø  Create Simple && Complex Procedures and Triggers alone and combine

Ø  Execute and implement Procedure in correlation with Cursor and Tables

 

 

Practice Scenarios / Questions will be dicsussed

a.        Example -1 : Implementing Procedures using Cursor / Tables / DML using different types of parameters

b.       Example -II : Implementing Triggers  using Cursor / Tables / DML using different types of parameterized Procedures / Functions

 

 

10 min

 

 

 

 

 

10 min