Advanced Accounting Information Systems

1. Advanced Accounting Information Systems Day 8 Database Modeling September 11, 2009

2. Announcements Business Week Career Fair Borthick and Jones case –read pages 112-118 for Monday (on WebCT only) Graduate student papers

3. Objectives Understand why databases are important in today’s environment Describe concepts of data hierarchy, record structures, and keys Describe how to use data dictionaries Describe why processing accuracy, concurrency, and security are important to multiuser databases Develop data models using REA Identify the key differences between logical view of information and physical view of information Identify the advantages of using a relational structure rather than either a hierarchical or network structure to organize database records

4. Questions for today What is the purpose of a foreign key?

5. Question for today What is referential integrity? Why is it important to store primary key values consistently within different tables of the same database?

6. Question for today What are database cardinalities?

7. Resource Event Agent Data Model Special ERD to model accounting information systems Three ‘rules’ Each event generally involves at least one resource Each event generally involves two agents – one internal and one external Each subsequent event should be shown below its prior event

8. Resource Event Agent Model Basic model for each transaction cycle

9. Resource Event Agent Model Example Martin Shoes manufactures and distributes orthopedic footwear. To sale its products, the marketing department requires sales personnel to call on the shoe retailers within their assigned geographic territories. Each salesperson has a laptop computer, which he/she uses to record sale orders during the day and to send these sales orders to Martin’s network nightly for updating the company’s sales order file. Each day, warehouse personnel review the current sales orders in its file, and where possible, pick the goods and ready them for shipment. When the shipping department completes a shipment, it also notifies the billing department, which then prepares an invoice for the customer. Payment terms vary by customer, but most are net 30. When the billing department receives a payment, the billing clerk credits the customer’s account and records the case received.

10. Cardinality

11. Converting ERD to physical database Each entity becomes a separate table Each M:M relationship becomes a separate table with concatenated primary keys Each 1:M relationship is implemented by using foreign keys

12. Organizing database records Database structure Hierarchical Network Relational

13. Guidelines for creating database tables and records Design FIRST Name tables systematically tbl or qry Use mnemonic names for data fields – descriptive but NOT too long Assign correct data types to data fields Data fields that link tables together MUST be the same data type Limit size of text data fields to reasonable fields Use input masks

14. FAA Airport database Identify at least three challenges

15. Questions for Monday What is normalization and why is it valuable in database analysis? Describe the three problems associated with a database in first normal form. What is the difference between first and second normal form?