db application design (1) is 240 – database management lecture #16 – 2004-04-13 prof. m. e....

DB Application Design (1)

IS 240 – Database Management

Lecture #16 – 2004-04-13Prof. M. E. Kabay, PhD, CISSP

Norwich University

mkabay@norwich.edu

2 Copyright © 2004 M. E. Kabay. All rights reserved.

Topics

Functions of DB ApplicationViewsUser Interface DesignHomework

3 Copyright © 2004 M. E. Kabay. All rights reserved.

Functions of DB Application

OLAP: OnLine

Analytical Processing –

decision support SW

OLAP cubes: multidimensional

database like 3D (or nD) spreadsheet for

seeing many different views of data

4 Copyright © 2004 M. E. Kabay. All rights reserved.

CRUD

Four basic functions common to all DB

applications CreateReadUpdateDelete

Hence CRUD

5 Copyright © 2004 M. E. Kabay. All rights reserved.

Format/Materialize

Independent aspects of DB dataData content = viewAppearance = materialization or format

Materialize = to design appearance of DB application

6 Copyright © 2004 M. E. Kabay. All rights reserved.

Enforce Constraints

Structural constraintsDomain specificationsUniquenessRelationship constraints

E.g., “no order detail without order header”

Business rulesNon-structural, non-obvious; e.g., ___________________________________

{rule from a student DB}

Think of an

example here.

7 Copyright © 2004 M. E. Kabay. All rights reserved.

Provide Control and Security

Six fundamental goals of information security

ConfidentialityControl or possessionData integrityAuthenticityAvailabilityUtility

Think of examples of

each of these in a database

application

8 Copyright © 2004 M. E. Kabay. All rights reserved.

Execute Application Logic

Applications execute business logicActions in one part of a DB usually imply

effects on other partsE.g., in hospital application

Issuing prescription in hospital pharmacy implies modifying current inventory

Reaching re-order point initiates order to supplier

Order to supplier implies changes to current budget line-item

9 Copyright © 2004 M. E. Kabay. All rights reserved.

Topics

Functions of DB ApplicationViewsUser Interface DesignHomework

10 Copyright © 2004 M. E. Kabay. All rights reserved.

Views

Structured list of data attributes Projection or subset

From complete list of available entitiesOr semantic objects Defined in data model

Can be materialized or formatted as On-line form or Hard-copy report orGraphical representation

StaticDynamic

11 Copyright © 2004 M. E. Kabay. All rights reserved.

View: Structured ListCustomer view: Structured List of Attributes

for an Art Gallery DB [Figure 10-4(a), p. 263]

CUSTOMER.Name

CUSTOMER.AreaCode

CUSTOMER.LocalNumber

TRANSACTION.PurchaseDate

TRANSACTION.SalesPrice . . .

WORK.ARTIST.Name

WORK.Title

WORK.Copy

ARTIST.Name . . .

What do the ellipses

imply?

12 Copyright © 2004 M. E. Kabay. All rights reserved.

Sample Data for Customer View

Jackson, Elizabeth206284-6783

7/15/944,300

Mark TobeyLithograph One10/75

2/5/992,500

Juan MiroPoster 145/250

11/22/9717,850

Juan MiroAwakening Child1/1

Juan MiroMark TobeyDennis Frings

Figure 10-4 (b), p. 263

What is all

this?

13 Copyright © 2004 M. E. Kabay. All rights reserved.

View Create

Create

INSERT INTO CUSTOMER

(CUSTOMER.Name, CUSTOMER.City)

VALUES (NewCust.CUSTOMER.Name, NewCust.CUSTOMER.City)

14 Copyright © 2004 M. E. Kabay. All rights reserved.

View Read

Read

SELECT CUSTOMER.CustomerID, CUSTOMER.Name

FROM CUSTOMER, WORK

WHERE CUSTOMER.CustomerID = WORK.CustomerID

15 Copyright © 2004 M. E. Kabay. All rights reserved.

View Update

Update

INSERT INTO CUSTOMER

(CUSTOMER.Name, CUSTOMER.City)

VALUES (NewCust.CUSTOMER.Name, NewCust.CUSTOMER.City)

16 Copyright © 2004 M. E. Kabay. All rights reserved.

View Delete Cascading deletions depend on relationship

cardinality

??

17 Copyright © 2004 M. E. Kabay. All rights reserved.

Topics

Functions of DB ApplicationViewsUser Interface DesignHomework

18 Copyright © 2004 M. E. Kabay. All rights reserved.

User Interface Design

Form Structure Follows View Structure & User Needs

Structure Should Guide ActionsGood Form Design Can Prevent CatastropheDrop-Down Menus Restrict Choices and

Reduce ErrorsRadio Buttons for Mutually Exclusive OptionsCursor MovementsFunction KeysContext-specific HELP

19 Copyright © 2004 M. E. Kabay. All rights reserved.

Form Structure (1)

No absolute, unambiguous algorithm for determining where to place dataForm structure follows

View structure & User needs

Vary among users Group form elements logically / ergonomically

User should naturally see data as being related to each other

Sequence should reflect natural usage patterns from user point of view

Or among groups of users (functions)

20 Copyright © 2004 M. E. Kabay. All rights reserved.

Form Structure (2)

Consider hospital DB with PATIENT, DOCTOR and PATIENT-DOCTOR tables

Discuss ways you could group information rationally on forms to helpDoctors treating individual patientsA head nurse responsible for

