ACCY225: Data Management

Data Hierarchy

The structure of data consisting of a Database (the common pool) containing Tables/Files, which contain Records (rows), which contain Fields (columns).

File-Based Approach

A traditional system where each department maintains its own separate master file, causing data silos, massive redundancy, and inconsistency.

Database Approach

A centrally coordinated set of interrelated tables with minimal redundancy that serves all programs via a DBMS.

Data Independence

The separation of data storage (physical) from application programs (logical), allowing one to change without affecting the other.

OLTP (Online Transaction Processing)

The primary day-to-day operational database used to process current business transactions; optimized for speed and throughput.

Data Warehouse (OLAP)

An extremely large, purposely redundant database containing multi-year data from numerous sources, used purely for analysis, not daily operations.

Data Mining

Using statistical analysis and AI (e.g., neural networks) to discover unknown or unhypothesized relationships in large data sets (like fraud patterns).

Data Scrubbing

The process of verifying data accuracy before loading it into a data warehouse; it is the most expensive and time-consuming step in business intelligence.

Logical View

How users conceptually organize and understand the data (e.g., a sales manager viewing all customer info in one single table).

Physical View

How data is actually stored on the computer hardware, such as memory addresses and disk sectors (managed by the Database Administrator).

External Level (Subschemas)

An individual user's logical view of their specific portion of the database; where access rights (Create, Read, Update, Delete) are defined.

Conceptual Level (The Schema)

The organization-wide view and blueprint of the entire database, listing all data elements and their relationships.

Internal Level (Physical Schema)

The low-level view describing how data is physically stored, including record layouts and indexes.

Data Dictionary

Maintained by the DBMS, it stores structural information about every data element (e.g., description, length, authorized users).

DDL (Data Definition Language)

A restricted language used by admins to build the data dictionary, create the database, describe logical views, and specify security constraints.

DML (Data Manipulation Language)

A language used by admins and programmers to change database content (insert, update, delete).

DQL (Data Query Language / SQL)

An English-like language available to all users to retrieve, sort, and display data (e.g., the SELECT command).

Relational Data Model

Represents data as if it were stored in two-dimensional tables, where each table represents one single entity.

Primary Key (PK)

An attribute (or combination of attributes) that uniquely identifies each row in a table.

Entity Integrity Rule

The relational database rule stating that a primary key cannot be null.

Foreign Key (FK)

An attribute in one table that is a primary key in another table, used to link the two tables together.

Referential Integrity Rule

The relational database rule stating that foreign key values must match an existing primary key value in the related table, or be null.

Update Anomaly

Occurs in a single-table database when duplicating data means updating it requires changing every matching row; missing even one creates an inconsistency.

Insert Anomaly

Occurs when you cannot add a new item to the database until a specific event occurs because the primary key cannot be null.

Delete Anomaly

Occurs when deleting a transaction row inadvertently deletes all information about an entity (like a customer) if it was their only transaction.

Normalization

The process of solving anomalies by splitting data into related tables (usually Third Normal Form / 3NF).

REA Data Model

An entity-relationship modeling framework specifically for AIS database design, focusing on Resources, Events, and Agents.

REA Resources

Things with economic value to the organization (e.g., inventory, cash, equipment).

REA Events

Business activities management wants to track for planning or control (e.g., sales, receiving cash).

REA Agents

People or organizations participating in events (e.g., customers, employees, vendors).

REA Rules

Every event must link to: at least 1 resource, at least 1 other event (economic duality), and at least 2 agents (one internal and one external).

Cardinality

The minimum and maximum instances of one entity that can be linked to another (e.g., 1:1, 1:N, M:N); must reflect actual business policies.

Participation Relationship

In REA modeling, a relationship that links an Agent to an Event (e.g., Employee -> Sale).

Stock-Flow Relationship

In REA modeling, a relationship that links an Event to a Resource, showing increases or decreases (e.g., Sale -> Inventory).

Fulfilment Relationship

In REA modeling, a relationship that links a Commitment event to an Economic event (e.g., Customer Order -> Sale).

Duality Relationship

In REA modeling, a relationship that links two Economic Events, showing a resource given up in exchange for another (e.g., Sale -> Receive Cash).

Associative Table

A table used to resolve a Many-to-Many (M:N) relationship in a relational database (e.g., a Sales_Line table linking Sales and Inventory).

Internal Controls

Systematic processes, policies, and procedures implemented to safeguard assets, maintain accurate data, and ensure compliance.

Preventive Controls

Controls that deter problems before they arise (e.g., passwords, separation of duties, DLP software, encryption).

Detective Controls

Controls designed to discover problems that were not successfully prevented (e.g., bank reconciliations, audit trails, digital watermarks).

Corrective Controls

Controls that identify and correct problems and recover from resulting errors (e.g., automated system backups, disaster recovery plans).

General vs. Application Controls

General controls apply across the entire IT infrastructure (e.g., server security), whereas application controls prevent/detect errors within specific software (e.g., data entry validation).