Chapter 5 – Database Management SystemsLearning ObjectivesIntroduction to DatabasesTwo Approaches To Business Event ProcessingTwo Approaches To Event ProcessingRecord Layouts under the Applications Approach to Business Event ProcessingProblems with Applications ApproachRecord Layout – Applications ApproachDatabase Management SystemsSchemas and SubschemasRelational Database TablesRecord Layouts as TablesFormulating a Query in SQLSQL – Query ExampleDisadvantages of DBMSLogical Database ModelsSlide 17Object-Oriented Database ModelElements/Parts of a Relational DatabaseComparison of Database and SpreadsheetClassifying and Coding DataSequential CodingBlock CodingSignificant Digit CodingSlide 25Hierarchical Data CodingSlide 27Mnemonic Data CodingCoding ExamplesDatabase NormalizationUnnormalized RelationFunctional Dependence and Primary KeysRelation in First Normal Form (1NF)Problems with First Normal Form (1NF)Update AnomaliesTwo steps to get from 1NF to 2NFRelations in Second Normal Form (2NF)Third Normal Form (3NF)Transitive DependenciesRelations in Third Normal Form (3NF)Entity-Relationship Models (REA)Slide 42Entity Relationship Models (REA)Characteristics of RelationshipsCreate the E-R Diagram in 5 StepsRelational DatabaseDecision Support Systems: DSSExecutive Information System: ESSDecision Support Systems and Executive Information SystemsGroup Support Systems: GSSExpert SystemsExcel Decision Model ExampleIntelligent AgentsData WarehousesKnowledge Management SystemsChapter 5 – Database Management SystemsAccounting Information Systems 8eUlric J. Gelinas and Richard Dull© 2010 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom useLearning Objectives•Describe the limitations of traditional application approaches to managing data.•Analyze the advantages gained by using the database approach to managing data.•Create normalized tables in a relational database.•Use entity-relationship diagrams in database design and implementation.•Explain the importance of advanced database applications in decision support and knowledge management.23Introduction to Databases•The enterprise database is at the heart of Accounting Information Systems (AIS).•The chapter discusses the major types of databases available and how organizations undertake database design for AIS. •Larger organizations store information in data warehouses in ways that let managers analyze it to gain important insights.•Many companies combine their data resources with decision support systems, executive information systems, group decision systems, and other advanced technology-based systems to improve decision making and operations.4Two Approaches To Business Event ProcessingApplications Approach•Concentrates on the process being performed.•Data support the role of the programs that run in each application system.•Each application collects and manages its own data in separate, distinguishable files.•Data redundancy can cause inconsistencies among the same data in different files when information is updated in one file but not the others.•Storage costs are increased because the system must store and maintain multiple versions of the same data in different files.•Data residing in separate files are not shareable among applications.Database Approach•Facts about events are stored in relational database tables instead of separate files.•Solves many of the problems caused by data redundancy.•Improves the efficiency of processing data by eliminating data redundancies and improving data integrity.•Enables the creation of integrated business information systems that include data about all of a company’s operations in one massive collection of relational tables.•Multiple users from throughout the organization can view and aggregate event data in a manner most conducive to their needs.5Two Approaches To Event Processing6Record Layouts under the Applications Approach to Business Event Processing7Problems with Applications Approach•Data redundancy - files stored may include redundant information increasing storage requirements and the risk of inconsistency.•Data in files is not shareable across applications because applications depend on a fixed record layout in data files.8Record Layout – Applications Approach•Each application stores all the data required for analysis.•Shows many redundancies across applications/files.•Data lacks integrity when the data stored by one application is inconsistent with data stored by another application.9Database Management Systems•A set of integrated programs designed to simplify the tasks of creating, accessing, and managing data.•Integrates a collection of files that are independent of application programs and are available to satisfy a number of different processing needs.•Database contains data related to all the organization’s applications in one place.•Supports normal data processing needs and enhances the organization’s management activities by providing data useful to managers.•Data is independent of the application that created the data (i.e., can be changed/used by other applications).•The term enterprise database is synonymously with database management system or DBMS.Schemas and Subschemas1011Relational Database Tables•The next slide shows a database with data stored in a relational structure.•This is most common type of database structure used in businesses today. •The data from three files are now stored in four tables: –CUSTOMERS (instead of the customer master data file)–INVENTORY_ITEMS (instead of the inventory master data file)–SALES_ORDERS–SALES_LINES (replace the sales order master data file)•The logical database view is how the data appear to the user to be stored. –This view represents the structure that the user must use to extract data from the database.12Record Layouts as Tables13Formulating a Query in SQLUsers can access the data in the tables by:1. Formulating a query.2. Preparing a report using a report writer.3. Including a request for data within an application program.14SQL – Query ExampleA query that uses the SQL SELECT command can return to the customers assigned to salesperson Garcia.–SELECT Cust_Code Cust_Name Cust_City–FROM CUSTOMERS–WHERE Salesperson = ‘Garcia’There are