Transcript Database Management Systems

Database Management Systems
Chapter 6
Forms and Reports
Jerry Post
Copyright © 2003
1
D
A
T
A
B
A
S
E
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
2
D
A
T
A
B
A
S
E
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
 Art to enhance, graphics
 Sound
 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
3
D
A
T
A
B
A
S
E
Windows Interface Standards
 The Windows Interface: An
Application Design Guide
(Microsoft)
 Navigation and Choices
 Mouse, Icons
 Keyboard, Short-cuts
 Menus
 Selections from a list
 Single
 Contiguous Multiple
 Disjoint Multiple
 Manipulation
 Activation
 Drag and Drop
 Feedback
 Progress indicators and
status gauges
 Flashing
 Tool tips
 Status bar
 3-D controls
 Message boxes
 Focus
 Outline box
 Cursor
4
D
A
T
A
B
A
S
E
Windows Interface
 Window components
 Frame (sizing)
 Title bar
 Control-menu box
 Buttons
 Minimize
 Maximize
 Close
 Scroll box (thumb)
 Scroll bar
5
D
A
T
A
B
A
S
E
Windows Menus
 Menus
 Drop-down
 Short Cut Keys
 Mnemonic character
 Pop-up (as needed)
6
D
A
T
A
B
A
S
E
Message Box (A Simple Form)
 Message Boxes





Title
Message
Simple buttons
Icons
Modal (required)
7
D
A
T
A
B
A
S
E
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
8
D
A
T
A
B
A
S
E
Form Layout
 Types of Forms




Tabular
Single Row
Sub-forms (one-to-many)
Switchboard
 Controls
 Form Properties
 Form Events
Form
Order
Items
11
7
13
Dog
Dog
Cat
5
1
2
9
D
A
T
A
B
A
S
E
Tabular Form
 Works best for single table.
 Designer can control data
entry sequence.
 Probably include buttons for
sorting.
10
D
A
T
A
B
A
S
E
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.
11
D
A
T
A
B
A
S
E
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).
12
D
A
T
A
B
A
S
E
Switchboard Form
 Blank Form
 Graphics/Picture/Backgroun
d
 Identify User
 Choose Task.
13
D
A
T
A
B
A
S
E
Menu Design
Main Menu
Customer Information
1.
Setup Choices
Daily Sales Reports
2.
Data Input
Friday Sales Meeting
3.
Print Reports
Monthly Customer Letters
4.
DOS Utilities
5.
Backups
Hard to understand
Exit
Organized by user tasks.
14
D
A
T
A
B
A
S
E
Menus
 Consistency
 With operating environment
 Within project
 Pull-down






Name, Action
Shortcut keys
Access keys (&File, File)
Breaks/groups (-)
Dimmed option
Check mark
 Pop-up





Miniature form
Tied to location/pointer
Right-mouse button
Attribute settings
Modal (keeps focus) or not
 Submenus (>)
 Logical groupings
 Tradeoff: length v depth
 Form indicator (…)
15
D
A
T
A
B
A
S
E
Queries
 Queries are used to
automatically look up data.
 e.g., Customer name
 e.g., Product description
 Be very careful when using
queries.
 Each form should store data
in only one table.
 For multiple tables, use a
subform or separate forms.
 Usually Lock the look up
data so it cannot be
changed accidentally.
16
D
A
T
A
B
A
S
E
Form Query Example
 Clerk enters a CustomerID.
 Stored in the Order table.
 Query joins Sale and
Customer.
 Automatically matches the
CustomerID.
 Matching name is displayed
on the form.
 Do not include the join column
(CustomerID) from the look up
table (Customer).
Customer Order
SaleID 1234
Date 7/25/01
CustomerID 17
Carly
Embry
17
D
A
T
A
B
A
S
E
Form Query: Underlying Tables
Customer Order
SaleID 1234
Date 7/25/01
CustomerID 17
Carly
Embry
Data
entry
Data
display
Customer
Sale
SaleID
1232
1233
1234
CustomerID
23
74
17
Sdate
7/24/01
7/24/01
7/25/01
Query
Join
CustomerID
15
16
17
First
Last
Connie Fisher
Rosie Wade
Carly
Embry
18
D
A
T
A
B
A
S
E
Form Properties (Some)
 Data
 Base Table / Query
 Filters
 Sort
 Integrity
 Edits
 Additions, Deletions
 Locks
 Other
 Format








