Acc 682 Analysis & Design of Accounting Databases

Welcome

Welcome to Acc 682, and to the world of databases. During the course of this semester you will have ample opportunity to master the theoretical foundations of relational and object-relational database management systems in the context of accounting systems. You also will have an opportunity to design and implement a relational database for a part of the accounting system for a small toy company. While we will use ORACLE database management system for most class-work, you will have ample opportunity in the course, should you wish, to experiment with most commercial-grade database management systems including IBM's DB2, Informix, as well as Microsoft's SQLServer. You also will have an opportunity to learn the basics of the Extensible Markup Language (XML) in the context of databases. In the course of this semester, you will have access to one of the best stocked (in terms of hardware as well as software) facility in the Arthur Andersen Laboratory. Use it, and enjoy!

Administrivia

Semester: Fall, 1999
Time: Th: 4:15 - 7:05 PM
Room: BA 363 (C & L Classroom)
Instructor: Jagdish S. Gangolly
Office: BA 365A
Phone: (518) 442-4949
Fax: (707) 897-0601
Office Hours: Th: 2:45 - 4:15 PM. or by appointment
Instructor Homepage: https://www.albany.edu/acc/gangolly
Newsgroup: sunya.class.acc682
Announcements Page: https://www.albany.edu/acc/courses/acc682.fall98.html

Prerequisites:
This course is intended to be taken concurrently with Acc 681.Therefore you are expected to be familiar with the unix operating system, the Emacs editor, and the Java programming language (or some other higher level language with object-oriented features such as C++), to the extent covered in that course. Materials on topics such as data structures, discrete mathematics, or algorithms, to the extent needed and covered by the textbook, will be covered in the course. You are also expected to have background in accounting at least at the level of Intermediate accounting, and be quite familiar with the fundamentals of controls in accounting systems.
Handouts:
This will be the only web handout you will get during the semester. All future handouts will be postscript. Therefore it is important that you learn to print postscript documents in the Lab, or, if you have computers at home, to download Ghostscript/Ghostview from ftp://ftp.cs.wisc.edu and install it so you can print them on non-postscript printers. You will need to download and install Ghostscript interpreter and the fonts from ftp://ftp.cs.wisc.edu/ghost/aladdin/gs510/, Ghostview browser from ftp://ftp.cs.wisc.edu/ghost/rjl, and the Ghostscript manual in .pdf form from ftp://ftp.cs.wisc.edu/ghost/.

Course Objectives