managing an entire floor

What are the issues you would

have to think about when designing the

forms?

21 Copyright © 2004 M. E. Kabay. All rights reserved.

Form Structure (3)

DOCTOR'S FORM

Patient:Name XXXXAge xxBed xx

Status:Date/Time Heart Respiration Temp xxxx xx xx xx xxxx xx xx xx xxxx xx xx xx xxxx xx xx xx

Treatment Schedule:xxxxxxxxxxxxxxxxxxxxxxxxxx

Notes:

22 Copyright © 2004 M. E. Kabay. All rights reserved.

Form Structure (4)

Head Nurse Form

Bed Patient Nurse Next med Current status T H Rxxx xxxxx xxxx xxxxxx xxxxx xxxxx xxxxxxx xxxxx xxxx xxxxxx xxxxx xxxxx xxxxxxx xxxxx xxxx xxxxxx xxxxx xxxxx xxxxxxx xxxxx xxxx xxxxxx xxxxx xxxxx xxxxxxx xxxxx xxxx xxxxxx xxxxx xxxxx xxxx

Nurse Beds CoveredXxxxx xxx xxx xxx xxx xxx xxx xxxXxxxx xxx xxx xxx xxx xxx xxx xxxXxxxx xxx xxx xxx xxx xxx xxx xxxXxxxx xxx xxx xxx xxx xxx xxx xxx

Doctor Pager #Xxxx xxxxXxxx xxxxXxxx xxxxXxxx xxxx

23 Copyright © 2004 M. E. Kabay. All rights reserved.

Structure Should Guide Actions (1)

Design form so that it's easy to do the right thing and hard to make mistakes; e.g.,Highlight fields for visibility where user is

expected to enter dataGray out fields where user is forbidden to

enter or change dataSet field length to imply or limit acceptable

input; e.g., "State" field can be 2 characters only

If possible, use lists of allowed data See later – e.g., drop-down boxes, radio

buttons. . . .

24 Copyright © 2004 M. E. Kabay. All rights reserved.

Structure Should Guide Actions (2)

Can have different variations of forms for different functions; e.g.,DISPLAY form such as the ones discussed

for doctors and for head nurses may mostly show data but allows few changes

Discuss which fields might change in the forms shown in these notes

NEW PATIENT form specifies fields for all the data needed for registration to collect appropriate information

25 Copyright © 2004 M. E. Kabay. All rights reserved.

Good Form Design Can Prevent CatastropheChemical plant in Netherlands

Computer-controlled reaction vesselsOperator entered numerical codes into form

on screen to determine compounds entering vats

1992: data entry errorOperator typed 634 instead of 632 in numerical

code for tank # of 1 of 2 compoundsMixture of wrong chemicals caused massive

explosionPlant destroyed; 3 firemen killed & 11

employees injuredDiscuss how better form design could have

prevented accident

26 Copyright © 2004 M. E. Kabay. All rights reserved.

Drop-Down Menus Restrict Choices and Reduce Errors

In the example of the explosion, a good form design might have shown a choice of allowable combinations instead of allowing any combination including explosive pairs

Drop-down menus are perfect for showing allowed choices; e.g.,

Enter compound #1 Enter compound #2

A

B

C

D

12345

A

C

E

G

12354

27 Copyright © 2004 M. E. Kabay. All rights reserved.

Radio Buttons for Mutually Exclusive Options

Drop-down boxes are useful for data-dependent choices that can change

When there are fixed choices that are mutually exclusive, radio buttons make it intuitively easy for the user to understand the restriction:

Select one of the following meal plans:

21/week

14/week

7/week

Per meal

28 Copyright © 2004 M. E. Kabay. All rights reserved.

Cursor Movements

Make the cursor move from field to field in a way that reflects what the user will be doing

For example, if a doctor tends to fill in information in a particular way, make the ENTER and TAB keys move the cursor to the logically-next field; e.g., Let doctor enter next treatmentENTER moves cursor to next line in list TAB moves cursor to next field (e.g.,

Notes)Be consistent in use of these keys

29 Copyright © 2004 M. E. Kabay. All rights reserved.

Function Keys

All special functions that are assigned to function keys (e.g., ESC, F1 F2 F3. . . ) should be available in drop-down menus

If you define function keys, be absolutely consistent; e.g., if F1 is HELP, it should be HELP in every

form;If ESC moves to the previous (or higher)

form, it should always do so throughout the application

30 Copyright © 2004 M. E. Kabay. All rights reserved.

Context-specific HELPGeneral help (e.g., from F1 key or pulldown

menu)Provides overview of entire formMay have hyperlinks for specific

informationProvides suggestions for additional topicsAllows navigation to an index of help

topicsMay provide find function

Help-cursorAllows user to point to specific part of

formBrings up pop-up help to explain meaning

or choices for particular field or feature

? Pop-up Help

31 Copyright © 2004 M. E. Kabay. All rights reserved.

Homework

Reread Chapter 10, pp 257-274 using the full SURVEY-QUESTION-READ-RECITE-REVIEW phases of SQ3R

Read ahead to complete the chapter for next Thursday’s class

REQUIRED: For Tuesday 20 April for 46 pointsComplete and hand in printed replies to

Group 1 Questions 10.1 through 10.23Remember to include the question itself in

your answer sheets (makes them much more useful for review)

32 Copyright © 2004 M. E. Kabay. All rights reserved.

DISCUSSION