Module 1: Relational Model

Introduction

• Database Systems Applications
• Database Systems versus File Systems
• View of Data
• Data Models
• Database Languages
• Database Users and Administrators
• Transaction management
• Database System Structure
• Application Architectures
• History of Database Systems

Entity Relationship Model

• Basic Concepts
• Constraints
• Keys
• Design Issues
• Entity-Relationship Diagram
• Weak Entity Sets
• Extended E-R Features
• Design of an E-R Database Schema
• Reduction of E-R Schema to tables

Relational Model

• Structure of Relational Databases
• The relational Algebra
• Extended Relational-Algebra Operations.
• Modification of the database
• Views
• The Tuple Relational Calculus
• The Domain Relational Calculus

Relational Database Design

• First Normal Form
• Pitfalls in Relational Database Design
• Functional Dependencies
• Decomposition
• Boyce-Codd Normal Form
• Third Normal Form
• Fourth Normal Form
• More Normal Forms
• Overall Database Design Process

Oracle 10g as a RDBMS

• System Development Life cycle
• Data storage in different media
• Relational database concepts
• Definition of Relational Database
• Data models
• Entity Relationship Model
• Entity Relationship Modelling conventions
• Relational Database Terminology
• Relating multiple tables
• Relational Database Properties
• Communicating with RDBMS using SQL
• Relational Database Management System
• Oracle 10g – Object Relational Database Management System
• Oracle 10g – Internet Platform Database for Internet Computing Features
• Oracle Internet Platform
• SQL statements
• About PL/SQL
• PL/SQL environment

Module 2: SQL

Writing Basic Select Statement

• Capabilities of SQL SELECT Statement
• Basic SELECT statement
• Writing SQL Statements
• Selecting all columns
• Selecting Specific Columns
• Column heading defaults
• Arithmetic Expressions
• Using Arithmetic Operator
• Operator Precedence
• Using Parentheses
• Defining a NULL Value
• NULL Values in Arithmetic Expressions
• Defining a Column Aliases
• Concatenation Operator
• Using Concatenation Operator
• Literal Character Strings
• Duplicate Rows
• Eliminating Duplicate Rows
• SQL and SQL * Plus Interaction
• SQL Statements Versus SQL *Plus Commands
• Overview of SQL*Plus
• Logging In to SQL *Plus
• Displaying Table Structure
• SQL *Plus Editing commands
• SQL *Plus File Commands

Restricting and Sorting Data

• Limiting Rows Using a Selection
• Limiting Rows Selected
• Using the WHERE Clause
• Character Strings and Dates
• Comparison Operators
• Using BETWEEN, IN, LIKE, IS NULL operator
• Using Logical Operators
• Rules of Precedence
• ORDER BY Clause

Single-Row Functions

• SQL Functions
• Two Types of SQL Functions
• Single-Row Functions
• Character Functions
• Case Conversion Function
• Character Manipulation Function
• Number Function
• Working with Dates
• Conversion Function
• NVL function
• DECODE function

Displaying Data from Multiple Tables

• Obtaining Data from multiple tables
• What is a join?
• Cartesian Product
• Generating a Cartesian product
• Type of joins
• Equijoin
• Non-equijoin
• Self Join
• Outer join

Aggregating Data Using Group Functions

• What are group functions?
• Types of group functions
• Using group functions
• Creating Groups of Data
• Group by clause on multiple columns
• Illegal queries using group function
• Excluding group results
• Nesting group functions

Subqueries

• Using a subquery to solve a problem
• Using a subquery
• Guidelines for using a subquery
• Types of subqueries
• Executing single row subquery
• Using group function in a subquery
• Having clause with subqueries
• Multiple row subquery
• Using ANY/ALL operator

Multiple Column Subqueries

• Using multiple column subqueries
• Column Comparison
• Non-pairwise comparison subquery
• Pair wise subquery
• NULL values ina subquery
• Using subquery in the FROM clause

