Database Management System Class 11 Notes CBSE Accountancy Chapter 15 (Free PDF Download)

Database management system (DBMS) 

A database management system is a program that helps in the management of a database containing a large set of organised data. It also assists in running operations on the stored data as required by the users of the stored data.

MS Access and Its Components 

One of the most popular DBMS used to create and manage a database is Microsoft Access. It is commonly called MS Access, or just Access. Access is used to create components. These components are objects, and a collection of such similar objects Forms a class. 

In MS Access, there are seven object classes having varied functions. These are described as follows:

1) Tables: This object class enables the designer of the database to create data tables with their respective field names, data types and properties. 

2) Queries: This object class helps to create the SQL compatible query statement, store data, and retrieve data and information. The SQL compatible query statement can be created with or without the help of a Graphic User Interface (GUI) for defining the tables. 

3) Forms: This object class enables the designer of the database to create an interactive user interface such that the users can interact with the back-end database, which is defined by tables and queries. 

4) Reports: This object class helps to create various reports according to the requirements of the user. Such reports are designed using the information based on the tables and queries. 

5) Pages: This object class helps to create the Data Access pages. These pages can be posted either on a web site, or sent through email to someone within the organisation’s network.

6) Macros: Macro-oriented actions are objects that use individual instructions in macro programming. These actions are manipulated in macro programming. The list of such macro-oriented actions that run as a unit is called a macro. Access is capable of macro programming.

7) Modules: This object class constitutes the foundations of an application, designed to enable the designer to create a set of programming instructions. These sets of instructions are called functions or subroutines and can be used throughout the application. 

Access Basics for Creating a Database 

When a new database is created from the scratch without the help of database wizard (that is an automated process in Access), the following steps are required: 

1) First, open the Access window, select the blank Access database, and click on the OK button. 

2) The File New Database dialog box appears. Enter the file name and location for the database, and then click on the Create button.

3) The task pane can be opened (if not opened already) by selecting File from the menu bar, and clicking new to open the task pane and create a new database. 

Creating of Tables in Access 

The understanding of the procedure and the components in Access are necessary to create desired tables. The following steps need to be followed to create tables in access.

In Access, click the Tables object, and then double click on create table by design view. This opens a table window. The upper part of the window has three tabs: Field Name, Data Type and Description. These can be used to define the particular schema of the table being created, where each row corresponds to a particular column. 

Two primary properties of the columns of a table are the field name and data type. These are described as below:

1) Field name

The field name refers to the name of the table that is being created. It is used to define the column that is being created, which is followed by the data type of that column.

2) Data Types

Numerous data types are supported by Access. These are described as below:

(a) Text: The text data type is used to define a string of characters, which includes words and numbers that are not required in any calculations. A maximum of 255 characters can be used in a text field.

(b) Memo: This data type is used to store comments. A maximum of 65,636 characters can be used in a field with this data type. However, if a field has this data type, then it cannot be amended to sort or filter data records.

(c) Number: The numbers data type is used to define numbers like integers, bytes, single, double (for storing values in decimal point with greater magnitude and precision), or decimal types. 

(d) Date/Time: The Date/Time data type enables storing dates, times, or both in the field. 

(e) Currency: The Currency data type is used to store numbers which define currency, such as Dollars, Rupees, Euros, etc.

(f) AutoNumber: This data type is the numeric data which Access automatically enters. This data type is especially important when the individual fields or their combination in a table is not unique.

(g) Yes/No: This data type is used to create a logical field, which contains only one of two opposite values. The opposite values may be Yes/No, On/Off, True/False, etc.

(h) OLE Object: The Object Linking and Embedding (ODE) object can be any photograph, barcode image, or document that is created in some other software application. 

(i) Hyperlink: The Hyperlink data type is used for storing the Universal Resource Locator (URL) and e-mail addresses. 

3) Properties

After specifying the data type of the column, Access enables the designer of the database to define the properties of each of the columns. These properties can be categorised as General and Look up. 

(a) General

The general properties in context of text and data type are: 

• Field Size: The field size property of a text field refers to the maximum number of characters that can be used in the column. The field size property of a number field refers to the type of numbers that are stored according to the requirements.

• Format: The Format property is used to define how the contents in the fields are displayed. In Access, there are certain standard Format types that can be used.

• Decimal places property: The decimal places property is the property applied to single, double, or decimal types of numbers. 

• Input mask: Input masks are the Formats used to enter data that includes placeholders or punctuations. Input masks can function only in case of text or data type of fields.

• Caption: The label used for the fields in the datasheet view, on Forms, or on reports is called caption. In case the caption property is set as blank, the default caption is used as the field name to label the field.

• Default Value: The default value property is used to specify the value for new entries in the data records. When a data item is entered, the default value can always be overwritten by the operator.

