RDBMS Concepts

Overview of Relational Databases, SQL, and PL/SQL :


Relational database management systems (RDBMS) have 3 major theoretical characteristics:
1. Structures - objects such as tables, indexes, and views that are manipulated by operations.

2. Operations - well-defined actions that act upon the data and structures and conform to predefined integrity rules.

3. Integrity rules - control the type of actions allowed on data or objects in the database.

Summary of SQL operation categories -

· DCL (Data Control Language) REVOKE, ALTER USER, GRANT
· DML (Data Manipulation Language) SELECT, UPDATE, DELETE, INSERT
· DDL (Data Definition Language) CREATE, DROP, ALTER, TRUNCATE
· TCO (Transaction Control Operations) ROLLBACK, COMMIT, SAVEPOINT, and SET TRANSACTION

Benefits of an RDBMS -

· Physical data is stored separate from the logical database structure.
· Easy access to data in many variations.
· Database design is not constrained and has a very high degree of flexibility.
· Data can be stored very efficiently, with minimal redundancy.

ORDBMS (object relational database management system)

· Oracle's object-relational model gives the ability to define objects and have them used within the RDBMS.
· Objects have a name, attributes and methods.
· The name uniquely identifies and object.
Attributes are the columns or fields defined for an object to model a real world entity.

Software Development Life Cycle followed in Oracle Development:

Strategy and Analysis,

Design,

Build and Document,

Transition,

Production.

1. CREATE:

Examples:

Create table TABLE1 (Column1 Varchar2(10) CONSTRAINT PK_1 PRIMARY KEY , column2 char NOT NULL , Column3 date DEFAULT SYSDATE , Column4 Long ,Column5 number UNIQUE DEFAULT 988 , Column6 varchar2(10) CONSTRAINT FK_1 FOREIGN KEY REFERENCES TABLE2(COLUMN6) );

· Column with LONG Datatype cannot be a Key Column.
· Column2 has not been allocated the size of the Datatype CHAR , in this case the Datatype would be 1.
· Column6 is Linked with a Foreign Key , and in this case the Table Referenced TABLE2 is the Master Table . Both the Columns i.e. the referenced as well as the referencing column should necessarily be of the same Datatype and Size.

Create Table TABLE1 as Select * from TABLE2 ;

· To Create a Duplicate Table that’s basically a replica .
· In this case only the NOT NULL Constraint is transferred onto the Created Table.

Create table TABLE1 as Select Column1,Column3,Column5 from TABLE2 where Column3 is NULL;

Creating a Duplicate Table with selected data from Main Table.

Create Table TABLE1 as Select * from TABLE2 where 1>2;

Creating a Duplicate Table with only the STRUCTURE of the Main Table.


2. ALTER :

Examples:

Alter Table TABLE1 DROP COLUMN COLUMN1; ( Feature of Oracle8i )

Alter Table TABLE1 MODIFY COLUMN6 DEFAULT 766;

Alter Table TABLE1 MODIFY COLUMN6 VARCHAR2(15);

1. To Decrease the SIZE of any column there should not be any value in the Column.

Alter Table TABLE5 DROP PRIMARY KEY ;

Incase the NAME of the Constraint is Not Known, then that could be found in the Data Dictionary View , User _Constraints ..

Alter table table_name DISABLE Constraint cons_name CASCADE;

To disable dependent integrity constraints .

3. USING NULL :

Examples :

Select * from TABLE1 WHERE COLUMN1 != ‘ABC’;

· Will not return Columns where Column1 is NULL.

Select NVL(COLUMN1,’XXX’) , COLUMN2 FROM TABLE1;

· NVL Function replaces the Null Values in the respective column with the string mentioned in single codes .
· That Datatype on the column and the value to be replaced should be the same.

Update TABLE1 SET COLUMN5 = COLUMN5+ 10 ;

· If the value of Column5 is null , the value after update would remain NULL .
To get the right solution ,

