Oracle 8i-9i in Hindi

SQL किसी भी Backend Database की मुख्‍य Query Language होती है और बिना SQL Query Language को सीखे हुए आप कोई भी ऐसा Application Develop नहीं कर सकते, जिसका Data किसी Backend Database में Store होता हो।

इसलिए यदि आप Database आधारित Applications Develop करना सीखना चाहते हैं, तो ये पुस्‍तक आपके लिए काफी उपयोगी साबित होगी। क्‍योंकि इस पुस्‍तक में न केवल SQL को Detail से व विभिन्‍न प्रकार के Examples के साथ समझाया गया है, बल्कि किसी Application की Requirements के आधार पर एक Perfect Database कैसे Design किया जाता है और उस Database को Backend के रूप में किस तरह से Use किया जाता है, इसके बारे में भी काफी विस्‍तार से समझाया गया है। यानी यदि आप एक Professional Database Application Development को Step by Step सीखना चाहते हैं, तो ये पुस्‍तक आपके लिए ही है।

वास्‍तव में ये पुस्‍तक कुल तीन हिस्‍सों में विभाजित है, जहां पहला हिस्‍सा, DBMS Concepts का है, जिसके आधार पर एक Business Requirement के आधार पर Database Design करने से सम्‍बंधित Details को Discuss किया गया है।

इस हिस्‍से को Oracle से पूरी तरह से Separate रखने के लिए हमने इसे एक Separate EBook के रूप में Create कर दिया है। यानी जब आप ये पुस्‍तक खरीदते हैं तो वास्‍तव में आपको इसके साथ DBMS in Hindi नाम की एक और पुस्‍तक Free प्राप्‍त होती है, जिसमें Database Development से सम्‍बंधित विभिन्‍न Concepts को विस्‍तार से Discuss किया गया है।

जबकि दूसरे हिस्‍से में हमने SQL व PLSQL को ढेर सारे Examples के माध्‍यम से काफी सरल भाषा में Discuss किया है व तीसरे हिस्‍से में उस Database को Backend के रूप में Use करने के बारे में विस्‍तार से Discuss किया है, जिसे  दूसरे हिस्‍से के SQL/PLSQL Section द्वारा Develop किया था।

इतना ही नहीं, यदि इस पुस्‍तक के PLSQL Section को छोड दें, तो शेष पुस्‍तक के अन्‍य Concepts को Oracle के अलावा किसी भी अन्‍य Database Software को Use करते हुए Database Application Development हेतु समान रूप से Use किया जा सकता है।

यानी PLSQL केवल Oracle के लिए ही Develop किया गया था, इसलिए यदि हम PLSQL को छोड दें, तो इस पुस्‍तक में Discuss किया गया Database Designing Process व SQL Related सारा Content एक Standard Content की तरह हैं, जिसे सीखने का मतलब ये है कि आप  Microsoft SQL Server, MySQL, DB2, Sybase आदि विभिन्‍न  DBMS Software पर SQL Queries को Exactly समान रूप से Fire कर सकते हैं।

इसलिए यदि आप इस पुस्‍तक को पढते हुए SQL, Database Designing तथा Application Development Process को सीखते हैं, तो किसी भी अन्‍य DBMS Software जैसे कि Microsoft SQL Server, MySQL, DB2, Sybase आदि पर SQL Queries, Database Designing तथा Application Development Process Concepts को Exactly समान रूप से उपयोग में ले सकते हैं।

इस प्रकार से एक Professional Database Application Develop करने से सम्‍बंधित विभिन्‍न जरूरी Steps को Perfect तरीके से समझने हेतु ये पुस्‍तक निश्चित रूप से आपके लिए काफी उपयोगी होगी, जिसमें निम्‍न Contents को Include किया गया है:

Index of Contents – Oracle 8i/9i SQL/PLSQL in Hindi

Oracle – The History

The Relational Database Architecture
       File-Based Systems
        Client/Server Architecture
        Multi-Tire Architecture
Network Computing Architecture
       Clients
        Application Server
        Universal Data Server

Oracle – Architecture

Memory Structures
       The Database Buffer Cache
        Redo Log Buffer
        Shared Pool
        Additional Memory Areas
