Transcript Slide 1

Advanced Accounting
Information Systems
Day 10 answers
Organizing and Manipulating Data
September 16, 2009
Announcements
– Return Quiz 3
– Business Week – Free food at noon today
– Assignment 2 is posted
– Graduate student papers
Objectives






Understand normalization process
Understand techniques for validating data inputs
Understand the importance of extracting data from database and
AIS uses of such extractions
Create simple and multitable queries using Access
Understand object-oriented and multimedia databases
Be familiar with data warehouses and their uses in accounting
applications
Questions for today

What are the two methods to design databases?

What are the advantages / disadvantages of each?

If you wanted to use a bottom/up approach to
designing a database, which method would you use?

If you wanted to use a top/down approach to designing
a database, which method would you use?
Questions for today


Review first normal form
– Definition
– Examples of concerns
• Data redundancy
• Insertion anomaly
• Deletion anomaly
Second normal form
– Definition
– Examples of concerns
• Transitive relationship

Third normal form
Question for today

What is a database management
system?
– Process of examining and arranging file
data in a way that helps avoid problems
when organizations use or modify them later
– Alternative to data modeling concepts such
as ERD, organizations may use
normalization to organize the data
intelligently
Question for today

Describe the three problems associated
with a database in first normal form
– Data redundancy
– Insertion anomaly
– Deletion anomaly
Question for today

What is a data warehouse?
What is wrong with flat files?

Flat files – files with no sequence or
order to them – student grades of an
examination in random order
– Almost impossible to
• find a particular record easily (because records
are not stored systematically),
• link files to one another to provide information
from related records
• store file data efficiently
Three levels of normalization we
will look at



First normal form
Second normal form
Third normal form
First normal form (INF)


All record’s attributes (data fields) are
well defined and information can be
stored as a flat file – compare Figure
14.1 to 14.2
Problems remain
– Data redundancy
– Insertion anomaly
– Deletion anomaly
Second normal form (2NF)


Database is in first normal form and all
the data items in each record depend on
the record’s primary key
Problem remain
– Field A determines value in field B
Third normal form (3NF)


Database is in second normal form and
contains no transitive dependencies (i.e.
no relationships where data field A
determines data field B)
Actions available with 3NF
– Avoids insertion anomaly
– Avoids deletion anomaly
DBMS



Software system that allows users to create
database records, delete records, access
specific information, select (query) records for
viewing or analysis, alter database
information, reorganize records as needed
Not a database
Examples – Access, dBASE, paradox, FoxPro,
DB2 , Oracle, Sybase, SQL Server
Data Validation

DDL of DBMS
–
–
Enables users to define the record structure of any particular database table
Validation techniques
• Proper data types for fields
• Input masks – telephone numbers, ssn, date
• Default values – 40 hours per week
• Validation rules – between 1 to 100
• Referential integrity –
Cascade update –
Cascade downward
Extracting Data from Databases:
Data Manipulation Languages




Schema
– Map or plan of the entire database
Subschema
– Subset of information in database – designed to meet user
needs
Queries
– Allow database developers to create customized subschemas
Dynaset
– Dynamic subset of a database that you create with such
queries
Creating Action Queries

Update queries
– Allows user to alter selected table records systematically

Append queries
– Allow user to append records from one table to the end of
another table

Delete queries
– Allow user to delete table records selectively

Make-table queries
– Allow user to create a new table from the records that you
select in an existing table
Creating Action Queries

Find-duplicate queries
– Allows user to find records with duplicate entries in specified
fields

Find-unmatched queries
– Allow user to find the records in one table with no matching
records in another table

Crosstab queries
– Allow user to perform statistical analysis of the data ina table
and provides the cross-tabulation results in a row-and –
column format similar to a pivot table in a spreadsheet
Guidelines for Creating Queries





Spell accurately – look out for Case sensitivity
Specify AND and OR correctly
Be sure to JOIN tables properly
Name queries systematically
Choose data fields selectively
Structured Query Language
Select attribute(s)
 From table(s)
 Where restrictions
and joins
 Group by
 Order by

More complex databases

Object-oriented

Multi-media

Multidimensional databases
More complex databases

Data warehouses (enterprise wide systems)
– Pool data from separate applications into a large, common
body of information
– Useful if data warehouses include
– Advantage
– Need to standardize and scrub (clean) data to ensure uniform
accuracy and consistency
– look at restaurant example – p. 462-463

Data marts –
First SQL Practice

Marcia Felix Corporation
– Problem 14.21
– Employee table – Figure 14-15
Questions for Friday

Identify and describe five basic features
of SQL