Transcript Introduction to Database Management

Chapter 1
Introduction to Premiere Products and
Henry Books
A Guide to SQL, Sixth Edition
1
Objectives
Introduce Premiere Products, a company whose
database is used as the basis for many of the
examples throughout the text
Introduce Henry Books, a company whose
database is used in the case that runs throughout
the text
2
The Premiere Products
Database
Premiere Products is a distributor of
appliances, housewares, and sporting
goods
It is no longer feasible to maintain
customer, order, and inventory data
using its current manual system
3
Benefits of a Computer
Database System
Ensure that the data is current and
more accurate than in the present
manual system
Produce a variety of useful reports
Ask questions concerning the database
and obtain answers easily and quickly
4
Required Data for Sales Reps
Number
Last name
First name
Address
Total commission
Commission rate of each sales rep
5
Required Data for Customers
Customer number
Last name
First name
Address
Current balance
Credit limit
The number of the sales rep who represents
the customer
6
Required Data for Parts
Part number
Part description
Number of units on hand
Item class
Number of the warehouse where the
item is stored
Unit price for each part in the inventory
7
Sample Order
8
Components of Sample Order
Heading (top of the order) contains:
Order number and date
 Customer’s number, name, and address
 Sales rep number and name

Body of the order contains a number of
order lines called line items
9
Components of Sample Order
Each order line contains:
Part number
 Part description
 Number of units ordered
 Quoted price
 Total, called ‘extension’

Footing (bottom of the order) contains
the order total
10
Item Information Stored for
Each Order
Order number, date the order was
placed, and the customer number of the
customer who placed the order
Order number, part number, number of
units ordered, and quoted price for each
order line
11
Overall Order Total
Overall order total is not stored as part
of the database
Total order will be computed whenever
an order is printed or displayed on the
screen
12
Sample Data for Premiere Products
13
Sample Data for Premiere Products
14
Sample Data for Premiere Products
15
Sample Data for Premiere Products
16
Sample Data for Premiere Products
17
Sales Rep Information
Three sales representatives identified
by numbers
Example:
Sales Rep Number: 20
 Name: Valerie Kaiser
 Address: 624 Randall, Grove, FL 33321
 Total commission: $20,542.50
 Commission rate: 5% (.05)

18
Customer Information
Premiere Products has 10 customers who are
identified with the numbers 148, 282, 356,
408, 462, 524, 608, 687, 725, and 842
Example:






Customer Number: 148
Name: Al’s Appliance and Sport
Address: 2837 Greenway in Fillmore, FL 33336
Current Balance: $6,550.00
Credit Limit: $7,500.00
Sales Rep: 20
19
Part Information
There are 10 parts identified by part
numbers AT94, BV06, CD52, DL71,
DR93, DW11, FD21, KL62, KT03, and
KV29
Item class codes are HW
(housewares), AP (appliances), and
SG (sporting goods)
20
Part Data Example
Example:
 Part Number: AT94
 Part Description: Iron
 Units on Hand: 50
 Item Class: HW (housewares)
 Warehouse Number: 3
 Unit Price: $24.95
21
Order Information
Seven orders identified with the
numbers 21608, 21610, 21613, 21614,
21617, 21619, and 21623
Example:
Order number: 21608
 Order date: October 20, 2003
 Customer number: 148 (Al’s Appliance and
Sport)

22
Alternative ORDERS Table Structure
23
Problem with Alternative
ORDERS Table
Some of the individual positions within
the table contain multiple entries
Multiple entries make it difficult to track
the information between columns
24
Practical Issues Raised by a
Complex Table
How much room do you allow for these
multiple entries?
What if an order has more order lines
than you have allowed for?
Given a part, how do you determine
which orders contain order lines for that
part?
25
ORDER_LINE Table
Advantages
No multiple entries
Does not matter how many order lines
exist for any order
Finding all the orders that contain order
lines for a given part is easy
26
Henry Books Database
Ray Henry is the owner of a bookstore chain
named Henry Books
Ray has decided to store his data in a
database because he wants to:



Ensure that the data is current and accurate
Produce several important reports
Ask questions concerning the data and obtain
answers to these questions easily and quickly
27
Information Gathered by
Henry Books
Henry gathers and organizes
information about:
Branches
 Publishers
 Authors
 Books

28
Types of Information
Each branch has a number that
uniquely identifies the branch
In addition, Ray tracks the branch’s:
Name
 Location
 Number of employees

29
Types of Information
Each publisher has a code that uniquely
identifies the publisher
In addition, Ray tracks the publisher’s:
Name
 City

30
Types of Information
Each author has a number that uniquely
identifies the author
In addition, Ray records each author’s:
Last name
 First name

31
Types of Information
Each book has a code that uniquely
identifies the book
For each book, Ray also tracks:
Title
 Publisher
 Type of book
 Price
 Whether the book is a paperback or not

32
Summary
Premiere Products is an organization
whose information requirements include
the following:
Sales representatives
 Customers
 Orders
 Parts
 Order lines

33
Summary
The database for Henry Books contains
the following information:
Branches
 Publishers
 Books
 Authors
 Inventory
 Author Sequence

34
SQL Project One Completed
Good Luck
H. Zamanzadeh
35