Midterm

1
Midterm 1 Results
Average 153.87 Std. Dev. 21.03 Median 159.25 Ave
rage Grade B

• Good work! Most students have a solid
  understanding of the course material
• Exam solution will be reviewed in Wednesdays
  session
• Extra credit points will be used for midterm and
  final grade determination

2
Administrative Details

• Midterm grades will be posted to administration
  by Monday 10/18
• HW 7 due Tuesday 10/26
• No lab or workshop this week due to Heinz School
  mid-semester break and Network NY
• Lecture on Tuesday 10/19 will focus on class
  project description and project management
• Lab on Thursday 10/21 will focus on reports
• Lab on Friday 10/22 will focus on project
  management using Microsoft Project98
• HW 6 will not be collected

3
Reporting in the Relational Database Model

• The relational database model (RDBM) we have
  studied has the following key characteristics
• Normalized tables
• Primary key uniquely defines each record in a
  table
• All attributes are dependent on the primary key
  and not each other
• Focus on data integrity
• Multiple tables with minimum number of columns
• Operational focus
• Short time frame
• Specific transactions that occur at a given time
• Queries are small in scope and complexity
• Business rules implemented through E-R diagrams
  and application-level code

How can we summarize and present data in order to
make tactical or strategic, as well as
operational decisions?
4
Example Reporting Requirements for RDBM

• Tactical/Strategic
• What 10 buses have had the most repairs in the
  past year?
• What is the average number of gallons in gas
  fill-ups?
• What percentage of all brake inspections for Ford
  trucks have resulted in repairs?

• Operational
• What repairs have been performed between 6/1/99
  and 7/30/99? What parts were used in each repair?
• What inspections have been performed on buses
  30, 42 and 70?

5
Architecture of RDBM Reports

• Report Header - Information which identifies the
  report title, organization,
• Group Header - Segment which identifies section
  of data that are similar according to grouping
  criterion
• Group Body Rows of data corresponding to records
  in table/view that have same grouping criterion
• Group Footer Segment which summarizes
  information in group e.g. though descriptive
  statistics
• Report Footer Segment which summarizes
  information in entire report
• Page Footer Information which appears at the
  bottom of every page date/time/page

6
Typical RDBM Reports

• Basic reports
• use a single table, or a table plus a code table,
  as input to the report
• one report row per table record plus a summary
  row
• Example report of buses currently in use
• group by make
• summarize by average odometer reading of all
  buses in make category

7
Typical RDBM Reports (contd)

• Complex reports
• use a data view/ complex query as input
• apply grouping levels
• Example report of employee usage by repair event
  (summarize total cost)

8
Typical RDBM Reports (contd)

• Reports/Subreports
• use one data view for information according to
  one criterion (main report)
• use another data view for other information
  according to another criterion (subreport)
• Example Monthly gasoline usage summary
• Gas fill up summary (main report)
• Gas tank levels (subreport)

9
Using the Report Wizard

• Select tables/queries
• Confirm relationships
• Select grouping levels
• Select sorting rules
• Specify summary information
• Select report format
• Preview

10
Scoping Out A Report

• What information do I want the report to contain?
• List of data for particular tables and summary
  statistics (simple report)
• Trends over time, across company divisions or for
  ranges of values
• Spatial data as well as aspatial data
• How do I want the information organized?
• Simple tabular or columnar representation
• Grouped/sorted by criterion values
• Cross-tabulations by multiple criteria
• Report/subreport
• What will be the reports look and feel?
• Report header/footer? Group header/footer?
• Font sizes/typefaces?
• Formal (annual report) or informal (newsletter)
• How will the report be used?
• Once or periodically?
• Based on user input or hard-coded?

11
Putting the Report Together

• Create queries that group the data
• Underlying data can be modified without having to
  open the report (useful on the road)
• One query can be used for multiple reports
• Confirm data accuracy without printing out the
  report
• Create a report template (perhaps with Access
  Report Wizard)
• Save header/footer formats and titles
• Preserve a consistent look and feel
• Create a report prototype for inspection by
  client
• Are the data correct?
• Is the look and feel acceptable?
• Identify alternative ways to profile the data
• Revise, revise, revise!
• Work by hand if Report Wizard cant do what you
  want
• Automate work with macros or Visual Basic for
  Applications where possible

12
Reporting in a Decision Support System

• DSS reports are often much more complex than
  reports in operationally-oriented databases
• Summarization of large volumes of data
• Use make-table queries for greater speed
• Frequent use of delete and update queries
• Complex cross-tabulations
• Variety of date ranges by year/quarter/month/week
  /day/shift/hour
• Multiple row headings
• Summarize across rows as well as down columns
• Present data in a variety of formats
• Textual/Graphical/Spatial
• Interface with other applications
• Word processor
• Spreadsheet
• Web