Transcript Chapter 14
ACCOUNTING INFORMATION SYSTEMS The Crossroads of Accounting & IT Chapter 14 Accounting System Design © Copyright 2012 Pearson Education. All Rights Reserved. Databases Types Databases can be: External databases, such as Gartner Research. Internal databases, such as the database used to store accounting transactions. © Copyright 2012 Pearson Education. All Rights Reserved. Databases Types Internal databases can be classified as: Operational databases Databases used in the operations of the business Most are relational databases Oracle is the largest provider of relational database tools. Data warehouse Contains data collected from a variety of sources Data not used for routine business activities. Often used for business intelligence (BI) purposes XBRL databases Contains XBRL data and metadata used for creating XBRL reports. © Copyright 2012 Pearson Education. All Rights Reserved. RDBMS Relational database management system (RDBMS) software Software used to create, manage, maintain, and manipulate a relational database. Software that interacts with accounting software to insert, update, and delete data from the database. All database programs (including MS Access, MS SQL Server, and Oracle) use RDBMS software. © Copyright 2012 Pearson Education. All Rights Reserved. Databases Essentials Revisited Database essentials an accountant needs to know include: Field: pieces of information about events and objects. Records: collection of related fields. Table: a collection of related records with a unique table name. Database: a collection of related tables with a unique database name. © Copyright 2012 Pearson Education. All Rights Reserved. Database Integrity Database integrity refers to data being accurate and complete. Four rules helps to ensure database integrity: Rule 1: Entity Integrity. Each record in the database must have a unique identifier called a primary key. No two records in the database table can have the same primary key value. Rule 2. Primary Key Integrity. The primary key value cannot be null (empty). Each record must have a value for the primary key field. Rule 3. Domain Integrity. The field values must be from a predefined domain. For example, the Date field must have a date and not any other values (Validation Rule). Rule 4. Referential Integrity. Data referenced and stored in related tables must be consistent across the database. Referential integrity is improved when the customer address, for example, is stored in one location only, eliminating the possibility of the address differing from table to table. © Copyright 2012 Pearson Education. All Rights Reserved. Database Anomalies Database anomalies are problems within a database table that result in inaccurate or incomplete data. A well-designed database is free of deletion, update, and insertion (DUI) anomalies and are removed through a process called normalization. © Copyright 2012 Pearson Education. All Rights Reserved. Database Anomalies Three types of anomalies shown in Figure 14.2 are: Deletion problem. Deleting Vincent as a customer will cause the deletion of four records. These four records hold vital information about four orders and four items. Update problem. To update Vincent’s customer address, four records would need to be updated. Insertion problem. To identify a unique record, we have to use the customer number, order number, and item number. That means these three fields together form the primary key. If we want to insert a new customer who hasn’t placed an order yet, then the order number and item number fields would be empty. (This violates database integrity Rule No 2: The Primary Key Value Cannot Be Null.) © Copyright 2012 Pearson Education. All Rights Reserved. Database Modeling Database modeling, also called data modeling, is a technique used to develop an anomaly-free database with database integrity. When designing a database, you can think of it as three different realms: Database designer realm Database builder realm Database user realm © Copyright 2012 Pearson Education. All Rights Reserved. © Copyright 2012 Pearson Education. All Rights Reserved. What is ERD (Entity Relationship Diagram)? Entity relationship diagram (ERD) depicts the relationship among entities in the database. The ERD shows: Entities: Customer and Sales Order Relationship between entities: customer places an order Relationship cardinality:one-to-one, one-to-many, or manyto-many. © Copyright 2012 Pearson Education. All Rights Reserved. Database Entities © Copyright 2012 Pearson Education. All Rights Reserved. Entity Relationships An entity relationship diagram shows the relationships among entities. Three different types of relationships among entities are: One-to-one relationships One-to-many relationships Many-to-many relationships © Copyright 2012 Pearson Education. All Rights Reserved. Entity Relationships For a single occurrence of one entity (one customer), cardinality defines the maximum and minimum number of occurrences that might relate to the other entity (sales orders). The name given to the relationship (one-to-one, one-to-many, and many-to-many) refers to the maximum cardinalities (Steps 1 and 3 in Figure 14.8). © Copyright 2012 Pearson Education. All Rights Reserved. Entity Relationships © Copyright 2012 Pearson Education. All Rights Reserved. How To Build an ERD Step 1: Determine the relevant business transactions that the organization must support. Step 2: Determine the business rules for a specific business transaction. Step 3: Determine business entities and the relationship among them. Step 4: Determine the entity identifier(s) for each entity. Step 5: Draw entity relationship diagram. Step 6: Optimize the entity relationship diagram by removing many-tomany relationships. Step 7: Add business-related attributes to the entity relationship diagram. Step 8: Implement relationships. Step 9: Develop relational database tables. Step 10: Build relational database tables. © Copyright 2012 Pearson Education. All Rights Reserved. ERD Relationship Cardinality Symbols © Copyright 2012 Pearson Education. All Rights Reserved. One-to-Many Relationship Possibilities © Copyright 2012 Pearson Education. All Rights Reserved. ERD Using Intersection Table to Remove Many-to-Many Relationships © Copyright 2012 Pearson Education. All Rights Reserved. EspressoCoffee Sales Transaction ERD with Foreign Keys © Copyright 2012 Pearson Education. All Rights Reserved. Conceptual Database Tables © Copyright 2012 Pearson Education. All Rights Reserved. Relational Database Design and Development © Copyright 2012 Pearson Education. All Rights Reserved. How is SQL Used in Database Queries? Structured query language (SQL) is a 4th generation, nonprocedural language designed to define and manipulate relational database objects. SQL can query many records from one or more tables with a single statement. SQL statements, also called commands, are divided into two distinct categories: Data definition language (DDL) Data manipulation language (DML) © Copyright 2012 Pearson Education. All Rights Reserved. DDL Statements DDL statements create, alter, and drop database objects. Examples: CREATE statement is used to create relational database objects such as creating a database or a table. ALTER statement is used to change the relational database object such as adding a new field to a specific table. DROP statement is used to delete a database object such as deleting a table or removing an existing field from a specific table. © Copyright 2012 Pearson Education. All Rights Reserved. DML Statements DML statements are used to manipulate data within a relational database. DML statements select, insert, update, and delete database records. The SELECT statement is the primary one used to create database queries. © Copyright 2012 Pearson Education. All Rights Reserved.