ClientServer Databases and the Oracle9i Relational Database - PowerPoint PPT Presentation

1 / 63
About This Presentation
Title:

ClientServer Databases and the Oracle9i Relational Database

Description:

Markets a line of clothing and sporting goods via mail-order catalogs ... Many clothing items are available in multiple sizes and colors. ... – PowerPoint PPT presentation

Number of Views:51
Avg rating:3.0/5.0
Slides: 64
Provided by: amit202
Category:

less

Transcript and Presenter's Notes

Title: ClientServer Databases and the Oracle9i Relational Database


1
Client/Server Databases andthe Oracle9i
RelationalDatabase
2
Relational Database Terms
  • Entity an object about which you want to store
    data
  • Relationships links that show how different
    records are related
  • Key Fields establish relationships among records
    in different tables
  • Five main types of key fields
  • primary keys
  • candidate keys
  • surrogate keys
  • foreign keys
  • composite keys

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

4
Candidate Keys
  • Candidate key
  • Any field that could be used as the primary key
  • Should be a unique, unchanging numeric field

5
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

6
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

7
Composite Keys
  • 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

8
Personal Database Management Systems
  • DBMS and database applications run on the same
    workstation and appear to the user as a single
    integrated application
  • Personal databases used primarily for creating
    single-user database applications
  • Support small multiuser database applications by
    storing the database application files on a file
    server instead of on a single users workstation
    and then transmitting the files or the parts of
    files containing the desired data to various
    users across a network
  • Database developers should use a personal
    database only for non-mission-critical
    applications

9
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

10
Client/Server Database Architecture
11
The Oracle9i Client/Server Database
  • Oracle9i is the latest release of Oracle
    Corporations relational database
  • All Oracle server- and client-side programs use
    Oracle Net, a utility that enables the network
    communication between the client and the server

12
Client-Side Utilities
  • SQLPlus
  • for creating and testing command-line SQL queries
    and executing PL/SQL procedural programs
  • Oracle9i 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

13
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

14
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

15
Clearwater Traders Data Requirements
  • Customer name, address, daytime and evening
    telephone numbers, user names, and passwords
  • Order date, payment method (check or credit
    card), order source (catalog description or Web
    site), and associated item numbers, sizes,
    colors, and quantities ordered
  • Item descriptions and photo images, as well as
    item categories (womens clothing, outdoor gear,
    and so on), prices, and quantities on hand. Many
    clothing items are available in multiple sizes
    and colors. Sometimes the same item has different
    prices depending on the item size
  • Information about incoming product shipments

16
Clearwater Traders Table Relationships
17
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

18
The Northwoods University Student Registration
Database (cont)
  • 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

19
Northwoods University Data Requirements
  • Student name, address, telephone number, class
    (freshman, sophomore, junior, or senior), date of
    birth, PIN (personal identification number), and
    advisor ID
  • Course call number (such as MIS 101), course
    name, credits, location, duration, maximum
    enrollment, instructor, and term offered
  • Instructor name, office location, telephone
    number, rank, and PIN
  • Student enrollment and grade information

20
Northwoods University Table Relationships
21
The Software Experts Project Management Database
  • Software Experts
  • Consulting firm that creates custom software
    applications
  • Managers determine the number and the skill sets
    of the consultants that a given project requires
  • Managers locate available consultants with the
    necessary skills
  • One consultant is named project manager
  • When completed, the project manager evaluates all
    the other consultants and all the consultants
    evaluate the project manager
  • A project can be subdivided into multiple
    subprojects

22
Software Experts Requirements
  • Consultant information, including name, address,
    city, state, ZIP code, phone number, and e-mail
    address
  • Descriptions of consultant skills
  • Client information, including the client name,
    client contact name, and contact phone number

23
Software Experts Requirements
  • Project information, including the project name,
    the client associated with the project,
    associated subprojects, the project manager, and
    required skill sets
  • Dates that a consultant started and finished
    working on a specific project and total days that
    the consultant spent working on the project
  • Consultant evaluation information, including the
    date the evaluation was completed, who performed
    the evaluation, and the evaluation score and
    comments

