Study of Relational Database Management

Systems through Oracle/PL SQL

UNIT 5
 DBMS RDBMS
1) DBMS applications store data as file. RDBMS applications store data in a tabular form.
In DBMS, data is generally stored in either a In RDBMS, the tables have an identifier called primary key and the data
2)
hierarchical form or a navigational form. values are stored in the form of tables.
3) Normalization is not present in DBMS. Normalization is present in RDBMS.
DBMS does not apply any security with regards to RDBMS defines the integrity constraint for the purpose of ACID
4)
data manipulation. (Atomicity, Consistency, Isolation and Durability) property.

DBMS uses file system to store data, so there will be in RDBMS, data values are stored in the form of tables, so a relationship
5)
no relation between the tables. between these data values will be stored in the form of a table as well.

DBMS has to provide some uniform methods to RDBMS system supports a tabular structure of the data and a relationship
6)
access the stored information. between them to access the stored information.

7) DBMS does not support distributed database. RDBMS supports distributed database.

DBMS is meant to be for small organization and RDBMS is designed to handle large amount of data. it supports multiple
8)
deal with small data. it supports single user. users.

9) Examples of DBMS are file systems, xml etc. Example of RDBMS are MySQL, postage, SQL server, oracle etc.
 Clients

Connection/thread handling

Query cache Parser

Optimizer

Storage engine

A logical view of the MySQL server architecture

• The topmost layer contains the services that aren’t unique to MySQL. They’re services most network-based
client/server tools or servers need: connection handling, authentication, security, and so forth.
• The second layer is where things get interesting. Much of MySQL’s brains are here, including the code for query
parsing, analysis, optimization, caching, and all the built-in functions (e.g., dates, times, math, and encryption).
Any functionality provided across storage engines lives at this level: stored procedures, triggers, and views, for
example.
• The third layer contains the storage engines. They are responsible for storing and retrieving all data stored “in”
MySQL. Like the various filesystems available for GNU/Linux, each storage engine has its own benefits and
drawbacks. The server communicates with them through the storage engine API. This interface hides differences
between storage engines and makes them largely transparent at the query layer. The API contains a couple of
dozen low-level functions that perform operations such as “begin a transaction” or “fetch the row that has this
primary key.” The storage engines don’t parse SQLor communicate with each other; they simply respond to
requests from the server.
Physical files and memory structures
Introduction:
The collection of data that makes up a computerized database must be stored physically on some computer
storage medium. The DBMS software that can then retrieve, updates, and processes this data as needed.
Computer storage media from a storage hierarchy that includes two main categories.
Primary Storage
The category includes storage media that can be operated on directly by the computer Central Processing Unit
(CPU), such as the computer main memory and smaller but faster cache memories. Primary storage usually
provides fast access to data but is of limited storage capacity.
Secondary Storage
This category includes magnetic disks, optical disks, and tapes. These devices usually have a larger capacity,
cost less, and provide slower access to data than do primary storage devices. Data in secondary storage cannot
be processed directly by the CPU: it must first be copied into.
Primary storage.
The storage media are classified by the speed with which data can be accessed, by the cost per unit of data to
buy the medium, and by the medium’s reliability. Let’s look into the media that are typically available.
Main Memory: The general-purpose machine instructions operate on main memory. If a power failure or
system crash occurs, the contents of main memory are usually lost. (i.e.) volatile memory type.

Flash Memory: Also, known as Electrically Erasable programmable read only memory (EEPROM). Data Survive
power failure in flash memory (Non-volatile Type). Reading data from flash memory takes less than 100
nanoseconds, which is as fast as reading data from main memory. However, writing data to flash memory is
more complicated. Data can be written once, but cannot be overwritten directly. To overwrite memory that has
been written already, we have to erase an entire bank of memory once.
It is used for storing small volumes of data (5-10 MB) in hand-held computers, digital cameras.

Background process. SHOW PROCESSLIST shows you which threads are running

