Reading Summary and Analysis Discussion Post: Exploring Information Systems

9

2 Database Structure for Everyone: Records, Fields, and Indexes

Whether you are using this book in an upper-level database searching course or an entry-level intro to reference course, it’s likely that you have or will have the opportunity to take a true “database” course. This means that some of you may already be familiar with the concepts in this chapter. My goal is to focus on helping you learn and develop strategies to search and interact more effectively with library databases rather than getting into the real technology of how databases are built. This chapter provides a brief and simple introduction to how databases are conceptually put together. In my experience, this is as much as you need to know to apply appropriate search techniques and use the database effectively. There’s no point in piling on technical detail if it doesn’t further your ultimate goal, which in this case is searching.

Database Building Blocks

Fields, Records, and Tables

In essence, databases are made up of fields and records. Fields are like one cell in an Excel spreadsheet: a bit of computer memory dedicated to hold- ing one particular type of information, one value. For example, an age field might hold the value 28. The type of information could be text, numbers, or an image. A set of fields makes up a record, the idea being that the informa- tion in all the fields of one record relate to one thing: a person, a company, a journal, a purchase order, etc. An analogy would be a row in Excel: one row equals one record. But while you could have an Excel file with 5000 rows

C o p y r i g h t 2 0 1 5 . L i b r a r i e s U n l i m i t e d .

A l l r i g h t s r e s e r v e d . M a y n o t b e r e p r o d u c e d i n a n y f o r m w i t h o u t p e r m i s s i o n f r o m t h e p u b l i s h e r , e x c e p t f a i r u s e s p e r m i t t e d u n d e r U . S . o r a p p l i c a b l e c o p y r i g h t l a w .

EBSCO Publishing : eBook Collection (EBSCOhost) - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA AN: 1197818 ; Bell, Suzanne S..; Librarian's Guide to Online Searching: Cultivating Database Skills for Research and Instruction, 4th Edition : Cultivating Database Skills for Research and Instruction Account: s5672194.main.ehost

10 Librarian’s Guide to Online Searching

(records), and 30 columns (all the different fields), such a file wouldn’t ulti- mately be very efficient to search, and definitely isn’t scalable (it would not, actually, be a database, but only a “flat file”). Enter the idea of relational da- tabases, which are structured with tables. It’s like having many Excel work- sheets that can have indefinitely many rows, but only a few columns (fields). One of the fields in every table is dedicated to a unique identifier, which ties

Figure 2.1. DMV relational database example: tables, fields, and complete record.

Personal Dat< ID Number 12345678 23456789 98765432 [etc.]

3 Table Last Name Smith Jones K e p l e r

First Name John Martha J o h n

M l Q A T

DOB 19451121 19950401 19620714

Gender M F M

Eye Table ID Number 12345678 23456789 98765432 [etc.]

Eye color Blue Brown Grey

Corrective Lenses Y N Y

Address Tabk ID Number 12345678 23456789 98765432

3

Street 123 Main St 60 Merriman St 238 Bayview Dr

City Clyde Rochester Greece

State NY NY NY

Zip 14433 14607 14612

[etc.]

Photo Table ID Number 12345678

23456789

98765432

[etc.]

BadPic Driving Histo ID N u m b e r 12345678 23456789 98765432

ry Table Years D r i v i n g 53 3 34

Accidents 2 1 0

[etc.]

"Show me the complete record for J o h n T. Kepler"

I D #

98765 432

Last N a m e Kepler

First N a m e John

MI

T

DOB

19620 714

Gen.

M

Street

238 Bayview Dr

City

Gre ece

Sta te NY

Zip

146 12

Eyes

Grey

Lens es Y

Yrs Drv 34

Ace.

0

PicpIC

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 11

together all the material relating to the same person, company, etc. together. All of that material now represents a record. The table structure (and some additional features we will touch on presently) make possible the desired storage efficiency and speed of access even for huge amounts of information.

