Transcript Select - McGraw Hill Higher Education

ENTERPRISE INFORMATION SYSTEMS
A PATTERN BASED APPROACH
Chapter 7
Information Retrieval from Relational
Databases
McGraw-Hill/Irwin
Copyright © 2005 by The McGraw-Hill Companies, Inc. All rights reserved.
Chapter Learning Objectives
1.
2.
3.
4.
5.
6.
7.
8.
9.
Identify and explain the purpose of the three primary relational
algebra operators
Identify and explain the primary components of a Structured
Query Language (SQL) statement
Identify the relational algebra operations achieved by a given
SQL statement
Create a SQL statement to retrieve requested information
from a relational database
Examine a SQL statement and the tables to which it will be
applied and identify the query result
Find errors in a SQL statement
Create a Microsoft Query-by-Example (QBE) to retrieve
information from relational tables
Examine a Microsoft Access QBE query and the tables to
which it applies and identify the query result
Find errors in a Microsoft Access QBE query
7-2
Examples of Needs for Multiple
Views of One Data Set
• Cash-basis versus Accrual Accounting
• Weighted Average versus FIFO or LIFO
• Double-Declining Balance Depreciation versus
Straight Line
• Foreign Currency Translation
How do we get these multiple
views???????
7-3
Answer: Query the Data Set
• What is Querying?
– It is asking questions about the data in the
database and manipulating or combining the
data in different ways
– We can isolate certain rows in tables, we can
isolate certain columns in tables, we can join
tables together, we can create calculations
based on various data items, etc.
7-4
Querying/Information Retrieval
Several ingredients are necessary for effective querying
1. A database that is well-designed
•
If tables are not fully relational or incompletely
specified, or if conceptual model has not been correctly
converted into relational form, querying will be difficult or
impossible
2. A query developer who understands the table
structures and the nature of the data in the tables
3. A query developer who understands the desired query
output
4. A query developer who has good logic and
reasoning skills
5. A query developer who knows the querying
language used to retrieve information from the
enterprise database
7-5
Three Query Languages
• Relational Algebra
– Three main operators: Select, Project, Join
– Provides the conceptual basis for SQL and QBE
• Structured Query Language (SQL)
– The user enters commands according to a pre-defined
syntax to retrieve desired data.
• Query By Example (QBE)
– The user starts with a sample of the table(s) columns
and marks the fields he or she wants to include in the
answer. Defaults are available for summarizing and
manipulating the data.
7-6
Relational Algebra
• Select
– includes only certain rows from a database
table in its “answer”.
• Project
– includes only certain columns from a
database table in its “answer”
• Join
– combines two or more database tables on the
basis of one or more common attributes
7-7
Example Tables (Incomplete Enterprise Database)
from Dunn & McCarthy (2004) working paper
Sale
Sale#
S-1
S-2
S-3
S-4
S-5
S-6
S-7
Amount Date
7,200 1 July
10,000 21 July
16,000 22 July
10,000 26 July
16,600 31 July
35,000 15 Aug
23,000 21 Aug
Cust# SalesRep#
C-1
E-12 Salesperson
C-2
E-10 Employee Quarterly Comm
Sales $
rate
C-5
E-10 Number
.12
C-2
E-10 E-12
.10
C-5
E-10 E-10
.10
C-3
E-10 E-99
0
.15
C-4
E-99 E-78
Sale-CashRecDuality
Sale#
RA#
Applied
S-2
RA-1
1,666
S-4
RA-2
10,000
S-1
RA-3
7,200
S-3
RA-4
16,000
S-5
RA-4
16,600
S-2
RA-5
1,666
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Cash
Account#
BA-6
BA-7
BA-8
BA-9
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
Type
Checking
Checking
Draft
Checking
Bank
Boston5
Shawmut
Shawmut
MassNat
Balance
75,000
0
Cash Receipt
Remittance
Advice#
Amount
RA-1
1,666
RA-2
10,000
RA-3
7,200
RA-4
32,600
RA-5
1,666
Inventory-Sale Stockflow
Inventory Sale
Actual
ItemID
Number Quantity Price
A-4
S-1
2
600
A-1
S-1
3 2,000
A-6
S-2
2 5,000
A-1
S-3
1 2,000
A-5
S-3
2 4,000
A-3
S-3
6 1,000
A-6
S-4
2 5,000
A-2
S-5
2 3,000
A-4
S-5
2
300
A-6
S-5
2 5,000
A-2
S-6
10 3,500
A-6
S-7
2 7,000
A-5
S-7
3 3,000
Bank
Account#
BA-6
BA-7
BA-7
BA-7
BA-6
Date
25 July
26 July
15 Aug
15 Aug
25 Aug
Customer
Number
C-2
C-2
C-1
C-5
C-2
Cashier
Number
E-39
E-39
E-39
E-39
E-39
7-8
Relational Algebra SELECT
Find the cash receipts from Customer #2 (keeping all
the details of those cash receipts)
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
Cash Receipt
Remittance
Advice#
Amount
RA-1
1,666
RA-2
10,000
RA-3
7,200
RA-4
32,600
RA-5
1,666
Bank
Account#
BA-6
BA-7
BA-7
BA-7
BA-6
Date
25 July
26 July
15 Aug
15 Aug
25 Aug
Customer
Number
C-2
C-2
C-1
C-5
C-2
Cashier
Number
E-39
E-39
E-39
E-39
E-39
Select Cash Receipt Where Customer Number = C-2
Giving Answer Answer
Remittance
Advice#
RA-1
RA-2
RA-5
Bank
Amount Account#
1,666
BA-6
10,000 BA-7
1,666
BA-6
Customer
Date
Number
25 July C-2
26 July C-2
25 Aug C-2
Cashier
Number
E-39
E-39
E-39
7-9
Relational Algebra PROJECT
Find the customer number, name, and salesperson
number for all customers
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
Salesperson
Employee Quarterly Comm
Number
Sales $
rate
E-12
.12
E-10
.10
E-99
.10
E-78
0
.15
SP#
E-12
E-10
E-10
E-99
E-10
Project Customer Over (Customer#, Name, SP#)
Answer
Giving Answer
Customer# Name
SP#
C-1
C-2
C-3
C-4
C-5
Bill
Mick
Keith
Charlie
Ron
E-12
E-10
E-10
E-99
E-10
7-10
Join Types
• Inner join
– includes only the records from both tables that
have the exact same values in the fields that
are joined
– I.e.,
• Outer join
– includes all records from one table, and
matches those records from the other table for
which values in the joined fields are equal
– I.e.,
Left Outer Join
Right Outer Join
7-11
Relational Algebra Inner Join
Find all details of all customers and all available details
of each customer’s salesperson
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
Salesperson
Employee Quarterly Comm
Number
Sales $
rate
E-12
.12
E-10
.10
E-99
.10
E-78
0
.15
Join Customer, Salesperson Where Customer.SP# =
[Salesperson.Employee Number] Giving Answer
Answer
Customer# Name
C-1
C-2
C-3
C-4
C-5
Bill
Mick
Keith
Charlie
Ron
A/R Amt SP# Employee Quarterly Comm
Number
Sales$
rate
E-12
E-12
.12
E-10
E-10
.10
E-10
E-10
.10
E-99
E-99
.10
E-10
E-10
.10
7-12
Relational Algebra Left Outer Join
Find all details of all sales and the cash receipt number and
amount applied of any cash receipts related to those sales
Sale
Sale#
S-1
S-2
S-3
S-4
S-5
S-6
S-7
Amount Date
7,200 1 July
10,000 21 July
16,000 22 July
10,000 26 July
16,600 31 July
35,000 15 Aug
23,000 21 Aug
Cust# SalesRep#
C-1
E-12
C-2
E-10
C-5
E-10
C-2
E-10
C-5
E-10
C-3
E-10
C-4
E-99
Sale-CashRecDuality
Sale#
RA#
Applied
S-2
RA-1
1,666
S-4
RA-2
10,000
S-1
RA-3
7,200
S-3
RA-4
16,000
S-5
RA-4
16,600
S-2
RA-5
1,666
Left Outer Join Sale, [Sale - CashRecDuality] Where
[Sale.Sale#] = [Sale - CashRecDuality.Sale#] Giving Answer
Answer
Sale#
Amount Date
Cust# SalesRep#
S-1
7,200 1 July
C-1
E-12
S-2
10,000 21 July
C-2
E-10
S-2
10,000 21 July
C-2
E-10
S-3
16,000 22 July
C-5
E-10
S-4
10,000 26 July
C-2
E-10
S-5
16,600 31 July
C-5
E-10
S-6
35,000 15 Aug
C-3
E-10
S-7
23,000 21 Aug
C-4
E-99
Sale#
S-1
S-2
S-2
S-3
S-4
S-5
RA#
RA-3
RA-1
RA-5
RA-4
RA-2
RA-4
Applied
7,200
1,666
1,666
16,000
10,000
16,600
7-13
SQL (Structured Query Language)
• Each query statement follows the same structure:
SELECT attribute name(s)
FROM table name(s)
WHERE criteria is met;
7-14
SQL Statements and Relational Algebra
• SQL’s SELECT component isolates columns
– i.e., relational algebra’s project
• SQL’s FROM component is used for identifying
the table(s) involved
– if >1 table, helps accomplish relational algebra’s join
(together with WHERE component that specifies equal
fields)
• SQL’s WHERE component isolates rows
– i.e., relational algebra’s select
– also helps accomplish relational algebra’s join
– may be left blank for single-table queries that retrieve all
rows
7-15
SQL and Relational Algebra SELECT
Find the cash receipts from Customer #2 (keeping all
the details of those cash receipts)
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
Cash Receipt
Remittance
Advice#
Amount
RA-1
1,666
RA-2
10,000
RA-3
7,200
RA-4
32,600
RA-5
1,666
Select *
From [Cash Receipt]
Where [Customer Number] = C-2;
Query Result
Remittance
Advice#
Amount
RA-1
1,666
RA-2
10,000
RA-5
1,666
Bank
Account#
BA-6
BA-7
BA-6
Bank
Account#
BA-6
BA-7
BA-7
BA-7
BA-6
Date
25 July
26 July
15 Aug
15 Aug
25 Aug
Customer
Number
C-2
C-2
C-1
C-5
C-2
Cashier
Number
E-39
E-39
E-39
E-39
E-39
(note: the brackets are needed
because of spaces in the table and
field names; also note * is a wild
card indicating all columns should
be included)
Customer
Date
Number
25 July C-2
26 July C-2
25 Aug C-2
Cashier
Number
E-39
E-39
E-39
7-16
SQL and Relational Algebra PROJECT
Find the customer number, name, and salesperson
number for all customers
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
Salesperson
Employee Quarterly Comm
Number
Sales $
rate
E-12
.12
E-10
.10
E-99
.10
E-78
0
.15
Select Customer#, Name, SP#
From Customer;
Query Result
Customer#
C-1
C-2
C-3
C-4
C-5
Name
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
7-17
SQL and Relational Algebra Inner Join
Find all details of all customers and all available details
of each customer’s salesperson
Customer
Customer#
C-1
C-2
C-3
C-4
C-5
Name A/R Amt
Bill
Mick
Keith
Charlie
Ron
SP#
E-12
E-10
E-10
E-99
E-10
Salesperson
Employee Quarterly Comm
Number
Sales $
rate
E-12
.12
E-10
.10
E-99
.10
E-78
0
.15
Select *
From Customer, Salesperson
Where Customer.SP# = [Salesperson.Employee Number];
Query Result
Customer# Name
C-1
C-2
C-3
C-4
C-5
A/R Amt SP# Employee Quarterly Comm
Number Sales$ rate
Bill
E-12
E-12
.12
Mick
E-10
E-10
.10
Keith
E-10
E-10
.10
Charlie
E-99
E-99
.10
Ron
E-10
E-10
.10
7-18
SQL and Relational Algebra Outer Join
Find all details of all sales and the cash receipt number and
amount applied of any cash receipts related to those sales
Sale
Sale#
S-1
S-2
S-3
S-4
S-5
S-6
S-7
Amount Date
7,200 1 July
10,000 21 July
16,000 22 July
10,000 26 July
16,600 31 July
35,000 15 Aug
23,000 21 Aug
Cust# SalesRep#
C-1
E-12
C-2
E-10
C-5
E-10
C-2
E-10
C-5
E-10
C-3
E-10
C-4
E-99
Sale-CashRecDuality
Sale#
RA#
Applied
S-2
RA-1
1,666
S-4
RA-2
10,000
S-1
RA-3
7,200
S-3
RA-4
16,000
S-5
RA-4
16,600
S-2
RA-5
1,666
Select *
From Sale LeftJoin [Sale-CashRecDuality]
Where [Sale.Sale#]=[Sale-CashRecDuality.Sale#];
Query Result
Sale#
Amount Date
Cust# SalesRep#
S-1
7,200 1 July
C-1
E-12
S-2
10,000 21 July
C-2
E-10
S-2
10,000 21 July
C-2
E-10
S-3
16,000 22 July
C-5
E-10
S-4
10,000 26 July
C-2
E-10
S-5
16,600 31 July
C-5
E-10
S-6
35,000 15 Aug
C-3
E-10
S-7
23,000 21 Aug
C-4
E-99
Sale#
S-1
S-2
S-2
S-3
S-4
S-5
RA#
RA-3
RA-1
RA-5
RA-4
RA-2
RA-4
Applied
7,200
1,666
1,666
16,000
10,000
16,600
7-19
Mathematical Comparison Operators
• SQL Queries may include mathematical comparison
operators such as
–
–
–
–
–
–
= equal to
< less than
<= less than or equal to
> greater than
>= greater than or equal to
<> not equal to (or != in some software)
• Mathematical comparison operators are typically included in
the WHERE clause of the SQL statement, and may be used
on all types of fields
– For date fields, dates that are earlier in time are “less than”
dates that are later in time.
– For text fields, A < B < C, etc.
7-20
SQL Mathematical
Comparison Operators
Cash
Account#
BA-6
BA-7
BA-8
BA-9
Type
Checking
Checking
Draft
Checking
Bank
Boston5
Shawmut
Shawmut
MassNat
Balance
75,000
0
Query Result
Account# Type
BA-8
Draft
Select Account#, Balance
From Cash
Where Balance>=50000;
Bank
Shawmut
Balance
75,000
7-21
SQL Mathematical Comparison
Operators on Character Attributes
Sale
Sale#
S-1
S-2
S-3
S-4
S-5
S-6
S-7
Amount Date
7,200 1 July
10,000 21 July
16,000 22 July
10,000 26 July
16,600 31 July
35,000 15 Aug
23,000 21 Aug
Cust# SalesRep#
C-1
E-12
C-2
E-10
C-5
E-10
C-2
E-10
C-5
E-10
C-3
E-10
C-4
E-99
Select Sale#, Amount
From Sale
Where SalesRep# <> E-10;
Query Result
Sale# Amount
S-1
7,200
S-7
23,000
7-22
Queries with Logical Operators
• Queries may include logical operators
AND, OR, and NOT
– AND accomplishes a set intersection – answer
includes all instances that meet BOTH
conditions
– OR accomplishes a set union – answer
includes all instances that meet one condition
and all instances that meet the other condition
– NOT identifies instances that do not meet one
or more conditions
7-23
Queries with Special Operators
• BETWEEN is used to define the range limits.
– The end points of the range are included
Sale
Sale#
S-1
S-2
S-3
S-4
S-5
S-6
S-7
Amount Date
7,200 1 July
10,000 21 July
16,000 22 July
10,000 26 July
16,600 31 July
35,000 15 Aug
23,000 21 Aug
Cust# SalesRep#
C-1
E-12
C-2
E-10
C-5
E-10
C-2
E-10
C-5
E-10
C-3
E-10
C-4
E-99
Select Sale#, Amount, Date
From Sale
Where Date BETWEEN 7/1 and 7/31;
Query Result
Sale# Amount Date
S-1
7,200 1 July
S-2
10,000 21 July
S-3
16,000 22 July
S-4
10,000 26 July
S-5
16,600 31 July
7-24
Queries with Special Operators
• IS NULL is used to retrieve attributes for
which the value is null.
Cash
Account#
BA-6
BA-7
BA-8
BA-9
Type
Checking
Checking
Draft
Checking
Select *
From Cash
Where Balance IS NULL;
Bank
Boston5
Shawmut
Shawmut
MassNat
Balance
75,000
0
Query Result
Account# Type
Bank
BA-6
Checking Boston5
BA-7
Checking Shawmut
Balance
7-25
Queries with Special Operators
• EXISTS is used to retrieve attributes for
which the value is not null.
Cash
Account#
BA-6
BA-7
BA-8
BA-9
Type
Checking
Checking
Draft
Checking
Bank
Boston5
Shawmut
Shawmut
MassNat
Select *
From Cash
Where Balance EXISTS;
Balance
75,000
0
Query Result
Account# Type
Bank
BA-8
Draft
Shawmut
BA-9
Checking MassNat
Balance
75,000
0
7-26
Aggregation Functions in Queries
• An aggregation function summarizes the data
values within a field (column)
– COUNT summarizes the number of rows that contain
a given value in the field
– AVERAGE computes the arithmetic mean value of all
rows included in the answer
– SUM computes the arithmetic sum of all rows included
in the answer
– MIN identifies the minimum (lowest) attribute value for
the field
– MAX identifies the maximum (greatest) attribute value
for the field
7-27
Queries with Horizontal Calculations
• “Horizontal” calculations mathematically
combine values from different fields for
each row
– Horizontal calculations should NOT be
included in the same query as an aggregation
function
• One query may perform a horizontal calculation and
another query that builds on the first query may
perform the aggregation function, or vice versa
– The “correct” order for the queries depends on
the goal
7-28
Relational Algebra SELECT in QBE
Cash Receipts from Customer C-2
7-29
Relational Algebra “Select” QBE Example:
Cash Receipts from Customer C-2
7-30
Relational Algebra SELECT in QBE
Cash Receipts from Customer C-2
7-31
Relational Algebra SELECT in QBE
Cash Receipts from Customer C-2
Enter =“C-2” as
Criteria in the
Customer
Number field
7-32
Relational Algebra SELECT in QBE
Cash Receipts from Customer C-2
Result
7-33
Relational Algebra PROJECT in QBE
Customer#, name, salesperson#
7-34
Relational Algebra PROJECT in QBE
Customer#, name, salesperson#
7-35
Relational Algebra PROJECT in QBE
Customer#, name, salesperson#
7-36
Relational Algebra PROJECT in QBE
Customer#, name, salesperson#
Result
7-37
Relational Algebra Inner Join in QBE:
All details of customers and their salespeople
7-38
Relational Algebra Inner Join in QBE:
All details of customers and their salespeople
7-39
Relational Algebra Inner Join in QBE:
All details of customers and their salespeople
7-40
Relational Algebra Inner Join in QBE:
All details of customers and their salespeople
Result
7-41
Relational Algebra Outer Join in QBE
Details of all sales, related cash receipts
7-42
Relational Algebra Outer Join in QBE
Details of all sales, related cash receipts
Double-click on
the join line
7-43
Relational Algebra Outer Join in QBE
Details of all sales, related cash receipts
Click on
appropriate
join type
Click OK
7-44
Relational Algebra Outer Join in QBE
Details of all sales, related cash receipts
Notice
change in
join line
7-45
Relational Algebra Outer Join in QBE
Details of all sales, related cash receipts
7-46
Relational Algebra Outer Join in QBE
Details of all sales, related cash receipts
Result
7-47
QBE with Mathematical Comparison Operator
Cash Account# and Balances >=$50,000
7-48
QBE with Mathematical Comparison Operator
Cash Account# and Balances >=$50,000
7-49
QBE with Mathematical Comparison Operator
Cash Account# and Balances >=$50,000
Result
7-50
QBE with Mathematical Comparison on
Character Attribute: Sales Not made by E-10
7-51
QBE with Mathematical Comparison on
Character Attribute: Sales Not made by E-10
7-52
QBE with Mathematical Comparison on
Character Attribute: Sales Not made by E-10
Result
7-53
AND operator in QBE: Sales made
before July 31 by Sales Rep E-10
7-54
AND operator in QBE: Sales made
before July 31 by Sales Rep E-10
7-55
AND operator in QBE: Sales made
before July 31 by Sales Rep E-10
Enter selection
criteria on same
line to accomplish
logical “AND”
7-56
AND operator in QBE: Sales made
before July 31 by Sales Rep E-10
Result
7-57
OR operator in QBE: Sales made
before July 31 OR by Sales Rep E-10
7-58
OR operator in QBE: Sales made
before July 31 OR by Sales Rep E-10
7-59
OR operator in QBE: Sales made
before July 31 OR by Sales Rep E-10
Enter selection criteria
on separate lines to
accomplish logical “OR”
7-60
OR operator in QBE: Sales made
before July 31 OR by Sales Rep E-10
Result
7-61
Aggregation SUM and Special Operator BETWEEN
in QBE: Total Sales between July 15 and July 31
7-62
Aggregation SUM and Special Operator BETWEEN
in QBE: Total Sales between July 15 and July 31
Bring only the
fields you
need into the
query grid
7-63
Aggregation SUM and Special Operator BETWEEN
in QBE: Total Sales between July 15 and July 31
Enter Criteria
with
BETWEEN
operator
Click on summation symbol
to add “Total” line to query
grid (used for aggregations)
7-64
Aggregation SUM and Special Operator BETWEEN
in QBE: Total Sales between July 15 and July 31
Total line defaults to “Group By”
for each field; Change the
Amount field to “Sum” and
change the Date field to “Where”
7-65
Aggregation SUM and Special Operator BETWEEN
in QBE: Total Sales between July 15 and July 31
7-66
Aggregation SUM and Special Operator BETWEEN
in QBE: Total Sales between July 15 and July 31
Result
7-67
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
7-68
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
Save query so that fields will be available to
the Expression Builder; then click on the
magic wand to start the Expression Builder
7-69
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
7-70
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
7-71
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
7-72
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
7-73
Horizontal Calculation in QBE:
Inventory-Sale Line Item Extension
Result
7-74
Querying Summary
• Querying provides the power of the relational database model
– Once you unlock the mystery of query construction, you can tap
into the wealth of information that is at your fingertips in a welldesigned relational database
• Querying requires organized thinking and logic
– You must understand the structure of the database tables and
the nature of the data in those tables.
– You must identify which table(s) are needed for each query, and
determine the appropriate manipulations that need to be made
in the appropriate sequence
• Some people find it helpful to organize their thinking by considering what
relational algebra operators are needed even though the relational algebra
language is rarely used
• Manually calculating the query result using a representative data sample is
also very helpful for identifying query errors
• Remember to separate horizontal calculations from vertical aggregations
• Comprehensive testing of queries is crucial before releasing
queries for use by general users
7-75
ENTERPRISE INFORMATION SYSTEMS
A PATTERN BASED APPROACH
Chapter 7
End of Chapter
McGraw-Hill/Irwin
Copyright © 2005 by The McGraw-Hill Companies, Inc. All rights reserved.