Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Page 1 of 13
CHAPTER 4
RELATIONAL DATABASES
Instructor’s Manual
Learning Objectives:
1. Explain the importance and advantages of databases, as well as
the difference between database and file-based legacy systems.
3. Explain fundamental concepts of database systems such as DBMS,
schemas, the data dictionary, and DBMS languages.
5. Create a set of well-structured tables to properly store data in
a relational database.
6. Perform simple queries using the Microsoft Access database.
Questions to be addressed in this chapter:
1. How are databases different than file-based legacy systems?
2. Why are databases important and what is their advantage?
Introduction
The emphasis in this chapter will be on understanding the structure of
a relational database system.
Files versus Databases
A file, as described in the back of our book in the glossary, is
a set of logically related records, such as the payroll records
of all employees.
Page 2 of 13
Figure 4-1 on page 87 shows the basic elements of data hierarchy.
We use the term database to mean the collected data sets that are
organized and stored as an integral part of a firm’s computer-
based information system.
In turn, we define the term data sets as flexible data
structures. Data sets include groupings of data that are
logically related as well as the files with which we are
familiar
Data independence, a critical feature of the data-base approach,
is the separation of data from the various applications that
access and process the data.
Data independence is achieved by interposing the database
management system (DBMS) software between the database and the
users of the data(e.g., the application programs.
Multiple Choice 1
A customer’s name would be a:
a. database
b. file
c. field
d. record
Multiple Choice 2
Which of the below would is a file?
a. a customer’s name
The Importance and Advantages of Database Systems
Note to Students: Some of the job announcements for accounting
positions require the knowledge and skills from experience in
using MS Access.
Most accounting students will audit or work for a company that
uses database technology to store, process, and report accounting
transactions.
Database technology is widespread because it provides
organizations with the following benefits:
1. Data integration: Integration is achieved by combining
master files into larger “pools” of data that many
2. Data sharing: Integrating data makes it easier to share
data with all authorized users.
3. Reporting flexibility: Reports can be revised easily and
4. Minimal data redundancy and data inconsistencies: Because
data items are usually stored only once, data redundancy
and data inconsistencies are minimized.
5. Data independence: Because data and the programs that use
6. Central management of data: Data management is more
Page 4 of 13
7. Cross-functional analysis: In a database system,
relationships, such as the association between selling
costs and promotional campaigns, can be explicitly defined
and used in the preparation of management reports.
Multiple Choice 3
The database technology benefit that makes it easier for information to
be combined in unlimited ways is:
a. data sharing
b. data independence
Database Systems
Logical and Physical Views of Data
Figure 4-3 at the bottom of page 90 provides an example of a
record layout of an accounts receivable file.
Multiple Choice 4
Learning Objective Two
Explain the difference between logical and
physical views of a database.
Page 5 of 13
The data view that shows how the user or programmer conceptually
organizes and understands the data is the
a. record layout view
b. logical view
Schemas
A schema describes the logical structure of a database.
Three levels of schemas: the conceptual, the external, and
the internal.
Figure 4-5 on page 91 provides the three levels of schemas
The conceptual-level schema is the organization-wide view
of the entire database.
The Data Dictionary
The data dictionary contains information about the
structure of the database. The data elements composing the
database are fully described in a data dictionary, which
Database Management System (DBMS) Languages
The data definition language (DDL) is used to (1) build the
data dictionary, (2) initialize or create the database, (3)
Page 6 of 13
A report writer is a language that simplifies report
creation.
Note to the Instructor: The additional definition and
additional uses that were not included in the book could be
provided to the students:
A database control system (DBCS) that controls the various
components of the DBMS. For example, the DBCS creates and
manages the structure used for storing the data. It also
creates the necessary support components that allow
simultaneous multiple-user access to the data.
In addition to the functions provided by these components,
many DBMS packages allow users to
2. Create and display graphs
3. Create customized applications via host programming
languages
4. Import and export data and images from other
packages
Multiple Choice 5
Which of the following statements are FALSE?
a. The data dictionary contains information about the structure of
the database.
b. The internal-level schema provides a high-level view of the
database.
Multiple Choice 6
There should be limited access to employees with administrative and
programming responsibilities to
Page 7 of 13
a. DML
b. DQL
c. DDL
d. A and C
e. All of the above
Relational Databases
A data model is an abstract representation of the contents
of a database.
The relational data model represents everything in the
database as being stored in the form of tables like the one
shown in Table 4-2 on page 94.
Types of Attributes
A primary key is the attribute, or combination of
attributes, that uniquely identifies a specific row in a
Multiple Choice 7
In a Sales Table, the most likely primary key would be
a. sales invoice number
Multiple Choice 8
In an inventory table, the most likely nonkey would be
a. item number
b. color
Learning Objective Four
Describe what a relational database is and
how it organizes data.
Page 8 of 13
Designing a Relational Data Base for S&S, Inc.
Option 1: Store All Data in One Uniform Table.
This data is provided in Table 4-3 on page 95
This approach has two disadvantages:
1. It creates a great deal of redundancy in terms of
stored data. For example, because there are three
Option 2: Vary the Number of Columns
This data is provided in Table 4-4 on page 94.
This required additional items which required five additional
columns:
1. Item
2. Quantity
3. Description
Page 9 of 13
This table is set up for the sale of five items. What
happens if eight items are sold? This would require 40
columns! Now we are going to have even more columns to add
three more items.
Basic Requirements of a Relational Database
1. Every column in a row must be single valued.
3. Foreign keys, if not null, must have values that correspond to
the value of a primary key in another table.
A set of relational databases are provide in Table 4-5 on page
98.
As shown in Table 4-5 we now have more than one table that have
relationships:
2. Sales-Inventory Table
4. Customer Table
Primary key is the attribute, or combination of attributes, that
uniquely identifies a specific row in a table. As a result, the
value for a primary key cannot be blank (null). If it was blank,
then there would be no way to identify a specific row and
retrieve any data.
The entity integrity rule ensures that every row in every
relation must represent data bout some specific object in the
real world.
Foreign keys are used to link rows in one table to rows in
another table. Refer to the lines and arrows in Table 4-5.
Page 10 of 13
For example, the customer number is a foreign key in the
sales table that relates to the customer number that is a
primary key in the customer table.
Nonkey attributes are items in a table that are neither a primary
key nor a foreign key. As previously described as the fourth
basic requirement for a relational database; all nonkey
attributes in a table should describe a characteristic about the
object identified by the primary key.
Two Approaches to Database Design
One approach is called normalization, which starts with the
assumption that everything is initially stored in one large
table.
Another alternative way to design well-structured relational
databases involves semantic data modeling. Under this approach,
the database designer uses knowledge about how business processes
typically work and about the information needs associated with
transaction processing to draw a graphical picture of what should
be included in the database. The resulting figure can then be
directly used to create a set of relational tables that are in
third normal form (3NF). Refer to the Website for the third
normal form.
Multiple Choice 9
Which of the following is not a basic requirement of a relational
database?
a. Every column in a row must be single valued.
b. Primary keys cannot be null.
Learning Objective Six
Perform simple queries using the Microsoft
Access database.
Page 11 of 13
Creating Relational Database Queries
Note to Instructor: If students want to follow along with the five
Next, the students need to follow the following five steps:
1. Left click on “Companion Website” under “Accounting Information
Systems 11/e.”
2. Click on “Chapter 4” from the “Chapter List.”
3. In the “Learning Objectives” window, left click on “Student
The MS Access menu is shown in Table 4-6 on page 99
Query 1. We are using the Sales and Customer Tables for this
Query.
Next we select the files from each Table to be included in the
Query. Note the Sales Table Primary Key is Sales Invoice # and
Note to Instructor: Attachment 1 to this Instructor Manual
provides a step-by-step picture screen explanation of how to
complete and run query 1.
Query 2. This query involves the Sales, Sales-Inventory, and
Inventory tables. Students should be able now to determine the
Query 3. Now we are using four tables; Customer, Sales, Sales-
Inventory, and Inventory tables.
Page 12 of 13
The Completed Inquiry 4 and Query 4 Answer are provided in Table
4-11 on page 103.
Multiple Choice 10
What tables were needed in the query that answered the question: “How
many televisions were sold in October?”
a. Sales table
b. Inventory table
Database Systems and the Future of Accounting
Database systems may profoundly affect the fundamental nature of
accounting. For instance, database systems may lead to the
abandonment of the double-entry accounting model.
the nature of external reporting.
Why not simply make a copy of the company’s financial database
and make it available to external users in lieu of the
traditional financial statements?
Focus 4-1 on page 125 discusses this possibility in more detail.
Page 13 of 13
Answer to Multiple Choice Questions:
Multiple Choice Question Answers
Number
Answer
Number
Answer
1
C
6
D
2
C
7
A
3
D
8
D
4
B
9
C
5
B
10
D
Note to Instructor: Attachment 2 to this instructor’s manual
provides a picture screen description of the steps used in the MS
