Structuring Database for Accounting

This page contains the CBSE accountancy class 11 chapter Introduction to Accounting notes. You can find the questions/answers/solutions for the chapter 1 of CBSE class 11 accountancy in this page. So is the case if you are looking for CBSE class 11 Commerce related topic Introduction to Accounting

Test Your Understanding
  1. Indicate against each of the following statements, True or False :
    1. Every relation has at least one super key by default, which is the combination of all its attributes. (T)
    2. Data transformation is called Information. (T)
    3. Referential integrity constraint arises because of relationships between various entities. (T)
    4. The complete absence of WHERE clause in SELECT statement implies that no tuples of a relation shall be selected. (F)
    5. ER model is an example of representational data model. (F)
  2. Fill in the blanks, an appropriate word(s)
    1. A ……….. does not have key attributes of its own. (Weak entity)
    2. The ……….. for binary relationship specifies the number of relationship instances that an entity can participate in. (Cardinality Ratios)
    3. Each simple attribute of an entity type is associated with a value set called ……….. of values. (Domain)
    4. When structure of AIS is based on both human and computer resources, it is called ……….. AIS. (liveware)
    5. An ……….. is a collection of all entities of a particular entity type. (Entity Set)
    6. A weak entity type always has a ……….. constraint with respect to its identifying relationship. (total participation constraint (existence dependency))
    7. When a relation has more than one attribute with unique values, each such attribute is called ………… (candidate key)

Short Answers
1. State main categories of data models.
The following are the main categories of data models.

  1. Relational model: It represents the data as relations.
  2. Network model: It represents the data as record types.
  3. Hierarchical model: It represents the data as hierarchical tree structure.
2. How are computers useful in processing the accounting data?
Data processing involves the technique of

  1. Collecting
  2. Sorting
  3. Relating
  4. Interpreting
  5. and computing data items

in such a manner as to provide useful and meaningful information for decision-making. This is achieved through the following steps.

  1. Data capturing
  2. Inputing
  3. Processing
  4. and generating information for the user.

In computerised accounting, this is achieved through

  1. identifying
  2. storing
  3. and retrieving of data content

of an accounting transaction. The machanism should allow the storage so as to permit easy and convenient retrieval of data as and when required. To achieve this, a suitable database is designed for accounting related activities. This database consists of inter-related data tables. The design is structured to ensure data consistency and integrity. Moreover, the data processing cycle is more efficient and reliable and available round the clock. In this way, the computers are very useful in processing of the accounting data.

3. What do you understand by accounting data? Discuss the stages through which it is finally transformed for being presented as information in financial statements.
Accounting data refers to the data gathered from the financial transactions and events. Such data should be financial in nature and is processed for generating meaningful and useful reports for the user so as to help them in the decision-making process.
It goes through the following stages through which it is finally transformed for being presented as information in financial statements.

  1. Source Documents: The accounting data is captured in the vouchers that are designed to allow recording of this data in a systematic manner.
  2. Input of Data: The data is input into the Data Entry Form that is designed to appear similar to the vourcher.
  3. Data Storage: In order to store the data, a data storage structure or data table is created during the structuring of the database for accounting.
  4. Manipulation of data: The stored data is manipulated to transform it to generate final reports. This data can be directly presented in the form of a report or it can be stored separately for generating the reports later on.
  5. Output of Data: The transformed data can be accessed in the form of accounting reports such as ledger, trial balance etc and viewed in a pre-designed format.
4. What do you understand by database. How does it differ from DBMS?
A database is a shared collection of inter-related data tables (also called as data storage structures), files or structures that are designed to meet the various informational needs of an organisation. The data tables are used for storing the data. These data tables are structured in such a way that they ensure data consistency and integrity. A database is thus a collection of related data that represents some aspect of the real world (also called mini-world or reality). A database has two important properties or characteristics as specified below.

  1. Integrated property: This implies that distinct data tables have been logically organised. This ensures that the redundancy (or duplicity) is reduced or eliminated. It also facilitates better data access.
  2. Shared property: This implies that only those users that are authorized to use the data/information have access to the relevant data.

On the other hand, DBMS is a collection of programs that enable users to create and maintain a database. It can be defined as the general-purpose software system that facilitates the processes of

  1. defining
  2. constructing
  3. and manipulating (or processing)

databases for various applications.

