1 / 34

Chapter 9

Chapter 9. Database Management Systems. Accounting Information Systems, 5 th edition James A. Hall . Flat-File Versus Database Environments. Computer processing involves two components: data and instructions (programs).

fancy
Download Presentation

Chapter 9

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 9 Database Management Systems Accounting Information Systems, 5th edition James A. Hall

  2. Flat-File Versus Database Environments • Computer processing involves two components: data and instructions (programs). • Conceptually, there are two methods for designing interface between programs and data: • file-oriented (flat-file) processing: A specific data file was created for each application [legacy systems] • data-oriented processing: Create a single data repository to support numerous applications [current systems] • Disadvantages of file-oriented processing include redundant data and programs and varying formats for storing the redundant data.

  3. File-Oriented (Flat-File) Environment Data User 1(Sales) Transactions Program 1 A,B,C User 2 (A/R) Transactions Program 2 X,B,Y User 3 (Payroll) Transactions Program 3 L,B,M Where B is a customer record. Assume B’s address changes. Each user that works with the B record must modify the address, increasing chance for error.

  4. Database Approach Database User 1 (Sales) Transactions Program 1 A, B, C, X, Y, L, M D B M S User 2 (A/R) Transactions Program 2 User 3 (Payroll) Transactions Program 3 With the database approach, any User could change B’saddress - it would be changed for all users. changes. [Assume all users have authority to change the address for this example.]

  5. Data Redundancy & Flat-File Problems • Data Storage - creates excessive storage costs of paper documents and/or magnetic form • Data Updating - any changes or additions must be performed multiple times (see example of B’s address) • Currency of Information - potential problem of failing to update all affected files • Task-Data Dependency - user’s inability to obtain additional information as his or her needs change

  6. Advantages of the Database Approach Data sharing/centralized database resolves flat-file problems: • Nodata redundancy - Data stored only once, eliminating data redundancy and reducing storage costs. • Single update - Because data are in only one place, requires only single update procedure, reducing time/cost of keeping database current. • Current values - Change to database made by any (authorized) user yields current data values for all other (authorized) users. • Task-data independence - As users’ information needs expand beyond immediate domain, new needs can be more easily satisfied than under flat-file approach.

  7. Disadvantages of the Database Approach • Can be costly to implement • additional hardware, software, storage, and network resources are required – yet these costs continue to drop… • Can only run in certain operating environments • may be unsuitable for some existing system configurations (e.g., legacy systems)

  8. Internal Controls and DBMS1 • Purpose of DBMS is to provide controlled accessto database. • DBMS is special software programmed to know which data elements each user is authorized to access and deny unauthorized requests of data. 1 DBMS = database management system

  9. Query Language • Query capability permits both end users and professional programmers to access data (information) in database without writing conventional programs.

  10. Three Steps in Designing a Database • Prepare conceptual model • Identify entities • Identify relationships between entities • Prepare ER diagram (ERD) • Specify logical design • Select logical database model (which will always be relational nowadays) • Transform conceptual data model using logical database model • Implement physical design • Physical structures • Access methods

  11. Phase 1 Draw an ERD to capture the process. Prepare the Conceptual Model

  12. Example of a Relationship LinkingTwo Entities places ORDER CUSTOMER Name Order Number Date Cust Number Example of 1:M relationship, using Crow’s Feet notation. We usually don’t cover the “0” relationship.

  13. Phase 2 Create relational tables. Specify Logical Design

  14. Logical Data Structures • Objective is to develop structure efficiently so data can be accessed quickly and easily. • Four types of database structures are: • hierarchical (tree structure) • network • relational • object-oriented Concentrate on relational DBs. They are the systems being created at the current time.

  15. The Relational Model • Relational model portrays data in form of two dimensional tables (looks like Excel worksheet): • relation - database table • attributes/fields (data elements) - columns • tuples (records) - rows • data - intersection of rows and columns

  16. Properly Designed Relational Tables • No repeating values – Primary Key cannot repeat in a table. • Attribute values in any column must all be of same class. [can’t put text in a date field] • Each column (field name) in a tablemust be uniquely named. [can’t have 2 address fields both named Address – use Addr1 and Addr2] • Each row (record) in a tablemust be unique in at least one attribute (primary key)

  17. Relational Model Data Linkages (>1 table) • No explicit pointers are present – data are viewed as collection of independent tables. • “Relations” formed by attribute/field that is common to both tables in relation. This field is a “foreign key.” • How to assign foreign keys: • if 1:1 association, either of table’s primary keys may be foreign key. • if 1:M association, primary key of the ONE side is embedded as foreign key in the MANY side. • if M:M (M:N) association, create separate linking table.

  18. Three Types of Anomalies(anomalies are found in Unnormalized tables) • Insertion Anomaly: New item cannot be added to table until at least one entity uses particular attribute item. • Deletion Anomaly: If attribute item used by only one entity is deleted, all information about that attribute item is lost. • Update Anomaly: Modification on attribute must be made in each of rows in which attribute appears. • Anomalies can be corrected by creating relational tables.

  19. Relational Tables • Various items of interest (customers, inventory, sales) stored in SEPARATE tables in database. • Advantages: • Removes all three anomalies • Efficient use of space. • Flexible. Users can form ad hoc relationships for queries.

  20. Normalization Process • Process that breaks up large, complex tables into smaller tables that meet two conditions: • all nonkey attributes (fields) in table are dependent on primary key (PK) • all nonkey attributes (fields) are independent of other nonkey attributes (fields) • When unnormalized tables are split and reduced to third normal form, they are linked together by foreign keys (secondary keys).

  21. all nonkey attributes (fields) in table are dependent on primary key (PK) This is an unnormalized table. Only Lname & Fname depend on the PK of Student ID. Other fields belong in another table(s).

  22. Steps in Normalization Unnormalized Table with repeating groups 1. Remove repeating groups Does field depend on PK? If no, remove and put in another table. First normal form (1NF) 2. Remove partial dependencies • Only concerns tables with composite PKs. See Fig. 9-38 Second normal form (2NF) 3. Remove transitive dependencies Third normal form (3NF) • When field depends on another non-key field in table. See Fig. 9-40

  23. Accountants and Data Normalization • Update anomalies can generate conflicting and obsolete database values. • Insertion anomalies can result in unrecorded transactions and incomplete audit trails. • Deletion anomalies can cause loss of accounting records and destruction of audit trails. • Accountants should understand data normalization process and be able to determine whether database is properly normalized.

  24. Phase 3 Decide about software and hardware. Implement Physical Design

  25. Distributed Data Processing

  26. President CENTRALIZED COMPUTER SERVICES FUNCTION VP Marketing VP Computer Services VP Operations VP Finance Systems Development Database Administration Data Processing New Systems Development Data Control Data Preparation Data Library Systems Maintenance Computer Operations DISTRIBUTED ORGANIZATIONAL STRUCTURE President VP Marketing VP Finance VP Administration VP Operations Manager Plant X Manager Plant Y Treasurer Controller IPU IPU IPU IPU IPU IPU

  27. Advantages: Cost reductions in hardware and data entry tasks Improved cost control responsibility Improved user satisfaction because control is closer to user level Backup of datacan be improved through use of multiple data storage sites Disadvantages Loss of control (organization-wide) Mismanagement of organization-wide resources Hardware/software incompatibility Redundant tasks/data Incompatible tasks may be consolidated Lack of standards Characteristics of DDP1 1 DDP = Distributed Data Processing

  28. Centralized Databases in DDP Environment • Data are retained in central location. • Remote IPUs (workstations) send requests for data. • Centralsite services needs of remote workstations. • Actual data processing is performed at remote workstation

  29. Data Currency • Can be an issue in DDP with centralized database • During transaction processing, data will temporarily be inconsistent as record is being read and updated. • Databaselockout procedures are necessary to keep workstations from reading inconsistent data and/or from writing over a transaction being written by another workstation.

  30. Distributed Databases: Partitioning • Splits central database into segments that are distributed to their primary users • Advantages: • users’ control is increased by having data stored at local sites • transaction processing response time is improved • volume of transmitted data between workstations is reduced • Reduces potential data loss from a disaster

  31. Distributed Databases: Replication • Duplication of entiredatabase for multiple workstations • Effective for situations with a high degree of data sharing, but no primary user • supports read-only queries. • Data traffic between sites is reduced considerably.

  32. The Deadlock Phenomenon • Especially a problem with partitioned databases • Occurs when multiple sites lock each other out of data that they are currently using • Special software needed to analyze and resolve conflicts.

  33. The Deadlock Phenomenon Locked A, waiting for C Locked E, waiting for A A,B E, F C,D Locked C, waiting for E

  34. The End!

More Related