Enterprise Information Systems: A PatternBased Approach By Dunn, Cherrington, and Hollander

1
Enterprise Information Systems A Pattern-Based
ApproachBy Dunn, Cherrington, and Hollander
Chapter 7 Information Retrieval from Relational
Databases As modified by JKW
2
The Need for Multiple Views of One Data Set Some
Examples

• 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???????
3
Answer By Querying 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.

4
Querying/Information Retrieval

• Several ingredients are necessary for good
  information retrieval
• A database that is well-designed
• A query developer who understands the table
  structures and the nature of the data in the
  tables
• A query developer who understands the desired
  query output
• A query developer who has good logic skills

5
Three Query Languages

• Relational Algebra (not covered)
• 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.

6
Example Tables (Incomplete Enterprise
Database)from Dunn McCarthy (2003) working
paper
7
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.,

8
SQL (Structured Query Language)

• Each query statement follows the same
  structureSELECT attribute name(s)FROM table
  name(s)WHERE criteria is met

9
SQL Statements and Relational Algebra

• SQLs SELECT component isolates columns
• i.e., relational algebras project
• SQLs FROM component is used for identifying the
  table(s) involved
• if gt1 table, helps accomplish relational
  algebras join (together with WHERE component
  that specifies equal fields)
• SQLs WHERE component isolates rows
• i.e., relational algebras select
• also helps accomplish relational algebras join
• may be left blank for single-table queries that
  retrieve all rows

10
Relational Algebra Select QBE Example Cash
Receipts from Customer C-2
11
Result Cash Receipts from Customer C-2
12
Relational Algebra Project QBE Example
Customer, name, salesperson
13
Result Customer, name, salesperson
14
Relational Algebra Inner Join QBE Example All
details of customers and their salespeople
15
Result Details of customers and their salespeople
16
Relational Algebra Outer Join QBE Example All
details of sales and related cash receipts
17
Result Details of sales and related cash receipts
18
Mathematical Operators

• SQL Queries may include mathematical operators
  such as
• equal to
• lt less than
• lt less than or equal to
• gt greater than
• gt greater than or equal to
• ltgt not equal to (or ! in some software)
• Mathematical 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 lt B lt C, etc.

19
SQL Example with Mathematical Operator

• Select Account, BalanceFrom CashWhere
  Balancegt50000

20
QBE Example with Mathematical Operator Cash
Account and Balances gt50,000
21
Result Cash Account and Balances gt 50,000
22
SQL Example Using Mathematical Operators on
Character Attributes

• Select Sale, AmountFrom SaleWhere SalesRep ltgt
  E-10

23
QBE Example using AND operator Sales made before
July 31 by Sales Rep E-10
24
Result Sales made before July 31 AND by E-10
25
QBE Example using OR operator Sales made before
July 31 OR by Sales Rep E-10
26
Result Sales made before July 31 OR by E-10
27
Queries with Aggregation Functions

• 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

28
QBE Example of Aggregation SUM and Special
Operator BETWEEN Total Sales between July 15 and
July 31
29
Result Sum of Sales Made Between July 15 and
July 31
30
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

31
QBE Example of Horizontal Calculation
Inventory-Sale Line Item Extension
32
Result Inventory-Sale Line Item Extension
33
Summary

• Querying requires organized thinking and logic.
  In order to develop accurate queries,
• You must understand the structure of the database
  tables from which you need to retrieve data and
  you must understand the nature of the data in
  those tables.
• You must identify which table is (or tables 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
• Remember to separate horizontal calculations from
  vertical aggregations
• Comprehensive testing of queries is crucial
  before releasing queries for use by general users