• Validation Rule and Text: The checking of data in order to eliminate the incorrect entries is called validation. For this property, the validation criteria can be specified.

• Required and Indexed: The Required property is used to indicate whether entering data in a field is mandatory or optional. This property provides a logical value Yes or No. If the property of a field is set to Yes, the user has to enter data in the field before saving the data record. If the property of a field is set to No, entering data in the field is optional.

• Allow-Zero Length: The Allow-Zero length property can be used only in case of text fields. This property provides a logical value Yes or No, which determines if a text spring having zero length is a valid entry or not.

(b) Look up

The lookup feature enables a field to find its values in any other table, query, or a particular list of values, which can be displayed using a list box or a combo box. The default display control of the lookup feature is a text box.

In the case of list box or combo box, some additional properties are used to specify the bound column, that is the column whose values are copied to this field as references. The column count property determines the number of columns that appear in the list box or the combo box.

• The steps mentioned to define a column have to be repeated to define every column that is to be created in a particular table.

• After the columns are defined, any of the columns that will have unique data values can be set as the primary key column of the table. This can be done by right clicking at the field which is to be specified as primary key, and then selecting the primary key item. 

• The table design can be saved by choosing the File option in the menu bar, and clicking on Save. The save dialog box displays a generic default name for the table, which can be edited as required. The table gets created and then appears as a list to the right of the table object. 

• The remaining tables in the database design can be created in a similar way. 

Database Design for Simple Transaction Vouchers 

The five data tables are: Employees, Accounts, Vouchers, Support and AccountType. These are described below in terms of their storage structure, i.e. column names, data types and properties.

1) AccountType

The AccountType table has two columns. These are CatId and Category. 

(a) CatId: This column specifies the value that identifies the category of accounts. 

(b) Category: This column is used for the storing of the string of characters. These express the category of accounts. For example: These express the account as Expenses account, Asset account, etc.

2) Accounts 

The Accounts table has three columns. These are Code, Name and Type. 

(a) Code: This column stores the code (the unique account number which identifies an account of the account. Since no calculations are required on this column, its data type is text.

(b) Name: The name of an account corresponding to the account code is stored in the name column.

(c) Type: Each account has to be defined as a particular account type in the AccountType table. The Type field acts as a foreign key to the reference CatId field of the AccountType table. 

3) Employees 

The Employees table is used for storing the data related to the employees in the organisation. This table contains the below six columns:

(a) EmpId: This column stores the EmpId of each employee, that is the unique data value assigned to identify each employee. 

(b) Fname: This column stores the first name of each employee. Since no calculations are required on this column, its data type is text.

(c) Mname: This column stores the middle name of each employee.

(d) Lname: This column stores the last name of each employee.

(e) PhoneNo: This column stores the phone number of each employee. Its data type is text with field size set to 12.

(f) SuperId: This column keeps and stores the EmpId of the supervisor or the immediate superior of the specific employee. 

4) Vouchers 

The Vouchers table is used for storing the data related to the transactions as written in the vouchers. This table contains the following nine columns:

(a) Vno: This column stores the store voucher number of each voucher, that is the distinct identity of each voucher. Its data type can be set to number if numeric digits are to be assigned to the vouchers.

(b) Debit: This column stores the code corresponding to the account which is debited while recording the transaction. 

(c) Amount: This column stores the amount credited or debited to the accounts in the transaction. 

(d) Vdate: This column stores the date of the transaction. 

(e) Credit: This column stores the code corresponding to the account which is credited while recording the transaction.

(f) Narration: This column stores the narration describing the transaction in the voucher. Its data type can be set to text with a field size set to 100.

(g) PrepBy: This column stores the identity, that is the EmpId of the employee (as described in the Employees table) who has prepared the voucher.

(h) AuthBy: This column stores the identity, that is the EmpId of the employee (as described in the Employees table) who has authorised the voucher.

(i) Support: This column stores the details related to the support documents attached to a voucher.

Modified Design for Implementing Compound Vouchers

1) Vouchers Main

This table stores one record for each transaction. The rows of this table include the data items of the vouchers that lie outside its grid. It includes six columns, that is Vno, AccCode, vdate, PrepBy, AuthBy and Type. 

• AccCode: This column stores the code of the complementing account. In case of a debit voucher, the complementing account is the credit account. Similarly, in the case of a credit voucher, the complementing account is the debit account.

• Type: This column stores the value 0 in case of a debit voucher, and the value 1 in case of a credit voucher. Its data type is set to Number with a field size set to byte.

2) VouchersDetail

This table keeps and stores the data items in the vouchers that come into view within the grid of the debit or credit voucher. It does not store the total amount because the total amount is derived data, calculated by adding the written amounts. It includes five columns, that is Vno, Sno, Code, Amount and Narration.