5. What is meant by entity type? How it is different from entity set? Illustrate by giving suitable example from accounting reality.
An entity type is a collection of entities that share a common definition interms of their attributes. Every entity type is assigned a name to facilitate its identification. The set of attributes, also called as schema, are used to describe the entity type in the database.
An example of entity type is as described below.
Entity Type: Accounts
Intension (or structure) of entity type

Code Name Type

An entity set is a collection of all entity instances of a specific entity type.
An example of entity set is as described below.
Entity Set: Collection of entity instances of an entity type “Accounts”
Intension (or structure) of entity type

111111 Capital Account 4
222222 Jain & Co 3
333333 Jayram Bros 2
6. What do you understand by relationship type? How is it different from relationship instance and relationship set?
Relationship type refers to the manner in which the entities from differet entity types are related to one another.
Relationship instance refers to the association between two entities from two different entity types.
Relationship Set is the collection of all the associations .
7. What do you understand by multi-valued attribute? How is it different from complex and composite attribute? Illustrate by giving suitable example.
An attribute with multiple values for an entity is known as multi-valued attribute. For example, the qualification of a person is a multi-valued attribute. A composite attribute is one that can be divided into smaller sub-parts to represent some more basic attributes with independent meanings. Examples include the name of a person that can further be sub-divided into First Name, Middle Name and Last Name. A complex attribute is one that is formed by nesting or grouping the composite and multivalued attributes. The parenthesis () are used for shoing grouping of components of composite attributes. The braces {} are used for showing the multi-valued attributes.

8. What do you understand by the concept of weak entity used in data modelling? Explain the relevance of owner entity type, partial key and identifying relationship in the context of such modelling.
The entities that do not have the identifier or key attribute of their own are known as weak entities. They are identified through their relation with specific entities from another entity type in combination with some of their attribute value. The other entity types with whose their entities are related are called as identifying or owner entity type. So, the relationship type that relates a weak entity type to its owner is called as identifying relationship of the weak entity. The weak entity type always has a total participation constraint (existence dependency) wih respect to its identifying relationship. This is because it cannot be identified without its owner entity.
A set of attributes of the weak entity type can uniquely identify the weak entities that are related to the same owner entity. This set of attributes are called as partial key. If this is not the case, the composite attribute of all the weak entity’s attributes will be parial key.
9. What is a participation role? State the circumstances under which the use of role names becomes necessary in description of relationship types.
Every entity type that participates in a relation plays a particular role. The role played by each entity is called as participating role. The role name specifies the role played by a participating entity of an entity type in each relationship instance. For example in the AUTHORISED BY relationship type, the EMPLOYEE plays the role of document authoriser and the Voucher plays the role document authorised.
Participation constraint specifies whether an entity type exists depending on whether it is related to another entity type or not. The following are the two types of participating constraints:

  1. Total participation: In this, entity of an entity type can exist only if it participates in a specific relationship with another entity type. It is also called as existence dependency. Example is the participation of ACCOUNTS in CLASSIFY relationship as every account must refer to atleast one of the account types. In the ER diagram, it is represented by a double line connecting the participating entity type to the relationship.
  2. Partial participation: In this, entity of an entity type can exist even if it does not participates in a specific relationship with another entity type. Example is the participation of EMPLOYEE in PREPARED BY relationship as there might be few employees who do not prepare the vouchers but still exist. In the ER diagram, it is represented by a single line connecting the participating entity type to the relationship.

10. Define foreign key. How is this concept useful in relational data model? Illustrate with suitable example.
A column in a table that refers to the primary key column of another table is know as foreign key. The table which contains the foreign key is referred to as the child table and the table which contains the candidate key is known as the parent table. The foreign keys enforce the referntial integrity constraint and there by maintaining consistency among the tuples of such relation. Thus the foreign keys are useful to maintain the referential integrity constraint in a relational data model.
Example: Consider the case where in the relation Voucher (Vno, Sno, Vdate, Debit, Amount, Credit, Amount, Prep_by, Auth_by, Narration) references the two other relations Accounts(Code, Name, Type) and Employees(EmpId, Fname, Minit, Lname, Address, PhoneNo, SuperId) as specified below.

Vouchers
Vno Sno Vdate Debit Amount Credit Amount Prep_by Auth_by Narration
Accounts
Code Name Type
Employees
EmpId Fname Minit Lname Address PhoneNo SuperId

