Client/Server Databases and
the Oracle 10g Relational
Database
Objectives
• Develop an understanding of the purpose
of database systems
• Describe the purpose and contents of an
entity-relationship model
• Explain the steps in the normalization
process
• Describe the differences between personal
and client/server databases
Introduction
• If an organization needs to store and
maintain a large amount of data that must
be viewed and updated by many users at
the same time, it often uses a client/server
database such as Oracle 11g.
Database Systems
• Data files consist of:
– Fields
• Describe characteristics
• Also called columns
– Records
• Group of related fields
Database Systems (continued)
• Problems with storing data in files:
– Proliferation of data management programs
– Presence of redundant data
– Inconsistent data
• Database
– Stores all organizational data in central
location
– Eliminates redundant data to reduce
possibility of inconsistent data
Database Systems (continued)
• Database management system (DBMS)
– Application that performs all routine data-
handling operations
– Provides central set of common functions for
managing database
• Inserting
• Updating
• Retrieving
• Deleting data values
Database Approach to Data Processing
Database Systems (continued)
• Database administrator
– Person responsible for installing,
administering, and maintaining the database
– Often called DBA
Overview of Relational Databases
• Table
– 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
Overview of Relational Databases
(continued)
Overview of Relational Databases
(continued)
• Entity
– Object about which you want to store data
– Different tables store data about each
different entity
• Relationships
– Links that show how different records are
related
Overview of Relational Databases
(continued)
• Key fields
– Establish relationships among records in
different tables
– Main types of key fields
• Primary
• Candidate
• Surrogate
• Foreign
• Composite
Primary Keys
• Column in relational database table whose
value must be unique for each row
• Serves to identify individual occurrence of
entity
• Every row must have a primary key
• Cannot be NULL
• NULL
– Value is absent or unknown
– No entry is made for that data element
Candidate Keys
• Any column that could be used as the
primary key
• Should be a column that is unique for each
record and does not change
Surrogate Keys
• Column created to be record’s primary key
identifier
• Has no real relationship to row to which it
is assigned other than to identify it
uniquely
• Surrogate key values automatically
generated using a sequence
Foreign Keys
• Column in table that is a primary key in
another table
• Creates relationship between two tables
• Value must exist in table where it is the
primary key
Composite Keys
• Unique key that is created by combining
two or more columns
• Usually comprises fields that are primary
keys in other tables
Composite
Key
ORDER_ID PRODUCT_ID ORDER_QUANTITY
100 1 2
100 2 2
200 2 2
200 1 1
Database Systems
• Consists of
– DBMS
• Manages physical storage and data retrieval
– Database applications
• Provide interface that allows users to interact with
database
• Server
– Computer that shares resources with other
computers
Database Systems (continued)
• Server process
– Program that listens for requests for
resources from clients
– Responds to requests
• Client
– Program that requests and uses server
resources
Client/Server Database Management
Systems
• DBMS server process runs on one
workstation
• Database applications run on separate
client workstations across network
• Server sends only requested data back to
client rather than entire database
Client/Server Database Architecture
Client/Server Database Management
Systems (continued)
• Generate less network traffic than
personal databases
• Extra features to minimize chance of
failure
• Powerful recovery mechanisms that often
operate automatically
• Maintain file-based transaction log on
database server
Client/Server Database Management
Systems (continued)
• Preferred for
– Database applications that retrieve and
manipulate small amounts of data from
databases containing large numbers of
records
– Mission-critical applications
– Web-based database applications that require
increased security and fault tolerance
The Oracle 10g Client/Server Database
• Oracle 10g
– Latest release of Oracle Corporation’s
relational database
– Client/server database
• Server side
– DBMS server process
• Oracle Net
– Utility that enables network communication
between client and server
Client/Server Architecture for
Oracle 10g DBMS
The Oracle 10g Client/Server Database
(continued)
• Oracle Application Server
– Used to create World Wide Web pages that
allow users to access Oracle databases
• Oracle client products:
– SQL*Plus
– Oracle 10g Developer Suite
– Enterprise Manager
