Acct 316 Acct 316 Chapter 4 Relational Databases

Acct 316 Acct 316 Chapter 4 Relational Databases Acct 316 UAA – ACCT 316 Accounting Information Systems Dr. Fred Barbee

Acct 316 Acct 316 Acct 316 Creating a database can be like creating a universe, only more complicated. At least when the universe was created, there was no one around to complain. Michael J. Hernandez Database Design for Mere Mortals Addison Wesley, Boston, 1997

Acct 316 Acct 316 An Out-of-the-Text Experience! Acct 316

Acct 316 Acct 316 Chapter Acct 316 Types of Databases 4

Types of Databases Operational Database Acct 316 Used whenever there is a need to collect, maintain, and modify data. Stores dynamic data (i. e. , the data changes constantly and reflects upto-the-minute information).

Types of Databases Analytical Database Acct 316 Used to store and track historical and time-dependent data. . Stores static data that is never (or rarely) modified. Information is applicable to a specific point in time.

Acct 316 Acct 316 Chapter Acct 316 Early Database Models 4

Hierarchical Database Model Data is structured hierarchically. Acct 316 Visualize as an inverted tree. A single table acts as the “root” of the tree. Other tables act as branches flowing from the root.

Hierarchical Database Model Agents Entertainers Schedule Clients Engagements Payments

Network Database Model Visualize as an inverted tree. Acct 316 Several inverted trees may share branches, All of which are part of the same database structure.

The Network Database Model Agents Represent Manage Clients Make Payments Entertainers Schedule Perform Engagements Play Musical Styles

A Basic Set Structure Owner Table Represent Relationship Agents Set Structure Clients Member Table

bases tab Da al ion Data lat ional Acct 316 Re Relational Database Relat as es Enter. . . The Relational Database Model

Relational Database Model Acct 316 Data is stored in relations, which are perceived by the user as tables. Each relation is composed of tuples (records) and attributes (fields).

Fundamental Data Storage Concepts and Definitions Accounts Receivable File Attributes Customer Credit number name Address limit Balance 301 ABC Co. Box 5 1, 000 400 555 XYZ Co. Box 9 6, 000 2 Entities 2 Records Individual fields Data values

Relational Database Model Acct 316 The physical order of the records of fields in a table is completely immaterial. Each record in the table is identified by a field that contains a unique value.

A Relational Database Model Agents Clients Entertainers Engagements Musical Styles Entertainer Styles

Agents Agent ID First Name Last Name 100 Mike Hernandez 05/16/02 333 -4444 101 Greg Piercy 10/15/02 444 -3333 Clients Client ID Agent ID Phone First Name Last Name …. . 9001 100 Stewart Jamison …. . 9002 101 Shannon Mc. Clain …. . Entertainers Ent ID Hire Date Agent ID First Name Last Name …. . 3000 100 John Slade …. . 3001 101 Mark Weiser …. . Engagements Client ID Eng. Date Time …. . 9001 3001 04/01/02 1 -3 PM …. . 9002 3002 04/12/02 7 -9 PM …. .

Acct 316 Acct 316 Now, Back to the Text Acct 316

Acct 316 Acct 316 Acct 316 Double-Entry Vs. Database Accounting

Double-Entry Versus Database Accounting Luca Pacioli and double-entry accounting. Acct 316 Captures and stores key attributes of transactions in a highly aggregated form.

Double-Entry Versus Database Accounting Acct 316 The beloved debit/credit convention provided an important internal control feature in manual systems. Enter computerized accounting.

Double-Entry Versus Database Accounting The ability to quickly capture lots of data quickly. . . Acct 316 Date and time of purchase Identity of item purchased Store location Checkout station number Cashier number

Double-Entry Versus Database Accounting Consider a simple transaction: Acct 316 On November 18, 2002 we made a sale to Jones Enterprises for $10, 000 on credit. Terms 2/10, n/30

Acct 316 Acct 316 Acct 316 How would we handle this transaction in a double-entry system?