Think about driver’s licenses. They all have an ID number, the owner’s name, address, date of birth, eye color, a bad photo, etc. All of that informa- tion undoubtedly resides in a database administered by the state agency that cares about driver’s licenses. It’s easy to imagine the Department of Motor Vehicle’s database having fields with names such as ID #, Name, Addr, DOB, Eyes, BadPic, etc. The fields are probably located in several tables: one for address information, one for driving history, one for the photo, etc. Pulled together by the ID number field, those fields make up records, each one of which represents a person (Figure 2.1).

The fields in the complete record represent every bit of information that appears on your license, and probably some that isn’t actually printed on the license as well. When you send in the paperwork and the check to renew your license, they look you up in the database by your ID number, make any changes that you might have indicated in your paperwork (e.g., change the values in your fields), and hit print. Presto, you’ve gone from being a data- base entry to being a small card with an unflattering photo.

Decisions, Decisions: Designing the Database

From here on, I’m going to discuss databases only in terms of fields and records, leaving the “tables” aspect out. In the real world, yes, what is behind the interface you are looking at is almost undoubtedly a relational database, built on tables. But those tables are simply fields that make up mini-records. At essence what matters are the fields, and how many of them you need to create a complete record.

And indeed, the crucial task in developing a database is deciding what fields the records in your database are going to have, and how big they are going to be, that is, how many characters or numbers they will be able to hold. This “size” represents the computer memory allocated every time a new record is added. (Although memory is cheap now, in a huge project, how much memory will be allocated is still something to consider.) In the best of all possible worlds, a whole design team, including software engi- neers, subject experts, people from marketing and sales, and potential us- ers, would wrestle with this problem. Nothing might ever get done in such a large and varied group, however, and so probably a more limited team of software engineers and content experts is the norm. The problem is that the design team had better make good choices initially, because it can be dif- ficult, if not impossible, to make significant changes to the record structure later.1 This is good and bad. It means there’s a certain inherent stability, or at least pressure on these database products not to change too much, but when you wish that they would fix something, it can take a long time for change to happen. You can take a certain amount of comfort, though, in the knowledge that however much the interface to the database—the way it looks—changes, behind the scenes the same types of information (fields), are probably still there.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

12 Librarian’s Guide to Online Searching

Food for Thought

For an article database, you’d probably have a field for the article title, the name of the journal it appeared in . . . and what else? Think about the other information you would want to capture. Again, the process is some- thing like this:

Define your fields (Figure 2.2): . . . that make up records (Figure 2.3) . . . . . . and form the basis of your database.

Quick Recap

In this section we have described the structure of databases in very simple terms and compared it to the structure of an Excel spreadsheet. The most basic elements of a database are fields and records. (Technically, the fields are usually structured in the form of tables, with one field in each table acting as the “unique key” to pull all the information relating to one record together.) A full set of fields makes up a record. Every record in the database has the same set of fields (even if, in some records, some fields are blank). All of the records together make up the database.

Beyond Fields and Records

Field Indexes

Fields and records are the basis, the “data” of a database. What makes a database fast, powerful, and efficient are the indexes of the fields. It would be very slow if every time you queried the database, it started at field1, record1, and searched sequentially through each field of each record—you might as well go back to hard copy at that rate.

An index, in the sense that we’re discussing now, is a list of all the values from a particular field, with some kind of identifier indicating from which record each value came (a pointer if you will). This is much like the

Figure 2.2. Database fields.

Figure 2.3. Database records.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 13

way the index at the end of a book indicates on which pages a word appears. In one sense, creating indexes of fields breaks one cardinal rule of databases: not to duplicate any data. But this one kind of duplication is worth the re- dundancy and extra storage space, because combined with sophisticated algorithms indexes make it possible to locate and retrieve the records as- sociated with specified values in nanoseconds. The field indexes become part of the database but have a separate existence from the records. (You could think of them as really minimal table structures: just two columns, one with the values for field X, and the other containing a pointer back to the record that each field X value came from.) Again, the power of an index is that it can be sorted and in other sophisticated ways optimized for searching.