Processes
      Database Writer (DBWR)
      Log Writer (LGWR)
      System Monitor (SMON)
      Process Monitor (PMON)
      Archiever (ARCH)
      Server Processes
      Listener Process
Database Files
      Control Files
      Parameter Files
      Online Redo Log Files
      Data Files

Data Concurrency and Data Consistency

Data Concurrency
Data Consistency
      Locking Strategies
      Consistency Achievement
      Schemas

Creating a Database

Administration Tools
      Identifying Database
      SQL *Plus
      Server Manager
      NT Instance Manager
Oracle 8i Installation
Configuring The Network
      Tnsnames.ora
      Listener.ora

Application Development Process

Finding The Requirements Of The Application
Designing The Database
Designing The Application
Enforcing Security
Performance Tuning
Maintaining and Updating

Database Design

Conceptual Design
      Entity-Relationship Diagrams
Logical Design
      Identifying the Record Types and Fields
      Identifying Any Data Dependencies
      Normalizing The Database
      Database De-Normalization
      Keys

Database Design – An Example from Start To End

Entity Relationship Modeling
      Step 1 – Finding Database Application Related Entities
      Step 2 – Determining Relationship between Entity Pairs
      Step 3 – Determining Relationship Nature
      Step 4 – Resolving Many To Many Relationship
      Step 5 – Foreign Keys For Enforcing Relationships
      Step 6 – Entity Attributes Setup
Database Normalization
      First Normal Form ( FNF or 1NF )
      Second Normal Form ( SNF or 2FN)
      Third Normal Form (TNF or 3NF)

Database Implementation

Tablespace Creation
      Creating Tablespace
      Optional Parameters
Table Creation
      Oracle Data Types
      Creating Tables
      USER_CONSTRAINTS Table
      Table Modification
      Data Dictionary
Index Creation
      Creating Index
Sequences Creation
      Creating Sequence
      Maintaining Sequence
Audit Trails
Other Schema Objects
      Clusters
      Views
      Synonyms
User Access Controlling
      Privileges
      Schemas
      System Privileges
      Role
      Object Privileges

Accessing Data – SQL Queries

SQL Overview
Types Of SQL Commands
      Query Commands
      Data Definition Language (DDL) Commands
      Data Control Language (DCL) Commands
      Data Manipulation Language (DML) Commands
      Transaction Control Language (TCL) Commands
      Session Control Commands
      System Control Commands
      Embedded SQL Commands
SELECT Command
      SELECT Syntax
      Using Dual
      Getting Selected Rows
      Sorting The Selection
Useful Operators
      = Equality Test
      != Inequality Test (Not Equal To)
      > Greater Than
      < Less Than
      >= Greater Than OR Equal To
      <= Less Than OR Equal To
      | | Character String Concatenation
      AND Returns TRUE If Both Conditions Are TRUE Otherwise FALSE
      OR Returns TRUE If Either Conditions Is TRUE Otherwise FALSE
      NOT Returns TRUE If Condition Is False and Vise Versa
      BETWEEN a AND b
      IN
      IS NULL
      LIKE
Calculated Fields
Oracle-Specific Functions
      SYSDATE
      USER
      TO_CHAR()
      TO_DATE()
      CONCATE()
      UPPER()
      LOWER()
      INITCAP()
      DECODE Function
GROUP Function
      AVG (Column_Name)
      COUNT (Column_Name)
      MAX (Column_Name)
      MIN (Column_Name)
      STDDEV(Column_Name)
      SUM(Column_Name)
      VARIANCE(Column_Name)
GROUP BY Clause
HAVING Clause
Join – Query with More Than One Table
      Outer Join
Subqueries
      Subqueries That Return Only One Value
      Subqueries That Return More Than One Row
Database Optimizer
Efficient SELECT Statements
Specifying Schemas

Updating Database

Tables Updating Philosophy
      INSERTING Rows
      UPDATING Rows
      DELETING Rows
Transaction Control
      COMMIT
      ROLLBACK
      SAVEPOINT name
      ROLLBACK TO SAVEPOINT name
