11
Essential Software
SUE SCHNEIDER
One objective of this essay is to provide an understanding of the widely used categories of software as well as how one might select software for one’s own use. The most commonly used categories and considerations for software selection and use are discussed, with an emphasis on databases and database software.
Another objective is to highlight the many usages of the term “database.”
Under the name “database,” one can buy a container of sets of related data, a software development product, or an application or system. It is up to the consumer to understand how the seller is using terminology.
In today’s workplace most companies purchase a suite of software prod- ucts. The minimal suite contains word processing, spreadsheet, presentation, and database software. Most personal computers purchased by individuals come with a suite of software products already installed; more than 85% of the time it is a Microsoft suite of products targeting personal or business use.
Software Categories
Word processing software facilitates the creation, editing, storage, and pub- lication of documents. Word processing software stores the material one types, plus various hidden codes for formatting the document, in a specialized file in a format unique and proprietary to the vendor of the software. Besides text paragraphs, sections, chapters, and so on, documents can contain embedded tables as well as other objects such as images, spreadsheets, and graphs. When word processing software starts up, it is ready for you to start typing your research paper, letter, report, essay, or book. As we moved through our formal education we had many opportunities to learn to express ourselves with the written word. Word processing software helps us store and publish our written communications.
Databases, Spreadsheets, Word Processing,
and Presentation Software
Spreadsheet software facilitates the creation, storage, and publication of worksheets. Worksheets are specialized files, composed of rows and cells, used to process numbers via formulas and functions, create graphs and charts from the numbers, and create financial, statistical, and other mathematical reports. Spreadsheets deliver the ability to perform complex mathematical functions, analyze statistics, and graph results quickly and easily. Spread- sheet software stores the worksheets in specialized files with a format unique and proprietary to the vendor of the software. If our education or experience has led us into bookkeeping or accounting or analysis of numerical informa- tion, we had to learn about some form of ledger sheets. Worksheets are an automated form of ledger sheets.
Presentation software facilitates the creation, storage, editing, preview- ing, and showing of slide shows. Presentation software helps one build a slide show presentation, enables one to insert images, text, spreadsheet cells, graphs, and so on into the slides, provides a place to store presentation notes, and prints handouts. Presentation software stores the slides to be used in the pre- sentation in a specialized file with a format unique and proprietary to the vendor of the software.
Database software is used to build databases and user interfaces to those databases. “Database” is a widely used and imprecise term. In this discussion, a database is a facility where data is collected and stored for the purpose of generating information and knowledge needed to solve problems. The actual set of procedures and displays used by that facility is called a “User Inter- face”. Databases are organized, accessible, structured, and secured to varying degrees. Below are some descriptions of databases.
Example 1. A generic office file—a filing cabinet of paper files—is a data- base. It is organized by the files’ tab contents, by topic or alphabetically.
Access and security are handled by physical control devices, such as locations and drawer locks. Its structure is the physical folders. The folder content may or may not have a specific sequence, structure, or standard format. This data- base is updated by adding, deleting or changing folder contents by whoever has access and motivation. The user interface is called “filing.”
Example 2. The campus telephone directory, a soft-cover book, is a data- base. It is organized by person or entity name. There are few access or security restrictions. It has a simple name/number structure presented in a standard format. This database is updated only by a single entity, the pub- lisher, but it can be read by all (a read-only database). In addition to look- ing up a number in the book, another user interface to the databases, called the operator or directory assistance, is available via the telephone.
Example 3. The Laboratory Information System (LIS) provided by Our Vendor is referred to as a database, as are various components of the LIS.
Technically, it is more accurate to refer to this item as an “application” or system; i.e., OurVendor Corporation’s proprietary software tools and pro- gramming languages are applied to the significant task of supporting the processes of operating and administering clinical and anatomic laborato- ries. It has both a physical and a logical structure. Both structures are
highly complex, sensitive to change, and require specialized resources to maintain.
This application’s database contains hundreds of files organized by nu- merous keys, such as patient identification number, encounter number, acces- sion number, and so on. It is accessible via a personal computer, secured with passwords, and requires training to access the information sought. The com- plex user interface is composed of various menus, screens, and reports whereby patients are registered, orders are placed, tests are resulted, and charts are printed. There is also a user interface especially for administering the system itself as well as to help administer the department workload.
The user interface provided by this version of OurVendor’s LIS is an example of a style of interface—a DOS-based interface versus the GUI (graphical user interface) used with Windows or Macintosh operating systems. The user interface provided on a newer version of OurVendor’s LIS is a graphical user interface.
Example 4. The Pathology Department’s budget spreadsheet is a database.
It is organized by worksheet name; its accessibility and security are per- formed via access to the Pathology network; and its structure determined by the author of the spreadsheet.
Database software products are not databases. Database software prod- ucts are tools or tool kits used to build databases and/or entire applica- tions. Use of a database software product enables one to do the following:
· Design the organization, accesses, and structure for data to be collected and retrieved.
· Define data security measures and their implementation.
· Enforce data integrity within its files.
· Design and develop interfaces for people to interact with the database to enter data, to perform a function, to generate information, to develop knowledge.
Various vendors, such as Microsoft, Oracle, IBM, Sun, Apple, et al., market multiple lines of software products as databases. There are many technical and proprietary differences among these vendors that are beyond the scope of this discussion. However, a database that will be updated by several people concurrently may have to be deployed under a more robust database than what is delivered by Microsoft Access to most desktops or home computers.
In this situation what is wanted is a database that is highly reliable and pro- vides security or locking at the record level (when someone is updating a record, the record is not available for another to update).
These more robust or enterprise-level software products cost significantly more to acquire and require qualified technical assistance to develop and administer. Three of the more popular products are Microsoft’s SQL Server, IBM’s DB2, and Oracle’s product line. Different products have different fea-
tures and trade-offs, such as power versus ease of use. A product may initially be selected for ease of use which over time is found to be criteria of lesser value than the criteria of power and scalability.
Other Useful Software
Graphics packages can be a helpful addition to suites of products. They make it possible to capture or edit photographs or other scanned images and graphics, as well as “screen shots” that can be embedded in training materials, reports, or presentations. When the standard graphics program is insufficient for particular tasks, an add-on advanced program may be used. Some vendors also offer multimedia products. Most scanners and digital cameras arrive with graphics software; however, one may be working with images captured by another party.
Advanced graphics programs directly support many different file formats and can read other file formats if the appropriate external import filters and drivers are installed along with associated scanner or digital camera hard- ware.
Project management software interacts with an individual planning or coordinating the effort needed to accomplish a change in the status quo. The individual identifies the tasks to be done, the time and resources needed for each task, task dependencies, and available resources. The software calcu- lates the critical path of the project and, when effort expended and project completions are input, calculates the status of the project. Timelines and Gantt charts can be printed, providing a framework for effective communica- tion among all concerned parties.
Formal project management is not consistently performed in many healthcare organizations. The level of project management followed is usu- ally relative to the project stakeholders’ perception of the magnitude of the project and/or appreciation of the consequences of its success or failure and/
or the experience of the project leader. Real project management is usually applied to large-budget projects with accountability such as mandated or contracted target dates.
Personal information manager programs help schedule time, manage contacts, track appointments and to-do lists, and record journal notes—re- placing the paper-based organizer.
Selecting Software Products
The first step in choosing a software product is to determine if a standard suite of software and standard hardware setup are in use at the company for which you work. If so, get that software and use it until such time as you determine that you have special requirements that cannot be met by that software. Using a standard setup does not preclude you from obtaining another vendor’s software for a spe- cialty or niche need. Acquisition and support of specialized hardware and soft-
ware is often a financial and a resource issue. Availability and experience of support staff may be a determining factor. Databases developed for and used in research tend to require more memory and a larger hard drive than the “standard”
personal computer. Also having the same setup on a home computer as at the office facilitates working from either location.
If your employer or primary network of associates does not have a standard software suite and you must make a selection yourself, then the first step is to identify your requirements—what you expect the software to do—in a list of questions. The more detailed the list, the more rigorous the selection process and the more time it will take, and the more likely you are to make a satisfac- tory choice. Next, prioritize and rank the importance of the items in the list.
Then ascertain by vendor or product how well they meet your requirements.
Some key questions to ask:
· “What do I want to do with this software?”
· “What does it cost?”
· “How well does this vendor’s software meet all my requirements or considerations?”
· “What kind of hardware do I have?” (IBM compatible PC, Apple, other)
· “What is the operating system installed on my hardware? And what version?” DOS, Windows 3.x, Windows9x or NT or 2000, Unix, Linux, and so on)
· “How will this software facilitate my working with others in my area as well as in my professional network?” This is a very important requirement.
“Can I easily exchange work products such as spreadsheets, documents, images, and reports with the majority of people with whom I work?”
Every vendor’s file format is unique and proprietary. Many vendors provide “drivers” so that other vendors can read or translate that vendor’s file format. Vendors frequently change file formats with each new version of their software. The new version will read older version formats but may write only the latest format. If you are the only one with the newest version of the software, no one else can open files you create.
· “When it breaks or when I’m learning to use it, where do I get help?” Most vendors provide a variety of help resources, from self-study and tutorial materials embedded in their software to onsite or telephone support representatives, as well as by e-mail and a knowledge base on the Internet.
When running the software and needing help there are many potential sources:
Look for the word “Help,” try the F1 key to find software documentation, position the cursor on a menu item and see if a Help tag appears, position the cursor on a menu item and right-click with the mouse to see if any options appear or see if you have a “Getting Started”manual.
Arrange to have a hands-on, demonstration of the software you expect to use the most. Try it out. Buyer beware: Not all “databases” are ready to use right out of the box.
Matching the Product to the Task At Hand
Suppose you want to create a table. You should determine which software is best suited to your task: word processing, spreadsheet, or database software.
All three can create and automatically format the table for you.
· For a table that includes complex formatting such as bulleted lists, custom tabs, numbering, or hanging indentations, use word processing software.
But if the table will be enhanced or need some specialized business rules applied to it, use database software.
· For a table that includes complex calculations, statistical analysis, or charts, use spreadsheet software. However, if your table entries will exceed 255 characters in length, do not use spreadsheet software; instead, use word processing or database software.
· For powerful sorting and searching capabilities, use either database or spreadsheet software.
· If you need to store and update more than 10,000 records or the data elements may expand, create the table in database software.
· For a table that can easily be included in a presentation, use word processing software.
In an integrated suite, the database software can publish or export a report or query for processing by the word processing or spreadsheet software.
When starting up word processing software, you expect to create a docu- ment and the software begins ready to accept typing, to create a document.
Word processing software replaces the typewriter. Spreadsheet and presenta- tion software similarly replace specialized office equipment, and what they do is implied in their names.
What office equipment does database software replace? Database software takes on a function performed by a highly trained, technical staff in a data processing or information services department and puts the components of that function onto the desktop in the business area. It is not automatically ready to accept data.
Database software is different from word processing and spreadsheet and presentation software. It requires instruction. To use database software, one must be ready to design a database using a technique called Relational Data- base Design and to design and develop a user interface with which to interact with the database.
Unless you’ve been specially trained or have specific experience, you may not be ready to design and develop a database and its user interface. Let’s review some of the techniques used to design a database.
1. Spreadsheet Design–This is a very common way for those accustomed to spreadsheets to design tables. The resulting tables typically have lots of re- dundant data and duplicate fields. Redundant data and duplicate fields cause problems when one tries to get data out of the database (do a query).
2. Old Software-Driven Design–The “old” database system, Paradox or dBase or FoxPro or OurVendor, dictates how the tables are structured because the tables are imported/copied into the new database. (Vendor advertising assures us that their products will convert these old databases, but the user interface is not automatically converted.) By doing an import or automatic conversion, the design limitations of the old databases come with them. Often redundant data was built into these tables so that it could appear on a single screen/form; modern database software does not have that limitation. Redun- dant data can cause problems in getting data out of the database or in updat- ing it. The problem with redundant data is that it usually means redundant data entry and a lack of data consistency and integrity within the database.
3. Flat File Design, or “Let’s Keep It All in One Table”– The problems with this type of structure are numerous: duplication, wasted space, no unique index, difficult or impossible to retrieve the data, and so on. Flat-file design is like creating a mansion from a two-bedroom ranch house, with extra floors and rooms and portable buildings added on without the use of an architect or building inspector. Databases with this type of structure are also called “flat files” because there is no built-in hierarchy or relationship between the records.
Figure 11-1 is an example of a flat file redesigned into a database table.
4. Multi-Valued Fields Derivative–Putting Address, City, State, and Zipcode—four pieces of data—all in one field becomes a problem when you want to sort by State or find out who lives in Texas City (see Figure 11-1).
5. Relational Database Design. Before starting to use a database software product, do the following:
· Step 1. Determine the purpose of the database and write it down on paper.
· Step 2. Review current methods of collecting and presenting the data (forms and reports) to be stored in the database. This is the current process’s user interface.
· Step 3. Identify the tables needed. Develop a list of data elements on paper, and group the data elements logically, as the data is in the real world. This identifies database objects (data elements and tables). You might have a table for Patients, which would contain data elements for each patient’s First Name, Last Name, Patient Identification Number, and so on. In addition to numeric and alphanumeric data types, you may have a data element that is an image data type or a memo data type (equivalent to 8 pages of text).
· Step 4. Finetune the tables by “normalizing the data.” Database design theory includes data design standards called normal forms. The process of making your data and tables meet these standards is called normalizing data, or normalization. Normalization is the process of putting things right. The Latin word, norma, identifies a carpenter’s tool for assuring a right angle. In geometry, a line at right angles to another is said to be
“normal” to another. In relational database design, normalization has a specific mathematical meaning having to do with separating elements of
data (such as Patient Identification Number, First Name, Last Name, Date Of Birth, Doctor Identifier Code, and Ordered Test) into affinity groups (tables) and defining the normal, or right, relationships between the groups.
Redundant information is eliminated and tables are organized (by you) to make managing data and future changes in table structure easier. If the tables are normalized, then each data element/table attribute will be a fact about the key, the whole key, and nothing but the key.
FIGURE 11-1. “Flat file” to third normal form.
The key is analogous to an index or a label. Keys are used by filing systems to access individual records. In the example Patient Table, Patient Identifier Number could be a key, as could the combination of First Name and Last Name. The Patient Identifier Number is the better candidate for a primary key, as it will be unique.
In normalizing data, tables are divided into smaller, easier-to-maintain tables. Then relationships between the tables are defined so that the database software knows how to join tables in the queries, forms, and reports that you also develop. Tables are represented as joined together by drawing lines be- tween them, such as between Patients and Orders, and a way is found to anno- tate that relationship as a one-to-many relationship.
To establish a relationship, the two tables must have a field in common.
This field must be the primary key in one table and may be a foreign key in another table. A primary key uniquely identifies each record in the table and is present for each record in the table. The University of Texas Medical Branch’s patient-identifying Unit History (UH) Number is an example of a primary key for a Patient table, whereas Accession Number and Ordered Test might be the primary key for the Orders table. The Orders table could contain the UH number as a foreign key to relate the order to the patient.
Having identified specific tables (things about which you want to store data) and the relationships between them, you now have a logical model of your database on paper. It will be checked out and tuned when you work through the next step.
· Step 5. Using imagination, intuition, industry standards, and knowledge of current methods of collecting and presenting data, think through the planned use of the database.
· Step 6. Design and build the user interface, menus, navigation paths, queries, forms, reports, and security that will be needed. There are articles, books, and courses on graphical user interface design as well.
· Step 7. Finally, go to the computer, turn on the database software and translate the logical model on paper to a physical implementation.
Traditionally, creating databases with a hierarchical or relational structure has been the purview of central data processing departments. Marketing of database software products, corporate administrators’ continuing belief in silver bullets and other magical solutions, a department’s need for responsive tactical information services, and market advances have moved the tools of relational database design from the central data processing department to individual desktops. However, planning for training in these tools is usually absent, resulting in a potential user who may or may not have a desire to be a database programmer being assigned to do database design and program- ming, to develop a database in Microsoft Access, and to install the database on a network. It could happen to you.
Additional Commentary on Medical Informatics
Expanding on the term “engineering” used in an earlier chapter, when you design and develop a database and its associated forms and reports (the user interface), you are using database software to build an application or tool or database for a specific need. You will be more successful following a develop- ment life cycle, a “methodology,” to develop an application. Find a method- ology that has proven successful in a situation similar to yours.
The methodology could include developing a prototype or full working model as well as concurrently or serially developing a more robust, fully tested and documented production system ready for deployment. A tool suit- able for quickly building a prototype or even a full working model may not be the tool that is suitable for building the more robust production version.
Quite often they are different. On the other hand, when a production database appears to be too expensive or difficult to sell, a lower-cost prototype can be developed to provide “proof of concept” to obtain the funding to build the production version.
When you get into the “deployment” activity mentioned in an earlier chapter, was the end product a prototype or a production model? It may be that the database needs to be ported (converted) to a more robust database product such as Oracle, SQL Server, or DB2 because it now has to support more concurrent user update or a higher volume, or it requires more security.
These database products can require more knowledgeable resources to use them effectively. It is possible to be redeveloping your database within the deployment phase.
Two other unique activities that arise during deployment are data conver- sion and loading as well as additional exception research and reporting. Fa- miliarizing yourself with the content of a methodology that has proven successful will help you be proactive in your engineering and deployment activities.
Summary
This discussion has briefly touched on some of the software most frequently used in business. As technology and ways of doing business are changing faster than ever, parts of this discussion referring to specific products and specific vendors could be out of date in less than two years. After all, the latest trend is to put yourself and your work “on the Web,” the Internet. Many software products assist you in doing that. The newer releases of UTMB’s standard software suite have the ability to publish a document, image, report, or graph in a form acceptable to a Web browser with a click of your mouse.
Developing technology using mobile devices with built-in cellular chips will allow you to access your database away from the office.
A major point to be emphasized is that buying a database software product does not get you as close to having an effective database as buying word processing software gets you to having an effective document. As we move through our formal education we have many opportunities to learn to express ourselves with the written word. Word processing software helps us store and publish our written communications.
This same formal education may introduce us to computers and to a pro- gramming language in the same manner as we are introduced to a foreign language. Courses to develop the skills needed to design and organize an application or a database are not usually part of the curriculum. Database design and user interface design are areas where you will need to either get more education or buy some experience, depending on your available time and resources.
Chapter Glossary
database: Any storage facility where data is collected for the purpose of generating infor- mation.
data normalization: Process of making data and tables match database design standards.
The objective of these standards is to eliminate redundant information to make managing data and future changes in table structure easier. If the tables are normalized, each data element/table attribute will be a fact about the key, the whole key, and nothing but the key.
presentation software: Software that interacts with an individual developing, editing, and arranging slides for presentations.
integrated software suite: Term used when all the components of the suite can exchange information with each other, requiring minimal effort for the individual using the soft- ware.
project management software: Software that interacts with an individual planning or coordinating the effort needed to accomplish a change in the status quo. The individual identifies the tasks to be done, the time and resources needed for each, task dependencies, and available resources. The software calculates the critical path of the project and, when effort expended and project completions are input, calculates the status of the project.
spreadsheet software: Software that interacts with an individual processing numbers, creating graphs and charts from the numbers, and creating financial, statistical, and other mathematical reports.
user interface: Methods and techniques used to interact with a computer and the software running on the computer at the behest of the user. There are two major types of interface:
character and graphical. A character interface utilizes the keyboard as its input provider and happens character by character, line by line. A graphical interface is visual and utilizes a mouse or other pointing device in addition to keyboard input and mouse events.
Everyone’s graphical interface may be different in appearance whereas all character inter- faces resemble one another. Windows and applications written for it present a graphical user interface.
word processing software: Software that interacts with an individual typing, changing, formatting, spellchecking, and printing textual documents.
