NCERT Class 11 Accountancy Accounting System Using Database Management System

Accounting System Using Database Management System

In chapter 14, you have learnt about the fundamentals of creating a database design in the context of accounting system. This chapter deals with the basics of MS Access for implementing the  databases and specifically deals with implementationof accounting databases, the design of which has been shown, described and discussed in chapter 14 as Model-I and Model-II. The accounting database design has been discussed below in terms of its implementation modalities in the context of MS Access. 

15.1 MS Access and its Components 

It is one of the popularly used Database Management System (DBMS) to create, store and manage database. It is also popularly called ACCESS. Every component that is created using Access is an object and several such similar objects constitute a class. Access is functionally available with the following seven-object classes. Each of these object classes is capable of creating their respective object replicas.

• Tables : This object class allows a database designer to create the data tables with their respective fieldnames, data types and properties.

• Queries : This object class is meant to create the  SQL compatible query statement with or without the help of Graphic User Interface (GUI) to define tables, store data and retrieve both data and information. 

• Forms : This object class allows the designer to create an appropriate user interface to formally interact with the back end database, defined by the tables and queries.

• Reports: This object class is used to create various reports, the source of information content of which is based on tables, queries or both. Such reports are designed in Access according to the requirement of end-user.

• Pages : This object class is meant to create Data Access Pages, which can be posted on a Web site of an organisation using Internet or sent via e-mail to someone of the organisation’s network.

• Macros : In macro programming, the objects using individual instructions called macro-oriented actions are manipulated. A Macro is a list of macrooriented actions that run as a unit. Access provides for such Macro programming.

The functions return a value while subroutines do not return any value. Access provides for creating such modules. Each of these object classes is contained in the named database file of Access with MDB extension. Whenever this file is opened, a database window, as shown on next page, opens with all the above object classes available on the left hand side. As and when the specific objects are created or designed, they get listed on right hand side of this window against each of these object classes.

15.1.1 Access Basics for Creating a Database

When a new database is created from the scratch, there is complete control over the database objects, their properties and the relationships. In order to create a new database without the help of database wizard (that is an automated process in Access), the following steps are required :

(i) Open Access Window to choose blank Access database and click OK button. 

(ii) Access responds by displaying File New Database dialog box, which prompts the designer to enter a file name and a location for the database. This must be followed by clicking Create button.

(iii) If the task pane is not open, choose File from menu bar and click at new to open the task pane to create a new database.

15.1.2 Creating of Tables in Access

The creation of tables in Access requires the following steps and understanding of the components of table object. Click at Tables object of Access, followed by double click at create table by design view. This results in providing a table window, the upper part of which has three columns: Field Name, Data Type and Description. It is meant to define the schema of a table being created. Each of its rows corresponds to a column of the table being created. Two primary properties of the column of a table are its field name and data type.

(a) Field name : refers to column name of the table being created. The name of the column should be a string of contiguous characters. The Field name is meant to define the name of column to be created, followed by data type of such column. The designer can optionally provide description of the column also. Once the data type is defined, the designer can further specify the properties of each column in the lower part of the Table window.

(b) Data Types : Access supports different data types, the details of which are as given below :

• Text : It is used for a string of characters: words or numbers that are not to be used in any arithmetic calculations. The maximum length for a text field is 255 characters. It is the default data type because of being used most frequently.

• Memo : It is used for storing comments and is capable of accommodating 65,536 characters. But a field with this data type is not amenable to sorting or filtering of data records.

• Number : It is meant to store numbers, which could be integers (-32768 to 32767), long integers (–2,147,483,648 to 2,147,483,647), bytes ( 0 – 255), single (to store values with decimal point up to a certain limit), double (to store values in decimal point with greater magnitude  and more precision) or decimal types.

• Date/Time : It is used to store dates, times or a combination of both.

• Currency : It is used for storing numbers in terms of Dollars, Rupees or other Currencies.

Questions for Practice

Short Answers

1. State what do you understand by accounting reports.

2. What do you mean by programmed or casual reports?

3. With the help of an example, briefly state the meaning of parameter queries.

4. Briefly state the purpose of functions in SQL environment.

5. Briefly explain in steps the method of creating a query, using wizard.

6. List the structure of a good report created in Access.

7. List the ways to refine the design of a report.

8. Briefly explain the purpose of grouping and sorting of the data as a means to refine a report.

9. What do you understand by saving a report as snapshot?

10. State the procedure for creating ledger in MS Access.

Long Answers

1. Describe and discuss the procedure of creating the receipts side of a cash book.

2. Discuss the concept of accounting reports? Explain the three steps involved in creating such reports.

3. Discuss with a set of inter-related data tables, the basics of creating queries in MS Access?

4. Briefly explain the set of SQL statements to produce the receipts side of a cash book for Model-I.

5. Describe in steps the design view method to create a query in MS Access?

6. Discuss the SQL view method of creating a query?

7. Describe the ways to refine the design of a report.

8. Explain the data base design for Model-I for producing the receipts the series of SQL statements for producing the payment side of cash book for Model-II.

9. Describe the series of SQL statements to produce trial balance data base design for Model-II is used.

10. Using Model-III discuss the series of SQL statements to produce a trial balance up to a particular date.

Project Work

1. Payroll Accounting: Using the database design given in Exercise of Chapter-IV, as Project No: 1, you are required to generate the portion of payroll according to the specified format under MS Access environment.

2. Financial Accounting: Write the SQL statements for each of the following  queries separately by using database design of accounting specified as Model I, II and III in Chapter-IV.

   (a) List the transactions details of Accounts, which have been debited during the period April 01, 2001 to September 30, 2001.

   (b) List the transactions details of accounts which have been credited during the month of August 2001.

   (c) Find the total expenses incurred during the period September, 2001.

   (d) List all the transacted accounts with the amounts by which they have been debited and also the amount with which they have been credited.

   (e) List the amount of expenses authorised by each of the employees.

Please refer to attached file for NCERT Class 11 Accountancy - Accounting System Using Database Management System