Enhanced Guide to Oracle

1
Enhanced Guide to Oracle
Client/Server Databases and the Oracle
Relational Database
2
File-Based Data Processing

• Each application has data files that correspond
  to only that application

Checking Account Data Files
Auto Loan Data Files
Checking Account Programs
Auto Loan Programs
Savings Account Data Files
Savings Account Programs
3
Problems with File-Based Processing

• Each application must have duplicate programs for
  file-handling operations
• Duplicate data exists in each applications data
  files
• Duplicate data can become inconsistent

4
Database Processing

• All applications interface with the same data in
  a central database

Checking Account Programs
Database Management System
Auto Loan Programs
Database
Savings Account Programs
5
Database Processing

• Database Management System (DBMS) provides
  central set of common functions to manage data
• Insert
• Update
• Retrieve
• View
• Oracle is a relational, client/server DBMS

6
Types of Database Systems

• Hierarchical data relationships are maintained
  using pointers
• Pointers create links using physical hardware
  locations
• Relational data relationships are maintained
  using shared data values called key fields

7
Hierarchical Databases
Parent Data
Pointer
Child Data
8
Relational Databases

• Data is organized in tables
• Columns (fields) represent different data
  categories
• Rows (records) contain actual data values

Fields
Records
9
Relational Table Keys

• Primary key uniquely identifies a record
• Must be unique for each record
• Cannot be NULL (undefined)
• Candidate key field that could be used as a
  primary key
• Surrogate key numeric field created for the
  purpose of being the primary key

10
Relational Table Keys

• Foreign key
• Field in a table that is a primary key in another
  table
• Creates a relationship between the 2 tables
• Foreign key values must exactly match

11
Table Relationships
Primary Key
Foreign Key
12
Composite Key

• Primary key made of the combination of two or
  more fields

Composite Key
13
Database System Architectures

• Mainframe databases
• Both DBMS and applications run on mainframe
  computer
• Users access database and applications using
  terminals
• Personal databases
• Both DBMS and applications run on users
  workstation
• Client/server databases
• DBMS runs on central server
• Applications run on users (client) workstation

14
Advantages of Client/Server Databases

• Handling server and client failures
• Processing transactions
• Handling high data volumes
• Providing security
• Servicing multiple simultaneous users

15
The Oracle Environment(Server Side)

• Enterprise Edition (on central DB server)
• Or
• Oracle Personal Edition
• Personal database that acts just like a
  client/server database

16
The Oracle EnvironmentClient Side

• SQLPlus PL/SQL
• Developer
• Procedure Builder
• Form Builder
• Report Builder
• Graphics Builder
• Project Builder

17
Case Study Databases

• Clearwater Traders
• Inventory and sales order system
• Northwoods University
• Student registration system
• Software Experts
• Project management system

18
Clearwater Traders Database Tables

• CUSTOMER
• CUST_ORDER
• ORDER_SOURCE
• ITEM
• CATEGORY
• INVENTORY
• SHIPMENT
• SHIPMENT_LINE
• ORDER_LINE
• COLOR

19
Northwoods University Database Tables

• STUDENT
• FACULTY
• LOCATION
• TERM
• COURSE
• COURSE_SECTION
• ENROLLMENT

20
Software Experts Database Tables

• CONSULTANT
• SKILL
• CONSULTANT_SKILL
• CLIENT
• PROJECT
• PROEJCT_SKILL
• PROJECT_CONSULTANT
• EVALUATION