ACC306 Ch4 Summary

Chapter Four: Relational Databases

Learning Objectives

  • Objective 1: Explain the importance and advantages of databases as well as the differences between database systems and file-based legacy systems.

  • Objective 2: Explain database systems, including:

    • Logical and physical views

    • Schemas

    • The data dictionary

    • Database management system (DBMS) languages.

  • Objective 3: Describe what a relational database is, how it organizes data, and how to create a set of well-structured relational database tables. Query a relational database using visual methods as well as using structured query language (SQL).

Integrative Case: SNS

  • Company Overview: SNS is a thriving company operating five stores and a successful website.

  • AIS Upgrade: Ashton Fleming believes it's time to upgrade the company's Accounting Information System (AIS) to provide easier access to data for owners, Sylvia and Sundip.

    • Recommendation: A new AIS based on a relational database.

  • Report Prepared by Ashton: Ashton prepared a report for Sylvia and Sundip that addresses:

    • What is a database system and how it differs from file-oriented systems?

    • What is a relational database?

    • How to design a well-structured set of tables in a relational database?

    • How to query a relational database?

Introduction

  • Modern AIS: Most modern AIS are built on relational databases.

  • Chapter Guidance: Chapters 19 through 21 provide guidance on designing and implementing databases.

  • Focus of the Chapter: Define a database, emphasizing the relational database structure and information extraction.

Topics Covered in Subsequent Chapters

  1. Chapter 19: Entity Relationship Diagramming and REA Data Modeling Tools

  2. Chapter 20: Implementation of REA Data Modeling

  3. Chapter 21: Advanced Data Modeling and Database Design Issues

  4. Chapters 5-7: Explore data analytics with relational databases to facilitate business decision-making.

Databases and Files

  • Data Storage in Accounting Systems: Accounting systems must store data about:

    • Assets, liabilities, equity

    • Revenue and expenses

    • Management, audit, and tax information (budgets, transactions, overhead, access logs, etc.)

  • Historical Context: Previous separate systems led to redundancy and fragmented data management.

    • Example: Bank of America had 36 million customer accounts in 23 separate systems, highlighting issues with file-oriented systems.

  • Impact of Redundant Data:

    • Difficulties in data integration and updating.

    • Example problem: A customer’s address updated in the shipping but not in the billing file.

  • Database Creation Rationale: Address problems of redundancy and establish a centralized database for easier data management and reduced duplication.

Elements of Database Structure

  • Entities and Records:

    • The database consists of entities (such as customers, sales, inventory) stored as records.

    • Each record consists of fields representing attributes (e.g., name, address).

Definition of Database

  • A database is a well-organized data collection that minimizes redundancy and consolidates data for diverse user needs.

  • Data is a shared resource managed and utilized across departments, as opposed to siloed systems.

Database Management System (DBMS)

  • Definition: Software program that manages data and communication between the database and application programs accessing that data.

  • Components of a Database System:

    • Database

    • DBMS

    • Application Programs

  • Database Administrator (DBA): Oversees and manages the database.

Data Warehousing for Data Analytics

  • OLTP Databases: Used for normal business transactions, often processing over one million transactions per minute.

  • Data Warehouses: Large databases for analytics that store detailed and summarized data beyond typical transactional processing.

    • Size: Can contain hundreds or thousands of terabytes, sometimes petabytes (1 petabyte = 1,000 terabytes).

    • Updates: Support strategic decision-making through periodic data updates rather than real-time.

  • Advantages of Data Warehouses:

    • Complement OLTP databases by maximizing query efficiency, providing support for complex analytics.

Data Analytics and Business Intelligence

  • Definition of Data Analytics: Analyzing large datasets to facilitate decision-making.

  • Key Techniques:

    • Online Analytical Processing (OLAP): Querying to examine hypothesized relationships.

    • Data Mining: Employing statistical analysis to discover unknown relationships in the data (e.g., identifying fraud).

  • Data Validation Controls: Essential to verify data accuracy when entered into the data warehouse.

  • Security Measures: Include access control and data encryption to protect the data warehouse.

  • Case Study: Bank of America's data warehouse provided significant insights with a fast query execution time.

