23-Dec-20

Chapter Outline
Chapter Three
• File Based and Database data storage
approaches
• Relational databases and their
Relational Databases and application in modeling transaction
cycles
Database Design • Normalization and semantic data
modeling database design approaches
• Coding and querying databases using
DBMS such as Access
• Database design process

Data Hierarchy
 Field
 Attributes
about an
entity

 Record
 Related group
of fields Flat-File Based and Database
 File Data Storage Approaches
 Related group
of records

 Database
 Related group
of files

1
 23-Dec-20

Flat-File Based Vs. Database Data Storage Flat-File Based Vs. Database Data Storage

There are a number of alternative ways of Flat-File Based Approach

organizing the relationships between data A file-orientated approach is based on a simple
elements stored in a database. ‘flat structure’
Such alternative ways are termed database Data files are ‘owned’ by particular
models (or data models), the most common ‘application specific’ groups within a company,
being: usually with such groups able to dictate
the flat data model,  the nature & structure of data capture procedures,
 the content and structure of the data records/data
the hierarchical data model,
files,
the network data model,  the timing of data maintenance issues, and
the relational data model, and  the nature and structure of data retrieval
the object-oriented data model. operations.

Flat-File Based Vs. Database Data Storage Flat-File Based Vs. Database Data Storage

Flat-File Based Approach Database Data Storage

No relationships are defined between records. A database is a tool that stores data, & lets you
Users own their data files create, read, update, and delete the data in
some manner
Two problems
Pooling of data into a common database that
Inhibit entity-wide integration of data
is shared by all the users.
Require data files to be structured to the No data redundancy - Each data element is
unique needs of the primary user stored only once
Redundant data and programs Single update
Varying formats for storing the redundant Current values
data Task-data independence - Users have access
to the full domain of data available to the firm

2
 23-Dec-20

Flat-File Based Vs. Database Data Storage FILE VS. DATABASES

Database management system Standing (DBMS)  The proliferation of master files

created problems:
DBMS is between the users’ programs & the Master File 1
 Often the same information
Enrollment
physical database Fact A
Program
was stored in multiple master
Fact B files.
Provide controlled access to the database. Fact C
 Made it more difficult to
Data in databases are manipulated by this effectively integrate data and
obtain an organization-wide
specialized software packages Master File 2 view of the data.
Fact A
Financial Aid
Fact D Program  Also, the same information
Fact F may not have been consistent
between files.
 If a student changed his
phone number, it may have
Master File 1 Grades
Fact A been updated in one
Fact B
Program master file but not another.
Fact F

FILE VS. DATABASES FILE VS. DATABASES

A database is a set of  The database

Database Database approach treats data
Fact A Fact B inter-related, centrally Fact A Fact B as an organizational
Fact C Fact D Fact C Fact D
Fact E Fact F coordinated files. Fact E Fact F resource that should be
used by and managed
for the entire
organization, not just a
Database Database particular department.
Management Management
System System
 A database
management system
(DBMS) serves as the
Enrollment Financial Aid Grades Enrollment Financial Aid Grades interface between the
Program Program Program Program Program Program
database and the
various application
programs.

3
 23-Dec-20

FILE VS. DATABASES FILE VS. DATABASES

 The combination of the  The person responsible for the
database, the DBMS, database is the database
Database and the application Database administrator.
Fact A Fact B Fact A Fact B
Fact C Fact D programs that access Fact C Fact D
Fact E Fact F
the database is referred Fact E Fact F  Data Dictionary
to as the database  Information about the
system. structure of the database
Database Database
Management Management  Field names,
System System
descriptions, uses
 As technology improves, many
Enrollment Financial Aid Grades Enrollment Financial Aid Grades
Program Program Program Program Program Program large companies are
developing very large
databases called data
warehouses.

Elements of the Database Environment

Advantages of Database Systems
 Data Integration
 Files are logically combined and made accessible to various
systems.

 Data Sharing
 With data in one place it is more easily accessed by authorized
users.

 Minimizing Data Redundancy and Data Inconsistency

 Eliminates the same data being stored in multiple files of the same
data.

 Data Independence
 Data is separate from the programs that access it. Changes can
