A20 – Accounting Information System - Marshall B. Romney ft. Paul John Steinhart & James A.

Hall
Chapter 4 – Relational Database

Files versus Databases

File - A set of related records
Database - A set of interrelated, centrally coordinated data files that are stored with as little data redundancy as
possible
Database Management System (DBMS) - The program that manages and controls the data and the interfaces
between the data and the application programs that use the data stored in the database.
Database System - The database, the DBMS, and the application programs that access the database through the
DBMS.
Database Administrator - The person responsible for coordinating, controlling, and managing the database
data attribute (field), a record, or an entire file

FLAT-FILE PROBLEMS SOLVED

Data sharing (the absence of ownership) is the central concept of the database approach. Let’s see how
this resolves the problems identified.
• No data redundancy. Each data element is stored only once, thereby eliminating data redundancy and
reducing storage costs.
• Single update. Because each data element exists in only one place, it requires only a single update
procedure. This reduces the time and cost of keeping the database current.
• Current values. A change any user makes to the database yields current data values for all other user
• Task-data independence. Users have access to the full domain of data available to the firm.
•
Using Data Warehouses for Business Intelligence
Data Warehouse - Very large databases containing detailed and summarized data for a number of years that are
used for analysis rather than transaction processing.
Business Intelligence - Analyzing large amounts of data for strategic decision making.
• Online Analytical Processing (OLAP) - Using queries to investigate hypothesized relationships among
data.
• Data Mining - Using sophisticated statistical analysis to “discover” hypothesized relationships in the data
The Advantages of Database Systems
• Data integration. Master files are combined into large “pools” of data that many applications programs
access. An example is an employee database that consolidates payroll, personnel, and job skills master files.
• Data sharing. Integrated data are more easily shared with authorized users.
• Minimal data redundancy and data inconsistencies. Because data items are usually stored only once, data
redundancy and data inconsistencies are minimized.
• Data independence. Because data and the programs that use them are independent of each other, each can
be changed without changing the other. This facilitates programming and simplifies data management.
• 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

DATABASE SYSTEMS

Logical and Physical Views of Data

Record Layout - Document that shows the items stored in a file, including the order and length of the data
fields and the type of data stored.
Logical View - How people conceptually organize, view, and understand the relationships among data items.
Physical View - The way data are physically arranged and stored in the computer system.

Schema
A20 – Accounting Information System - Marshall B. Romney ft. Paul John Steinhart & James A. Hall
Schema - A description of the data elements in a database, the relationships among them, and the logical model
used to organize and describe the data.
• Conceptual-Level Schema - The organization-wide view of the entire database that lists all data elements
and the relationships between them.
• External-Level Schema - An individual user’s view of portions of a database; also called a subschema.
• Subschema - A subset of the schema; the way the user defines the data and the data relationships.
internal-level schema - A low-level view of the entire database describing how the data are actually stored and
accessed
Data Dictionary - Information about the structure of the database, including a description of each data
element.

DBMS Languages
Data Definition Language (DDL) - DBMS language that builds the data dictionary, creates the database,
describes logical views, and specifies record or field security constraints.
Data Manipulation Language (DML) - DBMS language that changes database content, including data
element creations, updates, insertions, and deletions.
Data Query Language (DQL) - High-level, English-like, DBMS language that contains powerful, easy-to-use
commands that enable users to retrieve, sort, order, and display data.
Report Writer - DBMS language that simplifies report creation.

PHYSICAL DATABASE
- is the lowest level of the database.
- The physical database consists of magnetic spots on magnetic disks
- the database is a collection of records and files
- are based on the indexed sequential file structure
Indexed Sequential File - uses an index in conjunction with a sequential file organization
Inverted List - Multiple indexes can be used to create a cross-reference which allows even more flexible access
to data

RELATIONAL DATABASES
Data Model - An abstract representation of database contents.
Relational Data Model - A two-dimensional table representation of data; each row represents a unique entity
(record) and each column is a field where record attributes are stored.
Tuple - A row in a table that contains data about a specific item in a database table.
Entity - is anything about which the organization wishes to capture data
Data Model - is the blueprint for ultimately creating the physical database
Entity Relationship (ER) Diagram - graphical representation used to depict the model
Occurrence - is used to describe the number of instances or records that pertain to a specific entity.
Attributes - are the data elements that define an entity.

Types of Attributes
Primary Key - Database attribute, or combination of attributes, that uniquely identifies each row in a table.
Foreign Key - An attribute in a table that is also a primary key in another table; used to link the two tables

Database Anomalies
Update Anomaly - Improper database organization where a non-primary key item is stored multiple times;
updating the item in one location and not the others cause data inconsistencies.
- results from data redundancy in an unnormalized table.
Insert Anomaly - Improper database organization that results in the inability to add records to a database.
Delete Anomaly - Improper organization of a database that results in the loss of all information about an entity
when a row is deleted
A20 – Accounting Information System - Marshall B. Romney ft. Paul John Steinhart & James A. Hall
- involves the unintentional deletion of data from a table
Relational Database - A database built using the relational data model.

Basic Requirements of a Relational Database

1. Every column in a row must be single valued
2. . Primary keys cannot be null.
i. Entity Integrity Rule - A nonnull primary key ensures that every row in a table represents
something and that it can be identified.
3. Foreign keys, if not null, must have values that correspond to the value of a primary key in another table
ii. Referential Integrity Rule - Foreign keys which link rows in one table to rows in another table
must have values that correspond to the value of a primary key in another table.
4. All monkey attributes in a table must describe a characteristic of the object identified by the primary key.

Two Approaches to Database Design

Normalization - Following relational database creation rules to design a relational database that is free from
delete, insert, and update anomalies.
Semantic Data Modeling - Using knowledge of business processes and information needs to create a diagram
that shows what to include in a fully normalized database (in 3NF)

HALL
View Integration - Combining the data needs of all users into a single schema or enterprise-wide.
Distributed Data Processing (DDP) - as an alternative to the centralized approach.
Centralized Database Approach - remote users send requests via terminals for data to the central site, which
processes the requests and transmits the data back to the user
Database Lockout - which is a software control (usually a function of the DBMS) that prevents multiple
simultaneous accesses to data.
Distributed Databases - can be distributed using either the partitioned or replicated technique.
• Partitioned Database Approach - Splits the central database into segments or partitions that are distributed
to their primary users.
Deadlock - occurs here because there is mutual exclusion to data, and the transactions are in a wait state until
the locks are removed
Replicated Databases - are effective in companies in which there exists a high degree of data sharing but no
primary user
Concurrency Control - is to serialize transactions.
- This involves labeling each transaction by two criteria.
• First, special software groups transactions into classes to identify potential conflicts.
• second part of the control process is to time stamp each transaction