Chapter 1: ClientServer Databases and the Oracle10g Relational Database

1
Chapter 1Client/Server Databases andthe
Oracle10g RelationalDatabase

• Jason C. H. Chen, Ph.D.
• Professor of MIS
• School of Business Administration
• Gonzaga University
• Spokane, WA 99258
• chen_at_jepson.gonzaga.edu

2
Objectives

• Develop an understanding of the purpose of
  database systems
• Become familiar with the structure of a
  relational database and review relational
  database concepts and terms
• Explore the differences between personal
  databases and client/server databases
• Learn about the Clearwater Traders sales order
  database and the Northwoods University student
  registration database

3
Before Databases

• Information was kept in files
• Each field describes one piece of information
  about student
• Fields are separated by commas
• A record is a collection of related fields
• Each record is a separate line

4
Problems with Files

• Proliferation of data management programs to deal
  with different file formats
• Redundant data stored in files
• Data files may contain inconsistent data

5
Database Approach to Data Processing
Checking Account Programs
Organizational Database
Auto Loan Programs

Provides central set of common functions for
managing database ______, ________, Retrieving,
Deleting data values
Savings Account Programs
6
Database Approach

• Database stores all organizational data in a
  central location
• Good database design eliminates redundant data to
  reduce the possibility of inconsistent data
• Single application called the database management
  system (DBMS) performs all routine data handling
  operations
• Database administrator (DBA) person responsible
  for installing, administering, and maintaining
  the database

7
Overview of Relational Databases

• _______
• Matrix with columns and rows
• Columns/_____
• Represent different data fields
• Characteristics or attributes about entity
• Rows/______
• Contain individual records
• Attributes about a specific instance of entity

8
Relational Databases

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

PRODUCT table
Fields
Records
9
Relational Databases
Figure 1-5
10
Relational Database Terms

• Entity an object about which you want to store
  data
• Table, column, row
• Flat file, attribute (or field), record
• Relationships links that show how different
  records are related
• Key Fields establish relationships among records
  in different tables
• Five main types of key fields

11
Primary Keys vs. Candidate Keys

• Primary key
• Value must be unique for each record
• Serves to identify the record
• Present in every record
• Cant be NULL
• Should be numeric
• Candidate key
• Any field that could be used as the primary key
• Should be a unique, unchanging numeric field

12
Surrogate Keys

• Surrogate key created to be the records primary
  key identifier when no suitable primary key
  exists
• Surrogate key has no real relationship to the
  record to which it is assigned, other than to
  identify the record uniquely
• Developers configure the database to generate
  surrogate key values automatically
• In an Oracle database, you can automatically
  generate surrogate key values using a sequence
• Surrogate keys are always numerical fields,
  because the database generates surrogate key
  values automatically by incrementing the previous
  value by one

13
Foreign Keys

• Foreign key a field in a table that is a primary
  key in another table
• Foreign key creates a relationship between the
  two tables
• Foreign key value must exist in the table where
  it is a primary key

Figure 1-8
14
Composite Key

• Composite key a unique key that you create by
  combining two or more fields
• Usually comprised of fields that are primary keys
  in other tables

Composite Key
15
Database Design

• Main tasks involved with design of database
• Developing ____________ (ER) model
• _________ database tables

16
Entity-Relationship Model

• Designed to help you identify which entities need
  to be included in database
• Composed of
• Squares representing entities
• Lines representing relationships
• Types of relationships
• One to one (11)
• One to many (1M)
• Many to many (NM)

17
Normalization

• Step-by-step process used to determine which data
  elements should be stored in which tables
• Purpose
• Minimize data redundancy
• First normal form (1NF)
• 2NF
• 3NF
• We will study how to build a (E/R) model and how
  to Normalize the model

18
Database System Architectures

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

19
Figure 1-21 Personal Database used for
Multi-user Application
No knowledge of SQL/ other d.b. language
File Server 1. Listens for
file requests 2. Sends and receives files
d.b.
2. Listens for file request
3. Sends requested file or entire database
files
Network
User 1 Client Workstation (DBMS application
and Client DB applications) 1 .Sends file
requests 2. Receives entire files from the file
server 3. Filers data from files
and/or 4. Adds new data to files
User 2 Client Workstation (DBMS application
and Client DB applications) 1. Sends file
requests 2. Receives entire files from the file
server 3. Filers data from files
and/or 4. Adds new data to files
(DBMS)
(DBMS)
20
Question Total Sales for the First Quarter, in
year 2007

• 1. Client receives the request from the user
• 2. Client sends requests to the file server
• 3. Server sends the entire d.b. files to the
  requested client
• 4. Client processes/computes the total sales for
  the first quarter, in year 2007 with its DBMS
• 5. Send the solution back to the user

