dbmspost-chapter06-401

270

What You Will Learn in This Chapter

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 problems arise if a design always uses one-to-many relationships?•What are the basic roles of reports?•

Chapter Outline

Forms and Reports6Chapter

Introduction, 271Effective Design of Reports and Forms, 272

Human Factors Design, 273Standard Form Controls, 275User Interface—Web Notes, 277User Interface—Events, 278User Interface—Accessibility Issues, 278User Interface—International Environment, 279

Form Layout, 281Tabular Forms, 281Single-Row or Columnar-Forms, 282Subform Forms, 284Startup Forms, 285

Creating Forms, 285Updateable Queries, 286Linked Forms, 287Properties and Controls, 288Controls on Forms, 289Multiple Forms, 292

Direct Manipulation of Graphical Objects, 293

Sally’s Pet Store Example, 294The Internet, 294Complications and Limitations of a Graphical Approach, 295

Database Design Revisited, 296Reports, 297

Report Design, 298Terminology, 299Basic Report Types, 299Charts, 305

Summary, 306Key Terms, 307Review Questions, 307Exercises, 307Web Site References, 310Additional Reading, 310

271Chapter 6: Forms and Reports

A Developer’s View Ariel: Why the concerned look?

Miranda:Well,Ifinallyfiguredouthowtoanswerthosehardquestions.ButI’malittleworried.LotsoftimesIgotanswers,buttheywerewrong.IhavetobereallycarefulwithSQL.

Ariel:Oh,I’msureyou’lldofine.You’realwayscarefulabouttestingyourwork.

Miranda:Isupposeit’llgeteasier.

Ariel:That’sthespirit.Now,areyoufinallyreadytostartbuildingtheapplication?

Miranda:Isuream.Ilookedatsomeoftheinformationaboutformsandreports.Thisisgoingtobeeasy.

Ariel: Really?

Miranda:Sure.Andyouknowthebestpart?AlltheformsandreportsarebasedonSQL.Togettheinitialformsandreports,allIhavetodoisbuildqueriestogetthedataIwant.Thereareevenwizardsthatwillhelpcreatethebasicformsandreports.

Ariel:Ialwaysknewthatsomedaypeoplewouldcallonspiritsagain.

IntroductionHow do users interact with the database?Youneverwanttogiveusersdirectaccess to tables. Imagine thechaos thatwould result ifyouaskauser to enteranOrderID,ItemID,priceandquantitydirectlyintoaSaleItemtable.Formsandreportsareanimportantpartofthedatabaseapplication.Designersusethemtocreateanintegratedapplication,makingiteasierforuserstoperformtheirtasks.Decisionmakersandclericalworkersuseformsandreportsonadailybasis.Yearsagoformswereusedprimarilyasinputdevices,andreportswereusedtodisplayresults.However, asmanagersgainedgreater access toonlinedatabases, formsbecameincreasinglyimportant.Reportsarestillusedforoutputthatwillbedis-tributedorstoredinpaperform.However,formscanbedistributedelectronically,andcandisplayavarietyofoutputs.TheInternet,andspecificallytheWorldWideWeb,isbecominganincreasinglypopularmeansofdistributingdataaselectronicformsandreports.ThesamedesignprinciplesusedfordatabaseformsalsoapplytotheWeb.As summarized inFigure6.1, forms are used to collect data, display results

of queries, display analysis, and perform computations. They are also used asswitchboards, or connectors, to other forms and reports. In sophisticated,Win-dows-basedapplications,aformcanbeusedfordirectmanipulationofobjects.Agraphicalinterfaceenablesuserstodrag-and-dropobjectstoindicatechanges.Withthistypeofform,usersinteractvisuallywithamodelofthefirm.Reportsaretypicallyprintedonpaper,buttheyareincreasinglybeingcreated

fordirectdisplayonthescreen.Reportsareusedtoformatthedataandpresentresultsfromcomplexanalysis.Reportscanbedetailedandcoverseveralpages:anexamplewouldbeadetailedinventoryreport.Alternatively,reportscanpres-entsummarydata,incorporatinggraphsandtotals.Acommonbusinessexample


wouldbeaweeklysalesreportcomparingsalesbydivisionforthepastfewweeks.Thereportwouldgenerallybepresentedgraphicallyandwouldoccupyonepage.At this stage in the project, you need to create all of the forms and reports

neededbytheusers.Fortunately,mostsystemshavewizardstohelpyoucreateroughdraftsofformsandreports.Youwillstillspendconsiderabletimeimprov-ingthelayout,formattingdata,andestablishingaconsistentdesignscheme.But,itisrelativelyeasytocomebacklaterandmodifytheformsandreports.Inmanycases, youwillwant to take an interactive or prototyping approachwhere youobtainfeedbackfromtheuserseveraltimesandimprovethelayoutanddesignateachiteration.

Effective Design of Reports and FormsWhat is the difference between a good form and a bad form? Designingformsandreportshasmuchincommonwithcreatingart.Youstartwithablankpageandchooselayout,colors,anddesignelements.Infact,youmightwanttotakeagraphicsdesignclassorconsultagraphicsdesignertohelpwiththeartisticside.However,youhavetheadditionalburdenofmakingtheformsandreportseasytouse.Youalsohavetoensurethattheyconveycorrectandusefulinformation.Themost important concept to rememberwhendesigning forms and reports istounderstandthattheyaretheprimarycontactwiththeusers.Eachformandre-portmustbetailoredtospecificsituationsandbusinessuses.Forexample,someformswillbeusedforheads-down data entry—wheretouchtypistsconcentrateonenteringdatawithout lookingat thescreen.Otherformspresentexploratoryanalyses,andthedecisionmakerwillwanttoexaminevariousscenarios.Thefea-tures,layout,andcapabilitiesofthesetwotypesofformsareradicallydifferent.Ifyouchoosethewrongdesignfortheuser,theform(orreport)willbevirtuallyuseless.Thekeytoeffectivedesignistodeterminetheneedsoftheuser.Thecatchis

thatusersoftendonotknowwhattheyneed(orwant).Inparticular,theymaynotbeawareof thecapabilitiesand limitationsofamodernDBMS.Asadesigner,youtalkwiththeuserstolearnwhattheywanttoaccomplish.Thenyouuseyourexperiencetoprovidefeaturesthatmaketheformmoreuseful.Justbecarefultofindthefinelinebetweenhelpingtheuserandtryingtosellusersanapplicationtheydonotneed.Researchers inhuman factorshavedeveloped severalguidelines tohelpyou

designforms.Tobegin,allformsandreportswithinanapplication(orevenwithin

Collect dataDisplay query resultsDisplay analyses and computationsSwitchboard for other forms and reportsDirect manipulation of objects Graphics Drag-and-drop

Figure 6.1Basicusesofdatabaseforms.Itisimportanttounderstandtheuseofaform,sinceformsdesignedfordatacollectionwillbedifferentfromthosedesignedtoanalyzedata.


anorganization)shouldbeasconsistentaspossible.Keystrokes,commands,andiconsshouldbeusedforthesamepurposesthroughouttheapplication.Color,lay-out,andstructureshouldbecoordinatedsouserscanunderstandthedataandcon-textonanyformorreport.Basingapplicationsonasetofcommontasksreducesthetimeittakesforuserstolearnnewapplications.TheincreasingimportanceofWeb-basedapplicationssimplifiesdesigntosomeextent,becauseyounowhavealimitedsetoftoolsthatareunderstoodbyalmostallusers.Researchintohuman factors designhasalsoledtoseveralhintsandguidelinesthatdesignersshouldfollowwhenbuildingformsandreports.

Human Factors DesignFigure6.2summarizessomehumanfactorsdesignelementsthatsystemdesign-ersshould incorporate in theirapplications.Withcurrentoperatingsystems, theprimary factor is that theusers—not theprogrammerandnot theapplication—shouldalwayshavecontrol.Forexample,donotexpect(orforce)userstoenterdatainaparticularsequence.Instead,setupthebaseformsandletuserschoosethedataentryorderthatiseasiestforthem.Inthisapproach,theuser’schoices

Human Factors ExamplesUser Control Match user tasks

Respond to user control and eventsUser customization

Consistency Layout, design, and colorsActions

Clarity OrganizationPurposeTerminology

Aesthetics Art to enhanceGraphicsSound

Feedback Methods Visual Text AudioUses Acceptance of input Changes to data Completion of tasks Events / Activation

Forgiveness Anticipation and correction of errorsConfirmationondeleteandupdatesBackup and recovery

Figure 6.2Basichumanfactorsdesignelements.Alldesignsshouldbeevaluatedintermsofthesebasicfeatures.


trigger various events.Your application responds to these events or triggers byperformingcalculations,retrievingorstoringdata,andofferingnewchoices.Also,wheneverpossible,provideoptionsforusercustomization.Manyusers

wanttochangedisplayfeaturessuchascolor,typeface,orsize.Similarly,usershavetheirownpreferencesintermsofsortingresultsandthedatatobeincluded.Boththelayout(designandcolor)andtherequiredactionsshouldbeconsistent

acrossanapplication.Intermsofuseractions,becarefultoensureconsistencyinbasicfeatures,suchaswhethertheusermustpresstheEnterkeyattheendofaninput,whichfunctionkeyinvokestheHelpsystem,howthetabandarrowkeysareused,andtheroleofeachicon.Theseactionsshouldbeconsistentacrosstheentireapplication.Thisconceptseemsobvious,butitcanbechallengingtoimple-ment—particularlywhenmanydesignersandprogrammersarecreatingtheappli-cation.Twopracticeshelpensureconsistency:(1)Atthestartestablishadesignstandardandbasictemplatesforalldesignerstouse,and(2)towardtheendoftheapplicationdevelopmentalwaysgobackandcheckforconsistency.Always strive for clarity. Inmany cases claritymeans keeping the applica-

tionsimpleandwellorganized.Iftheapplicationhasmultipleformsandreports,organizethemaccordingtousertasks.Ithelpstohaveaclearpurposeforanap-plicationandtomakesurethedesignenhancesthatpurpose.Useprecisetermi-nology,avoidjargon,andstickwithtermsthatareusedwithintheorganization.If a company refers to its employees as “Associates,” use that term, insteadof“Employees.”

Aestheticsalsoplayanimportantroleintheuserinterface.Thegoalistousecoloranddesign(andsometimessound)toenhancetheformsandreports.Avoidthebeginner’smistakeofusingdifferentcolorsforeveryformorplacing10dif-ferentfontsonapage.Althoughdesignandartarehighlysubjective,baddesignsareimmediatelyobvioustoothers.Ifyouhaveminimalexperienceindesignaes-thetics,considertakingacourseortwoinartordesign.Ifnothingelse,studyworkdonebyotherstogainideas,totrainyourartisticsensibility,andtostayabreastofcurrenttrends.Rememberthatgraphicsandartareimportant,andtheyprovideanattractiveandfamiliarenvironmentforusers.

Feedback is crucial to most human-computer interactions. People want toknowthatwhentheypressakey,chooseanoption,orselectaniconthecomputerrecognized theiractionand is responding.Typicalusesof feedback includeac-ceptinginput,acknowledgingchangesofdata,highlightingcompletionofatask,or signifyingthestartorcompletionofsomeevent.Severaloptionscanbeusedtoprovidefeedback.Visually,thecursorcanbechanged,textcanbehighlighted,abuttoncanbe“pushedin,”oraboxmaychangecolor.Moredirectformsoffeed-back,suchasdisplayingmessagesonthescreen,canbeusedinmorecomplicatedcases.Somesystemsuseaudiofeedback,playingamusicalthemeorsoundwhentheuserselectsataskorwhenthecomputerfinishesanoperation.Ifyoudecidetouseaudiofeedback,besurethatyougiveusersachoice—somepeopledonotlike“noisy”computers.Ontheotherhand,donotbehastytodiscardtheuseofaudiofeedback—itisparticularlyeffectiveforpeoplewithlowvision.Similarly,audioresponsesareusefulwhenusersneedtofocustheirvisiononanexternaltaskandcannotlookatthecomputerscreen.Humansoccasionallymakemistakesorchangetheirminds.Asadesigner,you

needtounderstandthesepossibilitiesandprovideforthemwithinyourapplica-tion.Inparticular,yourapplicationshouldanticipateandprovideforcorrectionoferrors.Youshouldconfirmdeletionsandmajorupdates—givingusersachance


