Guide to oracle 10 g l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 52

Guide to Oracle 10 g PowerPoint PPT Presentation


  • 490 Views
  • Updated On :
  • Presentation posted in: General

Guide to Oracle 10 g. Chapter 1: Client/Server Databases and the Oracle 10 g 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

Related searches for Guide to Oracle 10 g

Download Presentation

Guide to Oracle 10 g

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Guide to oracle 10 g l.jpg

Guide to Oracle 10g

Chapter 1:

Client/Server Databases and the Oracle 10g Relational Database


Objectives l.jpg

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

Guide to Oracle 10g


Objectives continued l.jpg

Objectives (continued)

  • Understand the Northwoods University student registration database and the Clearwater Traders sales order database

Guide to Oracle 10g


Introduction l.jpg

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 10g.

Guide to Oracle 10g


Database systems l.jpg

Database Systems

  • Data files consist of:

    • Fields

      • Describe characteristics

      • Also called columns

    • Records

      • Group of related fields

Guide to Oracle 10g


Database systems continued l.jpg

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

Guide to Oracle 10g


Database systems continued7 l.jpg

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

Guide to Oracle 10g


Database approach to data processing l.jpg

Database Approach to Data Processing

Guide to Oracle 10g


Database systems continued9 l.jpg

Database Systems (continued)

  • Database administrator

    • Person responsible for installing, administering, and maintaining the database

    • Often called DBA

Guide to Oracle 10g


Overview of relational databases l.jpg

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 instanceof entity

Guide to Oracle 10g


Overview of relational databases continued l.jpg

Overview of Relational Databases (continued)

Guide to Oracle 10g


Overview of relational databases continued12 l.jpg

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

Guide to Oracle 10g


Overview of relational databases continued13 l.jpg

Overview of Relational Databases (continued)

  • Key fields

    • Establish relationships among records in different tables

    • Main types of key fields

      • Primary

      • Candidate

      • Surrogate

      • Foreign

      • Composite

Guide to Oracle 10g


Primary keys l.jpg

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

Guide to Oracle 10g


Candidate keys l.jpg

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

Guide to Oracle 10g


Surrogate keys l.jpg

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

Guide to Oracle 10g


Foreign keys l.jpg

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

Guide to Oracle 10g


Composite keys l.jpg

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

Guide to Oracle 10g


Database design l.jpg

Database Design

  • Main tasks involved with design of database:

    • Developing entity-relationship (ER) model

    • Normalizing database tables

Guide to Oracle 10g


Entity relationship model l.jpg

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 (1:1)

    • One to many (1:M)

    • Many to many (N:M)

Guide to Oracle 10g


Entity relationship model continued l.jpg

Entity-Relationship Model (continued)

  • One to one (1:1)

    • Each occurrence of a specific entity is found only once in each set of data

    • Rare in relational databases

  • One to many (1:M)

    • Instance can only appear once in one entity, but one or more times in the other entity

Guide to Oracle 10g


Entity relationship model continued22 l.jpg

Entity-Relationship Model (continued)

  • Many to many (N:M)

    • Instance can occur multiple times in each entity

    • Cannot be represented in physical database

    • Broken down into series of two or more 1:M relationships through use of linking tablein process of normalization

Guide to Oracle 10g


Normalization l.jpg

Normalization

  • Step-by-step process used to determine which data elements should be stored in which tables

  • Purpose

    • Eliminate data redundancy

  • Several levels of normalization

    • Forms

Guide to Oracle 10g


Normalization continued l.jpg

Normalization (continued)

  • Unnormalizeddata

    • Does not have a primary key identified

    • Contains repeating groups

  • First normal form (1NF)

    • Repeating groups removed

    • Primary key field identified

Guide to Oracle 10g


Example of unnormalized data l.jpg

Example of Unnormalized Data

Guide to Oracle 10g


Normalization continued26 l.jpg

Normalization (continued)

  • Second normal form (2NF)

    • In 1NF

    • No partial dependencies

  • Partial dependency

    • Fields within the table are dependent only on part of the primary key

Guide to Oracle 10g


Normalization continued27 l.jpg

Normalization (continued)

  • Basic procedure for identifying partial dependency:

    • Look at each field that is not part of the composite primary key

    • Make certain you are required to have bothparts of the composite field to determine the value of the data element

Guide to Oracle 10g


Normalization continued28 l.jpg

Normalization (continued)

  • Third normal form (3NF)

    • In 2NF

    • No transitive dependencies

  • Transitive dependency

    • Field is dependent on another field within the table that is notthe primary key field

Guide to Oracle 10g