Update TABLE1 SET COLUMN5 = NVL(COLUMN5,0) + 10 ;


4. DROP :

DROP USER USER1 CASCADE;
· ‘Cascade’ is used when the user has any object in his schema . This keyword allows the objects in the schema to be deleted along with the schema.

DROP TABLE TABLE1 CASCADE CONSTRAINTS;


5. DATE FUNCTIONS :

Examples:

Select SYSDATE FROM DUAL;

Select TO_CHAR(SYSDATE , ’DD MONTH YYYY’) FROM DUAL;

Select TO_CHAR(SYSDATE , ’DAY DDTH “OF” MONTH YYYY’) FROM DUAL;

Select TO_CHAR(SYSDATE , ’FMDDSPTH MONTH YEAR HH:MI:SS AM’) FROM DUAL;

Select TO_CHAR(SYSDATE , ’FMDDSPTH MONTH YEAR HH24:MI:SS AM DAY’) FROM DUAL;

Select TO_CHAR(SYSDATE,’DDD MON YEAR’) FROM DUAL;

Select TO_CHAR(SYSDATE,’DD MONTH YYYY’) FROM DUAL;

Insert INTO TABLE1 VALUES (TO_DATE(SYSDATE,’DD- MON-YYYY’));
Altering the Default Format before Inserting The Date.

Other Date Functions:

MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND ( As in ROUND (‘25-JUL-95’,’MONTH’) , o/p : 01–AUG-95 )
TRUNC ( As in TRUNC (‘25-JUL-95’,’YEAR’) , o/p : 01–JUL-95 )


6. TO_CHAR FUNCTION WITH NUMBERS :

9 : Represents a Number
0 : Forces a zero to be displayed
$ : Places a floating dollar sign
L : Uses a Local floating currency symbol
. : Prints a decimal point
, : Prints a thousand Indicator
D: Prints a decimal point
G: The global separator.

Select TO_CHAR(NUMBER_VAL,’$99,9999.99’) from table_name;


7. USING ROWID:

Examples:

Select COUNT(ROWID) from TABLE1;
· Has faster access as compared to select COUNT(*) from TABLE1; -- Used when counting the number of rows in a table .
Delete from TABLE1 where ROWID != ( Select MIN(ROWID) from TABLE1 where column1 = Value1 );
· Used for Deleting all the rows with Duplicate Values in a Table, leaving just one .

ROWID This is a special Oracle datatype which is a hexadecimal string that represents the unique address of a row in its table. The Oracle 8 rowid is made up of the following pieces:
the data object number of the object the data block in the datafile the row in the data block the datafile. The file number is relative to the tablespace.

CLOB This is the data type for a character large object containing single-byte characters. Variable-width character sets are not supported. The maximum size is 4 gigabytes. BLOB This is the data type for a binary large object. Maximum size is 4 gigabytes.



DISPLAYING DATA FROM MULTIPLE TABLES:

- You can assign an alias to a table and use it to prefix field names.
Example:
SELECT A.field1 as AF1, B.field1 as BF1 FROM tablea A, tableb B ;
- If you alias a table name in a FROM clause you must reference the alias in the SELECT statement. You CANNOT use the full table name.
- An inner join, also called equijoin, is the equality (=) linking of common columns in two tables. Only returns rows where joined values exist and are shared in both tables.
- An outer join returns data from table A, even if there are no matching records in table B. Use the (+) , plus sign in parenthesis, on the table that may have "missing" rows; In this example table B is considered the "outer table".

SELECT A.F1, B.F1 FROM A , B WHERE A.F1=B.F1(+) ;
-A self join, or recursive join, is when a table is joined to itself. You must use table aliases. Good for identifying duplicate rows.
- AND and = can be used in an outer join. IN and OR cannot be used in an outer-join.
- The minimum number of join statements required to join N tables = N-1. This avoids a Cartesian product.

