Sequences

A sequence is a database object , which can generate unique., sequential integer values.it can be used to automatically generate primary key or unique key values.

Create Sequence [sequence_name]Increment by [no]Start with [no[Maxvalue [no]Minvalue [no] Cycle cache [no]

1.Increment by no
No is an integer which specifies the interval between sequence numbers.

2.Start with no
It spcifies the first sequence number to be generatedminvalue no - specifiesthe minimum value of the sequence.

3. Maxvalue no
Specifies the maximum value of the sequence.

4.Cycle
Specifies that the sequence continues to generate values from the beginning after reaching either its max or min value.

5.Nocycle
Specifies that the sequence cannot generate more values after reaching either its maxvalue or minvalue.the default value is ' no cycle'.

6.Cache
The cache option pre-allocate a set of sequence number and retains them in memory so that sequence numbers can be accessed faster.

7.Nextval
Nextval returns initial value of the sequence , when referred to for the first time , later references to nextval will increment the sequence using the increment by clause and return the new value.

8.Curval
Curval returns the current value of the sequence which is the value returned by the last referenced to nextval.

Alter Sequence Procedure

The sequence can be altered when we want to perform the following :

a) Set ( or ) reduce minvalue or maxvlue.

b) Change the increment value.

c) Change the number of cached sequence numbers.

Data dictionary table for sequence User_Sequences will provide details of sequences that the user has created.

Views

A view is tailored presntation of the data contained in one or more tables ( or other views ).

A view tackes the output of a query and treats it as a table.

Advantages of Views

1) They provide an additional level of table security by restricting accessto a predeterminded set of rows and / or columns of a table.

2) They hide data complexity


3) They simplify commands for the user because they allow to select informationfrom multiple tables without actually knowing how to perform a join .


4) They separate applications from changes idefinitions of base tables.


5) They provide data in a different perspective than that of a base table by renaming columns without affecting base table.

View Creation Option

1) Check Option

When creating a view will allow to user to update only noted rows ,which are viewable through this view and give an error when user try to override it.


2) Creating Views with Errors

Oracle will create the view even if the veiw defining query refers to anon - existing table ( or ) an invalid column of an existing table or when the view owner does not have the required privilages .To create a view with errors , include the FORCE option in the CREATE VIEW command.



Data Dictionary Table for Views User_Views will provide details of views that the user has created.

Synonym

A Synonym is a database object , which is used as an alias [alternative name] for a table, view, sequence . They are are used to

· Simplify the SQL statemnt.
· Hide the name and owner of an object
· Provide Location transparency for remote objects of a distributed database.
· Provide public access to an object.

Synonym can either be private or public

Public synonyms are created by a Database Administrator to hide the identity of a base table and reduce the complexity of SQL statement .

Data Dictionary Table for synonym User_Synonyms will provide details of synonyms that the user has created.

SQL Functions

SQL functions are pre-defined program to perform specific tasks. These functions can receive values from outside in the form of Parameters and they return one value. SQL groups functions into two major categories:

1) Group Functions:

Those functions that process multiple records to give single record output. Some of the main group functions are:

a) SUM ()
b) AVG ()
c) MAX ()
d) MIN ()
e) COUNT ( * )

2) Row Functions:

Those functions that work on each record and give output for each record. Row functions are further categories into five groups:

a) Number Functions:
Input values are number and return value is number.

b) Character Functions:
Input values or Return value, any one is character.

c) Date Functions:
Input value or Return value, any one is Date.
d) Conversion Functions
Changes data type of Input value without changing the value.
e) Other Functions:
Input values or their data types or return value type are not fixed.

Restriction on Group Function:

· Group Functions are allowed only with SELECT & HAVING clauses
· Group Function can not be combined with Row Functions or Individual Columns
· Group Functions does not consider NULL value records

GROUP BY Clause:

This clause is used to group related records based on a column or set of columns. Group By clause is very useful for Group Functions as it allows the use of Row Functions and Individual Columns with Group Functions.

Syntax:

GROUP BY

HAVING Clause:

This clause is used to put conditions on the group of records. HAVING clause can not be used without GROUP BY clause as groups of records are provided by it. HAVING conditions are same as WHERE clause conditions but it allows the use of group functions directly.

Number Functions:

a) ABS ((n))
Returns absolute (positive) value.

b) SIGN ((n))
Returns 1 if value is positive, -1 if value is negative and 0 if value is zero.