Database Triggers
CREATE TABLE AS Subquery
Database Design For Fast Updates
Date Data Type

Complete SQL – Structured Query Language

iSQL *Plus Terminal
Making Simple Queries
Making Conditional Queries
Making Sorted Queries
Character Functions
      CONCAT Function
      INITCAP Function
      LOWER and UPPER Function
      LPAD and RPAD Function
      SUBSTR Function
      LTRIM and RTRIM Functions
      INSTR Functions
      LENGTH Functions
Numerical Functions
      MOD Functions
      POWER Functions
      ROUND Functions
      SIGN Functions
      SQRT Functions
      TRUNC Functions
Date Functions
      ADD_MONTHS Functions
      LAST_DAY Functions
      MONTHS_BETWEEN Functions
      NEXT_DAY Functions
Making Aggregate Queries
Making Join Queries
      Cartesian Products
      Equi – Join
      Non-Equi-Join
      Outer Join
      Self Join
      Creating Cross Joins
      Creating Natural Joins
      Left Outer Join
      Right Outer Join
      Full Outer Join
Making Subqueries
Making DML Queries
      State of the Data Before COMMIT and ROLLBACK
      State of the Data after COMMIT
      State of the Data after ROLLBACK

Introduction to PL/SQL

PL/SQL – The Extension of SQL
      Advantages Of PL/SQL
      PL/SQL Execution Environment
Stored Procedures
      Stored Procedures – The Advantages
      Procedure Structure
      Function Structure
      Trigger Structure
      Procedure Parameters
Packages
      Package Creation
      Built-In Packages
Stored Procedures Creating Tools
      SQL *Plus
      Visual Database Tools
      Oracle Procedure Builder
      Error Handling in PL/SQL
      Enhancing The Error Message

Creating Stored Procedures

PL/SQL Data Types
      Scalar Data Types
      User-Defined (Composite) Variables
Declaring Variables
      %TYPE and %ROWTYPE
Declaring Constants
Statements and Assignments
Flow Control Statements
      Decision Flow Control
      Loops
Using SQL Statements In PL/SQL
      Update Statements
      Using SELECT INTO
      Exceptions In SELECT INTO Statement
PL/SQL Cursors
      Declaring A PL/SQL Cursor
      Opening And Closing A PL/SQL Cursor
      Fetching A PL/SQL Cursor
      Error Conditions for PL/SQL Cursors
      PL/SQL Cursor In Action
      PL/SQL Cursor Loops
User-Defined Data Types
      Records
      PL/SQL Tables and Collections
Cursor Variables
      Declaring a Cursor Variable
      Opening A Cursor Variable
      Fetching Records
      Closing The Cursor Variable
      Passing Cursor Variables
Using Triggers
Providing a Transaction Audit
Procedures and Visual Basic
      Data Controls
      Data Access Objects (DAO)
      Remote Data Objects (RDO)
      ActiveX Data Objects (ADO)
      Oracle Objects or OLE (OO4O)

Distributed Databases

Overview
Centralized and Client-Server Architecture
Parallel Systems
Distributed Systems

Client-Side Technology

The Software Layers
      The ODBC Driver Manager
      ODBC Driver
      The OLE-DB Layer
      Oracle Call Interface
      Net8 and SQL *Net
      The Network Softwares
ODBC
      The Origins Of ODBC
      ODBC Drivers
      Selecting An ODBC Driver
      Setting Up An ODBC Data Source
      Creating An ODBC Connection String
      Testing Of ODBC Connection
OLE-DB
      Data Providers
      Data Consumers
      OLE-DB Cursors
      How Cursor Works?
      Choosing A Cursor
Connections
      DSN-Less Connections
      Closing An ODBC Connection
      ODBC Connection Pooling
      Pre-Started Connections
Alternative Access Methods
      Oracle Pre-Compilers
      Oracle Objects for OLE

Accessing Oracle from Visual Basic

The JET Engine
Data Access Objects
Data Control
Remote Data Objects
ODBCDirect
The ODBC API
ActiveX Data Objects
Oracle Objects for OLE

Oracle Objects for OLE

The OO4O Object Hierarchy
      OraClient
      OraSession
      OraConnection
      OraDatabase
