guide to oracle 10 g l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Guide to Oracle 10 g PowerPoint Presentation
Download Presentation
Guide to Oracle 10 g

Loading in 2 Seconds...

play fullscreen
1 / 52

Guide to Oracle 10 g - PowerPoint PPT Presentation


  • 579 Views
  • Uploaded 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

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

Guide to Oracle 10g

Chapter 1:

Client/Server Databases and the Oracle 10g Relational Database

objectives
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
Objectives (continued)
  • Understand the Northwoods University student registration database and the Clearwater Traders sales order database

Guide to Oracle 10g

introduction
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
Database Systems
  • Data files consist of:
    • Fields
      • Describe characteristics
      • Also called columns
    • Records
      • Group of related fields

Guide to Oracle 10g

database systems continued
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
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
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
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
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
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
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
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
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
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
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
Database Design
  • Main tasks involved with design of database:
    • Developing entity-relationship (ER) model
    • Normalizing database tables

Guide to Oracle 10g

entity relationship model
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
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
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
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
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
Example of Unnormalized Data

Guide to Oracle 10g

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

the oracle 10 g client server database continued
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
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
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
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
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
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
The Northwoods University Student Registration Database (continued)
  • Tables:
    • LOCATION
    • FACULTY
    • STUDENT
    • TERM
    • COURSE
    • COURSE_SECTION
    • ENROLLMENT

Guide to Oracle 10g

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