Jones Rama

Accountinginformation

system

A Business process approach

FREDERICK L. JONESDASARATHA V. RAMA

LOGO

Chapter 6UNDERSTANDING AND DESIGNING QUERIES AND REPORTS

King Saud University

Seham al otibi

Reference Lists and Status Reports

Contents

Introduction

QUERY

TYPES OF REPORTS

EVENT REPORTS

Introduction

• A database management system (DBMS ) : is a collection of programs that enables you to enter , organize , and select information from a database.

• A query is a request for information from a database• A report is a formatted and organized presentation of data

Query

1

2

Query languages – Enable users to communicate with the DBMS in more structured formats

Query languages – Enable users to communicate with the DBMS in more structured formats

Structured query language (SQL)Structured query language (SQL)

Query by example (QBE)

Structured query language (SQL)Basic format of an SQL query

format SQL Example• SELECT attributes SELECT order#,

Data ,customer#

• FROM tables FROM order

• WHERE criteria WHERE Data=#06/01

Query

Query

1

2

3

This Format allows: This Format allows:

Accessing data from tables based on a conditionAccessing data from tables based on a condition

Specifying tables from which attributes used in the criteria can be obtained Query by example (QBE)

Specifying attributes to include in the output

Query

Query languages – Query by Example (QBE):

• Simpler but less powerful approach for communicating with the database

• Specify the desired output from one or more tables using a grid– Enter name of each desired attribute (field)

into a different column in the grid– Enter selection criteria in the appropriate

attribute’s column• User doesn’t have to learn details of SQL

Query specification:

Single table queries1. What attributes do users require in the query output? What tables contain the attributes required?2. What criteria will be used to generate the output? What attributes will be used in the criteria? What tables contain the attributes

Multiple table queries

– Make sure the different tables in the query are properly linked to each other

– Answer question 1,2 & additional question :3.What foreign keys link the information in a table to the primary keys of other tables in the query?

Query specification:

• Single table queries – •Use a QBE grid to specify the given

information to the DBMS or •Express the information in a SQL

statement

Example

• ELERBEs orders database• QUERY A:List of all publication by a specific

author (e.g,”Cromwell’’)

• QUERY C:A new edition of a publication (e.g., ISBN=0-127-35124-8) is being planned . The marketing manager wants to contact customers who ordered a large number of copies of this product in 2002. Assum that the order table contains orders for multiple years. The marketing manager wants the report to include the customer name, address, contact person, phone, and quantity ordered.

Example

Summary of ELERBEs DatabaseInventory Table

Customer Table

ISBN Author Title Price Quantity_On_Hand

Quantity_Allocated

0-256-12596-7 Barnes Introduction to business $78.35 4000 300

0-127-35124-8 Cromwell Building Database Application $65 3500 0

0-135-22456-7 Cromwell Management Information system

$68 5000 50

Customer Name Address Contact_person phone

3450 Brownsvill c.c Brownsvill ,TX Smith 956-555-0531

3451 Educate,Inc Fairhaven,MA Cosa 508-888-4531

3452 Bunker Hill C.C Bunker Hill C.C Lafrank 617-888-8510

Example

Order Table

Order Detail Table

order Order _Date Customer

0100011 05/11/2003 3451

0100012 05/15/2003 3451

0100013 05/16/2003 3450

order ISBN Quantity

0100011 0-256-12596-7 200

0100012 0-135-22456-7 50

0100013 0-146-18976-4 35

Single table queries

QUERY A :List of all publications by Cromwell SQL Inventory Table1-what attributes do users require ISBN,Author,Title in the query output ?

2-what criteria will be used to Author=‘’Cromwell’’ generate the output ?What attributes will be used in the criteria ?

Example

Example

An SQL statement for QUERY A :

SELECT ISBN , Title, Author

FROM Inventory

WHERE Author=‘’Cromwell’’

Example

Under the QBE approach in MS Access, the system will first prompt you for the table from which you query must be answered. Then, you must specify the output criteria on the grid

(see page 246)

Example

Multiple table queries ( SQL)QUERY C :The marketing manager wants a report that shows all orders

for a book identified as ISBN 0-127-35124-8 during the year 2002. The information needed includes Name, Address , Contact _ Person, Phone ,and Quantity

Example

Table order order Detail customer-1-what attributes do Quantity Name,Address,

