I Introduction Copyright 2006 Oracle All rights reserved

I Introduction Copyright © 2006, Oracle. All rights reserved.

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

Goals of the Course After completing this course, you should be able to do the following: • Identify the major structural components of Oracle Database 10 g • Retrieve row and column data from tables with the SELECT statement • Create reports of sorted and restricted data • Employ SQL functions to generate and retrieve customized data • Run data manipulation language (DML) statements to update data in Oracle Database 10 g • Obtain metadata by querying the dictionary views I-3 Copyright © 2006, Oracle. All rights reserved.

Oracle 10 g One vendor Scalability Unified management Reliability Single development model I-4 Common skillsets Copyright © 2006, Oracle. All rights reserved.

Oracle 10 g I-5 Copyright © 2006, Oracle. All rights reserved.

Oracle Database 10 g Object-relational data Documents Multimedia Messages I-6 Copyright © 2006, Oracle. All rights reserved.

Oracle Application Server 10 g Portals Transactional applications Business intelligence Integration Application development framework I-7 Application server Copyright © 2006, Oracle. All rights reserved.

Oracle Enterprise Manager 10 g Grid Control • Software provisioning • Application service level monitoring I-8 Copyright © 2006, Oracle. All rights reserved.

Relational and Object Relational Database Management Systems • • • I-9 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 Copyright © 2006, Oracle. All rights reserved.

Oracle Internet Platform Clients Any mail client FTP client Internet applications Business logic Presentation and data business logic Databases Application servers Development tools System management Any browser Network services I - 10 Copyright © 2006, Oracle. All rights reserved. SQL PL/SQL Java

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

Data Storage on Different Media Electronic spreadsheet I - 13 Database Filing cabinet Copyright © 2006, Oracle. 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 I - 14 Copyright © 2006, Oracle. All rights reserved.

Definition of a Relational Database A relational database is a collection of relations or two-dimensional tables. Oracle server Table name: EMPLOYEES … I - 15 Table name: DEPARTMENTS … Copyright © 2006, Oracle. All rights reserved.

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

Entity Relationship Model • Create an entity relationship diagram from business specifications or narratives: EMPLOYEE #* number * name o job title DEPARTMENT #* number * name composed of o location assigned to • Scenario – “. . . Assign one or more employees to a department. . . ” – “. . . Some departments do not yet have assigned employees. . . ” I - 17 Copyright © 2006, Oracle. All rights reserved.

Entity Relationship Modeling Conventions Attribute Entity • • Singular, unique name Uppercase Soft box Synonym in parentheses EMPLOYEE #* number * name o job title • • Singular name Lowercase Mandatory marked with * Optional marked with “o” DEPARTMENT #* number * name composed of o location assigned to Unique identifier (UID) Primary marked with “#” Secondary marked with “(#)” I - 19 Copyright © 2006, Oracle. 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 I - 21 Foreign key Primary key Copyright © 2006, Oracle. All rights reserved.

Relational Database Terminology 2 3 4 5 6 1 I - 23 Copyright © 2006, Oracle. 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 I - 25 Copyright © 2006, Oracle. All rights reserved.

Communicating with an RDBMS Using SQL statement is entered. SELECT department_name FROM departments; Statement is sent to Oracle server I - 26 Copyright © 2006, Oracle. All rights reserved.

Oracle’s Relational Database Management System Oracle server User tables I - 27 Copyright © 2006, Oracle. All rights reserved. Data dictionary

SQL Statements I - 28 SELECT INSERT UPDATE DELETE MERGE Data manipulation language (DML) CREATE ALTER DROP RENAME TRUNCATE COMMENT Data definition language (DDL) GRANT REVOKE Data control language (DCL) COMMIT ROLLBACK SAVEPOINT Transaction control Copyright © 2006, Oracle. All rights reserved.

Tables Used in the Course EMPLOYEES DEPARTMENTS I - 29 Copyright © 2006, Oracle. All rights reserved. JOB_GRADES

Summary • Oracle Database 10 g is the database for grid computing. • 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. • With the Oracle server, you can store and manage information by using the SQL language and PL/SQL engine. I - 30 Copyright © 2006, Oracle. All rights reserved.