be made to the data without necessitating a change in the
programs and vice versa.

 Cross-Functional Analysis
 Relationships between data from various organizational
departments can be more easily combined.

4
 23-Dec-20

Schemas TRANSACTION PROCESSING

 Describe the logical structure  An important function of the AIS is to efficiently and
of a database
effectively process the data about a company’s
 Conceptual Level
transactions.
 Organization wide view of
the data  In manual systems, data is entered into paper journals
 External Level and ledgers.
 Individual users view of  In computer-based systems, the series of operations
the data performed on data is referred to as the data processing
 Each view is a cycle.
subschema
 Internal Level  The data processing cycle consists of four steps:
 Describes how data are  Data input
stored and accessed  Data storage
 Description of: records,
 Data processing
definitions, addresses,
and indexes  Information output

Data Input—Accuracy and Control Data Storage

Well-designed source documents can Types of AIS storage:
ensure that data captured is Paper-based
Accurate Ledgers
Provide instructions and prompts Journals
Check boxes Computer-based
Drop-down boxes
Complete
Internal control support
Pre-numbered documents

5
 23-Dec-20

Computer Based Storage File Types

 Entity  Transaction
 Person, place, or thing  Contains records of a business
(Noun) from a specific period of time
 Something an
organization wishes to  Master file
store data about  A file that stores cumulative
 Attributes information about an
organization’s entities: Customer,
 Facts about the entity employee, and inventory master
 Fields files
 Where attributes are  Permanent records
stored  Updated by transaction with the
 Records transaction file
 Group of related  Database
attributes about an
entity  Set of interrelated, centrally-
coordinated files.
 File
 When files about students are
 Group of related integrated with files about classes
Records and files about instructors, we
have a database.

Data Processing Data Output Types

Four Main Activities This output can be in the form of:
1. Create new records Documents
2. Read existing records Reports
3. Update existing records Queries
4. Delete records or data from records
Soft copy
Displayed on a screen

Hard copy
Printed on paper

6
 23-Dec-20

COMPUTER-BASED STORAGE CONCEPTS ERP Systems

The traditional AIS captured financial data.
 Non-financial data was captured in other, sometimes-
redundant systems

Enterprise resource planning (ERP) systems are

designed to integrate all aspects of a company’s
operations (including both financial and non-
financial information) with the traditional functions
of an AIS.

Romney & Steinbart, 2015

Enterprise Resource Planning (ERP) Leading ERP Products

Integrate an organization’s information into one  SAP(www.sap.com)
 mySAP ERP Financials; mySAP ERP Operations; mySAP ERP Human Capital
overall AIS Management; mySAP ERP Corporate Services

ERP modules:  Oracle | PeopleSoft (www.oracle.com)

 ORACLE E-BUSINESS SUITE , PEOPLESOFT ENTERPRISE SOLUTIONS; J.D.
 Financial
EDWARDS ENTERPRISEONE
 Human resources and payroll
 Microsoft (www.microsoft.com)
 Order to cash
 Dynamics GP; Dynamics AX; Dynamics SL; and others
 Purchase to pay
 Sage Software (www.sagenorthamerica.com)
 Manufacturing
 Project management  MAS 500

 Customer relationship management  SoftBrands (www.softbrands.com)

 System tools  Fourth Shift Edition

7
 23-Dec-20

Relational Database
Recall that a database is a collection of data
stored on the computer in a form that allows the
data to be easily accessed, retrieved,
manipulated, and stored.
A collection of related tables is called a
database, or a relational database
Relational Databases and Their The relational data model represents everything in
Application in Modeling the database as being stored in the forms of
Transaction Cycles tables (relations).

Relational Database
Relational data model represents the conceptual
and external level schemas as if data are stored
in tables.
Table
Each row contains data about one instance of
an entity.
This is equivalent to a record
Each column contains data about one
attribute of an entity.
This is equivalent to a field

8
 23-Dec-20

Attributes Database Design Errors

Primary Key If database is not designed properly data errors
 An attribute or combination of attributes that can be can occur.
used to uniquely identify a specific row (record) in a  Update Anomaly
table.  Changes to existing data are not correctly recorded.
Foreign Key  Due to multiple records with the same data
attributes
 An attribute in one table that is a primary key in another