Caption
Scroll Bars
Record Selectors
Navigation Buttons
Size and Centering
Background/Pictures
Colors
Tab Order
 Pop-up menus
 Menu Bar
 Help
19
D
A
T
A
B
A
S
E
Controls on Forms (Basic)
Label
Drop down list
or combo box
Text box
Last Name
Clothing
Shoes
Electronics
Country
Payment Method
Credit Card
Check
Cash
Option button
List box
Check box
Options
x Gift wrap
x Gift card
Monogram
Sales
Command
button
20
D
A
T
A
B
A
S
E
Pictures
 Background pictures




Unbound, unchanging.
Stored with the form.
Keep edit screen readable.
Sizing (zoom, scale, clip).
 Pictures stored as data
 Bound to a data column.
 Define column as object.
 Tie to scanner or graphics
package through OLE.
 Beware of data size
Employee
Name: Che Zhang
ID: 3354
Phone: 222-111-1524
...
Photo:
 Resolution
 Number of colors
 User machine capabilities.
21
D
A
T
A
B
A
S
E
Basic Controls
Label
Text Box
Option Group
(single response)
Command
Button
Combo Box
(click arrow to open)
List Box
(always open)
22
D
A
T
A
B
A
S
E
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.
23
D
A
T
A
B
A
S
E
Combo Box
 ControlSource sets the
column to receive the choice
(in the Sale table)
 RowSource 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
CustomerID
ControlSource
CustomerID
Format
DecimalPlaces Auto
InputMask
RowSource/Type Table/Query
RowSource
SELECT . . .
ColumnCount
4
ColumnHeads
No
ColumnWidths
...
BoundColumn
1
SELECT Customer.CustomerID,
Customer.LastName,
Customer.FirstName,
Customer.Phone FROM Customer
ORDER BY Customer.LastName;
24
D
A
T
A
B
A
S
E
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.
 Use a one column table.
 Easier to add to a table than
to change a combo box.
 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.
25
D
A
T
A
B
A
S
E
Controls on Forms (Complex)
Calendar
 Common






Tab
Tab
Grid
Calendar
Gauge
Grid
Slider
Spin Box
 Additional
 Purchase
 Create your own (C++)
Gauge
Slider
Spin box
26
D
A
T
A
B
A
S
E
Charts
Sale 1
 Build a query that generates
the data to be graphed.
Sale 2
 Numeric data
 Individual series
 Aggregate data
Sale 3
Animal
Animal
Animal
Merchandise
Merchandise
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
27
D
A
T
A
B
A
S
E
Multiple Forms
Sale
Edit
Customer
FirstName: Mary
LastName: Jones
Address: 123 Oaxaca Ave.
Customer
FirstName: Mary
LastName: Jones
Animals Purchased
Address: 123 Oaxaca Ave.
City: Los Angeles
Merchandise
ZipCode: 90086
Gender: Female
Age: 20
AccountBalance: $150
28
D
A
T
A
B
A
S
E
Multiple Forms
Animal
 Using data on other forms
 The forms object collection
 Forms![FormName]![Control]
AnimalID
 Subtotals and subforms
 The form property
 Forms![MainForm]![SubForm].
Form![Control]
Sale
=AnimalID from Animal form
 Multi-page v Separate forms
 Same recordset
 Screen size
 Side-by-side
ItemsSold
------------Subtotal=Sum(Price*Quantity)
=Forms!Sale!ItemsSold.Form!Subtotal
Subtotal
Tax
OrderTotal
=Subtotal*[TaxRate]
=Subtotal+Tax
29
D
A
T
A
B
A
S
E
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
30
D
A
T
A
B
A
S
E
 Design Standards