c) POWER ((n),(p))
Returns value raised up to times.

d) MOD ((n),(d))
Returns remainder of division. is divided by .

e) ROUND ((n) [,(d>))
Returns rounded off value up to specified number of decimal digits. If is not defined then no decimal digit.

f) TRUNC ((n), [,(d)])
Same as ROUND function but does not apply rounding off logic when truncating decimal digits.

g) CEIL ((n))
Returns smallest integer but greater than or equal to value.

h) FLOOR ((n))
Returns greatest integer but less than or equal to value

Character Functions:


a) SUBSTR ((string), (start) [,(no.)])
Returns (no.) from (string) starting from (start).

b) LENGTH ((string))
Returns count of characters within the (string).

c) INSTR ((string-1), (string-2))
Returns position of (string-2) within (string-1), if string-2 is not found then zero.

d) UPPER ((string))
Returns the string with alphabets converted into capital letters.

e) LOWER ((string))
Returns the string with alphabets converted into small letters.

f) LTRIM ((string) [,(char)])
Returns the string removing the from the left side of the string. If (charset) is not given then removes all leading spaces.

g) RTRIM ((string) [,(char)])
Returns the string removing the from the right side of the string. If (char) is not given then removes all trailing spaces.

h) LPAD ((string), (output) [,(filler)])
Returns the string within the

. If string is smaller than the size then (filler) is used to fill the left blank spaces. Default filler is space.

i) RPAD ((string), (output) [,(filler)])
Returns the string within the

. If string is smaller than the size then (filler) is used to fill the right blank spaces. Default filler is space.

j) INITCAP ((string))
Returns the string with each word starting with upper case letter and rest in lower.

k) ASCII ((char))
Returns the ASCII value of character.

l) CHR ((ascii))
Returns the character equivalent for given ascii value.

m) REPLACE ((string), (pattern) [,(replace)])
Returns the string replacing the (pattern) with (replace). If replace string is not defined then (pattern) is removed from the string.

n) TRANSLATE ((string), (char), (translate)
)Returns the string translating the (char) characters with character from (translate).

Date Functions:

a) ADD_MONTHS ((date), (no.))
Adds number of months to the date and return a new date. If no. of months is negative then it subtracts the months from date.

b) MONTHS_BETWEEN ((date-1), (date-2))
Returns difference of number of months between both the dates.

c) SYSDATE
Returns the system date.

d) LAST_DAY ((date))
Returns a new date with the last day of the month.

e) NEXT_DAY ((date), (day))
Returns a date for the next day from the date mentioned.

Conversion Functions:

a) TO_CHAR ((date/number) [,(output)])

It converts number or date values in character. Output format is used to change the date format or number formats.

b) TO_DATE((chars/numbers) [,(input format)])
It converts character or number into date. Output format is used to receive the date or number in different format and to convert them into default date format of DD-MON-YY.

c) TO_NUMBER ((char))
It converts a character value containing valid numeric value into number value.

Other Functions:

a) NVL ((value), (return))
This function is to check for NULL and to return a value instead of NULL value. Value and Return Value both must be of same data type.

b) GREATEST ((value))
Returns the largest value from the list of values.

c) LEAST ((value))
Returns the smallest value from the list of values.

d) USER
Returns the current user name.

DECODE DECODE (value) {,(pattern) , (value)} […] [,(default value)])
This function is an additional feature from Oracle to SQL. It is used to check pattern with value and returns a value for each pattern. If none of the pattern matches with the value then a default value can be defined that is optional.

Set Operators & Joins

SET OPERATORS

Set operators are used to combine output of two queries. Both the queries must return same number of output and corresponding value must of same data types. Following four types of set operators are provided by SQL:

1) Union:

Returns unique values available in any of the sets (queries).

2) Intersect:

Returns unique values common to both the sets (queries).

3) Minus:

Returns unique values available in first set but not in second set.

4) Union All:

Returns values from both the sets without suppressing duplicity.

JOINS

A join query is the most important method of solving problems involving two or more tables or single table based on Self Join. Join query overcomes the problem of sub-query of not able to display values from more than one table. Join query based solutions relate tables in Parent-Child hierarchy.

SQL provides four types of Join Query:

1) Equi Join:

When two tables are related to each other based on common column(s).

2) Non-equi Join:

When two tables are related to each other based on values instead of common columns.

3)Self Join:

