Oracle Is A Client/Server Relational Database
Oracle Is A Client/Server Relational Database
What is Oracle?
Oracle is a Client/Server Relational Database
Management System.
Server: The database consists of physical storage
of data and the database services (such as
searching, sorting, retrieving, updating and
analyzing data). All data access occurs through
server.
Client is a software program that interacts with
the server; either requesting data (or) sending
data.
The communication between the Client and
Server: This is done through physical layer ( a
network) and a logical layer (data access
language i.e. SQL).
Application
Development Tools
SQL*Net Protocol
Client
Oracle RDBMS
Server
db1 db2
Oracle Products
Oracle Server, Personal Oracle, SQL*Plus,
Pro*Oracle, Developer (Forms, Graphics, Reports,
Procedure Builder), Designer, Discoverer, Oracle
Book, etc.,
Oracle Server:
It is a database server (or) back-end is used
to control
Storage of data in designated database
areas
Retrieval of data for applications
Database Security, and the tasks permitted
for specified users.
Consistency and Protection of data
Communication and integrity of data where
databases are distributed across network.
SQL, PL/SQL and SQL*Plus
SQL is the language used to access a
relational data including Oracle.
PL/SQL is an Oracle’s Programming Language
for writing logic and for manipulating data.
SQL*Plus is an Oracle Product with which SQL
and PL/SQL languages can be used.
And also has its own language for controlling
the output of SQL queries.
In Simple, SQL and PL/SQL are languages
that are used in a number of Oracle Products.
SQL*Plus is just one of the product
supporting them.
Relational Databases
It is a collection of data (or) information
organized in two-dimensional matrix called as
“Tables” , consisting of columns and rows.
Before creating tables and other objects in
database, you must decide what tables you will
need to logically represent your data.
It should define:
Tables and their names
Column Names for each Table
Key column for each table
Relationships between tables
When designing th tables and relationships for a
database certain errors commonly occur.
A process called Normalization helps.
Normalization
Normalization is a process of refining tables,
keys, columns and relationships to create a
consistent database design.
Un-Normalized Form: It is a rough draft of a
data item(s).
First Normal Form:
Separate repeating data items.
Identify key data items.
Second Normal Form:
Each data item must depend on key
Third Normal Form:
Eliminate independent data items.
Database Objects
Tables, Data Types, Synonyms, Views, Snapshots,
Indexes, Sequences, etc.,
SQL
(Structure Query Language)
A relational database requires a language to
enable users to access data.
SQL is the language used to communicate with
server to access, manipulate, and control data.
The SQL language was developed by IBM in the
mid-1970s.
In 1979, Oracle corporation introduced the first
commercial implementation of SQL.
SQL is a non-procedural language: you specify
what information you want, not how to get it.
SQL is an English-like language.
SQL uses simple command set called as
Communication with a RDBMS
using SQL
SQL statement
is entered
SQL> SELECT
dname from DEPT;
Data
base
Data is displayed
DNAME
--------
Accounting
SQL Statements
SELECT
INSERT Data Manipulation Language
UPDATE (DML)
DELETE
CREATE
ALTER
DROP Data Definition Language (DDL)
RENAME
TRUNCATE
COMMIT
ROLLBACK Transaction Control Language(TCL)
SAVEPOINT
GRANT
REVOKE Data Control Language (DCL)
SQL*Plus
SQL*Plus is a software product from Oracle
Corporation that allows user to interactively use
the SQL commands, produce formatted reports
and support procedures to access Oracle
Database.
Logging on to SQL*Plus: To log in through a
Windows Environment :
Click Start Programs Oracle for Windows
SQL*Plus 8.0 (or)
Double-click SQL*Plus icon on Desktop
Fill in username, password and host (where the
database is resided)
Once you are successfully logged in to SQL*Plus,
you will get SQL prompt.
Writing Commands
At SQL prompt you may enter SQL statements
(or) SQL*Plus commands.
Writing SQL Statements:
SQL statements are not case sensitive
SQL statements can be entered on one or more
lines.
When you enter a SQL statement, it is stored in
a part of memory called the SQL Buffer, and
remains until you enter a new command.
SQL statement terminates with semi-colon (;)
Writing SQL*Plus Commands:
SQL*Plus commands are entered one line at a
time and are not stored in the SQL buffer.
EDIT command is used to invoke editor with
buffer contents to a file named afiedt.buf
Querying Tables
The SELECT statement retrieves information from
the database, implementing all operators of
relational algebra.
SELECT [DISTINCT] */ColumnName List
FROM tableName
[ORDER BY ColName/ColNumber/Alias [ASC/DESC]]
[WHERE condition]
[GROUP BY columnName]
[HAVING condition]