ACW2420 Week 8

• ER diagram


  Entity Relationship diagram
• Sale record


  • Can only be linked to 1 customer record
  • Can be linked to many customer records
• Customer record


  • May or may not be linked to any sale records
  • Can be linked to many sale records
• Sale record


  • May or may not be assigned to any customer record
  • Can be linked to at most one customer record
• Sale record


  • Must be assigned to one and only one customer record
• Customer record


  • May or may not be linked to any sales records
  • Can be linked to many sales records
• Item record


  • May or may not be linked to any sales records
  • Can be linked to many sales records
• Sale record


  • Must be linked to at least one item record
  • Can be linked to more than one item record
• Customer order record


  • May or may not involve any goods shipment record
  • Can be linked to many goods shipment records
• Goods shipment record


  • Must be linked to at least one customer order record
• Payment received record


  • Must be linked to one A/R clerk record
• A/R clerk record


  • May or may not be involved in any payment received records
  • Can be involved in many payment received records
• Database


  Efficiently and centrally coordinates information for a related group of files

  
• File


  A related group of records (Table)
• Record


  A related group of fields (Row)
• Attribute


  A specific characteristic of interest for the entity (Column)
• Field


  The intersection between the record and the attribute (Cell)
• Database views


  • Logical view - meaningful view of data and how data are related
  • Physical view - how the data is actually physically stored in the system

  
• Database designers


  Need to understand user's needs and the conceptual level of the entire database as well as the physical view
• Database users


  Always have a logical view, do not need to be concerned with how the data is stored in the system
• Advantages of databases


  • Data is integrated
  • Data sharing
  • Minimize data redundancy and inconsistencies
  • Data is independent of the programs that use the data
  • Data is easily accessed for reporting and cross-functional analysis
• File-based legacy system


  • Separate files for customers and sales, causing data redundancy and inconsistencies
• Relational database


  Comprised of a set of tables, with rows (records or tuples) and columns (attributes)
• Primary key


  An attribute that uniquely identifies a specific row in a table
• Foreign key


  Allows a table to relate to other tables
• Relational databases are the most common implementation of databases
• Data stored in one large table can be redundant and inefficient, causing update, insert, and delete anomalies</b>
• Relational database design rules


  • Every column in a row must be single valued
  • Primary key cannot be null
  • If a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table
  • All other attributes in the table must describe characteristics of the object identified by the primary key

  
• Query


  A question asked by a user to retrieve specific information from a relational database
• Creating a query


  1. Identify the tables where the required data is located

  2. Select only the attributes of interest

  3. Specify the criteria to filter the results
• Accountants are involved in the implementation of new database systems as they understand information, how to control it, and use it to report to management the performance
• Attributes


  Characteristics or properties of an entity
• Criteria


  Conditions that must be met to filter the information
• Creating the Query - through the GUI of the database software

  1. Select attributes from tables

  2. Specify criteria for a specific customer
• Accountants are involved in the implementation of new systems as they understand information, how to control it and use it to report to management the performance of the organization
• Accountants can provide management with evaluation of feasibility of the project and can estimate return on investment
• Database model


  Defines the database to faithfully represent all aspects of the organization, including its interactions with the external environment
• Data modeling


  Process of defining a database so that it faithfully represents all aspects of the organization, including its interactions with the external environment

  
• REA data model


  • Specifically designed to be used for accounting information systems
• Entity-relationship (ER) diagrams


  • Can depict any database relationship