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
|
|
|
Top |
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
|
|
|
Top |
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
|
|
|
Top |
Module – 4: Project Work
|
|
|
|
- Designing and Deploying a Database Solution
|