Ed4 0 Information Systems for Business User Guide
- October 27, 2023
- Ed4 0
Table of Contents
Information Systems for Business
Edition 4.0
Information Systems for Business
An Experiential Approach
Bélanger | Van Slyke | Crossler
Prospect Press
Information Systems for Business
An Experiential Approach
Edition 4.0 APPENDICES AK
France Bélanger
Virginia Tech
Craig Van Slyke
Louisiana Tech University
Robert E. Crossler
Washington State University
Copyright © 2022 Prospect Press, Inc. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system or
transmitted in any form or by any means, electronic, mechanical, photocopying,
recording, scanning or otherwise, except as permitted under Sections 107 or
108 of the 1976 United States Copyright Act, without either the prior written
permission of the Publisher, or authorization through payment of the
appropriate per-copy fee to the Copyright Clearance Center, Inc. 222 Rosewood
Drive, Danvers, MA 01923, website www .copyright.com. Requests to the
Publisher for permission should be addressed to the Permissions Department,
Prospect Press, 47 Prospect Parkway, Burlington, VT 05401 or email Beth.golub@
prospectpressvt.com.
Founded in 2014, Prospect Press serves the academic discipline of Information
Systems by publishing essential and innovative textbooks across the curriculum
including introductory, emerging, and upper-level courses. Prospect Press
offers reasonable prices by selling directly to students. Prospect Press
provides tight relationships among authors, publisher, and adopters that many
larger publishers are unable to offer. Based in Burlington, Vermont, Prospect
Press distributes titles worldwide. We welcome new authors to send proposals
or inquiries to Beth.golub@prospectpressvt.com.
Editor: Beth Lang Golub Production Management: Peter Holm, Sterling Hill
Productions Copyeditor: Carl Quesnel, Assurance Editorial Cover Design: Annie
Clark
eTextbook: Edition 4.0 ISBN: 978-1-943153-87-9 Available from Redshelf and
VitalSource
Printed Paperback: Edition 4.0 ISBN: 978-1-943153-88-6 Available from Redshelf
For more information, visit https://www.prospectpressvt.com/textbooks
/belanger-information -systems-for-business-4-0.
Contents
Appendix A: Computer Hardware Appendix B: Computer Software Appendix C: Access
Fundamentals Appendix D: Advanced Access Appendix E: Advanced Database
Concepts Appendix F: Excel Fundamentals Appendix G: Advanced Excel Appendix H:
Networking Basics Appendix I: Security and Privacy Appendix J: Funding
Information Technology Appendix K: Managing IS Projects
A1 A11 A23 A35 A51 A69 A81 A99 A111 A125 A133
Appendix A
Computer Hardware
Learning Objectives
By reading and completing the activities in this appendix, you will be able
to:
· Discuss the history of computing hardware and systems · Identify the major
hardware components of information systems · Identify the various types of
input and output technologies and their uses · Describe the main types of
primary and secondary storage technologies
and their characteristics
Appendix Outline
Introduction Computer Hardware Generations Computer Hardware Platforms
Focusing Story: Convergence
Learning Activity A.1: What Do I Want for a PC? Hardware Components
Learning Activity A.2: Is Moore’s Law Accurate? Learning Activity A.3: A Chip
for My PC? Learning Activity A.4: Life in the 3-D World
Introduction
The focusing story provides an example of how the utilization of computers has
changed as a result of convergence. Convergence can be defined as the tendency
for different technological systems to evolve toward performing similar tasks.
An example of convergence is using a single device to handle media, Internet,
entertainment, and telephony needs. While convergence is constantly changing
the computing landscape, it occurs as the result of the functionality of its
parts. In this appendix, we present a discussion of the various hardware
components that enable computers to perform as they do. These building blocks
provide the foundation for the converged technologies we enjoy today.
Computer Hardware Generations
Hardware refers to the physical equipment involved in the input, processing,
output, storage, and control activities of an information system. Hardware for
information systems has evolved over the years. Today, we usually refer to the
five main generations
A2 Appendix A
of hardware as those that used vacuum tubes, transistors, integrated circuits, microprocessors, and massively parallel processing. Table A.1 summarizes the different generations, and pictures can be seen here: http://www.tutorialspoint.com/computer_funda mentals/computer_generations.htm.
TABLE A.1 Computer Hardware Generations
Generation Approximate Dates Technology Used
First
194659
Vacuum tubes
Second Third
195965 196571
Transistors Integrated circuits
Fourth
197180
Microprocessors
Fifth
1980?
Superconductors and parallel processing
Comments
Tubes were used as amplifiers and also to represent binary numbers. Magnetic
drums were used for memory. These computers used a lot of space and required a
lot of electricity. Computers that would take up an entire room then had less
power than you probably have in your smartphone.
These were much smaller and much more efficient, replacing tubes.
Transistors were reduced in size, and many could be placed on a silicon chip,
also known as a semiconductor. This again reduced space and energy
requirements and made the systems more efficient.
At this point, thousands of integrated circuits could be placed onto each
silicon chip. This allowed smaller and more powerful computers, leading to the
PC and some of the very small devices you use today.
It is unclear exactly what the fifth generation will be. There was an attempt
to use massive parallel processing to get computing to another level, but it
is unclear that it has succeeded. Others suggest that grid computing (defined
below) has the potential to be the fifth generation.
Computer Hardware Platforms
Individuals and organizations need different levels of computing power, which
is why different computer hardware platforms exist. You are most likely
familiar with personal computers, which are in the category of microcomputers.
These are small enough and inexpensive enough that today most individuals have
access to one even if they do not own one. These include laptops, notebooks,
netbooks, tablets, and desktop computers.
Computer Hardware A3
FOCUSING STORY
Convergence
When you check email, visit Web pages, communicate with friends, talk on the
phone, and take pictures, you are likely doing so on an individual device–your
smartphone. This ability to use one device to accomplish all these tasks is a
somewhat new phenomenon as the result of convergence. When one of us was an
undergraduate student in the late 1990s, the Internet was a fairly new
technology, as were email and Web pages. To send email or visit Web pages, you
would often use a computer in the computer lab (for two main reasons: first,
not many undergraduates owned personal computers because of their cost, and
second, the Internet was much faster in a lab than dial-up at home). When
friends wanted to communicate, they would either call each other on the phone
or seek out one another and communicate face-to-face. At that time, some
students carried pagers so that they could know if they needed to go find a
phone to return a phone call. Pictures were often taken with cameras that
required film, which had to be developed into printed pictures.
Today, the tasks that used to require a number of different devices have
converged so that they can be completed all on the same device. For example, a
smartphone allows you to perform all the tasks discussed above and then some.
Likewise, tablet computers, such as the iPad, are a result of convergence.
They allow you to perform many functions all on one compact and portable
device. Unfortunately, with the convergence of devices often comes a trade-off
in quality, resulting in some people desiring to have a single-function device
for some functions. For example, tablet computers have not replaced laptops
for many individuals because of the difficulty of typing on the touch screen.
As a result, people will either add a keyboard (partly defeating the goal of
convergence) or also own a laptop for times when they need to be productive.
Similarly, the quality of pictures taken with cameras on smartphones is not up
to the quality of stand-alone digital cameras. What you want to accomplish
with the pictures may dictate whether you also own a separate digital camera.
Focusing Questions 1. List all the tasks you can perform with a smartphone. 2.
Identify how these tasks would have been completed prior to them converging
into a smartphone. 3. Identify advantages that this convergence has created
for individuals and then for businesses. 4. Identify challenges this has
created for businesses as well as examples of when businesses may want to
utilize single-function devices.
LEARNING ACTIVITY A.1
What Do I Want for a PC?
In this activity, you will “build” your own computer at the Dell website. Go
to http://www .dell.com and use the Products menu, then select “Laptops for
Business.” Select a laptop computer and then hit the Customize & Buy button.
Identify all the options presented to you and which ones you would decide to
pay for. Bring your final configuration to class.
In class, be prepared to discuss the following:
1. Which options were available to customize? 2. How did you decide whether
to pay for certain options? 3. Which options would you change if this laptop
was to be used for your
business, which runs 24/7 (24 hours per day; 7 days per week)?
A4 Appendix A
In organizations, however, you are likely to find large computer hardware
platforms such as midrange computers and mainframe computers. Some specific
organizations even have supercomputers. Midrange computers, also called
minicomputers, are often used as servers (see Chapter 7).They offer much
greater computing capacity than personal computers and can therefore be
accessed and used by several users simultaneously. When organizations need
substantial computing–for example, to process billing transactions for a
telecommunication company–they tend to use mainframes, or large computers that
can handle many thousands of users and/or transactions at the same time. While
you may hear that the age of the mainframe is over, you would be surprised to
find that most large corporations use mainframes extensively today.
Supercomputers, on the other hand, are meant for organizations that need
excessive processing capacities and speed. For example, models to predict
weather require such levels of processing, as do analyses of genetic data. It
is possible to achieve great processing power with smaller (e.g., midrange)
computers by loosely connecting them together with middleware. This is
referred to as grid computing, where computing resources from different
locations, organizations, or domains are loosely combined to achieve their
goal.
For personal computers, there is also an important categorization you might
encounter: personal computers are often classified as thin clients or thick
clients. This refers to the level of processing that is performed on the
personal computer. In a thick client, the personal computer is involved with
much more processing and storage activities, whereas in a thin client, the
personal computer performs less processing and storage and instead uses a
server to handle most processing. Some of you or your friends might have used
a Chromebook, the most common example of a thin client.
Hardware Components
The main categories of hardware include the CPU, storage technologies, input
technologies, and output technologies.
LEARNING ACTIVITY A.2
Is Moore’s Law Accurate?
For this activity, you need to identify what Moore’s law is and conduct
research on it. Determine whether it has been accurate over the years. Be
prepared to discuss these questions in class:
1. What is Moore’s law? 2. Has it been accurate over the years? 3. What has
made it possible to sustain Moore’s law so far? 4. What is the future of
Moore’s law?
Computer Hardware A5
Central Processing Unit The central processing unit (CPU) of a computer is
where the processing takes place, which is the handling of computations or
instructions given to it by the system. In other words, it is really the brain
of the computer. The CPU itself is made up of several components. The control
unit handles instructions and controls the flow of data to the various parts
of the CPU. The arithmetic-logic unit (ALU) performs the actual computations,
while registers are used to store data and instructions temporarily. Figure
A.1 shows a simplified version of a CPU.
Figure A.1 Components of a Simplified CPU
LEARNING ACTIVITY A.3
A Chip for My PC?
For this activity, you need to research the current developments in
microprocessors (chips). First, try to identify which microprocessor (chip) is
used in your personal computer. Then, go to a microprocessor manufacturer’s
website and identify its most recent products. Two examples are Intel
(http://www.intel.com) and Advanced Micro Devices (http://www.amd.com). (Tip:
For the Intel page, select “Products,” and for the AMD page, select
“Processors.”) Write a short report on the most current commercially available
chips, their performance, their cost (if available), and the expected future
developments of each chip.
In class, compare your findings with classmates. Be prepared to discuss the
following: 1. What are the processing speeds of the company’s fastest chips?
What do
these speeds mean? 2. Which company seems to offer the most advanced
microprocessors today? 3. What does the future hold for microprocessors? For
that company?
A6 Appendix A
LEARNING ACTIVITY A.4
Life in the 3-D World
Most of the devices we are familiar with are two-dimensional devices. However,
the world of 3-D is becoming mainstream today, with many television sets
offering 3-D screens. They used to require geeky glasses but are getting close
to the point where such glasses won’t be required. This is the new world of
3-D. However, 3-D is not limited to television. There are now many 3-D
displays, as well as 3-D printers, 3-D semiconductors, and virtual reality
headsets and devices, such as the Oculus Rift and Samsung Gear VR. Select one
of these 3-D technologies and perform research. Write a short essay about how
the technology works, what its main advantages and disadvantages are, and what
you find most surprising about the technology.
Input and Output Devices A wide variety of input technologies are available to
use with computers. You probably frequently use keyboards and mice, or maybe
consoles and joysticks for playing games. Many other input devices–such as
touch screens, cameras, styluses, digital pens, and even microphones for voice
recognition–are used to interact with various devices. When you use a kiosk,
you use touch-screen technology. When you write on a tablet PC like the
Microsoft Surface Pro, you most likely use a stylus. Your smartphone likely
uses a touch screen. Still other devices provide inputs to information systems
without human interaction, such as barcode scanners, magnetic ink readers,
remote sensors, magnetic stripe readers, chip readers, optical character
recognition sensors, and even the radio-frequency identification tags
discussed in Chapter 12.
There are also many output devices, such as the monitors and printers you may
use every day. Other output devices include voice devices like speakers and
headphones, projectors, plotters, and other industrial devices managed by
information systems. Today, we are seeing an increasing number of devices that
use three-dimensional outputs, such as 3-D displays and 3-D printers. Many
universities have “maker labs” that have workstations where you can design a
product, which you print on a 3-D printer. The prices of basic 3-D printers
have dropped to the point where they are affordable for home use.
Storage Technologies People often look at two factors in deciding which
personal computer to buy: processing power and memory. Processing power
depends on the CPU used, as described in the previous section. Memory actually
refers to two types of storage technologies related to the computer: primary
and secondary memory.
Primary storage or main memory represents the amount of temporary memory
available for storing information as the CPU is handling the applications and
functions used on the computer.The types of information stored in main memory
include data to be used for processing, instructions for the CPU to process
the data, and applications that are used by the computer. The main memory can
affect the performance of your computer if, for example, you have a small
amount available but typ-
Computer Hardware A7
ically try to run many applications (open programs) at the same time. The
types of main memory include registers (briefly mentioned when talking about
the CPU), random-access memory (RAM), and cache. RAM is the one that best
describes main memory–small amounts of data or parts of applications being
stored while in use for processing. Cache is used for storing data that are
used more often (instead of leaving them in secondary storage) to improve
computer performance. All these are managed by the computer’s operating system
and are considered volatile, in that they are cleared when the computer is
turned off. An additional type of memory is read-only memory (ROM), which is a
storage location for critical instructions that is nonvolatile.
Secondary memory, also called permanent memory, is where you store your files
and programs. While it takes longer for the computer to access secondary
storage, it is considered permanent in that saved files are not removed when
the power is turned off. Many devices are used for secondary storage, and the
cost of these devices continues to go down every year. You are familiar with
small USB devices described in the book. Over very few years, their cost
remained relatively low and stable while their capacity went from 128 Kb to
128 GB and more today. The same is true for larger devices. Technologies for
secondary storage also include magnetic tape (not many today) and disks and
optical devices, such as compact disc read-only memory (CD-ROM), digital video
disc (DVD), and Blu-ray Disc. Newer devices are gaining popularity in optical
storage, such as holographic memory that uses three dimensions to store data.
Solid-state drives (SSDs) have become popular, especially for lightweight
laptops and portable storage. Traditional drives are mechanical, with one or
more platters (a small, circular magnetic plate that stores data) that rotate
rapidly while a read-write head moves back and forth along the platter to read
or store data. In contrast, SSDs have no moving parts; they store data on
semiconductors, much like USB thumb drives. SSDs are lighter and much faster
than traditional drives, use less power, and are more reliable. However, an
SSD is more expensive than a traditional drive with the same storage capacity.
For large companies that have multiple storage devices and huge amounts of
data, making sure the data are not lost is very important. Many organizations
therefore use something called a redundant array of independent disks (RAID)
to ensure that the data are stored redundantly, which provides organizations
with a way to make sure they have access to the data if something happens to
one of their storage devices (since a copy of the data is maintained on a
different disk). This allows the company to continue operations with little to
no downtime.
Summary
In this appendix, we discussed basic concepts related to information systems
hardware, starting with different hardware generations and platforms. We then
discussed the hardware components of an information system, starting with the
central processing unit (CPU) and continuing with input and output devices and
storage.
A8 Appendix A
Here are the main points discussed in this appendix:
· There are five main information systems hardware generations: hardware using
vacuum tubes, transistors, integrated circuits, microprocessors, and parallel
processing or grid computing. The main hardware platforms include
microcomputers, midrange computers, mainframe computers, and supercomputers.
When several computing resources are loosely connected together to achieve
their goal, they form grid computing. When personal computers are involved
with more processing and storage activities, they are called thick clients,
whereas in a thin-client configuration, the computer performs less processing
and storage and instead uses the services of a server, such as a midrange
computer, to handle most processing.
· The major hardware components of information systems include the CPU, input
and output devices, and storage. The CPU handles the computations or
instructions given to it by the system.
· A wide variety of input technologies are available to use with computers,
such as keyboards, mice, consoles, joysticks, touch screens, cameras, pointing
sticks, styluses, digital pens, and microphones. Others include barcode
scanners, magnetic ink readers, remote sensors, magnetic strip readers,
optical character recognition sensors, and radio-frequency identification
tags. Output devices include speakers, headphones, projectors, plotters, and
other industrial devices managed by information systems.
· There are two types of storage: primary and secondary. Primary storage or
main memory is temporary memory available for storing information as the CPU
is handling the applications and functions used on the computer. Types of main
memory include registers, random-access memory, cache, and read-only memory.
Secondary memory is where files and programs are kept for permanent storage
(if they are not deleted). Types of secondary memory devices include USB
devices; magnetic tape and disks; optical devices, such as compact disc read-
only memory (CD-ROM), digital video disc (DVD), and Blu-ray Disc; and
solidstate drives (SSDs). A redundant array of independent disks (RAID)
maintains a copy of data in several locations.
Review Questions
1. What do we refer to when we talk about hardware? 2. What are the various
generations of information systems hardware? 3. What are the key differences
among transistors, integrated circuits, and
microprocessors? 4. How do mainframes and midrange computers differ? What are
their
similarities? 5. What is a thin client? A thick client?
Computer Hardware A9
6. What is a supercomputer? 7. What is grid computing? 8. What are the
components of a CPU? 9. What are the different types of memory or storage? 10.
What is RAID storage, and what is its purpose?
Reflection Questions
1. What is the most important thing you learned in this appendix? Why is it
important?
2. What topics are unclear? What about them is unclear? 3. What relationships
do you see between what you learned in this appendix
and what you learned in other parts of the text? 4. How is the material in
this appendix related to the material in Appendix B? 5. Explain how a
mainframe is similar to and different from a supercom-
puter. 6. Explain how the computer generations presented in Table A.1 support
the ideas proposed by Moore’s law. 7. What is the difference between grid
computing and distributed comput-
ing? 8. Why is there a need for primary storage, such as registers or cache?
9. What relations can you draw between the functioning of the CPU and
security threats discussed in the book? 10. Why do you think we are seeing
increasing storage capacities on devices
at such low costs?
Glossary
Arithmetic-logic unit (ALU): The component of the CPU that performs the actual
computations.
Blu-ray: A type of secondary memory device that uses optical technology that
superseded the DVD.
Cache: Memory used for storing data that are used more often to improve
computer performance.
Central processing unit (CPU): The component of a computer where the
processing or handling of computations or instructions is done.
Compact disc read-only memory (CD-ROM): A type of secondary memory device that
uses optical technology.
Control unit: The component of the CPU that handles instructions and controls
the flow of data to the various parts of the CPU.
Convergence: The tendency for different technological systems to evolve toward
performing similar tasks.
Digital video disc (DVD): A type of secondary memory device that uses optical
technology that superseded the CD-ROM.
A10 Appendix A
Grid computing: Computing resources from different locations, organizations,
or domains loosely combined to achieve their goal.
Hardware: The physical equipment involved in the input, processing, output,
storage, and control activities of an information system.
Holographic memory: A type of secondary memory device that uses three
dimensions to store data.
Integrated circuits: The third generation of information system hardware;
include many transistors.
Magnetic tape and disks: A type of secondary memory technology. Mainframes:
Large computers that can handle many thousands of users and/or trans-
actions at the same time. Microcomputers: Small computers for individual use;
include laptops, notebooks, tab-
lets, and desktop computers. Microprocessors: The fourth generation of
information system hardware; include
thousands of integrated circuits; currently used in most computers. Midrange
computers: Computers that offer greater computing capacity than personal
computers and can be accessed and used by several users simultaneously. Also
called minicomputers. Networked computers: Thin clients. Primary storage:
Represents temporary memory available for storing information as the CPU is
handling the applications and functions used on the computer. Also called main
memory. Random-access memory (RAM): Memory where small amounts of data or
parts of applications are stored while in use for processing. Read-only memory
(ROM): Nonvolatile memory where critical instructions are stored. Redundant
array of independent disks (RAID): A type of secondary memory where a copy of
data is maintained on several disks to ensure that the data are stored
redundantly. Registers: Components of the CPU used to store data and
instructions temporarily. Secondary memory: Memory where files and programs
are stored. Also called permanent memory. Solid-state drive (SSD): A secondary
storage device that stores data on semiconductors rather than magnetic
platters. Supercomputers: Very large computers meant for organizations that
need excessive processing capacities and speed. Superconductors: The fifth
generation of information system hardware; allow parallel processing; few
devices currently available. Thick client: A personal computer with
substantial processing and storage facilities. Thin client: A personal
computer performing less processing and storage, instead using a server, such
as a midrange computer, to handle most processing. Transistors: The second
generation of information system hardware; smaller and more efficient than
vacuum tubes. Vacuum tubes: The first generation of information systems
hardware.
Appendix B
Computer Software
Learning Objectives
By reading and completing the activities in this appendix, you will be able
to:
· Distinguish between system software and application software · Describe the
relationship between system software and application soft-
ware · Describe the main functions of an operating system · Describe the
purpose of utility software · Name and describe several frequently used kinds
of utility software · Use four dimensions to classify application software ·
Explain the concept of an “app store” and why they are important to
mobile operating system companies
Appendix Outline
Introduction Types of Software Focusing Story: Installation of Software
Learning Activity B.1: Why Did You Choose Your Operating System? Learning
Activity B.2: Find a Utility Applications Learning Activity B.3: Classify
Applications App Stores Learning Activity B.4: Find an App Learning Activity
B.5: App Usage
Introduction
Throughout this book, we have discussed many different kinds of software. This
appendix gives you some additional information about software, including
descriptions of some types of software not discussed elsewhere in the book.
Software is an organized set of data and instructions that govern the
operation of a computer. Procedures for using a computer are also considered
part of software.
Types of Software
There are two major categories of software: system software and application
software. Usually, you use a computer to complete some task–write a paper,
look something up
A12 Appendix B
FOCUSING STORY
Installation of Software
Most of us use numerous apps (application software packages) on our
smartphones and tablets. These apps allow us to play games, access social
media, read e-books, send text messages, check email, and perform numerous
other tasks. As is the case with any computing device, the usefulness of the
device is a function of the apps on it. Imagine having an iPhone but using it
for nothing more than phone calls. The popularity of smartphone apps is shown
by the millions of apps in the Google Play Store and Apple App Store. Apps are
so useful that there’s an old geek joke that goes something like this: What do
you call a computer without application software? The answer: A boat anchor.
Don’t worry if you didn’t laugh; the joke is usually funny only to old geeks.
The point of the joke is that apps are what make computing devices useful,
whether the device is a laptop, smartphone, tablet, or mainframe. This was
true in the earliest days of computing, and it will remain true in the future.
Focusing Questions
Think about the first issue, the ease with which software can be pirated, and
answer the following questions:
1. What are your favorite apps on your smartphone or tablet? Why are they
your favorites? In other words, what do they let you do on your device?
2. What are your most-used applications on your computer? What do these apps
let you do?
3. How do the two lists compare? Are there differences in what you do on your
smartphone, tablet, and computer?
on the Web, send an email, or play a game. Application software lets you perform these
tasks; it is software that includes the computer programs and procedures necessary to
carry out some specific task. When you use a computer, most of the time you are
interacting with application software. However, the application software cannot
operate without system software. System software controls the operation of the com-
puter’s hardware. It also serves as the
platform on which application software
operates. Figure B.1 shows the relation-
ship between application software and
system software.
While system software is usually
transparent to the user, it is sometimes
necessary to interact directly with sys-
tem software. Usually this happens
when you need to perform some house-
keeping chore, such as uninstalling soft-
ware; deleting, copying, or moving files;
or scanning your computer for viruses
and/or spyware. The point to remember
Figure B.1 Relationships among Application Soft- is that application software lets you
ware, System Software, and Hardware
accomplish what you want to do with
Computer Software A13
the computer, while system software is a “necessary evil” that enables your
computer to operate properly.
System Software System software essentially serves as an interface between
application software and the hardware of the computer. We can divide system
software into two broad categories: operating systems and utilities.
Operating Systems All computers require an operating system. This includes
devices such as smartphones and game consoles, as well as more obvious
computers, such as laptops and desktop personal computers. In addition to
providing a platform for running application software, operating systems also
manage multiple, simultaneous users and programs, making sure they do not
conflict. Operating systems also perform basic functions, such as sending
output to devices such as printers and monitors; accepting input from
keyboards, mice, and the like; and managing files. Most operating systems also
provide networking functions. The fact that operating systems provide these
functions relieves application programmers from the need to include these
functions in their programs. They simply provide the code necessary to
instruct the operating system to perform the function. For example, when you
print a file using your word processor, the word processor software sends a
message to the operating system telling it to print the document. The
application does not have to include instructions on exactly how to perform
the printing; it simply sends a printing request to the operating system,
which does the rest.
The kernel is one of the most important operating system components. The
kernel manages communication between the computer’s hardware resources and
application programs. For example, the kernel handles input and output
requests and manages the computer’s memory.
Memory management is one of the most important functions of the kernel.
Managing memory is very complicated, especially when several large programs
are running, which can require more primary storage than the computer has. In
these cases, the kernel uses a method called paging to allow the programs to
run. Paging basically takes some of the contents of primary storage and sends
them to secondary storage (such as a hard drive); this is virtual primary
storage. The kernel swaps data between physical and virtual primary storage
according to which open applications need to access data at that particular
moment. You may have noticed that your computer slows down when you have many
applications open at one time. One reason for this is that the kernel has to
do more swapping when many applications are running.This swapping takes time,
which slows down the computer’s responsiveness. This is also why adding
random-access memory (RAM) to a computer often results in significant
performance improvements.
Applications communicate with the operating system through system calls.
System calls are usually implemented through an application program interface
(API). The API provides application programmers with a set of routines or
protocols (rules)
A14 Appendix B
LEARNING ACTIVITY B.1
Why Did You Choose Your Operating System?
What operating systems do you use for your PC and mobile phone? Why are you
using these particular operating systems? How well do the two systems interact
with one another? Write a one-page report that answers these questions.
Compare your responses with another student.
for asking the kernel to perform a task. While this makes programmers’ work
easier, it also ensures a more consistent experience for users. For example,
the process for saving a document has the same look and feel for almost all
Windows programs. This is because most developers of Windows-based
applications use the Windows API to perform the task of saving a file.
Windows 10, Apple’s macOS, Chrome OS, and Linux are popular personal computer
operating systems. (Note that there are many different distributions of Linux,
such as Ubuntu and Fedora.) Windows in its various forms still holds a large
portion of the PC operating system market. As of July 2020, Windows held more
than 77% of the market. Google’s Android and Apple’s iOS are popular operating
systems for smartphones. Larger computers, such as mainframes, run a variety
of operating systems, including Unix, IBM’s z/OS, Solaris, Linux, and HP-UX.
Many operating systems are based on the Unix operating system. Examples
include Linux, macOS, Solaris, and HP-UX.
Which is the best operating system? That is largely a matter of opinion. Most
have their advantages and disadvantages, and all accomplish essentially the
same tasks. The most important thing to consider when choosing an operating
system is whether the application programs you need can run on the operating
system. For example, if you absolutely must have Microsoft Access, you need to
run some version of Windows. If GarageBand is a must for you, then you have to
use macOS. If the applications you need run on multiple operating systems,
then the choice comes down to your preferred hardware and interface. For
example, many people who purchase a Mac do so because they like the interface,
while many Windows users prefer the wider choice of hardware available for
Windows. The openness of the operating system is also a consideration.
Proprietary operating systems such as Windows and macOS are controlled by a
single company, whereas Linux is open source and therefore is much more open.
Utility Software Utility software consists of programs that perform tasks
related to the management of a computer. As is the case with operating
systems, few people would buy a computer just to run utility software. Rather,
utilities help you manage your computer. Antivirus software is a good example.
You would not buy a computer just to run antivirus software, but this utility
helps keep your computer operating correctly by avoiding viruses.
Computer Software A15
Distinguishing between operating systems software and utility software is
sometimes confusing. This is because many utility functions are now built into
operating systems. Disk defragmenting software is an example. (Over time, hard
drives can become fragmented, which means that data for a single file can be
spread across different parts of the drive. This can hurt performance.
Defragmenting software fixes this problem.) Defragmenting software is included
in Microsoft Windows (look in the System Tools folder) but is also available
as a separate utility. Years ago, it was only available as a utility.
The following are some popular examples of utility software:
· File compression and archiving: If you have ever received a “zipped” file,
you probably needed an “unzipping” utility to extract the files from the .zip
file. A .zip file serves two purposes. It compresses the included files so
that they take up less space, and it also combines multiple files into a
single file (the archive), which makes them easier to manage and share.
Actually, .zip is only one form of file compression and archiving. Other
examples include .cab, .rar, and .tar.gz. The last format (.tar.gz) is
interesting because it combines an archiving format (.tar) with a compression
format (.gz). Popular file compression and archiving utilities include WinZip,
7-Zip, and IZArc.
· Data backup: Data backup utilities make it easier for you to maintain a copy
of your data to protect against data loss. Some operating systems include
backup software, but this software is often limited in its functionality or is
relatively hard to use. The ability to schedule regular backups is a feature
of many backup utilities. Some backup utilities include the ability to create
multivolume backups, which means that a single backup can be stored on
multiple CDs or other media. Another useful backup feature is data
compression, which allows the backup to use less space. However, it does make
recovery a bit harder to perform. Backup utilities often give you the choice
of doing a full backup (back up all data) or an incremental backup, which only
backs up data that has changed since the last backup. One of the most
interesting developments in backup utilities is the growing popularity of
cloud-based backup utilities. Some of these are dedicated backup utilities.
For example, Carbonite (https://www.carbonite.com/home3/) provides automatic
or scheduled incremental backups to a remote server. One of the interesting
things about cloud-based backup is that your backup files are stored remotely,
which gives you an additional layer of protection (e.g., so physical damage to
your home, like a natural disaster, doesn’t damage your backup). Security is a
major concern for cloudbased backup, however. Dropbox
(https://www.dropbox.com) is another cloud-based storage system that provides
backup. Dropbox is primarily a file synchronization service, however. As a
result, only files that are marked for synchronization are backed up.
A16 Appendix B
· Antimalware: Unfortunately, computer viruses, spyware, and other types of
malware are a fact of life for many computer users. Utilities such as
antivirus and antispyware software help protect us from malware. Most
antimalware software runs in the background, continuously checking for
potentially harmful software. In most cases, you can also run periodic scans
to check your computer for malware. While there are threatspecific programs,
several companies offer suites that protect against multiple threats. Examples
of security suites include AVG, Kaspersky, McAfee, Norton, and F-Secure.
· System repair: System repair utilities, which are sometimes called system
tune-up software, perform a variety of tasks intended to improve the
performance of a computer. Over time, a computer can become sluggish for
several reasons, including incorrect registry entries, a large number of
temporary files, fragmented files, and unnecessary background processes. (The
registry is a database of system configuration files.) System repair utilities
clean up many of these problems. Some utilities address a specific problem,
while others are suites that address multiple issues. Be careful when using
system repair utilities, as there is a chance that the software could make
harmful changes. You should create a backup of important data prior to doing a
system tune-up.
· System migration: Getting a new computer is exciting, until you think about
the hassle of having to move your software, files, and settings to the new
computer. This is usually a time-consuming, complex task. System migration
utilities help make this task easier. For example, PCmover attempts to move
all your data, application software, and settings from one computer to
another. Notice that we said “attempts.” In most cases, the migrations are not
completely effective.
· File synchronization: Many computer users work on more than one computer.
For example, many users have a desktop and laptop computer and use both
regularly. If you have more than one computer, you have probably experienced
the frustration of needing a file that is on your other computer. The backup
utilities that we discussed earlier are a one-way form of file
synchronization. While this is helpful for backup, it is less useful if you
are trying to use multiple computers. In these cases, twoway syncing is
necessary. If you have a desktop and laptop, this means that files on your
laptop are sent to the desktop, and vice versa. Some synchronization systems
use removable storage media, such as a USB thumb drive, to keep the computers
in sync. However, network-based synchronization is often easier and more
effective. Dropbox, which we mentioned earlier, is an example of cloud-based
synchronization. Some utilities, such as GoodSync, can synchronize using
either a network server or a USB drive.
Computer Software A17
LEARNING ACTIVITY B.2
Find a Utility
For any three types of utility programs discussed above, find two specific
examples of a utility program in that category. You may not use the examples
provided in the text. For each utility program, provide the following:
1. The name 2. A brief description of the utility 3. The cost
Many other utilities exist. If you experience some problem related to the
operation of your computer, there is probably a utility that can help. While
the utilities that we described focus on personal computers, there are also
important utilities for other types of computers, such as mainframes.
Applications
Application software lets you perform specific tasks using a computer.
Applications range from large-scale systems, such as enterprise systems, to
computer games. You learned about many different kinds of application software
throughout the book. There is an almost endless array of application software
available today, so understanding the range of applications can be
challenging. To help you gain an understanding of application software, we
present several dimensions that can be used to classify them. These are (1)
business or personal-use software, (2) general productivity or special-purpose
software, (3) horizontal- or vertical-market software, and (4) locally
installed or cloud-based software. Note that this is not an exhaustive list of
dimensions, but it is sufficient for our purposes. Also keep in mind that
there are some gray areas. For example, photo editing software can be used by
individuals (personal use) or by businesses.
· Business or personal-use software: Applications can be for business or
personal use. Most of the applications we describe in the book are business
applications. Examples include ERP, customer relationship management, and
supply chain management. Office suites, such as Microsoft Office, are also
intended primarily for business use but blur the line, as this software is
also often used for personal use as well. Personal-use software tends to be
directed at pleasurable, entertainment-oriented uses. Examples include
computer games, media players (such as iTunes), and photo viewers.
Applications directed at hobbyists, such as software for keeping track of a
family tree, would also be considered personal-use software.
A18 Appendix B
LEARNING ACTIVITY B.3
Classify Applications
Partner with another student. Classify each of the following applications on
each of the four dimensions described above. Briefly explain each
classification. Be prepared to share your responses with the class.
· Google Docs · Your school’s registration system · Microsoft Project
(https://www.microsoft.com/en-us/microsoft-365/project/
project-management-software) · AutoCAD
(http://www.autodesk.com/products/autocad/overview) · Evernote
(http://www.evernote.com)
· General productivity or special-purpose software: General productivity
software is less focused than special-purpose software. For example, an
electronic spreadsheet application can be applied to many very different
tasks. The same can be said for drawing software (such as Microsoft Visio),
presentation software (such as Apple Keynote), and word processors (such as
Microsoft Word). Special-purpose software applications are more focused. These
applications are intended to perform a more narrow set of tasks. Examples
include software for accounting, computeraided design, statistical analysis,
inventory control, and customer relationship management.
· Horizontal- or vertical-market software: Vertical-market software is
software that is customized for use in a particular industry. Examples include
software for electronic medical records, property management, manufacturing
systems, and vehicle fleet management. Some of this software is very narrowly
focused. In contrast, horizontal-market software can be used across a wide
range of industries. CRM is an example, since it can be applied to almost any
industry. Interestingly, horizontal-market software is sometimes customized
for particular industries. Salesforce.com, for example, can be customized to
meet the needs of industries ranging from financial services to media.
· Locally installed or cloud-based software: A few years ago, we would not
have included this dimension. Today, however, there is a growing trend of
accessing applications through the Internet (or other network) rather than
having them installed locally. (Being installed locally means that the
software is installed on the user’s computer.) The CRM application offered by
Salesforce.com is an example of a cloud-based application. Google Docs is
another example. Microsoft and Apple both provide cloud-based storage and
applications. Spotify is a cloud-based music system that allows you to access
many songs and playlists across devices. As discussed in Chapter 7, there are
many advantages to cloud-based
Computer Software A19
applications, especially in terms of managing them. The main drawbacks to
cloud-based software are the need to have an Internet connection and potential
security vulnerabilities. As Internet access becomes more ubiquitous, we
expect the use of cloud-based applications to grow.
App Stores
The rise of application stores is an interesting development in the world of
computer applications. These “app stores” provide a relatively easy way to
research, purchase, and install application software. App stores are primarily
organized by companies that want to promote the use of their mobile operating
systems. The most popular app store is Apple’s App Store. Others include
Google Play, Amazon App Store, and Windows App Store. There are literally
millions of applications in these stores. Apple’s App Store has more than
2,200,000 applications, while the Google Play app store includes more than
3,000,000 apps. App stores are widely used. Between Apple’s App Store and
Google Play, there were just over 200 billion downloads in 2019 (Statista
2021).
The rise of app stores illustrates the importance of application software. The
usefulness of a computer, particularly tablets and smartphones, is driven by
the breadth and quality of the application software that it can run. Without
useful, usable applications, a computer is little more than an expensive
brick. Companies such as Apple and Google know this, so they provide an easy
way to find and acquire applications for devices running their operating
systems (iOS and Android, respectively). App stores have also promoted
creativity by giving small developers a way to distribute their applications.
We need to make one final comment on app stores. Not all programs in an app
store are application software. Many of the programs available in an app store
are actually better classified as utility software. Backup software is just
one example.
LEARNING ACTIVITY B.4
Find an App
Visit one of the app stores listed below. Find an application in each of the
following categories. Briefly describe each application and list the price and
the app store in which you found it.
App Category · Personal entertainment · General-purpose business · Industry
specific (vertical)
App Stores · http://www.apple.com/app-store/ · https://play.google.com/store ·
https://www.microsoft.com/en-us/store/apps/windows
A20 Appendix B
LEARNING ACTIVITY B.5
App Usage
While mobile phones are useful for making voice calls, most of us use our
mobile phones for other purposes. As discussed earlier, software applications,
or apps, give smartphones the ability to entertain us, keep us up-to-date on
the news, take photos, get directions, and so much more. According to a 2017
report (Perez 2017), the average user uses 9 apps per day. However, in the
United States, users have an average of around 90 apps on their phones. It is
not unusual for a user to download an app, use it once, then forget it’s
there. As you might imagine, app developers are very interested in what makes
users keep using or abandon apps. For this learning activity, think about your
own app use and answer the following questions. Be prepared to discuss your
answers in class.
1. What apps do you use the most? 2. How much time do you spend on these
apps? 3. Have you ever deleted an app after only a few uses? If so, why didn’t
you
keep using the app? 4. Have you ever downloaded an app and only used it once
or twice and then
forgot it was there? If so, why didn’t you delete it? 5. What are the main
differences between the apps you use and those you
abandon? How do they differ with respect to usefulness and usability? Have you
noticed other differences?
Summary
This appendix provided you with more information regarding computer software.
Here are the main points discussed in this appendix:
· Software can be divided into two major categories: system software and
application software.
· System software controls the operation of the computer’s hardware and
provides a platform for operating application software.
· Application software assists users in performing some specific task using a
computer.
· Operating systems provide a platform for running application software;
providing networking functions; and managing users, programs, hardware, files,
and memory.
· Utilities are small programs that perform computer management tasks. ·
Commonly used utilities include file compression and archiving, data
backup, antimalware (e.g., antivirus and antispyware), system repair, system
migration, and file synchronization. · Application software may be classified
along four dimensions: (1) business or personal-use software, (2) general
productivity or special-purpose software, (3) horizontal- or vertical-market
software, and (4) locally installed or cloud-based software.
Computer Software A21
· “App stores” are online repositories of applications for specific operating
systems, which are usually, but not always, mobile operating systems.
· App stores are important to mobile operating systems companies because they
facilitate finding and acquiring application software, which is critical to
the usefulness of a mobile device.
Review Questions
1. Define the term software. 2. Name and briefly describe the two main
categories of software. 3. Describe the relationship between system software
and application
software. 4. Name and briefly describe the two categories of system software.
5. Name the main functions of an operating system. 6. What is the purpose of
the operating system kernel? 7. Name three popular personal computer operating
systems. 8. Name and briefly describe six examples of utility software. 9.
Briefly describe four dimensions that can be used to classify application
software. 10. What are “app stores”? Name three app stores related to mobile
operating
systems.
Reflection Questions
1. What is the most important thing you learned in this appendix? Why is it
important?
2. What topics are unclear? What about them is unclear? 3. What relationships
do you see between what you learned in this appendix
and what you learned in other parts of the text? 4. Why can system software be
considered a “necessary evil”? 5. Explain why individual preferences are
important when choosing an
operating system. 6. Explain why the choice of application software is
important to the choice
of an operating system. 7. Some utilities have been added to operating systems
(such as the disk
defragmenting software described in the text). Why would operating systems
publishers add utilities to their operating systems? 8. Choose five software
applications you use frequently. (Your instructor may give you specific
applications.) Use the four dimensions discussed in the text to classify each
application. 9. What are the pros and cons of vertical-market software when
compared to horizontal-market software? 10. When Android phones were
introduced, proponents of Apple’s mobile devices sometimes cited the large
number of applications in the App
A22 Appendix B
Store as a reason to adopt the iPhone rather than an Android-based phone.
(There were far fewer apps for Android then, although that situation has
changed.) Do you think the number of available apps is a reason to adopt a
particular mobile operating system? Explain your answer.
References
Dogtiev, A. 2018. “App Download and Usage Statistics.” Business of Apps,
updated January 9. http://www.businessofapps.com/data/app-statistics/#2.
Perez, S. 2017. “Report: Smartphone Owners Are Using 9 Apps per Day, 30 per
Month.” Tech Crunch, May 4. https://techcrunch.com/2017/05/04/report-smart
phone-owners-are-using-9-apps-per-day-30-per-month/.
Statista. 2021. “Number of Mobile App Downloads Worldwide from 2016 to 2019
(in billions).” Statistics Portal, n.d.
https://www.statista.com/statistics/271644/ worldwide-free-and-paid-mobile-
app-store-downloads/.
Glossary
Application software: Computer programs and procedures necessary for carrying
out some specific task.
Horizontal-market software: Application software that can be used across a
wide range of different industries.
Kernel: A main component of an operating system that directly controls a
computer’s hardware and access to that hardware.
Operating system: Computer programs and data responsible for managing a
computer’s hardware resources and providing a platform for the operation of
application software.
Software: An organized set of data and instructions that governs the operation
of a computer.
System software: Computer software that controls the operation of the computer
and provides a platform on which application software operates.
Utility software: Small programs that perform tasks related to the management
of a computer.
Vertical-market software: Application software that is customized for use in a
particular industry.
Appendix C
Access Fundamentals
Learning Objectives
By reading and completing the activities in this appendix, you will be able
to:
· Given a database logical schema, create a database in Microsoft Access,
including the following: · · Creating tables, fields, and a primary key · ·
Establishing relationships between tables
· Add records to a table
Appendix Outline
· Introduction · Creating a Database in Access · Creating Tables · Creating
Relationships · Populating Tables with Data
Introduction
In this appendix, we will help you learn how to create and populate a database
using Microsoft Access. While most of you will probably never create large
databases as a major part of your jobs, it is useful to know how to create
small databases. As discussed in Chapter 5, using a database management system
(such as Access) to store some types of data can be very useful. As a result,
being able to create simple databases may be a handy skill in the future.
Fortunately, personal-level database management systems, such as Access, make
it relatively easy to create a database.
Once you have a good database design, you need to be able to do a few key
things when creating the database:
· Create tables · Create fields within tables · Choose the proper data type
for each field (e.g., text, numeric, date/
time) · Establish relationships among tables
A24 Appendix C
Figure C.1 Wish-List Database Schema
Once the database is created, you will also need to know how to add records to
the tables that make up the database.
We will use the “Wish-List” database from Chapter 5 to illustrate how to
accomplish all these tasks. The schema (design) of the database is shown in
Figure C.1. Creating a Database in Access In this section, we give you step-
by-step instructions for creating and populating the Wish-List database. The
first step (once you have a good design) is to give the database a name. This
effectively creates an empty container for the database. Microsoft Access
stores all database elements, including the data, in a single file on your
computer. In Access, this file has an .accdb file extension.
To create the database file, simply start Access and then click on “Blank
desktop database” in Access’s Getting Started screen, as shown in Figure C.2.
One of the most important aspects of creating a new, blank Access database is
knowing where it will be stored. You should make it a habit to click on the
folder icon in the screen displayed in Figure C.3. This allows you to specify
the location in which the database file will be stored. Also be sure to give
the database a meaningful name. These steps will help you locate the database
in the future. Once you have created the database file, you can start creating
tables.
Access Fundamentals A25
Figure C.2 Access’s Getting Started Screen Figure C.3 Specifying the Database
Name and Location
A26 Appendix C
Creating Tables Creating tables in Access is not difficult if you have a good
data model. To create a table, select the “Create” ribbon and then “Table
Design,” as shown in Figure C.4.
Figure C.4 Creating a Table
Selecting the Design view takes you to a form for adding the fields that make
up your table, as shown in Figure C.5. Enter the name of the field in the
“Field Name” column of the form. While Access allows spaces in field names, it
is generally a bad idea to use them, so we recommend avoiding spaces. When a
field name is made up of more than one word, capitalizing the first letter in
each word (as in FirstName) makes the field name easier to read.
Figure C.5 Entering Table Information
Access Fundamentals A27
TABLE C.1 Access Data Types
Data Type
Description
Short Text
Relatively short (up to 255 characters) alphanumeric values.
Long Text
Long blocks of text. Allows more than 255 characters, up to 1GB.
Number
Numeric values. There are several different variations of the number data type, including integer (from -32,768 to 32,767), long integer, and single and double precision.
Date/Time
Time-based data, including dates and times. The data can be displayed in several different formats.
Currency
Monetary values.
AutoNumber
A number that is automatically generated for each record in a table. The numbers are stored as integer values. The only purpose of an AutoNumber field is to make a record unique.
Yes/No
Boolean values such as true/false, yes/no, and on/off.
OLE Object
External objects that conform to the OLE standard. Microsoft recommends using the Attachment data type, which is less restrictive than OLE Object.
Hyperlink
Links to email addresses, websites, documents on intranets, etc.
Attachment
Files such as images and audio. You can attach multiple files to a single record.
Calculated
Data that are calculated from one or more fields.
In addition to the field name for each field, you will also need to select the
data type, which specifies what sort of data can be stored in each field.
Access’s data types are listed and briefly described in Table C.1. Data types
generally have additional properties that can be set. Discussing them in
detail is beyond our scope. However, there is one property we want to mention.
For text fields, you should set a reasonable field size rather than the
default of 255 characters, which is the maximum. For example, you probably do
not need 255 characters for the FirstName field. See
https://support.office.com/en-US/article/Data-types-for-Access-desktop-databa
ses-DF2B83BA-CEF6-436D-B679-3418F622E482 for more information about field
properties.
Recall from Chapter 5 that foreign key fields are used to link tables in a
relational database. When choosing data types, you must make sure that a
foreign key’s data type matches that of the primary key it references. When
creating a foreign key field that will refer to an AutoNumber primary key,
assign the foreign key a number data type. (AutoNumber field values are stored
as long integers.)
Selecting the Primary Key Fields Every table should have a primary key, as
indicated in the data model. To set a primary key in Access, select the field
or fields that make up the primary key by clicking on the gray area to the
left of the field name (see Figure C.6). Then select the “Primary Key” ribbon
button. If you do not see the button, select the “Design” ribbon
A28 Appendix C
under “Table Tools.” This will place a small key icon to the left of the
primary key field(s). If the key icon is already showing for the correct
field, you do not have to do anything further. If you accidentally select the
Figure C.6 Selecting a Single-Field Primary Key wrong field as the primary
key, simply select that field and click on the Primary Key button, which acts
as a toggle and will remove the key icon next to the field.
For a table with a composite primary key (which is made up of more than one
field), simply use a control-click to select all the fields in the primary key
and then proceed just as you did with the single-field primary key. The key
icon will appear next to each field in the primary key.
After you create a table, click the Save button, and you will be prompted to
specify the name for your table. Then proceed to the next table until all the
tables in the database are created. When all the tables are created, it is
time to create the relationships among the tables.
Creating Relationships
Before we describe how to create relationships, we want to point out the
importance of creating these relationships before you enter data. Creating the
relationships first can prevent you from entering foreign key values that do
not match an existing related primary key value. Without going into too much
detail, suffice it to say that each foreign key value should match the value
of a related primary key. When creating the relationships, we can tell Access
to make sure that this rule is followed. This rule is called “referential
integrity,” and it is a core principle of relational databases.
To create relationships among tables, go to the Database Tools ribbon and
select “Relationships,” as shown in Figure C.7.
Figure C.7 The Relationships Button
Selecting the Relationships button will bring up the Show Table dialog box,
which is shown in Figure C.8. Select all the tables, click on Add, and then
click on Close.
When you close the Show Table dialog box, you will see the tables you added.
You can resize and rearrange the layout of the tables to your liking. To
create a relation-
Figure C.8 Show Table Dialog Box Figure C.9 Edit Relationship Dialog Box Figure C.10 Relationships Created
Access Fundamentals A29
ship, click on a foreign key field and drag it to the primary key it
represents. When you release the mouse button, the Edit Relationship dialog
box appears, as shown in Figure C.9. Make sure that the table containing the
foreign key in the relationship is shown in the Related Table/Query column.
The table that contains the referenced primary key should be in the
Table/Query column. The correct fields should also be shown. Select the check
box next to “Enforce Referential Integrity,” which will tell Access to make
sure you do not violate the referential integrity rule we mentioned.
Continue this process until all the relationships are created. For our Wish-
List database, you should see something like Figure C.10. Keep in mind that
your layout might be different. You should see either an infinity symbol or a
“1” at the end of each relationship line. If you do not, it is because you
forgot to select “Enforce Referential Integrity.” You can correct this by
right-clicking on the relationship line and selecting “Edit Relationship.”
This brings up the Edit Relationship dialog box, which allows you to check the
referential integrity check box. If you created an incorrect relationship,
right-click on the line and select “Delete.” When all relationships are
created, you can close the Relationships ribbon. If you made any changes,
Access prompts you to save the changes.
Now that you have the relationships created, it is time to enter data.
A30 Appendix C
Populating Tables with Data There are several ways to enter data into an
Access table. In this section, we describe how to use the datasheet view of a
table to enter data. Access’s Navigation Pane, which is shown in Figure C.11,
lets you view and access the objects included in the database, including
tables. If you see the table into which you want to enter data, simply double-
click on it. If you do not see the table, click on the downward-pointing
navigation arrow, select “All Access Objects,” Figure C.11 Navigation Pane and
select the table you seek.
Double-clicking on a table opens the table in the Datasheet view.This is a
spreadsheet-like view of the table that facilitates data entry. Just enter
data into each field and record. Navigate between fields by pressing the “tab”
key or clicking on the next field. Note that a record is not entered into the
database until you proceed to the next record. An example of a Datasheet view
is shown in Figure C.12. Notice that (New) appears in the CustomerID field.
This is because CustomerID is an autonumber data type, so Access will
automatically enter the value.
All entries are restricted to the data type specified for that field. For
example, you cannot enter alphabetic data into a currency field. Trying to do
so results in an error similar to the one shown in Figure C.13.
Figure C.12 Datasheet View of a Table
Access Fundamentals A31
Figure C.13 Data Type Error
Another data entry error is entering a foreign key value that does not have a
matching primary key value in the related table. This is a violation of the
referential integrity error we discussed earlier. If an attempted entry
violates this rule, you will receive an error message such as the one shown in
Figure C.14.
Figure C.14 Referential Integrity Violation Error Message
Summary This appendix helped you learn how to create a database in Microsoft
Access. Here are the main points discussed in this appendix:
· It is important to have a good database design prior to creating a new
Access database.
· The first step in creating an Access database is to create the database’s
tables.
· For each field in a table, you must select the proper data type, which
specifies the data that can be stored in the field.
A32 Appendix C
· Each table should have a primary key. The primary key may be made up of one
or more fields. When a primary key is made up of more than one field, it is
called a composite primary key.
· It is important to create relationships among tables before entering data
into the tables.
· The Datasheet view of a table allows you to enter data into the table.
Additional Learning Activities
1. Create an Access database based on the data model shown below. The
database stores data for a small company that organizes various types of
events. Choose appropriate data types and lengths for each field. Also be sure
to create the proper relationships. Add at least five records to each table.
2. Create an Access database based on the data model shown below. The
database stores data related to books. AuthorOrder refers to the order in
which authors’ names appear. For example, for this book, Bélanger would have
an author order of 1, and Van Slyke’s author order would be 2. Choose
appropriate data types and lengths for each field. Also be sure to create the
proper relationships. Add at least five records to each table.
Access Fundamentals A33
3. Create an Access database based on the data model shown below. The
database stores information for a small bookstore’s purchasing department.
Terms refers to when the vendor must be paid. An example is Net 30, which
means the full payment is due in 30 days. Choose appropriate data types and
lengths for each field. Also be sure to create the proper relationships. Add
at least five records to each table.
4. Create an Access database based on the data model shown below. The
database stores data related to students and their membership in student
organizations. Classification refers to whether the student is a freshman,
sophomore, and so on. Choose appropriate data types and lengths for each
field. Also be sure to create the proper relationships. Add at least five
records to each table.
Appendix D
Advanced Access
Learning Objectives
By reading and completing the activities in this appendix, you will be able
to:
· Retrieve specified records from a database using Query by Example · Create
simple forms based on a specification · Create simple reports based on a
specification
Appendix Outline
· Introduction · Database Queries · Query by Example (QBE) · Forms · Reports
Introduction
In Appendix C, you learned how to create and populate a Microsoft Access
database. In this appendix, we help you learn how to retrieve data using
queries and how to create simple forms and reports. Forms are used to enter
records into a database and to retrieve and display small amounts of data.
Reports also display data but work better than forms for larger amounts of
data.
Database Queries
A query is an operation that retrieves specified records from a database. For
example, you could retrieve all records related to a particular order from an
order processing database. You specify which records to retrieve based on
criteria related to values of fields within one or more tables. These criteria
use operators such as equals (=), greater than (>), and less than (<). More
complex criteria use logical operators such as “and” and “or.”
Creating a query requires specifying three main things:
· The fields to retrieve · The tables from which to retrieve the data · Which
records to include
A36 Appendix D
The first two items (fields and tables) make up what database experts call
projection, which is simply specifying which fields to include in a query.
Since fields are contained in tables, projection also includes specifying
tables. (You can’t really specify fields without specifying tables.)
Specifying which records to include is called selection in database-speak.
There are two other important query operations: sorting and joining. We will
discuss them later.
Performing queries requires knowledge of the database’s structure. This
knowledge lets you know which fields and tables to include. The database
schema that we discussed earlier gives you a compact overview of the
database’s structure. The schema shown in Figure D.1 shows the structure of
part of the Northwind database, which is the sample database included with
Microsoft Access 2007. You can receive this database from your instructor. You
can view the structure of an Access database by selecting the “Database Tools”
ribbon and then selecting “Relationships.” (Note: You can ignore the I#
designators in the left-hand column of the tables below.)
Figure D.1 Northwind Database Schema
Advanced Access A37
Query by Example (QBE)
Relational database management systems all include one or more query
languages. A query language is a method and set of rules for creating queries.
Access includes two query languages: Structured Query Language (SQL) and Query
by Example (QBE). SQL is a very powerful language that is widely used for
software applications that interact with databases. Writing SQL queries is a
specialized skill that is beyond our scope. QBE was designed to be an easier
way to specify queries. While QBE is less powerful than SQL, it is easier to
understand and is sufficient for many users. Because of this, we focus on QBE.
It is easier to understand QBE concepts if we work through an example. Our
goal is to create a query that shows the following for all customers who are
in the United Kingdom or Germany:
· CustomerID · CompanyName · City · Region · PostalCode · Country
We want to sort the results by CompanyName. To create a query, select the
“Create” ribbon and select “Query Design.” This opens the Show Table dialog
box. (You may remember this dialog box from when we created relationships in
Appendix C.) The ribbon and dialog box are shown in Figure D.2. The next step
is adding the tables that should be included in your query. Only include
necessary tables. Including extra tables slows down the performance of the
query and in some cases may lead to unexpected results. Select the appropriate
tables and click on “Add.” In our example, we only need one table, Customers.
Clicking on Close closes the dialog box. You will see the selected table(s) in
the top portion of the QBE window. The next step is to select the fields to
include by clicking on the field and then dragging it to the QBE grid, which
is shown at the bottom of the QBE window. (Double-clicking also works.)
Continue this until all the desired fields are selected. You can rearrange the
fields by clicking on the gray bar above the field name in the grid, which
selects the field. Then you can drag the field to the desired location.
Earlier, we mentioned that sorting is an important query operation. To sort a
query using QBE, simply click on the Sort row under the sort field(s) and then
select either “Ascending” (AZ) or “Descending” (ZA). Figure D.3 shows the
almostcomplete QBE grid for our query. (We still need to provide the criteria
used to determine which records to include.) One limitation of QBE is that it
is somewhat inflexible in specifying multifield sorts. Multifield sorts are
simply sorting specifications that involve more than one
A38 Appendix D Figure D.2 Create Ribbon and Show Table Dialog Box Figure D.3 QBE Grid with Fields and Sorting
Advanced Access A39
Figure D.4 Query Results
field. For example, we might want to sort by Country, then City, then
CompanyName. Access determines the order in which fields are sorted by their
position in the QBE grid, with leftmost fields sorted first. That means that
the field you want to sort first must be to the left of the field you want to
sort next and so on. This may conflict with the order in which you want the
fields to appear. In practice, this is not a major problem, since we usually
use reports to display information rather than using the “raw” query results.
We can change the sort order when creating the report.
Once you have the query specified, click on the Run button, which is located
on the left-hand side of the Query Tools’ Design ribbon. This executes the
query and shows the results in a Datasheet view, as shown in Figure D.4. You
may notice that our results include companies in countries other than the
United Kingdom and Germany. This happened because we have not provided row
selection criteria yet. We provide the criteria next.
We use the Criteria section of the QBE grid to tell Access what to record in
the query. In our case, we enter “UK” on the first Criteria line under Country
and then enter “Germany” on the next line. It is important to understand that
criteria on different lines are treated as “Or” conditions. In other words, as
long as a record meets any of the criteria, that record is included in the
query’s results. Criteria on the same line are treated as “And” conditions,
which means that all criteria on that line must be met for a record to be
included.
Figure D.5 shows the QBE grid with the Country criteria added. Notice that the
criteria are enclosed in double quotes (” “). This is necessary when you want
to match particular text values. The quotes tell Access that the entry is text
rather than
A40 Appendix D
Figure D.5 Completed Query
a variable name. The Show row in the QBE grid is also worth noting. Sometimes
you want to use a field in a query but do not want to show the field in the
results. This usually happens when you want to select records based on a
field’s value but do not want that field to show up in the query results.
Unchecking the Show check box tells Access not to include that field in the
query results.
Figure D.6 shows the results of our query. Note that our results now include
only companies that are in the United Kingdom and Germany.
Many queries require data from more than one table. These queries are only
slightly more complicated to build than our example query. The main difference
is that a join operation must be completed. A join connects data from related
records; usually these records are in separate tables. The good news is that
Access creates these “joins” automatically, based on the relationships created
when the database was built.
We want to create a query that shows the OrderID, OrderData, CompanyName,
City, and Country for all orders. The results should be sorted in ascending
order of OrderDate and then CompanyName (ascending). We start the same way we
did for our earlier query, but this time we need to add the Orders table along
with the Customers table. We need to do this because two of our fields,
OrderID and OrderDate, are part of the Orders table. Next, drag the desired
fields to the QBE grid and select ascending sorting for OrderDate and
CompanyName. Finally, we add “UK” and “Germany” as criteria for the Country
field. The completed QBE grid is shown in Figure D.7. Creating queries with
more than two tables follows the same basic procedure.
Advanced Access A41
Figure D.6 Query Results
Figure D.7 QBE Grid for Multitable Query
One error that we sometimes see is forgetting a necessary table.This usually
happens when a linking table is involved. (Remember that a linking table is a
table that primarily serves to link records from two other tables.
OrderDetails is a good example in the Northwind database.) Consider the query
shown in Figure D.8, which is supposed to show the OrderID and OrderDate for
each order along with the ProductName of each
A42 Appendix D
Figure D.8 Query with Missing Table
product included in each order. (Notice that there is no relationship line
between Orders and Products. This tells us that something is wrong.)
Unfortunately, Access lets you run this query, but the results will include
too many records. We need to add the OrderDetails table, which properly links
records from the Orders table to related records in the Products table.
Fortunately, adding the table is easy; just click on the “Show Table” button
(which is circled in Figure D.8) and add the table.
As you might imagine, you can create much more complex queries, particularly
with respect to record selection criteria. Microsoft provides an informative
Web page (https://support.microsoft.com/en-us/office/examples-of-query-
criteria-3197 228c-8684-4552-ac03-aba746fb29d8) that can help you learn more
about complex record selection criteria.
You can think of a query as a temporary table. Because of this, a query can be
used for many of the same purposes as a table, including serving as the
foundation for forms, reports, and even other queries. It is important to keep
this in mind as we move to our next topics: forms and reports.
Advanced Access A43
Forms Access forms serve two main purposes. First, forms allow for easier,
more controlled data entry than using the Datasheet view. Second, forms are a
convenient way to display relatively small amounts of data. For example, an
order entry clerk could use a form to enter data for a new order or retrieve
information about an existing order. In addition, the clerk could use the form
to update the existing order.
The Form Wizard is usually the easiest way to create a form, especially for
casual users of Access. The Form Wizard uses dialog boxes to step you through
the process of creating a form. Start the Form Wizard by selecting the
“Create” ribbon and then clicking on “Form Wizard,” as shown in Figure D.9.
The first thing the Form Wizard needs to know is what table or query to use as
the basis for the form. If all the data you want to use in the form are
contained in a single table, you can simply select that table. If your form
needs data from multiple tables, it is more effective to create a query that
includes all the necessary fields and then base the form on that query. (We
will discuss this method further later in this section.)
Figure D.9 Selecting a Table in the Form Wizard
A44 Appendix D
Figure D.10 Selecting Fields in the Form Wizard
Next, you need to select the fields to include in the form. Notice the list of
available fields in the dialog box. To include a field, select it and then
click on the greater-than symbol (>). If you want to include all of a table’s
fields, select the double greater-than symbol (>>). You can remove a field
from the Selected Fields list by clicking on the less-than symbol (<). Figure
D.10 illustrates the steps in selecting fields.
Next, you need to select a layout for your form. For most forms, the Columnar
layout works well, but you may want to experiment with the other layouts. When
you select the radio button for a layout, a mock-up of that layout appears in
the lefthand side of the dialog box. When you have selected your layout, click
on Next, which brings up a dialog box that lets you name your form. You should
name the form so that it is easy to recognize the form’s purpose. Figure D.11
shows the dialog box for selecting your layout.
Figure D.12 shows our form in Form view and Design view. (The Form view is in
the upper left-hand corner of the figure.) Users interact with the form in
Form view. The Form view lets users navigate among records using the controls
at the bottom of the form. Users can add, update, and delete records using the
form. In addition, the search function allows users to retrieve specific
records.
Often the Form Wizard creates a form that is fine as is. Sometimes, however,
you may want to modify the form’s appearance or add special functions, such as
lists and combo boxes. You may also want to change labels to make them more
meaningful. These changes are done through the Layout or Design views. (Change
views by selecting the appropriate view from the Views tool on the Home
ribbon.) The
Advanced Access A45
Figure D.11 Form Wizard Layout Dialog Box
Figure D.12 Form and Design Views
Layout view is simpler and is often easier to work with. The Design view is
more detailed, which allows you to make some changes that are not possible in
the Layout view.
Let’s take a look at a more complicated form that includes fields from
multiple tables. In this case, we want to display products customers ordered.
The database we will use for this is the Wish-List database that was built in
Appendix C. The form will have six fields from four tables, as shown in Figure
D.13. As we discussed earlier,
A46 Appendix D
Figure D.13 Query for Wish-List Form
a form can be based on a table or a query. In this case, we want to create a
query that includes all the fields we want to include in the form. We will
base our form on this query. You can choose fields from multiple tables in the
Form Wizard, but we prefer basing the form on a query.
We want to create a special type of form that actually uses two forms: a main
form and a subform. This arrangement is common when you want to display data
from multiple tables. The main/subform arrangement works well in this case
because it provides a clean way to show information about a customer and the
items on her or his order in a single form. It also lets us view multiple
products on the order at the same time. The customer and order information are
shown on the main form, and the product information is shown on the subform.
This may sound complicated, but the Form Wizard makes it pretty easy to
create.
Once the query is created, we start the Form Wizard as before and base the
form on the query, as shown in Figure D.14. We also need to select the fields
to include in the form.
Clicking on Next brings up a dialog box that we did not see in our earlier
example. This dialog box lets us choose how we want to view the data in the
form. In this case, we want to view the data by customer, then within each
customer we want to see the products ordered. The dialog box illustrates the
general arrangement of the data. We also need to select “Form with
Subform(s),” as shown in Figure D.15. The next two dialog boxes let us choose
the layout for our form. In this case, we select “Datasheet.” Finally, we must
name and save the forms. (Note that we are creating two forms with this
wizard: the main form and the subform.)
Figure D.16 shows the resulting form. We can use the form to navigate among
customers or among products within a particular customer’s orders. As before,
we can modify the forms using the Design view.
Advanced Access A47 Figure D.14 Basing a Form on a Query Figure D.15 Form Wizard for a Main Form/Subform Figure D.16 Main/Subform
A48 Appendix D
The details of how to modify forms are beyond the scope of this appendix. To
learn more about how to add controls and make other changes to forms, see
https:// support.microsoft.com/en-us/office/create-a-form-in-access-5d550a3d-
92e14f38-9772-7e7e21e80c6b. Reports Like forms, Access reports show data, but
reports allow you to show many more records. In addition, reports are not
intended for data entry. Creating a report is very similar to creating a form.
We illustrate how to create a report using the Northwind database. We want our
report to show the fields shown in Figure D.17. Often, you want a report to
only show data that meet certain criteria. In this case, we only want to show
data for orders that were placed in 1998 or later. Since our report is based
on our query, we can limit the report by limiting the query. So we include the
criteria shown in Figure D.17. (We could also use >= #1/1/1998# as our
criterion.) The pound symbols (#) tell Access that you are using a date for
the criterion. (See https://support.microsoft .com/en-us/office/examples-of-
query-criteria-3197228c-8684-4552-ac03-aba746 fb29d8 for more information on
using dates as criteria.)
After creating the underlying query, we start the Report Wizard, which is on
the Create ribbon. After selecting the correct query and fields, we need to
tell the wizard how we want to view the data on our report, as shown in Figure
D.18. This is similar to the Main/Subform Wizard. In this case, we want the
report to show the data by customer. Then within each customer, we want to see
data about the customer’s
Figure D.17 Query for a Report
Advanced Access A49
Figure D.18 Report Wizard
order. In a report, you can have multiple grouping levels, which can also be
added later through a dialog box. After selecting how we want to view the
data, we tell Access how to sort the data and then select the report’s
orientation (portrait or landscape). Note that Access automatically sorts by
any grouping levels (CustomerID, in our case). For this report, sorting by
OrderDate within each customer makes sense. Of course, we also need to save
our report. Remember to give the report a meaningful name.
When finishing the report, you choose whether to preview the report or open it
in the Design view so that you can make changes. If you preview the report,
Access opens the report in a print preview mode. To exit this view, just click
on the Close Print Preview button. Like forms, you can modify reports using
either the Layout or Design view. See https://support.office.com/en-US/article
/Introduction-to-reports -in-Access-E0869F59-7536-4D19-8E05-7158DCD3681C for
more information on how to modify reports using these views. If you need to
change the criteria for selecting which records to include in a report, all
you have to do is change the criteria in the underlying query and then rerun
the report. The new report will use the updated criteria.
Summary This appendix covers how to perform queries and create forms and
reports using Microsoft Access. Here are the main points discussed in this
appendix:
· Database queries · Creating queries using Query by Example · Creating forms
· Creating reports
A50 Appendix D
Learning Activities
The following activities use the Northwind database, which is available from
your instructor.
1. Create a query that shows the Country, City, CustomerID, and Customer
CompanyName for all customers located in France, Spain, or Sweden. Sort the
query by Country, then CompanyName.
2. Create a query that shows the ProductID, ProductName, and UnitPrice for
all products with a UnitPrice greater than $50. Sort the query in descending
order of UnitPrice.
3. Create a query that shows the CategoryName, ProductID, ProductName, and
UnitPrice for all products in the Produce or Seafood categories. Sort the
query in order of CategoryName, then ProductName.
4. Create a query that shows the Supplier CompanyName, CategoryName,
ProductID, and ProductName for all products in the Produce or Seafood
categories. Sort the query in order of Supplier CompanyName, then
CategoryName, then ProductName.
5. Create a query that shows the OrderID, OrderDate, Customer CompanyName,
and Country for all orders placed by customers in the United Kingdom. Sort the
query by CompanyName.
6. Create a query that shows the OrderID, OrderDate, Customer CompanyName,
ProductName, Quantity, and UnitPrice for all products included in the order.
Use UnitPrice from the Order Details table (not the Products table). Only show
orders from the companies named “Lonesome Pine Restaurant” or “Rattlesnake
Canyon Grocery.” Sort the query by OrderDate, then CompanyName.
7. Create a form that will let you enter data for all fields included in the
Customers table.
8. Create a form that shows the CategoryID, CategoryName, and Description for
each category, along with the ProductID, ProductName, and UnitPrice for all
products in that category. The form should have Category in the main form and
Products in the subform.
9. Create a well-formatted report that shows the data from the query in
Activity #2.
10. Create a well-formatted report that shows the Supplier’s CompanyName,
CategoryName, ProductName, and UnitPrice for all products.
11. Create a well-formatted report that shows the data from the query in
Activity #6.
Appendix E
Advanced Database Concepts
Learning Objectives
By reading and completing the activities in this appendix, you will be able
to:
· Define terms related to conceptual data modeling · Understand the elements
of a conceptual data model · Develop a conceptual data model based on business
rules
Appendix Outline
Introduction The Entity-Relationship Diagram
Learning Activity E.1: Interpreting an ERD Learning Activity E.2: Reading
Relationships in an ERD Creating an E-R Diagram
Introduction
The ability of a database designer to understand and model the information
that an organization uses is a critical design skill. Data modelers use a
variety of tools and techniques to understand an organization’s data. To
understand how to properly model data, you must become familiar with a
modeling approach known as entityrelationship modeling, which is the subject
of this chapter.
Entity-relationship (E-R) modeling is one approach to semantic modeling. When
database designers attempt to understand and represent meaning, they are
engaged in semantic modeling, which can help in making database design more
systematic. Although a number of approaches to semantic modeling exist, this
appendix focuses on E-R modeling.
E-R modeling, introduced by Chen (1976), consists of a number of activities
that help database designers understand the objects the organization wants to
store information about, the important characteristics of these objects, and
the associations among various objects. Although most of you probably will not
design and develop a database from scratch, many of you may be involved in
teams that help database experts design complex databases and data warehouses.
Having some knowledge of the process will help you be a more effective team
member.
A52 Appendix E
The Entity-Relationship Diagram
The end result of E-R modeling is the E-R diagram (ERD), a graphic
representation of the logical structure of a database. An ERD serves several
purposes. First, the database analyst/designer gains a better understanding of
the information to be contained in the database through the process of
constructing the ERD. Second, the ERD serves as a documentation tool. Finally,
the ERD is used to communicate the logical structure of the database to users.
The E-R modeling process identifies three basic elements: entities,
attributes, and relationships. These are described in the following sections.
Entities The concept of an entity is similar to the concept of a table, which
we discussed in Chapter 5. An entity is a thing that can be distinctly
identified (Chen 1976). In database design, entities are the “things” about
which the database stores information. Entities can include, but are not
limited to, the following:
· Tangible items, such as equipment · Concepts, such as accounts · People,
such as employees · Events, such as sales · Places, such as business locations
The term entity type refers to a number of related items, while an entity
instance refers to a single occurrence of an entity type. For example,
employee number 12345 refers to a single occurrence, or entity instance, of
the entity EMPLOYEE. The term entity refers to entity type. In addition, note
that the term entity occurrence is sometimes used rather than entity instance.
Attributes An attribute is a single data value that describes a characteristic
of an entity. Other terms, such as data item and field (which we used in
Chapter 5), describe the same essential concept.
Each entity has a corresponding set of attributes that represent the
information about the entity that the organization is interested in. For
example, a university may wish to know the name, address, phone number, and
primary email address of each student. Put in database terms, STUDENT is the
entity of interest, and NAME, ADDRESS, PHONE, and EMAIL are the attributes of
interest.
Primary Keys Every entity must have a primary key. The primary key is an
attribute or combination of attributes that uniquely identifies an instance of
the entity. In other words, no
Advanced Database Concepts A53
LEARNING ACTIVITY E.1
Interpreting an ERD
Answer these questions for the ERD shown below: 1. How many tables are
represented? 2. For each table, list its primary key attribute(s) and any
foreign keys in the table. 3. List the relationships represented in the ERD.
two instances of an entity may have the same value for the primary key. Later
in this appendix, we discuss how to make good choices when deciding on primary
keys. Sometimes it is helpful to use more than one attribute to form a primary
key. When a primary key for an entity is made up of more than one attribute,
the key is called a composite key.
Relationships A relationship is an association among entities. For example, a
STUDENT entity might be related to a COURSE entity, or an EMPLOYEE entity
might be related to an OFFICE entity. In the ERD, lines between entities
represent relationships.
Different relationship degrees exist. The degree of a relationship refers to
the number of entities involved in the relationship. Although others exist, it
is often sufficient to understand the meaning of three relationship degrees:
unary, binary, and ternary. A unary relationship (also called a recursive
relationship) is a relationship involving a single entity. A relationship
between two entities is called a binary relationship. When three entities are
involved in a relationship, a ternary relationship exists. Relationships that
involve more than three entities are referred to as n-ary relationships, where
n is the number of entities involved in the relationship. In this appendix, we
deal exclusively with binary relationships; other relationship degrees are
beyond our scope.
A54 Appendix E
Cardinality Relationships can also differ in terms of their cardinality.
Maximum cardinality refers to the maximum number of instances of one entity
that can be associated with a single instance of a related entity. Minimum
cardinality refers to the minimum number of instances of one entity that must
be associated with a single instance of a related entity. The following
examples of binary relationships illustrate the concept of maximum
cardinality. Minimum cardinality is discussed later in the next section. Note
that each cardinality type is followed by a shorthand notation in parentheses.
If one CUSTOMER can be related to only one ACCOUNT and one ACCOUNT can be
related to only a single CUSTOMER, the cardinality of the CUSTOMERACCOUNT
relationship is one-to-one (1:1). If an ADVISOR can be related to one or more
STUDENTS but a STUDENT can be related to only a single ADVISOR, the
cardinality is one-to-many (1:N). Finally, the cardinality of the relationship
is many-to-many (M:N) if a single STUDENT can be related to one or more
COURSES and a single COURSE can be related to one or more STUDENTS. Many-to-
many relationships cannot be directly implemented in a relational database, so
it is a common practice to convert them to a pair of one-to-many
relationships. Each original entity is related to an intermediate entity,
which serves to link the two original entities together. This intermediate
entity is called an associative entity because it associates the original
entities. This is conceptually similar to the linking table we discussed in
Chapter 5.
In E-R diagrams, cardinality is represented by symbols attached to the
relationship line. A single vertical line intersecting the relationship line
indicates a “one” cardinality. A crow’s foot symbol indicates a “many”
cardinality. (Note that there are other ways to show cardinality.) Figure E.1
(a) shows the E-R diagram for a 1:1 relationship, Figure E.1 (b) shows a 1:N
relationship, and Figure E.1 (c) shows an M:N relationship that has been
converted to a pair of 1:N relationships. We have also added foreign keys
where necessary. (We discussed foreign keys in Chapter 5.)
When determining the cardinality of relationships, it is important to remember
that cardinality specifies how many instances of an entity can be related to a
single instance of a related entity. The trick to determining the cardinality
of a relationship is to determine the cardinality of one side at a time.
For example, suppose you want to determine the cardinality of the STUDENT to
ADVISOR relationship. The first step is to determine how many STUDENTS one
ADVISOR can be related to. One ADVISOR can be related to many STUDENTS. To
represent this on an E-R diagram, show the “many” symbol (the crow’s foot)
next to the STUDENT entity. Next, you need to determine how many ADVISORS one
STUDENT can be related to. While this may vary from school to school, in this
case, the answer is one. To show this on the E-R diagram, put the “one” symbol
(vertical line) next to the ADVISOR entity. Figure E.2 illustrates the
process.
Advanced Database Concepts A55 Figure E.1 Cardinality Symbols Figure E.2 Determining Cardinalities
A56 Appendix E
LEARNING ACTIVITY E.2
Reading Relationships in an ERD
Answer the following questions about the ERD shown below. Partner with another
student. One of you will explain your answers to 13, and the other will
explain your answers to 46. 1. Are there any employees who are not assigned
to an office? 2. Can there be more than one employee in an office? 3. Can
there be a building with no offices? 4. Does each employee have to be assigned
to an office? 5. Can there be a department with no employees assigned to it?
6. Does each employee have to be assigned to a department?
A similar process is used to determine minimum cardinality. The zero going
into STUDENTS indicates that some advisors are not assigned any students. The
innermost “1” going into ADVISORS shows that each student must be assigned to
at least one advisor. Since the maximum cardinality from STUDENTS to ADVISOR
is also 1, each student must have exactly 1 advisor. Figure E.3 shows the
maximum and minimum cardinalities.
Figure E.3 Maximum and Minimum Cardinalities
Advanced Database Concepts A57
If you have trouble determining the cardinality of a relationship, the
following method may help. Assign a name to the entity instance you want to
hold to one. For example, if you were having trouble determining the
cardinality of the ADVISOR to STUDENT relationship, you could ask yourself,
“How many ADVISORS can Jan Smith have?” or “How many STUDENTS can Dr. Johnson
advise?” Creating an E-R Diagram A number of steps are required to create an
E-R diagram that accurately represents the organizational data. These steps
are summarized in Figure E.4 and discussed in this section.
Figure E.4 Steps in Building an E-R Diagram
E-R Modeling Example: An Order Entry Form To illustrate the steps in building
an E-R diagram, we analyze an Order Entry Form. A sample of the Order Entry
Form is shown in Figure E.5. Note that we are purposely keeping the form very
simple. For example, the customer’s address is omitted.
Figure E.5 Order Entry Form
Step 1: Model the Entities The first step in creating an E-R diagram is to
model the entities. Recall that an entity is simply something about which the
organization wishes to store data. A number of information sources may be
helpful when identifying entities, including forms, data entry screens,
reports, and user interviews.
It is important to realize that an entity is basically defined by its
attributes, so when identifying entities, look for groups of related
attributes. It is particularly helpful to look for possible primary keys for
an entity. Generally, when a form has an
A58 Appendix E
identifier for a possible entity, it is likely to be the entity. For example,
if a form contains a space for a customer number, then the database (and the
E-R diagram) probably needs to contain a CUSTOMER entity.
In user interviews, be particularly aware of nouns that the user mentions.
Generally, entities are named with nouns such as CUSTOMER, STUDENT, and
EQUIPMENT. Nouns that crop up often in the course of a user interview are good
candidates for entities.
As you identify candidate entities, also try to determine attributes for the
entities. An effective technique is to write down any attributes you identify
next to the entity to which they belong. This may provide a means for you to
distinguish between entities and attributes, discussed later in this section.
Another useful E-R modeling technique employed by database analysts is to
highlight each item on a form, report, transcript, or other information source
to indicate that the item has been modeled. For example, when analyzing a
form, mark each item on the form as you write it on your list of possible
entities and attributes. This can serve as a useful check. You can go back
through each information source and make sure each important item is marked.
At this stage, be liberal in identifying entities. If there is any possibility
that some item on a form or some sentence in an interview identifies an
entity, include it in the list of possible entities. It is more difficult to
identify entities that are missed than it is to remove candidate entities that
turn out not to be entities required in the database. Most of the potential
entities that are later rejected turn out to be attributes of an entity rather
than entities in their own right. These can be culled later.
In the case of the Order Entry example, there are several candidate entities.
The initial analysis of the Order Entry Form indicates that there are three
entities that are clearly represented on the form: ORDER, CUSTOMER, and
PRODUCT.
After identifying the entities, it may be necessary to make some assumptions.
For example, when deciding which attributes to place with each entity, we must
make an assumption about PRICE. If the price of a product does not change from
one order to another, then PRICE is a function of PRODUCT. If, however,
different orders for the same product have different prices, then PRICE is a
function of the relationship between PRODUCT and ORDER. In actual practice,
the analyst must consult with the user representative to determine which view
is correct. We will make the assumption that PRICE varies across orders–PRICE
is a function of the relationship between PRODUCTS and ORDERS. At the moment,
we will not assign these attributes to an entity. We will model them later in
the process. Note that the attribute QTY (quantity) is purposely absent from
the list to illustrate the process of checking the ERD.
Table E.1 shows the initial list of entities and attributes identified in the
analysis of the Order Entry Form. The last row shows attributes that do not
belong to a single entity.
Once the list of candidate entities and their attributes is complete, the
database analyst must determine which entities need to be in the database and
which should
Advanced Database Concepts A59
TABLE E.1 Entity List for the Order Entry Form
Entity
Attributes
Orders
ORDER-ID, DATE
Products
PRODUCT-ID, DESCRIPTION
Customers
CUSTOMER-ID, CUSTOMER-NAME
PRICE, QUANTITY
be excluded. These decisions are based on whether a candidate entity is an
entity or an attribute. Many of the candidate entities clearly belong in the
database. The most likely candidate entities that should be rejected are those
for which no attributes have been identified. If you are unable to find any
attributes for an entity, that entity is probably in reality an attribute for
another entity. For example, CUSTOMERNAME might be identified as a possible
entity. However, no attributes can be identified for CUSTOMER-NAME. This leads
to the conclusion that CUSTOMERNAME is not an entity but is better considered
an attribute of the CUSTOMER entity.
Not all cases are so clear, however. Take the example of ADDRESS. Perhaps a
number of attributes were identified for this candidate entity, such as
NUMBER, STREET, CITY, STATE, and POSTAL-CODE. Does the presence of these
potential attributes of ADDRESS indicate that ADDRESS is an entity? Although
in some situations this will be the case, more often all of these should be
attributes of some other entity, such as CUSTOMER. Resolving these types of
situations becomes easier with more experience in data modeling and greater
knowledge of the organization.
Step 2: Choose Primary Keys
After identifying and modeling each entity and its attributes, primary keys
must be chosen for each entity. For many entities, the primary key is obvious
or already in place in the organization’s existing information systems. For
example, a university may already be using a student identification number. In
such cases, the best course of action is to retain the existing key.
When a natural primary key does not exist and is not obvious, candidate keys
must be identified. The major requirement for an attribute to be a primary key
is that the attribute uniquely identifies instances of the entity. In other
words, for each instance of the entity, the value of the attribute must be
unique. In addition, the proper functioning of the database requires that
primary keys can never be null. The primary key for each entity must always
have a unique, valid value for each instance of the entity.
Other characteristics of primary keys are also desirable. In general, a good
primary key is sometimes called data-less. This means that no actual
information is contained in the primary key. An account number, for example,
typically contains no useful information and serves no purpose other than to
identify an account. Primary
A60 Appendix E
TABLE E.2 Characteristics of a Good Primary Key Desirable Primary Key
Characteristics 1. Uniquely identifies an entity instance 2. Nonnull (always
has a value) 3. Data-less 4. Never changes
keys that are data-less also possess another desirable characteristic–they
never change. Primary keys whose values change over time lead to a number of
problems with maintaining the database.
An example illustrates the problem with using a primary key that does not meet
the characteristics in Table E.2. Suppose that a database designer decided to
make the primary key of the entity CUSTOMER the customer’s phone number.
Assume for the moment that each customer has a phone number and that no two
customers can have the same phone number. In other words, assume that the
attribute PHONE is unique and nonnull. While this is enough for PHONE to serve
as the primary key of CUSTOMER, problems may occur because PHONE is not data-
less and is also subject to change. What happens if a customer changes his or
her phone number? Should the database be updated to reflect a new value for a
primary key? This causes a number of database maintenance problems. These
maintenance problems stem from using the primary key to link records in
different tables. If the primary key value is changed for a record in the
CUSTOMER table, we must also change foreign key values in related records in
other tables.
When no suitable primary key can be found among the existing attributes for an
entity, it is acceptable to simply create a new attribute. These newly created
primary keys are counters that increment with each new instance of an entity
in the same manner in which a check number increments from one check to the
next.
For the Order Entry example, we need to identify primary keys for three
entities: ORDER, PRODUCT, and CUSTOMER. Fortunately, all of these entities
have
Figure E.6 Order Entry Form Entities and Attributes
Advanced Database Concepts A61
attributes that make acceptable primary keys. For ORDER, ORDER-NO is a good
choice for the primary key, for PRODUCT, PROD-ID makes an acceptable key, and
CUSTOMER-ID is a good key for CUSTOMER. Notice that all of these satisfy our
requirements for a primary key.They are unique for each occurrence of the
entity, they will never be null, they are data-less, and they are not subject
to change.
Once all the entities and their attributes and primary keys are identified,
the actual drawing of the E-R diagram can begin. Figure E.6 shows the results
for the Order Entry example. You might notice that the unassigned attributes
from Table E.1 are missing. We will take care of this in a later step.
When all the entities, attributes, and primary keys are modeled, it is time to
identify and model the relationships among entities.
Step 3: Model Relationships Relationships among entities are a critical part
of the E-R diagram. When these relationships are implemented in the database,
they provide the links among the various tables that give the database its
flexibility. To maximize the flexibility of a database, relationships must be
properly identified and modeled.
Many relationships are relatively easy to recognize, such as those between
ORDER and CUSTOMER or between STUDENT and COURSE. Others, however, are less
clear. Although becoming truly proficient at recognizing relationships
requires experience and practice, there are some general guidelines that can
help the database analyst recognize relationships.
When examining forms, reports, and entry screens, be on the lookout for
entities whose attributes appear on the same form, report, or screen. Analysis
of the Order Entry Form (Figure E.9) reveals three entities: CUSTOMER, ORDER,
and PRODUCT. We can conclude that these entities may be related to one
another. But how are they related? In cases where the form being analyzed only
represents two entities, the relationship is fairly obvious–the relationship
is between the two entities. However, with the Order Entry Form, there are
three entities represented. Further consideration is required to determine how
these entities are related. Knowing something about the organization is
particularly helpful in such situations.
In the case of the Order Entry Form example, ORDER and CUSTOMER are related,
as are ORDER and PRODUCT. However, we know that there may not necessarily be a
relationship between PRODUCT and CUSTOMER. Because both are related to ORDER,
we can report which products are ordered by a particular customer.
In some cases, the analyst works from user interviews rather than forms. In
these cases, the analyst can examine transcripts of user interviews to
determine the relationships among entities. Often, users mention related
entities in the same sentence. Consider the excerpt from a user interview
shown in Figure E.7. Notice how the user talks about orders and customers in
the same sentence. Once ORDER and CUSTOMER are identified as entities,
mentioning both in the same comment is a good indication that the entities are
related.
A62 Appendix E
ANALYST: What information do you need to know about orders? USER: Well, for
each order, we need to know the ID number, the date of the
order number, and which customer placed the order.
Figure E.7 Excerpt from a User Interview
Once you identify relationships, you need to determine the cardinalities of
the relationships.
Step 4: Determine Cardinalities Recall that there are both maximum and minimum
cardinalities. The maximum cardinality of a relationship is the number of
instances of one entity in a relationship that can be related to a single
instance of another entity in the relationship. In contrast, the minimum
cardinality is the number of instances of one entity that must be related to a
single instance of the related entity. Many novice database analysts find
determining the cardinalities of relationships more confusing than identifying
entities, attributes, and relationships. Thankfully, there are ways to make
this task easier.
It is common to determine maximum cardinalities before minimum cardinalities.
There are two parts to the maximum cardinality of a binary relationship, one
for each entity. Recall the example of the ADVISOR-STUDENT relationship
discussed earlier in this appendix. One ADVISOR can be related to many
STUDENTS. This indicates that the STUDENT side of the relationship has a
“many” cardinality. This is only half of the relationship’s cardinality,
however. To complete the cardinality, we must recognize that one STUDENT can
be related to only a single ADVISOR. This tells us that the ADVISOR side of
the relationship has a cardinality of one. Thus the cardinality of the
ADVISOR-STUDENT relationship is one-to-many.
One technique that often helps analysts determine the proper cardinality of a
relationship is to give the instance of the single-instance side of the
relationship a name. For example, consider trying to determine the cardinality
of the STUDENTCOURSE relationship. First, hold the STUDENT entity to a single
instance to determine the cardinality of the COURSE side of the relationship.
This is easier to do if, rather than saying “How many COURSES can a STUDENT be
related to?” you say “How many COURSES can Mary Wilson be related to?” The
answer, of course, is “many,” so the COURSE side of the relationship has a
“many” cardinality. To determine the cardinality of the STUDENT side of the
relationship, ask “How many students can be related to MIS380?” Once again,
the answer is “many,” indicating that the STUDENT side of the relationship
also has a “many” cardinality. When asking these cardinality questions,
remember that you are always determining what cardinality symbol to draw next
to the entity you are not holding to one instance.
Advanced Database Concepts A63
Let’s return to the Order Entry Form example. There are two relationships: one
between ORDER and PRODUCT and the other between CUSTOMER and ORDER. One
instance of ORDER can be related to many instances of PRODUCT. In other words,
one ORDER can contain many PRODUCTS. Thus the cardinality from ORDER to
PRODUCT is many. Turning to the other side of the relationship, we can see
that one instance of PRODUCT can be related to many instances of ORDER–a
single PRODUCT can be on many ORDERS. So the cardinality from PRODUCT to ORDER
is many. Combining the two sides gives us a many-to-many cardinality between
ORDER and PRODUCT.
Now we must analyze the relationship between CUSTOMER and ORDER. A single
ORDER can be related to only one CUSTOMER. In other words, a single ORDER
can’t be placed by more than one CUSTOMER. This means that the cardinality
from ORDER to CUSTOMER is one. On the other side, a single CUSTOMER can place
many ORDERS, so the cardinality from CUSTOMER to ORDER is many, and we can say
that the cardinality of the CUSTOMER-ORDER relationship is one-to-many.
Now the minimum cardinalities must be determined. Both maximum and minimum
cardinalities are determined by business rules. However, cardinalities are
sometimes less obvious than others when the analyst does not have good
knowledge of the organization. For example, it is relatively easy to determine
that an ORDER must be related to at least one CUSTOMER, indicating that the
minimum cardinality from ORDER to CUSTOMER is one. The same can be said for
ORDER and PRODUCT. But must a CUSTOMER be related to at least one ORDER? This
is less clear. Maybe the organization allows customers to set up accounts
prior to placing their first order. In this case, the minimum cardinality from
CUSTOMER to ORDER is zero. We will make this assumption for the Order Entry
Form example. The situation is similar for the minimum cardinality from
PRODUCT to ORDER. It is reasonable to assume that we have products that have
not been ordered yet. If this is allowed, then the minimum cardinality from
PRODUCT to ORDER is zero.
Assumptions should only be temporary in actual practice. The analyst may have
to make assumptions to be able to proceed with the analysis, but it is
critical that the validity of the assumptions be checked with the users before
completing the analysis.
If you have any many-to-many relationships, you must convert them to
associative entities. In our example, the relationship between orders and
products is a manyto-many relationship. This relationship becomes an entity,
which we will call ORDERDETAILS. Recall that earlier we determined that PRICE
and QUANTITY were functions of this relationship. Because of this, we assign
these attributes to the ORDERDETAILS entity. Figure E.8 shows the Order Entry
E-R diagram with maximum and minimum cardinalities indicated. It is now time
for the last step in the E-R diagramming process, checking the model.
A64 Appendix E
Figure E.8 Order Entry Diagram
Step 5: Check the Model
The final step in creating an E-R diagram is often overlooked but is just as
important as any of the previous steps. Analysts who fail to carefully check
the ERD often produce diagrams of poor quality, which of course should be
avoided.
To check the ERD, you must return to your original information sources: the
forms, reports, and interviews with users. The basic idea is to go back to the
original documents and make sure that the structure represented in the ERD can
satisfy the requirements. For example, the representations in the ERD must be
able to reproduce any forms or reports required. We will use the Order Entry
Form, which for convenience is reproduced in Figure E.9, as an example.
ORDER ID: DATE: PRODUCT ID A123 C235 X002
44444444 Jan. 3, 2021 DESCRIPTION STEREO SYSTEM 8 SPEAKER SPEAKER WIRE
CUSTOMER ID: CUSTOMER NAME: PRICE 375.00 150.00
10.00 TOTAL
Figure E.9 Order Entry Form (Repeated)
1002 ABC Inc. QTY 2 8 5
EXTENDED 750.00
1,200.00 50.00
2,000.00
The first step in using the Order Entry Form to check the ERD is to make sure
that all the information contained in the form is also represented on the ERD.
The easiest way to do this is to take a copy of the form and check off each
item as you verify that the item is on the ERD.
Examination of the Order Entry Form shows that there are three items that are
not represented on the ERD: EXT, TOTAL, and QTY. Both EXT, which is short for
extended, and TOTAL can be computed, so it is not necessary to store these in
the database or represent them on the ERD. In most cases, it is not necessary
to store attributes that can be computed. There are times, however, when it
may make sense
Advanced Database Concepts A65
Figure E.10 Complete Order Entry Model
to store an item that can be computed. For example, TOTAL can be computed, but
doing so requires retrieving data from multiple records. It may be that
storing this value improves performance enough to justify storing TOTAL.
Unlike EXT and TOTAL, QTY (quantity) cannot be computed and therefore must be
stored in the database. Once the analyst decides that QTY should be
represented on the ERD, the question becomes how to represent it. Initial
ideas might include representing QTY as an attribute of ORDER or of PRODUCT.
However, QTY is not really an attribute of either of these but is properly
represented as an attribute of the relationship between ORDER and PRODUCT. It
is not unusual to have attributes attached to relationships with a many-to-
many cardinality. Adding QTY to the ERD results in the complete ERD, which is
shown in Figure E.10.
Review Questions 1. Define each of the following terms: entity, attribute,
relationship, composite primary key, and cardinality. 2. Explain the
difference between an entity type and an entity instance. 3. Compare and
contrast minimum and maximum cardinality. 4. Name the steps in building an E-R
diagram (as described in the text). 5. What is a “natural” primary key? 6.
Name the desirable primary key characteristics (as described in the text). 7.
Explain why a primary key should be “data-less.” 8. Why is it important to
check an ERD against the original information sources?
Additional Learning Activities Note: The activities below are purposely
simplified from what might be included in an actual database.This was done to
make the scope appropriate for an introductory class.
A66 Appendix E
1. Create an E-R diagram based on the purchase order form shown below. (Note: “Extended” is the extended cost, or quantity multiplied by cost.) State any assumptions that you made when creating the diagram.
PURCHASE ORDER FORM
PO No.: 12348
Vendor ID: 87459
Name:
Bithlo Books
Address: 27 E. SR 50
Bithlo, FL 32158
ISBN 1-55860-294-1 3-12368-998-2 2-78912-437-5
Title Database Modeling & Design Guide to the Outback Sports Heroes of the 1700’s TOTAL COST
Date: 1/8/2021
QTY 5 7 2
Cost 10.00 12.00 16.25
Extended 50.00 84.00 32.50
166.50
2. Your friend wants your help in creating a database that will help her keep
track of members of a student club. She wants to be able to track basic
contact information for members, including names, primary email addresses, and
on-campus addresses. In addition, she wants to track any leadership positions
members currently hold. (She does not care about past positions.) Finally, she
wants to store any other clubs to which members belong. Only the names of the
other clubs need to be stored in the database. Create an E-R diagram that
meets your friend’s requirements. State any assumptions you made.
3. Create an E-R diagram that could be used to create a database to track
your music collection. For each song, you need to know the title, artist(s),
date published, and album on which the song appeared. Keep in mind that an
artist can have multiple albums and that a song may involve more than one
artist. Be sure to state any assumptions you make.
4. You want to create a database that can help you keep track of your movie
collection. For each movie, you want to store the title of the movie; the year
it was released; the name and nationality of the director; and the full names,
nationalities, and birth dates of the female and male lead actors. You also
want to store the genre of each movie (such as comedy, sci-fi, etc.). Create
an E-R diagram that represents these requirements. State any assumptions you
make.
References
Chen, P. P. 1976. “The entityrelationship model–Toward a unified view of
data.” ACM Transactions on Database Systems 1(1): 936.
Advanced Database Concepts A67
Glossary
Binary relationship: A relationship involving two entities. Business rule: A
statement that defines or constrains an aspect of a business with the
intent of controlling behaviors within the business. Composite primary key: A
primary key made up of two or more attributes. Entity: A thing that can be
distinctly identified in a conceptual data model; the “thing”
about which one wants to store data in a database. Depending on the context,
“entity” may refer to an entity type or an entity instance. Entity instance: A
single occurrence of an entity type. Entity-relationship modeling: The process
of creating an entity-relationship diagram (ERD). Entity type: A category or
collection of entity instances. E-R diagram (ERD): A graphical representation
of the conceptual structure of a database. Many-to-many: A form of maximum
cardinality indicating that more than one instance of an entity can be related
to more than one instance of a related entity. Maximum cardinality: The
maximum number of instances of one entity that can be associated with a single
instance of a related entity. Minimum cardinality: The minimum number of
instances of one entity that must be associated with a single instance of a
related entity. One-to-one: A form of maximum cardinality indicating that a
single instance of an entity can be related to only one instance of a related
entity. One-to-many: A form of maximum cardinality indicating that a single
instance of an entity can be related to more than one instance of a related
entity. Primary key: An attribute or set of attributes that uniquely
identifies an instance of an entity. Relationship: An association among
entities or between instances of the same entity. Ternary relationship: A
relationship involving three entities. Unary relationship: A relationship that
includes only one entity; this represents a situation in which a record in a
table is related to another record in the same table. Also known as a
recursive relationship.
Appendix F
Excel Fundamentals
Learning Objectives
By reading and completing the activities in this appendix, you will be able
to:
· Navigate the Microsoft Excel interface · Work with cells and ranges of cells
in Excel · Enter data, formulas, and functions · Choose and implement
appropriate functions · Work with ranges of cells · Format a spreadsheet to
improve readability · Appropriately choose between absolute and relative cell
referencing · Create charts using Excel · Prepare a spreadsheet for printing
Appendix Outline
Introduction Excel Fundamentals Editing and Formatting Worksheets Functions
and Formulas Charts Printing
Introduction
The purpose of this appendix is to help you gain fundamental hands-on
spreadsheet skills. We discussed the purposes of electronic spreadsheets in
Chapter 6 of the main text, and here we delve into how to actually use them.
Although this appendix uses Microsoft Excel, many of the skills you will gain
will transfer to other spreadsheet software, such as OpenOffice Calc, Google
Sheets, and Numbers for Mac.
In the book, we explained that the power of a spreadsheet comes from the fact
that it can store information as relationships among cells rather than as
simple text or numbers. Of course, a spreadsheet can store numbers and text as
well. Because the spreadsheet uses relationships when you change a value in a
cell, all cells that depend on that value also change. So it is possible to
update very complex spreadsheets quickly and accurately. Spreadsheets can be
extremely complex, with tens of thousands of cells. In practice, the capacity
of an Excel spreadsheet seems limitless. Of course, there are limits to the
size and complexity that Excel can handle. Check out
A70 Appendix F
the following link to learn more about Excel’s capacity:
https://support.office.com/ en-za/article/Excel-specifications-and-limits-
1672b34d-7043-467e-8e27 -269d656771c3.
Note that we use Excel 2020 for Mac throughout this appendix. Some of you may
be using other versions of Excel, designed for Windows. You will find that
virtually all the material presented here applies to both versions.
This appendix is a little different because it relies on videos rather than
text and figures. For each section, you should first view the appropriate
videos and then try the activities that go along with that set of videos. The
relevant videos are listed at the beginning of each section.
Excel Fundamentals
This section is designed to help you gain a working knowledge of the
fundamentals of Excel. Please watch the videos in the Excel Fundamental
References
- ance.org - This website is for sale! - ance Resources and Information.
- coalition.org - This website is for sale! - Coalition Resources and Information.
- CCC | Copyright licensing, content & software solutions
- NIST Computer Security Resource Center | CSRC
- Computers, Monitors & Technology Solutions | Dell USA
- ipv6.com
- Prospect Press | Textbooks for Information Systems Curriculum
- rights.org
- Salesforce: The Customer Company - Salesforce.com US
- AMD ׀ together we advance_AI
- App Store - Apple
- AutoCAD Software | Get Prices & Buy Official AutoCAD 2023 | Autodesk
- App Download Data (2022) - Business of Apps
- EPIC - Online Guide to Privacy Resources
- ESPN: Serving sports fans. Anytime. Anywhere.
- Best Note Taking App - Organize Your Notes with Evernote
- Intel | Data Center Solutions, IoT, and PC Innovation
- Home - National Security Institute
- Cyber Security Training, Degrees & Resources | SANS Institute
- Test your IPv6.
- The Best Technical Questions And Answers
- Microsoft Support
- Microsoft 365 help & learning
- Microsoft Support
- Microsoft Support
- Report: Smartphone owners are using 9 apps per day, 30 per month • TechCrunch
- Home : Occupational Outlook Handbook: : U.S. Bureau of Labor Statistics
- carbonite.com/home3/
- Enterprise risk management: all systems go | CSO Online
- Dropbox.com
- CISA Certification | Certified Information Systems Auditor | ISACA
- CISM Certification | Certified Information Security Manager | ISACA
- CRISC Certification | Certified in Risk and Information Systems Control | ISACA
- Security Authorization Certification | CAP - Certified Authorization Professional | (ISC)²
- Cybersecurity Certification| CISSP - Certified Information Systems Security Professional | (ISC)²
Read User Manual Online (PDF format)
Read User Manual Online (PDF format) >>