The Early Binding Issue
Accessing Database
      BeginTrans
      CommitTrans
      ConnectSession
      CreateNamedSession
      LastServerErrResetSession
      OpenDatabase
      ResetTrans
      Rollback
Connection With Database
      Database_Name
      Connect_String
      Options
Database Object Methods
      Close
      CreateCustomDynaset
      CreateDynaset
      CreatePLSQLDynaset
      CreatePlsqlCustomDynaset
      CreateSQL
      ExecuteSQL
      LastServerErrReset
Creating OraDynasets
      Source
      Options
      Pessimistic Locking
Using Triggers
Tuning The OraDynaset
      Cache Parameters
      Fetch Parameters
OraDynaset Properties
      BOF
      Bookmark
      BookMarkable
      CacheBlocks
      CacheChanged
      CacheSliceSize
      CacheSizePerBlock
      Connection
      Database
      EditMode
      EOF
      FetchLimit
      FetchSize
      Fields
      LastModified
      NoMatch
      Options
      RecordCount
      RowPosition
      Session
      SQL
      Transactions
      Updatable
Exploring The OraDynaset Methods
      Populating OraDynaset
      Changing The Current Record
      Accessing the Fields Of An OraDynaset
      Changing the Records and Values in an OraDynaset
      Finding A Particular Record
      Closing A Dynaset
Using Parameters
      Name
      InitialValue
      Type
Executing SQL Commands
Calling Stored Procedures
Retrieving Cursor Variables
      SQLStatement
      CursorName
      Options
Calling PL/SQL Functions
Retrieving PL/SQL Tables
      Name
      Type
      ServerType
      ArraySize
      ElementSize
Using OraSQLStmts
      Sql_Statement
      Options
Batch Updates
Error Handling
Putting It All Together
Viewing The Structure Of A Database
      OralDataType
      OraMaxDSize
      OraMaxSize
      OraNullOK
      OraPrecision
      OraScale
      Size
      Truncated
      Type
      Value

ActiveX Data Objects (ADO)

ADO Object Model
      Connection Object
      Recordset Object
      Command Object
      Parameter Object
      Field Object
      Error Object
      Property Object
Connecting To Oracle
      Connection Object Methods
      Connection Object Properties
      Opening Connection
      2-Ways For Opening Connection
      ADO Events
      WithEvents
Working With Recordset
      Opening Recordset
      Populating The Recordset
      Fetching Multiple Rows
      ADO Recordset Navigation
      Changing The Rows and Values In A Recordset
      Searching A Record In Recordset
      Closing A Recordset
      Recordset Events
The Command Object
      Command Properties
      Command Parameters
      Command Object Properties
      Executing SQL Commands
Stored Procedures
      Calling Stored Procedures

Index of Contents – DBMS/RDBMS in Hindi

Database Management System

Introduction
Problem – Doing Something
System – Interrelated Group of Units to Solve a Problem

Database Management System – DBMS

Requirement of Good Database Design
Effects of Poor Database Design
      Unnecessary Duplicated Data and Data Consistency
      Data Insertion Problems
Data Deletion Problems
Meaningful Identifiers
Entities and Data Relationships
Entities and Their Attributes
Entity Identifiers
Single-Valued and Multi-Valued Attributes
Identifying Problem Related Entities
Documenting Logical Data Relationships
Entities and Attributes for Music Store Application
Domains
      Documenting Domains
      Practical Domain Choices

Basic Data Relationships

One To One Relationships
One To Many Relationships
Many To Many Relationships
Weak Entities and Mandatory Relationships
Documenting Relationships
Dealing with Many To Many Relationships
Composite Entities
Relationships and Business Rules
Data Modeling and Data Flow

Schema

Tables
      Primary Keys
      Composite Keys
      Representing Data Relationships
      Referential Integrity
Views
Data Dictionary

Normalization

Translating an ER Diagram into Relations
Normal Forms
      First Normal Form
      Second Normal Form
      Third Normal Form
      Boyce-Codd Normal Form
      Forth Normal Form
Equi – Join

Database Structure and Performance Tuning

Indexing
Clustering
Partitioning