class5 powerpoint

Class5 powerpoint - 33:010:458 33:010:458 Accounting Information Inf Systems Dr Peter R Gillett Peter R Gillett Associate Professor Department of

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 33:010:458 33:010:458 Accounting Information Inf Systems Dr. Peter R. Gillett Peter R. Gillett Associate Professor Department of Accounting, Business Ethics and Information Systems Rutgers Business School–Newark and New Brunswick Accounting Information Systems A.I.S. Class 5: Outline Group Work for Chapters 1 & 2 Learning Objectives for Chapter 6 Elements of Database Systems Group Work for Chapter 6 Work for Chapter September 16, 2009 Dr. Peter R. Gillett 2 Accounting Information Systems Group Work for Chapters 1 & 2 Discussion Questions Chapters 1 & 2 Chapter 2 Problems 3, 4, 5, 7 Narrative and flowcharts in the Chapter 1 fl Appendix for automated sales Appendix for automated sales September 16, 2009 Dr. Peter R. Gillett 3 Accounting Information Systems Group Contracts Please turn in if you have not already . . . Most of the rest of the documentation is Most of the rest of the documentation is “just in case” But the last three pages are logs that you the last three pages are logs that you need to maintain continuously – and you must turn copies in with each must turn copies in with each stage of the of the project September 16, 2009 Dr. Peter R. Gillett 4 Accounting Information Systems Learning Objectives for Chapter 6 After studying this chapter you should be able to: * distinguish between the file-oriented approach and the database approach * discuss fundamental relational database concepts fundamental relational database concepts such as composite and foreign keys * specify the types of relationships that can be represented in database systems * provide a detailed description of the relational database model database model September 16, 2009 Dr. Peter R. Gillett 5 Accounting Information Systems Learning Objectives for Chapter 6 After studying this chapter you should be able to: * discuss database integrity, emphasizing entity and referential integrity in particular * explain and provide examples of validation rules in and provide examples of validation rules in relational database systems * discuss how views and permissions can be used to restrict access to sensitive data in relational database systems * explain the data dictionary concept the data dictionary concept September 16, 2009 Dr. Peter R. Gillett 6 Accounting Information Systems Learning Objectives for Chapter 6 After studying this chapter you should be able to: * describe the types of database languages * construct SQL queries to extract information from relational database systems relational database systems * discuss database backup and recovery methods * explain concepts such as concurrency control concepts such as concurrency control * explain in general terms concepts such as the objectoriented approach to developing database systems September 16, 2009 Dr. Peter R. Gillett 7 Accounting Information Systems File Oriented Approach Data redundancy Proliferation of files Proliferation of files Lengthy application development Lack of data independence Duplicate processing Data inconsistencies September 16, 2009 Dr. Peter R. Gillett 8 Accounting Information Systems Database Approach Data redundancy virtually eliminated Eliminate data inconsistencies Eliminate data inconsistencies Data independence Rapid application development Centralized backup, control and security Avoid duplicate processing September 16, 2009 Dr. Peter R. Gillett 9 Accounting Information Systems Database Approach Complexity Data integrity requires programming and Data integrity requires programming and knowledge of DBMS Data accessible only through DBMS accessible only through DBMS Centralized backup, control and security September 16, 2009 Dr. Peter R. Gillett 10 Accounting Information Systems Database Concepts Keys * Primary keys * Composite (concatenated) keys * Foreign keys * Non-key attributes Cardinalities * 1:1 * 1:M and M:1 * M:M September 16, 2009 Dr. Peter R. Gillett 11 Accounting Information Systems Our Notation The textbook used to show primary keys underlined and foreign keys dotted underlined; sometimes they showed primary keys in italics and marked foreign keys with an asterisk. Now they show primary keys underlined and foreign keys with an asterisk primary keys underlined and foreign keys with an asterisk. We will stick to the notation shown here, with primary keys underlined and foreign keys in square brackets. Customer (Customer#, Name, Address, . . . ) Name Address Invoices (Invoice#, Date, [Customer#], . . .) InvoiceItems ([Invoice#], Item#, Qty, UnitPrice) or Inventory (Inventory#, InventoryName, . . .) InvoiceItems ([Invoice#], [Inventory#], Qty, UnitPrice) Qty UnitPrice) September 16, 2009 Dr. Peter R. Gillett 12 Accounting Information Systems Database Models Hierarchical * 1:1 and 1:M relationships (parent-child) (p * single root (entry point) * explicit pointers pointers Network * 1:1, 1:M, M:1 and M:M relationships 1:M M:1 and M:M relationships * multiple parents (multiple entry points) * explicit pointers September 16, 2009 Dr. Peter R. Gillett 13 Accounting Information Systems Database Models Relational * Relations (tables) * Tuples (rows) * Attributes (columns) (columns) * Relationships between tables are implicit September 16, 2009 Dr. Peter R. Gillett 14 Accounting Information Systems The Relational Database Model Relation (table) names must be unique Every relation must have a primary key Duplicate rows and columns are not allowed The order of rows and columns is immaterial (except by convention) Entity integrity Referential integrity Referential integrity September 16, 2009 Dr. Peter R. Gillett 15 Accounting Information Systems The Relational Database Model Entity integrity * Primary key cannot be null * Primary key must be unique Referential integrity Referential integrity * foreign keys (when part of a composite key) • must match an existing value (cannot be null) match an existing value (cannot be null) * foreign keys (when non-key attributes) • must match an existing value or be null match an existing value or be null September 16, 2009 Dr. Peter R. Gillett 16 Accounting Information Systems Validation Rules Greater than minimum value Less than maximum value Less than maximum value One of the acceptable values Correct number of di its digit Correct mathematical or logical relationship between fields September 16, 2009 Dr. Peter R. Gillett 17 Accounting Information Systems Restricting Access Specifying permissions Restricted logical views September 16, 2009 Dr. Peter R. Gillett 18 Accounting Information Systems Data Dictionary Stores information about the tables, attributes (columns), formats, access privileges etc privileges etc. September 16, 2009 Dr. Peter R. Gillett 19 Accounting Information Systems RDBMS Languages Data Definition Language (DDL) Data Manipulation Language (DML) Data Manipulation Language (DML) Data Query Language (DQL) * Query By Example (QBE) By Example (QBE) * Structured Query Language (SQL) Report writers Forms editors September 16, 2009 Dr. Peter R. Gillett 20 Accounting Information Systems SQL SELECT <table_name1>.<field_name1>, <table_name2>.<field_name2> . . . FROM table_name1, table_name2, . . . WHERE <table_name1>.<common_field1> = <table_name2>.<common_field2> . . . AND <condition> [INTO <result table>] SELECT CUSTOMERS.NAME, CUSTOMERS.BALANCE FROM CUSTOMERS, SALES, ITEMS_SOLD WHERE CUSTOMERS.CUSTOMERNO = SALES.CUSTOMERNO SALES AND SALES.INVOICENO = ITEMS_SOLD.INVOICENO AND ITEMS_SOLD.ITEMNO = 1250 September 16, 2009 Dr. Peter R. Gillett 21 Accounting Information Systems DBMS Backup and Control Static backup Dynamic backup Dynamic backup * Redundant Array of Independent Disks (RAID) Concurrency control control * lock out * write lock September 16, 2009 Dr. Peter R. Gillett 22 Accounting Information Systems OODBMS Encapsulation Polymorphism Inheritance Currently relatively high cost and weak performance Currently relatively high cost and weak performance Hybrid systems for the future? * ORDBMS (Object-Relational Database Management System) (Object Database Management System) * SQL-1999 * PostgreSQL September 16, 2009 Dr. Peter R. Gillett 23 Accounting Information Systems Group Work for Chapter 6 Discussion Questions – if there are problems Please work on Problems Please work on Problems 2, 5, 6, 7 Narrative and flowcharts in the Chapter 1 Appendix for manual Purchases Appendix for manual Purchases Group Projects – Ash Accounting * Stage 1 documents the existing manual systems • Submit your assigned flowcharts in the box outside my office before class on Monday September 16, 2009 Dr. Peter R. Gillett 24 ...
View Full Document

This note was uploaded on 03/19/2010 for the course 010 458 taught by Professor Gillett during the Fall '09 term at Rutgers.

Ask a homework question - tutors are online