Cartesian Product :
Formed when :
1) The where clause in the join condition is omitted
2) A join condition is invalid
3) All rows in the first table are joined to all rows in the second table.
To avoid a Cartesian Join , if the number of tables used in the Join are X then the number of conditions in the where clause should be minimum X-1 .

USING SELECT QUERY IN INSERT AND UPDATE:

Insert into table1 (col1,col2,col32) select col1,col2,col3 from table2;

Update table1 set col5 = (select col3 from table2 where col1 = value)
where job (select job from emp where empno = 7788);

WILDCARDS:

· % (percent sign) is the multicharacter wildcard.
· _ (underscore) is the single character wildcard.
Escape "\" (backslash) is required to use either one of these in a LIKE phrase. $ and / can be used as a literal.
ñselect * from wildcrd where cola like ‘%/%’ ESCAPE ‘/’;

ACCEPTING VALUES AT RUNTIME :

&: To take input from User at Runtime .
&&: To use a previously defined value .
DEFINE: Create a CHAR datatype user variable.
UNDEFINE: To Abruptly clear a variable .

ACCEPT: Read a User Input and store it in a variable
· ACCEPT variable [DATATYPE] [ PROMPT text ] {HIDE}

SQL*PLUS EDITOR AND COMMANDS:

- A[PPEND] adds specified text to end of the current line.

- To include a single blank enter two spaces between APPEND and text.

- To include a semi-colon at end of appended text, use two semicolons.

- DEL deletes the current line from the buffer.

- I[NPUT] adds one or more lines of text after the current line in the buffer.

- The command buffer can be terminated (and therefore executed) in 3 ways: with slash (/) , semicolon(;) or by pressing ENTER twice.

- The GET command requires a previously save file.

- DDL statements and exiting SQL*Plus cause an implicit COMMIT.

- Bombing out of SQL*Plus causes an implicit ROLLBACK.


8. VIEWS:

Examples:

Create OR Replace VIEW VIEW1 AS Select * from TABLE1 WHERE COLUMN1 = VALUE1 WITH CHECK OPTION CONSTRAINT CHK_VW1 ;

· Now any attempt to change the Column1 value for any row in view will fail.

· In Oracle 7.3 , ORDER BY clause could not be used while making a View , but in Oracle 8i , this can be done with ease .

· As of Oracle7.2, you can embed view queries directly within the FROM clauses of queries, and therefore do not have to create the views (such as SALES_TOTAL_VW ) separately. In the following listing, the SALES_TOTAL_VW syntax is embedded in the query’s FROM clause, and the view is joined to the COMPANY table.

select Name, Sum_Sales_Total
from COMPANY,
(select Company_ID Sales_Co_ID,
SUM(Sales_Total) Sum_Sales_Total
from SALES
group by Company_ID)
where COMPANY.Company_ID = Sales_Co_ID;

· Data in a view cannot be modified if it contains:
- Columns defined by expressions
- Rownum psuedocolumn
· Data cannot be added if there are NOT NULL columns in the base tables that are not selected by VIEW.

9. ORACLE DATA DICTIONARY:

USER_VIEWS: Information about the Views in schema and the View creating text. USER_SOURCE: For Source Code of Procedures , Functions and Packages.
USER_OBJECTS: All objects in the User’s Schema
USER_INDEXES: Information about the existing Indexes and the indexed columns.
USER_TRIGGERS: Trigger Information and the source code for trigger in schema.
USER_SEGMENTS: Segment Information (Indexes and Tables) in user’s schema.
USER_SYNONYMS: Existing Synonym information in user’s schema.
USER_CONSTRAINTS: Column Constraints along with a search condition.
USER_TAB_COLUMNS: All table names and the columns in the user’s schema.
USER_COL_GRANTS_MADE: Grants made on certain columns of tables in schema.
USER_COL_GRANTS_RECD: Grants received on certain columns of tables in schema.
USER_IND_COLUMNS: Information about all indexed columns in the schema.
USER_OBJECT_SIZE: Object Size , name and type of objects in schema.
USER_SEQUENCES: Sequence details residing in user’s schema.
USER_SYS_PRIVS: System Privileges possessed by the user,
USER_TABLES: All tables ,and the details of those tables in the schema.
USER_TAB_COMMENTS: Comments on tables in schema.
USER_TAB_GRANTS_MADE: Table grants made by the user.
USER_TAB_GRANTS_RECD: Table grants received by user.
USER_TRIGGER_COLS: Columns linked to database triggers.
USER_UPDATABLE_COLUMNS: Columns in an Updateable status in the user’s schema.