24
Software Experts Table Relationships
25
Introduction to SQL
  • Structured Query Language (SQL) the standard
    query language for relational databases
  • Data definition language (DDL)
  • Create new database objects
  • Modify or delete existing objects.
  • Data manipulation language (DML)
  • Insert, update, delete, and view database data.

26
Oracle9i User Accounts
  • User account - identified by a unique username
    and password
  • User schema - all of the objects that the user
    creates and stores in the database
  • Object owner has privileges to perform all
    possible actions on an object

27
Defining Oracle9i Database Tables
  • To create a new table specify
  • Table name
  • Name of each data field
  • Data type and size of each data field
  • Constraints restrictions on the data values that
    a field can store
  • Oracle Naming Standard
  • Series of rules Oracle Corporation established
    for naming all database objects
  • Objects must be from 1 to 30 characters long
  • Can contain letters, numbers, and the special
    symbols , _, and
  • Must begin with a character

28
Creating a Table
  • CREATE TABLE tablename
  • (fieldname1data_type,
  • (fieldname2 data_type,
  • )

29
Oracle9i Data Types
  • Data type specifies the kind of data that a
    field stores
  • Assigning a data type provides a means for error
    checking
  • Data types enable the DBMS to use storage space
    more efficiently by internally storing different
    types of data in different ways

30
Character Data Types
  • VARCHAR2
  • Stores variable-length character data up to a
    maximum of 4,000 characters
  • Values in different records can have a different
    number of characters
  • fieldname VARCHAR2(maximum_size)

31
Character Data Types
  • CHAR
  • Fixed-length character data up to a maximum size
    of 2,000 characters
  • Data values for different records all have the
    same number of characters
  • DBMS adds trailing blank spaces to the end of the
    entry to make the entry fill the maximum_size
    value
  • Data longer than maximum_size causes an error
  • fieldname CHAR(maximum_size)

32
Character Data Types
  • NVARCHAR2 and NCHAR
  • Analogous to VARCHAR2 and CHAR but use Unicode
    rather than ASCII
  • Used to hold character data in languages other
    than English

33
Number Data Types
  • Stores negative, positive, fixed, and floating
    point numbers between 10130 and 10125, with
    precision up to 38 decimal places
  • General Syntax fieldname NUMBER (precision,
    scale)
  • Integer fieldname NUMBER(precision)
  • Fixed point fieldname NUMBER(precision,scale
    )
  • Floating point fieldname NUMBER

34
Date and Time Data Types
  • DATE
  • Dates from December 31, 4712 BC to December 31,
    4712 AD
  • Default format DD-MON-YY
  • Default time format HHMISS A.M.
  • fieldname DATE
  • TIMESTAMP
  • Similar to DATE but stores fractional seconds
  • fieldname TIMESTAMP (fractional_seconds_precision)

35
Date and Time Data Types
  • INTERVAL YEAR TO MONTH
  • Time interval, expressed in years and months
  • 02-11 specifies a positive time interval of 2
    years and 11 months
  • fieldname INTERVAL YEAR(year_precision) TO
    MONTH
  • INTERVAL DAY TO SECOND
  • Time interval, expressed in days, hours, minutes,
    and seconds
  • -04 032032.00 4 days, 3 hours, 20 minutes, and
    32 seconds
  • fieldname INTERVAL DAY(leading_precision) TO
    SECOND(fractional_seconds_precision)

36
Large Object (LOB) Data Types
37
Constraints
  • Rules that restrict the data values that you can
    enter into a field in a database table
  • Integrity constraints define primary and foreign
    keys
  • Value constraints define specific data values or
    data ranges that must be inserted into columns
    and whether values must be unique or not NULL
  • Table constraint restricts the data value with
    respect to all other values in the table
  • Field constraint limits the value that can be
    placed in a specific field, irrespective of
    values that exist in other table records

38
Oracle Constraint Naming Convention
  • tablename_ fieldname_constraintID

39
Integrity Constraints
  • Define primary key fields
  • Specify foreign keys and their corresponding
    table and column references
  • Specify composite keys

40
Value Constraints
  • Check conditions field value must be a specific
    value or fall within a range of values
  • NOT NULL constraints specify whether a field
    value can be NULL
  • Default constraints specify that a field has a
    default value that the DBMS automatically inserts
    for every record, unless the user specifies an
    alternate value
  • Unique constraints specify that a field must
    have a unique value for every table record

41
Creating Database Tables Using SQLPlus
  • Type SQL commands at the SQL prompt
  • End each command with a semicolon ()
  • Not case sensitive

42
Log On to SQLPlus
43
SQLPlus Program Window
44
Create a Table
45
Table Creation Sequence
  • When creating tables with foreign key references,
    create referenced tables first
  • Always create tables without foreign keys before
    those with foreign keys

46
Using Notepad
  • Useful to use Notepad to edit sql commands
  • Commands can be edited without retyping
  • Commands can be saved
  • Saving multiple sql commands in a file creates a
    script

47
Errors
  • When an error occurs error information is
    displayed
  • Line number
  • Position on line
  • Error code
  • Description of error
  • Error codes
  • 3 letter prefix (I.e. ORA)
  • 5 digit code
  • More information on errors can be found at
    http//otn.oracle.com

48
Exiting SQLPlus
  • Three ways to exit SQLPlus
  • Type exit at the SQL prompt
  • Click File on the menu bar, and then click Exit
  • Click the Close button on the program window
    title bar
  • Database session ends when SQLPlus exits

49
Create Table with Foreign Key Constraint
50
Viewing Information About Tables
  • describe tablename displays column names and
    data types
  • Data dictionary tables that contain information
    about the structure of the database.
  • USER shows the objects in the current users
    schema
  • ALL shows both objects in the current users
    schema and objects that the user has privileges
    to manipulate
  • DBA allows users who are database administrators
    to view information about all database objects

51
Viewing Tables in the Database
52
Viewing Constraints on One Table
53
Modifying and Deleting Database Tables
  • Modify existing database tables by
  • Changing the name of a table
  • Adding new columns
  • Deleting columns that are no longer needed
  • Changing the data type or maximum size of an
    existing column
  • Unrestricted action some specifications can
    always be modified
  • Restricted action specifications modified only
    in certain situations

54
Unrestricted Action
55
Restricted Actions
56
Deleting and Renaming Tables
  • To delete
  • Drop table tablename
  • Use with caution
  • To delete foreign key constraints, add cascade
    constraints
  • To rename
  • Rename old_tablename to new_tablename
  • DBMS automatically transfers to the new table
    integrity constraints, indexes, and privileges
    that referenced the old table.

57
Adding Fields to Existing Tables
  • To add a field
  • ALTER TABLE tablename ADD(fieldname
    data_declaration constraints)

58
Modifying Existing Field Data Definitions
  • Can only change datatype to compatible data type
    (i.e. varchar2 to char)
  • ALTER tablename MODIFY(fieldname
    new_data_declaration)

59
Deleting a Field
  • ALTER TABLE tablename DROP COLUMN fieldname
  • Can be used to rename a field first drop, then
    add

60
Adding and Deleting Constraints
  • Add a constraint ALTER TABLE tablename ADD
    CONSTRAINT constraint_name constraint_definition
  • Remove a constraint ALTER TABLE tablename DROP
    CONSTRAINT constraint_name

61
Enabling and Disabling Constraints
  • When modifying a database it can be useful to
    disable constraints
  • Constraints are enabled by default
  • To disable a constraint ALTER TABLE tablename
    DISABLE CONSTRAINT constraint_name
  • To enable a constraint ALTER TABLE tablename
    ENABLE CONSTRAINT constraint_name

62
Summary
  • SQL commands include
  • Data description language (DDL) commands create,
    modify, Deleted database objects
  • Data manipulation language (DML) commands
    insert, update, delete, view database data
  • To create a table
  • specify the table name, the name of each data
    field, and the data type and size of each data
    field
  • Data types ensure correct data values
  • Constraints restrict values of database fields
  • SQLPlus commands are not case sensitive

63
Summary
  • Errors include line number, position, error code
  • Use DESCRIBE command to display a tables
    fieldnames and data types
  • Tables can be modified or deleted but some
    changes are restricted
Write a Comment
User Comments (0)
About PowerShow.com