Accounting Database for Database Management System Chapter Notes | SSC CGL Tier 2 - Study Material, Online Tests, Previous Year PDF Download

CHAPTER-15 

ACCOUNTING SYSTEM USING DATABASE MANAGEMENT SYSTEM 

- Database management system (DBMS):

 It is a computer program (or more typically, a suite of them) designed to manage a database, a large set of structured data, and run operations on the data requested by numerous users. 

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

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

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

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

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

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

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

7) Modules: These are the foundations of any application and allow the designer to create a set of programming instructions, called functions or sub-routines that can be used throughout the application. 

- 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: 

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

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

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

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

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

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

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

• AutoNumber: It is a numeric data automatically entered by Access. It is of particular importance in a situation where none of the fields individually or a set of fields as a combination in a table is unique. 

• Yes/No: It is to declare a logical field which may have only one of the two opposite values alternatively given as: Yes or No, On or Off, True or False. 

• OLE Object: OLE stands for Object Linking and Embedding. It refers to an object that could be a photograph, bar code image or another document created in another software application. 

• Hyperlink: This data type is meant to store a Universal Resource Locator (URL) and e-mail addresses. 

3) Properties: Once the data type of a column is specified, Access allows the designer to define the properties of each column. These properties are of two types General and Look up. 

a. General: In the context of text data type the general properties are: 

• Field Size: This property, in case of text fields, refers to the maximum number of characters allowed in the column. The same property, in case of numbers, refers to the type of numbers being stored as per requirements. 

• Format: It is meant to indicate as to how the field’s contents are displayed. There are standard types of formats to choose from. 

• Decimal places property: It applies to single, double or decimal types of numbers. 

• Input mask: Formats for data entry that include placeholders and punctuations are called input masks. It works only for text and date type of fields. 

• Caption: It is a label used for the field in datasheet view and on the Forms and reports. If the caption property is set to blank, the field name becomes the default caption and is used to label the field. 

• Default Value: It is used for specifying a value for new entries of data records. While entering the data item, the operator can always over write the default value. 

• Validation Rule and Text: Validation means checking of data to eliminate incorrect entries. Validation criteria can be specified for this property. 

• Required and Indexed: The Required property must be provided a logical value Yes or No. When a field’s required property is set to Yes, a user must enter data in the field before saving the record. A value of No implies that the data entry in the field is optional. 

• Allow-Zero Length: This property is available only for text fields. Setting it to Yes/No determines whether a text string with zero length is a valid entry or not. 

b. Look up: The look up feature is used by a field to find its values in another table, query or from a fixed list of values. A list of valid values can be displayed using a list box or combo box. Text box is the default display control of look up. Look up is created in case of a field, which is foreign key (many side) into primary key (one side) between the tables that have one-to-many relationship. 

Some additional properties in case of list box or combo box are meant to specify the bound column whose values are copied to this field as references. Number of columns to appear in the list box or combo box is determined by column count property. 

• The above steps for defining a column need be repeated for every column to be created for a particular table. 

• After defining all the columns of the table, the primary key column of the table can be specified as any of the columns that are expected to have unique data values. This can be achieved by right clicking at the field to be specified as primary key followed by primary key item of right clicked window. 

• Save the table design by clicking at File item of menu bar followed by click at Save option. Access responds by providing a generic default name of table. The table name provided by Access may be accepted by clicking at OK or changed by re-typing another name at the input dialog box. This must be followed by clicking OK. The table stands created and appears as listed to the right of table object. 

• Every other table, which constitutes part of the database design, may also be created in the same manner as described above. 

- Database Design for Simple Transaction Vouchers:

There are five data tables: Employees, Accounts, Vouchers, Support and AccountType. For the purpose of implementation, each table is described below in terms of their storage structure, i.e. column names, data types and properties: 

1) AccountType: This table has two columns: CatId and Category. 

a. CatId: This column of the Account Type table is meant to specify the identification value of the category of accounts. 