Let’s return to the driver’s license example. It has a field for Last Name. You’d definitely want to create an index to that field, so you’d have your com- puter program harvest all the values from the Last Name field, along with the associated ID Number value for each one. Given that the data is textual (a name), you’d probably want to sort the index alphabetically.2 Then if you wanted to find the record for Smith, John, your computer program could zip to the Ss in the Last Name index list (and then to the Js in the First Name index), find a set where the ID Numbers matched, and based on that pull up the full record for Mr. Smith. And do this all in much less time than it takes to write about it. Using indexes to find records also means that the order of the rows in your database, that is, what order you enter your records, doesn’t matter at all. You simply build an index and search that when you want to find something in your database. Or you can build several indexes; you can make an index of any field you want. However, as always, there are costs and reasons why you might not index every field.3

A Very Simple Example

Say we have three articles:

Milky Way’s Last Major Merger.

Science News. v. 162 no. 24 p. 376

It’s a Dog’s Life.

The Economist. December 21, 2002. p. 61

Manhattan Mayhem.

Smithsonian. v. 33 no. 9 p. 44

Let’s enhance these just a little by adding a one-line description to each re- cord (so that we have a few more words to search on):

Record 1:

Milky Way’s Last Major Merger.

Science News. v. 162 no. 24 p. 376.

New clues about galaxy formation indicate early collision affected Milky Way’s shape.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

14 Librarian’s Guide to Online Searching

Record 2:

It’s a Dog’s Life.

The Economist. December 21, 2002. p. 61.

From hard labour to a beauty contest, a history of the work and whims of dog breeding.

Record 3:

Manhattan Mayhem.

Smithsonian. v. 33 no. 9 p. 44

Martin Scorsese’s realistic portrayal of pre–Civil War strife—Gangs of New York—re-creates the brutal street warfare waged between immigrant groups.

My database will have just four fields (Figure 2.4):

1. Record number (four-number places, e.g., my database will never grow to more than 9,999 articles)

2. Article title (50 characters allocated)

3. Journal name (50 characters allocated)

4. Abstract (200 characters allocated)

Now let’s index the fields. The initial list of words from the Article Title field looks like this:

Milky

Way’s

Last

Major

Merger

It’s

a

Dog’s

Life

Manhattan

Mayhem

Figure 2.4. A very simple database record.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 15

More Database Decisions

There are various things about this list that one might question. What will our indexing program do with those possessives and contractions? Do we want to clog it up with little words like a? There are many decisions for database designers to make:

• How will the indexing program handle apostrophes and other punc- tuation? We take it for granted now that the system will simply pre- serve it, and users can search for contractions or possessives, but you may still encounter systems that insert a space instead of the apostrophe (dog s), or ignore it and treat the letters as a string (end- ing up with “dogs” for “dog’s”).

• What will the indexing program do with the “little words”? That is, words such as a, an, by, for, from, of, the, to, with, and so forth, which are usually referred to as stop words. These are words that are so common that database designers usually decide they don’t want to expend time and space to index them. Indexing programs are pro- grammed with a list of such words and will “stop” indexing when they hit a word on the list. A more descriptive term would be skip words, because that is what really happens: the indexing program skips any stop list words and continues to the next word. Almost all databases employ a stop word list, and it can vary greatly from one vendor to the next. (Even Google has stop words, words it doesn’t index.)

• Should the system be designed to preserve information about capi- talization, or to ignore the case of the words? We are so used to sys- tems that do not distinguish upper and lowercase (so that you don’t have to worry how you type in your query), but there are times when you would really like the system to know the difference between, say, AIDS (the disease) and aids (the common noun or verb).

Because this is a modern system, we’ll decide to preserve the apostro- phes and to make a one of our stop words, so it won’t be included in the index. We can then sort the list alphabetically:

Dog’s

It’s

Last

Life

Major

Manhattan

Mayhem

Merger

Milky

Way’s

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

16 Librarian’s Guide to Online Searching

Can you see the problem here? We have neglected to include an identi- fier to show which record a word came from. Let’s start over.

