SQL
- Chapter 1
Introduction to Premiere Products and Henry Books
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 course
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
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
Required Data for Sales
Reps
Number
Last name
First name
Address
Total commission
Commission rate of each sales rep
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
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
Components of Sample Order
Heading (top of the order)
contains:
l Order number and date
l Customer’s number, name, and address
l Sales rep number and name
Body of the order contains a
number of order lines called line items
Each order line contains:
l Part number
l Part description
l Number of units ordered
l Quoted price
l Total, called ‘extension’
Footing (bottom of the order)
contains the order total
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
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
Sample Data for Premiere
Products
Sample
Data for Premiere Products
Sample
Data for Premiere Products
Sales
Rep Information
Three sales representatives
identified by numbers
Example:
l Sales Rep Number: 20
l Name: Valerie Kaiser
l
Address: 624 Randall,
l Total commission: $20,542.50
l Commission rate: 5% (.05)
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:
l Customer Number: 148
l Name: Al’s Appliance and Sport
l
Address: 2837 Greenway in
l Current Balance: $6,550.00
l Credit Limit: $7,500.00
l Sales Rep: 20
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)
Part
Data Example
Example:
l Part Number: AT94
l Part Description: Iron
l Units on Hand: 50
l Item Class: HW (housewares)
l Warehouse Number: 3
l Unit Price: $24.95
Order
Information
Seven orders identified with the
numbers 21608, 21610, 21613, 21614, 21617, 21619, and 21623
Example:
l Order number: 21608
l Order date: October 20, 2003
l Customer number: 148 (Al’s Appliance and Sport)
Alternative
ORDERS Table Structure
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
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?
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
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:
l Ensure that the data is current and accurate
l Produce several important reports
l Ask questions concerning the data and obtain answers to these questions easily and quickly
Information
Gathered by Henry Books
Henry gathers and organizes
information about:
l Branches
l Publishers
l Authors
l Books
Types of
Information
Each branch has a number that
uniquely identifies the branch
In addition, Ray tracks the
branch’s:
l Name
l Location
l Number of employees
Types of
Information
Each publisher has a code that
uniquely identifies the publisher
In addition, Ray tracks the
publisher’s:
l Name
l City
Types of
Information
Each author has a number that
uniquely identifies the author
In addition, Ray records each
author’s:
l Last name
l First name
Types of
Information
Each book has a code that uniquely
identifies the book
For each book, Ray also tracks:
l Title
l Publisher
l Type of book
l Price
l Whether the book is a paperback or not
Summary
Premiere Products is an
organization whose information requirements include the following:
l Sales representatives
l Customers
l Orders
l Parts
l Order lines
The database for Henry Books
contains the following information:
l Branches
l Publishers
l Books
l Authors
l Inventory
l Author Sequence
SQL
Project One Completed
Good Luck
H. Zamanzadeh