Transcript Chapter_02
ACCOUNTING INFORMATION SYSTEMS The Crossroads of Accounting & IT Chapter 2 Accounting Databases © Copyright 2012 Pearson Education. All Rights Reserved. Enterprise Databases Enterprises databases include: Operational Databases for operating the business, such as accounting transactions (OLTP) Data warehouses for storing and analyzing data for business intelligence (OLAP) © Copyright 2012 Pearson Education. All Rights Reserved. Enterprise Databases External databases: typically accessed over the Internet owned by other organizations, such as SEC database © Copyright 2012 Pearson Education. All Rights Reserved. Database Administrator (DBA) Database Administrator (DBA) is responsible for managing the enterprise’s databases. The DBA is responsible for database security, access, and disaster recovery. © Copyright 2012 Pearson Education. All Rights Reserved. © Copyright 2012 Pearson Education. All Rights Reserved. © Copyright 2012 Pearson Education. All Rights Reserved. Accounting System Architecture: 3 Tiers © Copyright 2012 Pearson Education. All Rights Reserved. Database Essentials: Field/Record/Table/Database Field: a piece of data Record: a collection of related fields Table: a collection of related records Database: a collection of related tables © Copyright 2012 Pearson Education. All Rights Reserved. Database Essentials: Forms/Queries/Reports Database forms: input data Queries: search and extract data Database reports: output of database queries © Copyright 2012 Pearson Education. All Rights Reserved. Databases: Divide & Conquer Abbreviated steps to build an accounting database for EspressoCoffee: 1.Identify and build database tables. 2.Identify and enter fields in each database table. 3.Select the primary key for each table. 4.Identify and build relationships among database tables. © Copyright 2012 Pearson Education. All Rights Reserved. Step 1: Identify and Build Database Tables Databases tables can consists of data about: People (Customers) Things (Inventory Items) Transactions (Sales Transactions) © Copyright 2012 Pearson Education. All Rights Reserved. Step 1: Identify and Build Database Tables Databases tables can consists of data about: People (Customers) Things (Inventory Items) Transactions (Sales Transactions) © Copyright 2012 Pearson Education. All Rights Reserved. Step 2: Identify and Enter Fields in each Database Table Fields for three database tables for EspressoCoffee: © Copyright 2012 Pearson Education. All Rights Reserved. Step 3: Select the Primary Key © Copyright 2012 Pearson Education. All Rights Reserved. Step 4: Identify and Build Relationships between Tables Three types of database table relationships: One-to-One Relationships One-to-Many Relationships Many-to-Many Relationships © Copyright 2012 Pearson Education. All Rights Reserved. Step 4: One-to-One Relationships For each one record in one database table, there is one record in the related table. © Copyright 2012 Pearson Education. All Rights Reserved. Step 4: One-to-Many Relationships For each one record in one database table, there are many records in the related table. © Copyright 2012 Pearson Education. All Rights Reserved. Identify Relationships Example What is the maximum number of orders a customer can place? Many © Copyright 2012 Pearson Education. All Rights Reserved. Identify Relationships Example Second, what is the maximum number of customers who can place a specific order? 1 © Copyright 2012 Pearson Education. All Rights Reserved. Step 4: Many-to-Many Relationships Many-to-many relationships exist when there may be many records in one table that relate to many records in the related table. One sales order can contain many items. A specific item can appear on many sales orders. © Copyright 2012 Pearson Education. All Rights Reserved. Step 4: Many-to-Many Relationships All many-to-many relationships must be removed in a relational database. Intersection tables, a special type of database table, is used to remove many-to-many relationships. © Copyright 2012 Pearson Education. All Rights Reserved. Step 4: Many-to-Many Relationships Use Intersection tables to remove many-to-many relationships. 1. Create an intersection table at the intersection of the many-to-many relationship. 2. Create two new one-tomany relationships to connect the intersection table. 3.Create a composite primary key for the intersection table using the primary keys of the two tables involved. © Copyright 2012 Pearson Education. All Rights Reserved. Database Tables for Recording Sales Transactions The Sales Order Line table (Intersection table) eliminated the many-to-many relationship. The Primary Key (Customer No.) of the Customer table is the Foreign Key in the Sales Order table, connecting the two tables. © Copyright 2012 Pearson Education. All Rights Reserved. Database Integrity Database Integrity refers to the database containing accurate, valid data. Rule 1. Entity Integrity. Each record in the database must have a unique identifier (i.e., a unique 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. Rule 4. Referential Integrity. Data referenced and stored in related tables must be consistent across the database. For example, a customer address should be the same in any table in which it is referenced. Referential integrity is improved when the customer address is stored in one location only. © Copyright 2012 Pearson Education. All Rights Reserved. Database Anomalies Database anomalies are three types of problems in database tables: 1.Deletion Problem. Deleting one record (customer) causes deletion of other records. 2.Update Problem. Updating one field (address) requires updating multiple fields. 3.Insertion Problem. Inserting a new field (customer) is problematic because the primary key is null. © Copyright 2012 Pearson Education. All Rights Reserved. Anomaly-Free Databases A well-designed database is free of Deletion, Update, and Insertion (DUI) anomalies. Database anomalies are removed through a process called normalization and/or functional decomposition. © Copyright 2012 Pearson Education. All Rights Reserved. Database vs Manual Accounting Systems © Copyright 2012 Pearson Education. All Rights Reserved. EXERCISE Complete the following: 1.Using Microsoft Access, create a Customer table for EspressoCoffee, identifying the appropriate fields. 2.Identify the primary key for the Customer table. 3.Populate the Customer table by entering test data for three customers. © Copyright 2012 Pearson Education. All Rights Reserved. EXERCISE Complete the following: 1.In the same database, create an Item table for EspressoCoffee, identifying the appropriate fields. 2.Identify the primary key for the Item table. 3.Populate the Item table by entering test data for three Items. 4.Identify the table relationship between the Customer table and the Item table, adding the appropriate foreign keys to the table(s). © Copyright 2012 Pearson Education. All Rights Reserved. EXERCISE Complete the following: 1.In the same database, create an Sales Order table for EspressoCoffee, identifying the primary key field and populating the table with 3 records of test data. 2.Identify the table relationships between the Customer, Sales Order and Item tables, adding the appropriate foreign keys to the table(s). © Copyright 2012 Pearson Education. All Rights Reserved. EXERCISE Complete the following: 1.In the same database, create an Sales Order Line table for EspressoCoffee, identifying the primary key field and populating the table with 3 records of test data. 2.Identify the table relationships between the Customer, Sales Order, Sales Order Line and Item tables, adding the appropriate foreign keys to the table(s). 3.Create the relationships between these four tables in your database. © Copyright 2012 Pearson Education. All Rights Reserved.