Better Field Indexing

Let’s make sure that our index list includes the record number and which field the word came from:

0001 Milky TI

0001 Way’s TI

0001 Last TI

0001 Major TI

0001 Merger TI

0002 It’s TI

0002 Dog’s TI

0002 Life TI

0003 Manhattan TI

0003 Mayhem TI

One more thing: we can include a number representing the order of the word within the field (why might this be useful?). We now have something like this:

0001 Milky TI 01

0001 Way’s TI 02

0001 Last TI 03

Now we’ll sort again.

0002 Dog’s TI 03

0002 It’s TI 01

0001 Last TI 03

0002 Life TI 04

0001 Major TI 04

0003 Manhattan TI 01

0003 Mayhem TI 02

0001 Merger TI 05

0001 Milky TI 01

0001 Way’s TI 02

Note how even though we deleted the stop word a in the title “It’s a dog’s life,” the numerical position of “dog’s” reflects that there was an inter- vening word there: its position is recorded as 3, not 2.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 17

Because people might want to search on the name of the publication, it would be good to index that as well. Our index of the Journal Name field looks something like this:

0002 Economist JN 02

0001 News JN 02

0001 Science JN 01

0001 Science News JN 01, 02

0003 Smithsonian JN 01

Note the multiple indexing of Science News. The technical term for this is double posting.

To make things even faster and more efficient, after indexing each field, combine the indexes so that you have only one list to search:

0002 Dog’s TI 03

0002 Economist JN 02

0002 It’s TI 01

0001 Last TI 03

0002 Life TI 04

0001 Major TI 04

0003 Manhattan TI 01

0003 Mayhem TI 02

0001 Merger TI 05

0001 Milky TI 01

0001 News JN 02

0001 Science JN 01

0001 Science News JN 01, 02

0003 Smithsonian JN 01

0001 Way’s TI 02

We undoubtedly want to index the content of the one-sentence “ab- stracts,” as well. Here is a list of the words in raw form:

new beauty Pre-Civil

clues contest War

about a Strife

galaxy history Gangs

formation of Of

indicate the New

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

18 Librarian’s Guide to Online Searching

early work York

collision and Re-creates

affected whims The

Milky of brutal

Way’s dog street

Shape breeding warfare

From Martin waged

hard Scorsese’s between

labour realistic immigrant

to portrayal groups

a of

Decisions and cleanup are needed on this list of words:

• Stop words—what will they be?

• Hyphenated words—how will they be recorded?

• Proper names—“double post” to include the phrase too?

• Alternative spellings—do we do anything about them or not? (What might you do?)

Luckily, software does almost all of this work for us. You probably will never see any indexes in their raw state. What we’ve been going over here is in real life very under the hood, often proprietary material for the database vendors. You don’t need to know exactly how any particular database works; you simply need to grasp some of the basic principles that govern how data- bases in general are put together and how they are indexed. This determines how you search them—and what you can expect to get out of them.

Quick Recap

This section discussed the idea of field indexes and the importance of good planning in the design of huge databases. Field indexes refer to the idea that the values in a database’s fields can be extracted and put into their own lists that consist of just the value and a pointer back to the record it came from. These indexes exist separately from the records in the database, and make rapid, efficient searching of huge databases possible. Much thought goes into the initial database design (i.e., what fields to include, what they are called, how much space to allocate for each one), because the design cannot be easily changed later. Many decisions go into the design of indexes as well, for example, which fields will be in- dexed, how contractions and possessives will be handled, which words will be treated as stop words, and if and how identification of phrases will be supported.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 19

Examples of Indexes in Common Databases

In the examples that follow, see if you can relate what we’ve just gone over with how the field indexes are presented to you as a user in these common databases. We’ll start with two multidisciplinary databases: Aca- demic OneFile from Gale, which has a single Subject list, and EBSCO’s MasterFILE Premier, which offers separate Subjects, Places, and People indexes. Last, we’ll consider the very elaborate indexing used by OCLC’s WorldCat.

