Database Software. What Is a Database, and How Does It Work?

Database Software

What Is a Database, and How Does It Work?

A database is a collection of information about a particular subject or purpose. It is organized in such a way that users can retrieve information quickly and easily. One of the best-known database examples is the telephone book. Each person or business with a publicly listed telephone number is shown, in alphabetical order, by city. Each entry in the phone book shows the person’s last name, first name or initials, street address, and telephone number. Some directories also list the postal code.

The phone company has much more information about each customer on file. For example, it has billing information showing how much a customer owes each month and when the last payment was made. In a product file, the company knows which of its services the customer uses—such as Call Display or Call Waiting.

For a customer who uses the company’s long-distance service, it has a file showing the telephone numbers dialled and the length of each call. The common thread throughout these files is the telephone number. Although the phone company collects different pieces of information and keeps them in different places, it can also pull all these pieces of information together to create a complete profile based on just a phone number. By defining how each file is related (the telephone number), the data needs to be entered only once.

This kind of database is known as a relational database.

THE ANATOMY OF A DATABASE

Before creating a database, you must first understand the various components that make up the database.

1. Record—A record is all the information relating to an individual item or person in a database. For example:

• all the information collected about a person or organization by the telephone company

• all the information collected about you by your school or school board

• information about each CD in your CD collection (e.g., title, artist, year of release)

Each record has its own unique number within the database.

This is the ID number, or record number, for that record. As well, each record contains the same types of information. In Figure 8.17 on the next page, you can see examples of records shown in FileMaker Pro. Each row contains a different record.

Figure 8.15 Sample of a relational database. A relational database has many parts connected by one element—your student number, for example.

Student No.

00631264

Report Card Timetables

Personal Information

Course

Selections Attendance

Figure 8.16 A database file is made up of files, which are made up of records. Each record contains numerous pieces of information in fields.

Files

I

DATABASE

H G

F E

D C

B A

Fields

name street address city

Records

2. Field—Within each record are the pieces of information you have collected. Each piece of information is contained in a field.

In FileMaker Pro, fields are the columns in the table. Using your CD collection as an example, you might want to record the CD title, the recording company, the artist’s name, and the songs on each disc. You might also want to include your own comments about each song. Each of these bits of information is entered in its own field or column.

3. Data type—For each field, you must choose what type of data you will enter. Types of data you can specify depend on your software. Some examples of field data types are text (the default data type), date, time, memo, and yes/no fields. Yes/no fields are useful for when you want to enter only one of two options.

For example, you might want to record a person’s language preference as English or French. Yes could be assigned to either language. If you are planning on performing any calculations on the data, you must define the data type as numeric or currency, depending on what the data will contain. In number fields, you can even specify the format (e.g., currency), which will help to streamline data entry into the database (e.g., dollar signs can be added automatically to the amounts). Some database programs even allow you to enter calculations in fields with formulas similar to those used in your spreadsheet software.

4. Field size—For each field, you must determine how many characters will be needed for the data being entered. The default field size in Microsoft Access is 50 characters. If the data is longer than that, you can adjust the field size to accommodate your data after the field is created.

Once you determine the field names, data type, and size for each field, you enter them into the field definition or database design window.

Again, each database program functions slightly differently, so you must familiarize yourself with the program’s requirements. Using FileMaker Pro, your database might look like Figure 8.17 below.

Figure 8.17 CD database, FileMaker Pro

UNIVAC

One of the first computerized database uses was for the 1951 U.S. Census. In that year, the American government used the new UNIVAC (Universal Automatic Computer) to input and process data. UNIVAC was the first computer to use magnetic tape. It read both alphabetic and numeric data at 7200 digits per second.

QUICK BYTE

ENTERING INFORMATION INTO A DATABASE

To enter data into the database, you key the data for each field within the table, then tab to the next field when finished. Some database packages will automatically assign a number to the records. Once you have entered the data into one record, you either click New Record (FileMaker Pro) or press Tab (Microsoft Access) to create the next blank record ready for data entry. The record number is indicated at the side or bottom of the screen. In Figure 8.18, at the left side of the screen, the 1 indicates the record number, and the total number of records is 3. There are navigating buttons beside the record number that allow you to move from record to record.

Figure 8.18 Database form in FileMaker Pro indicating the current record number

Record Number

R E V I E W I T ! AND D O I T !

1. What is a database? Give an example of a database that you have used.

