Chapter 1 Chapter 5 Introduction to Database Accounting

Chapter 1 Chapter 5 Introduction to. Database Accounting Information Management Systems

Learning Objectives • Understand techniques to model complex accounting phenomena in E-R diagram • Develop E-R diagrams that model effective accounting database structures • Recognize components of relational tables and keys to effective DB design • Understand use of SQL to create relational tables during implementation • Manipulate tables to extract data

Entities/Attributes • Entity - object, event, or agent about which data are collected – Objects - Inventory, equipment, cash – Events - Orders, sales, purchases – Agents - Customers, employees, vendors • Attribute - item of data that characterizes and entity or relationship 3

Attribute hierarchy for entity Client 4

Model representations for entities and attributes 5

Relationships 6

Constraints on data model relationships EMPLOYEE 1 Works N WORKS_COMPLETED a. Cardinality constraints (1, 1) EMPLOYEE b. Participation constraints Works (0, N) WORKS_COMPLETED 7

Relational Databases • Relation - collection of data representing multiple occurrences of an object, event, or agent. • Tuples (rows) - set of data that describe an instance of the entity represented by a relation; like a record in traditional file structure. • Attributes (columns) - items of data that characterize an object, event, or agent; like fields in traditional file structure. 8

Mapping E-R Diagram to Database • Create separate relational table for each entity. • Determine primary key for each relation. Must uniquely identify any row within table. • Determine attributes for each entity. • Implement relationships among entities. (Primary key in one table exists as attribute in every table (entity) where relationship specified in E-R. • Determine attributes, if any, for relationship tables. 9

CLIENT Schema - billing & HR database Client_No. Name Street_address City State Zip_code. Contact Phone_no WORK_COMPLETED Employee_No. Date Client_No. Hours EMPLOYEE Employee_No. Soc_Sec_No Name Supervisor_No Billing_Rate. Pay_Rate TRAINING_COMPLETED Employee_No. Date Hours Train_code RELEASE_TIME Employee_No. Date Hours Vacation_Sick

CLIENT Referential constraints - relational schema Client_No. Name Street_address City State Zip_code Contact Phone_no WORK_COMPLETED Employee_No. Date Client_No. Hours EMPLOYEE Employee_No. Soc_Sec_No Name Supervisor_No Billing_Rate. Pay_Rate TRAINING_COMPLETED Employee_No. Date Hours Train_code RELEASE_TIME Employee_No. Date Hours Vacation_Sick

SQL : Relational DB Query Language • Used to – define database systems – query DB for information – generate reports from DB – access DB from within programs • De facto standard DB language 13

Constructing DBs using SQL • • Assign name to relation Assign names to attributes Specify data type for attributes Specify constraints, when appropriate, on the attributes. 14

Other SQL functions • Update DB – add tuple using INSERT – remove tuple using DELETE – change attribute value using UPDATE • Query DB using SELECT/WHERE • Generate standard reports from DB – ad hoc (as necessary) – repetitive - might generate subschema view 15

Learning Objectives • Understand techniques to model complex accounting phenomena in E-R diagram • Develop E-R diagrams that model effective accounting database structures • Recognize components of relational tables and keys to effective DB design • Understand use of SQL to create relational tables during implementation • Manipulate tables to extract data