IMPORTANT: These notes are placed on these web pages solely for the benefit of the students at the State University of New York at Albany, and therefore, are to be used in the context of the course (Acc681 Accounting Information Systems) for which they are intended.

[6.1.Database Management Systems (Basics)] [6.2. Data Normalisation] [6.3. References]

Simplicity does not precede complexity, but follows it.

Alan Perlis

A picture is worth 10K words - but only those to describe the picture. Hardly any sets of 10K words can be adequately described with pictures.

Alan Perlis

Fools ignore complexity. Pragmatists suffer it. Some can avoid it. Geniuses remove it.

Alan Perlis

It goes against the grain of modern education to teach children to program. What fun is there in making plans, acquiring discipline in organizing thoughts, devoting attention to detail and learning to be self-critical?

Alan Perlis

Contents

• 6.1. Database Management Systems (Basics)
• 6.2. Data Normalisation
• 6.3. References

6.1. Data Base Management Systems

In the traditional file based accounting information systems, the various accounting applications (such as billing, accounts payable, accounts receivable, payroll, general ledger, etc,) own their own data. This data ownership has many drawbacks:

• Data Depandencies: Since the applications own data, should applications change, so must the data.
• Data Redundancies: Due to the ownership of data, the same data may have to be duplicated in many files.
• Lack of Uniformity of Meaning of Data: Each application may interpret the meaning of data in its own way. This will mean that data sharing between different applications is difficult.
• Difficulties in Standardisation, Security and Integrity:There is a lack of unified view of data, and therefore problems in enforcing integrity and security of data.
• Difficulties in Data Access by Users: Since users must access data through programs that have data structures embedded in them, the use of such systems is demanding on the users.

The figure below illustrates a typical traditional file based system.

In database oriented systems, on the other hand, the ownership relationship between applications and data is absent, and therefore data can be shared by various applications/users. Data Base Management System (DBMS) is the software for implementation of databases.

• Data Independence: This is possible because of the lack of ownership of data by the applications.
• Reduced Data Redundancies: There is no need for unnecessary duplication od data.
• Uniformity of Meaning of Data:
• Possibility of Standardisation, Enforcement of Integrity and Security of Data:
• Data Abstraction: The users do not have to be concerned with HOW the data is organized.

The figure below illustrates the architecture of a database system.

Return to Contents

6.2. Data Normalization

NOTE: Read the section below in conjunction with Ch.4 of the Roman text.

BASIC CONCEPTS:

• Functional Dependency: In a relation, if the value of a particular attribute A uniquely determines the value of another attribute B, then the attribute B is said to be functionally dependent on attribute A. For example, in the relation below, NAME, ADDRESS, DATE-OF-BIRTH are all functionally dependent on SOCIAL-SECURITY-NUMBER.
• Relation Key: An attribute (or a set of attributes) is said to be the key of a relation if all other (non-key) attributes in such relation are functionally dependent on such attribute (or set of attributes).
• Derived Functional Dependencies: Functional dependencies are transitive in that if attribute A is functionally dependent on B and B in turn is functionally dependent on attribute C, then attribute A is also functionally dependent on attribute C. In database design, it is important to consider all functional dependencies, including those that can be derived from the known ones.
• Full Functional Dependency: Consider a database for a project that uses material items. The database records the price and quantity information for all materials used in the projects. The attributes and the functional dependencies between them in the parject-materials usage relation is shown in the figure below, where the relation key consists of two attributes, viz., Project No. and Item No.. As the above figure shows,
  • The attributes Quantity used and Cost are functionally dependent on the whole relation key consisting of the attributes Project No. and Item No. Therefore, the attributes Quantity used and Cost are said to be fully functionally dependent on the relation key.
  • The attribute Price is functionally dependent on the attribute Item No. only, ie., on a part of the relation key and not the whole key.
  • The attributes Startdate and Enddate are functionally dependent on the attribute Project No. only, ie., on a part of the relation key and not the whole key.
• Non-Normalized Relation: In a non-normalized relation, there are repeated groups of attributes. The illustration below shows an example for the invoice discussed earlier.
• First Normal Form Relations (1NF): The 1NF relations are derived from non-normalized relations by splitting such relations so as to eliminate repeated groups. For example, we can split the above invoice relation into two relations (Invoice and Invoice Details) as done below: The above figure also shows the functional dependencies. Since there are no repeated groups of attributes in either of the relations (Invoice and Invoice details), they are in the First Normal Form.
• Second Normal Form Relations (2NF):A relation is said to be in the Second Normal Form if it is in the First normal Form and all non-key attributes are functionally dependent on the WHOLE key. Now let us see if the two relations in the above figure are in 2NF.
  • INVOICE RELATION: INVOICE-NUM is the relation key since all other attributes in the relation INVOICE are functionally dependent on it. Since all such non-key attributes are functionally dependent on the whole key (consisting of the single attribute INVOICE_NUM), the relation INVOICE is in 2NF.
  • INVOICE DETAILS RELATION:Since ITEM-DESCR, PRICE, QUANTITY, and AMOUNT are all functionally dependent on the set of attributes {INV-NUM, ITEM-NUM}, this set is the relation key. However, this relation is NOT in the Second Normal Form since the non-key attributes ITEM_DESCR and PRICE are functionally dependent on a part of the relation key ( ITEM_NUM) rather than the whole relation key. We can derive the Second Normal Form relations for INVOICE_DETAILS by splitting this relation into two by forming a separate relation with the offending attributes of ITEM_DESCR and PRICE. The resultant 2NF relations for INVOICE_DETAILS are given in the figure below.
• Third Normal Form Relations (3NF):A relation is said to be in the Third Normal Form if it is in the Second Normal Form, and in addition each non-key attribute is functionally dependent on the whole key and nothing but the key. The INVOICE relation above is in the Second Normal Form, but is NOT in the Third Normal Form since the attribute CUST_ADDR is functionally dependent on the non-key attribute CUST_NAME. To convert it to the Third Normal Form, we again split the INVOICE relation into INVOICE and CUSTOMER relations by removing the offending attributes from INVOICE and creating the CUSTOMER relation. The final 3NF relations for the INVOICE example are given below:

Return to Contents

6.3. References

• Batini, C., S. Ceri, and S. Navathe. (1992) Conceptual Database Design: An Entity-Relationship Approach. Redwood City, Calif: The Benjamin/Cummings Publishing Company, Inc.
• Elmasri, R, and S. Navathe. (1994) Fundamentals of Database Systems, 2d ed. Redwood City, Calif: The Benjamin/Cummings Publishing Company, Inc.
• Martin, J. (1985) System Design from Provably Correct Constructs. Englewood Cliffs, NJ: Prentice Hall.
• Ullmann, J. (1988) Principles of Database and Knowledge-base Systems, Volume 1. Rockville, MD: Computer Science Press.
• Yourdon, E. (1989) Modern Structured Analysis. Englewood Cliffs, NJ: Prentice Hall.

Return to Contents

Updated on October 6, 1997 by gangolly@cnsunix.albany.edu