2. What is a relational database? What makes it so powerful? What type of relational database might have your name and personal information in it?

3. What are records? What are fields?

4. When specifying the data type for each field in a record, what are your options? When would you choose each data type?

5. Why would you have to change the field size?

6. Brainstorm all the organizations or places that collect personal information. What kind of information is collected? How do you think the people who collect the information organize it? Is there any common thread that ties the information together? Compare your list with those of other members of your class.

7. Explore the database software in your school, and find out the steps required to set up an address book for your friends and relatives. Set up the database with fields for first name, last name, address, city, province, postal code, telephone number, birth date, and e-mail address.

Some people are more comfortable entering information on a form within the database rather than a table. A form is an electronic version of a document used to collect data. Fields have blank areas for data entry, just like on a printed form. The process for entering data is the same as in a table. You tab from field to field. When a record is complete, you start a new one on a fresh screen. FileMaker Pro

GETTING INFORMATION OUT OF A DATABASE

• Find/queries—A database can become quite large by the time all the data is entered into the records. If you want to view only certain information, most databases allow you to design a query (or Find function) to do this.

Suppose you want to find out how many songs by Some Guy are in your CD collection and on which CDs you would find the songs. You would design a query to find each occurrence of the artist Some Guy, listing the song title and CD title, in your database in alphabetical order. In Microsoft Access, the Query screen would look like Figure 8.19.

• Reports—The other way to view information is in the form of a report. A report looks more like a word-processed document, but it is created by the database program.

You can create a report based on the entire database, or only those records generated by a query.

In Figure 8.20, the songs in our database are grouped by CD title, in alphabetical order by artist.

Figure 8.20 Reports generated from a database present the data in an organized way.

Figure 8.19 Microsoft Access Select Query is asking for a list of the records in the database with Some Guy as the Artist. When run, the query will display only the records by that artist in alphabetical order by song title and CD title.

Now, you will look at a different way to handle the data you have collected for your Chapter Project. Print a copy of your spreadsheet (not the chart), and use it to write rough planning notes for your database.

Identify which information in the spreadsheet could be used for database fields. What type of data will be entered for each field—text? numbers? date? Why?

Do you need other fields that are not listed here?

If so, what are they?

Once you have determined the fields you need and the data type, open a new file in your database software. Name it My Movie Database. Set up the database structure defining the fields and data type.

Enter the data into the records before moving on to the next section in the text.

G E T O N I T ! PROJECT UPDATE

automatically creates a database form for data entry after you define the fields. You can even customize the look of the form through the Layout Mode to make it resemble a more familiar paper-based form.

You are almost there! Now that you have the

information in your database, you can do a number of things with it. Do each of the following to practise getting information out of your database:

• Search your database for the movies starring your favourite actor.

• Create a report of the movies sorted in date order (most recent movie first).

• Create a report grouped by movie studio with the name of the movie, year it was released, and the box-office receipts. If you can, do a subtotal for the box-office receipts for each studio.

• Prepare another report of your own design.

Save these reports in your database for review in the final stage of the project at the end of the chapter.

G E T O N I T ! PROJECT UPDATE

I N V E S T I G AT E I T ! ETHICS

Have you ever heard that clothes tell a lot about a person? Well, now they might just be saying more than you know. A technology called RFID (Radio Frequency IDentification) is being used by many businesses to track products as they move from the manufacturer to the consumer. One example is the 407 ETR in the Greater Toronto Area, which uses RFID to determine when and where you drive onto the 407 and where you get off to be able to bill you for the use of the toll road.

Clothing manufacturers are incorporating RFID devices into shoes, bags, jeans, and countless other consumer goods. If you were scanned with all these items on your person, the store would not know your name, but they would know all about what you are wearing. If you were to come back into the store later, they would know it was you! If that is

not spooky enough, consider that if you used a credit card, they would know that you have a credit rating, and, with a little more work, they could find out your identity. Your personal privacy might have been given away by your clothes!

Questions

1. Does your school have a policy that keeps data about students confidential? Find out about the school policy from your school principal.

2. For a 24-hour period, keep track of all the people you and your family interact with, websites you visit, and tasks you perform that require you to identify yourself in some way. Note what information you or your family members are asked for (e.g., telephone number, birth date, social insurance number). Make a list of all the information that is being collected about you and your family members. Bring your list into class, and compare it with lists of your classmates.

Figure 8.21 RFID allows manufacturers and clothing stores to trace you by the clothes you buy and wear.

