Guide to oracle 10 g
Download
1 / 52

Guide to Oracle 10 g - PowerPoint PPT Presentation


  • 559 Views
  • Updated On :

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Guide to Oracle 10 g' - Ava


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



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



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



Summary l.jpg
Summary (continued)

  • 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) (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) (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