Accounting Information Systems 9 th Edition

Learning Objectives • Explain the difference between database and file-based legacy systems. • Describe what a relational database is and how it organizes data. • Explain the difference between logical and physical views of a database. • Create a set of well-structured tables to properly store data in a relational database. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Introduction Ashton Fleming, the accountant for S&S, believes that the best way to provide Susan Gonzalez and Scott Parry with easy access to the information they need to run their business is to build S&S’s new AIS as a database system. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Introduction • Ashton decides to prepare a brief report for them addressing the following questions: • What is a database system? • What is a relational database system? • How do you design a relational database? ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Introduction • This chapter explains what a database is and how it differs from a file-oriented system. • It also describes the structure of a relational database system. • The chapter concludes by discussing the basic steps involved in designing a database. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Types of Files • Two basic types of files are used to store data. • The master file, which is conceptually similar to a ledger in a manual system. • 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

File Approach • For many years, companies created new files and programs each time an information need arose. • This proliferation of master files created problems: • 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

File 1 Sales Program Fact A Fact B Fact C File 2 Shipping Program Fact B Fact D Fact E File 3 Billing Program Fact A Fact G Fact E File-Oriented Approach ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

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

Databases • Database management system(DBMS) is the program that manages and controls access to the database. • Database systemis 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

Database Approach Sales Program Database Fact A Fact B Fact C Fact D Fact E Database management system Shipping Program Billing Program ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Relational Databases • A data modelis an abstract representation of the contents of a database. • The relational data modelrepresents everything in the database as being stored in the form of tables. • Technically, these tables are called relations. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Relational Databases • 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

Logical and PhysicalViews of Data • A major advantage of database systems over file-oriented systems is that the database systems separate the logical and physical view of data. • What is the logical view? • It is how the user or programmer conceptually organizes and understands the data. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Logical and PhysicalViews of Data • What is the 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

Past Due Accounts NameBalanceDays Jackson 2145 48 Houston 1595 65 Logical and PhysicalViews of Data Logical View User A Logical View User B October Sales by Region Database DBMS Operating system ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Logical and PhysicalViews of Data Program-data independence is the separation of the logical and physical views of data. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Schemas • A schema describes the logical structure of a database. • There are three levels of schemas: • Conceptual-level schema • External-level schema • Internal-level schema ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Schemas • 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

Jackson 210 Houston 100 r r Inventory Sales Customer Cash receipt Schemas Subschema A Subschema B Subschema C xxxxxxx xxxxxxx Mapping external level views to conceptual level schema ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Schemas Inventory Sales Customer Cash receipt Mapping conceptual level facts to internal level descriptions Inventory Record Item number – integer (5), non-null, index = itemx Description – character (15) ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

The Data Dictionary • The data dictionary 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

The Data Dictionary • The data dictionary is often one of the first applications of a newly implemented database system. • What are some inputs to the data dictionary? • records of any new or deleted data elements • changes in names, descriptions, or uses of existing data elements ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

The Data Dictionary • What are some outputs of the data dictionary? • reports useful to programmers, database designers, and users of the information system • What are some sample reports? • lists of programs in which a data item is used • lists of all synonyms for the data elements in a particular file ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

DBMS Languages • Every DBMS must provide a means of performing the three basic functions: • 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

DDL Language • The data definition language (DDL) is used to... • 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. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

DML Language • The data manipulation language (DML) is used for data maintenance. • What does it include? • updating portions of the database • inserting portions of the database • deleting portions of the database ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

DQL Language • The data query language (DQL) is used to interrogate the database. • The DQL retrieves, sorts, orders, and presents subsets of the database in response to user queries. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Learning Objective 4 • Create a set of well-structured tables to properly store data in a relational database. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Basic Requirements of the Relational Data Model • Each column in a row must be single valued. • Primary keys cannot be null. • Foreign keys, if not null, must have values that correspond to the value of a primary key in an other relation. • All non-key attributes in a table should describe a characteristic about the object identified by the primary key. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Anomalies That May Occur in Non-Normalized Relational Tables • Update Anomaly: When changes (updates) to data values are not correctly recorded. • Instead of having to update once, each record in the single table has to be updated individually in order to avoid inconsistencies in the database. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Anomalies That May Occur in Non-Normalized Relational Tables • Insert Anomaly: There is no way to store information about one entity in the database without it being associated with another entity • In the text, we would not be able to store information on new customers without their being associated with transactions first! ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Anomalies That May Occur in Non-Normalized Relational Tables • Delete Anomaly: Unintended results arising from deleting a row of data pertaining to one entity and resulting in the deletion of data regarding another entity as well. • In the text, if a particular Inventory item were discontinued and hence removed from the database table, we would lose information on the customer associated with that inventory item as well. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

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

Database Systems and theFuture of Accounting • Database systems have the potential to significantly alter the nature of external reporting. • Perhaps the most significant effect of database systems will be in the way that accounting information is used in decision making. ©2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart

Accounting Information Systems 9 th Edition