10. INDEX :

B*Tree Indexes : Provide the best performance on High Cardinality columns (i.e. with many distinct values).
They basically maintain the sort order of the data , making it easy to look up a range records. They form a Tree with leaf nodes at the bottom of the tree containing the index entries together with pointers to those rows for those entries.

When creating a B*Tree Structure two options are available:
REVERSE: Reverses the bytes in each index
UNIQUE : Requires that each index entry be unique


Bitmapped Indexes: Introduced in Oracle 8
Best for Low to Medium Cardinality columns where multiple bitmap indexes can be combined with AND & OR.

Points to Note :
· Use Unique Indexes only when you need to enforce a business rule requiring unique values in a column.
· Consider the Reverse option when you have large numbers of Index Entries all starting with the same characters.
· Do not use the Reverse option if you are querying for ranges of data.
· Consider Bitmapped Indexes for columns containing a low number of distinct values – a Yes or No type of column.
· Usually only those type of columns which are repeatedly used in the Where clause of a select query are to be Indexed.
· Long or Long Raw columns cannot be Indexed.


11. TABLE PARTITIONING : Feature introduced in Oracle8

Table Partitioning needs to be specified while creating the table itself.
Syntax:
CREATE TABLE table_name (…………….)
Partiton by Range (column_name) ( PARTITION partition_name VALUES LESS THAN (values),
PARTITION partition_name VALUES LESS THAN (values),
PARTITION partition_name VALUES LESS THAN (values),….));

· Previously created partitions can be split further any number of times but only after it has been previously defined in the CREATE statement.

· ALTER TABLE table_name SPLIT PARTITION partition_name AT (value) INTO (PARTITION partiton_name, PARTITION partiton_name );

· ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN (MAXVALUE);


12. INDEX PARTITIONING:

Basically two types of Partitioned Indexes can be created:

Local : An Index that covers each table partition individually.These indexes contain partition keys that only map to rows stored in a single named partition.
Example:
Creating an Index Partition from students table.
Create INDEX dept_idx ON students (student_dept_id)
LOCAL
( PARTITION dept_id_1 TABLESPACE OM1,
PARTITION dept_id_1 TABLESPACE OM1,
PARTITION dept_id_1 TABLESPACE OM1,
PARTITION dept_id_1 TABLESPACE OM1);


Global : An Index that spans all the table partitions. It contains keys that refer to more than one partition of an underlying table.
Example:
dept_idx Index on table students can be created as a Global Index.
Create INDEX dept_idx ON students (student_dept_id)
GLOBAL PARTITION BY Range (student_dept_id)
( PARTITION dept_id_1 Values LESS THAN (1000,100) TABLESPACE OM1,
PARTITION dept_id_2 Values LESS THAN (2000,200) TABLESPACE OM2,
PARTITION dept_id_3 Values LESS THAN (4000,400) TABLESPACE OM3,
PARTITION dept_id_4 Values LESS THAN (MAXVALUE,MAXVALUE)
TABLESPACE OM4 );

Guidelines:
· If the table requires regular maintenance, Local Indexes are recommended. If the partition resides on its own table-space, you can still access the underlying table while the partition is brought offline for maintenance.
· The downtime for a partition is proportional to the size of the partition – not to the size of the underlying table.
· A Partitioned Index cannot be applied on Cluster Tables.
· A Bitmap Index on a partitioned table must be a Local Index.
· Partitioned and Non-partitioned Indexes can be applied to partitioned and non-partitioned tables.
· Bitmap Indexes on non-partitioned tables cannot be range partitioned.