table.  Insert Anomaly
 Used to link the two tables  Unable to add a record to the database.
 Delete Anomaly
 Removing a record also removes unintended data
from the database.

Design Requirements for Relational Relational database for a transaction cycle

Database consists of the following steps:
1. Every column must be single valued. 1. Identify entities
• identify the primary entities of the organization
2. Primary keys must contain data (not null). • construct a data model of their relationships
3. Foreign keys must contain the same data as the 2. Construct a data model showing entity
primary key in another table. associations
• determine the associations between entities
4. All non-key attributes must identify a
• model associations into an ER diagram
characteristic of the table identified by the
primary key. 3. Add primary keys and attributes
• assign primary keys to all entities in the model to
uniquely identify records
• every attribute should appear in one or more user
views

9
 23-Dec-20

Relational database for a transaction cycle

3. c
consists of the following steps:
4. Normalize and add foreign keys
• remove repeating groups, partial and transitive
dependencies
• assign foreign keys to be able to link tables
5. Construct the physical database Normalization and Semantic
• create physical tables
• populate tables with data
Data Modeling Database Design
6. Prepare the user views Approaches
• normalized tables should support all required views of
system users
• user views restrict users from having access to
unauthorized data

Data Normalization Data Normalization

 To normalize
Two approaches to Database Design: unorganized
Normalization data, data is
taken
Semantic data modeling through
three
Normalization is the process of converting data different
into tables that meet the definition of a relational forms
database
Initially, one table is used for all the data in a
database.
Following rules, the table is decomposed into
multiple tables related by:
 Primary key–foreign key integration

10
 23-Dec-20

Data Normalization Data Normalization

Example Example

 Step 1: Remove Repeating Groups  A primary key is data field or a combination of data fields
 select an attribute or a combination of attributes that that makes each record in the table unique.
make each row or record unique. Student# ? Course?  If a record cannot be uniquely identified using a single
Combination of both data field, a concatenated key (two or more data fields
 A table is in 1NF when the following is done: which, when combined, uniquely identify each record) is
● Repeating Groups have been removed. used.
● A unique primary key exists for each record.

Data Normalization Data Normalization

Example Example
 Step 2: Remove the partial dependencies
 Already in 1NF form (each record is uniquely identified
and therefore unique)
 No non-primary key attribute depends on only a portion
of the primary key
 Second normal form only occurs when the primary key
is concatenated (made up of more than one data
field).
 If there is no concatenated key, you can skip 2NF and
go directly to 3NF.
 Student Name and Major only describe and are
dependent on the Student, and not the Course, we
have a “partial dependency.”

11
 23-Dec-20

Data Normalization Data Normalization

Example Example
 Step 3:Remove Transitive Dependencies
 Already in 1NF or 2NF, and
 All data fields functionally depend on the primary key &
only the primary key - no transitive dependencies exist.
 Instructor Phone depends on the Instructor attribute

 The FK in the table stands for foreign key.

 Since we may need to reconnect these tables sometime
in the future, we always leave a field “in common”
between the two tables we have created.
 That field is usually the primary key of one of the newly
created tables.

Semantic Data Modeling Database

Design Entity-Relationship Diagrams
Using knowledge of business processes and Used to graphically represent a database
information needs to create a diagram that
shows what to include in a fully normalized
schema
database (in 3NF) Depicts entities
Facilitates the efficient design of transaction Anything an organization wants to collect
processing databases information about

Graphical model explicitly represents the Relationships between entities

organization’s business processes and policies
Tools:
 Entity-Relationship diagramming and
 REA modeling

12
 23-Dec-20

E-R Diagram Variations Resources-Events-Agents Diagram

Developed for designing AIS
Categorizing entities into:
Resources
Things that have economic value
Events
Business activities
Management wants to manage and
control
Agents
People and organizations that participate
in events

REA Diagram Rules Business Cycle Give–Get Relationships

1. Each event is linked to at least one
resource that it affects.
2. Each event is linked to at least one other
event.
 Types of links (relationships):
 Get events
 Give events
 Participation events

3. Each event is linked to at least two

participating agents.

13
 23-Dec-20