In the above, the Vouchers relation uses its Debit and Credit attributes to refer to the Accounts relation. Here Debit and Credit attributes are Foreign keys. They refer to the tuples of Accounts relation through its primary key named Code.
Also, the Vouhchers relation uses its Prep_By and Auth_By attributes to refer to the Employees relation. Here Prep_By and Auth_By attributes are foreign keys. They refer to the tuples of Employees relation through its primary key named EmpId.

11. What is meant by NULL value? What are the reasons that lead to their occurrence in database relations?
NULL value is a special value that is used to represent the absense of data item.
The following are the reasons that lead to their occurrence in database relations.

  1. When a particular attribute does not apply to an entity.
  2. Value of an attribute is not known at the moment, even though it exists.
  3. Value of an attribute is not known because it does not exist.
12. Why are duplicate tuples not allowed in a relation?
By definition, a relation is a set of tuple and should have all its tuples distinct. In a database, each data record, which corresponds to a tuple of a relation, in a table must be unique. This implies that no two tuples/rows in a relation/table can have the same combination of values for all their data items.
By default, every relation will have atleast one key which is the combinaion of all its attributes. This is called super-key by default. Therefore, any such super-key specifies uniqueness constraint. Presence of duplicate tuples implies that there are two super-keys which are same. This goes against the basic princile of unqueness constraint. Due to this reason, duplicate tuples are not allowed in a relation.

13. What do you understand union compatibility of relations? For which operations such compatibility is required and why?
Union compatibility refers to whether set operations can be applied between two relations or not. Two relations A and B are said to be union-compatible if they satisfy the following conditions.

  1. Both the relations should have the same number of attributes.
  2. The domain of the similar attributes should be the same for all the attributes.

The set operations i.e. Union, Intersection, Difference and Cartesian product, require the union compatibility. Union compatibility is required for these operations because these set operations are binary (an operation that combines two elements to give a third element) and for the result of a binary operation to be a set they should have the same number of attributes and their domains should be the same. In otherwords, the two participatint sets should be union compatible.

14. What is the need for database normalisation?
Database Normalization is needed due to the following reasons.

  1. The possibility of duplicate or redundant data items is reduced or eliminated.
  2. Improve the storage efficiency by avoiding the storage of redundant data.
  3. Achieve scalability
  4. To achieve a flexible database structure
  5. To achieve more efficient database structure
  6. Ease of maintenance
Long Answers



1. Discuss the basic concepts of Entity Relationship (ER) Model. Illustrate as to how an ER model is diagrammed.
Entity Relationship (ER) model is a popular conceptual model. It is mostly used in database-oriented applications. The major elements of the ER Model are

  1. Entities: An entity is anything in the real world that has the indepenent existence. It is an object that has
    1. physical existence. For example a person, a building or a bike etc.
    2. conceptual existence. For example an account, a voucher, a university course, a job, a company etc.

    The following five entities exist in the context of a typical accounting reality. These entities are used to capture the accounting data.

    1. Accounts
    2. Vouchers
    3. Employees
    4. Accounting Type
    5. Support Documents
  2. Atributes: Attributes are some properties of interest (or characteristics) that are used to further describe the entities. For example, the height, birth place and date of birth of a person or code, name of an Account. Each attribute of an attribute will have a value. These values are stored in the database. The following are different types of attributes.
    1. Simple or Atomic Attributes vs Composite Attributes: Simple or atomic attributes can not be further sub-divided. Composite attributes can be further sub-divided into smaller sub-parts.
    2. Single valued vs multi-valued attribtues: Single value attribute of an entity has only one value. Multi-valued attribute of an entity has multiple values.
    3. Stored vs Derived attributes: A basic attribute is stored attribute. Derived attributes depend on the basic attribute. Example: Date of Birth is a stored attribute. Age can be derived from the date of birth.
    4. Null Values:The NULL values indicate the absence of data.
    5. Complex attributes: The composite and multi-valued attributes may be nested or grouped to form complex attributes.
    6. Entity Types and Entity Sets: Entity type is a collection of entities that share a common definition of attributes. Entity Set is a collection of all the entity instances of an entity type.
    7. Value Sets of Attributes: The set of all possible values that may be assigned to a specific attribute.
  3. Identifiers: The attribute of an entity type that has unique value is called as identifier. It is used for identifying the entity instance. Sometimes two or more attributes combined together (called as composite key) constitute such distinct value. The entity types that do not have a key attribute are called as weak entities.
  4. Relationships: Relationships exist between two or more entity types. They represent the interaction among the entities of the entity types. A relation exists when an attribute of an entity type refers to another entity types. In the ER model, these references are represented as explicit relationships instead of attributes.
    1. Types of relationships: A relationship type is constituted when entities from different entity types are related to one another in a specific manner.
    2. Degree: Degree of relationship type is the number of participating entity types in a relation.
    3. Roll Names: Each entity type participating in a relationship type plays a particular role in the relationship.
    4. Structural Constraints: The reality imposes certain constraints or restrictions to limit the possible combination of entities participating in a relationship set
      1. Cardinality Ratio: This specifies the number of relationship instances that can participate in a binary relationship.
      2. Participation Constraint: It specifies the whether an entity type exists based on its relation with another entity based on the relationship type. When it is required that every entity of an entity type must relate to another entity type, it is called total participation. On the contrary, when some entities of an entity type need not be related to another entity type, it is called as partial participation.