OBJECTS IN ORACLE 8:

Abstract Datatype:
Create TYPE type_name1 as OBJECT (COL_OBJ1 DATATYPE, COL_OBJ2 DATATYPE,…));
ñCreate TABLE table_name (COL1 DATATYPE , COL2 TYPE_NAME1 , ………));

Nested Tables:
Create TYPE my_tab IS TABLE OF type_name1;
Insert into THE (Select colum_name from table_name WHERE [condition] ) VALUES
(type_name1 (value1,value2,………));



13. PLSQL PROGRAMING:

PL/SQL BLOCKS

- There are 2 types of PL/SQL blocks: anonymous blocks and named blocks.- Anonymous blocks may have the following Keywords::

DECLARE
declare variables in this section (not required)
BEGIN
this is the executable section (required)
EXCEPTIONS
this is the error handling section (not required)
END;

- At a minimum an anonymous block must have the BEGIN and END keywords.- An anonymous block is available in all PL/SQL environments.- Named blocks are create using the CREATE OR REPLACE PROCEDURE command and are stored as objects in the database.- In a named block the header, executable, and end subprogram components are required.

VARIABLES IN PL/SQL

- Local variables are scoped to the current block except when a block is named using a label. In this situation, the following PL/SQL block can reference a variable from the preceding block using the LABEL_NAME.VARIABLE_NAME notation.

- Aggregate variables are implicitly declared in a CURSOR FOR LOOP.

-BINARY INTEGER is better than NUMBER for defining the data-type for a counter variable because it is more efficient than NUMBER.

- A variable that has a colon ( : ) in front of it is a BIND variable. These are defined and assigned a value in SQL*PLUS and used in PL/SQL like a global variable.

- Multiple variables can not be declared in the same statement. For example: "var1, var2 VARCHAR2(10)" is an invalid expression.


Basic PROCEDURE Syntax :