toverifythechanges,orevenundothem.Finally,youroverallapplicationshouldincludemechanismsforbackupandrecoveryofdata—bothincaseofnaturaldi-sastersandincaseofaccidentaldeletionsorlossofdata.

Standard Form ControlsMost systems use an object-oriented approach to building forms.The standardformisdrawninawindoworWebpagethatcontrolsthesizeandprovidesscrollbarstodisplaysectionsoftheformthatdonotfitonthepage.Somesystemsal-lowyoutocontrolthesepagefeatures,butyoushouldalmostalwaysleavethemalone,so theycontinue towork theway theusersexpect.Today,mostDBMSshaveadoptedarelativelystandardapproachtoforms.Althoughthetoolsandop-tionsaredifferent,thebasicconceptsarethesame.Figure 6.3 shows themost common formcontrols that are supported by all

DBMSs.Labelsarefixedtextthatisdisplayedontheform.Somesystemsattachthelabeltothedatacontrol,butyoucangenerallysetitsfontpropertiesandmoveitaroundonthepage.Atext boxdisplaysdatafromatablecell.Generally,itcanbeeditedornewdatacanbeentered.Thetextboxisadata-boundcontrol,whichmeansthatyouspecifythetableandcolumninthedatabase.Theformprocessorautomaticallyretrievesthedatafromarowanddisplaysthevalueinthetextbox.Anychangesornewdataenteredistransferredbacktothespecifieddatabasecol-umn.Youcansetformatpropertiestocontrolthewaythedataisdisplayed.Youcanalsospecifyan input mask to limit thetypeofdata thatcanbeenteredbytheuser.Forinstance,youcouldspecifythatonlynumberscanbeentered,oryoucanautomaticallyformatdatatoafixedlayout.MostsystemshavesampleinputmaskstohandlephonenumbersandZIPcodes.However,becarefulwithinputmasks.Restrictingtheusertoenteringdataacertainwaymightcauseproblems.Postalcodespresentaclassicexample.Ifyourdataisgoingtocontaininterna-tionaladdresses,youcannotuseaninputmask.PostalcodesoutsideofAmericaincludelettersandhyphensaswellasdigits.Similarproblemsarisewithphone

Last Name

City London

Individual Corporate

Animal InterestsBirdCatDogReptile

xx

LabelText box

Drop down listComboBoxOption or

Radio button

Check box

Figure 6.3Commonformcontrols.AllDBMSshavethesebasiccontrols.Labelsandtextboxesareusedthemost.Roundoptionbuttonsareusedformutuallyexclusiveitems.


numbers.Whenyouhavetoincludecountrycodesandotherextensions,thestan-dardAmericanformattingwillnotwork.

Option buttonsaresometimescalledradiobuttonsbecauseonmanysystemstheyaredrawnasfilledcirclesthatlookliketheknobsonoldradios.Check box-esarealmostalwaysdisplayedassquareboxesusingsometypeofcheckmarktoindicateselection.Youneedtobecarefulwhenchoosingbetweenoptionbuttonsorcheckboxes.Mostsystemsareflexibleandallowyoutouseeitheroneforanytypeofproblem.However,thedesignconventionisthatoptionbuttonsshouldbeusedformutuallyexclusiveevents.Usersknowwhentheyseetheroundbuttonsthattheycanselectonlyoneoftheitems.Theyalsoknowthattheycanselectmul-tipleitemsifthesquarecheckboxesareused.Youfollowthisconventiontoavoidconfusingusers.Optionbuttonsandcheckboxesareusuallydataboundcontrols.Usually,theyareboundtocolumnswithyes/noorbitdatatypes.Onsomesys-tems,theyareboundtointegerdata,whereazerorepresentstheuncheckedstate.

The drop down listissometimescalledacomboboxbecauseitisacombina-tionofatextboxandalistbox.Itisoneofthemorecomplicatedformcontrols,butitisextremelyusefulinrelationaldatabases.Itretrieveslistsofitemsfromatableorquery.Whentheuserpicksanitemfromthelist,thechosenvalue(usu-allyanID)isenteredintothemainformfield.Intheexample,thedatabasehasa table that containsa listof cities that isdisplayed in thedropdown list.Theuserpicks a city and the selectedvalue is entered into the customer table.Themainadvantageinthiscaseisthattheuserdoesnothavetotypethenameofthecity—whicheliminateserrorsandabbreviations,providingmoreconsistentdata.

3958DavidSanchez1160394MaryJones1152223SueBrown113PhoneFirstLastCID

3958DavidSanchez1160394MaryJones1152223SueBrown113PhoneFirstLastCID

Sales Form

Customer Jones, Mary

Customer Table

SaleID 298

Sale Date 6/1/....

1156/1/…298

CIDSaleDate

SaleID

1156/1/…298

CIDSaleDate

SaleID

Sales Table

Display list

Selected value

Figure 6.4Dropdownlistforforeignkeylink.ThevaluesdisplayedinthelistcomefromtheCustomertable.Whentheuserselectsonecustomer,thematchingIDvalueisstoredintheSalestablethatunderliestheSalesform.


Thesetypesoflookuplistsareoftenusedwhenyouwanttolimitthedatathatcanbeenteredbytheuser.Dropdownlistsarealsocommonlyusedonreferentiallinksacrosstables.Fig-

ure6.4illustratestheprocessforatypicalsalesform.RecallthattheSalestablecontainstheCustomerIDcolumn,andhasaforeignkeyrelationshiptotheCus-tomertable.YoucannotexpectthesalesclerksorcustomerstomemorizeIDval-ues.So,youaddadropdownlistthatdisplaysalistofcustomersretrievedfromthecustomertable.Whentheclerkchoosesacustomer,thematchingIDvalueisplacedintotheunderlyingSalestable.Todefinethedropdownlist,youhavetospecifythetableorquerythatwillprovidethelistofdata.Youhavetospecifyadisplaycolumnandakey(ID)column.Youoftencreateaquerytogeneratethedisplaycolumn,suchasappendingfirstandlastnames.YoualsohavetodatabindthedropdownlisttotheCustomerIDcolumninthetargetSalestable.Thisstepensuresthatthechosenvalueisstoredinthepropercolumn.Because relational databases tend to havemany foreign keys, youwill find

manyopportunitiestousedropdownlists.However,inaWebenvironmentwithrelativelyslownetworklinks,dropdownlistscancauseproblems.Itcouldtakealongtimetotransferthousandsofrowsofdatatodisplayinthelist.EvenwithafastnetworkandWindows,adropdownlistwiththousandsofentriescanbedifficulttouse.Usersmayhavedifficultiesscrollingthroughthousandsofentriesinthelist.Somesystems(notablyOracle),provideanalternativemethodtodropdownlistswhenyouneedtodisplayorsearchthousandsofrowsofdata.Essen-tially,apopupboxletstheusersenterasimplesearchcondition,suchasthefirsttwolettersofacustomer’sname)toretrieveonlyafewselectrows.Mostsystemshaveseveralothercontrols.Forexample,youcanaddcommand

buttonsandwritecodetoopenotherformsorreportsorperformsomecustomaction.Youcanaddbackgroundimages,icons,ordrawlines,rectangles,andcir-cles.Theseitemsaregenerallydecorationsplacedontheformandnotboundtothedatabase.Mostsystemshaveacontrolthatstoresanddisplaysimagesorotherbinaryobjectsinadatatable.Forexample,youcouldstoreaphotographofeachemployeeorproductdirectlyinthedatabase.Othercontrolsexisttohelpsegmentthedataonaform.Forinstance,theSales

formcoulduseasubformtodisplaytherepeatinglistofitemsbeingpurchased.Youcouldalsoconsiderplacingalloftherelatedcustomerdata(suchasaddressandphonenumber)ontheSalesforminaseparatebox.Mostusershavelimitedscreenspace, so it isusuallybetter toaddaneditbutton toopen theCustomerformwiththematchingdata.But,thatisadesignchoiceyouwillhavetomakewiththeusers.

User Interface—Web NotesInmanyways, theWeb environment is similar to the standardWindows envi-ronment.Youhavecontrolover layout,color,anddataentry.Oneof theusefulfeaturesofWebpagesandformsisthattheycanbestandardizedsotheyaredis-playedthesamewayonalmostanytypeofclientcomputerorbrowser.Addition-ally,usersunderstandbasicinterfacefeatures:Clickingahyperlinkbringsanewpage,buttonsareusedtosubmitaform,dropdownlistsareusedtoselectanitem,andsoon.When you develop applications forWeb sites, be sure to incorporate style

sheets.StylesheetscanbepowerfultoolsfordesigningconsistentformsontheWeb.Theyenableyoutodefineastandardlayoutandcolorschemethatwillbe


usedbyallofyourpages.Alteringafewparametersinthestylesheetdefinitionappliesthechangestoallofthepages,creatinganewlookfortheWebsite.Youwillwanttoobtainaneditingtooltocreateandalterstylesheets.MostWebde-velopmentenvironmentscontainatooltomakeiteasytosetfontsandcolorsandotherdesignelementsonstylesheets.WebpagesarealsodifferentfromWindowsformsintermsofthesizeofthe

browser. For starters, the user has control over the browser, not the developer.Inmostcases, theusercanset thefontsizeorcontrol theresolution;whichaf-fectsthedisplayspaceonthepage.OneofthemostimportantissuesindesigningformsforWebpagesistokeepeachpagerelativelysmall.Itisgenerallybettertosplit theformintoseparatelinkedpages, insteadof tryingtocramdozensofthingsontoonepage.Eventually,youalsohavetobemorecautiousaboutsecuri-tyissues—anythingtransmittedacrosstheInternetcouldbeinterceptedatseveralpoints.

User Interface—EventsMostsystemstreatformsusinganeventmodel.Eachform(andcontrol)cangen-eratedifferentevents.Youcanwritecodetoperformacustomactionwhensomeeventistriggered.Forexample,openingandclosingaformarebasiceventsforeveryform.Youcancontrolhowtheformoperatesbycreatingactions thataretakenwheneacheventoccurs.Manyoftheeventsinvolvetheindividualcontrols.Oneof themost commonevents to control is the click eventwhenabutton isclickedorsubmitted.Althoughaformcanhavemultiplecontrols(e.g., textboxes)onit,onlyone

controlata timehasthefocus.Thecontrol thathas thefocuswillreceivekey-strokesenteredbytheuser.Thiscontrolisoftenhighlightedwithanoutlineoradifferentcolor.Thesameconceptapplieswhenanapplicationdisplaysmultipleformsonascreen.Onlyoneformhasthefocusatatime.Withinaform,userscanusuallyusetheTabkeytomovetothenextcontrolinasequence,whichisknownas the tab order.Youmustbesuretocheckthetaborderonallformssothatitvisuallymatchesthelayoutoftheform.Insomecases,youmightevenwanttohandlethetaborexitevent.Whenauserleavesacontrol,yourcodecanperformadditionalcalculationsorassigndefaultvalues.Byhandlingtheeventswithcustomcode,youcanmaketheformrespondto

userrequestsandtohandlecommontasksautomatically.Withdozensofcontrolsanddozensofpossibleeventspercontrol,thechallengeistochooseexactlywhicheventisneededtoperformaspecifictask.

User Interface—Accessibility IssuesOneofthegreateststrengthsoftheWindowsandWebinterfacesisitsgraphicalorientation—whichmakesiteasyforpeopletoperformcomplexoperationswithafewmovesofthemouseorselectionsonthescreen.Oneofthedrawbackstothistypeofinterfaceisthatitismoredifficulttomakeasystemthatisaccessible tousersfacingsomephysicalchallenges.Asadesigneryoucanmakeyourap-plicationsaccessibletoawiderbaseofusers.Tobegin,yourapplicationshouldaccept multiple sourcesof inputs.Donot relyon justamouseorapointerbutalsousethekeyboard,andincreasingly, theuser’svoice.Similarly, it ishelpfulifyourapplicationcanprovidemultipletypesofoutput.Increasingly,youshouldconsiderhowtointegratesoundandvoiceoutput.Theusermustalsobeabletosetthecolorandsizeoftheoutput.