Database systems29 l.jpg

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

Guide to Oracle 10g


Database systems continued30 l.jpg

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

Guide to Oracle 10g


Personal database management systems l.jpg

Personal Database Management Systems

  • DBMS and database applications run on same workstation

  • Appear to user as a single integrated application

  • Used primarily for creating single-user database applications

  • Can also be used for some multiuser applications

  • Should be used only for applications that are not mission critical

Guide to Oracle 10g


Personal database management systems continued l.jpg

Personal Database Management Systems (continued)

  • Microsoft Access

    • Stores all data for database in a single file with an .mdb extension

    • Database administrator stores .mdb file on a central file server

Guide to Oracle 10g


Using a personal database for a multiuser application l.jpg

Using a Personal Database for a Multiuser Application

Guide to Oracle 10g


Personal database management systems continued34 l.jpg

Personal Database Management Systems (continued)

  • Transaction processing

    • Grouping related database changes into units of work that must either all succeed or all fail

    • DBMS can use the transaction log to reverse—or roll back—the changes

Guide to Oracle 10g


Client server database management systems l.jpg

Client/Server Database Management Systems

  • DBMS server process runs on one workstation

  • Database applications run on separate client workstations across network

  • Server sends onlyrequested data back to client rather than entire database

Guide to Oracle 10g


Client server database architecture l.jpg

Client/Server Database Architecture

Guide to Oracle 10g


Client server database management systems continued l.jpg

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

Guide to Oracle 10g


Client server database management systems continued38 l.jpg

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

Guide to Oracle 10g


The oracle 10 g client server database l.jpg

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

Guide to Oracle 10g


Client server architecture for oracle 10 g dbms l.jpg

Client/Server Architecture for Oracle 10g DBMS

Guide to Oracle 10g


The oracle 10 g client server database continued l.jpg

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 10gDeveloper Suite

    • Enterprise Manager

Guide to Oracle 10g


The database cases l.jpg

The Database Cases

  • Fictional organizations:

    • Clearwater Traders

    • Northwoods University

  • Design principles:

    • Convert all tables to third normal form

    • Include primary key as foreign key in table on “many” side of relationship

    • Specify data type for each column

Guide to Oracle 10g


The clearwater traders sales order database l.jpg

The Clearwater Traders Sales Order Database

  • Clothing and sporting goods through mail-order catalogs

  • Wants to begin accepting orders using Web site

  • Required data consists of information for:

    • Customers

    • Orders

    • Items

    • Shipments

Guide to Oracle 10g


The clearwater traders sales order database continued l.jpg

The Clearwater Traders Sales Order Database (continued)

  • Tables:

    • CUSTOMER

    • ORDER_SOURCE

    • ORDERS

    • CATEGORY

    • ITEM

Guide to Oracle 10g


The clearwater traders sales order database continued45 l.jpg

The Clearwater Traders Sales Order Database (continued)

  • Tables (continued): :

    • ORDER_LINE

    • SHIPMENT

    • INVENTORY

    • SHIPMENT_LINE

    • COLOR

Guide to Oracle 10g


Visual representation of the clearwater traders database l.jpg

Visual Representation of the Clearwater Traders Database

Guide to Oracle 10g


The northwoods university student registration database l.jpg

The Northwoods University Student Registration Database

  • Student registration system

  • Data items consist of information about:

    • Students

    • Courses

    • Instructors

    • Student Enrollment

Guide to Oracle 10g


The northwoods university student registration database continued l.jpg

The Northwoods University Student Registration Database (continued)

  • Tables:

    • LOCATION

    • FACULTY

    • STUDENT

    • TERM

    • COURSE

    • COURSE_SECTION

    • ENROLLMENT

Guide to Oracle 10g


Visual representation of the northwoods university database l.jpg

Visual Representation of the Northwoods University Database

Guide to Oracle 10g


Summary l.jpg

Summary

  • Relational databases

    • Store data in tabular format

    • Create relationships that link related data using key columns

  • Primary key

    • Column that uniquely identifies specific record

  • Foreign key

    • Creates relationship between two tables

Guide to Oracle 10g


Summary continued l.jpg

Summary (continued)

  • Entity-relationship (ER) model

    • Used to describe types of relationships between entities

  • Normalization process

    • Used to determine which fields belong in which tables

  • Database system consists of

    • DBMS

    • Database applications

Guide to Oracle 10g


Summary continued52 l.jpg

Summary (continued)

  • Client/server databases divide database into

    • Server process that runs on network server

    • User application processes that run on individual client workstations

  • Oracle 10g is a client/server database

Guide to Oracle 10g


  • Login