D A T Database Management Systems A B

D A T Database Management Systems A B Chapter 6 A Forms and Reports S Jerry Post E Copyright © 2013 1

Objectives ² ² ² How do users interact with the database? What is the difference between a good form and a bad form? What common structures are used in forms? What are the main steps used to create forms? Can form usability be improved? What are the basic roles of reports? 2

Usability ² Never give users direct access to tables! ² Consider Sale. Item: Sale. ID, Item. ID, Sale. Price, Quantity ª Users will not understand the table. ª They will not enter data correctly. ª They will be frustrated. ² Even for simple tables such as Customer, use a form. ² Create Forms to enter data. ² Create Reports (and interactive forms) to examine data. ² Forms must be intuitive and easy to use. ª Need to match the forms to what the users understand the way they work. ª Follow current interface practices and standards (which evolve). 3

Uses of Forms ² ² ² Collect Data Display Query Results Display Analysis and Computations Switchboard for other Forms and Reports Direct Manipulation of Objects ª Graphics ª Drag and Drop and touch 4

Human Factors Design ² User Control ª Match user tasks. ª Application responds to user control & events. ª User customization ² Consistency ª Layout, Design & colors ª Actions ² Clarity ª Organization ª Purpose ª Terminology ² Aesthetics ² Feedback ª Methods © Visual © Text © Audio ª Uses © © Acceptance of input Changes to data Completion of tasks Events / Activation ² Forgiveness ª Anticipation and correction of errors ª Confirmation on delete and updates ª Backup and recovery ª Art to enhance, graphics ª Sound 5

Interface / Accessibility ² Multiple Input Methods ª Keyboard ª Mouse ª Voice ² Multiple Output ª Visual ª Sound ª Color ² Some Suggestions: ª Beware of Red/Green. ª Avoid requiring rapid user responses. ª Avoid rapid flashing on the screen. ª Give users customization options. © Volume © Color © Typefaces & Fonts 6

Standard Form Controls Label Option or Radio button Last Name Text box City Drop down list or Combo. Box Individual Corporate Animal Interests Check box Bird x Cat x Dog Save Button Reptile 7

Foreign Keys and Drop Down Lists Customer Table CID Last First Phone 113 Brown Sue 2223 115 Jones Mary 0394 116 Sanchez David 3958 Query: display list Sales Form Sale ID 298 Sale Date 6/1/… Customer Jones, Mary 113 115 116 Brown, Sue Jones, Mary Sanchez, David Selected value Sales Table Sale. ID Sale. Date CID 298 6/1/… 115 10

User Interface—International ² ² ² ² Language and characters Currency Time zones Time and date formats Calendar—starting day Number formats Country names and maps National ID numbers—privacy 11

International Attributes ² ² Language Character sets and punctuation marks Sorting Data formats ª ª ª Date Time Metric v English Currency symbol and format Separators (decimal, . . . ) Phone numbers © Separators © International code prefix ª Postal codes ª National ID Numbers 12

International: Multiple Languages English Last. Name. Label City. Label Individual. Label Corporate. Label Bird. Label Cat. Label x Dog. Label Reptile. Label Last Name City Individual Corporate Bird Cat Español Animal. Interests. Label x Last. Name. Label City. Label Individual. Label Corporate. Label Bird. Label Cat. Label … Save Button Last. Name. Label City. Label Individual. Label Corporate. Label Bird. Label Cat. Label … Nombre de Familia Ciudad Individuo Corporativo Pájaro Gato Resource file for each language 13

Style Sheets and Templates Template: Page Layout Style sheet: Fonts+Colors Menu … … … Menu. Text Black, 10 point, … Help icon Help. jpg Title. Text Black, 12 point, bold Label. Text Blue, 9 point, … Help Title Labels Controls… Find Edit Delete ? Animal. ID Name … 14

