Midterm #1 Results

1. Midterm #1 Results Average 153.87 Std. Dev. 21.03 Median 159.25 Average Grade: B+ • Good work! Most students have a solid understanding of the course material • Exam solution will be reviewed in Wednesday’s session • Extra credit points will be used for midterm and final grade determination 90-728 MIS Lecture Notes

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 90-728 MIS Lecture Notes

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? 90-728 MIS Lecture Notes

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? 90-728 MIS Lecture Notes

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 # 90-728 MIS Lecture Notes

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 90-728 MIS Lecture Notes

7. Typical RDBM Reports (cont’d) Complex reports: • use a data view/ complex query as input • apply grouping levels Example: report of employee usage by repair event (summarize total cost) 90-728 MIS Lecture Notes

8. Typical RDBM Reports (cont’d) 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) 90-728 MIS Lecture Notes

9. Using the Report Wizard • Select tables/queries • Confirm relationships • Select grouping levels • Select sorting rules • Specify summary information • Select report format • Preview 90-728 MIS Lecture Notes

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 report’s 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? 90-728 MIS Lecture Notes

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 can’t do what you want • Automate work with macros or Visual Basic for Applications where possible 90-728 MIS Lecture Notes

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 90-728 MIS Lecture Notes