Database

Database Testing

Need for development of database Disadvantage of file – No data type support – Retrieving and searching for data is time consuming – Memory won’t be allocated dynamically – Supports limited information – Poor security – Data can’t be shared by multiple users and platforms

Models of database HDBMS – main disadvantage is data redundancy NDBMS – if data is misplaced once, it is difficult to find it

RDBMS Features : – Information represented in row column format – Low data redundancy – No physical link is used – Supports integrity constraints, Cudd’s rules, null values – Provides high security – Data can be shared by multiple users and platforms

ORACLE Datatypes: – Simple – Composite – LOB’S – Large objects, max size is 4GB – Diff types of LOB’S • • • •

CLOB BLOB NCLOB BFILE

Supports Five sub languages – DDL - Used to change/remove DB objects – DML – Used to insert/modify/remove rows/data – DQL/DRL – Used to retrieve data in read only mode – DCL – Used to control flow of information between users – TCL – Used to save/cancel changes made by DML statements

NULL Undefined Incomparable Not equal to 0 or space Any arithmetic operation with null returns null values Every RDBMS package supports null values It is not case sensitive

Special Operators  IN – used to pick values one by one specified in the range  Between – Inclusive operator  LIKE – Used to search for pattern valid for character values only. – % - Represents more than one character – _ - Represents single character  IS NULL – Used to compare null values

FUNCTIONS  Arithmetic function  Character function  Date Function  Aggregate Function  General Function

JOINS Used to retrieve data from more than one table at a time Types of JOINS: – Equi Join – Non Equi / Cartesian Join – Outer Join – Self Join

SET Operator Used to join the outputs of select statements Similar data type columns and equal number of columns must be specified in Select statement Types: – – – –

UNION ALL UNION INTERSECT MINUS

Sub Query or Nested Query  Special operators used with sub queries – EXISTS – ANY or SOME – ALL CORRELATED SUB QUERY

Integrity Constraints  A set of predefined rules can be applied on tables while creating or after creation  Automatically activated when DML statements are performed on table  Provides high security  If rules are satisfied, transactions are accepted else rejected  Not Null  Unique  Default  Check  Primary Key  Reference

Types: – Column Constraint Syntax: • Ex:- Create table Dept (deptno number(4) primary key, dname varchar2(20) unique not null, LOC varchar2(20) default ‘HYD’)

– Table Constraint Syntax: • Ex:- Create table Dept (deptno number(4), dname varchar2(20) not null, LOC varchar2(20) default ‘HYD’, constraint pk_deptno primary key (deptno))

Database Objects Tables – Used to store and manipulate data Views and Synonyms – Used to manipulate data Sequence – Used to generate serial no automatically Index and Cluster – Used to improve performance

VIEWS It is a virtual component which holds select statements in it’s memory It’ll not hold data DESC, Select and DML statements are allowed on views Stored permanently in database in table “user_views” Can share with other users

Synonyms  Hides original name and owner of database objects  DESC, Select and DML statements allowed  It’ll not hold data  Can share with other users  TYPES: – Private – Public

Sequence Used to generate numbers automatically Not related to any table Uses two pseudo columns – Nextval : Holds the next value to be generated – Currval: Hold the previous value

INDEX  It is activated when indexed column is used in “where” clause  Used to improve performance while retrieving or manipulating records  Holds the physical address of data  It is like a C pointer which locates the data  TYPES : – Composite Index – Unique Index

CLUSTERS Used to improve performance while manipulating related tables It holds the common column available in two tables Not allowed on existing tables

LOCKS Used preserve data while manipulating TYPES : – Implicit – Explicit • Row Level • Table Level – Share Mode – Share Update Mode – Exclusive Mode

PL / SQL Supports variables and constants Support executing more than one statement as one unit Supports conditional constructs Supports error handling

CURSOR It is temporary memory location used to hold transactional data It’ll not store data permanently Valid in a PL/SQL block only TYPES : – Explicit Cursor – Implicit Cursor

EXCEPTIONS PL/SQL Errors Used to trap an exception and provide some solution TYPES : – Pre Defined – User Defined

TRIGGERS  A set of PL/SQL statements automatically activated whenever and event raising statement is executed  Stored permanently in database  Advantage : – Used to impose user defined restrictions on table – Provides high security while manipulating tables Parts : Triggering event : Indicates when to activate the trigger Trigger Type : Trigger will activate n times if n rows are manipulated by DML statements

 Trigger Restrictions: Indicates when to deactivate the trigger  Trigger Body : A set of PL/SQL statements

PACKAGES :  It is a collection of related variables, cursors, procedures and functions  Advantages : – Improves performance while accessing subprograms from client – Stored permanently

Parts : – Package specifications : Holds component declarations available in package – Package Body : Holds the body of procedures and functions