Microsoftguidelinesprovide somesuggestions formakingyourapplicationsaccessibletomoreusers.DetailedideasandcurrentdevelopmentscanbefoundonitsWebsite.Humanfactorsexperiencewithotherapplicationshasgeneratedsomespecificsuggestions.Forexample,donotusered-greencolorcombinations.Approximately 10 percent of theU.S.male population experiences some diffi-cultydistinguishingbetweenredandgreen.Trytopickhigh-contrastcolorsthatmostpeoplecandistinguish(e.g.,blackandwhite,yellow,blue,andred).Whenindoubt,askpeople to testyourcolorcombinations.Betteryet, letusersselecttheirowncolors,orusethesystemcolorschemethatisconfiguredontheuser’scomputer.Second,avoidrequiringrapiduserresponses.Donotputtimelimitsoninput.

Although itmight be fun in a game,manyusers have slowerdata entry skills.Somedesignersincludepop-upmessagestocheckonuserprogressafteradelayin data entry.Thesemessages are usually pointless and canbe annoying.Withmodernscreen-saversecuritysystems,userscansettheirowndelaycontrolsandmessages.Third,avoidcontrolsthatflashrapidlyonthescreen.Theytendtoannoymost

users.Worse,certainflashrateshavebeenknowntotriggerepilepticseizuresinsomepeople.AninterestingsituationaroseinJapanattheendof1997whenase-quenceofflashinggraphicsonananimatedtelevisionshow(Pokemon)sentabout700childrentothehospital.Fourth,asmuchaspossible,enableuserstocustomizetheirscreens.Letthem

choose typefaces, font sizes, and screen colors.Thatway, users can adjust thescreen to compensate for any vision problems theymay have.And if you usesound,letpeoplecontrolthevolume,evenpitch,ifpossible.Inmanycases,theWindows environment providesmuch of this functionality, so the key point istoavoidoverridingthatfunctionality.Also,youshouldtestyourapplicationsonvariouscomputers.Somevideosystemsmaydistortyourchoiceofcolorsorwillbeincapableofdisplayingyourformsatthedesiredresolution.

User Interface—International EnvironmentToday,youmustbuildyourformsandreportswiththeunderstandingthatpeoplefromaroundtheworldwilluse them.Figure6.5summarizessomeof thecom-monissuesyouhavetoconsiderwhenbuildingformsandreports.Languageisthemostobvious,anditcanbethemostdifficult.First,allofyourtextdataneedstobestoredusingUnicodecharacters.WithmanyDBMSs,youneedtospecify

Language and characters•Currency•Time zones•Time and date formats•Number formats•Country names and maps•National ID numbers—privacy•

Figure 6.5Commoninternationalizationissues.Applicationsthatwillbeusedbypeopleindifferentnationsneedtoadapttonationalconventionsasmuchaspossible.


nvarcharinsteadofnvarchar.WithUnicodecharacters,names,addresses,andoth-erdataelementscanbestoredinanylanguage.Dealingwithmultiplelanguagesonformsandreportsismorechallenging,but

mostdevelopmentsystemshavemethodstosimplifythetask.Yourfirstthoughtmight be to create every formmultiple times: once for each desired language.Abandonthatthoughtearly.Itwouldbeextremelypainfultoalteraformifyouhadtochangeitforeverydifferentlanguage.Instead,youspecifythelayoutanddesignoftheformonce.Thenyoumakeallthetextitems(titles,labels,buttons,andsoon)dynamic.Theactualmethoddependsonthetoolsyouareusing.Gen-erally,yougiveeachitemanameanddeclareitinaresource file.Youthencreatearesourcefileforeachsupportedlanguageforeachformorreport.Forexample,theSalesformmighthaveresourcefilesforEnglish,Spanish,French,German,andJapanese.Theresourcefilecontainssimplepairs:theitemnameandthetexttranslatedtothedesiredlanguage.Ofcourse,youhavetofindsomeonetotrans-latealloftheitems.Whentheformisgeneratedfortheuser,thesystemchoosesthe appropriate language resourcefile anddisplays each text item in theuser’schosenlanguage.Youcreateaformonlyonce,butitcandisplaythetextinanylanguagewithamatchingresourcefile.Youcansimplyshiptheresourcefiletoatranslator,andalloftheworkisstoredinonelocation.Youwillprobablywanttoincludecopiesoftheformssothetranslatorscanseethecontexttocreateabettertranslation.Youalsohavetotesttheformwitheachlanguage,particularlynon-Latinlanguages,toensurethetranslatedwordsstillfitcorrectlyontheformanddonotoverlapothercontrols.Onceyouhavedevelopedthedynamicformpageandsetuptheresourcefiles,itisrelativelyeasytosupportadditionallanguages—simplyhaveeachresourcefiletranslatedtothenewlanguage.Dataformatsforcurrency,dates,andnumbersalsovarybyregionornation.

Forinstance,theU.S.usescommastoseparatethousands,andadecimalpointtoseparatefractionalvaluesinanumber.MostEuropeannationsreversethesetwo,usingdotstoseparatethousands.Datescancauseseriousconfusionifyouusetheshortenednumeric format. In theUnitedStates, 1/5/2008 represents January5,2008;whilethesameshortdateinEuropeindicatesMay1,2008.WithWindows-basedapplications,youcanquerytheregionalsettingsontheuser’scomputertofindtheappropriateformatfornumbersanddates.However,itisprobablysafestto avoid the short-date format anduse a format such as 05-Jan-2008,which isclearlyunderstoodbyalmosteveryone.Ifyoudoretrievelocalsettingsfromtheuser’scomputer,beextremelycarefulaboutcurrencyvalues.Pickingupalocalcurrencysymboldoesnotconvertthevalues.SeriousproblemscouldresultifanAmericanenters$100.00intothedatabase,butthesystemdisplaysitas£100.00tosomeoneinEngland.The issueof countrynamescanbecomeaproblem,or evenan international

incident,ifyouareusingageographicinformationsystemormappingprogram.Several areas around the globe are politically disputed or face different claims(sometimes even calling an area “disputed”might anger the participants).Youneed tohave thecorrectmapforeachregion.Countrynamesalsovaryby lan-guage,butyoucangenerallypickonelanguageandusethenamesdefinedbytheInternationalOrganizationforStandardization(ISO)ortheUnitedNations.Finally, be careful to abide by all national lawswith respect to security and

accesstothedata.Forexample,manyEuropeannationshavestrongprivacystat-utes—particularlywithrespecttocustomerdataandnationalIDnumbers.Insomecases,itmaybeillegaltotransferdatacollectedinEuropetotheUnitedStates.


Form LayoutWhat common structures are used in forms? Individual forms or windowsare your primarymeans of communicatingwith peoplewho use your applica-tion.Formsareusedtocollectdata,displayresults,andorganizetheoverallsys-tem.Fromadatabaseperspective,yourapplicationisbuiltfromseveralstandardtypesofforms.Asyoubeginworkingwiththesebasiclayouts,keepinmindthatyoucancreatecomplexformsthatusefeaturesfromseveraldifferentformtypes.However,youshouldunderstandthelayoutandusesofeachindividualformtypefirst.As summarized inFigure 6.6, youwill beworkingwith four basic types of

forms: (1) tabular forms,whichdisplaydata in rowsandcolumns, (2)single-row forms,whichshowdataforonerowatatimeandinwhichthedesignercanarrangethevaluesinanyformatonthescreen;(3)subform forms,whichdisplaydatafromtwotablesthathaveaone-to-manyrelationship;and(4)startup forms, ormenus,whichdirecttheusertootherformsandreportsintheapplication.Youcanthinkofasubformasacombinationofasinglerowformandatabular

form.TheSalesformisaclassicexample.TheSalestableformsthefoundationofthemainform,andtheSaleItemstabledataishandledinthesubform.Becauseeachsalecancontainmanyitemsbeingsold,youneedthesubformtohandletherepeatingsection.

Tabular FormsAsshowninFigure6.7,oneofthesimplestformsisthetabularform,whichdis-playsthecolumnsandrowsfromatableorquery.Somesystemsprovidevaria-tionsonthetabularform,suchasthedatasheetinMicrosoftAccess.Thetabularformisusedasthemainformwithalimitednumberofcolumns,andwhenusersneedtoseemultiplerowsatthesametime.Itisparticularlyusefulforadministra-tiveforms,suchaseditingvaluesinalookuptable.

Form Type Common usesTabular 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.

Switchboard or startup Customized with buttons.

A designed form that is used as the main menu or switchboard to the other forms and reports.

Figure 6.6Formlayout.Onceyouunderstandthesinglerowandtabular(repeating)forms,youcancreatemorecomplexformsbycreatingsubforms.Thestartupformissimilartoamenuthatlinkstheformsandreportstogether.


Theprimaryfeatureoftheformisthatitdisplaysmultiplerowsofdatafored-iting.Consequently,userscanseeandcomparedataacrossseveralitems.Itisuse-fulforshortlistsofdata.Itcancauseproblemswhenthetabletobeeditedistoolarge—bothintermsofnumberofrowsandtoomanycolumns.Iftheuserhastoscrollrepeatedlytofindaspecificentry,atabularformwillnotworkverywell.Tabularformsarecommonlyusedassubforms,wheretheycollectanddisplay

alimitedlistofdatathatisrelatedtothemainform.Forexample,anorderformoftencontainsasubformtiedtotheOrderItemtabletodisplaythelistofitemsbe-ingpurchasedonthecurrentlydisplayedorder.

Single-Row or Columnar-FormsAsingle-rowformdisplaysdataforonerowatatime.Thegoalistodisplayeverycolumnononescreen.Itsgreatestfeatureisthatthedesignercandisplaythedataatanylocationontheform.Itisusefulfordesigningaformthatlookslikeatra-ditionalpaperform.Thedesignercanalsousecolor,graphics,andcommandbut-tonstomaketheformeasiertouse.AsillustratedinFigure6.8,thisformdesignrequiresnavigationcontrolsthatenabletheusertoscrollbackwardandforwardthroughtherowsofdata.Commonnavigationcontrolsalsoincludebuttonstogotothefirstandlastrowsandtogotoaparticularrowofdata.Ingeneral,youwillwanttoincludeaFindcommandthatenablestheuserto

locateaparticularrowofdata—basedonthevaluesinsomerow.Forexample,aformdisplayingcustomerdatashouldhaveasearchoptionbasedoncustomername.Similarly,theuserwilloftenwanttosorttherowsindifferentorders.Thesingle-rowformisgenerallythemost-usedformlayout.Withcarefulde-

signyoucanuseittodisplaysubstantialamountsofdata.Byincludingsubforms,

Figure 6.7Sampletabularform.Youdefinethecontrolsforonerow,andtheDMBSdisplaysdataforalloftherowsinthequery.Scrollbarsenabletheusertoseemorerows(orcolumnsofdata).


Figure 6.8Asimplesingle-rowform.Thisformdisplaysdataforonerowatatime.Youhavesubstantialcontroloverlayoutthroughcolor,graphics,andcommandbuttons.Thenavigationbuttonsonthebottomenabletheusertodisplaydifferentrows.

Figure 6.9Subformexample.ThemainformisbasedontheSaletable,whichhasaone-to-manyrelationshipwiththeSaleAnimaltableusedonthesubform.ThesubformcontentsarelinkedviatheSaleID.Thetwosubformsinthisexampleareindependent.


youcanhighlightrelationshipsamongvariouspiecesofdataandmakeiteasyforuserstoenterdata.Youcanalsoincludechartstohelpusersmakedecisions.

Subform FormsAsubformisusuallyatabularformembeddedonthemainform.Asubformgen-erallyshowsaone-to-manyrelationship.IntheexampleinFigure6.9,asalecouldincludemanyanimals,soyouneedasubformtodisplaythisrepeatinglist.Themainformmustbeasingle-rowform,andthesubformshouldbeatabularview.TheSaleexampleforthePetStoreismorecomplexthanforothercompaniesbe-causeitusestwosubforms.Animalsaretreatedasseparateobjectsfrommerchan-dise,soeachisrecordedinaseparatesubform.Ifyou lookat theunderlying tables,youwill see thatSaleID links themain

