Transcript Accounting Information Systems 9th Edition

Relational Database and
Data Modeling
Lecture 10
(Chapter 4)
Introduction


This lecture explains what a database is and
how it differs from a file-oriented system.
It describes the structure of a relational
database system & the basic steps involved
in designing a database.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-2
Databases


For many years, companies created new
files and programs each time an information
need arose.
This proliferation of master files created
problems:
1
2
Often the same data was stored in two or
more separate files.
The specific data values stored in the
different files were not always consistent.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-3
Databases



The database approach views data as an
organizational resource that should be used
by, and managed for, the entire
organization, not just the originating
department or function.
Its focus is data integration and data
sharing.
Integration is achieved by combining master
files into larger pools of data that can be
accessed by many application programs.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-4
Types of Files
Two basic types of files are used to store
data:
1 The master file, which is conceptually
similar to a ledger in a manual system.
2 The transaction file, which is
conceptually similar to a journal in a
manual system.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-5
File-Oriented Approach
File # 1
Item A
Item B
Item C
Application
program #1
File # 2
Item B
Item D
Item E
Application
program #2
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-6
Database Approach
Application
program #1
Database
Item A
Item B
Item C
Item D
Item E
Database
management
system
Application
program #2
Application
program #3
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-7
Database Approach versus
File-Oriented Approach
Minimum data redundancy
Fewer data inconsistencies
Standardized data format
No duplicated processing or storage
Allows cross-functional data analyses
Central data management / data security
Lower cost
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-8
Databases
Database management system
(DBMS) is the program that manages
and controls access to the database.
 Database system is the combination
of the database, the DBMS, and the
application program that uses the
database.
 Database administrator (DBA) is the
person responsible for the database.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-9
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.
Technically, these tables are called relations.
Each row in a relation, called a tuple,
contains data about a specific occurrence of
the type of entity represented by that table.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-10
Logical & Physical Views of Data
A major advantage of database systems over fileoriented systems is that the database systems
separate the logical and physical view of data:


Logical view: It is how the user or programmer conceptually
organizes and understands the data.
Physical view: It refers to how and where the data are
physically arranged and stored on disk, tape, CD-ROM, or
other media.
The DBMS controls the database so that users can
access, query, or update it without reference to how
or where the data are physically stored.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-11
Schemas
What are schemas?
 A schema describes the logical
structure of a database.
 There are three levels of schemas:

1
2
3
Conceptual-level schema
External-level schema
Internal-level schema
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-12
Schemas
Schema Levels:
1.
Conceptual
2.
External
3.
Internal
The conceptual-level schema is an
organization-wide view of the entire
database.
 The external-level schema consists of
a set of individual user views of
portions of the database, also referred
to as a subschema.
 The internal-level schema provides a
low-level view of the database.

©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-13
The Data Dictionary

What is a data dictionary?


It contains information about the
structure of the database.
For each data element stored in the
database, such as the customer
number, there is a corresponding
record in the data dictionary
describing it.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-14
The Data Dictionary

The data dictionary is often one of the first
applications of a newly implemented database
system.

Inputs to the data dictionary:
–
–

records of any new or deleted data elements
changes in names, descriptions, or uses of existing
data elements
Outputs of the data dictionary:
–
reports useful to programmers, database designers,
and users of the information system
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-15
DBMS Languages

Every DBMS must provide a means of
performing the three basic functions:
1
2
3

Creating the database
Changing the database
Querying the database
The sets of commands used to perform
these functions are referred to as the data
definition, data manipulation, and data
query languages.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-16
DBMS Languages

Data definition language (DDL):
–
–
–
–

Data manipulation language (DML):
–

build the data dictionary.
initialize or create the database.
describe the logical views for each individual user or
programmer.
specify any limitations or constraints on security
imposed on database record or fields.
Data maintenance (updating / inserting / deleting)
Data query language (DQL):
–
Interrogate the database (retrieves / sorts / orders and
and presents subsets of the database in response to
user queries)
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-17
Basic Requirements of the
Relational Data Model
1
2
3
4
5
6
Primary keys must be unique.
Every foreign key must either be null or have a
value corresponding to the value of a primary key
in another relation.
Each column in a table must describe a
characteristic of the object identified by the
primary key.
Each column in a row must be single-valued.
The value in every row of a specific column must
be of the same data type.
Neither column order nor row order is significant.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-18
Database Design Objectives








Completeness
Relevance
Accessibility
Up-to-dateness flexibility
Efficiency
Cost-effectiveness
Integrity
Security
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-19
Approaches to Database
Design

Normalization



Starts with the assumption that all data is
initially stored in a large non-normalized
table.
This table is then decomposed using a set of
normalization rules to create a set of tables
in the Third Normal Form.
Semantic Data Modeling

The database designer uses his/her
knowledge about the business structure to
create a set of relational tables.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-20
Designing and Implementing
a Database System
Six basic steps involved in designing and
implementing a database system:
1.
2.
3.
4.
5.
6.
Initial planning to determine the need for and feasibility
of developing a new system.
Identifying user needs.
Developing the contextual-, external-and internal-level
schemas.
Translating the internal-level schema into the actual
database structures that will be implemented in the new
system.
Transferring all data from the existing system to the new
database.
Using and maintaining the new system.
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-21
The REA Data Model

Data modeling is the process of defining a
database so that it represents all aspects of the
organization, including its interactions with the
external environment.

The REA data model is a conceptual modeling tool
specifically designed to provide structure for
designing AIS databases.
REA data model provides structure in two ways:

1
2
By identifying what entities should be included in the AIS
database
By prescribing how to structure relationships among the
entities in the AIS database
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-22
End of Lecture 10
©2003 Prentice Hall Business Publishing,
Accounting Information Systems, 9/e, Romney/Steinbart
Lecture 10-23