These elements are used to express a reality for which a database is to be designed. The figure below gives a depicts the ER Model through the ER symbols. These symbols are used to represent different types of entities, attributes, identifiers and relationships.

Meaning
Shape Used
Symbol
Entity
Rectangular Box
Weak Entity
Double lined Rectangular Box
Relationship Type
Diamond Shaped Box
Identifying Relationship Type
Double lined Diamond Shaped Box
Attribute Name
Enclosed in oval and attached to their entity type by straight line
Key Attribute Name
Enclosed in oval and attached to their entity type by straight line
Multi valued attribute
Double oval
Derived attribute
Dashed line oval
Total participation of E2 in R
Cardinality Ratio 1:N for E1:E2 in R

2. What integrity constraints are specified on database schema? Why is each considered important?
A relational database schema is a set of relation schemas and a set of integrity contraints. These integrity constraints must hold in every database state of that schema. There are four different constraints that can be specified on a database schema. These are

  1. Domain: It requires that the value of each attribute must be
    1. indivisible value.
    2. drawn out of possible values associated with its domain.
    3. conforming to the data type associated with the domain
  2. Key Constraints and NULL values: As per the definition, a relation is a set of tuples that are distinct. So, in a relation, each data record that corresponds to a tuple should be unique. This implies that no two tuples (or rows) in a relation (or table) should have same combination values for all their data items. Also, every relation, by default, will have the combination of all the attributes as the key. This is also called as super-key. This super key specifies the uniqueness constraint. Such a super key may have redundant attributes. So, a key that does not have a redundancy will be more useful.
    Minimal super-key, also called as Key, is a part of the super-key constituting the least possible number of attributes that are unique. The value of this minimal super-key is used to identity each tuple in a relation. This key should continue to be unique even after adding a new tuple.
    When a relation has more than one key, such key is called as candidate key. One of these candidate keys is called as primary key. The circumstances of the mini-world would decide which of the available candidate keys can be a primary key.
    Flow chart to reach a minimal super key
    Flow chart to reach a minimal super key
  3. Entity integrity constraint: As per this constraint, a primary key value can not be NULL because the primary key is used to identity individual tuple in a relation. If a primary key has a NULL value it implies that such tuples can not be identified or it identifies multiple tuples as unique. In otherwords, this means that there are duplicate tuples.
  4. Referential integrity constraint: Referential integrity constraint is specified on two relations to maintain consistency among the tuples of these relations. As per this, a tuple in a relation must refer to an existing tuple in another relation through a foreign key.

3. Discuss the different types of update operations in relation to the integrity constraints which must be satisfied in a relational database model.
The integrity constraints which must be satisfied in a relational database model are

  1. Domain
  2. Key constraints and NULL values
  3. Entity integrity constraint
  4. Referential integrity constraint.

The following are the different types of update operations in relation to the above integrity constraints which must be satisfied in a relational database model.

  1. Insert: This operation is performed to add a new tuple in a relation. The insert operation might violate any of the above four constraints.
  2. Delete: This operation is performed to delete a tuple from a relation. Though the delete operation, a specific data record from a table can removed. The delete operation might violate referential integrity constraint. This happens when a tuple being removed is referenced by foreign key from other tuples.
  3. Modify:The operation is performed to modify the values of some attributes in existing tuples. This operation is helpful to change values of an existing record in a table. This operation might violate the integrity constraint only when the operation tries to modify either primary key or foreign key. Otherwise, it does not violate any of the constraints.

