Accounting Information Systems: Essential Concepts and Applications Fourth Edition by Wilkinson, Cerullo, Raval, and Wong-On-Wing Chapter 6: Data-Base Modeling and Applications Slides Authored by Somnath Bhattacharya, Ph.D. Florida Atlantic University Introduction We use the term Data Base to mean the collected data sets that are organized and stored as an integral part of a firm’s computerbased information system Data Sets are flexible data structures that include groupings of data that are logically related The Database Approach to Data Storage A Database is a set of computer files that minimizes data redundancy and is accessed by one or more application programs for data processing The database approach to data storage applies whenever a database is established to serve two or more applications, organizational units, or types of users A Database Management System (DBMS) is a computer program that enables users to create, modify, and utilize database information efficiently Characteristics of the Database Approach Data Independence - the separation of the data from the various application programs and other accesses by users Data Standardization - data elements within a database have standard definitions, thus stored data are compatible with every application program that accesses the data One-Time Data Entry and Storage individual data values are entered into the database only once; consequently, redundancy is reduced and inconsistencies between data elements are eliminated Characteristics of the Database Approach Data Integration - data sets integrate the data, which enables all affected data sets to be updated simultaneously Shared Data Ownership - all data within a database are owned in common by the users. The portion of the database that is of interest to each user is known as the sub-schema Centralized Data Management - the database management system stands guard over the database and presents the logical view to users and application programs Program-Data Independence Application Program A Database Management System Application Program B Figure 6-1 Database Questions for Database Design and Construction What data management perspective should be adopted? What is the proposed system’s initial objective? What systems and users will use the data? Which existing or future systems will the proposed system interface with? How much data will be stored initially? In the future? How many data accesses (reads and updates) will occur on an hourly, daily, and monthly basis? How can the data be organized, both logically and physically, to best serve the users of the system? Iterative Phases in Database Development: Planning & Analysis Planning Cost-benefit Analysis Effective usage Analysis Analysis Enterprise Diagram User Requirements Data requirements Firm’s operations and relationships Development of logical design Expected output requirements Inputs Processes Appropriate Conceptual Model Data Modeling through Entity-Relationship Diagrams Specification of logical view(s) Designation of Primary and Secondary keys Development of Data Dictionary Iterative Phases in Database Development: Detailed Design Technical Specifications Report Layouts Data Flows Screen Layouts DBMS Selection Data Definition Language (DDL) Data Manipulation language (DML) Query language [Structured Query Language (SQL) and/or Query by Example (QBE)] Data-base Control System (DBCS) DBMS Many DBMS packages allow users to: Analyze Data Prepare ad hoc or customized Reports Create and Display Graphs Create Customized Applications via Programming Languages Import and Export Data Perform On-line Editing Purge or Archive Obsolete Data Backup data Maintain Security Measures Interface with Communication Networks Iterative Phases in Database Development: Post-Design Phases Implementation Testing Unit Testing System Testing User Acceptance Test Maintenance Entity-Relationship Model Relative to the detailed nature of Record layouts and data dictionaries, Entity-Relationship (E-R) Models provide a broader and more conceptual view of the firm’s data A Data Model documents the key entities in a firm and the relationships or associations among those entities An Entity is an object that exists and is identifiable. e.g., an agent, event, or a resource Entity-Relationship Model Conventions Rectangles represent entities and diamonds represent relationships. Each rectangle is usually denoted by the attributes of the entity. E-R Diagrams can easily model the information needs of the entire enterprise or segments of the enterprise such as divisions or departments, and even detailed data issues such as detailed data repositories such as records and/or tables. Database Relationships In a database, relationships occur among data elements for two reasons: Because of the nature of the elements themselves. e.g., the relationship between a customer no. and a customer name Because of the need to retrieve information from a database in some prescribed manner. e.g., customers and invoices. Kinds of Relationships No relationship. e.g., student numbers and physical plant codes One-to-one relationship. This occurs least frequently, e.g., reference no. to course description; product no. to product description; customer no. to customer name Directed relationships - 2 views: One-to-many relationship. e.g., advisor to students, customer to invoices Many-to-one relationship. e.g., invoice lines to invoice Many-to-many relationship. e.g., students to courses, customers to products A Many-to-Many Relationship: Variation of Figure 6-6 Customers April June Summers m Product A n Products Figure 6-6 Product B Product C Product D Relational Databases In a relational database, data are perceived by users to be structured in the form of simple flat files or tables Each table consists of records that are comprised of a key and associated data elements In order to lay claim as a relational database, it must do the following: Present data to users as tables only Support the relational algebra functions of Restrict (Select), Project, and Join without requiring any definitions of access paths to support these operations Relational Algebra Functions in a Relational Database - Select Select (Restrict): This function produces a new table with only rows from a single source table whose columns meet prescribed conditions, e.g., Customer_Name=Adam Smith; DOB=2/29/64; Legal Residence=California, etc Select C u st N o. C u st. D a te N am e of B irth C red it L eg a l L im it R es. 1000 A dam S m ith 3 -1 2 -6 2 1 0 0 0 CA 1010 L o rd 2 -2 9 -6 4 2 0 0 0 K ey n es TX Relational Algebra Functions in a Relational Database - Project This function produces a new table with only some columns from a single source table. e.g., Project Student table on Student_Name and Student_Major S tu d en t_ N a m e S tu d en t_ M a jo r E stu d ia n te G a rcia F ren ch M a d elein e N o ta llb rig h t In tern a tio n a l R ela tio n s Relational Algebra Functions in a Relational Database - Select & Project The combination of Select and Project produces a new table with both fewer columns and rows than the original table. e.g., Project on Student_Name and Student_Major where Student_Major = Latin Select & Project S tu d en t_ N a m e P en n y P a sta C o n n ie C u rry Tony Lam a S tu d en t_ M a jo r L a tin G reek T ib eta n S tu d en t_ N a m e P en n y P a sta S tu d en t_ M a jo r L a tin S tu d en t_ S ta tu s S en io r F resh m a n J u n io r Relational Algebra Functions in a Relational Database - Join The Join function produces a new table from two or more source tables that have at least one common column The new table is wider than either of the two source tables because it contains all the columns from both source tables Join C u stom er_ N am e Joh n D oe C u stom er_N am e C u stom er_C od e Joh n D oe 1001 C u stom er_C od e C red it_L im it 1001 10,000 C u stom er_ C od e 1001 + C red it_L im it 10,000 = This may get repeated twice, but then will be eliminated under the third normal form. Query Languages for a Relational Database Structured Query Language (SQL) SELECT CLIENT_NO, CLIENT_NAME, PROJECT_NAME FROM PROJ.TABL WHERE CLIENT_NO = 531 Query-by-Example (QBE) Use of Dynasets Relational Databases: Advantages & Disadvantages Advantages Ease of use for nontechnical users Flexible structure English-like commands or menus Easy structural changes Disadvantages Relative inefficiency Huge storage space required More redundancy than other data base structures Not suitable for highvolume applications Hierarchical Database Structure The hierarchical data structure (or tree structure) expresses hierarchical relationships among stored data. The root node is at the top and for any two adjacent records, the elder or higher-level record is called the parent record. The younger or lower-level record is called the child record and any two records on the same level are called sibling records. The Hierarchical Conceptual Model Customer Salesperson Invoice Salesperson In this model all data deemed necessary must be defined when the database Invoice 1 Invoice 2 Invoice 3 is created The inverted tree structure Line Line of the database means that Item 1 Item 2 each node can only have one parent Therefore, the hierarchical Customer model only allows for oneto-one and one-to-many Invoice 1 Invoice 2 Invoice 3 relationships. Many-tomany relationships cannot be modeled except through Line Line duplication of data elements Item 1 Item 2 Hierarchical Conceptual Model - III R ec. A ddress R ec. C ontent Forw ard P ointer 1 2 3 C ust A C ust B Inv 10 4 6 0 Customer A 4 Inv 9 7 Invoice 9 Invoice 12 Invoice 16 5 Inv 16 0 Customer B 6 Inv 8 3 7 Inv 12 5 In this model, pointers must be stored either within the records or in separate index files Invoice 8 Invoice 10 The Use of Pointers in the Hierarchical Conceptual Model Rec. Address Rec. Content Forward Pointer 1 2 3 Cust A Cust B Inv 10 4 6 0 4 Inv 9 7 5 Inv 16 0 6 Inv 8 3 7 Inv 12 5 The Network Structure Like the Tree structure, the Network structure establishes explicit access paths or links among data nodes Unlike the Tree Structure, however, the Network structure: Allows any data node to be linked to any other node Permits entry at more points than a single root node Requires at least one subordinate data node to have two or more owner nodes Network Database Structure The network data structure handles complex relationships among records by linking related records together with “pointer fields” Pointer fields are embedded in each record and contain disk addresses of related records The pointers maintain the data relationships, thereby enabling an AIS to prepare familiar reports Network Conceptual Model - I In this model there is no distinct data hierarchy. This enables network models to handle all types of relationships Along with this ability, though, comes high inherent complexity Simple networks contain one-to-many relationships Complex networks contain many-to-many relationships. Usually these are reduced to numerous one-to-many relationships through intersection records Network Conceptual Model - II Pointers are also used to link data elements in network models. Micro-computer-based network models are uncommon. These are more common in large mainframe environments. True Network Model Decomposed to One-to-Many Relationships Customers Products Customer Product Cust-Prod Object-Oriented Database Structure - I The object-oriented database (OODB) is a new type of database that stores objects with (non-textual) information in them These unstructured objects may be graphic images, still photographs, animated visual, music and speeches Objects are grouped into object classes, with each member of the class having the same set of attributes, which can be manipulated Object-Oriented Database Structure - II Object Classes feature class hierarchies Super-classes are at the top of the hierarchies, with classes and sub-classes linked below Movement within class hierarchies is downward from super-class to class to subclass Classes may also form sidewise associations, e.g., association of university person with university; faculty with academic dept Characteristics of Objects Attributes Make Model Year Object Drive Operations Engine Size Mileage Car Park Lock Wash Color Characteristics of Objects Attributes Part No. Note that Objects possess 2 characteristics: attributes & operations Description Qty on Hand Object Reduce Operations Reorder Point Order Qty Supplier No. Inventory Review Qty Reorder Replace Sometimes these may change objects Object-Oriented Database Structure - III OODBs feature: Encapsulation: Storing procedures or operations called methods with the data to which the methods relate. This brings together the data attributes and operations pertaining to objects and object classes Because of encapsulation, the application programs that access the data base can be greatly simplified, thereby reducing programming errors Inheritance: This allows subclasses to inherit methods and/or data from higher classes within a class hierarchy The major advantage of inheritance is that programmed instructions (objects) are reusable. Libraries of commonly used objects (programs) can be maintained These standardized programs (fully pre-tested and applied) can greatly reduce reprogramming efforts Inheritance Between Classes CONTROL Object Class Operations: Verify key before Update * Other * operations * Object Subclasses ACCT-PAY ACCT-REC INVENTORY Attributes: Ven-Num (key) * Other * Attributes * Attributes: Cust-Num (key) * Other * Attributes * Attributes: Part-Num (key) * Other * Attributes * Accounting Information Systems: Essential Concepts and Applications Fourth Edition by Wilkinson, Cerullo, Raval, and Wong-On-Wing Copyright © 2000 John Wiley & Sons, Inc. All rights reserved. Reproduction or translation of this work beyond that permitted in Section 117 of the 1976 United States Copyright Act without the express written permission of the copyright owner is unlawful. Request for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for distribution or resale. The publisher assumes no responsibility for errors, omissions, or damages, caused by the use of these programs or from the use of the information contained herein.