COMP - Chapter 1 Accounting Information Systems: An...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Chapter 1 Accounting Information Systems: An Overview Learning Objectives Define “system” Understand what an AIS is Describe the basic AIS functions Understand why studying AIS is important Examples of Systems UH system UH main Campus CBA Registrations NYSE Automobile production Accounting information system “System” Defined A set of two or more interrelated components The components interact to achieve a goal Smaller subsystems usually perform a specific supporting function Components of an AIS People Procedures Data Software IT infrastructure Internal controls and security measures Three Functions of an AIS 1. Collecting and storing data about a) Resources affected by the b) Events (activities) of the organization and the c) Agents who participate 2. Transforming data into information useful for decision making 3. Providing adequate controls to safeguard data and other assets Information Usefulness Decision Usefulness Understandable, Complete & Accessible Relevance Reliability Predictive Value Verifiability Feedback Value Neutrality Representational Faithfulness Timeliness Comparability Consistency Convergence of GAAP and IFRS Costs Fundamental Characteristics Enhancing Characteristics Comparability Relevance Verifiability Decision Usefulness Faithful Representation Timeliness Read Convergence of FASB and IASB on WebCT under Readings Materiality Understandability Why Study AIS ? Auditors must understand the system used to prepare financial statements Tax accountants must have confidence in tax planning and compliance information CPAs often help design, select, and implement AIS as management consultants Internal accountants must understand the system used to collect, store, and transform financial data into information AIS vs. MIS AIS courses focus on Accountability Control Specific accounting software MIS courses focus on specialized skills Database Expert systems Telecommunications AIS Design Factors ORGANIZATIONAL ORGANIZATIONAL CULTURE CULTURE STRATEGY AIS INFORMATION TECHNOLOGY TECHNOLOGY Strategic Role of AIS AIS is an important part of strategy Traditionally viewed as financial transaction processing systems Debit/credit based Other systems filled nonfinancial requirements Enterprise resource planning (ERP) systems integrate financial and nonfinancial information requirements AIS Adds Value Improved Quality & Reduced Costs Improved efficiency Improved decision making Shared knowledge Decision Structure Structured Repetitive & routine Delegated to lower-level employees Example: retail check out Incomplete rules Subjective assessments & judgment supplement formal analysis Example: capital investment analysis Nonrepetitive & nonroutine Incomplete formal framework for analysis Require experience, judgment & intuition Example: Advertising decisions Semistructured Unstructured Decision Scope Operational control Effective & efficient performance of specific tasks Example: Wal Mart check-out clerks & supervisors Management control Effective & efficient use of resources for accomplishing organization objectives Example: Wal Mart store manager Strategic planning Establishing organization objectives & policies Example: Wal Mart CEO Decision Synthesis Level Top management Middle management Semistructured Lower-level supervisors & employees Scope Strategic planning Management control Operational control Semistructured or structured Structure Unstructured The Cost of Information Collecting data Processing data into information Storing data & information Organizing information into a useful form Distributing information to decision makers END OF CHAPTER 1 Chapter 2 Overview of Business Processes Learning Objectives Learning Understand the basic business transaction cycles Become familiar with basic accounting documents & procedures Understand the basic functions of an AIS Become familiar with the basic internal control objectives and procedures Overview of Business Processes Overview The AIS and Its Subsystems Obtaining and repaying funds from investors and creditors FINANCING CYCLE Get cash Give cash Selling products and services and REVENUE CYCLE collecting cash Give goods Get cash Obtain and paying for inventory, EXPENDITURE CYCLE supplies and Give Get services cash goods General Ledger and reporting system Obtaining and HUMAN paying for laborRESOURCES CYCLE give cash get labor Produce the products and PRODUCTION CYCLE services for sales Give Get F/G MLO Basic Expenditure/Revenue Cycle Expenditure PO 1 2 Receiving Report 4 3 Payment Voucher 5 Payment 1 2 Sales Order 5 4 3 Invoice Shipping Document Components of the Accounting System Components Chart of accounts General journal Subsidiary ledgers Special journals Report generation Internal control Audit trail Data Input Data Paper source documents original data are recorded on paper Turnaround documents returned to sender to be used as input Data entry screens Original data are entered directly by keyboard Source data automation Turnaround Document Example Turnaround “Detach and Return Top Portion With Your Payment. Return Address on Reverse Side” Data Processing Data Updating recorded information Resources affected by the event Agents who participated Types Batch Online real time Data Storage Elements Data Entity: something about which information is stored Attribute: characteristic of interest which is stored Field: physical space where data values are stored Data value: the contents of a field Record: the set of data fields for an entity File: a group of related records Database: a set of interrelated coordinated files Data Storage Elements A sset Num b er 3479 1453 8526 6278 A sset Desc rip t ion Com p ut er Truc k Phot oc op ier A irc ra ft Purc ha se Da t e 01/ 02/ Y1 07/ 14/ X5 11/ 29/ X9 03/ 19/ Y1 Orig ina l Cost 3,000 55,000 5,000 8,000,000 A c c um ula t ed Dep rec ia t ion 1,200 38,500 3,000 1,000,000 Accounting Terminology Accounting Files used to store cumulative information are __ledger accounts__ The set of summary accounts is the _______general ledger The set of accounts containing detailed data for a general ledger account The that has many subaccounts is a __________subsidiary ledger that A general ledger account that has many subaccounts is a _____________ The Chart of Accounts The • Lists of all the accounts in use • Structure is very important – – It affects the detail of transaction classification and recording It affects the preparation of financial statements and reports • More detail usually better than less (rollup) • The code system should identify account categories and The subcategories subcategories • Each major category should be assigned a block of numbers • Should correspond to financial statement order • Gaps for room to grow • Differ from one organization to another depending on needs – Retail, manufacturing, service, etc. Chart of Accounts Chart See charts of accounts for Peachtree sample companies Internal Control Internal Objectives Information reliability Business efficiency Compliance with management objectives Compliance with regulations Control Issues Control Input control Processing control Output control Data access Physical access Internal Control Examples Internal System Documentation a) b) c) Helps with verification that assigned responsibilities were completed correctly Helps identify potential problems Helps avoid making commitments that cannot be kept Prevents concentration of transaction control with one person Especially important for cash & other assets easily converted to cash Challenging for small organizations Segregation of duties a) b) c) Transaction authorization a) b) Prevents concentration of transaction control with one person Helps assure that transactions are bona fide Counts and reconciliations a) b) Helps maintains agreement between records and physical reality Especially important for cash, inventory, and depreciable assets Planning systems and documentation (budgets, strategies, etc.) a) b) Establishes a benchmark for comparing what is with what should be Helps guide management decisions More Internal Control Examples More Sequentially prenumbered data sources (paper documents & Sequentially computer records) computer Simplifies completeness verification Simplifies verification that no documents have been lost Facilitates communication with interested parties Well designed forms and screens Grouping related data together Providing instructions and/or prompts Presenting options with check-off boxes, buttons, & pull-down menus Shading and bordering separate items Opportunity for application of psychology and graphic arts Review financial statements before distribution More later Transaction Processing General & Special Journals General & Subsidiary Ledgers Learning Objectives Learning Understand AIS data flow Describe & understand special journals Describe & understand subsidiary ledgers Journals & Ledgers Journals Special journals • Sales journal • Purchases journal • Cash receipts journal • Cash disbursements journal • Payroll journal General journal General ledger Subsidiary ledgers ACCOUNTING SYSTEM INFORMATION FLOWS OBJECTIVE VERIFIABLE EVIDENCE ANALYZE CATEGORIZE SUSPENSE FILE JOURNALIZE IN SPC JRN JOURNALIZE IN GEN JRN POST DETAILS TO SUBS POST TO GL & CONT- ROL RECONCILE SUBS & CONTROL PREPARE FINANCIAL STMNTS PREPARE TRIAL BALANCES PREPARE ADJ ENTRIES INCOME STATEMENT BALANCE SHEET STATEMENT OF CHANGES IN RET EARN STATEMENT OF CASH FLOWS SPECIAL JOURNALS & SUBSIDIARY LEDGERS (abbreviated example) Sales Journal Customer Wine Depot Liquor Locker Stein's Fine Wines Liquor Locker Dr A/R & Cr Sales 300.00 500.00 400.00 100.00 1,300.00 Date Invoice # 1/2/05 1001 1/15/05 1002 1/22/05 1003 1/28/05 1004 Acct. # 2404 2401 2403 2401 A Cash Receipts Journal Account Name Dr Cash Wine Depot 300.00 Liquor Locker 200.00 Wine Depot 150.00 Stein's Fine Wines 100.00 Dividend income 50.00 800.00 Accounts Receivable Subsidiary Ledger Accounts Acct. # 2404 Date Ref 1/1/05 1/2/05 SJ 1/7/05 CRJ Acct. # 2401 Date Ref 1/1/05 1/15/05 SJ 1/20/05 CRJ 1/28/05 SJ Wine Depot Credit Balance 0.00 300.00 300.00 0.00 Liquor Locker Credit Balance 300.00 800.00 200.00 600.00 700.00 Debit 300.00 Date Acct. No. 1/7/05 2404 1/20/05 2401 1/25/05 2404 1/30/05 2403 1/31/05 5608 Cr A/R 300.00 200.00 100.00 600.00 Cr Sales Cr Sundry 150.00 50.00 50.00 150.00 Debit 500.00 100.00 General Ledger Accounts Acct # 1000 Date 1/1/05 1/31/05 Acct # 1400 Date 1/1/05 1/31/05 1/31/05 Acct # 4000 Date 1/31/05 1/31/05 Acct # 8000 Date 1/31/05 Cash Balance 100.00 900.00 Ref Debit 800.00 Credit Acct. # 2403 Date Ref 1/1/05 1/28/05 SJ 1/30/05 CRJ Debit 400.00 Stein's Fine Wines Credit Balance 0.00 400.00 100.00 300.00 Ref SJ CRJ Accounts Receivable Control Debit Credit Balance 300.00 1,300.00 1,600.00 600.00 1,000.00 Sales Balance 1,300.00 1,450.00 A Ref SJ CRJ Debit Credit 1,300.00 150.00 A Ref CRJ Debit Dividend Income Credit Balance 50.00 50.00 END OF CHAPTER 2 END Chapter 3 Systems Development Systems and Documentation Techniques Learning Objectives Learning Prepare and use data flow diagrams Prepare Draw flowcharts In order to understand, evaluate, and design In information systems information Introduction Introduction A clear understanding of the system is a precondition for Operation Internal control evaluation Methods of describing systems Graphical methods Data Flow Diagrams (DFDs) Flow charts (document & system) Program flow charts (not covered in this course) Written descriptions Combination of a graphical methods and written Combination descriptions descriptions Documentation Skill Levels Read To understand how a system operates To understand job tasks and responsibilities Evaluate System of internal controls Identify strengths and weaknesses Design audit program Recommend improvements Proposed systems Prepare System of internal controls description System implementation proposals Documentation Tools Data flow diagram: graphical representation of data flow Document flow chart: graphical representation of a document flow System flow chart: graphical representation of system inputs, processing, System and output and Program flow chart: graphical representation of program operations Data Flow Diagrams Graphically describe data flows Within existing systems For planning and designing revision of new For systems systems Elements Data sources Data Data processes Data destinations Data storage Data Flow Diagram Symbols Data Symbol Name Data sources and destinations Explanation People & organizations that send data to and receive data from the systems Storage of data Data stores Transformation processes Transformation of data from inputs to outputs Flow of the data into or out of a source, destination, store or process Data flows Sales Order Processing DFD Sales Context Diagram Sales Order Processing DFD Sales DFD Guidelines Understand the system Observe Interview Ignore Control processes Control actions Determine system boundaries Include all relevant items Exclude irrelevant items If in doubt, include (review later) DFD Guidelines (cont.) Identify data flows entering or exiting a system’s Identify boundary boundary All data flows come from and go to either a Transformation processes Data store (file) Data source Data destination Data flows can be Unidirectional Bidirectional DFD Guidelines (cont.) Identify data transformation processes Processes transform data into information All processes should have one or more incoming and outgoing data All flows flows Group transformation processes that Are logically related Or occur at the same time and place Describe all data stores (files) Identify all data stores Identify all data flows that enter or exit a data store DFDs do not identify the storage medium Name all DFD elements Use verbs & verb phrases to name processes Use nouns & noun phrases to name everything else DFD Guidelines (cont.) Subdivide the DFD if more than 5-7 processes Give each process a sequential number Data should flow from lower to higher numbered processes Exception: bidirectional flows Repeat the process until refined Prepare a final copy Data flow lines should not cross Include on each page • • • DFD name Preparer’s name Date Subdividing the DFD DFDs are subdivided into successively lower levels with DFDs greater detail greater The highest level is refered to as a ___________. Summary level view Depicts • The system • Entities that are sources and destinations of inputs and Entities outputs outputs Document Flow Charts Describe manual systems Describe manual Graphically portray document flows Physical movement of documents and Physical information Departments and responsibility areas involved Departments Activities performed “Cradle to grave” Origination Distribution Distribution Processing System Flow Charts Portray systems that have both manual and computer Portray manual components components Input Processing Output Disk Tape Terminals etc. Also describe media used by the system Basic Flow Chart Symbols Basic Symbol Represents Start & end points Paper document Multi-part paper document Manual input Decision Computer display Computer process Symbol Represents Computer disk data storage Direct access storage T T Document file (Temporary, On-page connector Off-page connector Manual process Flow by Date, by NAme, by Number) See Microsoft Excel Autoshapes for more Flowchart Guidelines Understand the system Observe Interview Identify the entities Divide the flowchart into columns Flow proceeds generally from top to bottom, left to right Clear beginning and ending Manual processing should have an input and output No direct connection of two documents A document moving between columns should be shown in document both columns both Flowchart Guidelines (cont) Use connectors On-page connectors avoid excessive lines Off-page connectors move among pages Manual processes are not needed to show Manual no Forwarded documents Documents entering a file Data stored or retrieved from a computer file should pass Data through a processing operation (program) first through Use an iterative process Start with a rough sketch Redesign to avoid clutter and crossed lines Verify accuracy Identify each page with flowchart name, date, and preparer’s name Amusement Park Maintenance Flowchart Example Amusement (see separate Lecture Guide file on WebCT for a more readable version) RIDE OPERATIONS RIDE OPERATOR JRU GENERAL MANAGER SERVICE REQUEST 2 MAINTENANCE A SERVICE REQUEST 2 SERVICE REQUEST 2 ACCOUNTING B WORK ORDER 4 PREPARE SVC REQUEST APPROVE? SERVICE REQUEST 1 2 YES NO PREPARE W ORK ORDER COST & PREPARE SUM REPAIR TYPE? WORK ORDER 2 3 4 1 SERVICE REQUEST 2 WRK ORDER SUMMARY 2 1 3 WORK ORDER 4 MAINTENANCE YES APPROVE? A C RECORD MAT'LS & LABOR NO C SERVICE REQUEST 2 WORK ORDER 2 N WORK ORDER 3 4 C N N B Prepared by: J.B. Stinson Prepared on: 5/5/2005 Last revised on: none END OF CHAPTER 3 END Chapter 4 Relational Databases Relational Learning Objectives Learning Understand the difference between Understand relational database and file-based systems relational Describe a relational database Understand the difference between logical Understand and physical views of a database and Be able to construct well-structured tables Be to properly store data to Data Hierarchy Accounts Payable File Example Accounts Database Database Customer File Record1 Vendor1 Field 1 Vendor Number Field 3 Web Address Field 5 City Field 7 Zip Code Vendor File Record 2 Vendor 2 Inventory File Record N Vendor N Field 2 Vendor Name Field 4 Street Address Field 6 State Field 8 Country Types of Files Types Two Basic Files Two Master files Transactional Master File Stores information about Resources of the organization Agents with whom it interacts Permanent (__cumulative________) Exists across fiscal periods Individual records are frequently changed Occasionally records are added/deleted Conceptually similar to the _ledger_____ Transaction File Stores information about Specific business transactions (events) That occur during a __fiscal period________ Not permanent (__temporary_____) Usually maintained on-line For one fiscal period Closed to the ___master file______ Conceptually similar to the _journal____ Database Systems Address the problem of file proliferation Data integration Data sharing Components Database Database management system (DBMS) Application programs File-based Approach File-based Master File 1 Master File 1 Data: A B C D Master File 2 Data: A C D Master File 3 Data: A B D E Purchasing Program Receiving Program A/P Program Database Approach Database Database Database Data: A B C D E DBMS Purchasing Program Receiving Program A/P Program Logical & Physical Views of Data The __logical__ view is how the user or programmer The conceptually organizes and understands the data. conceptually The __physical__ view is how and where the data are The arranged or stored. arranged The benefit of separating the two views is: The programmers can focus on the logic of the application without focusing on how and where data are stored and accessed and Logical & Physical Views of Data Logical Logical view: Logical STUDENT ID 451087475 123635988 662345582 573447913 259187364 582597320 226371961 NAME (Last, First, MI) HOME ADDRESS STREET 6214 Bonner 4806 East 42nd 2021 Elm 1315 Dover 3165 Glove CITY Houston Spring Katy Alief Houston Houston STATE TX TX TX TX TX TX TX ZIP 77024 77081 77017 77530 77040 77022 77008 HOME PHONE 7137818301 7134652328 7132404588 2815589553 7137434276 7137830183 2815317548 CR HOURS SEM 15 12 15 18 12 15 12 TOT 45 120 95 37 84 118 75 Decker, William B 5193 Elton Elliott, James D Su, Alice S Able, Robert A Nguyen, Kim H Grimes, Gerald Shah, Mohan 9197 Havner Ln Houston Physical view: 11 10 11 00 10 00 01 The Database Management System (DBMS) (DBMS) Links the __physical data__ with the _logical view__ Links of users of Allows users to access, update, or query the database Allows without regard to __physical storage__ without Allows users to change their logical view without Allows changing the way the data are __physically stored__ changing The database administrator (DBA) can change the The way the data are physically stored without affecting __users or application programs__ __users The DBMS Supports Multiple Logical Views of Data User A Logical View Students by Zip Zip Name Zip Name 77022 Grimes 77024 Decker . . . . . . DBMS Operating System User B Logical View Credit Hours Credit Name Hrs Hrs Able 37 Decker 45 . . . . . . Translates logical views into data retrieval requests Translates DBMS requests into physical data retrieval instructions Database Database Schema Describe __the logical structure of the database__ The three levels of schemata are: External level schema Conceptual level schema Internal level schema The next slide depicts these schema graphically Levels of Schema Levels Subschema User A Students by Zip Subschema User B Total Hours Subschema User C Class Roll External Level User logical views of portions of the database (subschema) Mapping Courses Students Class Enrollment Pre­ requisites Conceptual Level Conceptual view (Enterprise view) of entire database Mapping 11 10 11 00 10 00 01 Internal Level Physical view (data storage details) Accountants and Schema Accountants most often are involved in which two schema levels? External level schema Conceptual level schema External subschema should be designed to: Satisfy the needs of individual users and applications Prevent access to irrelevant database information Conceptual level schema should be designed to: Provide the information to support a variety of external schema Data Dictionary Contains information about the structure of the database For each data element stored in the database, there is a corresponding record describing it Example - vendor data from an Access database: DBMS Languages Data Definition Language (DDL) • Builds the data dictionary • Initializes or creates the database • Describes the logical views • Security • System restrictions Data Manipulation Language (DML) • Updates data • Inserts • Deletes • Access restricted to systems administrators and programmers (no users) Data Query Language (DQL) Interrogates the database in response to user queries Retrieves, sorts, orders, and presents subsets of the database Access granted to users Report writer Assists with the creation of reports Access granted to users DDL Example DDL DDL command to create (not fill) the customer table below: CREATE TABLE (Customer# Name Address City State Zipcode Balance Customer Table Customer # Name ► 0 0 1►► Record ◄ ◄ Contact 0 ►* Customer INTEGER (5), NOT NULL CHARACTER (15), CHARACTER (20), CHARACTER (12), CHARACTER (2), CHARACTER (10), FLOATING (10) ) X Address 0 City 0 State 0 Zipcode 0 Balance 0 of 1 DML must be used to fill the table (next slide) DML Example DML DML command to insert a new row: INSERT INTO Customer (Customer#, Name, Contact, Address, Customer City, State, Zipcode, balance) City, VALUES (2215, ‘Creepy Critters’,’Weird Al’,’7301Crazy LN’, Crawly’,’TX’,72010’,0) Customer Table Customer # Name Contact ► 2215 Creepy Critters Weird Al 1 ► ► ►* of 1 Record ◄ ◄ X Address City 7301 Crazy Ln Crawly State TX Zipcode 72010 Balance 0 DML Example DML DML command to update Creepy Critters: UPDATE SET WHERE Customer Balance = 8476 Customer# = 2215 X Customer Table Customer # Name Contact ► 2215 Creepy Critters Weird Al ◄◄ 1 ► ► ►* of 1 Record Address City 7301 Crazy Ln Crawly State TX Zipcode 72010 Balance 8476 DML command to delete Creepy Critters: DELETE FROM WHERE Customer Customer# = 2215 Access Query Example Access Tables: Order ID 122 238 753 824 829 923 942 Customer Employee Number ID 103 2465 105 1538 103 2465 101 2768 103 1245 104 1285 103 5345 Quantity Unit Price 21 4.52 87 4.49 9 7.85 48 6.48 842 2.99 192 3.99 638 3.29 Birthdate 5/23/1980 5/23/1980 7/9/1983 4/5/1972 6/20/1960 2/3/1959 Access Query: Employee ID First Name 1245 Tom 1285 Erin 1538 Paula 2465 Christie 2768 Jamie 5345 Michaela Customer Number 100 101 102 103 104 105 106 Last Name Morgan Franklin Younger Oldham Smith Jones Customer Name Jacob Johnson Linea Marks Smith Wiggum Jonas Region Texas Oklahoma Oklahoma Texas Texas Louisiana New Mexico Query results: Last Name Jones Morgan Oldham First Name Michaela Tom Christie Customer Number 103 103 103 Customer Name Marks Marks Marks Relational Databases A DBMS is characterized by the type of data model upon which it is based A data model is an abstract representation of the contents of a database Most databases are called relational databases • They use the relational data model • Developed by E.F. Codd (1970) Relational Data Model A relational data model represents everything in the form of __tables____ Tables are called ___relations___ Each row is called a ___tuple___ The data are not actually stored in tables Stored according to the _internal level schema_ RDM only describes how the data appear in the • __conceptual level_ schema • ____external level__ schema Types of Attributes A _primary key_ uniquely identifies a row (_tuple_) Can be a single __attribute__ Can be a combination of attributes A foreign key is an attribute that __appears in one table __ ___and is a primary key in another table_ Non-key attributes __store information about the entity RDM Requirements A well structured (normalized) database: Every column in a row must be single valued Primary keys cannot be null (_entity integrity rule) Foreign keys must be either null or have a value that corresponds to the value of a primary key in another table (__referential integrity rule__) Non-key attributes should describe a characteristic about the object identified by the primary key Integrity Rules Texas Drivers License Table TDL 100 200 300 400 500 700 800 900 Last Name First Name Street City Student Table Peoplesoft ID 11 12 13 14 15 16 17 18 TDL 300 700 200 500 800 400 100 Last Name First Name Street City Problems with Storing All Data in One Table Problems Creates redundancy: Creates Update anomaly Insert anomaly Delete anomaly Delete Update Anomaly Changes to some redundant data values are overlooked if all data are in one table Example: a new in vendor web address • Vendor data would be repeated for every purchase • Changes would have to be made wherever the number appeared rather than just once • Some might be missed Inefficient communication Lost on-line B-to-B orders Insert Anomaly Inability to add new entities until a transaction occurs Example: if P.O. number is primary key • A new vendor could not be entered until a P.O. number is issued • Therefore, a new vendor could not be entered until the first purchase occurred Delete Anomaly Unintended results when deleting a row in a table that contains all data Example: only one item has been purchased from a vendor • Deleting that row would delete all information about the vendor Un-normalized Data Registrar’s view of university student enrollment (unnormalized database): Stu Num Stu Name 6432 Adams Major ACCT Course ACCT2331 ACCT2332 MATH1421 7689 8653 Shinn Miller MANA ACCT MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study Calculus Intro Mgt Professor Stinson Francia Smith Phillips Archer Stinson Smith Phillips Office 390 370 230 343 285 390 230 343 Phone Grade 34898 34825 31892 32788 33561 34898 31892 32788 A B B C B B B C What are examples of •Delete anomaly? •Update anomaly? •Insert anomaly? Normalization Normalization is one approach to designing well-structured databases (more later) Potential Impact of Database Systems on Traditional Accounting Elimination of double entry accounting redundancy • Trial balances are redundant checks • Computer can do it Changes in financial reporting & managerial decision making • Database available to users • Users determine their own needs • Integration of operating and financial data (Wal Mart) Types of Query Languages Text based: Structured Query Language (SQL) Graphical: used by Access This is all you are responsible for from the appendix • We won’t use SQL (take the MIS database course) • We will use the graphical language of Access END OF CHAPTER 4 MS Access Assignment Database Design NOT IN THE BOOK Learning Objectives List the steps for database design List the steps in the normalizations process Understand the normalization process Database Design Steps for Converting User Views to Underlying Tables Identify user views Create an unnormalized table for each user view Determine table relationships (1:1, 1:N, M:N) Identify foreign keys (and place in tables) Add database attributes the topic for today Normalize the tables Normalization Normalization Eliminates anomaly-causing dependencies: Repeating groups Partial dependencies Transitive dependencies Anomalies Anomalies Update anomaly Cause: Insertion anomaly Cause: Deletion anomaly Cause: Normalization Splits unnormalized tables into smaller tables such that • All nonkey attributes in the table are dependent on the entire primary key • all nonkey attributes are independent of the other nonkey attributes Tables that meet these are in third normal form (3NF) Look for “themes” for each table The Normalization Process The Unnormalized Data User Table Remove repeating groups Data in First Normal Form (1NF) Remove partial dependencies (exist only when primary key is concatenated) Data in Second Normal Form (2NF) Remove transitive dependencies Data in Third Normal Form (3NF) 1. 2. Base Table All nonkey attributes depend on the primary key All nonkey attributes are independent of other nonkey attributes Normalization Example Normalization Registrar’s view of university student enrollment (unnormalized database): Registrar’s Stu Num Stu Name 6432 Adams Major ACCT Course ACCT2331 ACCT2332 MATH1421 7689 8653 Shinn Miller MANA ACCT MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study Calculus Intro Mgt Professor Stinson Francia Smith Phillips Archer Stinson Smith Phillips Office 390 370 230 343 285 390 230 343 Phone Grade 34898 34825 31892 32788 33561 34898 31892 32788 A B B C B B B C Are there any repeating groups that should be eliminated? If so, how? Students can take more than one course: The data for each course Remove Repeating Groups Remove Repeating Group Stu Num Stu Name PK Stu Num Stu Name Major Course Crse Name Professor PK Office Phone Grade Major ACCT MANA ACCT 6432 7689 8653 Adams Shinn Miller Student (3NF) Stu Num 6432 6432 6432 7689 7689 8653 Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Crse Name Professor Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study Calculus Intro Mgt Stinson Francia Smith Phillips Archer Stinson Smith Phillips Office Phone Grade 390 370 230 343 285 390 230 343 34898 34825 31892 32788 33561 34898 31892 32788 A B B C B B B C Theme: student 8653 8653 Course-Grade (1NF) What anomalies exist? Update: changes name of a course requires changing every record that contains course name Insert: cant add new course until at least one student registers Delete: data on ACCT4397 are lost if student 8653 withdraws from school Remove Partial Dependencies Remove PK Stu Num Course Crse Name Professor Office Phone Grade PK Stu Num 6432 6432 6432 7689 7689 8653 8653 8653 Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Grade A B B C B B B C Course-Grade (1NF) PK Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 • • Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study • • Transitive Dependencies (depend on Professor, not Course) Office 390 370 230 343 285 390 • • Phone 34898 34825 31892 32788 33561 34898 • • Professor Stinson Francia Smith Phillips Archer Stinson • • FK Student-Grade (3NF) Course-Professor (2NF) What anomalies exist? Theme: grades Update: professor must be listed for each course; any change in professor attributes must be changed for each occurrence Insert: Cant maintain data for professor unless teaching a course(what if new or on sabbatical?) Delete: data on professor are list if teaching only one course that is cancelled. Remove Transitive Dependencies Remove Transitive Dependencies (depend on Professor, not Course) Course Crse Name Professor Office Phone Course-Professor (2NF) PK Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 • • • Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study • • • FK Professor Stinson Francia Smith Phillips Archer Stinson • • • PK Professor Stinson Francia Smith Phillips Archer Office 390 370 230 343 285 Phone 34898 34825 31892 32788 33561 Professor (3NF) Course (3NF) Theme: course (assumes each prof teaches only one section of each course) Theme: professor (assumes phone depends on prof, not office) Four Normalized Tables Four Linked by Common Attributes PK PK Student (3NF) Stu Num Stu Name Major Stu Num Course Grade Student-Grade (3NF) FK Course (3NF) Course Crse Name Professor Professor Office Phone Instructor (3NF) PK FK PK Accounting and Normalized Data Update anomalies can cause conflicting and obsolete database values Insertion anomalies can result in unrecorded transactions and incomplete audit trails Deletion anomalies can result in loss of accounting records and destruction of audit trails Summary Splits unnormalized tables into smaller tables such that • All nonkey attributes in the table are dependent on the primary key • All nonkey attributes are independent of the other nonkey attributes Look for “themes” for each table END OF CHAPTER 4 END Chapter 15 Database Design Using the REA Data Model Learning Objectives Describe the REA model for designing a database Use the REA model to build an Access database REA Data Model Resources, Events, Agents Provides guidance for database design by • Identifying what entities should be included • How to structure relationships among entities REA Data Model Definitions Resources are things that have economic value Examples: cash, land, equipment Events are the activities of the business Examples: sales, purchases, cash receipt, cash disbursement Agents are the people and organizations that participate in the business activities Examples: customers, suppliers, employees REA Data Model Definitions Economic exchanges are the value chain activities that directly affect the quantity of resources Examples: sales, cash collection, cash disbursement Commitments represent promises to engage in future economic exchanges Examples: orders placed by customers, orders placed with vendors REA Data Model Each event is linked to a resource Events that change the quantity of a resource are linked to the resource by a stockflow relationship Events that represent future commitments are linked to the resource by reserve relationships Each event is linked to two agents Each economic exchange event is linked with another economic exchange event in a give-to-get duality relationship REA Data Modeling Steps Identify economic exchange events that represent give-to-get duality relationships Identify the resources affected by each economic exchange event Identify the agents who participate in each economic exchange event Analyze each economic exchange event to determine if it should be decomposed into a combination of one or more commitment events and an economic exchange event. Replace if necessary Determine the cardinalities of each event Revenue Cycle REA Diagram Revenue (Batini min max method fig. 15-6) Events Inventory­ Orders (0,N) Reserve Inventory (1,N) Customer Orders (0,1) Leads to (0,1) (1,N) Stockflows Economic Exchange (give­to­get duality) (1,1) Sales (1,1) (0,N) Sales­Cash Receipts (1,N) Cash Stockflow Cash Receipts (1,1) Participant Cashier Participant Participant (0,N) (0,N) Salesperson (1,1) (1,1) Each event linked w/ two agents Participant (0,N) Resources Agents Customer Commitment Inventory­ Sales (0,N) Participant Cardinalities (0,N) (0,N) Participant Customer (0,N) (1,1) (1,1) (0,N) Cardinalities Indicate how many instances of one entity can be linked to one specific instance of another entity In a relational database • Each entity is a table • Each instance is a row in that table Therefore, in relational databases cardinalities indicate how many rows in one table can be linked to each row in another table Cardinalities The first number is the minimum cardinality • Indicates whether a row in that table must be linked to at least one row in the other table • 0 means a new row can be added to that table without being linked to any specific rows in the other table • 1 means that each row in that table must be linked to at least one row in the other table The second number is the maximum cardinality • Indicates whether a row in that table can be linked to more than one row in the other table • 1 means each row in that table can be linked to, at most, only one row in the other table • N means that each row in that table can (but need not) be linked to more than one row in the other table Three Types of Relationships A one-to-one (1:1) relationship • Exists when the maximum cardinality for each entity in that relationship is 1 A one-to-many (1:N) relationship • Exists when the maximum cardinality of one entity in the relationship is 1 and the maximum cardinality for the other entity in that relationship is N A many-to-many (M:N) relationship • Exists when the maximum cardinality for both entities in the relationship is N Three Types of Relationships Three Cardinalities Relationship Cardinalities Relationship Cardinalities Relationship (0,N) (0,N) (0,1) (0,N) (0,1) (0,1) Revenue Cycle REA Diagram Revenue (Batini min max method - fig. 15-6) Events Inventory­ Orders (0,N) (1,N) Customer Orders (0,1) Inventory­ Sales Leads to (0,1) (1,N) (1,1) Sales (1,1) (0,N) Sales­Cash Receipts (1,N) Cash Stockflow Cash Receipts (1,1) Participant Cashier Participant (0,N) (0,N) Participant Customer Participant (0,N) (0,N) Salesperson (1,1) (1,1) Participant (0,N) Resources Agents Customer Inventory (0,N) Participant (0,N) (1,1) (1,1) (0,N) (Romney & Steinbart/Oracle graphical method - fig. 15-6) Revenue Cycle REA Diagram Revenue Implementing an REA Diagram in a Relational Database Relational Create a table for each distinct entity and for each Create many-to-many relationship many-to-many Assign attributes to appropriate tables Use foreign keys to implement one-to-one and oneto-many relationships REA Diagrams REA Useful for the Access assignment Read chapter 15 prior to beginning the Access Read assignment assignment END OF CHAPTER 15 END Chapter 17 Special Topics in REA Modeling Learning Objectives Develop REA data models for a variety of Develop organizations organizations Develop REA data Models for the HR/payroll, Develop and capital assets transaction cycles and Sale of Services (Romney & Steinbart fig. 17-2) Group exercise questions: • Why is the minimum cardinality from Sales to Inventory 0? • What does the cardinality from Sales to Employee indicate? • Why is the relationship between Services and Sales (M:N)? Rental Transactions (Romney & Steinbart fig. 17-3) Group exercise questions: • What might be the primary key in the Rental Inventory table? • What do the cardinalities between Rent Item to Receive Cash indicate? • Why is the relationship between Rent Item and Return Item (1:1)? HRM/Payroll Cycles (Romney & Steinbart fig. 17-7) Group exercise questions: • What might be the primary key in the Interview Applicants table? • What does the cardinality from Interview Applicants to Applicants indicate? • Any Big 4 accounting firm is required to interview each PPA student at TAMU. What does that change? Minimum of 0 changes to 1. What would group interviews change? • Why is the relationship between Skills and Employees (M:N)? Financing Cycle Transactions (Romney & Steinbart fig. 17-8) Group exercise questions: • Per the text, the minimum cardinality between Disburse Cash to Issue Debt is 0 because a particular Disburse Cash event may be linked to either an Issue Debt event or an Issue Stock event. Do you agree? • What do the cardinalities between Issue Debt and Cash indicate? • What do the cardinalities between Issue Debt and Transfer Agent indicate? ...
View Full Document

Ask a homework question - tutors are online