users require in the query Contact_Person,Phone output ?2-what criteria will be used to generate order_Date>#12/31/2001# and order_Date<#01/01/2003# ISBN=0-127-35124-8

output ?What attributes will be used in the criteria ?3.What foreign keys link the customer#(to identify order#(to linkinformation in a table to the appropriate customer) to the order records)primary keys of other tables in the query?

Queries

Specifying Criteria With Multiple ConditionsExample(Order Data<#12/31/2001#AND Order

_Data<#01/01/2003)AND ISBN=0-127-351124-8

That each of the conditions in the criteria must be true for the overall criteria to be satisfied.

Types of Reports

• 4 reports based on the organization of the data -– Simple list– Grouped detail– Group Summary– Single entity

• We can classify reports based on the type of data

Types of Reports

First , we review the format (layout) of a report.

Then, we consider the following questions related to report design: – 1-What data are included in a report? – 2-How are the data organized?

Types of Reports

Report layout:• Most reports have:

– Report header – Page header – Page footer – Report footer – Report details section

Types of Reports

Report Layout:• Two important elements

of any report:– Label boxes

• Display descriptive text • Unaffected by table data • Static - do not change when underlying data

changes– Text boxes

• Display data taken or derived from a table• Depends on current contents of table• Text boxes are dynamic

Types of Reports

Report Layout:• Grouped Attribute.

– Grouped reports are grouped by something

– Contain:•Group header•Group detail•Group footer

contenue

Types of Reports

• Report content:- Designers should analyze the information needs of the users

and then decide what data need to be included in each report.

- the data items required for the report should be identified.- select report information based on criteria

• Report Organization: Designers have several choices for organizing the

information on a report.

Event Reports

• List or summarize event data available in transaction tables

• Draw on data available in transaction tables for most of their content

• Criteria are used to restrict output to specific events

Event Reports

• Classifies event reports according to the four report types:

- Simple Event List- Grouped Detail- Group Summary- Singel Event

Event Reports

Simple event lists:• Provide asimple listing of events during a

time period organized by event date or transaction with

No grouping or subtotals

• Any criteria used to select information should be shown on the report to inform the user of these criteria.

• As shown in the layout,the starting and ending dates of these events coud be listed in the report header

Event Reports

Grouped event detail reports:Show a list of events during a period-Commonly grouped by products/services or

agents- Subtotals are often reported -Easier to analyze than ungrouped reports

Event Reports

Group Event summary report: Summarizes event data by various parameters -May be superior to grouped event detail

reports when a large number of groups needs to be reported

Event Reports

Single event report: gives details about a single event -Often printed for documentation or to give to

customers or suppliers

Reference Lists and Status Reports

• Focus on providing information about products, services, or agents

• Criteria can be used to restrict the output

Reference Lists and Status Reports

• Four types of reports that focus on organizing and summarizing master table data – Reference lists– Grouped detail status reports– Summary status reports – Single product/service/agent status

reports

Reference Lists and Status Reports

Reference lists:- Report only reference data that are taken

from master tables - Information about balances or quantity on

hand not included in reports

Reference Lists and Status Reports

Status reports: - Summary data about products, services,

agents• Grouped detail status reports

– Display summary data, usually some reference data, and supporting details

– Draw on event and product/service/agent records

– Usually list one type of event

Reference Lists and Status Reports

Status reports:• Summary status reports

– List reference and summary data about products/services/agents

– Summarize the status of the product, service, agent

• Single product/service/agent status reports – Provide detailed data – Include both reference and summary data

for a single entity

Copyright

COPYRIGHT©2003 BY SOUTH-WESTERN, A DIVISION OF THOMSON LEARNING. THOMSON LEARNING™ IS A TRADEMARK USED HEREIN UNDER LICNSE .

Printed in Canada “1 2 3 4 5 05 04 03 02” from more information, contact south-western, 5191 natorp boulevard, mason, ohio 45040.

Or you can visit our internet site at http://www.swcollege.comAll rights reserved.No part of this work covered by the copyright hereon may be reproduced or used in any form or

by any means—graghic , electronic, or mechanical, including photocopying, recording , taping, web distribution or information storage and retrieval systems—without the written permission of the publisher.

For permission to use material from this text or product, contact us by Tel:(800)730-2214Fax:(800)730-2215http://www.thomsonrights.com

LOGO

www.themegallery.com

.