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 to experiment with most commercial-grade database management systems including IBM's DB2, Informix, as well as Microsoft's SQLServer. In the course of this semester, you will also have access to one of the best stocked (in terms of hardware as well as software) facility in the Arthur Andersen Laboratory. Much of the hardware for the laboratory should be installed by early october. Use it, and enjoy!

Administrivia

Semester: Fall, 1998
Time: T: 5:45 - 8:35 PM
Room: BA 223 (C & L Classroom)
Instructor: Jagdish S. Gangolly
Office: BA 365A
Phone: (518) 442-4949
Fax: (518) 442-3944
Office Hours:
MT 4:45 - 5:45 PM. or by appointment
Instructor Homepage:
http://www.albany.edu/acc/gangolly
Newsgroup:
sunya.class.acc682
Announcements Page: http://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/.
Classmeetings:
Due to a quirk in scheduling, officially there are only 13 sessions. Therefore, we will need to make up one class session at the convenience of all students in the class. Depending on the pace in the class, I may have to introduce SQL earlier than shown in the tentative schedule below, so that you can successfully complete the group projects.

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.
  • 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).

    Textbooks/Readings

    The main textbook for the course is:

    {short description of image}
    A First Course in Database Systems, by Jeffrey D. Ullman & Jennifer Widom, (Prentice Hall, 1997.)

    Since we will be working with ORACLE during the semester, you are strongly advised to have access to the following books on ORACLE SQL, Developer 2000 and Designer 2000:

    {short description of image} {short description of image} {short description of image}

    We will not have time in the class to go over each and every minute details of ORACLE 8. Therefore, you will need to do much of the readings from these three books by yourselves, 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 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 4 or 5 groups, each group 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
  • 100 points: The Database Architecture/Security Project
  • 50 points: Pop-quizzes, when given (max)
  • 25 points: Class participation & Home-work, when given (max)
  • 425 points: Total points (max)
  • TENTATIVE SCHEDULE

  • September 1, 1998
  • Topic: Introduction to Database Systems.
  • Read: U&W Ch.1
  • September8, 1998
  • Topic: Database Modeling
  • 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 15, 1998
  • Topic: Database Modeling
  • Read: U&W Ch.1,2 (Omit Section 2.7).
  • Do: See assignments for September 8, 1998).
  • October 6, 1998
  • Topic: The Relational Data Model I
  • 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 13, 1998
  • Topic: The Relational Data Model II
  • Read: U &W Ch.3.
  • Do: See assignments for October 6, 1998.
  • October 20, 1998
  • Topic: Operations in the Relational Model
  • Read: U&W Ch.4 (Omit sections 4.3 & 4.4)
  • Do: TBA
  • October 27, 1998
  • Topic: Operations in the Relational Model
  • Read: U&W Ch.4 (Omit sections 4.3 & 4.4)
  • Do: TBA
  • November 3, 1998
  • Topic: Introduction to SQL
  • Read: U&W Ch.5. Assigned chapters from K&L, D&K, and M.
  • Do: TBA
  • November 10, 1998
  • Topic: SQL
  • Read: U&W Ch.5. Assigned chapters from K&L, D&K, and M.
  • Do: TBA
  • November 17, 1998
  • Topic: SQL, Constraints & Triggers in SQL
  • Read: U&W Ch.5, 6. Assigned chapters from K&L, D&K, and M.
  • Do: TBA
  • November 24, 1998
  • Topic: SQL: System Aspects.
  • Read: U&W Ch.5,6.7. Assigned chapters from K&L, D&K, and M.
  • Do: TBA
  • December 1, 1998
  • Topic: SQL: System Aspects.
  • Read: U&W Ch.5. Assigned chapters from K&L, D&K, and M.
  • Do: TBA
  • December 8, 1998
  • Topic: SQL: System Aspects.
  • Read: U&W Ch.5. Assigned chapters from K&L, D&K, and M.
  • Do: TBA

  • Updated on August 1, 1998 by Jagdish S. Gangolly.