When single table keeps data related to two entities and two records of same table are related to each other as Parent-Child.

4) Outer Join:

When a join query has to fetch matching records along with non-matching records from tables.

The syntax of the outer join is a bit different from that of the inner join, because it includes a special operator called the outer join operator. The outer join operator is a plus sign enclosed in parentheses, i.e., (+). This operator is used in the join condition in the WHERE clause following a field name from the table that you wish to be considered the optional table. In our suppliers and parts example, the PART table doesn't have information for one supplier. Therefore, we will simply add a (+) operator to the join condition on the side of the PART table. The query and the result set look as follows:

Select s.supplier_id, s.name supplier_name, p.part_nbr, p.name part_namefrom supplier s, part p where s.supplier_id = p.supplier_id (+);

The outer join operator (+) can appear on either the left or the right side of the join condition. However, make sure you apply this operator to the appropriate table in the context of your query.

Join Condition:

A join query must have a join condition that relates one table to another table. Join condition are given with the query, the programmer based on the tables required for the query decides them. Generally number of join conditions are equal to number of tables less one:

Number of Join Conditions = Number of Tables – 1

A join query without join conditions returns number of records equal to product of all records from all tables.

Cartesian Product

If you don't specify the join condition while joining two tables, Oracle combines each row from the first table with each row of the second table. This type of result set is called as a Cartesian product. The number of rows in a Cartesian product is the product of the number of rows in each table.

Here's an example of a Cartesian product:

Select E.Lname, D.Name From Employee E, Department D;

Data Query Language (DQL)

Data Query Language (DQL)

This sub-language deals with retrieval of data from database and it includes SELECT command.

Syntax to Fetch Records from Table:

SELECT * []
FROM

[

]
[WHERE ]
[START WITH ]
[CONNECT BY PRIOR ]
[GROUP BY
[HAVING ] ]
[ORDER BY ];

Relational Operators:

Relational operators are pre-defined programs that are used in defining condition during data retrieval from tables. Relational Operators take input of values at left and right side and after evaluation they return Boolean value TRUE/FALSE. SQL provides two group of relational operators:

· Basic Relational Operators (=, <>, >, <, >=, <=) · Advanced Relational Operators (IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE,NOT LIKE, IS, IS NOT, ANY, ALL) Syntax for Advanced Relational Operators:











Note:

Two special characters are used to define with LIKE operator. These characters are:

% (Percent) - It represent all characters or rest of characters,
_ (Underscore) - It represent single character.

Some of the pattern examples are:

‘A%’ - Starting with alphabet A,
‘%A’ - Ending with alphabet A,
‘%A%’ - Any where search for alphabet A,
‘_A%’ - Second character must be alphabet A,
‘%A_’ - Second last character must be alphabet A,
‘_ _ _’ - Searches for only three characters

Concatenation Operator:

This operator is used to combine two values as single value. Two vertical bar () are used for concatenation operator. Syntax:


Example: SELECT ename job FROM emp;

Logical Operators:

Logical operators are used to combine two or more conditions. SQL provides three types of logical operators:

a) NOT

It reverse the meaning of condition. This operator is used before the condition.

b) AND

It joins two conditions and returns true when both conditions are true else it returns false.

c) OR

It joins two conditions and returns true if any of the conditions are true. If both conditions are false then it returns false.

Sub-query:

A query within another query is known as sub-query. A sub-query problem is needed in following cases:

a) When output columns are from one table but columns for conditions are from another table or from more than one tables.

OR

b) When output columns are from one record while columns for conditions are from another record or from more than on records.

A sub-query problem may have following type of queries:

a) Root Query:

The top most queries are root query. It can be SELECT, DELETE, UPDATE or INSERT statements.

b) Parent Query:

A query having a sub-query under it is known as Parent Query. Every Root Query is a parent query. Only SELECT statement can be a parent query.

c) Child Query:

A query under any other query is child query. Only SELECT statement can be a Child query.

A sub-query can be defined with following clauses of a parent query:

--> FROM --> WHERE --> START WITH --> HAVING --> SET


Types of Sub-query:

There are two types of sub-query:

a)Independent or Simple Sub-query:

This type of sub-query is processed once for its parent query and parent query starts the process with the output of sub-query.

b)Correlated Sub-query:

This type of sub-query is processed for each record of its parent query. Correlated Sub-query uses columns from its parent query within its condition or output.

Constraints