Large Projects
 Templates
Customer Order
 Colors, layout
 Titles
 Actions, common buttons
Assembly
 Naming convention is crucial
Order
form
 Forms
 Controls
 Event procedures
 Variables
 Team Coordination
 Menu design
 Within a form/standards
 Across an application
 Event / action diagrams
Switchboard
form
Assembly
form
Order form
Item#
item not
available
Backorder
Notice
& Form
Customer#
 State diagram
 Scenario diagram/messages
large
customer
Customer
Discount
31
D
A
T
A
B
A
S
E
Objects
 Scenario diagrams




 Messages are usually
initiated by calling exposed
functions in an object
 Data can be passed directly,
or made available by
exposing properties
Objects
Properties
Events
Messages
Message: compute
discount using
Customer ID
& Order size
Market
Pricing
Object
Customer
Order object
Message: discount pct
32
D
A
T
A
B
A
S
E
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
33
D
A
T
A
B
A
S
E
Direct Manipulation of Objects
Kennel/
Orders
Current Choices
Customer

Bird
Cat
Tabby
Dog
Fish
Mammal
Reptile
Spider

Brown Lab
 A graphical approach.
 Minimize data entry.
 Drag and drop objects (blue arrows).
34
D
A
T
A
B
A
S
E
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!
35
D
A
T
A
B
A
S
E
Oracle Forms
Use List of Values (LOV) instead of select boxes.
36
D
A
T
A
B
A
S
E
Oracle Forms Designer
37
D
A
T
A
B
A
S
E
Oracle Forms: Sales
Oracle provides
minimal support for
updateable queries,
so several items are
grayed out to indicate
they cannot be
changed here.
38
D
A
T
A
B
A
S
E
Oracle Forms: Sales Design
Two new
data blocks
are used for
the
repeating
sections.
39
D
A
T
A
B
A
S
E
Oracle Forms Design Hints
 Displaying non-updateable data from other tables is tricky.
In Master/Sale set:
 DML Data Target Type = Table
 DML Data Target Name = Sale
 For SaleID, set PrimaryKey = Yes
 Add the other tables
 Query Data Source Type = Table
 Query Data Source Name (parentheses are critical!)
(SELECT Sale.columns, Customer.Columns, Employee.Columns
FROM Sale, Customer, Employee
WHERE (Sale.CustomerID = Customer.CustomerID)
AND (Sale.EmployeeID = Employee.EmployeeID))
40
D
A
T
A
B
A
S
E
Oracle Forms Hints
Add non-updateable columns by hand. Use aliases in the query to
ensure all column names are unique. Then set properties:
General – Name:
cLastName
Functional – Enabled
No (optional but clearer to the user)
Database – Database Item
Yes
Database – Column Name
cLastName
Database – Query Only
Yes
Database – Insert Allowed
No
Database – Update Allowed No
41
D
A
T
A
B
A
S
E
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
42
D
A
T
A
B
A
S
E
Terminology
 Page Layout
Facing pages (portrait)
 Landscape v. portrait
 Margins
 Gutter (binding space)
 Typefaces
Serif (Times New Roman)
Sans-serif (Arial)
Ornamental
Fixed width
 Font size
 common: 10 - 12 point
 72 points approx. 1 inch
 pica (1/6 inch) (12 points)
gutter
margins
Landscape



Alignment marks for
color separations.
43
D
A
T
A
B
A
S
E
Report Types: Tabular
44
D
A
T
A
B
A
S
E
Report Types: Labels
45
D
A
T
A
B
A
S
E
Report Types
 Column.
 Column with groups.
46
D
A
T
A
B
A
S
E
Report Layout
 Report Header
 Page Header
 Group Header1
 Group Header2
 ...
 Detail
 ...
 Group Footer2
 Group Footer1
 Page Footer
 Report Footer
