Transcript Introduction

Introduction
17 July 2015
Create By Pantharee Sawasdimongkol
1
Objectives
After completing this lesson, you should be able to
the following




Discuss the theoretical and physical
aspects of a relational database
Describe the Oracle implementation
of the RDBMS and ORDBMS
Describe new features of Oracle8i
Describe how SQL and PL/SQL are
used in the Oracle product set
Describe the use and benefits of
PL/SQL
17 July 2015
Create By Pantharee Sawasdimongkol
2
System Development Life Cycle
Strategy
and
Analysis
Design
Build
and
Document
Transition
Production
17 July 2015
Create By Pantharee Sawasdimongkol
3
Data Storage on Different Media
SALGRADE
DEPT
DEPTNO
------------10
20
30
40
Electronic
17 July 2015
Spreadsheet
DNAME
GRADE
LOSAL
HISSAL
-------------
-------------
------------
LOC
--------------
-------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
NEW YORK
DALLAS
CHICAGO
BOSTON
700
1201
1401
2001
3001
Create By Pantharee Sawasdimongkol
Filling Cabinet
1200
1400
2000
3000
9999
Database
4
Relational Database Concept



Dr. E.F. Codd proposed the relational
model for database systems in 1970.
It is the basis for the relational database
management system (RDBMS).
The relational model consists of the
following:
• Collection of objects or relations
• Set of operators to act on the relations
• Data integrity for accuracy and consistency
17 July 2015
Create By Pantharee Sawasdimongkol
5
Definition of a Relational
Database
Database
Table Name: EMP
EMPN
O
ENAME
JOB
7839
KING
PRESIDENT
7698
BLAKE
7782
7566
Table Name: DEPT
DEPTNO
DEPTNO
DNAME
LOC
10
10
ACCOUNTING
NEW YORK
MANAGER
30
20
RESEARCH
DALLAS
CLARK
MANAGER
10
30
SALES
CHICAGO
JONES
MANAGER
20
40
OPERATIONS
BOSTON
17 July 2015
Create By Pantharee Sawasdimongkol
6
Data Models
Model of
System
in client’s
mind
Entity model of
Client’s model
Table model
Of entity model
17 July 2015
Create By Pantharee Sawasdimongkol
server
Tables on disk
7
Entity Relationship Model

Create an entity relationship diagram from
business specifications or narratives
EMPLOYEE
#*
*
O