b. Category: This field is meant to store the string of characters to express the category of account such as Expenses, Revenues, Assets and Liabilities. 

2) Accounts: This table has three columns: Code, Name and Type. 

a. Code: A unique account number or code identifies an account. This column is meant to store this code. Its data type is chosen as Text because it is not to be subjected to any calculations. 

b. Name: In a system of accounting, every account has a name. This column is meant to store the name of an account corresponding to the account code by which it is identified. 

c. Type: Every account must belong to one of the accounts type as stored in AccountType table. This field is a foreign key to reference CatId field of AccountType table. 

3) Employees: This table stores the data pertaining to employees of the organisation and is designed to have following columns: 

a. EmpId: Each employee is identified by a unique data value called EmpId, which in turn gets reflected in employee table as a column to store for each employee record a unique identification value. 

b. Fname: This column refers to the first name of employee and its data type is declared as Text because it is meant to store string of alphabets. 

c. Mname: Mname column is meant to store the middle name of an employee. 

d. Lname: Lname column has been included in the table structure to store the Last name of an employee. 

e. PhoneNo: This column is meant to store the Phone number of the employee and its data type is set to Text with field size equal to 12. 

f. SuperId: This column in the Employee table structure refers to EmpId of the supervisor or immediate superior of the employee. 

4) Vouchers: This table has been designed to store the transaction data as contained in a voucher. It has nine columns, the details of each are given below: 

a. Vno: This column is meant to store voucher number, which indicates the distinct identity of a transaction. Its data type could be number if numeric digits are assigned to each of the vouchers. 

b. Debit: This column is meant to store the code corresponding to an account, which has been debited in recording a transaction. 

c. Amount: This column is meant to store the amount of transaction and is common to the accounts being debited and credited. 

d. Vdate: This column of the table stores the date of transaction. 

e. Credit: This column is meant to store the code corresponding to the account being credited in recording a transaction. 

f. Narration: This column is meant to store the narration. Its data type can be set to text type with field size set to 100 characters. 

g. PrepBy: This column is meant to store the identity of an employee who has prepared the voucher. EmpId as defined and described in schema of Employees table identifies the employee. 

h. AuthBy: This column is meant to store the identity of the employee who has authorised the vouchers. 

i. Support: This table is created to store the details of support documents annexed to a voucher. 

- Modified Design for Implementing Compound Vouchers 

1) Vouchers Main: This table has been created to store one record for every transaction. The rows of this table refer to those data items of the vouchers, which lie outside the voucher grid. It consists of Vno, AccCode, vdate, PrepBy, AuthBy and Type. 

• AccCode: This column is meant to store the complementing account code, which in the context of debit voucher is credit account and in the context of credit voucher is a debit account. 

• Type: This column has been created to store a value 0 (for debit voucher) or 1 (for credit voucher). Its data type therefore is set to Number with field size set to byte. 

2) VouchersDetail: This table is meant to store those data items of the voucher, which appear in the grid of debit or credit vouchers. However, the Total amount of voucher is not stored because it is derived data. It consists of Vno, Sno, Code, Amount and Narration as its columns. 

• Vno: This column is meant to store voucher number of Debit/ Credit record of VouchersMain table to which the Credit/Debit entries of vouchersDetails table are related.

• Sno: This column has been included in the table structure to store serial numbers 1,2,3… to correspond to the serial number of debit/credit entries being referred to in the grid of an accounting Voucher: Debit or Credit. 

• Code: This column is meant to store the account codes, which in the context of debit voucher are debit accounts and in the context of credit voucher are credit accounts. 

- Vouchers Using Forms:

 The scope of this section includes the basics of Access for creating a Form in Access; transforming the voucher designs in terms of Access objects and properties; and also the procedure for creating Forms for vouchers. 

- Access Basics for Creating Forms:

 A Form in Access may be designed, developed and used for the following purposes: 

1) Data Entry: Form is used for entering, editing and displaying data. 

2) Application flow: Form is used for navigating through an application. 