The main objectives of the course are:

  • To gain an understanding of the object-oriented approach and the Entity-Relational approach to database modeling.
  • To gain a basic understanding of the ODL (Object Definition Language), and an in-depth understanding of SQL 2 (via ORACLE's sqlplus) in the context of accounting systems. The emerging standard SQL 3 will also be briefly introduced in the class.
  • To gain an understanding of the architecture and working of commercial database management systems (mainly ORACLE, DB2, Informix, and Sqlplus).
  • To gain an in-depth understanding of relational database theory in relation to the design of accounting systems.
  • To gain an understanding of the Extensible Markup Language (XML) in the context of databases.

    • Catalog Description

    Basics of relational database theory in the accounting context. Semantic modeling of accounting data including the REA model. Use of CASE tools for the analysis, design & documentation of accounting databases. Languages for retrieval of information from accounting databases. Controls in accounting databases.

    An Honest Description

    A brief history of database systems - Database Modeling - The Relational Model (including conversion from ODL and E/R diagrams to relational designs) - Database design (including functional dependencies & database normalisation) - Relational Algebra and Structured Query Language (SQL) - Constraints and triggers in SQL (including a very brief discussion of SQL 3) - System aspects of SQL (including SQL/Host language interface, transactions in SQL, security and user authorisation in SQL); Extensible Markup Language (XML) and databases.

    Textbooks/Readings

    The main textbooks for the course are:

    A First Course in Database Systems, by Jeffrey D. Ullman & Jennifer Widom, (Prentice Hall, 1997.)
    Oracle 8: The Complete Reference by George Koch & Kevin Loney, (McGraw Hill, 1997)
    XML and Java: Developing Web Applications by Hiroshi Maruyama, Kent Tamura & Naohiko Uramoto, (Addison-Wesley, 1999)

    We will not have time in the class to go over each and every minute detail in ORACLE 8. Therefore, you will need to do much of the readings from this book by yourself, with help from me. You will need those details in order to successfully complete the semester group database projects. You are most welcome to ask for clarifications of the materials in these books either on the class newsgroup or during my office hours either individually or in groups.

    Requirements

    The classes will consist of database concepts, solution of problems, discussion of cases and programming exercises. I shall be dividing the class into groups of 3-4 each, balanced in terms of skills in accounting, programming, facility with computers, mathematical maturity, and other such attributes. The groups will work through out the semester in two group projects:

  • The Accounting Database Project:
    You will analyse and design the part of an accounting database for a toy example. I shall be providing the details of this case shortly after the start of the semester. I shall be requiring frequent interim reports and class presentations on the project. You will make a final oral presentation in the class and also submit a written report on the project.
  • The Database Architecture/Security Project:
    You will study the architecture and security features of one assigned commercial database management system. You will make a final oral presentation in the class and also submit a written report on the project.
    •

    Grading

    The final course grade is dependent on the following factors:

  • 100 points: Test (Details will be announced)
  • 150 points: The Accounting Database Project
  • 50 points: The Database Architecture/Security Project
  • 0 - 50 points: Pop-quizzes, when given
  • 25 points: Class participation & Home-work, when given (max)
  • 325 - 375 points: Total points (max)
    •

    TENTATIVE SCHEDULE

  • September 9, 1999
  • Theme: Introduction to Database Systems.
  • Topics: History, Architecture, Future.
  • Read: U&W Ch.1
  •
  • September16, 1999
  • Theme: Database Modeling I
  • Topics: Object Definition Language (Attributes, Relationships & Types, Classes & subclasses), Entity-Relationship Diagrams (Attributes, Roles, Multi-way and Binary Relationships), Design Principles, Modeling constraints, Weak entity sets.
  • Read: U&W Ch.1,2 (Omit Section 2.7).
  • Do: U & W: E 2.1.1, 2.1.2, 2.1.5, 2.2.1, 2.2.2, 2.3.1, 2.4.1, 2.5.1(a & d), 2.5.2(a), 2.6.1.

  • September 23, 1999
  • Theme: Database Modeling II
  • Topics: See topics for September 16, 1999.
  • Read: U&W Ch.1,2 (Omit Section 2.7).
  • Do: See assignments for September 16, 1999).
    •

  • September 30, 1999
  • Theme: The Relational Data Model I
  • Topics: The Relational Model (Attributes, Schemas, Tuples, Domains, etc.), Conversion from ODL designs to Relational designs, Conversion from Entity-Relationship Diagrams to Relational designs, Converting subclass structures to relations, Functional Dependencies, Design of Relational Database Schemas, Multivalued dependencies.
  • Read: U&W Ch.3.
  • Do: U & W: E 3.1.1, 3.1.2, 3.2.1(a, b, & e), 3.2.3, 3.3.1, 3.3.3(a, b, & c), 3.4.1, 3.4.2, 3.4.3(a), 3.4.5, 3.5.1, 3.6.1, 3.6.2, 3.7.1, 3.8.2.

  • October 7, 1999
  • Theme: The Relational Data Model II and Relational Algebra I
  • Topics: Set operations (union, intersection, difference), Cartesian Product, Selection, Joins (Natural joins, Theta joins), Renaming, Constraints on relations, Relational operations on Bags.
  • Read: U &W Ch.4 (omit sections 4.2 - 4.4).
  • Do: See assignments for September 30, 1999.
    •

  • October 14, 1999
  • Theme: Relational Algebra II
  • Topics: Continuation of Relational Algebra I.
  • Read: U&W Ch.4 (Omit sections 4.2 - 4.4)
  • Do: See assignments for October 7, 1999.
    •

  • October 21, 1999
  • Theme: Structured Query Language (SQL) I
  • Topics: Simple queries, Queries involving more than one relation, Subqueries, Duplicates, Aggregatioon, Database modifications, View definitions, Null values and outer joins.
  • Read: U&W Ch.5. Assigned chapters from K&L.
  • Do: TBA
    •

  • October 28, 1999
  • Theme: SQL II
  • Topics: Keys, Referential integrity, Constraints on the values of attributes, Global constraints, Modification of constraints, Triggers.
  • Read: U&W Ch.6. Assigned chapters from K&L.
  • Do: TBA
    •

  • November 4, 1999
  • Theme: Extensible Markup Language (XML) I
  • Topics: XML applications & the role of Java, Constructing, generating & parsing XML documents,.
  • Browse: MTU Ch1 - 3.
  • Do: TBA
    •

  • November 11, 1999
  • Theme: XML II
  • Topics: Document Object Model (DOM), manipulating DOM structures, Working with metacontent, Interfacing XML and databases.
  • Browse: MTU Ch.4 - 6.
  • Do: TBA
    •

  • November 18, 1998
  • Theme: XML III.
  • Browse: MTU Ch.4 - 6.
  • Do: TBA
    •

  • November 25, 1998
  • THANKSGIVING (NO CLASS)

  • December 2, 1998
  • TEST

  • December 9, 1998
  • Group Project Presentations

    • Updated on September 8, 1999 by Jagdish S. Gangolly.