Intro to structured query language .ppt

introduction to Sql

1. I
Copyright © Oracle Corporation, 2001. All rights
reserved.
Introduction

2. I-2 Copyright © Oracle Corporation, 2001. All rights
reserved.
Objectives
After completing this lesson, you should be able
to do the following:
•List the features of Oracle9i
•Discuss the theoretical and physical aspects of
a relational database
•Describe the Oracle implementation of the
RDBMS and ORDBMS

3. I-3 Copyright © Oracle Corporation, 2001. All rights
reserved.
Oracle9i
Scalability
Reliability
Single
development
model
Common
skill sets
One
management
interface
One
vendor

4. I-4 Copyright © Oracle Corporation, 2001. All rights
reserved.
Oracle9i

5. I-5 Copyright © Oracle Corporation, 2001. All rights
reserved.
Oracle9i Application Server
Business IntelligenceBusiness intelligence
Transactional AppsTransactional Apps
PortalsPortals
A
P
A
C
H
E
IntegrationIntegration

6. I-6 Copyright © Oracle Corporation, 2001. All rights
reserved.
Oracle9i Database
MultimediaMultimedia
Object Relational DataObject Relational Data
MessagesMessages
Documents
XML
Documents
XML

7. I-7 Copyright © Oracle Corporation, 2001. All rights
reserved.
Relational and Object Relational Database
Management System
•Relational model and object relational model
•User-defined data types and objects
•Fully compatible with relational database
•Support of multimedia and large objects
•High-quality database server features

8. I-8 Copyright © Oracle Corporation, 2001. All rights
reserved.
Oracle Internet Platform
Clients
S
y
s
t
e
m

m
a
n
a
g
e
m
e
n
t
Network services
Databases
Application
servers
D
e
v
e
l
o
p
m
e
n
t

t
o
o
l
s
Internet applications
Presentation and
business logic
Business logic
and data
Any browser Any FTP client
Any mail
client
Java
SQLSQL
PL/SQLPL/SQL

9. I-9 Copyright © Oracle Corporation, 2001. All rights
reserved.
System Development Life Cycle
Strategy
and
analysis
Design
Build
and
document
Transition
Production

10. I-11 Copyright © Oracle Corporation, 2001. All rights
reserved.
Data Storage on Different Media
Electronic
spreadsheet
Filing cabinet
Database

11. I-12 Copyright © Oracle Corporation, 2001. All rights
reserved.
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

12. I-13 Copyright © Oracle Corporation, 2001. All rights
reserved.
Definition of a Relational Database
A relational database is a collection of relations or
two-dimensional tables.
OracleOracle
serverserver
Table Name: EMPLOYEES Table Name: DEPARTMENTS
… …

13. I-14 Copyright © Oracle Corporation, 2001. All rights
reserved.
Data Models
Model of
system
in client’s
mind
Entity model of
client’s model
Table model
of entity model
Tables on disk
Oracle
server

14. I-15 Copyright © Oracle Corporation, 2001. All rights
reserved.
•Create an entity relationship diagram from
business specifications or narratives
•Scenario
–“. . . Assign one or more employees to a
department . . .”
–“. . . Some departments do not yet have assigned
employees . . .”
Entity Relationship Model
EMPLOYEEEMPLOYEE
#* #* numbernumber
** namename
oo job titlejob title
DEPARTMENTDEPARTMENT
#* #* numbernumber
** namename
oo locationlocation
assigned to
composed of

15. I-16 Copyright © Oracle Corporation, 2001. All rights
reserved.
Entity Relationship
Modeling Conventions
Entity
Soft box
Singular, unique name
Uppercase
Synonym in parentheses
Attribute
Singular name
Lowercase
Mandatory marked with “*”
Optional marked with “o”
Unique Identifier (UID)
Primary marked with “#”
Secondary marked with “(#)”
EMPLOYEEEMPLOYEE
#* #* numbernumber
** namename
oo job titlejob title
DEPARTMENTDEPARTMENT
#* #* numbernumber
** namename
oo locationlocation
assigned to
composed of

16. I-18 Copyright © Oracle Corporation, 2001. All rights
reserved.
Relating Multiple Tables
•Each row of data in a table is uniquely
identified by a primary key (PK).
•You can logically relate data from multiple
tables using foreign keys (FK).
Table Name: EMPLOYEES
Table Name: DEPARTMENTS
Primary key Primary keyForeign key
…

17. I-19 Copyright © Oracle Corporation, 2001. All rights
reserved.
Relational Database Terminology
1
2
3 4
5
6

18. I-20 Copyright © Oracle Corporation, 2001. All rights
reserved.
Relational Database Properties
A relational database:
•Can be accessed and modified by executing
structured query language (SQL) statements
•Contains a collection of tables with no physical
pointers
•Uses a set of operators

19. I-21 Copyright © Oracle Corporation, 2001. All rights
reserved.
Communicating with a RDBMS
Using SQL
SELECT department_name
FROM departments;
SQL statement
is entered.
Oracle
server
Statement is sent to
Oracle Server.

20. I-22 Copyright © Oracle Corporation, 2001. All rights
reserved.
Relational Database Management System
User tables Data
dictionary
Oracle
server

21. I-23 Copyright © Oracle Corporation, 2001. All rights
reserved.
SQL Statements
SELECT

INSERT
UPDATE
DELETE
MERGE
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMIT
ROLLBACK
SAVEPOINT
GRANT
REVOKE
Data retrieval
Data manipulation language (DML)
Data definition language (DDL)
Transaction control
Data control language (DCL)

22. I-24 Copyright © Oracle Corporation, 2001. All rights
reserved.
Tables Used in the Course
EMPLOYEES
DEPARTMENTS JOB_GRADES

23. I-25 Copyright © Oracle Corporation, 2001. All rights
reserved.
Summary
•The Oracle9i Server is the database for Internet
computing.
•Oracle9i is based on the object relational database
management system.
•Relational databases are composed of relations,
managed by relational operations, and governed
by data integrity constraints.
•With the Oracle Server, you can store and manage
information by using the SQL language and
PL/SQL engine.