3) Custom Dialog Box: It can be used for providing messages to the user or getting parameters from the user for executing a parameter-based query. 

4) Printing information: It can be used for providing hard copies of data entry information. 

- Tool Box and Form Controls:

 A tool box is a collection of visual objects (or controls) that are placed (or embedded) on the Form to provide some meaning or functionality. The Form is designed by placing several such controls, which have their own functionality and properties. 

- Properties of Controls: Every form control is a complete object with its independent set of properties, which determine the shape, size, behaviour and functionality of the object. The properties of these objects are divided into three categories: Format, Data and Others. All these properties may not apply to all the controls. Some important properties of these objects are as described below: 

1) Format Properties: Some of the important properties are as described as under: 

• Format: It determines the manner in which the data in the control is displayed. This property is inherited from its underlying data source. 

• Caption: The caption property applies to label, command button and toggle buttons. This property is used to specify what printed matter will appear on the face of the control. 

• Visible: This property specifies whether the control embedded on the Form should be visible or hidden when the Form is opened. 

• Fore Colour: This property can be used for assigning a colour of choice to the text being formatted. 

• Layout Properties (Left, Top, Width, Height): These properties are used to set the position and size of the control. 

2) Data Properties: 

• Control Source: This property specifies the field from a record source that is associated with particular control. By default, it is the record source that underlies the Form being designed. 

• Input Mask: The input mask property affects the format used for data entry into the control as opposed to its appearance, which is affected by Format and Decimal places property. 

• Default Value: This property determines the value assigned to the field while adding a new data record. It is inherited from the underlying field of record source to which the control is bound. 

• Validation (Rule and Text): The function performed by Validation Rule and Validation Text for controls is the same as it applies to Fields of database tables, except that the validation is performed at Form level in case of control and database level in case of fields. 

• Enabled and Locked: This property is meant to determine whether focus is allowed on the control or not. If it is set to No, the control appears dimmed and mouse action cannot be performed on such control. 

3) Other Properties:

 • Name: This property allows the designer to provide a customised name to a control. The names assigned by the designer should be purpose oriented so that the design structure of the Form becomes self-documenting. 

• Status Bar Text: This control specifies the text message that is displayed in the status bar when the control acquires the focus.

• Enter Key Behaviour: This property is meant to determine whether the use of Enter key adds a new line in the current control or results in moving the cursor to next control. 

• Allow AutoCorrect: This property, when set to Yes, enables the auto correction feature to correct automatically common spelling errors and types. It is useful while using Text control for Memo field. 

• Vertical: This property is meant to determine whether the text in a control appears horizontally or vertically. The default setting is No to mean the horizontal. When set to Yes, the text within the control is rotated at 90 degrees. 

- Common Controls in MS Access:

 Access provides for a number of controls and more can be added using the add-inmanager in Tools of menu bar. There are three types of controls: Bound, unbound and calculated. Some of the common controls important for designing a Form are discussed below: 

1) Label: This control is used to write dark prints on the Form such as Transaction Voucher, Voucher No, S.No, Debit, Credit, Amount, Narration, Authorised By, Prepared By on the left hand side and “Choose the Account to Debited” and “Choose the account to be Credited” on the right hand side of Access voucher Form design. 

2) Text Box: This control is included in a Form to provide a blank area for entering the data with or without default values. Blank space next to Amount label, for example, is a text box control to receive the value of amount of voucher. 

3) List Box: This control is used for allowing a user to make a limited choice from a given set of values. The domain of its values is predefined and therefore limited. 

4) Combo Box: This control combines the features of a list box and text box by allowing a user to select an item from a list or enter a value using the keyboard. 

5) Sub-form: Many Forms are based on more than one table with One-toMany relationship. The records of such tables can be displayed by creating form within a form, with tabular presentation of records. The Form within a Form (also referred to as Main Form) is called SubForm.