Form types Form Type Common Uses Tabular Multiple rows of data. Lookup lists or tables with a limited number of columns when it is useful to see several rows of data at a time. Single row One row of data at a time. The most common type. Provides complete control over page layout and space for many columns and links. Subforms Combine row and details. One-to-many relationships. Repeating section shows data related to main form. The items section of a sales form is a typical example. Startup Customized with buttons. A designed form that is used as the main menu to open the other forms and reports. 15

Tabular Form Works best for single table. Designer can control data entry sequence. Probably include buttons for sorting. 17

Single Row (Columnar) Form Data for only one row. Designer can set optimal layout. Similar in appearance to paper forms. Can use color, graphics, and command buttons to make the form easier to use. Note the importance of the navigation buttons. Probably want a Find command. Useful to include subforms. 18

Sub-Forms Typically a one-to-many relationship. Subform contents are linked to the main form through a common column (not displayed on the subform. ) Can have multiple subforms (Independent or Nested). 19

Startup Form Blank Form Graphics/Picture/Background Identify User Choose Task. 20

Menu Design Main Menu 1. Setup Choices 2. Data Input 3. Print Reports 4. DOS Utilities 5. Backups Hard to understand Customer Information Daily Sales Reports Friday Sales Meeting Monthly Customer Letters Quit Organized by user tasks. 21

Forms: One Table ² Each form should focus on a single event. ² Each form should add new data to only one table. ª Straightforward form as single-row or even multiple rows. ª For main/subform © The main form is tied to one main table. © The subform is tied to a second, linked table. ² Queries ª ª Often you want to display data on a form from multiple tables. Such as Sales, which needs to include Customer data. To work, the form needs to include all columns from the main Sale table In most systems, you can use a query to add in some columns from the Customer table—but never include its key column: Customer. ID 22

Form Query Example ² Clerk enters a Customer. ID. ª Stored in the Order table. ² Query joins Sale and Customer. ª Automatically matches the Customer. ID. ª Matching name is displayed on the form. ª Do not include the join column (Customer. ID) from the look up table (Customer). Customer Order Sale. ID 1234 Date 7/25/01 Customer. ID 17 Carly Embry 23

Updateable Query Customer Order Sale. ID 1234 Date 7/25/. . 17 Customer. ID Carly Embry Data entry Data display Customer Sale. ID 1232 1233 1234 Customer. ID 23 74 17 Sdate 7/24/. . 7/25/. . Query Join Customer. ID 15 16 17 First Last Connie Fisher Rosie Wade Carly Embry 24

Updateable Query SELECT Sale. ID, Sale. Date, Sale. Customer. ID, Customer. Last. Name, Customer. First. Name, Customer. Phone FROM Sale INNER JOIN Customer ON Sale. Customer. ID=Customer. ID The Sale form is designed primarily for the Sale table. The query includes all columns from the base table (Sale). It can include some columns from the Customer table. But do not include key columns for other tables (Customer. ID) 25

Linked Forms Sale Form Customer. ID 15 Customer Edit Customer. ID Last Name First Name Phone 15 Connie Fisher (409) 116 -3589 If you cannot or do not want to use subforms, You can created linked forms. It is a common approach on the Web. The goal is to keep simple screens that work with a single topic. 26

Form Properties Category Properties Data Base table / query Filters Sort Integrity Edits Additions, deletions Locks Format Caption Scroll bars Record selectors Navigation buttons Size and centering Background/pictures Colors Tab order Other Pop-up menus Menu bar Help 27

Form Properties: Access 28

Form Properties: Visual Studio 29

Controls on Forms (Basic) Label Text box Last Name Drop down list or combo box Clothing Shoes Electronics Country Payment Method Options Credit Card x Gift wrap x Gift card Check Monogram Cash Option button Check box List box Sales Comma nd button 30

Visual Studio Controls The list is long. And you can buy or write additional controls. 31

Pictures ² Background pictures ª ª Unbound, unchanging. Stored with the form. Keep edit screen readable. Sizing (zoom, scale, clip). ² Pictures stored as data ª ª Employee Name: Che Zhang ID: 3354 Phone: 222 -111 -1524. . . Bound to a data column. Photo: Define column as object. Tie to scanner or graphics package through OLE. Beware of data size © Resolution © Number of colors ª User machine capabilities. 32