Revenue Cycle REA Diagram Developing REA Diagram

1. Identify the events about which management
wants to collect information.
2. Identify the resources affected by each event
and the agents who participate in those events.
 What economic resource is reduced by the “Give”
event?
 What economic resource is acquired by the “Get”
event?
 What economic resource is affected by a commitment
event?

3. Determine the cardinalities of each relationship.

Cardinalities Three Types of Relationships

 Describe the nature of relationships between entities Relationship type is based on maximum
 How many instances of one entity can be linked to each specific cardinality:
instance of another entity
 Minimum can be: 0 or 1
 One-to-One:
 Maximum can be: 1 or Many  an entity in A is associated with at most one entity in
B, and an entity in B is associated with at most one
entity in A.
 an employee has only one spouse in a married-to
relationship.

14
 23-Dec-20

Three Types of Relationships REA Diagram—Revenue Cycle

 One-to-Many: An entity in A is associated with at most
one entity in B, an entity in B is associated with many
entities in A.
 an employee works in a single department but a
department consists of many employees.

 Many-to-Many: An entity in A is associated with many

entities in B, and an entity in B is associated with many
entities in A.
 A customer may have many bank accounts. Accounts may be
joint between multiple customers.

REA Diagram—Expenditure Cycle REA Diagram—Payroll Cycle

15
 23-Dec-20

REA Redundancies Integrated REA Diagram

 Separate REA for an organization will have redundant
entities

Expenditure Cycle
 Resource

Revenue Cycle
 Each resource entity must be connected to:
 The resource entity is linked to event entities in one business
cycle and to event entities in the other cycle
 One event that increases the resource and,
 One event that decreases the resource
 No effect on cardinality
 Events

Payroll Cycle
 Alters the minimum cardinalities associated with the other
events that are related to the merged event
 May be linked to either an event that is part of one business
cycle or to an event that is part of another cycle but cannot
be linked to both events
 The minimum cardinality associated with the other events
must be 0 in the integrated REA diagram

Integrated, Enterprise-wide REA Diagram Cardinality Effect of Merging

Resources

Cardinalities between resource and entities remain the same.

16
 23-Dec-20

Rules for Creating Integrated REA

Cardinality Effect of Merging Events
Diagram
1. Every event must be linked to at least one resource.

2. Every event must be linked to two agents who

participate in that event.

3. Every event that involves the disposition of a resource

must be linked to an event that involves the acquisition of
a resource.

4. Every resource must be linked to at least one event that

increments that resource and to at least one event that
decrements that resource.
The cardinality between Disburse Cash and Supplier and
Employee (as payee) is now 0 to 1, that is, a disbursement 5. If event A can be linked to more than one other event,
can be made to the supplier or the employee but not but cannot be linked simultaneously to all of those other
both! events, then the REA diagram should show that event A is
linked to a minimum of 0 of each of those other events.

Using REA Diagram to Create

Relational Database
REA to Database Steps
Advantage: 1. Create a table for each distinct entity in the diagram
and for each many-to-many relationship.
Ensures the elimination of anomalies:
2. Assign attributes to appropriate tables.
Update  Identify primary keys:
Insert  Attributes that uniquely identifies each record.
Delete  For M:N relationships the primary key consists of two
attributes that represent the primary keys of each
entity linked in that relationship.
 Identify remaining attributes for table.
3. Use foreign keys to implement one-to-one and one-to-
many relationships.
 An attribute of one entity that is itself the primary key of
another entity.

17
 23-Dec-20

Retrieving Information from REA

Database
Journals
 Information contained in event tables

Ledgers
 Information contained in resource tables

Financial statements Coding and Querying

 Information contained in resources and
 Information on imbalances Databases Using DBMS Such as
 Accounts receivable Access
 Sales transactions for which customer payments
have not yet been received
 Accounts payable
 Purchases from suppliers that have not yet been
paid for

Coding and Querying Databases Using DBMS

Coding Techniques Such as Access: Process
 Sequence
 Determine the purpose of your database - This helps
 Items numbered consecutively
prepare you for the remaining steps.
 Block Digit Position Meaning
 Specific range of numbers are 1–2 Product Line, size,  Find and organize the information required - Gather all of