A constraint is rule or check or validation defined on the database. Oracle provides two methods of applying validations or business rules on database. First in the form of Constraint and other is Database Trigger. Oracle provides following constraints:

Types of Integrity Constraints

1. Domain Integrity Constarints

These constaints set a range , and any violations that take place will prevent the user from performing the manipulation that caused the breaking the rule.

Types of Domain Integrity Constraints

a) Not Null Constraints

Not Null constraint is enforced though , either on a column or a set of columns in a table , it will not allow Null Value Programmer should mind following rules.

# Not Null integrity constraint can be defined using alter table command even when the table contains rows # Zero and Null are not equivalent # One Null is not not equivalent to another null.

b) Check Constraints

Check constraint can be defined to allow only a particular range of values .

2. Entity Integrity Constarints

An entity is any data recorded in a database . Each row in a table can be uniquely identified using the entity constraint

Types Entity Integrity Constarints

a) Unique Constraints

Usage of the unique key constraint is to prevent the the duplication of values. Column defined with unique constraint can also allow Null values.

b) Primary Key Constraints

The Primary Key constraint avoids duplication of rows and does not allow Null values

3. Referential Integrity Constarints

To establish a ' Parent - Child ' (or) ' Master - Detail ' relationship Between two tables having a common column .

To impliment this constraint should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent key .
Basic Concept related to referential integrity are :

a) Foreign Key

A column or combination of columns included in the definition of relation integrity which would refer referenced key.

b) Referenced Key

It is a unique (or) a primary key which is defined on a column belonging to the 41 Child Table.

This table depends upon the values present in the referenced key of the parent table , which is referred by a foreign key .

Parent Table

This table determines if insertion or updation of data can be done in child table .This table would be referred by child's table foreign key .

On Delete Cascade Clause

The ' On Delete Cascade ' clause specifies that Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary key value is removed .

Deferrable Constraints

To check for constraint violations a setting called defereed constraint can be set. Enabling and Disabling of constraints are also permitted. Programmers must concider the following conditions when would be crossed to the Deferrable constraints.

i) Deferrable intially immediate - this checks for constraint violation at the time of insert.

ii) Deferrable intially deferred - this checks for constraint violation at the time of commit;

iii) Non Defferable intially immediate - this is the default condition which need not be specified .

Enforcing Constraints

Enforcement is applied to any future inserts and updates , but doed not care about data already in the table . This option is very useful when enabling a constraint on a very table since it avoids the considerable effort of checking every row .

Table Level Constraints

Table Level constraint is part of the table definition . An Integrity Constraint defined at table level can force rules on any columns in the table .

Column Level Constraints

Column level constraint being a part of the column definition can be forced only on the column on which it is defined . The ' Not Null ' constraint can be given only at column and not table level.

SQL (Structured Query Language)

This language is developed by IBM but commercial use started by Oracle. This language has been defined as the standard query language for RDBMS servers. SQL language works on the following two concepts:

· What to do? Is provided by the users, and
· How to do? Is pre-defined within the SQL and it decides the path of execution using its

own logic.

SQL*PLUS:

This is a front-end tool provided by Oracle that interacts with Database Server and allows the users (DBAs, Programmers & Operators) to work with SQL and PL/SQL. From SQL*PLUS we can use following commands:

· SQL Commands
· PL/SQL Blocks
· SQL*PLUS Commands

SQL is basically collection of four sub-languages:

a) Data Description/Definition Language (DDL):

This sub-language deals with data structure that includes CREATE, ALTER and DROP commands.

b) Data Manipulation Language (DML):

This sub-language deals with data within the structure and it includes INSERT, UPDATE and DELETE commands.

c) Data Query Language (DQL):

This sub-language deals with retrieval of data from database and it includes SELECT command.

d) Data Control Language (DCL):

This sub-language deals with security of data and data structures. It includes GRANT and REVOKE commands.

e) Tranaction Control Language (TCL):

This sub-language deals controlling transactions. These commands are COMMIT, ROLLBACK and SAVEPOINT. These commands are known as Transaction Processing Commands.

Oracle’s Client/Server Architecture

Oracle is a client/server database. It means the database server runs independent of the applications that access it.

The server listens the request from clients, process those requests and sends the results back to client. Most of the Oracle Applications have been two-tier applications.

Advent of the Web and the emphasis on the Internet access to information are driving more and more applications to be implemented using a three-tier architecture.

Oracle DB architecture