• Vno: This column stores the voucher number of the debit or credit record of the VouchersMain table to which the entries of the vouchersDetails table relate.

• Sno: This column stores the serial numbers 1, 2, 3 and so on. These serial numbers correspond to specific debit or credit entries that appear within the grid of the debit or credit voucher.

• Code: This column stores the code of the account. In case of a debit voucher, the account is the debit account. Similarly, in case of a credit voucher, the account is the credit account.

Vouchers Using Forms 

The following section involves the creation of vouchers using Forms, transformation of the voucher designs in terms of Access objects and properties, and specifies the procedure for the creation of Forms for vouchers. 

Access Basics for Creating Forms 

Some of the purposes for which a Form is designed, developed, and used in Access are:

1) Data Entry: A Form can be designed and used to enter, edit, and display data. 

2) Application flow: A Form can be designed and used to navigate through an application.

3) Custom Dialog Box: A Form can be designed and used to provide certain messages to the user, or to receive parameters from the user, which are required to execute a query based on parameters.

4) Printing information: A Form can also be used to print information and thus, provide hard copies of information related to data entry.

Tool Box and Form Controls 

A tool box contains a number of visual objects or controls that are embedded on a Form to provide meaning and functionality to the Form. Several controls having distinct functionalities and properties are used to design a Form.

Properties of Controls 

Each control in the tool box used to design the Form is a complete object having distinct functionalities and properties. These properties define the shape, size, behaviour, and functionality of the object. The properties are categorised into format, data, and other properties, which are described below:

1) Format Properties

• Format: This property is used to define the manner in which the data is to be displayed in the control. This property is obtained from its underlying data source. 

• Caption: This property specifies the printed matter that appears on the face of the control. It applies to labels, command buttons, and toggle buttons. 

• Visible: This property is used to specify whether the controls placed should appear or be hidden when the Form is opened. 

• Fore Colour: This property is used to assign any colour to the text that is being formatted.

• Layout Properties (Left, Top, Width, Height): The Layout properties are used to decide the size and position of the controls that are embedded to the Form.

2) Data Properties 

• Control Source: This property is used to specify the field from the record source associated with the specific control. This property specifies the record source underlying the Form by default.

• Input Mask: This property is meant to affect the data entry format used in the control.

• Default Value: This property is used to provide the values assigned to the fields when new data records are added. It is obtained from the underlying field of record source with which the control is bound. 

• Validation (Rule and Text): This property is used to perform the validation function at Form level in case of controls. 

• Enabled and Locked: This property determines whether focus is permitted on the control or not. If this property is set to No, then the control appears dimmed and no mouse action can be performed on that control.

3) Other Properties 

• Name: This property is used by a designer to provide a customised name to a particular control. The name that is assigned must be purpose oriented such that the structured Form becomes self-documenting.

• Status Bar Text: This property is used to specify the text messages displayed in the status bar when focus is permitted on a control.

• Enter Key Behaviour: This property determines the function of the Enter key. The Enter key can be used either to add a new line to the current control, or move the cursor to the next control.

• Allow AutoCorrect: This property can be set to Yes to turn on the auto correction feature. This feature corrects common spelling errors and types automatically, and is used when the Text control for Memo field is being used.

• Vertical: This property determines the direction of the text in the control. If set to No, the text appears horizontally. If set to Yes, the text in the control appears vertically, that is rotated at 90 degrees.

Common Controls in MS Access 

There are a number of controls in Access that can be used to design a Form. More controls can be embedded to the Form using the add-in manager in Tools of menu bar. The three types of controls are: Bound, unbound and calculated. 

Some common controls essential for designing a Form are described as follows: 

1) Label: This control is used for writing dark prints on the Form. Some dark prints written using this control include Transaction Voucher, Voucher No, serial number, Debit, Credit, Amount, Narration, Authorised By, Prepared By, etc.

2) Text Box: This control is used to add a blank area which can be used to enter data. For example: A blank space added next to an amount label can be used to receive the value of the amount of the voucher.

3) List Box: This control allows a user to choose from a limited set of values. For example, the domain of the values is limited and predefined.

4) Combo Box: This control allows a user to choose an item from a list, or enter a value using a keyboard. Therefore, this control features a combination of the features of the text box and list box. 

5) Sub-Form: Many forms are generally based on more than one table. The Subform is a Form within a Form used to tabulate and present the records of such tables.

Creating Tables in Access 

In Access, a table refers to an organized structure that holds information. It includes fields of information in which records of the table are entered. A record refers to the collection of related fields that describe a single item. A field refers to the column that consists of one category of information.