DML Statement

• Data Manipulation Language
• Adding a New Row to a Table
• The INSERT Statement
• Inserting New Rows
• Inserting Rows with NULL values
• Inserting Special Values
• Inserting Specific Date Values
• Inserting Values by Using Substitution variables
• Creating a Script with customized Prompt
• Copying rows from another table
• Copying data in a table

• The UPDATE statement
• Updating rows in a table
• Updating with multiple-column subquery
• Updating rows based on another table
• Updating rows – integrity constraint error
• Removing arrow from the table
• The DELETE Statement
• Deleting Rows from a table
• Deleting rows based on another table
• Deleting rows :Integrity constraint error

Database Transaction

• Advantages of COMMIT & ROLLBACK statements
• Controlling Transactions
• Implicit Transaction Processing
• State of Data before COMMIT or ROLLBACK.
• State of Data after COMMIT or ROLLBACK.
• Rolling Back Changes to a Marker
• Statement level roll back
• Read Consistency
• Implementation of Read Consistency
• Locking

Creating Tables

• Database Objects
• Naming Conventions
• The CREATE TABLE Statement
• Referencing Another User’s Tables
• The DEFAULT option
• Creating Table
• Tables in ORACLE database
• Querying the data dictionary
• Datatypes
• Creating a Table by Using a subquery
• The ALTER TABLE statement
• Adding a column
• Modifying a column
• Dropping a Tables
• Changing the name of an Object
• Truncating a table
• Adding comments to a table

Including Constraints

• What are constraints?
• Constraint guidelines
• Defining constraints
• The NOT NULL Constant
• The UNIQUE KEY Constraint
• The PRIMARY KEY Constraint
• The FOREIGN KEY Constraint
• FOREIGN KEY Constraint Keywords
• The CHECK Constraints
• Adding a constraint
• Dropping a Constraint
• Enabling/disabling a constraint
• Cascading constraint
• Viewing the columns associated with constraints.

Creating Views

• Database Objects
• What is a view?
• Why use views?
• Simple views and complex views
• Crating a view
• Retrieving Data from a view
• Querying a view
• Modifying a view
• Creating a complex view
• Rules for performing DML operations on a view
• Using the with CHECK option clause
• Denying DML operations
• Removing a view
• Inline view
• “TOP-N” analysis

Other Database Objects

• Database Objects
• What is sequence?
• CREATE SEQUENCE statement
• What is an index?
• Creating an index
• Function based index
• Removing an index
• Synonyms
• Creating and removing synonyms

Controlling user access

• Controlling user access
• Privileges
• System privileges
• Creating users
• User System Privilege
• Granting System Privilege
• What is a Role?
• Creating and Granting privileges to a Role
• Changing your password
• Object Privileges
• Granting Object Privilege
• Using WITH GRANT OPTION and PUBLIC Keywords
• Confirming Privileges Granted
• Revoking privileges

Module – 3: P/L SQL

Declaring Variables

• About PL/SQL
• Benefits of PL/SQL
• PL/SQL Block Structure
• Block types
• Program constructs
• Use of variables
• Handling variable in PL/SQL
• Types of variables
• Declaring PL/SQL variables
• Naming Rules
• Assigning Values to Variables
• Varchar initialisation and keywords
• Scalar datatypes
• Base Scalar Datatypes
• Scalar variable declarations
• The %TYPE Attribute
• Declaring Variables with the %TYPE Attribute
• Declaring Boolean Variables
• PL/SQL record structure
• LOB Datatype Variables
• Bind Variables
• Referencing Non-PL/SQL Variable
• DBMS_OUTPUT.PUT_LINE

Writing Executable Statements

• PL/SQL Block Syntax and Guidelines
• Commenting Code
• SQL Function in PL/SQL
• PL/SQL function
• Datatype Conversion
• Nested Blocks and Variable Scope
• Operators in PL/SQL
• Using Bind Variable
• Programming Guidelines
• Code naming conventions
• Indenting Code
• Determining Variable Scope