number
name
job title
DEPARTMENT
#*
*
number
name
O
location
Scenario
• “…Assign one or more employees to a
department…”
• “…Some departments do not yet have.
Assigned employees…”
17 July 2015
Create By Pantharee Sawasdimongkol
8
Entity Relationship
Modeling Conventions
Entity
Attribute
Singular name
Lowercase
Mandatory marked with “*”
Optional marked with “0”
Soft box
Singular, Unique name
Uppercase
Synonym in parentheses
Assigned to
EMPLOYEE
#*
*
O
DEPARTMENT
number
name
job title
Composed of
#*
*
number
name
O
location
Unique Identifier (UID)
17 July 2015
Primary marked with “(#)”
Create By Pantharee Sawasdimongkol
Secondary marked with “(#)”
9
Relational Database Terminology
EMPNO
ENAME
7839 KING
7698 BLAKE
JOB
MGR
PRESIDEN
T
HIREDATE
SAL
COMM
DEPTN
O
17-Nov-81
5000
10
MANAGER
7839
1-May-81
2850
30
MANAGER
7839
9-Jun-81
2450
10
MANAGER
7839
2-Apr-81
2975
20
7654 MARTIN SALEMAN
7499 ALLEN
SALEMAN
7698
28-Sep-81
1250
1400
30
7698
20-Sep-81
1600
300
30
7844 TURNER SALEMAN
7900 JAMES
CLERK
7698
8-Sep-81
1500
0
30
7698
3-Dec-81
950
7521 WARD
7902 FORD
SALEMAN
7698
22-Feb-81
1250
ANALYTS
7566
3-Dec-81
3000
20
7369 SMITH
7788 SCOTT
CLERK
7902
17-Dec-80
800
20
ANALYST
7566
9-Dec-82
3000
20
CLERK
7788
12-Jan-83
1100
7782
23-Jan-82
1300
7782 CLARK
7566 JONES
7876 ADAMS
17 July 2015
7934 MILLER
CLERK
Create By Pantharee Sawasdimongkol
30
500
30
20
10
10
Relating Multiple Tables


Each row of data in a table is uniquely
identified by a primary key (PK).
You ca logically relate data from multiple
tables using foreign keys (FK).
Table Name : EMP
Primary Key
17 July 2015
Table Name : DEPT
Foreign Key
Primary Key
Create By Pantharee Sawasdimongkol
11
Relational Database Properties
A relational database
 Can be accessed and modified by
executing structured query language
(SQL) statement


Contains a collection of tables with
mo physical pointers
Uses set of operators
17 July 2015
Create By Pantharee Sawasdimongkol
12
Communicating with a RDBMS
using SQL
SQL statement
Is entered
SQL > SELECT loc
2
From
dept ;
Statement is sent to
database
Database
Data is displayed
Loc
-------------------NEW YORK
DALLAS
CHICAGO
BOSTON
17 July 2015
Create By Pantharee Sawasdimongkol
13
Relation Database
Management System
server
User tables
17 July 2015
Create By Pantharee Sawasdimongkol
Data
dictionary 14
Oracle10g: Object Relational
Database Management System




User-defined data types and objects
Fully compatible with relational
database
Support of multimedia and large
objects
High-quality database server
features
17 July 2015
Create By Pantharee Sawasdimongkol
15
Oracle 8i: Internet Platform
Database for Internet Computing
Features





Advanced tools to manage all types of
data on web sites
More than a simple relational data store:
iFS
Integrated Java VM in the server” JServer
Better performance, stronger security,
language improvement
Greater integration with Windows NT
environment: AppWizard
17 July 2015
Create By Pantharee Sawasdimongkol
16
Oracle Internet Platform
Clients
Any mail
Any browser
client
Eudora
dos
Internet applications
Business logic
and data
Database
Presentation
and data
Application
servers
Network services
Create By Pantharee Sawasdimongkol
Development tools
System management
N
17 July 2015
Any FTP client
SQL
PL/SQL
JAVA
17
SQL Statements
SELECT
INSERT
UPDATE
DELETE
Data retrieval
Data Manipulation Language (DML)
CREATE
ALTER
DROP
Data definition language (DDL)
RENAME
TRUNCATE
COMMIT
ROLLBACK
SAVEPOINT
Transaction control
GRANT
REVOKE
Data control language (DCL)
17 July 2015
Create By Pantharee Sawasdimongkol
18
About PL/SQL


PL / SQL is an extension to SQL with
design features of programming
language.
Data manipulation and query
statements
of SQL are included with in
procedural
units of code.
17 July 2015
Create By Pantharee Sawasdimongkol
19
PL/ SQL Environment
PL / SQL engine
PL / SQL
PL / SQL
block
PL / SQL
block
SQL
Procedural
Statement
Executor
SQL Statement Executor
Oracle Server
17 July 2015
Create By Pantharee Sawasdimongkol
20
Table Used in the Course
EMP
EMPNO ENAME
JOB
COMM
DEPTNO
------------------- --------------------------7369
SMITH
CLERK
20
7499
ALLEN
SALESMAN
30
7521
WARD
SALESMAN
30
DEPTNO
--------10
20
30
40
17 July 2015
DEPT
DNAME
LOC
--------------- -----------ACCOUNTING NEW YORK
RESEARCH
DALLAS
SALES
CHICAGO
OPERATIONS BOSTON
MGR
HIREDATE
---------7902
7698
7698
SAL
--------17-DEC-80
20-FEB-81
22-FEB-81
GRADE
---------1
2
3
4
5
Create By Pantharee Sawasdimongkol
---------800
1600
300
1250
LOSAL
---------700
1201
1401
2001
3001
SALGRADE
500
HISAL
---------1200
1400
2000
3000
9999
21
Summary





Relational databases are composed of relations,
managed by relational operations, and governed
by data integrity constraints.
The Oracle Server allows you to store and
manage information by using the SQL language
and PL/SQL engine.
Oracle8 is based on the object relational
database management system.
The Oracle8i Server is the database for Internet
computing.
PL/SQL is an extension to SQL with design
features of programming languages
17 July 2015
Create By Pantharee Sawasdimongkol
22