Accounting Systems of Northwind Report (Assessment)

Introduction

This report is a review of the existing database system of Northwind called Accounting Information System. There are four major objectives for which the review is carried out. These objectives are related to the database structure of the given application database system. It involves analysis of its eight tables, their significance, and queries made based on the tables and business needs. In a broader view, the database has all the relevant features that it must have. After considering all requirements, the database developer built it in a very systematic manner by incorporating eight different but related tables, necessary forms queries, reports, open access pages, macros, and modules, etc.

The database deals with all business-related activities of Northwind. Each table contains unique data of each branch or department of the organization. Prioritizing the main objectives of the review, this report discusses the characteristics of components of the database and their effective utilization in the organization.

Objective 1: Database Table Structures

Northwind Accounting Information database system consisting of eight distinct tables with their unique identity. These tables maintain a sort of relationship, thus forms the relational database system. Analyzing, in detail, it is noticed that the above-said database has been well designed giving consideration to the main factors like lossless decomposition, dependency preservation, and redundancy proof. The first factor is important to ensure precision in data, the second to guarantee the efficiency of updates, and the last to avoid redundancy of data. Still, it has some flaws.

As far as all tables of this database are concerned, each table is of the Third Normal Form. “A relational table is in 3NF if and only if all non-key columns are

• mutually independent and
• fully dependent upon the primary key.

Mutual independence means that no non-key column is dependent upon any combination of the other columns.” (Source: Basic Concepts) .

Tables are created and designed in the MS Access model. The relationship among each table is well established. However, there are some significant limitations in the design part as the primary key fields of the independent tables are mostly ‘AutoNumber’ in type. These tables are Suppliers, Categories, Products, Employees, Orders, and Shippers whose primary keys are ‘AutoNumber’ fields. It is not a good practice of designing tables in a database environment.

Another lacuna due to which the design is not up to the required standard is the inappropriate defining of Private Key. ‘ProductID’ and ‘OrderID’ are the primary key fields of tables ‘Products’, and ‘Orders’ respectively. These two fields are connected with the ‘Order Details’ table. So these two fields in ‘Order Details’ should be foreign keys. But they have been defined in ‘Order Details’ as the Primary key which is incorrect.

Considering basic elements of the database, the designing of tables is an important step for developing a database system. Any small error could lead the complete system towards havoc. Therefore, while designing tables, it is to be ensured that the ‘AutoNumber’ field is used minimum or it is not used.

Objective 2: Audit Trail

“An audit trail is a series of records of computer events, about an operating system, an application, or user activities. It is generated by an auditing system that monitors system activity.” (Source: Introduction ). Audit trails have many uses like Individual Accountability, Events Reconstruction, Problem Detection and Intrusion recognition in the field of Information security. As regards the audit trail to the ‘Orders’ table, two additional fields are to be appended into the ‘Orders’ table, first one is Audit_Rec_No (Type – AutoNumber) and the second is Audit_Rpt (Type – Memo). Appending two fields does not make any difference in the primary key for the table. Audit trail checks every entry of tables and stores reports based on that scrutiny.

Objective 3: Systems Documentation

Necessary documentation for the operation and maintenance of the Northwind Database system is very essential for the smooth functioning of this AIS. Relevance of documentation is that whenever the operating staff or any employee comes as a novice, he or she can go through the documentation and works as per the guidelines enclosed in it. Each and every information viz… Database designing, Numbers or Tables, Forms, Queries, Modules, Macros, etc. should be briefly described in the documentation. Detailed information regarding tables, their role in the database, and the relationship among each table should also be mentioned.

Updating techniques, if any, required for future reference may also be included in this part. The techniques of data flow and the functions of the database are to be displayed diagrammatically so as to enable the end-users to handle each and every function of the database. Dataflow diagrams show how data is passed through different processes and how the final reports are received. System flowcharts tell about the functioning of the entire database system which will be very useful for further redesigning of the database on the requirement. Document flowcharts and program flowcharts are also helpful for the effective functioning of the database system.

Objective 4: Database Queries

1. Select Top 10 products.productid, products.productname, suppliers.supplierid, suppliers.companyname, products.unitprice From Suppliers Inner Join Products on Suppliers.supplierid = Products.supplierid Group by Products.productid, Products.productname, Suppliers.supplierid, Suppliers.companyname, Products.unitprice Order by Products.unitprice Desc;
2. Select Distinctrow orders.orderid, First(suppliers.supplierid) As [First of Supplierid], First(Suppliers.companyname) As [First of Companyname], Count(*) As [Count of Suppliers] From Suppliers Inner Join (Products Inner Join (Orders Inner Join [Order Details] on Orders.orderid = [Order Details].Orderid) On Products.productid = [Order Details].Productid) On Suppliers.supplierid = Products.supplierid Group by Orders.orderid;
3. Select Distinctrow customers.customerid, customers.companyname, [Order Details].Productid as [First of Productid], Sum([Order Details]![Unitprice]*[Order Details]![Quantity]) As expr1, Count(*) As [Count of Customerid] From (Customers Inner Join Orders on Customers.customerid = Orders.customerid) Inner Join [Order Details] on Orders.orderid = [Order Details].Orderid Group by Customers.customerid, Customers.companyname, [Order Details].Productid;
4. Select categories.categoryid, categories.categoryname, products.productname, Sum([Order Details Extended].Extendedprice) As Productsales From Categories Inner Join (Products Inner Join (Orders Inner Join [Order Details Extended] on Orders.orderid=[Order Details Extended].Orderid) On Products.productid=[Order Details Extended].Productid) On Categories.categoryid=Products.categoryid Where (((Orders.orderdate) Between #1/1/1997# And #12/31/1997#)) Group by Categories.categoryid, Categories.categoryname, Products.productname Order by Categories.categoryname;
5. Select Distinctrow employees.country, employees.lastname, employees.firstname, orders.shippeddate, orders.orderid, [Order Subtotals].Subtotal as Saleamount From Employees Inner Join (Orders Inner Join [Order Subtotals] on Orders.orderid = [Order Subtotals].Orderid) On Employees.employeeid = Orders.employeeid.

Conclusions and Recommendations

The existing Accounting Information System of Northwind has some limitations as the table lacks in defining appropriate data types. If the database system is to function error-free and effectively, then it should be designed in a planned manner minimizing all the loopholes. Proper attention should be given while defining Primary Key and Foreign Key fields. By adopting the best techniques available in developing database components, a purposeful Database of Accounting Information System can be designed.