Oracle DB architecture study can be divided into following four groups:

• Physical Database Architecture
• Logical Database Architecture
• Memory Architecture
• Process Architecture

Physical Database Architecture:

Physical Database involves Operating System files of following files:

a) Parameter Files:

Parameter file is not a database file but it is very important. It is a simple text file that can be edited by any text editor. It contains a number of settings that affects the oracle database instance. One or more Parameter files are maintained. Parameter files contain following important settings:
• Location of database control file,
• Data Buffer Size to pool data into RAM,
• Buffer Size to keep SQL and PL/SQL reusable codes, etc

b) Control File:

Oracle uses control files to keep information about the state of the database. One or more Control files are maintained. A control file keeps following information:
• Database Name,
• Information about Tablespaces,
• Names and Locations of all data files,
• Names and Locations of all redo files,
• Checkpoint information, etc

c) Data Files:

The most important types of files are Data Files. One or more data files are maintained to keep complete database. They keep following types of data:
• Table Data,
• Index Data,
• Data Dictionary Definition,
• Rollback Data (Information used to undo transaction),
• PL/SQL Codes/Programs,
• Temporary Data,

d) Redo Files (Log Files):

Log Files are used to keep sequential record of changes to a database. As soon as an SQL statement changes the data in database, Oracle generates one or more redo log entries to record that change. There are two types of redo files: Online Redo Files and Offline Redo Files (Archived Redo Files). All Oracle databases use Online Redo Files but Offline Redo Files are optional.

Logical Database Architecture:

Logical Database architecture involves different objects which are abstraction of data from physical into logical. At the top of the object hierarchy is Database. Following main objects are important for studying logical database architecture:

a)Database:

A database is collection of Tablespaces identified by a unique ID. Database has no direct link with any of the physical files. A database is top level abstraction of data.

b)Tablespace:

A tablespace is collection of logical objects like tables, views, synonyms, etc. These objects are also grouped under another logical object named Schema/User. There is no direct relation between a Tablespace and a Schema. Tablespace has direct relation with physical data file. A tablespace may have one or more data files. Size of all data files collectively is the size of a tablespace.

c)Schema:

Schema is pure logical abstraction of data. It collects related objects required for one application or environment. Objects grouped under schema are table, View, Sequence, Index, Synonym, etc.

d)Data Objects:

These are the objects that represent logical data directly or indirectly like, Tables, Views, Synonyms, Index, etc.

Memory Architecture:

When an Oracle Instance starts up, it allocates a large block of memory known as SGA (System Global Area). All Oracle background processes use the SGA. In addition, each process associated with Oracle Instance will have its own private area or memory known as a Program Global Area (PGA). SGA is composed of following components:

a)Database Buffer Cache:

This area of SGA keeps the frequently used data from database. It is further divided into three parts:
i) Keep Buffer Pool: Keeps frequently accessed schema objects that must be kept
in memory all the time. Data read into this pool is retained until the Oracle Instance
is shut down.
ii) Recycle Buffer Pool: Keeps the object data that should be flushed out of memory as
quickly as possible.
iii) Default Buffer Pool: Keeps the data that does not fall into above two categories.

b)Redo Log Buffer:

It is an area in SGA where Oracle places Redo Log Entries that need to be written to the disk. Redo Log entries are written immediately to the disk to avoid data loss in case of power failure or instance crash.

c)Shared Pool:

The two main components of shared pool are Library Cache and Dictionary Cache. Library cache is further divided into Shared PL/SQL Area and Shared SQL Area. Dictionary Cache keeps the Data Dictionary information from database that is used during parsing of SQL statements. Other two caches keep the PL/SQL code and SQL statements that are used frequently by Oracle Instance.

d)Fixed SGA:

It keeps the number of values that Oracle Instance needs to keep the track of different process and status internally. Fixed SGA can not be tuned.


Process Architecture:

An Oracle instance (Oracle Server) is composed of processes and memory structures. An Oracle instance is composed of a number of processes called background processes. They are called background process because they are always running, whether or not any users are connected to the database. Some of the important processes are:

a)Database Writer (DBWR):

This process writes modified data blocks back to the Data Files.

b)Log Writer (LGWR):

Log Writer writes Redo Log entries to the Redo Log Files.

c)System Monitor (SMON):

System Monitor process performs crash recovery and coalesces free space.

d)Process Monitor (PMON):