form(basedonSale)andsubform(basedonSaleAnimal)toeachother.Youwillrarelydisplaythelinkingcolumnonthesubform.Ingeneral,doingsowouldbepointless,since the linkingcolumnwouldalwaysdisplay thesamevalueas therelatedcolumnonthemainform.Thinkaboutwhatthatmeansforaminute.TheSaletablehasaSaleIDthatisgeneratedbytheDBMSwhenanewsaleiscre-ated.TheSaleAnimaltablealsohasaSaleIDcolumn,andeveryanimalsoldmustcontainthesameSaleIDvaluefromthemainform.YetitwouldbepainfulfortheclerktoreentertheSaleIDonthesubformforeachanimalthatissold.ByusingthesubformandspecifyingtheSaleIDasthelink(MasterandChildproperty),theDBMSautomaticallyentersthemainSaleIDintothetableforthesubform.Mostdatabasesystemsenableyoutocreateformsthathavemultiplesubforms.

Thesubformscanbeeitherindependent—asseparateboxesonthemainform—ornested—whereeachsubformliesinsideanother.Inmostcases,youwillwanttheparentformstobesingle-rowforms.However,somesystemssupporttabularlistsforboth themainand thesubforms. Inmostapplications,userswouldfindthisapproachdisconcerting:Theywouldfirsthavetoselectasinglerowintheparentformandthendealwiththematchinglistinthesubform.

Figure 6.10Sampleswitchboardform.Thebuttonsmatchtheuser’stasks.


Startup FormsStartupormenuformsprovide theoverall structure toanapplication.Theyarestraightforwardtocreate,althoughyoumaywanttheassistanceofagraphicsde-signer.Thestartuporswitchboardformoftencontainsimages,andthedesignre-flectsthestyleofthecompany.Youbeginwithablankformandremoveanyscrollbarsandnavigationcon-

trols.Picturescanbeinsertedasbackgroundimagesorasindividualcontrolsthatcanbeusedasbuttonstoopenanotherform.AsshowninFigure6.10,command buttonsorlinksarethemostimportantfeatureofthestartupform.Whentheuserselectsabutton,acorrespondingformorreportisopened.Themainstartupformwillbeusedquiteoften,soyoushouldpaycarefulattentiontoitsdesign.InWeb-basedapplications,thebuttonsareusuallyreplacedwithhyperlinkstothedesiredformorreport.Thekeytoasuccessfulapplicationbeginswiththestartupform—notjustits

designbutalsoitscontent.Theformsshouldmatchtheuser’stasks.Oneapproachistofirstidentifytheuserandthenprovideaselectionofbuttonsthatmatcheshisorhertasks.Considerasimpleexample.Amanagerneedstoprintadailysalesreportofbest-sellingitems.EveryweektheDBMSmustprintoutalistoftotalsalesbyemployee.Thefirmalsosendsletterstothebestcustomerseverymonthofferingthemadditionaldiscounts.Asecretarywillbeinchargeofprintingthesereports,soyoucreateasimplemenuthatlistseachreport.Thesecretarychoosesthedesiredreportfromthelist.Somereportsmightaskquestions,suchaswhichweektouse.Thesecretaryenterstheanswers,andthereportisprinted.Thefirststepincreatinganapplicationistothinkaboutthepeoplewhowill

useit.Howdotheyperformtheirjobs?Howdothedatabaseinputsandreportsfitintotheirjob?Thegoalistodeviseamenusystemthatreflectsthewaytheywork.Twoexamplesofafirstmenuare shown inFigure6.11.Whichmenu is easierforaclerktounderstand?Answer:Theonethatbestrelatestothejob.Onceyouunderstandthebasictasks,writedownasetofrelatedmenus.Somemenuoptionscallupothermenus,someprintreports,andothersactivatetheinputscreensyoucreated.

Creating FormsWhat are the main steps used to create forms?Thefirststepincreatingaformistobesurethatyouunderstanditspurposeandhowitwillbeused.Itsusagedic-tatesthespecificdatathatneedstobedisplayed.Onceyouknowthedataneeded,

Main Menu1. Setup Choices2. Data Input3. Print Reports4. DOS Utilities5. Backups

Customer InformationDaily Sales ReportsFriday Sales MeetingMonthly Customer Letters

Quit

Figure 6.11Designingmenusforusers.Whichmenuiseasierforasecretarytounderstand?Whendesigningapplications,youshouldorganizetheapplicationtomatchtheprocessesusersperform.


youcan identify thedatabase tables thathold thatdata.One importantpoint toremember:Aformshouldonlyattempttoupdatedatatoonetableatatime.Forexample,acommonsalesformmightdisplaydataabouttheSale,theCustomer,theSaleItemsselected,andperhapsdetaileddataabout the individualProducts.Throughthedesignprocess,thisdataneedstobestoredinfourrelatedtables,sohowcanyoucreateaformthatupdatesonlyonetable?Theanswertothisquestionactuallydeterminesthecharacteristicsofmanyda-

tabasesystems.Thereasonyoucanputonlyonetableonaformisthatmultipletablesmakeitdifficultfortheformtounderstandexactlywhattheuserisattempt-ing todo.For instance, if themainSales formcontainedall columns from theSalestableaswellastheCustomertable,whatdoesitmeantoaddanewrow?ShouldthatrowbeaddedtotheSalestableortheCustomertable?Theprocessiscomplicatedwhenyouwanttodisplaydatafrommultipletables.

Forinstance,youprobablywanttodisplayacustomer’snameandphonenumberonasaleform.DependingontheDBMSyouareusing,youcouldhaveseveraloptions.Youmightbeabletodisplaythecustomerdataonthesaleformwithoutneedingtoedit it. In thiscase,youcouldaddabuttonorhyperlinktoopenthecustomer formwith the corresponding data available for editing.Alternatively,youmightbeabletocreatesectionswithinaform,whereeachsectioncanholddataforanew,linkedtable.Oneofthesesectionscouldbeasubformthatcon-tainsrepeatingrowsofdatalinkedtothemainform.Dependingonthedatabasesystem,youmightbeabletouseanupdateablequerytodisplaydatafrommul-tipletables.

Updateable QueriesTheissueofmultipletablesonaformisrelatedtotheproblemofupdateableque-ries.Ifasystemcansupportqueriesthatusemultipletablesasupdateable,thenit ispossible toputcarefullyselectedcolumnsfrommultiple tablesonasingle

Customer Order

SaleIDCustomerID

Date

Carly Embry

SaleID CustomerID Sdate1232 23 7/24/011233 74 7/24/011234 17 7/25/01

CustomerID First Last15 Connie Fisher16 Rosie Wade17 Carly Embry

Sale Customer

QueryJoin

Dataentry Data

display

7/25/07123417

Figure 6.12BasingtheOrderformonaquery.ThequerycontainsallthecolumnsfromtheSaletableandsomecolumnsfromtheCustomertable.ThequerymustneverincludetheCustomerIDcolumnfromtheCustomertable,whichisthecolumnusedtojointhetwotables.


form.Figure6.12showsacommonSalesOrdermainform.TheSaletableholdstheCustomerIDasaforeignkey.Torecordasale,asalespersonsimplyneedstoentertheIDnumberoftheappropriatecustomer.Butitisnoteasytoremembernumbers,anditwouldbenicetodisplaythematchingcustomerdataonthemainformtoverifythenameandaddress.Technically,aquerycouldbebuilt that includesallof thecolumns from the

SaleOrdertablealongwithsomeofthedescriptivecolumnsfromtheCustomertable.Toremainupdateable,thequerymustnotincludetheprimarykey(Custom-erID)fromtheCustomertable.Figure6.13showsthebasicquery.OnepotentialdrawbacktothisapproachisthatwheneveranewCustomerIDisenteredintoasale,theformmustmakeatriptothedatabasetolookupthematchingcustomerdata.Consequently,someformssystemsdonotsupportthisapproach.Inparticu-lar, itcancauseproblemsonWeb-basedformsbecauseof thedelay insendingdatatotheserverandwaitingforareply.

Linked FormsAnotherapproachtotheproblemistouselinkedforms.AsshowninFigure6.14,theseformsmightbedisplayedseparately,ortheymightbeseparatesectionsdis-playedonasingleform.Thislatterapproachisusedforparent/childforms,wherethechildformcontainsdatafromasecondtablethathasaone-to-manylinktotheparentform.Conceptually,displayingarelatedtableononeformversusinasepa-ratewindowisequivalent.Themaindifferenceliesintheamountofscreenspaceneeded.Ifyouputseveralsectionsononeform,userswillneedlargescreenstoseeallofthedata.Byusingmultiplewindows,theuserwillnotbeabletoseeall

SELECT Sale.SaleID, Sale.SaleDate, Sale.CustomerID, Customer.LastName, Customer.FirstName, Customer.PhoneFROM SaleINNER JOIN Customer ON Sale.CustomerID=Customer.CustomerID

Figure 6.13UpdateablequeryforSalesform.NewrowswillbeenteredintotheSaletable,butsomesystemswillalsosupportupdatestothethreecolumnsfromtheCustomertable.NotethatyoushouldneverincludetheCustomer.CustomerIDcolumnintheSELECTstatement.

Sale FormCustomerID 15 Edit

Customer

CustomerIDLast NameFirst NamePhone

15ConnieFisher(409) 116-3589

Figure 6.14Linkedforms.TheEditbuttonopenstheCustomerformandloadsthedatathatmatchesthecurrentvalueofCustomerIDontheSaleForm.


ofthedataatonetimebutcanswitchbetweenthewindowstoseeandedit thedata.Linkedformsworkbyusingaquerytomatchthedatadisplayedinthesecond-

aryformtoakeyvaluefromtheoriginalform.Forinstance,fromthemainSaleOrderform,theCustomerIDcanbeusedtodisplaythematchingcustomerdatainalinkedform.Similarly,aSaleItemsubformcandisplaytherowsthatmatchtheSaleIDfromthemainform.Eachrelatedportionofthedatacanbedisplayedinaseparateformorregion.Ideally,theformssystemwillhaveamethodtoautomati-callyperformthelinkingandretrievethematchingdata;otherwise,youwillhavetowritecustomizedcodetomodifytheunderlyingqueryandrefreshthedataasneeded.

Properties and ControlsMostsoftwarepackagesarebuiltusinganobject-orientedapproach.WithanOOdesign,eachobjecthaspropertiesthatdescribeitandmethodsorfunctionsthatitcanperform.Objectsarealsocloselytiedtoanevent-drivensystem,whereuseractionsandchangescantriggervariousevents.Mostdatabaseformsfollowthismethodology.However,youwillgenerallyuseobjectsthathavealreadybeende-finedbytheDBMS.Yourjobistoassignpropertiesandwriteshortprogramstorespondtovariouseventstomaketheapplicationeasierforusers.AshighlightedbythelistofpropertiesinFigure6.15,formandcontrolprop-

ertiescanbegroupedintoprimarycategories.Thefirstcategory(data)relatestothesourceofthedata,whereyousetthebasetableorquery.Youcansetfilterstodisplayonlythedatarowsthatmeetaspecificcondition.Youcanalsospecifythe

Category Sample PropertiesData Base table / query

FiltersSort

Integrity EditsAdditions, deletionsLocks

Format CaptionScroll barsRecord selectorsNavigation buttonsSize and centeringBackground/picturesColorsTab order

Other Pop-up menusMenu barHelp

Figure 6.15Basicpropertiesforforms.Ataminimum,setthedatasourceandbasicformatproperties.Additionalpropertiesensureconsistency,protectdata,andmaketheformeasiertouse.


sortorderandaWHEREclausedirectlyintheunderlyingquery,whichnormallywouldbeamoreefficientapproach.This stepessentiallybinds thecontrolele-menttothedatabase.Asecondsetofpropertiesreferstodataintegritytohelpyoucontrolthetype

ofeditingandchangesallowedontheform.Forexample,youmightsettheprop-erties for individualusers so that someusers cannot addordeletedatausingaparticularform.However,keepinmindthatitisgenerallysafertosetthesecon-ditions inSQLso that theyapply throughout thedatabase, andnot justononeform.Forexample,salesclerksshouldprobablybepreventedfromaddingnewsuppliers.A third levelofpropertiescontrols thedisplayof the form.Everything from

thecaptiontoscrollbars,formsize,andbackgroundaresetbydisplayproperties.Again,rememberthatconsistencyisavirtue.Beforebeginningaproject,chooseadesigntemplateandstandards;thensetallformandcontrolpropertiestomeetthatstandard.

