Database Management Systems

1
University of ManitobaAsper School of
Business3500 DBMSBob Travica
Chapter 6, part 2 Database System Development
(cont.)
Based on G. Post, DBMS Designing Building
Business Applications
Updated 2013
2
Complex Controls Used on Forms
Calendar

• Examples
• Tab
• Grid
• Calendar
• Gauge
• Slider
• Spin Box

Tab
Grid
Gauge
Spin box
Slider
3
Linking Forms
Customer FirstName Mary LastName Jones Address
123 Oaxaca Ave. City Los Angeles ZipCode
90086 Gender Female Age 20 AccountBalance 150
4
Complex Inputting via Forms Flow and
Calculations
Task Start sale while querying an
animal record.

• Linking via forms object collection
• Forms ! Form Name ! Control
• AnimalID is passed from Animal form to
• Sale form
• Subtotals and subforms
• Calculate subtotal in subform ItemsSold
• Pass the result of calculation (Subtotal) to
  main form Sale (Forms!MainForm!SubForm.Fo
  rm!Control)
• Add more calculations on main form
• (Tax, Order Total)

Animal
AnimalID
Sale
Forms!Animal!AnimalID
ItemsSold
- - - - - - - SubtotalSum(PriceQuantity)
Forms!Sale!ItemsSold.Form!Subtotal
Subtotal
SubtotalTaxRate
Tax
SubtotalTax
Order Total
5
Forms and Integrity

• Avoid relying on forms for integrity
• Set integrity conditions in table definitions
• Be sure to set referential integrity
  (relationships)
• Use forms for input validation contribution to
  data integrity
• Main purpose of form
• Use forms to increase usability of user interface
  (ease of use, efficiency)

6
International Attributes

• Language
• Character sets and punctuation marks
• Sorting
• Data formats
• Date
• Time
• Metric vs. Imperial system
• Currency symbol and format
• Separators (decimal, . . .)
• Phone numbers
• Separators
• International code prefix
• Postal codes
• National ID Numbers

7
Direct Manipulation of Graphical Objects

• Ease data input for end-users Drag and drop
  objects (blue arrows).

8
Creating a Graphical Approach

• Get the hardware.
• Scanners, Microphone and Sound card, Camera
• Lots of disk space.
• High speed processors.
• Design
• Add an object column to your table definition.
• Avoid using graphics just for show.
• Double-click, drag-and-drop as manipulation
  methods

9
Reports

• Security concerns
• Distribution list
• Unique numbering ore reports
• Concealed/non-printed content
• Secure printers
• Transmission limits
• Print queue controls

• Core of system development
• Electronic, but can also be printed on paper.
• Database queries reports TPS
• TPS more elaborate reports MIS, reports
  define MIS
• How often is report generated?
• Events that trigger report?
• (time - regular, by user exception
  reports)
• Size of report? Copies? Colors?...

10
Paper Report Terminology

• Page Layout
• Landscape v. portrait
• Margins
• Gutter (binding space)
• Typefaces
• Serif (Times New Roman)
• Sans-serif (Arial, Verdana)
• Fixed width
• Font size
• common 10-12 point

11
Paper/Electronic Report Types Tabular
12
Electronic Report Types Group

• Organizing report around a certain attribute
• Example Column Order with associated Items

• Calculations supported

13
Electronic Report Design View

• Ideas of hierarchy and
• nested structuring
• Report Header
• Page Header
• Group Header1
• Group Header2
• . . .
• Detail
• . . .
• Group Footer2
• Group Footer1
• Page Footer
• Report Footer

More
14
Electronic Report Design Terminology

• Group Footer
• Subtotals for the group.
• Page Footer
• Printed at the bottom of every page--page totals
  or page numbers and notes.
• Report Footer
• Printed one time at the end of the report.
  Summary notes, overall totals and graphs for
  entire data set.

• Report Header
• Title pages that are printed one time for entire
  report.
• Page Header
• Title lines or page notes that are printed at the
  top of every page.
• Group Header
• Data for a group (e.g., Order) and headings for
  the detail section.
• Detail
• Innermost data.

More
15
Electronic Report Design Groups

• Groups used for 1M relationships.
• Use a query to join all necessary tables.
• Can include all columns.
• Use query to create computed columns (e.g.,
  ExtendedPriceQuantity).
• Each one-to-many relationship becomes a new
  subgroup.

• Customer(C, Name, )
• Order(O, C, Odate, )
• OrderItem(O, Item, Qty, )

Report of Orders Rpt footer graph
orders by customer
Group1 Customer H1 Customer name, address,
F1 Customer total orders
Group2 Order H2 Order, Odate,
Salesperson. F2 Order total Sum(Extended)
Detail Item, Qty, Extended
16
Report Computations

• Query does
• Some row computations.
• Extended Price PriceQuantity
• Report does
• Totals of grouped records.
• Page and report totals.
• Other computations commission rate total
• Scope depends on location
• Group footer subtotal
• Page footer page total
• Report footer report total

17
Graphs in Reports

• Graphs
• Separate query needed.
• Detail
• Locate in detail or group footer section.
• Avoid aggregation and groups in query.
• Include column that links to detail query in
  report.
• Subtotals and totals
• Typically located in report footer or header.
• Compare group totals
• Relies on Group By and aggregation.
• Be sure query groups match report groups.

18
Graphs From Queries

• Build a query that generates the data to be
  graphed.
• Numeric data
• Columns to link to form.
• Summary chart--unlinked.

19
Graph for Groups