Chapter 6, part 2 Database System Development (cont.)

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. Examples: Tab Grid Calendar Gauge Slider Spin Box Complex Controls Used on Forms Calendar Tab Grid Gauge Spin box Slider

3. Using forms for the output by linking them. Sale Button triggers query for the customer record Customer FirstName: Mary LastName: Jones Address: 123 Oaxaca Ave. Edit Animals Purchased Merchandise Subforms Linking Forms Customer FirstName: Mary LastName: Jones Address: 123 Oaxaca Ave. City: Los Angeles ZipCode: 90086 Gender: Female Age: 20 AccountBalance: $150

4. 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].Form![Control]) Add more calculations on main form (Tax, Order Total) Complex Inputting via Forms – Flow and Calculations Task: Start sale while querying an animal record. Animal AnimalID Sale =Forms!Animal!AnimalID ItemsSold - - - - - - - Subtotal=Sum(Price*Quantity) =Forms!Sale!ItemsSold.Form!Subtotal Subtotal Tax =Subtotal*[TaxRate] Order Total =Subtotal+Tax

5. 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): Forms and Integrity

6. 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 International Attributes

7. Ease data input for end-users: Drag and drop objects (blue arrows). Current Choices Kennel/ Orders Bird Cat Tabby Dog Fish Mammal Reptile Spider Brown Lab Direct Manipulation of Graphical Objects

8. 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 Creating a Graphical Approach

9. 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?... Reports

10. 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 Facing pages (portrait) gutter margins Paper Report Terminology

11. Paper/Electronic Report Types: Tabular

12. Organizing report around a certain attribute Example: Column Order with associated Items Electronic Report Types: Group • Calculations supported

13. Ideas of hierarchy and nested structuring… Report Header Page Header Group Header1 Group Header2 . . . Detail . . . Group Footer2 Group Footer1 Page Footer Report Footer Electronic Report Design View More…

14. 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. Electronic Report Design Terminology More…

15. Groups used for 1:M relationships. Use a query to join all necessary tables. Can include all columns. Use query to create computed columns (e.g., Extended:Price*Quantity). Each one-to-many relationship becomes a new subgroup. Customer(C#, Name, …) Order(O#, C#, Odate, …) OrderItem(O#, Item#, Qty, …) Electronic Report Design – Groups 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. Query does: Some row computations. Extended Price = Price*Quantity 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 Report Computations

17. 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. Graphs in Reports

18. Build a query that generates the data to be graphed. Numeric data Columns to link to form. Summary chart--unlinked. Sale 1 Merchandise Animal Sale 2 Merchandise Animal Merchandise Sale 3 Animal Total Sales Merchandise Animals Graphs From Queries

19. Graph for Groups