Handling Photos for Web Apps ² It is usually best to store images as files on the server. ² Pages are delivered with HTML controls and HTML knows how to integrate image files. ² If you store the actual image in a database table, you need to write a program page to convert HTML requests into database queries to retrieve the image and stream the bytes to the browser. ² Storing images in a database quickly eats up table space. The free versions of commercial DBMS software allow limited storage space. ² It is easier to transfer images as files than to export them and import them through the database. 33

Basic Controls Label Text box Command button Option group Single select Check box List box Combo box 34

Combo & List Boxes ² User selects from a list ² Combo box can enter new data, or restrict to list. ² Two basic uses: ª Insert a value into a table © Choose from a list of preset options, e. g. gender. © Select from a different table, e. g. , choose a customer. ª Find the data record in this form that matches the choice. ª Be careful! Many systems do not distinguish between the two uses (enter data and search). ² Example when you want to use data entry: ª On a sales form, use a combo box for customer. ª It takes a value from the Customer table and inserts the ID into the Sale table. ² Example when you want to use a search: ª On a Customer edit form, you might use a combo to search the Customer table. ª Be sure the combo is not bound to the table! ª Probably need to write code for search. 35

Combo Box ² Control. Source sets the column to receive the choice (in the Sale table) ² Row. Source generates the list of data to display. ª Uses standard SQL. ª Note 4 columns displayed. ª First column is the one to store in the data table. Properties Name Customer. ID Control. Source Customer. ID Format Decimal. Places Auto Input. Mask Row. Source/Type Table/Query Row. Source SELECT. . . Column. Count 4 Column. Heads No Column. Widths. . . Bound. Column 1 SELECT Customer. ID, Customer. Last. Name, Customer. First. Name, Customer. Phone FROM Customer ORDER BY Customer. Last. Name; 36

Combo Box Sources ² Microsoft Access supports three methods: ª Fixed list. ª Query from a table. ª Defined function. ² With some systems (e. g. , Visual Basic), you write code to generate each list entry. ² You might use a fixed list for simple lists like “male”, “female”, “unknown”. ² It is better to query from a table, even for simple lists. ² Useful feature of list combo box. ª The Row Source property is a text string. ª This string can be generated by code. ª List entries can be changed in response to user actions. ² Programmed function. ª For straightforward cases, it is easier to use a fixed list and just change the text. ª More complex cases, you can write a subroutine that generates the list choices following a specific format. ª Use a one column table. ª Easier to add to a table than to change a combo box. 37

Controls on Forms (Complex) Calendar Common Tab Grid Tab Calendar Gauge Slider Grid Spin Box Additional Purchase Create your own (C++) Gauge Slider Spin box Note: Calendar control was removed in Office 2010! Date picker is semi-automatic with date text boxes. 38

Charts ² Build a query that generates the data to be graphed. ª Numeric data © Individual series © Aggregate data Sale 1 Animal Merchandise Sale 2 Animal Merchandise Sale 3 Animal Merchandise ª Labels ª Columns to link to form. ª Summary chart--unlinked. ² Insert chart. ª Set chart type. ª Set up data and labels. ª Set chart properties. ª Verify size. Total Sales Merchandise Animals 39

Multiple Forms Sale Edit Customer First. Name: Mary Last. Name: Jones Address: 123 Oaxaca Ave. Animals Purchased Customer First. Name: Mary Last. Name: Jones Address: 123 Oaxaca Ave. City: Los Angeles Merchandise Zip. Code: 90086 Gender: Female Age: 20 Account. Balance: $150 40

Multiple Forms Animal Using data on other forms The forms object collection Forms![Form. Name]![Control] Subtotals and subforms The form property Forms![Main. Form]![Sub. Form]. Form![C ontrol] Multi-page v Separate forms Same recordset Screen size Side-by-side Animal. ID Sale =Animal. ID from Animal form Items. Sold ------Subtotal=Sum(Price*Quantity) =Forms!Sale!Items. Sold. Form!Subtotal Tax Order. Total =Subtotal*[Tax. Rate] =Subtotal+Tax 41

