copyright oracle corporation, 1999. all rights reserved. i introduction

Copyright Oracle Corporation, 1999. All rights reserved.

II

IntroductionIntroduction

I-2 Copyright Oracle Corporation, 1999. All rights reserved.

ObjectivesObjectives

After completing this lesson, you should be After completing this lesson, you should be able to do the following: able to do the following:

• List the features of Oracle 11g.

• Discuss the theoretical and physical aspects of a relational database.

• Describe the Oracle implementation of the RDBMS and ORDBMS.

• Understand the goals of the course.

After completing this lesson, you should be After completing this lesson, you should be able to do the following: able to do the following:

• List the features of Oracle 11g.

• Discuss the theoretical and physical aspects of a relational database.

• Describe the Oracle implementation of the RDBMS and ORDBMS.

• Understand the goals of the course.


Goals of the CourseGoals of the CourseAfter completing this course, you should be able After completing this course, you should be able to do the following:to do the following:

•Retrieve row and column data from tables with Retrieve row and column data from tables with the SELECT statement.the SELECT statement.

•Create reports of sorted and restricted data.Create reports of sorted and restricted data.

•Employ SQL functions to generate and retrieve Employ SQL functions to generate and retrieve customized data.customized data.

•Run data manipulation language (DML) Run data manipulation language (DML) statement to update data in Oracle.statement to update data in Oracle.

•Obtain metadata by querying the dictionary Obtain metadata by querying the dictionary views.views.

After completing this course, you should be able After completing this course, you should be able to do the following:to do the following:

•Retrieve row and column data from tables with Retrieve row and column data from tables with the SELECT statement.the SELECT statement.

•Create reports of sorted and restricted data.Create reports of sorted and restricted data.

•Employ SQL functions to generate and retrieve Employ SQL functions to generate and retrieve customized data.customized data.

•Run data manipulation language (DML) Run data manipulation language (DML) statement to update data in Oracle.statement to update data in Oracle.

•Obtain metadata by querying the dictionary Obtain metadata by querying the dictionary views.views.


Relational and Object Relational Database Management SystemsRelational and Object Relational Database Management Systems

•Relational model and object relational Relational model and object relational

model, both supported by Oracle.model, both supported by Oracle.

•User-defined data types and objects.User-defined data types and objects.

•Fully compatible with relational databaseFully compatible with relational database

•Support of multimedia and large objectsSupport of multimedia and large objects

•High-quality database server features. High-quality database server features.

•Relational model and object relational Relational model and object relational

model, both supported by Oracle.model, both supported by Oracle.

•User-defined data types and objects.User-defined data types and objects.

•Fully compatible with relational databaseFully compatible with relational database

•Support of multimedia and large objectsSupport of multimedia and large objects

•High-quality database server features. High-quality database server features.


System Development Life CycleSystem Development Life Cycle

StrategyStrategyandand

AnalysisAnalysisDesignDesign

BuildBuildandand

DocumentDocument

TransitionTransition

ProductionProduction


Data Storage on Different MediaData Storage on Different Media

Electronic Electronic spreadsheetspreadsheet

Filing cabinetFiling cabinet

DatabaseDatabase

SALGRADESALGRADE GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

SALGRADESALGRADE GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

DEPTDEPT DEPTNO DNAME LOC

--------- -------------- ----------

10 ACCOUNTING NEW

YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

DEPTDEPT DEPTNO DNAME LOC

--------- -------------- ----------

10 ACCOUNTING NEW

YORK

20 RESEARCH DALLAS

30 SALES CHICAGO



Relational Database ConceptRelational 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

• 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


Definition of a Relational Database

Definition of a Relational Database

A relational database is a collection of relations or A relational database is a collection of relations or two-dimensional tables.two-dimensional tables.A relational database is a collection of relations or A relational database is a collection of relations or two-dimensional tables.two-dimensional tables.

DatabaseDatabase

EMPNO ENAME JOB DEPTNO

7839 KING PRESIDENT 10

7698 BLAKE MANAGER 30

7782 CLARK MANAGER 10

7566 JONES MANAGER 20






Table Name: Table Name: EMPLOYEESEMPLOYEES

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO


DEPTNO DNAME LOC


20 RESEARCH DALLAS

30 SALES CHICAGO


Table Name: Table Name: DEPARTMENTSDEPARTMENTS


Data ModelsData Models

Model ofModel ofsystemsystem

in client’sin client’smindmind

Entity model ofEntity model ofclient’s modelclient’s model

Table modelTable modelof entity modelof entity model