Cache: The cache is the fastest and most costly form of storage. Cache memory is small. The computer
hardware manages its use
Table Spaces
A data file that can hold data for one or more InnoDB tables and associated indexes.
There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are:
a. System tablespace b. File per tablespace c. General tablespace System tablespace contains,
1.InnoDB data dictionary.
2.DoubleWrite Buffer.
3.Change buffer
4.Undo Logs.

Apart from this it also contains,

1.Tables &
2.Index data
Introduction to Data Blocks, Extents, and Segments
My sql allocates logical database space for all data in a database. The units of database space allocation are
data blocks, extents, and segments. Figure shows the relationships among these data structures:
• Mysql stores data in data blocks (also called logical blocks, My sql blocks, or pages). One data block
corresponds to a specific number of bytes of physical database space on disk.
• The next level of logical database space is an extent. An extent is a specific number of contiguous data
blocks allocated for storing a specific type of information.
• The level of logical database storage greater than an extent is called a segment. A segment is a set of
extents, each of which has been allocated for a specific data structure and all of which are stored in the
same tablespace. For example, each table's data is stored in its own data segment, while each index's data is
stored in its own index segment. If the table or index is partitioned, each partition is stored in its own
segment.
• My sql allocates space for segments in units of one extent. When the existing extents of a segment are full,
My sql allocates another extent for that segment. Because extents are allocated as needed, the extents of a
segment may or may not be contiguous on disk.
Dedicated Server
A dedicated server is a single computer in a network reserved for serving the needs of the network. For
example, some networks require that one computer be set aside to manage communications between all the
other computers. A dedicated server could also be a computer that manages printer resources. Note, however,
that not all servers are dedicated. In some networks, it is possible for a computer to act as a server and perform
other functions as well.
In the Web hosting business, a dedicated server is typically a rented service. The user rents the server, software
and an Internet connection from the Web host.
Multithreaded server
Multithreaded server, also known as a shared server, allows many user processes to share a few shared server
processes to connect to the database. Without MTS, each user process spawns its own dedicated server
process, consuming OS memory. A dedicated server process remains associated to the user process for the
remainder of the connection.
Multithreading will allow a program or an OS to handle multiple users and requests simultaneously by utilizing
multiple threads, as opposed to running the program multiple times. It uses the program resources more
effectively by handling multiple queries or commands and tracking them with multiple threads until completed
Advantages of Multithreading
Increase performance
 Concurrency
 Reduce number of servers needed reducing cost and maintenance
 Compatibility with applications that create client threads such as API
 Parallel tasks
 More efficient use of system resources
Disadvantages of Multithreading
 Synchronizing resources that are being share
 Potential deadlocks
 Increased complexity in programming as well as troubleshooting problems
Distributed Database System-
A distributed database (DDB) is a collection of multiple, logically interrelated databases distributed over a
computer network.
A distributed database management system (D-DBMS) is the software that manages the DDB and provides an
access mechanism that makes this distribution transparent to the users.
Distributed database system (DDBS) = DDB + D– DBMS
What is not a DDBS?
 A timesharing computer system.
 A loosely or tightly coupled multiprocessor system.
A database system which resides at one of the nodes of a network of computers - this is a centralized database
on a network nod.
Database links and snapshot
Database Links?
A database link is a pointer that defines a one-way communication path from an Oracle Database server to
another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the
link, you must be connected to the local database that contains the data dictionary entry.
A database link connection is one-way in the sense that a client connected to local database A can use a link
stored in database A to access information in remote database B, but users connected to database B cannot use
the same link to access data in database A. If local users on database B want to access data on database A, then
they must define a link that is stored in the data dictionary of database B.
A database link connection allows local users to access data on a remote database. For this connection to occur,
each database in the distributed system must have a unique global database name in the network domain. The
global database name uniquely identifies a database server in a distributed system.
Snapshot
A snapshot is a replica of a target master table from a single point-in-time. Whereas in multimaster
replication tables are continuously being updated by other master sites, snapshots are updated by one or
more master tables via individual batch updates, known as a refresh, from a single master site.

Oracle offers a variety of snapshots to meet the needs of many different replication (and non-replication)
situations
Data dictionary
A metadata (also called the data dictionary) is the data about the data. It is the self describing nature of the
database that provides program-data independence. It is also called as the System Catalog. It holds the
following information about each data element in the databases, it normally includes:
 Name
 Type
 Range of values
 Source
 Access authorization
 Indicates which application programs use the data so that, when a change in a data structure is