Interacting with Oracle Server

• SQL Statements in PL/SQL
• SELECT statement in PL/SQL
• Retrieving Data in PL/SQL
• Manipulating Data using PL/SQL
• Inserting Data
• Updating Data
• Naming Conventions
• COMMIT & ROLLBACK Statements
• SQL Cursor
• SQL Cursor Attributes

Writing Control Structures

• Controlling PL/SQL Flow of Execution
• IF Statements
• Simple IF Statement
• IF-THEN-ELSE Statement Execution Flow
• IF-THEN-ELSE Statement
• IF-THEN-ELSEIF Statement
• IF-THEN-ELSEIF Statement
• Building Logical Conditions
• Logic Table
• Boolean Condition
• Iterative Control: LOOP Statements
• Basic Loop
• FOR Loop
• WHILE Loop
• Nested Loops and Labels

Working with Composite Datatypes

• PL/SQL Records
• Composite Datatypes
• PL/SQL Records
• Creating a PL/SQL Record
• PL/SQL Record Structure
• The %ROWTYPE Attribute
• PL/SQL Tables
• Creating a PL/SQL table
• PL/SQL Table Structure
• Creating a PL/SQL Table
• Using PL/SQL Table Methods
• PL/SQL Table of Records
• PL/SQL Table of Records

Writing Explicit Cursors

• Explicit Cursor Functions
• Controlling Explicit Cursors
• Declaring the Cursor
• Opening the Cursor
• Fetching Data from the Cursor
• Closing the cursor
• Explicit Cursor attribute

Advanced Explicit Cursor Concepts

• Cursors with Parameters
• The FOR UPDATE Clause
• The WHERE CURRENT OF clause
• Cursor with Subqueries

Handling Exceptions

• Handling Exceptions with PL/SQL
• Handling Exceptions
• Exception Types
• Trapping Exceptions
• Trapping Exception Guidelines
• Predefined Exceptions
• Non-predefined exception
• User-defined exception
• Calling Environment
• RAISE_APPLICATION_ERROR Procedure

Overview of PL/SQL

• Oracle Complete Solution
• PL/SQL Program Construct
• Block Structure for Anonymous PL/SQL Blocks
• Block Structure for PL/SQL Subprograms
• Benefit of Subprograms
• Development Environment

Creating Procedures

• Overview of Procedures
• Syntax for creating Procedures
• Procedural Parameter Mode

Creating Functions

• Overview of Stored Functions
• Syntax for creating functions
• Creating a function
• Creating a Stored Function using SQL * Plus.
• Executing Functions
• Invoking Functions in SQL Expressions: Restrictions
• Removing Functions

Creating Package

• Component of a package
• Referencing Package Objects
• Developing a Package
• Creating the Package Specification
• Declaring Public Constructs
• Creating package specification
• Public and private constructs
• Creating package body
• Invoking package constructs
• Global Variables
• Referencing a public variable from a stand alone procedure
• Removing package
• Developing packages
• Advantages of packages

More package concepts

• Overloading
• Forward Declarations
• Creating a One-Time-Only Procedure
• Restrictions on Package Functions used in SQL
• PL/SQL Compilation checking
• Purity level of a package function
• PRAGMA RESTRICT_REFERENCES
• Invoke a User-Defined Package Function from a SQL Statement
• Persistent State: Package Variable
• Persistent State: Package PL/SQL Tables and Records

Oracle Supplied Packages

• Using DBMS_PIPE
• Dynamic SQL
• Execution flow
• DBMS_SQL
• DBMS_DDL
• DBMS_JOB
• DBMS_OUTPUT

Creating Database Triggers

• Designing triggers
• Creating triggers
• Trigger component
• Firing sequence
• INSTEAD OF trigger
• DROP Trigger

Module – 4: Project Work

• Designing and Deploying a Database Solution