Controls on FormsThestandardformcontrolsaresupportedbyeveryforms-developmenttool.Manysystems have wizardsthatquicklycreatestandardlabelsandtextboxcontrolsfordesiredcolumns.Eachcontrolneedstobegivenanameandboundtotheappro-priatedatacolumn.Becarefulwiththename;onceyouhavesetit,itisdifficulttochange.Youneedtopickameaningfulnamewhenyoufirstcreatethecontrol.Ifyoutrytochangeitlater,youwillhavetofindeverycontrolorprogramthatreferstothatcontrol—whichcanbeatime-consuminganderror-pronetask.Thenameisusedbyothercontrolsandprogramcode(muchlikeavariablename)toretrieveandstoredataontheform.Some people name controls based on the type of control. For example, the

nameofa labelcontrolwouldendwith thewordLabel, suchasAddressLabel.

Employee

Name: Sue Zhang

ID: 3354

Phone: 222-111-1524

. . .

Photo:

Figure 6.16Imageboundtoadatacolumn.Employeephotoisscannedandstoredinthedatabasecolumn.


Alternatively,somedevelopersprecedethenameofthecontrolwithitstype,suchaslblAddress.Thedifferenceaffectsthesortorderofthecontrols.Ifyouendwiththe“Label”notation,controlnamessortedalphabeticallywillgroupbythedataname(AddressLabel,AddressTextBox).Ifyoustartwiththe“lbl”notation,allofthelabelswillbelistedtogether(lblAddress,lblPhone).Youneedtodecideatthestarthowyouwanttolocateandrememberthecontrolnames.Oncethebasecontrolsareset,youwillhavetosetformatting,andrearrange

thecontrolsonthepage.Youwillalsohavetopayattentiontothelayoutofcon-trolsonthepage.Generally,youcandragthecontrolsandlabelstonewpositions,butbesuretousethealignmenttoolstomatchtheedges.Graphics FeaturesOccasionallyyouwillwanttoaddgraphicsfeaturestoyourform.Therearecon-trolstoaddpictures,aswellassimplelinesandboxes.Linesandboxesareoftenusedtocreateathree-dimensionaleffectforothercontrolsbyaddingshadingorhighlighting.TodisplayanimagefromatableasshowninFigure6.16,youmustfirstdefine

anObjectorimagecolumnwithinthattable.Thentheboundobjectframeisusedlikeatextboxtopositionanddisplaytheimageontheform.Touse an imageor texture as a background,first use a graphics package to

makesure the image is lightenough tonot interferewith the readabilityof theotherboxes.ThensetthePicturepropertyoftheformtothenameoftheimagefile.Inmostcases,youwanttoembedtheimageontheformsothepictureisin-cludeddirectlywiththedatabase.Complex ControlsAdditionalcontrolobjectscanbecreatedusingavarietyofcomputerlanguagesorpurchasedfromcommercialvendors.AfewadditionalcontrolsareshowninFigure6.17.TheTabandCalendar con-

trolsareparticularlyusefulinbusinessapplications.ThereisalsoaGridcontrol

Tab

Grid

Gauge Slider Spin box

Calendar

Figure 6.17Additionalcontrols.Thousandsofcontrolsareavailabletoimprovetheuserinterfaceorperformspecializedtasks.Commoncontrolsincludetabs,grid,calendar,gauges,sliders,andthespinbox.Youcanalsocreatecustomcontrols.


thatenablesyoutodisplaydatainaspreadsheetlayout.Thesetypesofcontrolsarenotaseasytouseasthestandardboundcontrols.Thedeveloperhastowriteshortprogramstoloaddataintothecontrolandtorespondtothecontrol’sevents.ChartsDatabaseapplicationsusedformakingdecisionsoftencontaincharts or graphs.Chartsareanothertypeofcontrolthatcanbeplacedonaform(orreport).Thefirststepincreatingausefulchart is todiscusswiththeuserexactlywhat typeofdataandwhattypeofchartwillbeneeded.ThenyouusuallybuildanewSQLquery thatwill collect the data to be displayed on the chart.The chart controlplaces thecharton the formandspecifies the individualattributes (like typeofchart,axisscale,andcolors).Twobasictypesofchartsareusedondatabaseformsandreports:(1)graphs

thatshowdetailfromthecurrentlydisplayedrowand(2)graphsthatdisplaysum-marydataacrossall(orseveral)oftherows.Thedifferencebetweenthetwoap-proachesliesinthelevelofdatadisplayed.Detailgraphschangewitheachrowofdatadisplayed.Summarygraphsareusuallygeneratedfromtotalsoraverages.Figure6.18illustratesthetwotypesofgraphsastheymightbeusedinthePet

Storedatabase.Eachchartshowstheamountofmoneyspentonanimalsversustheamountspentonmerchandise.However,thetopsetofchartsshowsthesplitforeachindividualsale,sothegraphsvarywitheachrowintheSalequery.Thebottomchartshowstheoveralltotalforthestore—evenifitisplacedonaSalesformthatshowseachrowofdata,itwillnotchange(exceptovertime).Tocreatethetwotypesofcharts,themaindifferenceisinthequery.Thequeryforthedetailchartscontainsacolumn(SaleID)thatislinkedtotherowofdatabeingdisplayed

Sale 1

Sale 2

Sale 3

Merchandise

Merchandise

Merchandise

Animal

Animal

Animal

Total Sales

Merchandise

Animals

Figure 6.18Chartsonformsorreports.Thetopchartsshowthesplitinsalesforeachindividualsaleandwillchangewitheachrowofdata.Thebottomgraphshowsthesalessplitforallsalesandisnotboundtoanindividualrow.


ontheform(basedonitsSaledID).Thesummarygraphcomputesthetotalsacrossallofthesalesandisnotlinkedtoanyparticularsale.

Multiple FormsAsyou canguess, an applicationwill quickly spawnmanydifferent forms.Ofcourse, theformsshouldbelinkedtoeachothersouserscanquicklymovebe-tween the formsbyclickingabutton,datavalue,or image.Switchboard formsplay an important role in tying forms together.However, you can also connectforms directly.Themost common example is the use of subforms placed on amainform.InthissituationtheformsarelinkedbysettingtheMasterandChildpropertiesofthesubform.Thenthedatabasesystemkeepsthedatasynchronizedsothatwhentheuserselectsanewrowinthemainform,thematchingrowsinthesubformarelocatedanddisplayedautomatically.

When the forms contain related data, you sometimeswant to open the newformanddisplayonlytherowofdatathat isrelated.Forexample, if theOrderformcontainscustomerdata,whentheuserclicksanEditbutton(ordouble-clicksonthecustomername),theapplicationshouldopentheCustomerform.TheCus-tomerformshoulddisplaythedatathatcorrespondstothecustomerontheOrderform.ThetechniqueforlinkingformsvariesbyDBMS.Theaccompanyingwork-booksprovidethesyntaxandexamplesneededforeachDBMS.IntheSaleformexample,thelinkcriteriaspecifiesthattheCustomerIDintheCustomerformmustmatchtheCustomerIDfromtheSalesform.Inmostcases,theuserwouldclosetheCustomerformandreturntothemain

Saleform.Butwhatifuserscommonlykeepbothformsopenatthesametime?Then theywouldexpect thedatabetween the twoforms tobesynchronizedso

Animal

AnimalID

Sale=AnimalID from Animal form

- - - - - - -- - - - - - -

Subtotal=Sum(Price*Quantity)

=Forms!Sale!ItemsSold.Form!Subtotal

ItemsSold

=Subtotal*[TaxRate]Subtotal

Tax=Subtotal+TaxOrderTotal

Figure 6.19Copyingdatafromadifferentform.ThedefaultAnimalIDiscopiedintotheSaleformfromtheAnimaltable.Likewise,thesubtotalisfirstcomputedonthesubformandthencopiedtothemainform.


thatwhenanewrowisdisplayedontheSaleform,thematchingdatawouldbedisplayedontheCustomerform.Youmighthavetowriteacoupleoflinesofcodetoenablethissynchronizationtowork.Essentially,whenevertheSaleischanged,yourcodegrabsthenewCustomerID,passesittotheCustomerform,requeriesthedatabase,andredisplaystheformwiththematchingdata.Athird,relatedsituationisshowninFigure6.19.Perhapswhilelookingatani-

maldata,thecustomerdecidestobuythatanimal.APurchasebuttonontheAni-malformcouldquicklybringuptheSaleform.Itwouldbeconvenientifthebut-tonthencopiedtheAnimalIDintotheappropriatespaceontheSaleform.Again,youneedtowriteacoupleoflinesofcodetoinserttheAnimalIDinto

theappropriatecontrolontheSaleform.Insomesituations,youmightwanttheSalesformtoreferbacktotheIDvalueontheAnimaltable.Businessapplicationscommonlyneedtocomputesubtotalsfromsubforms.As

showninFigure6.19,somesystemstreatsubformsasentirelyseparateforms,soyoumustfirstdothesubtotalcalculationonthesubform,andthencopyitsvaluebacktothemainform.

Direct Manipulation of Graphical ObjectsCan form usability be improved?Inthelastfewyears,theuserinterfacetoap-plicationshasbeenchanging.Theheavyuseofgraphicshasledtoanemphasison direct manipulation of objects.Insteadoftypingincommands,theusercandraganitemfromonelocationonthescreentoanothertoindicateachange.Mostpeoplehaveseenthisapproachusedwithbasicoperatingsystemcommands.Forexample, in thedaysofDOSyouhad to typeacommandsuchas:COPYMY-FILE.DOCA:MYFILE.DOCtocopyafile.Todayyouclickonthefileiconanddragittoadiskdriveicon.

Tabby

Lab

Current Choices

Kennel/Orders

Bird

Cat

Dog

Spider

Fish

Mammal

Reptile

Customer

Figure 6.20DirectmanipulationofgraphicalobjectsatthePetStore.InsteadofenteringanAnimalIDintoabox,youdragthepictureoftheanimaltothecustomertoindicateasale.Double-clickingonanitembringsupmoredetailorrelatedgraphicsscreens.


Sally’s Pet Store ExampleAgraphicalapproachcanmakeyourapplicationseasier touse.However, it re-quireschangingthewayyouthinkaboutapplications,andagooddoseofcreativ-ity.ConsiderthePetStoreexample.Thebasicformsdesignedearlierinthischap-terwere easy to create, and theywill performadequately.However, you couldchangetheentireapproachtotheapplication.Figure 6.20 shows a partial screen for the Pet Store example.Compare this

formto the traditionaldataentry formshown inFigure6.9.The traditionalap-proachrequiresuserstoentertextintoabox.Withthegraphicalapproachtheuserseesphotosoftheindividualanimalsanddragsthemtothecustomertoindicateasale.Double-clickingonanitemprovidesmorepicturesoradditionaldetails.Asimilarapproachwouldbeused tospecial-orderanimals,usingdrag-and-drop techniquestodefineananimal(breed,color,etc.),andtoplacetheorder.Notethatyoucannotentirelyeliminatedataentry.Atsomepoint,youneedto

collect basic data on customers (name, address, phonenumber, etc.).This datacould be entered on a traditional form that is activatedwhen the clerk double-clicksthecustomericonorphoto.Thatis,theforminFigure6.21replacesthetra-ditionalsalesformbutdoesnotreplacethebasiccustomerform.Ofcourse,oncethecustomerdataisonfile,itcanbedraggedbacktothismainformwheneverthecustomerreturns.

The InternetTheemphasisongraphicsandadirectmanipulationofobjectscanbeparticularlyvaluableforformsusedwiththeInternet.Forstarters,mostoftheuserswillhavelittle experiencewith databases and only limited knowledge of your company.

CustomerIDLastNameFirstNamePhoneAddressCityStateZIPCode

Customers

EmployeeIDLastNameFirstNamePhoneAddressCityStateZIPCode

Employees

SaleIDCustomerID

SaleCustomers

SaleIDEmployeeID

SaleEmployees

SaleIDSaleDate

Sales

SaleIDItemIDQuantitySalePrice

SaleItems

ItemIDDescriptionListPrice

Items

1

*

1

*

1

*

*

1 **

1

Figure 6.21Databasedesignvariation.Iftherecanbemanycustomersandmanyemployeespersale,thedatabasedesignneedstwonewtables:SaleCustomersandSaleEmployees.


Creatingagraphicalmodelofthecompanyanditsprocessesachievestwoimpor-tantobjectives:(1)Itmakesthesiteeasiertousebecauseitmatchesthephysicalpurchasemethodsusersalreadyknow,and(2)itlimitstheactionsoftheuserstothosethatyouhavedefined.Oneofthegoalsofthegraphicalapproachistohidetheuseofthedatabase.