Tables on diskTables on disk

ServerServer


• 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 . . .”

• 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 ModelEntity Relationship Model

EMPLOYEEEMPLOYEE#* #* numbernumber** namenameoo job titlejob title


DEPARTMENTDEPARTMENT#* #* numbernumber** namenameoo locationlocation


assigned toassigned toassigned toassigned to

composed ofcomposed ofcomposed ofcomposed of


Entity Relationship Modeling Conventions

Entity Relationship Modeling Conventions

EntityEntitySoft boxSoft boxSingular, unique nameSingular, unique nameUppercaseUppercaseSynonym in parenthesesSynonym in parentheses

AttributeAttributeSingular nameSingular nameLowercaseLowercaseMandatory marked with “*”Mandatory marked with “*”Optional marked with “o”Optional marked with “o”

Unique Identifier (UID)Unique Identifier (UID)Primary marked with “#”Primary marked with “#”Secondary marked with “(#)”Secondary marked with “(#)”





assigned toassigned toassigned toassigned to

composed ofcomposed ofcomposed ofcomposed of


Relational Database TerminologyRelational Database Terminology

1

2 3 4

5

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------------- ------------ --------------------- -------- ---------------- ----------- -------------- -----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

6


Relating Multiple TablesRelating Multiple Tables

• Each row of data in a table is uniquely identified by a primary key (PK).

• Each row of data in a table is uniquely identified by a primary key (PK).

Table Name: Table Name: EMPEMP Table Name: Table Name: DEPTDEPT

Primary keyPrimary key Primary keyPrimary key

• You can logically relate data from multiple tables using foreign keys (FK).

• You can logically relate data from multiple tables using foreign keys (FK).

Foreign keyForeign key






DEPTNO DNAME LOC


20 RESEARCH DALLAS

30 SALES CHICAGO



Relational Database PropertiesRelational Database Properties

A relational database 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

A relational database 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


Communicating with a RDBMS Using SQL

Communicating with a RDBMS Using SQL

SQL> SELECT loc 2 FROM dept;

SQL> SELECT loc 2 FROM dept;

SQL statementSQL statementis enteredis entered

DatabaseDatabase

Statement is sent to Statement is sent to databasedatabase

LOC

-------------

NEW YORK

DALLAS

CHICAGO

BOSTON

LOC

-------------

NEW YORK

DALLAS

CHICAGO

BOSTON

Data is displayedData is displayed


Relational Database Management SystemRelational Database Management System

User tablesUser tables Data Data dictionarydictionary

ServerServer


Oracle Internet PlatformOracle Internet PlatformClients

Sys

tem

man

agem

ent

Network services

DatabasesApplication

servers

Develo

pm

ent to

ols

Internet applications

Presentation andbusiness logic

Business logicand data

Any browser Any FTP clientAny mailclient

JavaJava

SQLSQLSQLSQL

PL/SQLPL/SQLPL/SQLPL/SQL


SQL StatementsSQL StatementsSELECT SELECT INSERTINSERTUPDATEUPDATEDELETEDELETE

CREATECREATEALTERALTERDROPDROPRENAMERENAMETRUNCATETRUNCATE

COMMITCOMMITROLLBACKROLLBACKSAVEPOINTSAVEPOINT

GRANTGRANTREVOKEREVOKE

Data manipulation language (DML)Data manipulation language (DML)

Data definition language (DDL)Data definition language (DDL)

Transaction controlTransaction control

Data control language (DCL)Data control language (DCL)


Tables Used in the CourseTables Used in the Course


--------- ---------- --------- --------- --------- --------- --------- ---------








7900 JAMES CLERK 7698 03-DEC-81 950 30


7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20


7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10


--------- ---------- --------- --------- --------- --------- --------- ---------








7900 JAMES CLERK 7698 03-DEC-81 950 30


7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20


7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

EMPLOYEESEMPLOYEES

DEPARTMENTSDEPARTMENTS

DEPTNO DNAME LOC

--------- -------------- ----------


20 RESEARCH DALLAS

30 SALES CHICAGO


DEPTNO DNAME LOC

--------- -------------- ----------


20 RESEARCH DALLAS

30 SALES CHICAGO


GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

JOB_GRADESJOB_GRADES


SummarySummary

• The database 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.

• The Oracle Server allows you to store and manage information by using the SQL language and PL/SQL engine.

• The database 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.

• The Oracle Server allows you to store and manage information by using the SQL language and PL/SQL engine.

copyright oracle corporation, 1999. all rights reserved. i introduction

Documents