47
D
A
T
A
B
A
S
E
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.
48
D
A
T
A
B
A
S
E
Report Layout/Groups
 Often use groups/breaks for  Customer(C#, Name, …)
one-to-many relationships.  Order(O#, C#, Odate, …)
 Use a query to join all
 OrderItem(O#, Item#, Qty, …)
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.
 Each one-to-many
relationship becomes a new
subgroup.
Report of Orders
Group1: Customer
H1: Customer name, address, …
Group2: Order
H2: Order#, Odate, Salesperson.
Detail: Item#, Qty, Extended
F2: Order total: Sum(Extended)
F1: Customer total orders:
Rpt footer: graph orders by customer
49
D
A
T
A
B
A
S
E
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
50
D
A
T
A
B
A
S
E
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.
51
D
A
T
A
B
A
S
E
Report Graph for Group
52
D
A
T
A
B
A
S
E
Oracle Report Writer: Preview
53
D
A
T
A
B
A
S
E
Oracle Report Writer: Design
54
D
A
T
A
B
A
S
E
Oracle Reports: Data View
The data view can be used to
create reports with complex data
structures. It is primarily used for
master/detail reports.
In this example, each supplier can
be sent many purchase orders,
which each contain many items
being ordered. The report can
produce group breaks on all three
sections.
55
D
A
T
A
B
A
S
E
Application Features








Application organization
Menu
Toolbar
Help
Transactions
Improving forms
Customized reports
Distributing Applications
File Edit Help
Customer
Report
File Edit Help
File Edit Help File Edit Help
Switchboard
Sales
Report
56
D
A
T
A
B
A
S
E
Application Design
 Customer Form
 Order Form
 Bad design:
Customer
Order
1592
Jane Doe
333 Elm St.
Customer:
1592 Jane Doe
333 Elm St.
 Enter data twice.
 Poor design:
 Memorize data (ID) on one
form to enter on second.
 Better design:
 Automatically transfer data
across forms.
57
D
A
T
A
B
A
S
E
Application Importance
 User interface
Make users’ jobs easier.
Tie input forms and reports.
Automate basic tasks
Tie to external data
collection devices.
 Help system.




 Ensure data integrity





Validate data.
Perform computations.
Verify totals.
Control user access.
Maintain related
transactions.
 Backup and recovery.
 Decision Support
 Monitoring of events.
 Analysis, Graphs, Reports.
 Statistical analysis and
optimization.
 Forecasts and simulation.
 Linking to other software.
 Expert Systems &
Intelligence
 Logic and forward chaining.
 Analysis and decisions in
code.
 Databases of cases,
situations and solutions.
58
D
A
T
A
B
A
S
E
Application Organization
 Organized by user needs.
 Identify user.
 Outline tasks.
 Organize forms and reports.
 Direct users to tasks.
 Potential drawbacks
 Too many layers makes it
difficult for users to find
anything.
 Poor organization confuses
users and requires
additional support and
training.
 Build forms and reports.
 Start with a core concept.
 Identify most important
features. Get them correct.
 Add features, forms and
reports. Issue application
updates--number and date!
 Use menu stubs for
incomplete and future work.
 Make them invisible to the
user with the Visible
property.
 Be sure they are disabled.
59
D
A
T
A
B
A
S
E
Application Structure
Forms and Reports Front end
Visual Basic
Internet
Oracle Forms
Database
Oracle
SQL Server
DB2
Access
Back end
Middle Tier
(Optional)
If x > 10,000 Then
Else
Business logic
End If
Rules
60
D
A
T
A
B
A
S
E
User Orientation
 Database application is a model
of the organization.
 Applications based on user jobs.
 Flexibility and user control.
 Application organization
 User tasks.
 User control over sequence.
 Forms
 Minimize user entry.
 Anticipation.
 Reports
 Easy access from forms.
 User selection of scope and
conditions or filters.
61
D
A
T
A
B
A
S
E
Initial Menu / Switchboard
 Starting point for users.
 Identify the user.
 From network if possible.
 Separate log in if needed.
 Customized for users.
 Hide restricted options.
 Different forms as needed.
 Avoid cluttered screens.
 Use graphics and color to
enhance the presentation.
 Limit the number of options.
62
D
A
T
A
B
A
S
E
Switchboard Uses
 Acts as a directory for the application.
 Identifies users.
 Contains startup and shutdown code.
 Can preload forms in background.
 Make them invisible.
 Speed up later usage.
 Can initiate transaction and security logs.
 Can establish network connections.
 Contains copyright and usage notes.
63
D
A
T
A
B
A
S
E
Sally’s Pet Store: Poor Organization
Order
Merchandise
Item
Receive
Merchandise
Item
What is wrong?
Focus needs to be at higher level
(Order, Receipt, Sale); not Item.A
Sell
Merchandise
Item
Get
Customer
Data
You cannot go from Order to Receipt.
You cannot go from Receipt to Sale.
You need to get customer data before
recording the sale.
64
D
A
T
A
B
A
S
E
Sally’s Pet Store: Better Organization
Supplier
Customer
Orders
Receipt
Sale
Inventory
Items
More links--usually as buttons.
Separate sales from orders,
except for special orders.
65
D
A
T
A
B
A
S
E
Sally’s Pet Store: Initial VTOC
Sales
Sale Animal
Animals
Sale Merchandise
Customers
Animal Health
Animal Genealogy
Suppliers
Purchase Animals
Purchase Merchandise
Customer Receipts
Supplier Payments
Inventory
Sales Report
Accounting
Cash Flow
Marketing
Accounts Payable
Employees
Accounts Receivable
66
D
A
T
A
B
A
S
E
Menus
 Why a custom menu?




Limit user actions.
Simplify user interface.
Add custom actions.
Menus can be activated by
keystrokes.
 Accessibility
 Touch-typists and
heads-down data entry.
 Sometimes need different
menus for each form.
File Help
Contents
Search
About Rolling Thunder
File Edit
Help
Add Customer
Delete Customer Ctrl+D
Modify Customer Data
67
D
A
T
A
B
A
S
E
Creating Menus
 View | Toolbars | Customize
 Drag and Drop
 Multilevel menu.
 Sublevels/hierarchy.
 Each level is a separate menu with its
own name.
 Menu choices
 Each entry has a name.
 Access key: & (e.g., &File).
 Status Bar Text
 Actions
 Submenu.
 Run any code.
68
D
A
T
A
B
A
S
E
Toolbars
Print
 Why toolbars?
 Single click for complex
actions.
 Commands available across
Switchboard
the application / shortcuts.
 Position and customization
by user.
 Toolbar components
 Button
 Text
 Icon/graphic (bitmap)
 Tool Tip
 Status Bar description
 Action
·Identify report
·Ask for single or
multiple pages.
·Preview or print.
Weekly Sales Analysis
Build graphs
Print reports
Export data to spreadsheet
69
D
A
T
A
B
A
S
E
Menus and Toolbars
70
D
A
T
A
B
A
S
E
Creating Toolbars
 View | Toolbars | New
 Customizing
 Add new button.
Select from DBMS list.
Bring up query/form/report.
Run code.
 Change icon.
Modify existing icon.
Replace icon.
Create your own icon and paste it on the button.
Place text label on button.
 Tool tips are vital.
 Status bar for description.
71
D
A
T
A
B
A
S
E
Icons
 16 by 16 pixels
 16 colors
 Bright and shaded
 Dither to mix colors
 Outline in black
72
D
A
T
A
B
A
S
E
Help
 On-line help replaces manuals
 Context sensitive:
 Pressing F1 key provides
information on topic with
current focus
 Hypertext links to related topics
 Sequential topics
 Descriptions
 Examples
 Definitions / Glossary
 Contents / overview
 Index / keywords
 Full-text search
 Windows 95 & Win-NT
Sally’s Pet Store--Contents
Copyright Notice
The Firm
Introduction
Processes
Entering Data
Sales
Animal Health
Breeds (and other terms)
74
D
A
T
A
B
A
S
E
HTML Help
 Get the Microsoft HTML Help Workshop:
 http://msdn.microsoft.com/library/tools/htmlhelp/
 Create each of the following
 Help project files
 Use separate directory
 HTML topic files
 Standard HTML with some additions for keywords
 Topic Header and Text File
 Graphics and multimedia
 Avoid monster sizes
 Contents files
 Can auto-generate from heading tags (<H1>, <H2>, …)
 Index files
 Use Help workshop to set keywords within each topic
75
D
A
T
A
B
A
S
E
HTML Help Workshop
76
D
A
T
A
B
A
S
E
HTML Project Hints
 Project Options
 Project Title
 Default file (first page)
 Can create new files with File - New
 Be sure to Add/Remove Topic files to project list
 Edit – Compiler Information to add keywords to HTML file
 Concentrate on creating useful help content
 On large projects, hire/train someone to manage help
 Add useful features
 Keep content up to date
 Manage/organize all the files
77
D
A
T
A
B
A
S
E
Context-Sensitive Help
Set the help file name in the form properties.
Set the topic number (Context Id) for each form or control.
78
D
A
T
A
B
A
S
E
Context Sensitive HTML Help
 Create a Topic file for pop-up
topics
.topic Filename1
Description
.topic Filename2
Description
 Create a header file to link
the topic names to numbers
 Use HTML API to set the
filenames
#define Filename1 10000
#define Filename2 20000
…
…
79
D
A
T
A
B
A
S
E
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)
 CHAR
 Fixed length string
 VARCHAR2
 Variable length string
 LONG, LONG RAW
 Binary data
 DATE
 BOOLEAN
 Yes/No
80
D
A
T
A
B
A
S
E
Appendix: Oracle PL/SQL Structure
CREATE OR REPLACE PACKAGE myPackage AS
PROCEDURE myProcedure(oldProjectID IN NUMBER);
END myPackage;
CREATE OR REPLACE PACKAGE BODY myPackage AS
DECLARE
myGlobalVar NUMBER;
PROCEDURE myProcedure(oldProjectID IN NUMBER) IS
DELCARE
myLocalVar NUMBER;
BEGIN
myLocalVar := oldProjectID;
IF
END IF
COMMIT;
END myProcedure;
End myPackage;
81
D
A
T
A
B
A
S
E
Appendix: PL/SQL Operators
Operator
**
*, /
+, -, ||
=, !=, <, >, <=, >=,
IS NULL, LIKE, BETWEEN , IN
NOT, AND, OR
Operation
Exponentiation
Multiply, Divide
Add, Subtract, Concatenate
Comparison
Logical operators
82
D
A
T
A
B
A
S
E
Appendix: PL/SQL IF-THEN-ELSE-ELSEIF
DECLARE
X NUMBER(10,2);
BEGIN
-- retrieve the balance
IF (BALANCE > 0) THEN
X = BALANCE*1.10;
ELSE
X = 0.0;
END IF;
END;
Watch the semicolons!
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;
83
D
A
T
A
B
A
S
E
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;
84
D
A
T
A
B
A
S
E
Appendix: Procedures or Subroutines
PROCEDURE DropOldAccounts (CutDate DATE) IS
-- local variables are defined here
BEGIN
-- First copy the data to a backup table
INSERT INTO OldAccounts
SELECT * FROM Account WHERE AccountID NOT IN
(SELECT AccountID FROM Order WHERE
Odate > CutDate);
-- Copy additional tables…
-- Delete from Account automatically cascades to others
DELETE FROM Account WHERE AccountID NOT IN
(SELECT AccountID FROM Order WHERE
Odate > CutDate);
END DropOldAccounts;
85
D
A
T
A
B
A
S
E
Appendix: SQL Cursors
DECLARE
CURSOR c1 IS
SELECT Name, Salary, DateHired FROM Employee;
varTotal Employee.Salary%TYPE;
BEGIN
varTotal = 0;
OPEN c1;
FOR recEmp in c1 LOOP
varTotal := varTotal + recEmp.Salary;
END LOOP;
CLOSE c1;
-- Now do something with the varTotal
END;
86
D
A
T
A
B
A
S
E
Appendix: Error Handling
PROCEDURE myProc ( ) 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 myProc;
87