Integrity ² Avoid relying on forms ª Set integrity conditions in table definitions ª Be sure to set referential integrity (relationships) ² Use forms to make it easy to enter quality data ª ª ª ª Combo/list boxes Menus Pop-up forms Ties to related forms Data transfer across forms Computations Error checking & trapping ² Controls ª Security rights ª Data formats © Data entry © Round-off ª Selectivity © Visible © Enabled & Locked © Example: no production change after item is sold. ² User assistance ª ª Tool tips Status bar Menu Help--context sensitive 42

Direct Manipulation of Objects Group/ Adoptions Current Choices Customer Bird Cat Tabby Dog Fish Mammal Reptile Spider Lab A graphical approach. Minimize data entry. Drag and drop objects (blue arrows). 43

Creating a Graphical Approach ² Get the hardware. ª ª ª Images: Scanners Sound: Microphone and Sound card Video: Camera and capture card Lots of disk space. High speed processors. ² Add an object column to your table definition. ² Design the screens. ª ª ª Be creative. Get user input. Make the user’s job easier. Avoid using graphics just for show. Double-click Drag-and-drop ² Programming! 44

Design Problems of Many-to-Many Customers Customer. ID Last. Name First. Name Phone Address City State ZIPCode 1 Sale. Customers * Sale. ID Customer. ID * 1 Sale. Employees * Employees Employee. ID Last. Name First. Name Phone Address City State ZIPCode Sale. ID Employee. ID Sales Sale. ID Sale. Date 1 Sale. Items * Sale. ID Item. ID Quantity Sale. Price 1 * Items Item. ID Description List. Price * 1 More flexible? Each sale can have many customers. Each sale can involve many employees. 51

Design Options: Multiple Sections 52

Report Design ² Report usage/user needs. ² Report layout choices. ª Tabular ª Columns/Subgroups ª Charts/graphs ² ² ² ² Paper sizes. Printer constraints. How often is it generated? Events that trigger report? How large is the report? Number of copies? Colors? ² Security controls ª ª ª Distribution list Unique numbering Concealed/non-printed data Secured printers Transmission limits Print queue controls ² Output concerns ª Typefaces © Readability © Size © User disabilities ª OCR needs 53

Terminology ² Page Layout Facing pages (portrait) ª Landscape v. portrait ª Margins ª Gutter (binding space) ² Typefaces ª ª Serif (Times New Roman) Sans-serif (Arial) Ornamental Fixed width gutter margins Landscape ² Font size ª common: 10 - 12 point ª 72 points approx. 1 inch ª pica (1/6 inch) (12 points) Alignment marks for color separations. 54

Report Types: Tabular 55

Report Types: Labels Dwight Parrish 9904 Plum Springs Road Worcester, MA 01613 Dwight Logan 1760 Clearview Street Boston, MA 02109 David Sims 6623 Glenview Drive Boston, MA 02116 Hershel Keen 8124 Industrial Drive Nashua, NH 03060 Reva Kidd 5594 Halltown Road Bangor, ME 04401 Dan Kennedy 3108 Troon Court Burlington, VT 05401 Sharon Sexton 2551 Elementary Drive Barre, VT 05641 Kelly Moore 6116 Clearview Street Middlebury, VT 05753 Cassy Tuck 7977 Fairways Drive Clinton, NJ 07015 56

Report Types Column with groups. 57

Report Layout Report Header Page Header Group Header 1 Group Header 2. . . Detail. . . Group Footer 2 Group Footer 1 Page Footer Report Footer 58

Report Layout/Common Use ² 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. ² 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. ² Detail ª Innermost data. 59

Report Computations ² Query ª Same row computations. ª Extended=Price*Quantity ² Report ª Group subtotals. ª Page and report totals. ª Mixed, e. g. , commission = rate * total ª Scope depends on location © Group footer: subtotal © Page footer: page total © Report footer: report total 60