Yes,allbasicproductinformation,figures,andsalesdataarestoredinthedata-base.Thedatabasesystemprovidessearchcapabilitiesandstoresuserselections.It also provides reports and data analysis for managers. However, users neverneedtoknowaboutthedatabaseitself.Userssimplyseeanimageofastoreanditsproducts.Theymanipulate theobjects to learnmoreor toplaceorders.Onedifficultyof the Internet is thatyouare limitedby thecapabilitiesof theuser’sbrowser. Browsers handlemost simple data entry controls, but rarely have theabilitytoperformdrag-and-dropoperations.Nonetheless,searchingforgraphicalapproachescanhelpyoufindwaystomaketheformsmoreintuitiveandeasiertouse.

Complications and Limitations of a Graphical ApproachSeveralpotentialdrawbacksexisttobasingaformonthedirectmanipulationofgraphicalobjects.Themostimportantisthatitcanbeaninefficientwaytoenterdata. For example, youwould not expectworkers at a receivingdock to use adrag-and-drop form to record the receipt of several hundred boxes.Abar-codescannerwouldbeconsiderablymoreefficient.Likewise,aqualitycontroltechni-cianwouldpreferasimplekeystroke(orvoice)systemsoheorshecouldenterdatawithoutlookingawayfromthetask.EventhePetStoresalesformisadebatableuseofthedrag-and-dropapproach.

Thinkabouttheoperationsatatypicallargepetstore.Considerwhatwouldhap-penwhendozensof customers bring shopping carts full ofmerchandise to thecheckoutcounter.Ifaclerkhastouseadrag-and-dropscreen,thecheckoutpro-cesswouldtakeforever.Again,bar-codescannerswouldspeeduptheprocess.Ontheotherhand,perhapstheoperationsofthestorecouldbeimprovedbyeliminat-ingthecheckoutclerk.Thinkabouthowthestorewouldfunctionifshoppersusedthestore’sdrag-and-dropWebsitetoselectproducts,whichwerethendelivered,orbaggedandstackedfordrive-throughpickup.Thedifferenceinthevalueoftheapproachdependsontheoperationsofthebusinessandonwhowillbeusingtheapplication.Aseconddifficultywiththegraphicsapproachisthateachapplicationrequires

aconsiderableamountofcustomprogramming.Thetraditionalapproachisrela-tivelystraightforward.Commontoolsexistforenteringdatawithformsmadeupoftextboxes,comboboxes,andsubforms.Thesetoolscanbeusedforvirtuallyanydatabase application.On theother hand, directmanipulationof objects re-quiresthatindividualbusinessobjectsbedrawnonthescreenandassociatedwithdata.Theneachuser action (double-clickanddrag-and-drop)has tobedefinedspecificallyforthatapplication.Inthefuturetoolsmaybecreatedtoassistinthisprogramming.However,today,agraphicalapproachrequiresconsiderablymoreprogrammingeffortthanotherapproaches.Building graphical database applications across the Internet carries similar

problems.Therearetwoprimarylimitations:transmissionspeedandlimitationsofsoftwaretools.However,ahugeamountofmoneyandeffortisbeingdirectedtowardtheWeb.Manyfirmsinseveralindustriesareworkingonsolutionstobothlimitations.


Database Design RevisitedWhat problems arise if a design always uses one-to-many relationships?Itishelpfultoseehowdatabasedesigndecisionsaffectthelayoutofaform.Thekeyelementtorememberisthatone-to-manyrelationshipsarebuiltonformsaseitherasubformoralinkedform.The“many”sideoftherelationshipisrepeating,sotheformneedssomemechanismforcollectingmultiplerowsofdata.Rememberwhenyoudesignadatabaseyouoftenhavetodecideifarelationshipisone-to-oneorone-to-many.Ifyoutakethelazywayoutandmakeeverythingone-to-one,userswillyellbecause itwillnotbepossible to record the importantdata.Forexample,inthestandardsalesform,thinkaboutwhatwouldhappenifthedesignallowedonlyoneitemtobesoldatatime,insteadofmany.YouwouldnolongerneedtheSaleItemstableanditscorrespondingsubform.Instead,theSalesformwouldcontainasinglespottoselectoneproductbeingsold.Insomefields(suchas real estate), this approach is reasonable. But, what happens when someonewantstopurchasefiveitemsatthesametime?Theclerkcouldturnawaythecus-tomer(whichisreallybad).Or,theclerkcouldinitiatefivedifferentSalestransac-tions—eachwithasingleitem.Itwouldwork,buttheclerkwouldbeunhappy,andthecustomerwoulddecideyouhadnocluehowtobuildaninformationsys-tem.So,youcreatedtheSaleItemstableandaddedthesubformtotheSalesformtohandlemultipleitems(rows)persale.Nowlookattheotherextreme.Youmightoptforflexibilityinyourdesignand

buildeveryrelationshipasone-to-many.Again,considerthesalesexample.Mostorganizationsassumethatanygivensale isprocessedbyoneemployeeforonecustomer.Tohandleawidervarietyof situations,youdecide tomodelbothofthoseasone-to-manyrelationships:(1)Anysalecanbemadetomanycustomers,and(2)Anysalecaninvolvemultipleemployees.Thecatchisthatyounowneed

Figure 6.22Databasedesignvariation.Tohandlemanycustomersandmanyemployees,twonewsubformsneedtobeaddedtothesalesform.Thisformismorecomplexandmoredifficulttousethanthestandardsalesform,soitshouldbeavoidedunlesstheuserstrulydoneedtorecordmanycustomersandmanyemployees.


toaddtwotablestothedesign(SaleCustomersandSaleEmployees).Figure6.21showsthetwonewtables.ObservethatbothSaleIDandCustomerID(orEmploy-eeID)needtobepartoftheprimarykey.More importantly,youneed toadd tworepeatingsections to theSales form.

Figure6.22showsaversionofthenewform.Noticetheadditionofthetwoscroll-ingregionsforenteringcustomersandemployees.Atraditionalsalesformwouldcontainonlyasingleentryforcustomerandforemployee.Iftheorganizationof-tenneedstorecordmultiplecustomersandemployeesforeachsale,thereisnoth-ingwrongwiththisversionoftheform.However,ifthecompanyalmostalwaysrecordsonlyoneentryforeach,thenthisformisoverkill.Ittakesupmorescreenspaceandismoredifficult tounderstand.Itwillprobablyrequiremoretrainingtouse—justtoexplaintoclerksthatmostofthetime,theywillsimplyenteronecustomerandoneemployee.Thepointisthatyouneedtothinkabouttheusabilityoftheformswhenyou

designthedatabase.Youcannotjustrandomlychooseone-to-oneorone-to-manyrelationships.Theymustmatchthetrueneedsoftheorganization.

ReportsWhat are the basic roles of reports?Whenyouunderstandforms, reportsarestraightforward. Increasingly, themaindifferencebetween forms and reports isthatreportsaredesignedtobeprinted,whereasformsaredisplayedonthescreen.Thereare twoadditionaldifferences: (1) formscanbeused tocollectdata,and(2)reportsaregenerallyusedtopresentsummarizeddata.Consequently,reportscannothavecontrolsthatcollectdata.But,why,ifyoucanprintforms,wouldyouneedreports?Thetwomainstrengthsofareportarethat(1)itcaneasilyhandlemultiplepagesofoutput(withconsistentpageheadersandpagenumbering)and(2)itcancombinebothdetailedandsummarydata.Chapter5illustrateshowSQLqueriescanproducerelativelycomplexresultswiththeGROUPBYclause.How-ever,asingleSQLquerycanbeusedtodisplayeitherdetailrowsofdataorthe

Report usage/user needsReport layout choices Tabular Columns/subgroups Charts/graphsPaper sizesPrinter constraintsHow often is it generated?Events that trigger reportSize of the reportNumber of copiesAvailability of color

Security controls Distribution list Unique numbering Concealed/nonprinted data Secured printers Transmission limits Print queue controlsOutput concerns Typefaces Readability Size User disabilities OCR needs

Figure 6.23Fundamentalsofreportdesign.Determinecontentandlayoutwithusers.Estimatesizeandprintingtimes.Identifysecuritycontrols.Checktypefacesandsizingforuserreadability.


summaries—notboth.AgoodDBMSreportwriteralsoprovidesadditionalcon-trolovertheoutput,suchasprintingnegativevaluesinred.

Report DesignAssummarizedinFigure6.23,severalissuesareinvolvedindesigningreports.Asinthedevelopmentofforms,youandtheusersneedtodeterminethecontentandlayout.Youmustalsoidentifythetypicalsizeofthereport(numberofpagesandnumberofcopies),alongwithnotinghowoftenitmustbeprinted.Becauseofthephysicalstepsinvolved,printingreportscanbeatime-consumingprocess.A reportof a fewdozenpages isnoproblem.However,whena reportbloomsintohundredsofpageswiththousandsofcopies,youhavetoplanmorecarefully.First,youneedafast,heavy-dutyprinter.Thenyouneedmachinesandpeopletoassembleanddistributethereportcopies.Yougenerallyhavetoscheduletimetousetheprinterforlargereports.Paper reports alsopresent adifferent challenge to security.Paper reports re-

quire the use ofmore traditional security controls, such aswritten distributionlists,numberedcopies,andcontroldata.Ifsecurityisanimportantissueinanor-ganization,thenthesecontrolsshouldbeestablishedwhenthereportisdesigned.Severalphysicalandartisticaspectsareinvolvedindesigningreports.Thesize

ofthepage,thetypefaceused,andoveralldesignofthepageallmustbedeter-mined.NewerDBMSreportwritersarerelativelyflexible,whichisgoodandbad.Thegoodpartisthatdesignershavegreatercontroloverthereport.Thebadpartisthatdesignersneedtounderstandmoreaboutdesign—includingtheterminology.Artisticdesignandathoroughtreatmentofdesignissuesarebeyondthescope

of thisbook. If you are serious aboutdesign (forpaper reports, forms,orWebpages),youshould takeacourse ingraphicdesign. Inanycase, ithelps ifyoulearnafewbasicterms.

Page layout Landscape vs. portrait Margins Gutter TrimTypefaces Serif (TimesNewRoman) Sans serif (Arial) Ornamental (Script) Fixed width (Courier)Font size Common: 10-12 point 72 points approximately 1 inch Pica: 1/6 inch, 12 points

Facing pages (portrait)

guttermargins

Landscape

Alignment marks for color separations.

Trim area

Figure 6.24Basicpublishingterminology.Understandingthebasicdesigntermshelpsyoudesignbetterreportsandcommunicatewithpublishersandtypesetters.


TerminologyManyof thebasic termscomefromtypesettingandgraphicsdesign.ThetermsshowninFigure6.24willhelpyouunderstandreportwritersandproducebetterreports.Thefirststepistochoosethepagelayout,intermsofpapersize;orien-tation(portraitversuslandscape);andmargins.Thetypeofbindingsystemwillaffectthemargins,andyoumighthavetoleaveanextraguttermargintoaccom-modatebinding.Thenextstepistochoosethetypefaceandfontsize.Ingeneral,seriftypefaces

areeasiertoread,butsansseriffaceshavemorewhitespace,makingthemeasiertoreadatlargerandsmallersizes.Avoidornamentaltypefacesexceptforcoversandsomeheadings.Columnsofnumbersaregenerallyprintedatafixedwidthtokeepcolumnsaligned.Specialfixed-widthtypefaces(e.g.,Courier),inwhichallofthecharactersuseexactlythesamewidth,areespeciallyappropriateifyouneedtoaligncolumnsofnonnumericdatawithouttheuseoftabstops.Fontsizeisgenerallyspecifiedintermsofpoints.Mostcommonprintedmate-

rialrangesfrom10-to12-pointfonts.Ausefulruleofthumbisthatacapitalizedletterina72-pointfontisapproximately1inchtall.Somereportsystemsmeasuresizesanddistanceinpicas.Apicais1/6ofaninch,orthesameheightasa12-pointfont.Ifyourreportsincludegraphsandimages,theterminologybecomesmorecom-