associated with a category and so on
the types of information you might want to record in the
 10000–199999 = Electric 3 Color
Range database, such as product name and order number.
4–5 Year of
 Group Manufacture
 Divide the information into tables- Divide your information
 Positioning of digits in code 6–7 Optional Features
provide meaning
items into major entities or subjects, such as Products or
1241000 12 = Dishwasher
Orders. Each subject then becomes a table.
4 = White
 Mnemonic
10 = 2010
 Letters and numbers 00 = No Options  Turn information items into columns- Decide what
 Easy to memorize information you want to store in each table. Each item
 Code derived from description becomes a field, and is displayed as a column in the
of item
table. For example, an Employees table might include
 Chart of accounts fields such as Last Name and Hire Date.
 Type of block coding

18
 23-Dec-20

Coding and Querying Databases Using DBMS

Such as Access: Process Queries
 Specify primary keys- Choose each table’s primary key. Users may want specific information found in a
The primary key is a column that is used to uniquely
identify each row. An example might be Product ID or
relational database and not have to sort through
Order ID. all the files to get that information. So they query
 Set up the table relationships- Look at each table and (ask a question) the data.
decide how the data in one table is related to the data in
other tables. Add fields to tables or create new tables to
An example of a query might be: What are the
clarify the relationships, as necessary. invoices of customer D. Ainge and who was the
 Refine your design- Analyze your design for errors. Create salesperson for those invoices?
the tables and add a few records of sample data. See if
you can get the results you want from your tables. Make
adjustments to the design, as needed.
 Apply the normalization rules- Apply the data
normalization rules to see if your tables are structured
correctly. Make adjustments to the tables, as needed.

Creating the Query Query Answer

4-15
4-16 4-16

19
 23-Dec-20

Create a Database
Overview of Access

Access Objects
 Tables: - a collection of data about a topic
arranged in rows and columns.
 Forms: - a place to enter data easily
 Queries: - a tool that lets you view, change, and
analyze data in different ways
 Reports: - a method to present your data in a  click the Start button, and then click All Programs
printed format, such as charts & invoices  click Microsoft Office, and then click Microsoft Office Access 2007
 Click Blank Database. Type a name for your database in the File
Name Box. Create the database browsing the appropriate location

Database Tables and Forms Database Queries

• When database is created Table 1 is also • You use a query to retrieve specific information from a
displayed database.
• To create fields for the table, double click the Add • A query is a question that you enter in a database
program.
New Field cell, type First Name and press the
• The database program then performs the required
ENTER key.
operations to present the answer in the form of a report.
• To add data to the table, type in the data in each • A query helps you view specific data to modify or analyze
field cell. it.
• Click in the first record, the top most left cell, to • To create a query, click the Create tab. In the Ribbon, click
select it >Type in the text Last Name & Press ENTER Query Wizard.
>Click on the down arrow in the Data Type cell. • When the New Query window appears, make sure Simple
• To create a form from a table, click on the Form Query Wizard is selected.
icon from the Create tab. • Transfer data from the Available Field to the Selected Field one
at a time using >

20
 23-Dec-20

Creating Queries (cont.)

Working with Reports
When the final screen appears, type the Names of
the query
 To create a report by using the Report Wizard,
click the Create tab, and in the Reports group,
• Click in the circle next to “Open the query to
click Report Wizard.
view…” to select it.

• Click <Finish>
• The query is automatically saved and executed.

Define Relationships Define Relationships

 Three types of table relationships:  Double-click the line representing the relationship to which
 one-to-many, you want to apply referential integrity.
 many-to-many and  At the bottom of the Edit Relationships dialog box, select
 one-to-one. Enforce Referential Integrity
 Open Access 2013  Click Cascade Update Related Fields to ensure that changing a
primary key value in the primary table automatically updates
 In the Database Tools tab Show/Hide group, click
the foreign key field.
Relationships
 Click Cascade Delete Related Records
 Click Show Table.
 Click OK to return to the Relationships tab.
 Select a table and click Add. Repeat to add
 If prompted, select Yes to save changes to the relationships
 Click Close
layout
 Drag the primary key field from one table to the equivalent