This process watches for processes that are prematurely disconnected. It releases any lock that they hold and take care of any other necessary cleanup tasks.

e)Recoverer (RECO):

This process resolves distributed transaction.

f)Lock (LCK0):

These processes are used by parallel server option to manage inter-instance locking.

g)Checkpoint (CKPT):

This processes periodically checkpoint the database. Checkpointing is the process of recording the current system change number in all of the database files.

h)Archiver (ARC0):

These process copies filled redo log files to the archive log destination that can be Hard Disk, Tape, etc.

Oracle Datatypes

Oracle Internal Datatypes Oracle supports the following datatypes

1.Character Datatypes

The following are the character datatypes supported by oracle

Char Datatype

The Char datatype is used when a fixed length character string is required. It can stored alphanumeric values.The column length of such a datatype can between 1 - 2000 bytes . By default it is one byte.

Eg: Sex char(1)

Varchar2 Datatype

The varchar2() datatype supports a variable length character string .It also stores Alphanumeric values . The size for this datatype ranges from 1 - 4000 bytes.While Defining this datatype programmer should specify the size using varchar2 saves disk space when compared to char.

Eg: Employeename varchar2(25)

# Currently varchar datatype is equivalent to varchar2 datatype Long Datatype.

The Long datatype is used to store variable character length. Maximum size is 2GB. Long datatype has several characters similar to varchar2 datatype.

The following restriction will be followed when Long datatype used in tables.

1. Only one column in a table can have long datatype.
2. This should not contain unique or primary key constraints.
3. The column cannot be indexed .
4. Procedures or stored procedures cannot accept long datatype as arguments. Eg Feedback Long

2.Number Datatypes

The Number datatype can store positive numbers , negative numbers , zeroes , fixed point numbers , and floating point numbers with a decimal

Egs: Age number(3)
Salary number(10,2)

3.Date Datatypes

Date dataype is used to store date and time in a table.
Date format - "dd - mon - yy".
Limitation of dates between Jan 1 , 4712 BC to Dec 31 , 4712 AD.

Eg: Dob date

4.Raw Datatypes

Raw datatype is used to store bytes based data like binary data or byte strings and the maximum size of this datatype is 2000 bytes.
We must mention storage size when using to raw datatype,because default it does not specify any size P> # Only Storage and Retrieval of data are possible, manipulation of data cannot be done . Raw datatype can be indexed .

Eg: Slno Raw(3)

Long Raw Datatypes

Long Raw datatype is used to binary data of variable length.which can have size of 2 GB .
# This datatype cannot be indexed

Eg: Comments Raw

5.LOB Datatypes

LOB stands for Large Object.This can store unstructured information such as Sound Clips, Video files etc .The maximum size is 4 GB .
The Different internl LOBs are mentioned below

CLOB

A column with its data type as CLOB stores character objects with Single Bytes Characters
CLOB stands for Character Large Object .
A table can have multiple columns with CLOB as its datatype

BLOB

A column with its data type as BLOB can store Large Binary Bytes Objects such as Graphics , Video Clips and Sound Files.
BLOB stands for Binary Large Object .

Eg: Fav_ movie LOB

BFILE

A BFILE column stores File Pointers to LOBs managed by file systems external to the database.A BFILE column may contain filenames for photos stored on a CD-ROM.

Eg: Employee_Photo BFILE

Tools of Oracle

SQL * Plus

SQL * Plus is a Structured Query Language supported by Oracle .Using SQL * Plus We can perform calculations , list of column definitions for any table and can also format query.

PL / SQL

PL / SQL is an extension of PL / SQL block can contain any number of SQL statements integreted with flow of control statements.Thus PL / SQL combines the data manipulating power of SQL with data processing power of procedural languages.

Forms

This is graphical tool used for generating and executing Forms based applications.A form basically comprises blocks and filelds.Multiple tables can be accessed over a single form , based on the application with the help of transaction commands.Oracle forms Builder is the design component of a Oracle Forms.

Reports

It is an application development tool of Oracle for developing , executing , displaying and printing reports . We can create a wide variety of reports , Oracle Reports are powerful , yet easy to use.

Codd 's Rules

Codd's rules are designed in such a way that when database is ready for use it encapsulates the relational theroy to its full potential . The twelve rule listed below.

1. The Information RuleAll information is clearly and logically represented in tables as data values.

2. The Rule of Guaranteed AccessEvery item of data must be logically addressed with the help of a table name, primary key value and column name.