YOUR CLOTHING MIGHT BE GIVING YOU AWAY!

I N V E S T I G AT E I T ! ETHICS

For as long as organizations have collected data, they have also analyzed it for many reasons, often to be more successful and profitable. For example, grocery stores know when to order larger quantities of soft drinks based on the date and time of year. When you purchase a DVD online, vendors often suggest other titles based on your selection. How do they know? They have analyzed the thousands of purchases that have gone through their site, looking at patterns or trends in the data. However, it is not just sales information that they analyze.

What other types of data do organizations collect? In order to be able to increase sales, businesses want to know as much as they can about potential customers. They collect consumer data, which includes everything from customer purchase history to credit card numbers to personal information (including salary) to their level of satisfaction with the shopping experience. Computers have made large-scale data collection and analysis possible. This is called data mining. But what are these organizations mining? They are looking for information about relationships between pieces of data that might help them increase sales.

Some organizations sell the information they learn about their customers to other organizations. Many think this is an invasion of customers’ privacy.

One Internet filtering software company sold information about students’

Web-surfing habits to other companies. This company faced such a huge backlash from the public that it stopped the practice immediately.

The growing number of databases and the ability to share information are raising serious ethical questions. Ken Badley, in Worldviews: The Challenge of Choice, asks the following questions about issues of privacy:

• Should a bank sell information about your buying habits to a mail-order retailer without your knowledge and permission?

• Should information about your driving record be made available to prospective employers?

• Should your school-attendance record be given to prospective employers?

• Should insurance companies have access to your health records?

Questions

1. Do you think it is possible to prevent sharing of information from different databases? Discuss in small groups.

2. What steps would you suggest?

DATA COLLECTION

BIZTEK DVD

Mining Your Business 1:

Gathering Consumer Information

Your name, address, and contact information are valuable to some businesses, and they are often bought and sold without your knowledge.

In this clip, Marketplace shows how new parents are targets of this kind of questionable information gathering. After viewing the clip, devise a list of things to look for when a company asks for your personal information. What red flags would stop you from giving that information?

BIZTEK DVD

Mining Your Business 2:

Loyalty Programs

Retail loyalty programs that promise you prizes and savings are so popular that 76 percent of Canadians belong to at least one. What do the companies do with the information collected through these programs? After viewing the clip, create a list of the sorts of fields that a loyalty-marketing database would have to include. You may want to use a loyalty program you are familiar with as an example to think this through.

BUSINESS APPLICATIONS FOR DATABASES

A database is a powerful tool for collecting information for many purposes in business. Information relating to customers, suppliers, product inventory, customer accounts, and purchases are just some of its uses. Integrating all these pieces of information into a relational database can provide business decision makers with valuable information they need to survive in a competitive environment.

You have now completed all the research necessary to do your analysis/comparison on the similarities and differences between spreadsheets and databases. You have created and formatted a spreadsheet of the movie data, and you have performed calculations on some of the values in the spreadsheet. In the same program, you created a chart of the box-office receipts for the movies. You then created a database of the same data, performed queries, and created a report of the data.

Before you move to the next step, print out the various spreadsheet and database components so they are readily available.

The final stage of your project is to write a short one- or two-page report outlining the differences and

similarities between spreadsheets and databases. (Refer to Chapter 6 for step-by-step instructions on writing a business report.) Include a short introduction that outlines what you will be comparing. Your report should discuss what you can use each one for. Can you see overlapping uses? Are there unique features that would make one more useful than the other for certain tasks?

Use examples from your various project tasks (movie spreadsheet and database) to help illustrate your points. Be sure to include a conclusion that summarizes your findings. Edit and proofread your report. Include all printouts from the various tasks assigned throughout the chapter as illustrations and supporting

documentation to accompany your final report.

G E T O N I T ! PROJECT UPDATE

R E V I E W I T ! AND D O I T !

1. In what formats can you enter information into a database? Why might one method be preferable over the other?

2. What is a find/query, and how does it work?

3. What is a report, and what does it look like?

4. Do reports have to be created from the entire database? What other options do you have?

5. What are some of the common business uses for databases?

6. Use a paper and pencil to plan a database for a small-service business (e.g., hairdresser, personal trainer, lawn care, computer-repair shop). Determine the fields (name, data type, and size) you would need to keep track of sales to various customers.

Do not forget the different product categories the business might sell. Once you have determined the necessary fields, use your database program to design a form in which to input your data.