Report Graphs ² Graphs ª Separate query. ª 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. 61

Report Layout/Groups Customer(C#, Name, …) Order(O#, C#, Odate, …) Order. Item(O#, Item#, Qty, …) Often use groups/breaks for one-to-many relationships. Use a query to join all necessary tables. Can include all columns. Use query to create computed columns (e. g. , Extended: Price*Quantity). Avoid creating aggregates or subtotals in the query. Report of Orders Group 1: Customer H 1: Customer name, address, … Group 2: Order H 2: Order#, Odate, Salesperson. Detail: Item#, Qty, Extended F 2: Order total: Sum(Extended) F 1: Customer total orders: Rpt footer: graph orders by customer Each one-to-many relationship becomes a new subgroup. 65

Report Graph for Group 66

Appendix: Oracle PL/SQL: Data Types ² Primary Data Types ª NUMBER(precision, scale) © precision: Number of digits © scale: Round-off point © NUMBER(7, 4): 123. 4567 ª INTEGER © Default: NUMBER(4) ª NCHAR © Fixed length string ª NVARCHAR 2 © Unicode Variable length string ª LONG, LONG RAW © Binary data ª DATE ª BOOLEAN © Yes/No 67

Appendix: Oracle PL/SQL Structure CREATE OR REPLACE PACKAGE my. Package AS PROCEDURE my. Procedure(old. Project. ID IN NUMBER); END my. Package; CREATE OR REPLACE PACKAGE BODY my. Package AS DECLARE my. Global. Var NUMBER; PROCEDURE my. Procedure(old. Project. ID IN NUMBER) IS DELCARE my. Local. Var NUMBER; BEGIN my. Local. Var : = old. Project. ID; IF END IF COMMIT; END my. Procedure; End my. Package; 68

Appendix: PL/SQL Operators 69

Appendix: PL/SQL IF-THEN-ELSEIF DECLARE X NUMBER(10, 2); BEGIN -- retrieve the balance IF (BALANCE > 0) THEN X= BALANCE*1. 10; ELSE X = 0. 0; END IF; Watch the semicolons! END; Use ELSEIF for case statements. IF (ACCOUNT = ‘P’) THEN -- do personal accounts ELSEIF (ACCOUNT = ‘C’) THEN -- do corporate accounts ELSEIF (ACCOUNT = ‘S’) THEN -- do small business ELSE -- handle error END IF; 70

Appendix: PL/SQL Loops (Start statement) LOOP … WHILE (condition) LOOP EXIT; … EXIT WHEN (condition); END LOOP; … FOR (variable) IN low. . . high LOOP END LOOP; … END LOOP; 71

Appendix: Procedures or Subroutines PROCEDURE Drop. Old. Accounts (Cut. Date DATE) IS -- local variables are defined here BEGIN -- First copy the data to a backup table INSERT INTO Old. Accounts SELECT * FROM Account WHERE Account. ID NOT IN (SELECT Account. ID FROM Order WHERE Odate > Cut. Date); -- Copy additional tables… -- Delete from Account automatically cascades to others DELETE FROM Account WHERE Account. ID NOT IN (SELECT Account. ID FROM Order WHERE Odate > Cut. Date); END Drop. Old. Accounts; 72

Appendix: SQL Cursors DECLARE CURSOR c 1 IS SELECT Name, Salary, Date. Hired FROM Employee; var. Total Employee. Salary%TYPE; BEGIN var. Total = 0; OPEN c 1; FOR rec. Emp in c 1 LOOP var. Total : = var. Total + rec. Emp. Salary; END LOOP; CLOSE c 1; -- Now do something with the var. Total END; 73

Appendix: Error Handling PROCEDURE my. Proc ( ) IS DECLARE -- declare all local variables BEGIN -- SQL statements here EXCEPTION WHEN OTHERS THEN -- you can specify a particular error -- but OTHERS captures all errors -- PL/SQL code to execute if an error arises END my. Proc; 74