Transcript Document - Oman College of Management & Technology

Enhanced Guide to Oracle8i
Chapter 1:
Client/Server Databases and the
Oracle8i Relational Database
1
File-Based Data Processing
 Each application has data files that
correspond to only that application
Checking
Account
Data
Files
Checking
Account
Programs
Savings
Account
Data
Files
Auto Loan
Data
Files
Auto
Loan
Programs
Savings
Account
Programs
2
Problems with File-Based
Processing
 Each application must have
duplicate programs for filehandling operations
 Duplicate data exists in each
application’s data files
 Duplicate data can become
inconsistent
3
Database Processing

All applications interface with the same
data in a central database
Database
Database
Management
System
Auto
Loan
Programs
Checking
Account
Programs
Savings
Account
Programs
4
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
5
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
6
Hierarchical Databases
Customer Data
Name
Address
Phone
Maria Chavez
John Severson
Harold Brown
1441 Adams Court
8988 Grover Road
3511 Pluto Drive
605-777-8992
605-898-2314
605-666-3298
Checking Account Data
Acct. Number
Current Balance
986-335
988-310
355-822
445.11
2988.44
898.14
Date Last
Transaction
07/11/03
07/01/03
06/15/03
Auto Loan Data
Acct. Number
100988
101732
Current Balance
5676
1545.33
Date Last Pmt
06/15/03
07/01/03
Pointer to Checking
Acct. Data
Pointer to Auto
Loan Data
Parent
Data
Pointer
Child
Data
7
Relational Databases

Data is organized in tables


Columns (fields) represent different data
categories
Rows (records) contain actual data values
Fields
PRODUCT_ID
DESCRIPTION
QUANTITY_ON
_HAND
1
Plain Cheesecake
8
2
Cherry Cheesecake
10
Records
8
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
9
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
10
Table Relationships
Primary Key
PRODUCT_ID
DESCRIPTION
QUANTITY_ON
_HAND
1
Plain Cheesecake
8
2
Cherry Cheesecake
10
Foreign Key
ORDER_ID
PRODUCT_ID
ORDER_QUANTITY
100
1
2
100
2
2
200
2
2
200
1
1
11
Composite Key

Primary key made of the combination of
two or more fields
Composite
Key
ORDER_ID
PRODUCT_ID
ORDER_QUANTITY
100
1
2
100
2
2
200
2
2
200
1
1
12
Database System
Architectures

Mainframe databases



Personal databases


Both DBMS and applications run on mainframe
computer
Users access database and applications using
terminals
Both DBMS and applications run on user’s
workstation
Client/server databases


DBMS runs on central server
Applications run on user’s (client) workstation
13
Advantages of Client/Server
Databases





Handling server and client failures
Processing transactions
Handling high data volumes
Providing security
Servicing multiple simultaneous
users
14
The Oracle8i Environment
(Server Side)
 Enterprise Edition (on central DB
server)
Or
 Oracle Personal Edition

Personal database that acts just like a
client/server database
15
The Oracle8i Environment:
Client Side
 SQL*Plus & PL/SQL
 Developer





Procedure Builder
Form Builder
Report Builder
Graphics Builder
Project Builder
16
Case Study Databases
 Clearwater Traders
 Inventory and sales order system
 Northwoods University
 Student registration system
 Software Experts
 Project management system
17
Clearwater Traders Database
Tables










CUSTOMER
CUST_ORDER
ORDER_SOURCE
ITEM
CATEGORY
INVENTORY
SHIPMENT
SHIPMENT_LINE
ORDER_LINE
COLOR
18
Northwoods University
Database Tables







STUDENT
FACULTY
LOCATION
TERM
COURSE
COURSE_SECTION
ENROLLMENT
19
Software Experts Database
Tables








CONSULTANT
SKILL
CONSULTANT_SKILL
CLIENT
PROJECT
PROEJCT_SKILL
PROJECT_CONSULTANT
EVALUATION
20