4. Discuss the steps you would take to transform an ER Model into various relations of Relational Data Model. Give suitable examples.
The following are the desired steps to transform an ER Model into various relations of Relational Data Model.

  1. Create a relation for every strong entity: For every strong entity type that has the primary key in the ER schema, a separate relation is created. This relation includes all the simple attributes of that entity. The primary key can be either one of the key attributes of this entity or a set of simple attributes that uniquely indentity this entity.
    For example, Employee entity is strong because it has one of its unique attribute EmpId as the primary key. So, we’ll create a separate relation for the Employee as specified below. Note that the primary key is underlined.
    Employee(EmpId, Fname, Minit, Lname, Address, PhoneNo, SuperId)
    Similarly, other relations are created for the below strong entities.
    Accounts(Code, Name, Type)
    Vouchers(VNo, vDate, Amount, Description)
    Account Type(CatId, Category)
  2. Create a separate relation for each weak entity type: Every weak entity has a owner entity. The weak entity type is identified through the identifying relationship. For every weak entity type, a separate relation that includes all its attributes is created. The primary key of this new relation is the combination of its unique attribute(s) for a particular tuple of the owner relation along with the primary key attribute of the owner relation. In addition, the primary key of owner entity is included as foreign key in such a relation key of the owner entity. This foreign key acts as the relation key between the owner and the weak entity type. The weak entity has the composite key which is combination of the foreign key and the partial key of the weak entity.
    If we take the example of Vourchers and Support Documents entities, the Vourchers entity is the owner entity and Support documents entity is the weak entity. The Support Documents entity does not have a primary key of its own. It has the composite key which is combination of the primary key of Vouchers i.e. vNo along with SNo. It is given below.
    Support Documents(vNo, SNo, dName, sDate)
  3. Identify entity types participating in binary 1:N relationship type: Identity the first relation on the n-side of the relationship and second on 1-side of the relationship. The primary key of the second relation should be included as a foreign key in the first relation.
    For example, an employee can authorize a number vouchers. So, in the Auth_By relation, the Vouchers participates on the n-side and the Employees participate on the 1-side. So, the primary key of the Emploees i.e. EmpId should be included as foreign key in the Vouchers relation. Similarly the Vouchers and Employees participate in the Prep_By relation. The mapping of both of these relations require that the EmpId should be included twice, but with different roles. As the relation cannot have two attributes with the same name, we include their role names as attribute names to designate the foreign keys. The Vouchers relation is modified accordingly and provided below:
    Vochers(VNo, vDate, Amount, Description, Auth_By, Prep_By)
    Similarly, two relationships exist between Vouchers and Accounts. One is Debit and one is Credit. Here Vouchers include the Code attribute of Accounts as foreign key. As the relation cannot have two attributes with the same name (i.e. Code and Code), we include their role names (Debit and Credit) as attribute names to designate the foreign keys. The Vouchers relation is modified accordingly and provided below:
    Vochers(VNo, vDate, Debit, Credit, Amount, Description, Auth_By, Prep_By)
  4. Identify entity types participating in binary M:N relationship type: For each binary M:N relationship type, create a new relation to represent such relationship. The primary keys of the relations that represent the participating entity types should be included as foreign keys in this new relation.
    For example, consider the case of credit voucher which has one debit and multiple credit accounts as depicted below.
    ER Diagram showing relationships between vouchers and accounts in the context of credit vouchers, with one debit and several credit entries
    ER Diagram showing relationships between vouchers and accounts in the
    context of credit vouchers, with one debit and several credit entries

    In this case, the relationship Credit has cardinality ratio of M:N between vouchers and accounts(multiple vouchers are related to multiple accounts) where as the relationship Debit has cardinality ratio of N:1 (Multiple vouchers are related to one account). In addition to that the credit relationship has the attributes Sno, amount and Description. So, we’ll create a new relation as
    Credit(vNo, Sno, Code, Amount, Description)
    In the above relation credit Code is foreign key to represent the primary key of accounts relation. Vno is foreign key to represent the primary key of Vouchers relation.
    On similar note, the relation for the Debit voucher will be
    Debit(vNo, Sno, Code, Amount, Description)

You might also want to refer the following pages.

  1. Theory Base of Accounting
  2. Recording of Transactions – I
  3. Recording of Transactions – II
  4. Bank Reconciliation Statement