CREATE OR REPLACE PROCEDURE procedure_name
[(parameter [{IN OUT IN OUT } ] datatype
[{ := DEFAULT } expression ]
[,(parameter [{IN OUT IN OUT } ] datatype
[AUTHID { CURRENT_USER DESIGNER }]
{ IS AS }
[declarations ]
BEGIN
Code
[EXCEPTION
exception_handlers]
END;

· After the procedure or Function is compiled, in case of errors , the command SHOW ERROR can be used (in case of Procedure ) , or USER_ERRORS data dictionary table can be queried to obtain the error details.

Basic FUNCTION Syntax:

CREATE OR REPLACE FUNCTION function_name
[(parameter [{IN OUT IN OUT } ] datatype
[{ := DEFAULT } expression ]
[,(parameter [{IN OUT IN OUT } ] datatype
[{:= DEFAULT } expression]…..]]
RETURN return_type
[AUTHID { CURRENT_USER DESIGNER }]
{ IS AS }
[declarations ]
BEGIN
Code
[EXCEPTION
exception_handlers]
END;

- Example of referencing "record members" directly using dot notation:
IF my_rec.f1 > 10 THEN ….
- Example of passing record type variable into a procedure:
PROCEDURE some_procedure (ME_REC) ;
- Example of assigning expression to a "record":
my_rec.f2 := ‘Rick’;

PL/SQL Tables:
- A PL/SQL table is an object of type table. It is a table in memory. It must have at least one field and one primary key. It can based upon column(s) taken from an existing base-table column or defined explicitly. All three examples below are valid:

1. TYPE my_table IS TABLE OF some_table.some_column%type INDEX BY BINARY_INTEGER.

2. TYPE my_table IS TABLE OF some_table.some_column%type NOT NULL INDEX BY BINARY_INTEGER.

3. TYPE my_table IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER.
- The INDEX BY phrase is mandatory and a BINARY_INTEGER index is the fastest for retrieval. – Use table index to reference values. Examples:
1. IF my_table(12) = 99.99 THEN …
2. my_table(some_variable + 5) ;

CURSORS:
Implicit Cursors: - An implicit cursor is automatically defined when any SQL statement is issued. It results in 2 fetches:
Fetch1 - ID’s if more than one row will be returned
Fetch2 - Gets the row
- An implicit cursor can only be used to process one row at a time. If more than one row is returned an implicit exception is raised.

Implicit Cursor Variables:
- %FOUND: Before the first fetch on an opened cursor equals NULL. If the latest fetch is successful then it equals TRUE. Check only after a fetch command.
- %NOTFOUND: If the latest fetch is unsuccessful it equals TRUE. If the latest fetch is successful it equals FALSE.
- %ISOPEN: Before a cursor is opened it equals FALSE. If the cursor has been opened and is still open it equals TRUE.
- %ROWCOUNT: Before a cursor is open it equals NULL and raises an INVALID_CURSOR exception. Before first fetch after cursor is open it equals 0 (zero). After a fetch while a cursor is open it equals the last valid count of rows fetched.

- Explicit cursor definition is done in the declaration section. Remember, no SELECT INTO statement in a cursor!


Using CURSORS with NOWAIT (Using a Cursor FOR LOOP):
Example:
Declare
CURSOR C1 IS SELECT * FROM EMP FORM UPDATE NOWAIT;
…….
Begin
……..
FOR EMP_RECORD IN C1 LOOP
UPDATE………

WHERE CURRENT OF C1;

END LOOP;
COMMIT;

· As in :
Select empno from emp where deptno = 10 FOR UPDATE;
FOR UPDATE clause to lock rows before update or delete

DATABASE TRIGGERS:

A stored PL/SQL program unit associated with a specific database table , executing upon the occurrence of a specific event .

Events in Oracle:

SERVERERROR : Oracle fires the trigger whenever a server error message is Logged.
LOGON : Trigger fired after a client application logs on to the database successfully.
LOGOFF : Trigger fired after a client application logs off the database.
STARTUP : Trigger fired immediately after the database is opened.
SHUTDOWN : Trigger fired just before the server starts a shutdown of an instance of the
Database.

Create or Replace TRIGGER TRIGGER_NAME
(BEFORE / AFTER )
(SERVERERROR / LOGON / LOGOFF / STARTUP / SHUTDOWN )
ON DATABASE

BEGIN
Pl/sql statement;
END;

Example:
CREATE OR REPLACE LOGON_TRIGG
AFTER LOGON ON SCHEMA

BEGIN
Insert INTO LOG_TRIG_TABLE
(USER_ID, LOG_DATE,ACTION)
Values
(USER , SYSDATE , ’LOGGING ON’ );

END;

· One should be very careful with database triggers. They affect all database users , and if you create one with errors , it can affect all database users. A logon trigger that does not compile correctly will prevent all database users from connecting . The only way to Log On and drop such a trigger is to connect as INTERNAL or as SYSDBA.

INSTEAD OF Trigger:
Allows user to write code that is invoked when a user tries to Insert , Update or Delete through a view.

Example:
An Instead –of Trigger for deleting a loan ID and Record.

Create or Replace TRIGGER students_and_books_delete
INSTEAD OF DELETE ON students_and_books
REFERENCING OLD AS old NEW as new
BEGIN
--First , delete the loan from data from BOOKS.
UPDATE books
Set book_loan_id = NULL ,
Book_due = NULL
WHERE BOOK_ID = :old.bl_book_id;

--Then delete the actual loan record
DELETE FROM books_loaned
WHERE bl_book_id = :old.bl_book_id;

14. OPTIMIZER HINT

In Oracle 8i, the default Optimizer is set to CHOOSE in init.ora. This is Oracle's documented preference when running 11i applications as some SQL may run better with the RULE hint. In Oracle 8i, statistics are gathered for tables and indexes and, unless you code a hint to force RULE, the Cost Based Optimizer will be used.