Gale’s Academic OneFile: One Subject List

Academic OneFile, one of the Gale Company’s “Infotrac” suite of data- bases, prominently offers a Subject Guide Search. If you choose the Subject Guide Search from the navigation bar, useful Search tips are displayed on that interface page (Figure 2.5). The tips text suggests using this search mode “when you want to browse a dynamic list of topics, people, products, locations, organizations and more.”

Once you have searched for a term, then you can browse forward through the list, as long as there are headings containing your search term somewhere within them. But the Gale system doesn’t offer unlimited, free- form browsing capability, unlike MasterFILE’s true browse access (i.e. Mas- terFILE presents you with the very beginning of whichever index list you choose, and you could, if you wanted, simply page through—browse—the whole thing without any searching at all). An advantage to the Gale subject list over MasterFILE’s is that it is all-in-one: you don’t have to think about the nature of what you’re looking for (Is it a subject? a person? a place?). You can look once and know for sure whether the topic you’re looking for is there or not. For example, Academic OneFile at the time of this writing

Figure 2.5. Representation of the Academic OneFile Subject search interface.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

20 Librarian’s Guide to Online Searching

does not appear to have any articles on the singing group Chanticleer. The only Subject Guide entry is for Chanticleer and the Fox, which is helpfully glossed “(Novel).” Being an all-in-one list, these parenthetical notes are very useful. Other examples include “(Planet),” “(Medication),” “(Motion Pic- ture),” the names of sports (to distinguish the different “World Cup” events), and various others. In the Subject Terms results list, links to Subdivisions and Related Subjects are provided if applicable, as well as “See” entries to get you to the term Gale has decided to use (e.g., “Coffee addiction See Cof- fee habit”). If one is willing to slow down enough to look through the list of Subdivisions, it is well worth it, as examining the list can make finding articles on exactly the aspect of [topic x] very easy and efficient. Looking at the Subdivisions for Coffee (Beverage) provides an excellent example: just looking for how much coffee is consumed? Try the Subdivision Consump- tion data. Environmental aspects, Market share, Prices, Research, Risk fac- tors, Statistics—the Gale indexers have done an excellent job identifying the kinds of things people look for most often and which can be hard to find without the human intervention of applying intelligent subject headings. The Gale subject list also includes the number of results for every heading and subdivision, which is extremely helpful. Being able to see the count lets you know that Academic OneFile is probably a good place to find articles about the “Health aspects” of coffee (945 results in May 2014), but perhaps not for learning more about “Diseases and pests” of coffee (only 4 results in May 2014).

Figure 2.6. Initial Subjects Index interface in EBSCO’s MasterFILE Premier. © 2014 EBSCO Industries, Inc. All rights reserved.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 21

EBSCO MasterFILE Premier: Subjects, Places, & People Indexes

Even in a fairly simple display, there is a lot to look at and look for. In this view of the subject index interface in EBSCO’s MasterFILE Premier (Figure 2.6), A is the area identifying where we are: who is providing the database, which database it is, and a search box to collect the results of our choices from the Subjects, Places, and People indexes.

The section marked B tells us we are accessing the Subjects lists: there are separate indexes for the Subjects, Places, and People fields. The interface to these index lists allows us to simply start at the very beginning of the list and browse forward, page by page, or to jump to any point in the index by searching on a word or phrase, with the option of having our search term at the beginning of the Subject entry (“Term begins with”), or anywhere within it (“Term contains”). The third option, “Relevancy ranked,” will return all of the Subject headings containing your search term arranged by relevance rather than alphabetically (although it is hard to tell how “relevance” is be- ing determined).

Figure 2.7. The Subjects Index in MasterFILE Premier, showing the beginning of the “coffee” entries. © 2014 EBSCO Industries, Inc. All rights reserved.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

22 Librarian’s Guide to Online Searching