21
Figure 1-22 Client/Server Database Architecture
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, or 2. Adds or updates data
With knowledge of SQL/ other d.b. language
d.b. DBMS
Process
2. Receives and Processes data request
3. Sends requested data (result only)
Network
4. Receives requested data (result only)
User 1 Client Workstation (Client DB
application) 1 .Sends data request 2a. Receives
filtered data or 2b. Sends data input
or update
User 2 Client Workstation (Client DB
applications) 1. Sends data request 2a. Receives
filtered data or 2b. Sends data
input or update
(DBMS)
(DBMS)
Applications
22
Question Total Sales for the First Quarter, 2007

• 1. Client receives the request from the user
• 2. Client checks the syntax and generates d.b.
  request in SQL or other d.b. language appropriate
  to the application logic
• 3. Client transmits the message to the servers
  and waits ...
• 4. Server accepts and processes the d.b. requests
  and transmits the result back to the client
• 5. Client receives the servers results and send
  it back to the user

23
Client/Server Database Management Systems

• Client/server database
• Takes advantage of distributed processing and
  networked computers by distributing processing
  across multiple computers
• DBMS server process runs on one workstation, and
  the database applications run on separate client
  workstations across the network
• Preferred for database applications that retrieve
  and manipulate small amounts of data from
  databases containing large numbers of records
  because they minimize network traffic and improve
  response times
• Organizations generally use a client/server
  database if the database will have more than 10
  simultaneous users and if the database is mission
  critical

24
The Oracle10g Client/Server Database

• Oracle10g is the latest release of Oracle
  Corporations relational database management
  system
• All Oracle server- and client-side programs use
  Oracle Net, a utility that enables the network
  communication between the client and the server

25
DATABASE TRENDS
Linking Internal Databases to the Web
N
26
Web-Based Client/Server Database Architecture

• Network

27
System Response TimeDepends on ...

• the ______ of the network
• the ____ of the database
• the _____ the database is used
• a personal database running on a server might
  handle 10 users making database transactions at
  the same time before becoming overloaded.

28
Advantages of Client/Server Database Management
Systems

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

29
Client-Side Utilities

• SQLPlus
• for creating and testing command-line SQL queries
  and executing PL/SQL procedural programs
• Oracle10g Developer Suite
• for developing database applications including
  the following Developer tools
• Forms Builder
• for creating custom user applications
• Reports Builder
• for creating reports for displaying, printing,
  and distributing summary data
• Enterprise Manager
• for performing database administration tasks such
  as creating new user accounts and configuring how
  the DBMS stores and manages data

30
Design Principles

• To avoid creating tables that contain redundant
  data, group related items that describe a single
  entity together in a common table
• Do not create tables that duplicate values many
  times in different rows
• When creating a database and inserting data
  values, you must specify the data type for each
  column
• Recall that primary key fields should use a
  number data type to avoid typographical,
  punctuation, and case variation errors

31
SQL, SQLPlus, and PL/SQL
Terminal
Server
SQLPlus
Buffer
SQL and PL/SQL Scripts
32
The Clearwater Traders Sales Order Database

• Clearwater Traders
• Markets a line of clothing and sporting goods via
  mail-order catalogs
• Accepts customer orders via telephone, mail, and
  fax
• Wants to begin accepting orders using its Web
  site
• Has decided to offer 24-hour customer order
  service
• Existing microcomputer-based database system
  cannot handle current transaction volume
• Managers concerned that the current database does
  not have the failure-handling and recovery
  capabilities needed for an ordering system that
  cannot tolerate failures or downtime

33
The Northwoods University Student Registration
Database

• Northwoods University
• Decided to replace its aging mainframe-based
  student registration system with a more modern
  client/server database system
• School officials want students to be able to
  retrieve course availability information,
  register for courses, and print transcripts using
  personal computers located in the student
  computer labs
• Faculty members must be able to retrieve student
  course lists, drop and add students, and record
  course grades
• Faculty members must also be able to view records
  for the students they advise
• Security is a prime concern, so student and
  course records must be protected by password
  access

34
Summary

• A database stores all organizational data in a
  central location
• Database management system (DBMS) provides a
  central set of common functions for managing a
  database, including
• Inserting
• Updating
• Retrieving
• Deleting data values
• Most modern databases are relational databases
• Store data in a tabular format
• Columns represent different data fields
• Rows contain individual data records

35
Summary (cont.)

• In a relational database
• Data about different entities is stored in
  separate tables
• You create relationships that link related data
  using key fields
• A database system consists of
• DBMS manages the physical data storage
• Database applications provide the user interface
  to the database
• Many database systems use a client/server
  architecture
• DBMS runs as a server process
• Database applications run as client processes
• Oracle 10g is a client/server DBMS

36
Install your Personal Oracle
Step 1. Install Database Server and Client
(1CD) (the second CD is for documentation) Step
2. Install Developer Suite (2CDs) Step 3. When
Done Username scott P/W tiger