3. The Systematic Treatment of Null ValuesThe RDBMS must be able to support null values [ these value different from ZERO and spaces to represent missing or inapplicable information.

4. The Database Description Rule A description of database is maintained using the same logical structures with which was defined by the RDBMS .These are accessible to users with appropriate authority and are stored in the data dictionary.

5. Comprehensive Data Sub Language According to this rule . the system must support the following
# - Data Definition# - View Definition# - Data Manipulation# - Integrity Constraints# - Authorization# - Transaction management operations.

6. The View Updating RuleAll views that are theoretically updateable must also be updateable by the system.

7. The Insert and Update RuleA single user must hold good for all retrieval , update , delete , insert activities. This rule implies that all the data manipulation commands must be perationalonsets of rows having a relation rather than on a single row.

8. The Physical Independence RuleApplication programs must remain completed when any change are make in storage representation or acess methods.

9. The Logical Data Independence RuleThe changes that are made should not affect the user's ability to work with thedata .The change can be spliting the table into many more tables.

10. The Integrity Independence RuleThe Integrity constraints should be stored in the system catalog or in the Database as table .

11. The Distribution RuleThe system must be able to access or manipulate the data that is distributed in other systems.

12. The Non - Subversion RuleThe nonsubversion rule states that different levels of the language cannot subvert or bypass the integrity rules and constraints.

Database Models

DataBase models are widely classified into 2 categories . They are

1. Object - Based Logical Models

The Object - based logical model can be defined as a collection of ideal tools for describing data , data relationships , and data constraints.

2. Record - Based Logical Models

The Record - based model describes the data structures and Access techniques of a DBMS .

They are 4 types of Record - Based Logical Models

1. File Management Systems [ FMS ]

The File Management Systems was the first method used to store data in a computerized database.The data item is stored sequentially in one large file.A particular relationship can not be drawn between the items other than thesequence in which it is stored.

Disadvantages (or ) Reason for Failure

Locating and retrieving a record is very difficult Process,because of its sequentialaccess method.Altering the file structure Such as updating a record is also heavy work .To do this job the entire file has to be read and rewritten

2. Hierarchical Database Systems [ HDS ]

In the Hierarchical database systems Data storage is in the format of a Parent - Child relationship . The orgin of a data tree is the root Data located at different levels along a particular branch from the root is called the NODE . The last node in the series is called LEAF . This method supports One - to - many relationship .

Disadvantages (or ) Reason for Failure

It is not possible to enter a new level in to the system . This model does not support Many - to- Many relationship .

3. Network Database Systems [ NDS ]

The main idea behind the NDS model is to bring about Many - to - Many relationship . The relationship between the different data items is called sets .This system also uses a pointer to locate a particular record.

Disadvantages (or ) Reason for Failure

The use of pointers leads to difficulty in the structure . As a results of the increased complicate mapping [ seraching ]of related data becomes very difficult

4. Relational Database Systems

Dr.E.F.Codd first introduced the Relational Database Model in 1970.
The Relational model allows data to be represented in a simple row - column .Each Datafield is considered as a column.Each Record is considered as a row of a column

Disadvantages (or ) Reason for Failure

The major disadvantage of the Relational model is that a clear cut interface cannot be determined . But Resuability of a structure is not possible.

5. ObjectRelational Database Systems

Object Relational Model support both object oriented and relational concepts.It eleminates certain difficulty in relational model .In this model it is possible to provide well defined interfaces[area] for the application.Structure once created can be re-used this is the fundamental property of the OOPs concepts .

Relationship Among the Data

A relationship is defined as " An Association among singles " . A relationship may associate a single itself. Several relationships may exist between the same item.

TYPE OF RELATIONSHIPS

1. ONE - to - ONE

A data can be associate with only with another data it is called One - to -One relationship
method .
Like - Every Student's Roll Number can be associated only with student.

2. MANY - to - ONE

A data can be associate with multiple datas it is called Many - to - one relationship
method.

3. MANY - to - MANY

Multiple datas can be associate with multiple datas it is called Many - to - Many
relationship method .

Relational Databases Management Systems (RDBMS)

The relational database uses the concept of linked two-dimensional tables consisting of rows and columns.

Database

Database is collection of related data that can provide meaningful information after processing. Information are base of any decision making. A database is basically a repository of stored data with proper security and without or minimum redundancy (duplicity).