foreign key field in another table. Click on Create tab.
Continue dragging the primary key fields until all chosen
tables are related

21
 23-Dec-20

Relational Databases
 The real power of Access is derived from multiple
tables and the relationships between those tables.

Database design process

THE DATABASE DESIGN PROCESS THE DATABASE DESIGN PROCESS

 A database is a carefully designed & constructed  Systems development
repository of facts.  Process of creating information system
 It is a part of a larger picture called information system.
 Database development
 DB design focuses on how the database structure will
be used to store & manage end-user data.  Process of database design and implementation
 Creation of database models
Database design must reflect the information  Database design
system of which the database is a part
 create complete, normalized, non-redundant (to the
 A complete information system is composed of greatest extent possible), and fully integrated
people, hardware, software, the database(s), conceptual, logical, and physical database models
application programs, and procedures.  Implementation
 Systems analysis  Creating storage structure
 Process that establishes the need for an information  Loading data into database
system and its extent.  Providing for data management

22
 23-Dec-20

THE DATABASE DESIGN PROCESS

SDLC
The cycle that traces the history of an
information system.
The SDLC provides the big picture within which
database design & application development
can be mapped out and evaluated.

Phase 1: Initial Study Activities Phase 2: Database Design

Makes sure final product meets
requirements
Focus on data requirements
Sub-phases
I. Create conceptual design
II. DBMS software selection
III. Create logical design
IV. Create physical design

23
 23-Dec-20

Database Design Process

I. Conceptual Design
Data modeling creates abstract data
structure to represent real-world items
High level of abstraction
Four steps
Data analysis and requirements
Entity relationship modeling &
normalization
Data model verification
Distributed database design

Data analysis & Requirements Entity Relationship

Modeling and Normalization
Focus on:
Information needs
Information users
Information sources

Data sources
Developing and gathering end-user data views
Direct observation of current system
Interfacing with systems design group

Business rules

24
 23-Dec-20

Data Model Verification E-R Model Verification Process

E-R model is verified against proposed

system processes
End user views and required transactions
Access paths, security, concurrency control
Business-imposed data requirements and
constraints

Reveals additional entity and attribute

details

Iterative Process of Verification II. DBMS Software Selection

DBMS software selection is critical
Advantages and disadvantages need study
Factors affecting purchasing decision
 Cost
 DBMS features and tools
 Underlying model
 Portability
 DBMS hardware requirements

25
 23-Dec-20

III. Logical Design IV. Physical Design

Translates conceptual design into internal
model Selection of data storage and access
characteristics
Maps objects in model to specific DBMS Very technical
constructs More important in older hierarchical and
network models
Design components
Tables Becomes more complex for distributed
Indexes systems
Views
Designers favor software that hides physical
Transactions
details
Access authorities
Others

Phase 3: Implementation & Phase 4: Testing and

Loading Evaluation
Creation of special storage-related constructs Database is tested and fine-tuned for
performance, integrity, concurrent access, and
to house end-user tables security constraints
Data loaded into tables Done in parallel with application programming
Other issues Actions taken if tests fail
 Performance Fine-tuning based on reference manuals
 Security Modification of physical design
 Backup and recovery
Modification of logical design
 Integrity
Upgrade or change DBMS software or
 Company standards
hardware
 Concurrency controls

26
 23-Dec-20

Phase 6: Maintenance &

Phase 5: Operation
Evaluation
Database considered operational Preventative maintenance

Starts process of system evaluation Corrective maintenance

Unforeseen problems may surface Adaptive maintenance

Assignment of access permissions
Demand for change is constant
Generation of database access statistics to
monitor performance
Periodic security audits based on system-
generated statistics
Periodic system usage-summaries

Database Design Strategies Database Design Strategies

Top-down vs. Bottom-up Centralized vs. Decentralized
Top-down design (e.g., E-R modeling) Centralized design
1. Identify entities/data sets. small number of objects and procedures
2. Define attributes/data elements for each single design process
entity. Decentralized design
Bottom-up design (e.g., normalization) large number of entities with complex
1. Identify attributes. relations and operations
2. Group them together to define entities. multiple parallel design of subsystems &
aggregation

27
 23-Dec-20

Chapter End

28