plex.Be aware that the quality of bitmap images depends on the resolution oftheoriginalimageandtheresolutionoftheoutputdevice.Commonlaserprint-ershavea600-dots-per-inch(dpi)resolution.Typesetterstypicallyachieveabout2,400-dpi resolution.An image that looks good on a 600-dpi lasermaybe toosmallortoojaggedona2,400-dpitypesetter.Ifyourreportsareincolor,youquicklyencounteradditionalproblems.Inpar-

ticular, colorsonyour screenmaynotbe the sameason theprinter.Similarly,asamplereportprintedonacolorinkjetmightlookcompletelydifferentwhensubmittedtoa typesetter.ThePantone®colorstandardisdesignedtominimizetheseproblemsbyprovidingnumbersformanystandardcolors.Advancedsoft-warealsosupportsgammacorrectionthatyoucanapplytoyourmonitorsothatcolorsdisplayedonyourmonitorwillmatchthosefromtheprinter.Therelatedissueyouwillencounterincolorprintingistheneedtocreatecolorseparationsforallofyourreports.Forfull-colorsubmissionstoprintshops,eachreportpagewillneedfourseparatecolorsheets.DenotedCMYKforeachofthethreeprimarycolors—cyan(blue),magenta(red),andyellow—andthekeycolor(black).Inthiscase,eachpagewillneedhigh-resolutionalignmentmarksso thecolorscanbereassembledproperly.Oneofthefirstelementsofdesignthatyoumustlearnistokeepyourreports

simpleandelegant.Forinstance,sticktoonetypefaceandoneortwofontsizesonapage.Useplentyofwhitespacetohighlightcolumnsandfeatures.Lookattheentire layout toobservewhere theeyeswillbeattractedandhow theywillmove.Mostimportant,sincedesignstylecontinuallychanges,examinenewspa-perandmagazinelayoutsregularlyfornewideasandpatterns.

Basic Report TypesFrom the perspective of data layout, there are essentially three types of reportdesigns:tabular,groupsorsubtotals,andlabels.Thechoiceyoumakedependsonthetypeofdataanduseofthereport.Allreportwriterssupportthesethreebasicformats.Manyprovideoptionstocombinevariouselements.


Tabular and Label ReportsThetabularlayoutshowninFigure6.25isthesimplestreportdesign.Itbasicallyprints columnsof data,much like the output of a query.The advantageover asimple query is that the tabular report can print page headings and page num-bersoneverypage.Youalsohavealittlemorecontroloverfontsizeandcolumnwidth.Tabularreportsaregenerallyusedfordetailitemlistings,suchasinventoryreports.Notethatthesortorderbecomescrucial,sincethesereportswillbeusedtosearchforspecificitems.Ontheotherhand,thesereportsdonotcontainmuchinformation formaking decisions. In general, printed versions of these reportsshouldnolongerbeneeded.Itisgenerallyeasiertouseaformorreporttolookupthespecificitemsneededinsteadofcarryingthemaroundonpaper.AsshowninFigure6.26,labelsarealsostraightforward.Theessenceofala-

belreportisthatalloutputforonerowofdataisprintedinone“column”onthepage.Thenthenextrowisprintedinthefollowingcolumn.Thenamelabel report comesfromtheuseofpreprintedorprecutpagesusedforlabels.Thesereports

Customer

CID Phone First Name Last Name Address ZIP1 Walkin Walkin 2 (808) 801-9830 Brent Cummings 9197 Hatchet 968153 (817) 843-8488 Dwight Logan 1760 Clearview 021094 (502) 007-0907 Shatika Gilbert 4407 Green 403425 (701) 384-5623 Charlotte Anderson 4333 Highland 581026 (606) 740-3304 Seeroba Hopkins 3183 Highland 403307 (408) 104-9807 Anita Robinson 8177 Horse Park 950358 (606) 688-8141 Cora Reid 8351 Locust 410739 (702) 533-3419 Elwood Henson 4042 West 8912510 (302) 701-7398 Kaye Maynard 5095 Sugar 19901

Figure 6.25Tabularreportlayout.Tabularreportshavefewoptionsbutaregoodfordetaileddatalistings.Theyareusedforitemizedlistingsofdata.

Dwight Parrish9904 Plum Springs RoadWorcester, MA 01613

Dwight Logan1760 Clearview StreetBoston, MA 0210

David Sims6623 Glenview DriveBoston, MA 02216

Hershel Keen8124 Industrial DriveNashua, NH 03080

Reva Kidel5594 Halltown RoadBangor, ME 04401

Dan Kennedy3108 Troon CourtBurlington, VT 05401

Sharon Sexton2551 Elementary DriveBarre, VT 05641

Kelly Moore6116 Clearview StreetMiddlebury, VT 05753

Cassy Tuck7977 Fairways DriveClifton, NJ 07015

Figure 6.26Labelreportlayout.Thisthree-upreportiscommonlyusedtoprintonlabelpaper.Itcanbeusedforsmallsetsofdatathatneedtobeprintedacrossapage.


aresometimesnamedbasedonthenumberofphysicalcolumns.TheexampleinFigure6.24hasthreelabelsacrossapage,soitisathree-upreport.Beforereportwriters,printingalabelreportwasquitechallenging,sincetheprintercouldonlyworkfromthetopofthepage.Hence,youhadtowriteaprogramthatprintedthetoplineforthreedifferentrowsofdata,thenreturnandprintthesecondline,andsoon.Today’sprintersaremoreflexible,andreportwritersmakethejobeasy.Keepinmindthat labelreportscanbeusefulforother tasks—wheneveryou

wanttogroupdataforonerowintoseparatelocationsonapage.Forinstance,byinsertingblank rowsandchanging the label size,youmightcreatea tic-tac-toepatternofdata. It couldbean interestingeffect foracoverpageoradvertisingsheet,butavoidusingsuchpatternsforhundredsofpagesofdata.Groups or SubtotalsThemostcommontypeofreport isbasedongroupsandcomputessubtotals.Italso provides themost flexibility over the layout of items on the report.Com-monexamplesincludeprintingareceiptorabill.Manytimesthereportwillprintseveral rowsofdata, like theorder formshown inFigure6.27.Eachorder forthemonthisprintedinonereport,buttheitemsaregroupedtogethertoshowtheindividual order subtotals.Many people refer to these reports as control breakreports.Thekeytothesubtotalreportistonotethatitincludesbothdetailitemlistings

(itemordered,quantity,cost,etc.),andgrouportotaldata(orderdate,customer,andordertotal).Tocreatethisreport,youfirstbuildaquerythatcontainsthedatathatwillbedisplayed.TheexamplewouldprobablyincludetheOrder,OrderItem,Merchandise,Customer,Employee,andSuppliertables.Becareful:Ifyouwanttoseethedetail,donotincludeaGROUPBYstatementinthequery.Ifyouex-amine the rawdata from thishugequery,youwill see a largenumberof rowsandcolumns—manywithrepeatingdata.Thatisfineatthispoint,butnotexactlywhattheuserwantstosee.Theobjectiveofthereportistocleanupthedisplayofthedata.

Figure 6.27Grouporsubtotalreport.Notethatseveralordersarebeingprinted.Eachorderisagroupandhasadetailedrepeatingsectionofitemsbeingordered.Thereportcancomputesubtotalsforeachorderandatotalfortheentirereport.


Tocreateagroupedreport,examinethereportdesignshowninFigure6.28.Thislayoutpageshowsthegroup breaksinthedataandspecifiesthelayoutofeachelementonthepage.Again,layoutissetbytheindividualcontrols.Thecontrolshavepropertiesthatcanbechangedtoaltertheappearanceofthedatadisplayedbythatcontrol.Forexample,youcansetbasictypefaceandfontattributes.The basic elements of a report are headers, footers, group breaks and detail

areas.Thereport headercontainsdatathatisdisplayedonlywhenthereportisfirstprinted,suchasacoverpage.Similarly,thereport footerisusedtodisplaydataattheendofthereport,forexample,summarystatisticsorgraphs.Thepage header and page footeraredisplayedoneverypagethatisprinted,exceptforthereportheaderandfooterpages.Pageheadersandfooterscanbeusedtodisplaycolumnheadings,pagenumbers,corporatelogos,orsecurityidentifiers.Thereportfeaturesthatdefinethistypeofreportarethegroups.Theexample

showninFigure6.28hasonegroupdefined:MerchandiseOrder.PONumber.Thereportwillbreak,orcreateanewgroupofdata,foreachPONumberinthequery.NoticethateachOrdercancontainmanyitemsordered.Thereportdesignspeci-fiesthattheserowswillbesortedbytheItemIDnumber(withineachorder).Eachgroupingcanhaveagroupheaderandagroupfooter.Thegroupheaderdisplaysdata that applies to the entireorder (e.g.,Date,Customer,Employee, andSup-plier).Italsoholdsthecolumnlabelsforthedetail(repeating)section.Thegroupfooterdisplaysthesubtotalforeachgroup.ThecommonusesofeachreportelementaresummarizedinFigure6.29.Note

that all of the elements (exceptdetail) canwork inpairs—headers and footers.Youarenotrequiredtouseboth.Forinstance,youmightchoosetodisplaypagenumbersinapageheaderanddeletethepagefootertoprovideadditionalspaceonthepage.Forcomparison,Figure6.30showsasimilarreportcreatedusingOracle’sre-

portwriter.Noticethattheoverallstructureisthesame.Shading,boldface,anda

Report HeaderPage HeaderGroup Header1 Group Header2 … Detail … Group Footer2Group Footer1Page FooterReport Footer

Figure 6.28Reportlayoutforgroups.Notethebasicreportelements(reportheader,etc.).Alsonoticethatthepagelayoutissetbythepositionandpropertiesofthedatacontrols.InthesamplereportforMerchandiseOrders,onlyonegroupisdefined(ontheOrdernumber).


highlightcolorhavebeenusedtoimprovetheappearanceofthereport.However,it isstilla littlecramped,andcouldbenefitfrommorewhitespace.Ultimately,youwouldasktheuserstofocusthereportonaspecificitem,andthenimprovethelayouttohighlightthatitem.Inthisversion,thedetaillistingishighlighted,butifthetotalvalueoftheorderismoreimportanttotheusers,youwouldneedtoreorganizethisreport.

Report Section UsageReport 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.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.

Figure 6.29Commonusesforreportlayoutelements.Mostelementsareavailableinpairs,butyouarefreetodeleteanycomponentsyoudonotneed.

Figure 6.30GroupreportcreatedandpreviewedwiththereportwriterinOracle.Shadingisusedtoshowthegroupheadersandahighlightcolordrawsattentiontothedetailheading.


Figure6.31showsthedesignviewfortheOraclereport.First,noticetheover-all structure iscontrolledby thesections,whichareshaded in thedesignview.Second, observe that the individual data elements are displayed using text boxcontrols.Thesecontrolsaresimilartothetextboxesonaform,butonlydisplaydata.Youcansetpropertiestochangetheformat,font,orlayoutofthedata.Fi-nally,checkoutthehierarchicaltreelistingontheleftside.Itshowsthecompletestructureofthereportandmakesiteasytofindandselectindividualsectionsandcontrols.

Figure 6.31GroupreportcreatedandpreviewedwiththereportwriterinOracle.Shadingisusedtoshowthegroupheadersandahighlightcolordrawsattentiontothedetailheading.

Report of Orders

Report footer: graph orders by customer

Group1: CustomerH1: Customer name, address, …

F1: Customer total orders:

Group2: OrderH2: Order#, Odate, Salesperson.

F2: Order total: Sum(Extended)

Detail: Item#, Qty, Extended

Figure 6.32Nestedgroups.Forexample,eachcustomercanplacemanyorders,andeachorderhasmanydetaillines.Twogroupsareused:(1)toshowthetotalordersforeachcustomerand(2)toshowthetotalvalueofeachorder.


Groupsrepresentone-to-manyrelationships.Forexample,eachordercanhavemany items in thedetail section. If thereare severalone-to-many (ormany-to-many)relationshipsinthedata,youmightwanttousemultiplelevelsofgroups.AsillustratedinFigure6.32,eachgroupisnestedinsideanothergroup,withthedetailattheinnermostlevel.Tocreatethisreport,youmustbuildaquerythatcontainseveryitemthatwill

