Transcript Introduction to Oracle 10g

Introduction to Oracle 10g
Chapter 1
Introduction to Relational
Database Systems and Oracle 10g
James Perry and Gerald Post
1-1
© 2007 by Prentice Hall
Chapter Outline
•
•
•
•
•
•
1-2
Database Management Systems
Describing Systems as Entities and Relationships
Understanding the Relational Database model
Installing Oracle tools
Exploring the Oracle Environment
Introduction to the Book’s Databases
© 2007 by Prentice Hall
1.1 Three-tier DBMS Approach
Database management systems overview
Database
administrator
Database
server
Application
server
Application user
Application
developer
1-3
© 2007 by Prentice Hall
Generic Data Types
Data Type Description
Examples
Text
Any type of characters, numbers, or most punctuation.
123 Main Street
Number
Numbers are stored so they can be aggregated or
manipulated arithmetically. You can usually specify the
number of decimal places, such as zero to get integers.
Also used by Oracle to hold monetary values.
1.2345
15.32
153
Date
Used to hold dates and time values. It is important to use 13-JUL-2006
this type instead of text when possible because the
system can compute the difference between dates.
Binary
Used to hold data for non-traditional objects such as
pictures, spreadsheet files, or other large items.
1-4
Picture.jpg
© 2007 by Prentice Hall
1.2 One-to-many relationship between Customer and CustomerOrder
Database design
Customer
CustomerID
LastName
FirstName
Phone
Address
City
1-5
CustomerOrder
1
*
OrderID
OrderDate
CustomerID
© 2007 by Prentice Hall
1.3 Sample tables in a relational database
CustomerOrders
OrderID
OrderDate
CustomerID
1201
06-JUN-2006
151
1202
06-JUN-2006
155
1203
07-JUN-2006
151
Customers
1-6
CustomerID
LastName
FirstName
Phone
Address
City
151
Jones
Mary
111-2222
123 Main
Eureka
152
Smith
Susan
222-5555
738 Elm
Eureka
153
Brown
David
111-2355
235 East
Eureka
154
Sanchez
Maria
999-3332
351 Ocean
Arcata
155
Steuben
Saul
555-2351
111 Main
Orick
156
Hayworth
Michele
231-3252
761 West
Loleta
© 2007 by Prentice Hall
1.4 One-to-many and many-to-many relationships
Student
StudentID
LastName
FirstName
Phone
1-7
Participants
1
*
OrganizationID
StudentID
Role
*
1
Organization
OrganizationID
Title
Advisor
Category
© 2007 by Prentice Hall
1.5 Sample data showing the need for two columns to be part of the key
Organization
Student
OrganizationID
Title
Advisor
Category
101
Lacrosse
Amerit
Sports
StudentID
LastName
FirstName
Phone
115
Phi-Beta
Smith
Fraternity
12013
Fellini
Federico
111-4444
125
Student
Council
Antonio
Governance
12315
Bergman
Ingmar
222-3331
12551
Truffaut
Francois
888-2221
23664
Kurosawa
Akiro
999-4491
Participant
1-8
OrganizationID
StudentID
Role
101
12013
Captain
101
12551
Member
115
12013
Member
115
23664
President
125
12551
Treasurer
125
12315
Member
© 2007 by Prentice Hall
1.6 Form data to be normalized
Database design starting point
Customer Order
OrderID
Date
CustomerID
First Name
Last Name
Phone
Address
City, State Zipcode
ItemID Price Description
Quantity Value
1526
32.95 Basketball
1
32.95
3921
79.92 Running shoes 1
79.92
4797
1-9
1.59 Racquet balls
3
4.77
© 2007 by Prentice Hall
1.7 Placing all data into one table is a bad design
OrderID
Order Date
CustID
FName
LName
Phone
Address
City
State
ZIP
ItemID
Price
Description
Qty
1201
06-JUN-06
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
1526
32.95
Basketball
1
1201
06-JUN-06
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
3921
79.92
Running Shoes
1
1201
06-JUN-06
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
4797
1.59
Racquet balls
3
1202
06-JUN-06
155
Saul
Steuben
555-2351
111 Main
Orick
CA
95022
1526
32.95
Basketball
1
1202
06-JUN-06
155
Saul
Steuben
555-2351
111 Main
Orick
CA
95022
3144
15.72
Baseball
1
1-10
© 2007 by Prentice Hall
1.8 Reducing repetition by using non-atomic columns is still bad
OrderID
OrderDate
CustID
FName
LName
Phone
Address
City
State
ZIP
ItemID
Price
Description
1201
06-JUN-06
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
1526
3921
4797
32.95
79.92
1.59
Basketball
Running Shoes
Racquet balls
1
1
3
1202
06-JUN-06
155
Saul
Steuben
555-2351
111 Main
Orick
CA
95022
1526
3144
32.95
15.72
Basketball
Baseball
1
1
1-11
© 2007 by Prentice Hall
Qty
1.9 Split out the repeating data section
First normal form
OrderID
OrderDate
CustID
FName
LName
Phone
Address
City
State
ZIP
1201
06-JUN-06
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
1202
06-JUN-06
155
Saul
Steuben
555-2351
111 Main
Orick
CA
95022
1-12
OrderID ItemID Price
Description
Quantity
1201
1526
32.95
Basketball
1
1201
3921
79.92
Running Shoes
1
1201
4797
1.59
Racquet balls
3
1202
1526
32.95
Basketball
1
1202
3144
15.72
Baseball
1
© 2007 by Prentice Hall
1.10 Split out the columns that depend only on the ItemID
Second normal form
order-customer
OrderID
OrderDate
CustID
FName
LName
Phone
Address
City
State
ZIP
1201
06-JUN-06
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
1202
06-JUN-06
155
Saul
Steuben
555-2351
111 Main
Orick
CA
95022
OrderItems
1-13
Items
OrderID ItemID Quantity
ItemID Price
Description
1201
1526
1
1526
32.95
Basketball
1201
3921
1
3144
15.72
Baseball
1201
4797
3
3921
79.92
Running Shoes
1202
1526
1
4797
1.59
Racquet balls
1202
3144
1
© 2007 by Prentice Hall
1.11 Split out the columns that depend only on the CustomerID
Third normal form
Orders
OrderID
OrderDate
CustomerID
1201
06-JUN-06
151
1202
06-JUN-06
155
CustomerID
FName
LName
Phone
Address
City
State
ZIP
151
Mary
Jones
111-2222
123 Main
Eureka
CA
95001
155
Saul
Steuben
555-2351
111 Main
Orick
CA
95022
OrderItems
1-14
Customers
Items
OrderID
ItemID
Quantity
1201
1526
1
1201
3921
1
1201
4797
3
1202
1526
1
1202
3144
1
ItemID
Price
Description
1526
32.95
Basketball
3144
15.72
Baseball
3921
79.92
Running Shoes
4797
1.59
Racquet balls
© 2007 by Prentice Hall
1.12 Bank account example of object class inheritance
Account
AccountID
CustomerID
DateOpened
DateClosed
OpeningBalance
Checking Account
MinimumBalance
InterestRate
MonthlyFee
Savings Account
InterestRate
QuarterlyFee
Student Checking
MaxChecksPerMonth
OverdraftLimit
1-15
© 2007 by Prentice Hall
1.13 Elements of the DBMS
Custom Application
Oracle DBMS
Forms and Reports
Services
Admin.
Tools
Developer
PL/SQL Query
Processor
Data Engine
Data Dictionary
Security
Communication Network
Developer
Suite
Application Users
Operating System
Database Administrator
1-16
Disk Drives
© 2007 by Prentice Hall
1.14 Oracle Basic Installation choices
Installing the Oracle DBMS
1-17
© 2007 by Prentice Hall
1.15 Installation success but be sure to copy the URLs
1-18
© 2007 by Prentice Hall
1.16 Oracle services to be stopped
Stopping Oracle with Windows services
1-19
© 2007 by Prentice Hall
1.17 Oracle products to be removed
Uninstalling Oracle
Main database
Forms and Reports Services
1-20
© 2007 by Prentice Hall
1.18 Oracle registry entries to be deleted.
Delete the main Oracle entry
1-21
© 2007 by Prentice Hall
1.19 Specify a unique home name and path
Installing Oracle Developer Suite
1-22
© 2007 by Prentice Hall
1.20 Oracle network configuration
1-23
© 2007 by Prentice Hall
1.21 Entering the global database name
1-24
© 2007 by Prentice Hall
1.22 Copy and save the Forms and Reports Services configuration.
1-25
© 2007 by Prentice Hall
1.23 Starting SQL*Plus.
1-26
© 2007 by Prentice Hall
1.24 Standard login screen.
1-27
© 2007 by Prentice Hall
1.25 Running the setup script
1-28
© 2007 by Prentice Hall
1.26 Running a simple query with iSQL*Plus
1-29
© 2007 by Prentice Hall
1.27 A simple form for the Clients table
1-30
© 2007 by Prentice Hall
1.28 Sample report for Clients
1-31
© 2007 by Prentice Hall
1.29 Administration tasks in the Enterprise Manager
1-32
© 2007 by Prentice Hall
1.30 Diagram of the Redwood Realty database
Introduction to the book’s databases
ContactReason
PK
ContactReason
Description
Customers
PK
CustomerID
FirstName
LastName
Address
City
State
Zipcode
HomePhone
CellPhone
WorkPhone
CustAgentList
PK,FK2
PK,FK1
PK,FK3
PK
CustomerID
AgentID
ListingID
ContactDate
FK4
ContactReason
BidPrice
CommissionRate
Properties
1-33
Listings
PK
PropertyID
PK
ListingID
FK2
OwnerID
Address
City
State
Zipcode
Bedrooms
Bathrooms
Stories
SqFt
YearBuilt
Zone
LotSize
Latitude
Longitude
FK3
FK5
FK6
PropertyID
ListingAgentID
SaleStatusID
BeginListDate
EndListDate
AskingPrice
Agents
PK
AgentID
FK2
FirstName
LastName
HireDate
BirthDate
Gender
WorkPhone
CellPhone
HomePhone
Title
TaxID
LicenseID
LicenseDate
LicenseExpire
LicenseStatusID
LicenseStatus
PK
LicenseStatusID
StatusText
SaleStatus
PK
SaleStatusID
SaleStatus
© 2007 by Prentice Hall
1.31 Contact form for agents and customers
1-34
© 2007 by Prentice Hall
1.32 Simple sales listing report
1-35
© 2007 by Prentice Hall
1.33 Basic order form for the Coffee Merchant
1-36
© 2007 by Prentice Hall
1.34 Quarterly sales organized by inventory type and state
1-37
© 2007 by Prentice Hall
1.35 Race results form for Rowing Ventures
1-38
© 2007 by Prentice Hall
1.36 Race results with crew names
1-39
© 2007 by Prentice Hall
1.37 Order form for Broadcloth Clothing
1-40
© 2007 by Prentice Hall
1.38 Shipping from factory to customer
1-41
© 2007 by Prentice Hall
1.39 Factory production report
1-42
© 2007 by Prentice Hall