The tables in Access can be created by clicking the Create tab within the Design view in the Ribbon, and then clicking on the Table Design button in the Tables group. This displays a table in the area containing the tabbed documents. 

When in Design View, only the representation of the structure of the table, and not the actual data stored in the table can be seen. This provides more control over the field properties to the designer as compared to when using the Datasheet View for creating a table.

In the window, there are two panes. The top contains a design grid used to enter names of the fields and data types. The design grid also contains a small box called the row selector button, which can be clicked to select the entire row. Below the design grid, there is a properties section.

Create Tables in Access – Tutorial

The following picture depicts a new table being created by a user. It also shows the user assigning a data type to a particular field in the Design View in Access 2016. 

(Image will be uploaded soon)

In Design View, tables can be created by typing the unique, brief, and descriptive field names in the Field Name column. The field name should not contain spaces. Capitalizing the first letter of each word, or an underscore can be used to separate the words within the field name without adding spaces. 

The order in which the fields appear is the same order that appears from left to right in the Datasheet View of the table. 

In the next step, data types must be assigned to each field using the drop-down that appears when the “Data Type” column to the right of the field name is clicked. The data type tells Access the type of data that will be stored in the field. 

In relational databases, it is quicker to index and query the tables if many varied kinds of data types exist in the table. The data types can be changed and reviewed when assigning to the fields.

Field Data Types You Can Assign When You Create Tables in Access Data Types in Access 

1) Short Text: This data type includes text or combination of text, numbers, or some other information. The maximum length of text that can be added in this data type is 255 characters.

2) Long Text: This data type includes text or combination of text, numbers, or some other information. The maximum length of text that can be added in this data type is 65,535 characters.

3) Number: This data type includes numeric data on which calculations are to be performed. This does not include phone numbers or zip codes because no calculations are performed on such numeric data.

4) Date/Time: The Date/Time data type enables you to store dates, times, or both in the field. 

5) Currency: The Currency data type is used to store numbers formatted as currency, such as Dollars, Rupees, Euros, etc.

6) AutoNumber: This data type assigns a unique numeric ID to the records entered in the table. A field having this data type can be used as the primary key field.

7) Yes/No: This data type is used to create a logical field, containing only two possible values in the field, such as Yes/No, On/Off, True/False, etc.

8) OLE Object (Desktop Only): This data type connects to objects in other software applications, such as photographs, barcode image, or document. It can only be used in Desktop database files. 

9) Hyperlink: This data type contains the hyperlink to an address on the World Wide Web, such as the URL or e-mail addresses. 

10) Attachment (Desktop Only): This data type can be used to attach any type of supported file like image, spreadsheet, etc. It can only be used in Desktop database files, and gives more flexibility than the OLE object field. 

11) Calculated: This data type creates a calculated field. The values in these fields are derived as a result of a function on other table fields.

12) Lookup Wizard: This data type instructs how to set up a lookup field, which will contain values from another table, query, or list of values.

13) Image (Web App Only): This data type is used to store data in web apps.

Create Tables in Access – Tutorial: 

The following picture displays the field names, data types, and a primary key of a table in Design View in Access.

(Image will be uploaded soon)

The Field Properties section is below the design grid. This section can be used to set the properties of the selected field. The properties are displayed in the General and Lookup tabs, and can be edited or set by changing the values. 

After finishing the creation of the table, the primary key for the table needs to be set. This key identifies each table record as unique. Generally, the primary key field is there naturally in the data. In case the field is not there, the AutoNumber field can be added to the table.

To set a particular field as the primary key, select the row selector, click on the Design tab under Table tools in the ribbon, and then click on the Primary Key button in the Tools group.

Finally, save the table using the Save button in the Quick Access toolbar. In the save as dialog box, set a name for the table, and click on the OK button. 

Create Tables in Access Using “Design View”: Instructions 

1) Click on the Create tab in the Ribbon. 

2) In the Tables group, click on the Table Design button. 

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

4) Enter the name of the field into the Field Name column. 

5) Press the Tab key to shift the cursor to the next column to the right. 

6) Next, assign data types to the fields by using the drop-down menu in the Data Type column. 

7) Press the Tab key to shift the cursor to the Description column.

8) Enter a description of the data that is stored in the field. It is not necessary to enter a description if not required.

9) Press the Tab key to shift the cursor down to the next row.

10) Perform steps 4 through 9 again until all the necessary table fields have names, data types, and descriptions as required. 

11) Next, select the row selector containing the field that is to be set as the primary key for the table. 

12) In the Table tools tab in the ribbon, click on the Design tab. 

13) Click on the Primary Key button in the Tools group. 

14) Save the table by clicking on the Save button in the quick access toolbar. 

15) Add a name to the table in the dialog box that appears. 

16) Click the OK button.