Tables, Queries and Reports

1
Tables, Queries and Reports

• Load Transaction (Event) data and Master (Agents,
  Products/Services) data into separate tables
• Database Management System (DBMS) allows us to
  request information via a query
• These queries and tables can be the source of
  reports
• Organized and formatted presentation of data

Reports
Queries
Tables
2
Query Languages

• Structured Query Language (SQL)
• SELECT attributes What attributes are we
  interested in?
• FROM tables What tables contain those
  attributes?
• WHERE criteria Which rows?
• Query by example (QBE)
• Drag and drop GUI designer
• Built in to Access and other packages

3
Tables
Inventory
Customer
Order_Detail
Order
4
Simple Queries

• Show all books by Cromwell
• SELECT ISBN, Title, Author
• FROM Inventory
• WHERE Author Cromwell

QBE
5
Simple Queries

• What orders were placed on 05/15/2006?
• SELECT Order, Order_Date, Customer
• FROM Order
• WHERE Order_Date 05/15/2006

QBE
6
More Complex Queries

• A new edition of 0-135-22456-7 is being planned.
  We need to contact anyone who ordered a large
  number of copies in 2006 to let them know.
• SQL
• What attributes do we need?
• What tables do we need?
• What foreign keys link the tables?

7
What is that at the bottom?
SELECT Customer.Customer, Customer.Contact_Pers
on, Customer.Phone, Order_Detail.Quantity FROM
(Customer INNER JOIN Order ON
Customer.Customer Order.Customer) INNER
JOIN Order_Detail ON Order.Order
Order_Detail.Order WHERE (((Order_Detail.ISBN)
"0-135-22456-7") AND ((Order.Order_Date) Between
12/31/2005 And 1/1/2007))
8
Technical Terms in SQL

• Select rows
• Eliminate unwanted rows
• Project columns
• Eliminate unwanted columns
• Join tables
• Combining two or more tables based on common
  attributes
• SELECT PART.Part_Number, SUPPLIER.Supplier_Number,
  SUPPLIER.Supplier_Name, SUPPLIER.Supplier_Address
  
• FROM PART, SUPPLIER
• WHERE PART.Supplier_Number SUPPLIER.Supplier_Num
  ber AND Part_Number 137 OR Part_Number 152

9
Name and Address of Suppliers of part 137 or 152?
10
Navigation Template for Query Design

• Two ways to identify information in tables
• Identify records that satisfy a certain condition
• Identify records where a value in the attribute
  of one table matches the value of some attribute
  in another table
• Foreign key
• Build a navigation table

Table 1
Table 2
Find records where a attribute meets a criterion.
Get value of linking attribute
Find records where a attribute meets a criterion.
Get value of linking attribute
11
Who bought Management Information Systems in
2006? No, I dont know the ISBN you plebe. Oh, I
also need quantity.
Inventory
Order_Detail
Order
Customer
Find books with title Management Information
Systems
Get the ISBN
Find records with matching ISBN
Get the Order
Find records with matching Order
Find records where Order_Date is in 2006
Get the Customer
Find records with matching Customer
Display
Name, Address, Contact_Person, Phone
Quantity
12
Reports

• Built in to most DBMS
• Four main types
• Simple lists
• Show me the list of layaways for yesterday
• Grouped detail
• Show me the list of layaways for yesterday split
  out by sales clerk
• Summary
• Show me are total layaway sales (by department)
• Single entity
• Show me the layaway agreement for Jim Smith

13
New Example
(1,m)
(m,1)
Sale
Inventory
Manager
(m,1)
(1,m)
Sale_Detail
Deposit
(m,1)
Manager
Product
14
New Example, Cont.
(1,m)
(m,1)
Sale
Inventory
Manager
(m,1)
(1,m)
Sale
Sale_Detail
Deposit
(m,1)
Sale_Detail
Deposit
15
Single Event List Sale Detail
Use report wizard
16
The Base Report
Total?
17
Customizing Base Report for Total
1. High light report, click on
3. Add a textbox (click on, draw here)
2. Add report footer
18
Customizing Base Report for Total
4. Highlight new text box, click
5. Set Equation and Format
6. Set Label to Total
19
Grouping in Reports

• Use Report Wizard but ask for Grouping Levels

Click on Product
Later, hit this button
20
Grouping in Reports
X
X
21
Report Types and Examples

• simple event list
• A daily list of orders received.
• grouped event detail report
• A list of orders received during a period by
  product. The reference data about the product
  could be displayed in a group header followed by
  a list of all orders for that product during the
  specified period. The total orders for each
  product could be shown in the group footer.
• event summary report
• Total orders for each product during a period
  (without details of individual orders for the
  product).
• single event report
• A sales order.

22
Report Types and Examples

• reference listing
• A list of all customers.
• grouped detail status report
• An inventory report with reference data and
  beginning quantity on hand in the report header.
  The quantity on hand at the end of a period of
  each product could be shown in the group footer.
  The list of sales for each product could be
  shown.
• summary status report
• An inventory report showing reference data and
  quantity on hand but not the details of sales for
  each product.
• single agent report
• A customer report showing beginning balance,
  invoices, payments, and ending balances.