In Figure 2.7 we see the results of searching the subject list for the word coffee. The entries in all caps are values from the field designated as subjects in this database: terms the EBSCO indexers have chosen from the predetermined list of subject headings for this database, that they feel cap- ture the essence of the article’s content. We will talk more about this idea of the “predetermined list” of subjects in chapter 3, but for now, just tuck away the idea that entries in the Subjects list are not random: the indexers have deliberately compiled this list of terms.4 Thus every article about the his- tory of coffee is assigned the subject: COFFEE—History. But, as indicated by the helpful “Use” note, if you are looking for articles about the cultivation of coffee, rather than “COFFEE—Cultivation” you should “Use COFFEE growing.” The number of results for each entry is not provided, which is a bit annoying.

The “Places” list contains, obviously, names of places that have been the subject of articles in this database, helpfully glossed with the name of the country or state where they are located to disambiguate them (e.g., “abbe- ville (ala.),” “abbeville (france),” and “abbeville (la.)” etc.). In the “People” list you would find, obviously, names of people, but also of orchestras, musical groups, and musical events, all glossed with the parenthetical note “(per- former)” (e.g., “boston early music festival (performer)”). The entire content of the Places and People lists is lowercase, which seems a little odd, but in both of these lists the number of records for each entry is provided, which is very helpful.

Field Indexes for the WorldCat Database

Moving on to our third example, OCLC’s WorldCat database (a union catalog of library holdings from around the world) provides even more ex- amples of the use of separate indexes for many fields. As in EBSCO’s Sub- ject, Places, and People lists, the WorldCat Browse Index interface provides the opportunity to roam around in the indexes, discovering what is there (and thus, what is possible), before committing to a search. Some fields (such as Author) are even indexed twice, in separate lists, creating one index for single words only, and another for phrases. Figure 2.8 provides a drawing of the initial view of the Browse Index interface (A), and an example of a single-word and a phrase index for the same field. In the part of the draw- ing marked B, the drop-down menu has been changed to Author, and in C to Author Phrase. In the Author (single-word) index, you could browse only for an author’s last name, for example, Austen. In the Author Phrase, you could browse specifically for Austen, Jane.

You access the Browse Index screen via an icon in the WorldCat Ad- vanced Search interface, discussed in greater detail in chapter 7. For now, simply observe how it works.

Using the Subject Indexes

Figure 2.9 provides a stylized representation of looking up the word librarians in the Subject index of the WorldCat database. (Note that the

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 23

dropdown for choosing which index you want to browse has been changed to Subject, a single word index.)

The lower part of Figure 2.9 represents the results of searching this index. In this stylized drawing, I have only written out the terms of most interest and highest counts. The other entries represented by “[term]” are almost always odd spellings or outright typos,5 and have counts in the single digits. The Count column indicates how many records in the data- base have been assigned that Subject. Since the Count numbers change steadily, I have represented them with hash marks indicating the size of the number. When you are actually using WorldCat, these count numbers provide a rough indication of the content of the database, and how useful it might prove for the topic you’re working on. In this case, WorldCat ap- pears to have a wealth of material on librarians (plural) and librarianship, but far fewer entries for “librarian” in the singular. Last, observe that the term we searched for appears in the middle of the list, and is in bold. Why do you think the database designers have chosen to display the results this way?

In Figure 2.10, we see the results of a search for information retrieval in the Subject Phrase index. This drawing uses the same conventions as in Figure 2.9. Were you to get online and browse forward in this list, you would find literally hundreds of entries beginning with the words: “information retrieval.”

Figure 2.8. Representation of the initial Browse Index screen for the WorldCat® database and examples of single-word and phrase indexes offered for some fields.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

24 Librarian’s Guide to Online Searching

Record Structure Reflected in Fields Displayed

As a reminder, indexes are built from the fields included in a database record. The fields can be called the record structure, and you can get a sense of how simple, or elaborate, a database’s record structure is by studying the fields displayed when viewing a record from the database.

The WorldCat database has quite an elaborate record structure; these database designers were making sure that they didn’t leave anything out, and that the most complete set of bibliographic information they could as- semble would be available to users. The OCLC interface designers have the task of conveying a large amount of information as clearly as possible.

