Transcript Chapter 5
Chapter 1 Chapter 5 Introduction toDatabase Accounting Information Management Systems Systems 1 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 (0,N) Works 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_RatePay_Rate TRAINING_COMPLETED Employee_No. Date Hours Train_code RELEASE_TIME Employee_No. Date Hours Vacation_Sick 10 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_RatePay_Rate TRAINING_COMPLETED Employee_No. Date Hours Train_code RELEASE_TIME Employee_No. Date Hours Vacation_Sick 11 12 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