bedisplayed.Beginbyfocusingonthedetaillevelandthenjoinadditionaltablesuntilyouhaveallthecolumnsyouneed.Youcanusecomputedcolumnsformi-norcomputationssuchasPrice*Quantity.Becareful toavoidaggregate func-tions(e.g.,Sum)andavoidtheuseofGROUPBYstatements.Theonlytimeyoumightincludethesetwofeaturesisifyour“detail”rowisactuallyasubtotal(oraverage)itself.Groupreportsaregenerallyusedforcomputations—particularlysubtotals.In

general,computationsononerowofdatashouldbeperformedwiththequery.Ontheotherhand,aggregations(Sum,Average,etc.)arehandledbythereportwriter.Reportwritershavedifferentmethodsofdefiningthescopeoftheoperation—thatis,whatdatashouldbeincludedinthetotal.

ChartsReportsare increasinglyusedforanalysisandto identifypatternsandtrends inthedata.Asaresult,userswantchartstohelpthemvisualizethedata.Charts on reportsaresimilartographsonforms.Thefirststepistodecidewiththeuserwhattypeofgraphwillbestillustratethedata.Thesecondstepistodeterminewherethechartshouldbepositionedwithinthereportelements.Ifyouaregraphingde-tailitems,thenthegraphbelongsinthedetailsection,whereitwillberedrawnfor

Figure 6.33Samplegraphonthesalesreport.Itillustratestheportionofthesalespentonanimalsversusmerchandise.NotethatthegraphappearsonthesamelevelastheSaletable—notonthedetaillevelandnotonthereportfooter.


everyrowofdata.Ifitisasummarygraph,itbelongsinagroupfooter,orperhapsinthereportfooterifitsummarizesdataacrosstheentirereport.Onceyouhavedeterminedthetypeandlocationofthegraph,youbuildaquery

tocollectthedata.Thisquerycanbedifferentfromthequeryusedtoproducetheoverallreport.Inparticular,whenthegraphisinagroupfooter,youmightneedtouseaggregationfunctionsinthequeryforthegraph.Besuretoincludeacol-umnthatlinksthegraphtothedatainthereport—evenifthatcolumnwillnotbedisplayedonthegraph.Figure6.33showsonesaleontheSalesreportforthePetStore.Thetotalsforthegrapharecomputedbyaseparatequery.

SummaryFormsmustbedesignedtomatchtheuser’stasksandmakeyourapplicationeasytouse.Tomeetthisgoal,youneedtopayattentiontodesignprinciples,operatingsystemguidelines,andhumanlimitations.Wherepossible,youshouldbuildtheformtousedirectmanipulationofobjects,suchasdraggingitemsfromoneloca-tiontoanothertosignifyshipment.Formsarebasedontablesorqueries.Eachformhasasinglepurposeandcan

storedatainonetable.Morecomplexformscanbecreatedbyplacingsubformsontothemainform.Controlsontheformareusedtoenterdataintothetables,per-formlookupfunctions,andmanipulatedata.Severalstandardcontrolsareavail-ableforaWindowsenvironment(e.g.,textboxes,comboboxes,andoptionbut-tons).Additionalcontrolscanbepurchasedtohandlemorecomplextasks,suchascalendarsforschedulingandthree-dimensionalimaging.Reports are generally printed and differ from forms because reports are de-

signedonlytopresentdata,nottocollectit.Thereareseveraltypesofforms,butmanybusinessformsrelyonsubtotalsorgroupingstodisplaydifferentlevelsofdata.Forexample,asalesreportmightbegroupedbysalesdivisionorsalesper-sonorboth.Youuseaquerytocombinealldataitemsneededforareport.Therearetwobenefitstousingareportwriter:(1)Itisastraightforwardwaytosetdataformatsandalignment,and (2)The reportcan includedetail listingsaswellassubtotalsandtotals.

A Developer’s ViewAsMirandanoted,thedatabasewizardscancreatebasicformsforyou.However,beforeyoucrankuptheformwizardandgeneratehundredsofsmallforms,thinkabout the tasksof theusersand theoveralldesign.Try toput themost importantinformationononecentral formwitha fewsecondaryforms tohelp.Striveforaclean,well-organizedscreenandusecolorsandgraphicssparinglytoenhancetheappearance.Youshouldalsodevelopadesignstandardandlayoutfortheapplica-tiontoensureconsistency.Justbesuretoleaveroomforcreativity.Foryourclassproject,youshouldbegincreatingthebasicformsandreports.


Key Terms

accessibilityaestheticscheckboxclaritycommandbuttonconsistencycontrolsdata-boundcontrolsdirectmanipulationofobjectsdrag-and-dropdropdownlisteventfeedbackfocusgroupbreakheads-downdataentry

humanfactorsdesigninputmasklabeloptionbuttonpagefooterpageheaderreportfooterreport headerresourcefilesingle-rowformsubformformstartupformtabordertabularformtextboxUnicode

Review Questions1. Whichhumanfactorsareimportanttoconsiderwhendesigningforms?2. Howcanyoumakeyourapplicationsaccessibletoawidergroupofworkers?3. Howareinternationalissueshandledonforms?4. Whataretheprimaryformtypes?5. Whatarethemaincontrolsyoucanuseonforms?6. Whyareupdateablequeriesanissuewithforms?7. Whatisthepurposeofsubforms?8. Whatisthepurposeoflinkedforms?9. Whatarethemainreporttypes?10.Whataretheprimarysectionsofreports?

Exercises1. Researchandreportonthestepsneededtocreateamulti-languageversion

ofaformusingMicrosoft.NET(Webversion).OruseJavaifyourinstructorprefers.

2. ReviewthedocumentationfortheDBMSyouareusingandidentifythefeaturesthatitprovidestosupportaccessibilityforallusers.

3. ReviewthedocumentationfortheDBMSyouareusingandidentifythemainformscontrolsthatitprovides.

4. ReviewthedocumentationfortheDBMSyouareusinganddescribehowreportscanbeaccessedviatheWeb.


Forthefollowingquestions,createthedatabasesandbuildtheformsfortheexer-cisesfromchapters2and3.

5. Nurseschedule,Chapter2,Exercise1.6. Propertytaxassessor,Chapter2,Exercise2.7. Musicstoremarketing,Chapter2,Exercise3.8. Translationservice,Chapter2,Exercise4.9. Cleaningcompany,Chapter2,Exercise5.10.Clubevent,Chapter2,Exercise6.11. Networkmanagement,Chapter2,Exercise7.12.Healthjournal,Chapter3,Exercise1.13. Pipelineinspection,Chapter3,Exercise2.14.Distributordeliveries,Chapter3,Exercise3.15.Corporateflights,Chapter3,Exercise4.16.Corporategiftsales,Chapter3,Exercise5.17.Biologyresearch,Chapter3,Exercise6.18. Farmingsystem,Chapter3,Exercise7.

Sally’s Pet Store19.Createformstohandletheadministrationofthemerchandiseinventory,

includingformsforBreed,Category,andMerchandisetables.20.Createaformtorecordordersofanimalsfromsuppliers.21.Createformstorecordordersandreceiptofmerchandisefromsuppliers.22.Createaformtodisplayachartofmerchandisesalesbythemaincategories.23.Createaformthatenablesmanagerstoselectastartingandendingdateand

thendisplayachartshowingtotalmerchandisepurchasesoverthattimeperiodforeachsupplier.

24.Createareportthatdisplaysanimalpurchasesbysupplier.Includeachartthatcomparesthetotalpurchasesfromeachsupplier.

25.Createareportthatdisplaystotal(merchandiseplusanimal)salesbyemployeefortheweek.Includeachartshowingthepercentageofsalesbyeachemployeefortheweek.

26.CreateareportthatdisplaystotalmerchandisesalesbyCategorybymonth.


Rolling Thunder Bicycles27.BuildaversionoftheBicycleformthatisbasedonsimplemaster/detail

subforms.Comparetheresulttotheexistingform.Whatarethestrengthsandweaknesses?

28.Createaformtodisplayagraphofbicyclesales(valueandcount)bymodeltypeforaspecifiedtimeperiod.Thetimeperiodshouldbeenteredastextboxesontheform,withamethodtoredrawthegraph.

29.Createareportthatdisplayseachstate,theassociatedstorewithinthatstate,andthetotalsalesmadewiththehelpofthatretailstore.Includetotalsalesbystate.

30.Createareportthatdisplayssalesofbicyclesbymodeltypebymonth.Includeachartthatcomparestotalsalesbymodeltype.

31.Createareportthatdisplayssalesofbicyclesbyemployeebyyear.Includechartsforeachyearthatcomparethesalesacrossemployees.

32.Createareportthatchartstheaveragelengthoftimetobuildeachbikemodelbymonth.

33.Createaformthatenablesmanagerstoselectacomponentandgenerateachartthatshowsthepurchasesandsalesofthatcomponentovertime.

34.Managerswanttoseehowthepopularityofcolorschemeschangesovertime.Createareportthatchartsthenumberofbicyclespaintedineachcolorschemebymonth.Youmightneedaseparatechartforeachcolorscheme.

Corner Med 35.AddanobjectcolumntothePatienttabletoholdaphoto.AddthePhotofield

tothePatientform.Runtheformandaddsomestockphotosofpeople.36.Createareportthatprintsabillforapatientvisit.37.Createareportthatdisplaysalistofallpatientvisitsfortheweekby

physician.Includeachartattheendtoshowtheamountofrevenuegeneratedbyeachphysician.

38.Createareportthatdisplaysachartforaspecifiedpatient.Itshouldincludeallvisitsinchronologicalorderandlistssymptomsandtreatmentsateachvisit.

39.Createareportthatlistsallinsurancecompanybillingsandreceiptsbymonth..

40.CreateareportthatliststhecountofthenumberofpatientsbymonthundertheprimaryICDdiagnosiscategories.Themajorgroupingsarebasedonthetableonthefollowingpage.

Corner

Med

Corner

Med


Web Site References

http://www.microsoft.com/enable Accessibility guidelines.http://www.unicode.org Primary site for Unicode information.http://www.acm.org/sigchi/ Association for Computing Machinery—Special

Interest Group: Computer and Human Interaction.

http://www.acm.org/sigcaph/ Association for Computing Machinery—Special Interest Group: Computers and the Physically Handicapped.

Additional ReadingCooper,A.AboutFace:TheEssentialsofUserInterfaceDesign.FosterCity,CA:

IDGBooks,1997.[Agooddiscussionofvariousdesignissues.]Ivory,M.andM.Hearst,TheStateoftheArtinAutomatingUsability,

CommunicationsoftheACM,33(4),December2001,470-516.[Generaldiscussiononevaluatingsystemusability.]

MicrosoftCorporation.TheWindowsInterface:AnApplicationDesignGuide.Redmond:MicrosoftPress,1992.[Animportantsetofdefinitionsand“standards”thatdesignersshouldfollow.]

Raskin,J.HumaneInterface,The:NewDirectionsforDesigningInteractiveSystems,Reading,MA:Addison-Wesley,2000.[TheneedforanewinterfaceasexplainedbythecreatoroftheAppleMacintoshproject.]

Tsichritzis,D.FormManagement,CommunicationsoftheACM,25(7),July1982.[Basicconceptsofdatabaseforms.]

ICD9 Code Category001-139140-239240-279280-289290-319320-389390-459460-519520-579580-629630-677680-709710-739740-759760-779780-799800-999V01-V85E800-E999

1. Infectious and Parasitic Diseases2. Neoplasms3. Endocrine, Nutritional, Metabolic Diseases, and Immunity Disorders4. Diseases of the Blood and Blood-Forming Organs5. Mental Disorders6. Diseases of the Nervous System and Sense Organs7. Diseases of the Circulatory System8. Diseases of the Respiratory System9. Diseases of the Digestive System10. Diseases of the Genitourinary System11. Complications of Pregnancy, Childbirth, and the Puerperium12. Diseases of the Skin and Subcutaneous Tissue13. Diseases of the Musculosketletal System and Connective Tissue14. Congenital Anomalies15. Certain Conditions Originating in the Perinatal Period16.Symptoms,Signs,andIll-DefinedConditions17. Injury and PoisoningSupplementaryClassificationofFactorsInfluencingHealthStatusSupplementaryClassificationofExternalCausesofInjuryandPoisoning

http://www.microsoft.com/enable

http://www.unicode.org

http://www.acm.org/sigchi/

http://www.acm.org/sigcaph/

dbmspost-chapter06-401

Documents