contemplated, a list of the affected programs can be generated.

Active and Passive Data Dictionaries

An active data dictionary (also called integrated data dictionary) is managed automatically by the database
management software.
The passive data dictionary (also called non-integrated data dictionary) is the one used only for documentation
purposes. Data about fields, files, people and so on, in the data processing environment are.
Dynamic Performance Views
Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity.
These views are dynamic because they are continuously updated while a database is open and in use. The views
are sometimes called V$ views because their names begin with V$.
These views contain information such as the following:
 System and session parameters
 Memory usage and allocation
 File states (including RMAN backup files)
 Progress of jobs and tasks
 SQL execution
 Statistics and metrics
The dynamic performance views have the following primary uses:
 Oracle Enterprise Manager Uses the views to obtain information about the database (see "Oracle Enterprise
Manager").
 Administrators can use the views for performance monitoring and debugging.
Privilege and Security
A privilege is a right to execute a particular type of SQL statement or to access another user's object. Some
examples of privileges include the right to:
 Connect to the database (create a session)
 Create a table
 Select rows from another user's table
 Execute another user's stored procedure
There are two distinct categories of privileges:
 System privileges
 Schema object privileges
Privileges
Authorization includes primarily two processes:
 Permitting only certain users to access, process, or alter data.
 Applying varying limitations on user access or actions. The limitations placed on (or removed from) users
can apply to objects such as schemas, tables, or rows or to resources such as time (CPU, connect, or idle
times).
A user privilege is the right to run a particular type of SQL statement, or the right to access an object that
belongs to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle
Database.
Managing User Role
This section contains:
 About User Roles
 Predefined Roles in an Oracle Database Installation
 Creating a Role
 Specifying the Type of Role Authorization
 Dropping Roles
 Restricting SQL*Plus Users from Using Database Roles
Uses of Roles
In general, you create a role to serve one of two purposes:
 To manage the privileges for a database application (see "Common Uses of Application Roles")
 To manage the privileges for a user group (see "Common Uses of User Roles")
Hierarchical Queries
A hierarchy is built upon a parent-child relationship within the same table or view.

START WITH NOCYCLE

CONDITION
WITH BY
CONDITION

START WITH specifies the root row(s) of the hierarchy. CONNECT BY specifies the relationship between parent rows and
child rows of the hierarchy.The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a
CONNECT BY LOOP exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see
which rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.
Inline Query
When you use SQL Subquery in From clause of the select statement it is called inline view.
A common use for inline views in Oracle SQL is to simplify complex queries by removing join operations and
condensing several separate queries into a single query. A subquery which is enclosed in parenthesis in the
FROM clause may be given an alias name.
Flashback Query
It is useful to recover from accidental statement failures. For example, suppose a user accidently deletes rows
from a table and commits it also then, using flash back query he can get back the rows.
Flashback feature depends upon on how much undo retention time you have specified. If you have set the
UNDO_RETENTION parameter to 2 hours then, Oracle will not overwrite the data in undo tablespace even after
committing until 2 Hours have passed. Users can recover from their mistakes made since last 2 hours only.
For example, suppose John gives a delete statement at 10 AM and commits it. After 1 hour he realizes that
delete statement is mistakenly performed. Now he can give a flashback as of query to get back the deleted rows
like this.
Flashback Query
SQL>select * from emp as of timestamp sysdate-1/24;
Or
SQL> SELECT * FROM emp AS OF TIMESTAMP
TO_TIMESTAMP ('2007-06-07 10:00:00', 'YYYY-MM-DD HH:MI: SS')
To insert the accidently deleted rows again in the table he can type
SQL> insert into emp (select * from emp as of timestamp sysdate-1/24)
Cursor
Database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate
subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database
records.
There are two types of cursors −
 Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no
explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.
 Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit
cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement
which returns more than one row.
Creating an explicit cursor is –
 Declaring the cursor for initializing the memory
 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data; Closing the cursor to release the allocated memory
DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers; END;
Oracle Exception handling mechanism
An exception is an error condition during a program execution. PL/SQL supports programmers to catch such
conditions using EXCEPTION block in the program and an appropriate action is taken against the error
condition. There are two types of exceptions −
 System-defined exceptions i.e Predefined exceptions are error conditions that are defined by PL/SQL.
 User-defined exceptions i.e Error defined and raised by the application
The general syntax for exception handling is as follows:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
Stored procedure
A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing
language, stored in database. A procedure has a name, a parameter list, and SQL statement(s).
Why Stored Procedures?
 Stored procedures are fast. MySQL server takes some advantage of caching, just as prepared statements do.
The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires
checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's
stored on the server.
 Stored procedures are portable. When you write your stored procedure in SQL, you know that it will run on
every platform that MySQL runs on, without obliging you to install an additional runtime-environment
package, or set permissions for program execution in the operating system, or deploy different packages if
you have different computer types. That's the advantage of writing in SQL rather than in an external
language like Java or C or PHP.
 Stored procedures are always available as 'source code' in the database itself. And it makes sense to link the
data with the processes that operate on the data.
 Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003 standard. Others (DB2,
Mimer) also adhere.
Create Procedure
Following statements create a stored procedure. By default, a procedure is associated with the default
database (currently used database). To associate the procedure with a given database, specify the name as
database_name.stored_procedure_name when you create it. Here is the complete syntax:
Syntax:
CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter: [ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA
| MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
routine_body:
Function
A stored function is a special kind stored program that returns a single value. You use stored functions to
encapsulate common formulas or business rules that are reusable among SQL statements or stored programs.
Different from a stored procedure, you can use a stored function in SQL statements wherever an expression is
used. This helps improve the readability and maintainability of the procedural code.
MySQL stored function syntax
DELIMITER $$

CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)

DETERMINISTIC
BEGIN
DECLARE lvl varchar (10);
IF p_creditLimit > 50000 THEN
SET lvl = 'PLATINUM';
ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000)
THEN
SET lvl = 'GOLD';
ELSEIF p_creditLimit < 10000 THEN
SET lvl = 'SILVER';
END IF;

