Скачать презентацию Client Server Databases and the Oracle 9 i Relational Скачать презентацию Client Server Databases and the Oracle 9 i Relational

614fdab62b12cb85e6dce49b84419cca.ppt

  • Количество слайдов: 32

Client/Server Databases and the Oracle 9 i Relational Database Chapter 1 A Guide to Client/Server Databases and the Oracle 9 i Relational Database Chapter 1 A Guide to Oracle 9 i 1

Objectives • Develop an understanding of the purpose of database systems • Become familiar Objectives • Develop an understanding of the purpose of database systems • Become familiar with the structure of a relational database and review relational database concepts and terms • Explore the differences between personal databases and client/server databases • Learn about the Clearwater Traders sales order database, the Northwoods University student registration database, and the Software Experts project management database A Guide to Oracle 9 i 2

Before Databases • Information was kept in files: – – Each field describes one Before Databases • Information was kept in files: – – Each field describes one piece of information about student Fields are separated by commas A record is a collection of related fields Each record is a separate line A Guide to Oracle 9 i 3

Problems with Files • Proliferation of data management programs to deal with different file Problems with Files • Proliferation of data management programs to deal with different file formats • Redundant data stored in files • Data files may contain inconsistent data A Guide to Oracle 9 i 4

Database Approach • Database stores all organizational data in a central location • Good Database Approach • Database stores all organizational data in a central location • Good database design eliminates redundant data to reduce the possibility of inconsistent data • Single application called the database management system (DBMS) performs all routine data handling operations • Database administrator (DBA): person responsible for installing, administering, and maintaining the database A Guide to Oracle 9 i 5

Early Databases – Hierarchical Structure A Guide to Oracle 9 i 6 Early Databases – Hierarchical Structure A Guide to Oracle 9 i 6

Relational Databases A Guide to Oracle 9 i 7 Relational Databases A Guide to Oracle 9 i 7

Relational Database Terms • Entity: an object about which you want to store data Relational Database Terms • Entity: an object about which you want to store data • Relationships: links that show 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 A Guide to Oracle 9 i 8

Primary Keys • Primary key – – – Value must be unique for each Primary Keys • Primary key – – – Value must be unique for each record Serves to identify the record Present in every record Can’t be NULL Should be numeric A Guide to Oracle 9 i 9

Candidate Keys • Candidate key – Any field that could be used as the Candidate Keys • Candidate key – Any field that could be used as the primary key – Should be a unique, unchanging numeric field A Guide to Oracle 9 i 10

Surrogate Keys • Surrogate key: created to be the record’s primary key identifier when Surrogate Keys • Surrogate key: created to be the record’s 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 A Guide to Oracle 9 i 11

Foreign Keys • • • Foreign key: a field in a table that is 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 A Guide to Oracle 9 i 12

Composite Keys • Composite key: a unique key that you create by combining two 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 A Guide to Oracle 9 i 13

Personal Database Management Systems • DBMS and database applications run on the same workstation 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 user’s 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 nonmission-critical applications A Guide to Oracle 9 i 14

Client/Server Database Management Systems • Client/server database – Takes advantage of distributed processing and 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 A Guide to Oracle 9 i 15

Client/Server Database Architecture A Guide to Oracle 9 i 16 Client/Server Database Architecture A Guide to Oracle 9 i 16

The Oracle 9 i Client/Server Database • Oracle 9 i is the latest release The Oracle 9 i Client/Server Database • Oracle 9 i is the latest release of Oracle Corporation’s 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 A Guide to Oracle 9 i 17

Client-Side Utilities • • • SQL*Plus – for creating and testing command-line SQL queries Client-Side Utilities • • • SQL*Plus – for creating and testing command-line SQL queries and executing PL/SQL procedural programs Oracle 9 i 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 A Guide to Oracle 9 i 18

Design Principles • To avoid creating tables that contain redundant data, group related items 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 A Guide to Oracle 9 i 19

The Clearwater Traders Sales Order Database Clearwater Traders • Markets a line of clothing 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 A Guide to Oracle 9 i 20

Clearwater Traders Data Requirements • Customer name, address, daytime and evening telephone numbers, user 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 (women’s 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 A Guide to Oracle 9 i 21

Clearwater Traders Table Relationships A Guide to Oracle 9 i 22 Clearwater Traders Table Relationships A Guide to Oracle 9 i 22

The Northwoods University Student Registration Database Northwoods University • Decided to replace its aging 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 A Guide to Oracle 9 i 23

The Northwoods University Student Registration Database (cont) • Faculty members must be able to 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 A Guide to Oracle 9 i 24

Northwoods University Data Requirements • Student name, address, telephone number, class (freshman, sophomore, junior, 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 A Guide to Oracle 9 i 25

Northwoods University Table Relationships A Guide to Oracle 9 i 26 Northwoods University Table Relationships A Guide to Oracle 9 i 26

The Software Experts Project Management Database Software Experts • Consulting firm that creates custom 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 A Guide to Oracle 9 i 27

Software Experts Requirements • Consultant information, including name, address, city, state, ZIP code, phone 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 A Guide to Oracle 9 i 28

Software Experts Requirements • Project information, including the project name, the client associated with 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 A Guide to Oracle 9 i 29

Software Experts Table Relationships A Guide to Oracle 9 i 30 Software Experts Table Relationships A Guide to Oracle 9 i 30

Summary • A database stores all organizational data in a central location • Database Summary • A database stores all organizational data in a central location • Database management system (DBMS) provides a central set of common functions for managing a database, including – Inserting – Updating – Retrieving – Deleting data values • Most modern databases are relational databases – Store data in a tabular format – Columns represent different data fields – Rows contain individual data records A Guide to Oracle 9 i 31

Summary • In a relational database – Data about different entities is stored in Summary • In a relational database – Data about different entities is stored in separate tables – You create relationships that link related data using key fields • A database system consists of – DBMS: manages the physical data storage – Database applications: provide the user interface to the database • Many database systems use a client/server architecture – DBMS runs as a server process – Database applications run as client processes A Guide to Oracle 9 i 32