C H A P T E R

1
CHAPTER 1 Introduction to Client/Server
Databases
A GUIDE TO ORACLE8
1
2
File-Based Approach to Data Processing
Checking Account Data Files
Checking Account Programs
Figure 1-1
Auto Loan Data Files
Auto Loan Programs
Savings Account Data Files
1
Savings Account Programs
3
Problems with Data File Approach

• Redundant programs
• Duplicate data
• Inconsistent data

1
4
Database Approach to Data Processing
Checking Account Programs
Organizational Database
Database Management System
Auto Loan Programs
Figure 1-2
Savings Account Programsh
1
5
Personal Databases

• Single users desktop computer
• DBMS and Client Database applications are stored
  on users workstation
• All files are transmitted across network

1
6
Personal Database Structure
File Server 1. Listens for file requests. 2.
Sends and receives files.
Figure 1-3
File Requests Updated Files
Requested Files
File Requests Updated Files
Requested Files
User 1 Client Workstation (DBMS Application
Client DB Applications) 1. Send file requests.
2. Receives files. 3. Filter data from files
and/or 4. Add new data to files.
User 2 Client Workstation (DBMS Application
Client DB Applications) 1. Send file requests.
2. Receives files. 3. Filter data from files
and/or 4. Add new data to files.
1
7
Client/Server Databases

• DBMS process runs on server
• Client applications run on users workstation
• Data requests are transmitted to DBMS
• DBMS only returns filtered data

1
8
Client/Server Database Structure
Database Server (DBMS Process) 1. Listens for
data requests, new data, and updates. 2. Filters
requested data from the database and sends
it to client.
Figure 1-4
Data Request. Data input/update
Data Request. Data Input/Update.
Filtered Data
Filtered Data
User 1 Client Workstation (Client DB
Application) 1. Send data request. 2. Receive
filtered data. or 3. Send data input or update.
User 2 Client Workstation (Client DB
Application) 1. Send data request. 2. Receive
filtered data. or 3. Send data input or update.
1
9
Other Advantages of Client/Server Databases

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

1
10
Overview of Databases

• Hierarchical
• Parent-to-child relationships
• Relationships maintained using pointers
• Relational
• Data stored in tables
• Relationships created through shared key fields

1
11
Hierarchical Database
1
Figure 1-5
12
Relational Database Terms

• Table Matrix with columns and rows
• Column Data category
• Also called fields
• Row Contains actual data values
• Also called records

1
13
Relational Database With Two Tables
1
Figure 1-6
14
Relational Database Terms

• Primary key Field whose value must be unique
  for each record
• Candidate key Field that could be used for
  primary key
• Foreign key
• Value must be a primary key in another table
• Used to create a relationship

1
15
Creating a Relationship With a Foreign Key
1
Figure 1-7
16
Relational Database Terms

• Composite key Primary key created using
  multiple fields
• Surrogate key Numerical value generated by the
  database for use as a primary key

1
17
Example of a Composite Primary Key
Figure 1-8
1
18
(No Transcript)
19
The Oracle8 Environment

• SQLPlus
• PL/SQL
• Developer
• Procedure Builder
• Query Builder
• Form Builder
• Report Builder
• Graphics Builder
• Project Builder
• Oracle Application Server (Web pages)

1
20
Case Study Databases

• Clearwater Traders
• Mail-order clothing and sporting goods retailer
• Northwoods University
• University registration system

1
21
Clearwater Traders Tables

• CUSTOMER
• CUST_ORDER
• ITEM
• INVENTORY
• SHIPPING

• BACKORDER
• ORDERLINE
• COLOR
• ORDERSOURCE

1
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
Lookup Tables

• Lookup table List of legal values for a field
• COLOR
• ORDERSOURCE

1
26
Northwoods University Tables

• STUDENT
• FACULTY
• LOCATION
• TERM
• COURSE

• COURSE_SECTION
• ENROLLMENT

1
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)