Acct 316 Acct 316 Chapter Acct 316 Files Vs. Databases 4

Fundamental Data Storage Concepts and Definitions Accounts Receivable File Attributes Customer Credit number name Address limit Balance 301 ABC Co. Box 5 1, 000 400 555 XYZ Co. Box 9 6, 000 2 Entities 2 Records Individual fields Data values

Basic File Types Acct 316 The master file, which is conceptually similar to a ledger in a manual system. The transaction file, which is conceptually similar to a journal in a manual system.

File-Oriented Approach File # 1 Item A Item B Item C Application program #1 File # 2 Item B Item D Item E Application program #2

Acct 316 Acct 316 Acct 316 How would we handle this transaction in a relational database system.

Databases Acct 316 The database approach views data as an organizational resource that should be. . . used by, and managed for, the entire organization

Databases A database is a set of interrelated, centrally coordinated files. Acct 316 Focus is on Data integration Data sharing

Database Approach Application program #1 Database Item A Item B Item C Item D Item E Database management system Application program #2 Application program #3

Database System Database Item A Item B Item C Item D Item E DBMS refers to programs that control the data and interfaces Application between the data and the program #1 application programs. Database management system Application program #2 Application The Data Base Administrator (DBA) is responsibleprogram for the #3 database.

A Database Accounting System

Acct 316 Acct 316 Acct 316 Advantages & Disadvantages of Relational Database Systems

Advantages Reduce data storage costs Acct 316 Eliminate data redundancy Eliminate data inconsistencies Avoid duplicate processing

Advantages Ease add, delete, and update data maintenance tasks Acct 316 Make data independent of applications Centralize data management Centralize data security

Advantages Ease report modifications and updats Acct 316 Provide ad hoc query capabilities Facilitate cross-functional data analysis

Advantages Permit multiple users simultaneous data access. Acct 316

Disadvantages Greater hardware requirements Acct 316 The database software itself Employing a database administrator.

Disadvantages Centralization of data. . . Acct 316 System operation becomes critical Incorrect data entry corrupts many users’ work Territorial disputes over data ownership may arise

One Last Disadvantage Acct 316

Acct 316 Acct 316 It’s Your Turn! Acct 316 Discussion Question 4. 3 p. 99

The text explained how database technology may eliminate the need for double-entry accounting. This creates three possibilities: The double-entry model will be abandoned, The double-entry model will not be used directly, but an external-level schema based on the doubleentry model will be defined for accountant’s use; or The double entry model will be retained in database systems. Which alternative do you think is most likely to occur? Why?

Acct 316 Acct 316 Logical View Acct 316 Vs. Physical View

Physical View Acct 316

Physical View Acct 316 The physical view refers to how and where the data are physically arranged and stored on disk, tape, CD-ROM, or other media.

Logical View Acct 316 The logical view is how the user or programmer conceptually organizes and understands the data.

Logical View Vs. Physical View Logical View Data Physical View Credit Report Customer Number Credit Limit Balance Monthly Statement Customer Name Address Balance Customer Number Customer Name Address Credit Limit Balance How data are stored on disk.

Acct 316 Acct 316 Chapter Acct 316 Schemas 4

Schemas Acct 316 A schema describes the logical structure of a database. There are three levels: Conceptual-Level External-Level Internal-level

Conceptual-Level Schema An organization-wide view of the entire database.

External-Level Schema Consists of a set of individual user views of portions of the database, also referred to as a subschema.

Internal-Level Schema The internal-level schema provides a low-level view of the database.

Database Management System

Acct 316 Acct 316 Chapter Acct 316 DBMS Languages 4

DBMS Languages Acct 316 Every DBMS must provide a means of performing the three basic functions: Creating the database Changing the database Querying the database

Data Definition Language (DDL) Build the data dictionary. Initialize or create the database. Acct 316 Describe the logical views for each individual user or programmer. Specify any limitations or constraints on security imposed on database record or fields.