Advantages of Database Systems

  • Universal Use: Databases are utilized in various environments including mainframes, cloud, server, and personal computers.

  • Key Benefits:

    • Data Integration: Combines separate application files into accessible, comprehensive datasets.

    • Data Sharing: Centralized storage facilitates easier information access.

    • Data Independence: Allows changes to data and programs independently.

    • Cross-Functional Analysis: Enables relationships to be defined for managerial reporting.

Importance of Accurate Data

  • Consequences of Inaccurate Data: Can result in poor decision-making and financial losses.

  • Examples of Issues:

    • Incorrect customer addresses leading to substantial financial waste.

    • Valparaiso case leading to severe revenue shortfalls due to data entry errors.

  • Cost of Poor Data: IBM estimates that it costs the US economy over $3 trillion annually, with widespread distrust in data among business leaders.

Managing Increasing Data Complexity

  • Trends: Data volume doubles every 18 months.

  • Compliance: Sarbanes-Oxley (SOX) mandates that executives ensure the integrity of financial data.

Database System Components

Logical and Physical Views of Data

  • Best Practices: The database approach separates data storage from data usage for flexibility and efficiency.

  • Data Elements: Must be organized in schemas to aid various user understandings.

Schemas

  • Definition: Description of data elements, relationships, and logical models.

  • Types of Schemas:

    • External Level Schema: User-specific views of data, tailored for specific user needs.

    • Conceptual Level Schema: Organization-wide overview listing all data elements and relationships, managed by the DBA.

    • Internal Level Schema: Low-level view describing data storage access details.

The Data Dictionary

  • Contains crucial information about structure, relations, access rights, and specifications for each data element.

DBMS Languages

  • Different languages govern DBMS operations:

    • Data Definition Language (DDL): Builds the data dictionary and specifies constraints (e.g., CREATE, DROP, ALTER).

    • Data Manipulation Language (DML): Modifies database content (e.g., INSERT, UPDATE, DELETE).

    • Data Query Language (DQL): Facilitates data retrieval in an understandable format through commands such as SELECT.

Relational Databases

  • Definition: Composed of two-dimensional tables (relations) where each row (tuple) holds unique data and each column holds attributes.

  • Primary Key: Uniquely identifies a row in a table (e.g., Item ID).

  • Foreign Key: Links records between tables by referencing primary keys.

Designing a Relational Database for SNS

  • Methodology: Illustrate sales information capture using a structured approach with well-defined tables.

  • Issues with Simple Tables: Discusses redundancy errors, update anomalies, insert anomalies, and delete anomalies.

Creating a Set of Related Tables

  • Solution: Implement a relational database minimizing redundancy and preventing anomalies.

  • Guidelines for Structured Design:

    • Each column must have a single value.

    • Primary keys cannot be null.

    • Foreign keys must reference existing primary keys in linked tables.

    • Non-key attributes must relate to the primary key.

Approaches to Database Design

  1. Normalization: Reduces redundancy and anomalies; creates third normal form (3NF).

  2. Semantic Data Modeling: Combines business process knowledge with database structure for effective design.

Creating Relational Database Queries

  • Query Definition: A structured request for database information.

  • SQL Basics: Language standard for relational DBMS; enables effective data extraction.

SQL Example Queries in MS Access**

  • Query Creation Process: Steps to create and run queries using design view and SQL view.

  • Example Queries: Detail the structure and execution of specific data requests highlighting different SQL features, including query filtering, aggregation, and associations.

  • Key Commands: SELECT, FROM, INNER JOIN, WHERE, GROUP BY, ORDER BY, HAVING.

Database Systems and Future of Accounting

  • Discuss expansion of capacities for dynamic reporting.

  • Emphasize the significance of data harnessing for improved decision-making.

  • Highlight the role of accountants in designing and implementing database systems for effective controls and reliable information production.

Summary and Case Conclusion

  • Overview of Ashton’s Report: Explained aspects of DBMS and its foundational role based on logical models showcasing relational structures.

  • Decision Outcome: Sylvia and Sundip agreed upon upgrading the SNS database, tasking Ashton with oversight to ensure the new system meets requirements and that employees receive necessary training to utilize the database effectively.