Transcript All Powder Board and Ski

All Powder Board and Ski
Oracle 9i Workbook
Chapter 2: Database Design
Jerry Post
Copyright © 2003
1
DBDesign: An Expert System

http://time-post.com/dbdesign
 Benefits




Makes it easy to create database diagrams
Saves data in central location, so changes can be
made from almost any computer
Provides immediate detailed feedback on the
design
Requirements


Instructors must ask for a free account
Instructors and students need a Java-enabled Web
browser
2
Access Data Types (Domains)
Name
Text (characters)
fixed
variable
national/Unicode
memo
Data
Bytes
CHAR or NCHAR
VARCHAR2
NVARCHAR2
LONG
2000 bytes
4000 bytes
4000 bytes
2 gigabytes
Fixed
Variable
Variable
Variable
Numeric
Byte (8 bits)
Integer (16 bits)
Long (32 bits)
(64 bits)
Fixed precision
Float
Double
Currency
Yes/No
NUMBER(38)
NUMBER(38)
NUMBER(38)
NUMBER(38)
NUMBER(p,s)
NUMBER
NUMBER
NUMBER(p,4)
NA
38 digits
38 digits
38 digits
38 digits
p: 1...38, s: -84...127
38 digits
38 digits
38 digits
22
22
22
22
22
22
22
22
Date/Time
Interval
DATE, TIMESTAMP
INTERVAL YEAR/MONTH
1/1/-4712 to 12/31/9999 (sec)
Image
LONG RAW or BLOB
2 gigabytes, 4 gigabytes
Variable
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements2a.htm#54201
3
Initial Business Objects
Sale
SaleID
SaleDate
CustomerID
EmployeeID
Employee
EmployeeID
TaxpayerID
LastName
FirstName
Address
City
State
ZIP
Customer
CustomerID
LastName
FirstName
Phone
Address
City
State
ZIP
Rental
RentID
RentDate
CustomerID
ExpectedReturn
4
Associations or Relationships
Sale
SaleID
SaleDate
CustomerID
EmployeeID
1…1
0…*
Employee
EmployeeID
TaxpayerID
LastName
FirstName
Address
City
State
ZIP
Customer
CustomerID
LastName
FirstName
Phone
Address
City
State
ZIP
Rental
RentID
RentDate
CustomerID
ExpectedReturn
5
Getting Started
Enter the key
numbers you
received
Create a
username and
password.
Enter your
correct name,
e-mail address
and StudentID
6
Class Registration
Successful
account creation
Select your
university and class
Enter the admit
code
7
DBDesign: Example
Menu
Class
(entity)
Status line
Available
columns
Corrections
8
Relationships
Drag-anddrop column
Select min and
max for both
sides of the
relationship
9
Design Errors
Add SKU to
the Sale table
Connect the
Inventory table to
the Sale table
Double click the
diagnostic
message
Possible errors
are highlighted
10
More Errors
Try setting SKU
as a key
It still causes problems
because SaleDate does not
depend on the SKU
11
Split Many-to-Many Relationship
Many-to-Many
Inventory
SKU
Size
QOH
Sale
1…1
1…1
SaleItem
0…*
SaleID
SaleDate
CustomerID
EmployeeID
1…*
SaleID
SKU
QuantitySold
SalePrice
12
Ski Shop Inventory
Item: 196 cm
Item: 181 cm
Ski shops carry multiple lengths
of each ski or board model.
Model information refers to the
overall type of board or ski.
Inventory information refers to
an individual ski or board—
defined by its length.
Model:
Rossignol Axium
Photo: www.rossignol.com
13
Models and Items
14
Customer Skill Level
CustomerID, LastName, … Style, SkillLevel
CustomerID, LastName, … Style, SkillLevel
Business rule: Each customer can have one skill in many styles.
Business rule: Each style can apply to more than one customer.
Need a table with both attributes as keys.
CustomerID, LastName, … Style, SkillLevel
But you cannot include LastName, FirstName and so on, because
then you would have to re-enter that data for each customer skill.
15
Customer Style Skills
Customer
Style
CustomerID
LastName
FirstName
Phone
Address
City
State
ZIP
Style
StyleDescription
CustomerSkill
CustomerID
Style
SkillLevel
SkillLevel
SkillLevel
SkillDescription
16