Get online, and look up the record for your favorite book in WorldCat. Take time to study the full record display, noticing how the designers have used different fonts, colors, and alignments to convey meaning. Notice how the field names are lined up on the left, followed by colons, and the contents of the fields appear to the right. Find the section labeled “Subject(s),” and notice that the terms below are labeled “Descriptor.” (We will encounter some odd terms for subject headings in the course of this book.) Some of the fields may seem quite mysterious, but think about the purpose of the others, and why the database designers might have decided to include them. WorldCat has been

Figure 2.9. Representation of results from the single-word Subject index.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

Database Structure for Everyone: Records, Fields, and Indexes 25

around since 1967, and has probably struggled to adjust its record structure ever since to stay abreast of developments. If the WorldCat database had been invented today, the database designers might have made different choices.

Exercises and Points to Consider

1. What would your ideal database record for a journal article look like? Choose any article that interests you, and design a database record for it, keeping in mind that what you do for this one article you will do for every other article (how much do you expect your database to grow?). What fields will you use? How big will each field be? What will you call the fields? Sketch out what the overall database would be like (and why this article would be included), and justify your choices.

2. Why do you think WorldCat has separate one-word and phrase in- dexes for the same fields?

3. What is a useful piece of information that is provided when you browse the indexes (in Academic OneSource and WorldCat)? How might this affect your search strategy?

Figure 2.10. Representation of results from the multiple word Subject Phrase index.

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use

26 Librarian’s Guide to Online Searching

4. Using the MasterFILE Places index, look up: mars. What is EB- SCO’s preferred term for the “place” Mars? Switch to the Subject index, and look up: self acceptance (no hyphen). Notice how EBSCO clearly and easily gets you to the right form of the term, for this and so many other “self-” entries.

5. Can you do a field search with Google?

6. People generally think of Google as indexing all the words of all the web pages that it visits.6 With a few exceptions (it recognizes the markup codes for the page Title, for example), it offers only one huge index labeled “all the text.” Why do you think the commercial data- base vendors go to so much trouble to provide an elaborate record structure with indexed fields?

7. In the early days of online searching in libraries, only librarians per- formed searches, after a detailed and careful interview with the patron requesting the search. The librarian would plan the search carefully, and then “dial up” to connect to the database, using a password and employing a very terse, arcane set of commands to perform the search. Access fees were charged by the minute, with additional charges for records viewed. Try to picture this scenario, and then compare it with the situation today. How do you think the totally open, end-user ac- cess has affected databases and their interfaces? Can you think of anything about the current situation that is not an improvement?

Notes

1. If you’re wondering why it would be so hard to change the record structure, remem- ber to think of these databases as huge things: true, adding a new field to a database of just five records would be trivial. But a database of 500,000 records? How are you possibly going to retrospectively fill in the new field for all the existing records?

2. Computer scientists can cringe here. I’m sure it would actually be something much more sophisticated than a simple alphabetic sort.

3. For one thing, the process of initially building the index can take hours. Although this does not mean that it can’t be done, remember that every index has to be up- dated frequently to reflect any changes in your database. It just adds to the complex- ity of the whole operation.

4. And each vendor’s list is different. EBSCO has decided rather than “Coffee (Bever- age),” the subject should just be “Coffee” in the MasterFILE database. Meanwhile, the folks over at Gale decided that “Coffee (Beverage)” was preferable to simply “Cof- fee,” and that’s the preferred subject heading in Academic OneFile.

5. Go online and take a look: these are odd and sometimes amusing entries. What this shows is that the indexing process, that is, the harvesting of terms from the Subject field, is done by a computer program: it simply picks up whatever is there, typos and all. (The errors come from the humans who typed the values into the field. For fallible humans, they are impressively accurate.)

6. Does Google have stop words, words that it ignores? And does it really index every page all the way to the end? Does this matter?

EBSCOhost - printed on 2/4/2022 6:35 PM via UNIVERSITY OF BRITISH COLUMBIA. All use subject to https://www.ebsco.com/terms-of-use