- Creating Tables in Access:

 This lesson shows you how to create tables in Access. A table is an organized structure that holds information. It consists of “fields” of information into which you enter the “records” of the table. A field is a single column within a table, consisting of one category of information. A record is a collection of related fields that describe a single item, contained in a row within a table.

 One way to create tables in Access is by creating the tables in “Design View.” To create tables in Access in “Design View,” click the “Create” tab in the Ribbon. Then click the “Table Design” button in the “Tables” group. Doing this then shows the new table in the tabbed documents area. 

In “Design View,” you will not see the actual data stored in your table. You will only see a representation of the structure of the table. This gives you more control over the properties of the fields versus using “Datasheet View” to create a table. 

This window is divided into two panes. First, there is the “design grid” at the top, where you enter field names and data types. Then there is the “properties” section beneath it. In the “design grid” at the top of the table design view, there is a small box at the far left end of each field. This is the “row selector” button. You can click this small square to select the entire row. You need to do this frequently in Access, so note the location of this object 

Create Tables in Access – 

Tutorial: A picture of a user creating a new table and assigning a data type to a field in Design View in Access 2016. 

In “Design View,” you create tables in Access by typing the field names into the “Field Name” column. Field names must be unique within a table, and should be brief, yet descriptive. You should also consider not placing spaces within the field names. If you want, you can adopt a convention such as capitalizing the first letter of each word in a field name, or using the underscore character instead of a literal space between words in a field name. Also, the order of fields in this column is the order they appear from left to right in “datasheet view” of the table. “Datasheet view” is the view that lets you see the actual data in the tables. 

Next, for each field you create when you create tables in Access, you must assign it a data type. To do this, use the drop-down that appears when you click into the “Data Type” column to the right of the field name. When you create tables in Access, each field in the table must have a data type assigned to it. This tells Access what kind of data you will be storing in the field. “Short Text” is default data type for new fields in Access 2013 or later. In relational databases, the more varied kinds of data types that exist within a table, the quicker it is to index and query those tables. So, feel free to change the type, as needed. Review the various data types you can assign to fields in tables. 

Create Tables in Access –

Tutorial: A picture of a table in Design View in Access, showing field names, data types, and a primary key. 

Below the design grid is the “Field Properties” section where you set the properties of the currently selected field in the table. In this area, the properties of the currently selected field are displayed the “General” and “Lookup” tabs. You can edit or set the field’s properties here by changing values shown, as needed. 

When finished, you simply need to set the primary key for the table and then save it. The primary key field is the field that uniquely identified each table record. Often, this field exists naturally in the data. If not, you can add an “AutoNumber” field to a table to create a primary key field. To mark a field as a primary key field, click the row selector at the left end of the row that contains the field you want to set as the “primary key” for the table. Then click the “Design” tab within the “Table Tools” contextual tab in the Ribbon. Then click the “Primary Key” button in “Tools” group. 

To save the table when you are finished, click the “Save” button in the Quick Access toolbar. Then type a name for the new table into the dialog box that appears. Then click the “OK” button. 

- Create Tables in Access Using “Design View”: Instructions 

1) To create tables in Access using “Design View”, click the “Create” tab in the Ribbon. 

2) Then click the “Table Design” button in the “Tables” group. 

3) A new table then appears in the tabbed documents area. 

4) Type the name of a field into the “Field Name” column. 

5) Then press “Tab” on your keyboard to move to the next column to the right. 

6) Then use the drop-down menu in the “Data Type” column to assign the field a data type.

7) Press “Tab” on your keyboard to move to the “Description” column. 

8) If desired, type a description of the data stored in this field. 

9) Then press “Tab” on your keyboard to move down to the next row. 

10) Repeat steps 4 through 9 until you have created all of the necessary table fields. 

11) Click the row selector at the left end of the row that contains the field you want to set as the “primary key” for the table. 

12) Click the “Design” tab within the “Table Tools” contextual tab in the Ribbon. 

13) Then click the “Primary Key” button in “Tools” group. 

14) Click the “Save” button in the Quick Access toolbar. 

15) Then type a name for the new table into the dialog box that appears. 

16) Then click the “OK” button.