Data Manipulation Language (DML) Used for data maintenance. Acct 316 updating portions of the database inserting portions of the database deleting portions of the database

Data Query Language (DQL) The data query language (DQL) is used to interrogate the database. Acct 316 The DQL retrieves, sorts, orders, and presents subsets of the database in response to user queries.

Acct 316 Acct 316 Acct 316 More on the Relational Data Model Chapter 4

Table in a Relational Data Model

Basic Requirements of the Relational Data Model Primary keys must be unique. Acct 316 The primary key cannot be null (blank). Why? Known as the entity integrity rule.

What is the entity integrity rule? Acct 316 The entity integrity rule ensures that every row in every relation (table) must represent data about some specific object in the real world.

Basic Requirements of the Relational Data Model Acct 316 Every foreign key must either be null or have a value corresponding to the value of a primary key in another relation. Why? The referential integrity rule.

What is the referential integrity rule? Acct 316 The referential integrity rule ensures that links exist among the tables of a database.

Basic Relational Data Model Stuff Relation = Table = File Some terminology

Table in a Relational Data Model Tuple = Row = Record Some terminology

Table in a Relational Data Model Attribute = Column = Field Some terminology

Attribute = Column = Field Each column in a table must describe a characteristic of the object identified by the primary key.

Attribute = Column = Field Each attribute (column) in a table has a distinct name, called the attribute name.

Attribute = Column = Field Every entry in a column contains a value for that column only, and the values are of the same data type.

Attribute = Column = Field The order (position) of the columns in relation to each other is unimportant.

Tuple = Row = Record The entries in each column of any row must be single valued.

Tuple = Row = Record The order of the rows is unimportant.

Tuple = Row = Record Each row is unique; it differs from all other rows in the table.

Acct 316 Acct 316 Acct 316 How does a database differ from a spreadsheet?

Acct 316 Acct 316 Acct 316 How might an accountant’s familiarity with the tabular representation of spreadsheets facilitate or hinder learning how to use a relational DBMS?

Acct 316 Acct 316 Acct 316 Importance of a Well. Structured Relational Database Chapter 4

Acct 316 Acct 316 Acct 316 Anomalies that may occur in nonnormalized relational tables.

Acct 316 Acct 316 Acct 316 Normalization! Anomalies! WOW! You accountants are an odd lot! What is normalization? Is anything normal around here?

Normalization Acct 316 A technique used to make complex databases more efficient by removing undesirable redundancy. Break one large table into several smaller tables.

Normalization starts with the assumption that everything is initially stored in one large table. A set of rules is then followed to decompose that initial table into a set of normalized tables. The objective is to produce a set of tables that are in what is called third normal form (3 NF)

Normalization Problems caused by redundancy: Acct 316 Storage inefficiencies Processing inefficiencies Errors

Update Anomaly When changes (updates) to data values are not correctly recorded. Acct 316

Insert Anomaly Acct 316 There is no way to store information about one entity in the database without it being associated with another entity

Delete Anomaly Acct 316 Unintended results arising from deleting a row of data pertaining to one entity and resulting in the deletion of data regarding another entity as well.

Acct 316 Acct 316 Database Design Acct 316 Chapter 4

Approaches to Database Design Normalization Acct 316 Starts with the assumption that all data is initially stored in a large nonnormalized table. This table is then decomposed using a set of normalization rules to create a set of tables in the Third Normal Form.

Approaches to Database Design Semantic Data Modeling Acct 316 The database designer uses his/her knowledge about the business structure to create a set of relational tables.

Database Design Objectives Completeness Acct 316 Relevance Accessibility Up-to-dateness flexibility

Database Design Objectives Efficiency Acct 316 Cost-effectiveness Integrity Security

Database Systems and the Future of Accounting Acct 316 Database systems have the potential to significantly alter the nature of external reporting.

Database Systems and the Future of Accounting Acct 316 Perhaps the most significant effect of database systems will be in the way that accounting information is used in decision making.