RETURN (lvl);
END
Trigger
In a DBMS, a trigger is a SQL procedure that initiates an action (i.e., fires an action) when an event (INSERT,
DELETE or UPDATE) occurs. Since triggers are event-driven specialized procedures, they are stored in and
managed by the DBMS. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a
result of a data modification to the associated table. Triggers are used to maintain the referential integrity of
data by changing the data in a systematic fashion.

Syntax CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR]| UPDATE [OR]| DELETE}
[OF col_name]
ON table_name
Require INSTEAD OF Triggers
Mutating Errors
Mutating table errors occur when a trigger attempts to access a "mutating" table, like the table from which the
trigger has been called. In MySQL, triggers are not initiated by cascading foreign keys, and they cannot modify
the table from which they are called, so neither of those issues can cause a mutating table error. However,
foreign keys appear to operate before 'AFTER' triggers, so it is possible to create a trigger that will cause a
statement to fail.
Instead of Triggers
INSTEAD OF triggers can be defined on either tables or views; however, INSTEAD OF triggers are most useful
for extending the types of updates a view can support. For example, INSTEAD OF triggers can provide the logic
to modify multiple base tables through a view or to modify base tables.
Example using an INSTEAD-OF-trigger:
CREATE VIEW VIEW_TEST
AS SELECT A.NAME , A.ID_LOC , B.CITY , B.STATE , B.COUNTRY ,
A.DEPARTMENT FROM A, B
where A.ID_LOC = B.ID_LOC;
CREATE TRIGGER TRG_VIEW_TEST_INST_OF
INSTEAD OF INSERT ON VIEW_TEST
FOR EACH ROW
BEGIN
insert into B (ID_LOC, CITY, state, country)
values (varID, varCity, varState, varCountry);
INSERT INTO A (ID, NAME, ID_LOC, DEPARTMENT)
VALUES(varID, varName, varLoc, varDept);
END TRG_TEST_INST_OF;