Account 6 Marks Question

Entity Relationship (ER) Model:
It is a popular conceptual data model, which is mostly used in database-oriented applications. The major elements of ER Model are entities, attributes, identifiers and relationships that are used to express a reality for which a database is to be designed. The model is best depicted with the help of ER symbols, the list and description of which is shown in figure. While preparing an ER Diagram, the following symbols are used to represent different types of;

• Entities.
• Attributes.
• Identifiers and relationships.

The elements of ER model that are meant to describe and display the reality are discussed in the context of an accounting reality given below:

Meaning	Symbols
Entity Type as Rectangular Box.
Week entity Type as double linked Rectangular Box.
Relationship Type as diamond shaped Box.
Identifying relationship Type as double linked diamond shaped Box.
Attribute names enclosed in ovals and attached to their entity type by straight lines.
Key attribute names enclosed in ovals and attached to their entity tpe b straight lines.
Multi-valued attributes by double ovals.
Derived attributes by dashed line ovals.
Total participation of E2 in R
Cardinality Ratio 1 : N for E1 : E2 in R.

According to the given information, trial balance does not agree. Pen-drive is wrongly debited to office equipment account, instead of stationery account and supplier account is debited instead of crediting. Due to these mistakes, the following errors are committed:

1. The balance of office equipment is overstated by ₹ 3,500.
2. The balance of creditors account is understated by ₹ 7,000.
3. The total of the debit column of the trial balance is correctly stated.
4. The total of the credit column of the trial balance is understated by ₹ 7,000.
5. If the total of the debit column of the trial balance is ₹ 2,40,000 before rectifying error, the total of the credit column of the trial balance is ₹ 2,33,000 (i.e., ₹ 2,40,000 − ₹ 7,000).

Database is a collection of inter-related data, events and transactions. It is organised in a particular manner and provide access to the various users simultaneously. In this system, data is gathered and stored for a particular operation.
The following are the two important properties of a database:

1. Shared Property: The database is a combination of related data that can be accessed by the people who have authority to access it and to meet their different information needs.
2. Integrated Property: The database is arranged in such a manner so as to avoid and eliminate duplication of data. On one hand, database are used for storing accounting data, whereas, on the other hand, DBMS is that software which helps in creating, developing and maintaining the database. It is a system that provides easier access to data recorded in the database. It handles a huge amount of data and defines, organises and transforms the database for different functions as per the need. Hence, it can be said that DBMS facilitates access to the database.

There are two categories of operations on relational model. Updates and retrieval The three basic types of updates are as given below:

1. Insert: This operation is performed to add a new tuple in a relation. For example, an attempt to add another record of an account with data values corresponding to Code, Name and its Type to Accounts relation shall be made by performing Insert operation. The insert operation is capable of violating any of the four constraints discussed above.
2. Delete: This operation is carried out to remove a tuple from a relation. A particular data record from a table can be removed by performing such a operation. The delete operation can violate only referential integrity, if tuple being removed is referenced by foreign key from other tuples in the database.
3. Modify: The operation aims at causing a change in the values of some attributes in existing tuples. This is useful in modifying existing values of an accounting record in a data table. Usually, this operation does not cause problems provided the modification is directed on neither primary key nor foreign key.

Attributes may be defined as characteristics that reflect the features of the entity. In case of a person these characteristics may be height, weight, name, date of birth, etc. and in case of accounts it may be code, type, name, etc. These attributes own a value which is stored in database as data.
Multi-valued attribute is an attribute that has multiple values. For example, flavour of ice-creams. It is a multi-valued attribute as the flavour of an ice-cream can be strawberry, vanilla, butter-scotch, etc. In other words, multi-valued attributes are the different characteristics of a same entity.
Composite attributes are the combination or aggregate of related attributes. These attributes can be further divided into small portions to represent the independent meaning of some basic attributes. For example, contact details which is generally divided into telephone numbers (with STD code), mobile number and e-mail Ids.
Complex attributes are formed by grouping together the attributes of composite and multi-valued attributes. To show the grouping of components of composite attributes, the parenthesis ( ) are used and to show the grouping of components of complex attributes, the brackets { } are used.

An Entity Type is defined as a collection of entities, which share a common definition in terms of their attributes. Each entity type is assigned a name for its subsequent identification. The attributes of entity type are used to describe it in the database. The values of attributes of an entity belonging to entity type are known as Entity Instance. For example, (110001 Capital Account 4) is an entity instance of an account whose code = 110001, Name = Capital Account and Type = 4. An Entity Set is a collection of all entity instances of a particular entity type. An Entity Type is described by a set of attributes called “schema”. The set of entities pertaining to a particular entity type share the same set of attributes. The collection of entities of a particular entity type is grouped into entity set, called the extension of the entity type. For example,
Entity Type: Accounts.
Intension (or structure) of entity type.

Code

Name

Type

Entity Set: Collection of entity instances of an entity type “Accounts”.
Extension (or instances) of entity type.

110001 221019 221020

Capital Account Jain & Co. Jayram Bros.

4 4 4

Accounting data refers to the facts and financial data contained in journals, ledgers, financial statements and other books of accounts. That is, accounting data pertains to the financial data recorded in the books of accounts.
The procedure through which the accounting data is transformed for being presented as information in financial statements is known as data processing. Data processing is a process of collecting, storing, summarising, analysing and interpreting the data and facts in such a manner so as to fetch reliable information for efficient and effective decision making. The following are the various stages that are involved in transformation of data into information so as to present in the financial statements.

1. Collecting Data from the Source Documents: First of all, data is collected from the source documents such as payment slips, receipt slips, etc. for preparing vouchers. The preparation of vouchers is the basis for recording the accounting data in a systematic and chronological (date-wise) manner.
2. Input Data: A suitable database is designed to enter the accounting data contained in the vouchers. This is done with the help of pre-designed Data Entry Form. This form is similar to the physical voucher form.
3. Data Storage: Data storage is created for storing the input of data. It involves structuring the database which is used for recording the accounting data. Following is the format of blank data record.

Code	Name	Type

4. Manipulation of Data: This stage involves transformation of the stored data to generate final reports.
5. Output of Data: It means representing the accounting reports such as Journals, Ledger, Trial Balance, Financial Accounts, etc. as useful information. The accounting users can obtain these reports just by accessing the transformed data. This stage basically implies generation of final reports in a pre-designed format.

A foreign key is defined as a key that refers to a primary key column of another table. In other words, it is a field in a relational table that matches the primary key of another table. These keys relate different tables together in order to form an integrated database. For example, let us consider the two following tables-STUDENT table and BOOKS ISSUED table. The STUDENT table includes all the student data and the BOOKS ISSUED table includes all the books issued by the students from the school library. The basic objective here is that all the books issued must corresponds to the students that are listed in the STUDENT table. In order to do this, we need to place a foreign key in the BOOKS ISSUED table and need to relate it to the primary key of the STUDENT table.

STUDENT TABLE
S_Id	Name
142	Noor
135	Jagat
153	Mohini

BOOKS ISSUED TABLE
Name_of_Books	S_Id
Economics	135
Mathematics	135
English	142

The Join of STUDENT table and BOOKS ISSUED table appears as:

S_Id	Name	Name_of_Books
135	Jagat	Economics
135	Jagat	Mathematics
142	Noor	English

Thus, we can see that in the STUDENT table, student ID (i.e. S_Id) is the primary key, whereas, in the BOOKS ISSUED table, S_Id is a foreign key, which will be used to relate the data in the STUDENT table to that of the books issued by them in the BOOKS ISSUED table.

A particular role is played in the relationship by each entity that participates in a relationship type. The Participation role specifies the existence of an entity depending on being related to another entity via a relationship or not. The two types of such constraints are total or partial participation.

1. Total Participation: If it is required that every entity of an entity type must relate to another entity type, then such an entity can exist only if it participates in that specific relationship. This kind of participation is called total participation. This type of participation constraints is also known as existence dependency. For example, if it is required that every account must be related to at least one of the accounts type, then an account entity can only exist if it participates in CLASSIFY relationship instance.
2. Partial Participation: If in case, it is not required that every entity of an entity type must relate to another entity type, then the participation of the entity in the relationship type is partially constrained. Such an entity can exist even if a part of it is related to the relationship type. For example, if every employee is not expected to prepare at least one of the vouchers, then the participation of employee in PREPARED_BY relationship is partial.

In case, there exist two relationships between any two entities, then in order to map both the relationships, the primary key of the common entity is to be included twice. But a relation cannot have the same name, hence, we need to use different role names as attributes as foreign keys to indicate the relations.

The weak entities refer to those entities which do not have their own key attributes or identities. The weak entities are identified on the basis of their relation with the specific entities from another entity type in combination with some of their attribute values. These other entity types are regarded as identifying or owner entity type. The entity type, whose entities are in relationship with the weak entities are referred to as identifying or owner entity type. This can be described in other words, as the entities which are identified because of their relationship with their parent entity or dominant entity is referred to as weak entities or child entity type (or subordinate entity type), while the parent entity is referred to as identifying entity type.
Accordingly, the relationship through which the weak entity type relates to its owner entity is known as identifying relationship of weak entity. For a unique identification of the weak entity that is related to the same owner entity, a set of attributes is used which is known as partial key. Sometimes, the partial key is also known as the discriminator. This is because it is used to uniquely identify the weak entities that are related to the same parent entity. For example, if we assume that no two children of the same employee have the same first name, then the attribute Name of Children is the partial key that can be used to identify the children (weak entities) that are related to the same employee (owner entity).

The following are the various categories of data models:

1. Relational Data Model: This data model is based on the relationship of collected data values. In this data model, data is organised into rows and columns. A row is regarded as a tuple, a column header is known as an attribute and the collective set of rows and columns, i.e. a table is called a relation. The table is known as a relation as it expresses the relationship between the rows and columns. This model provides the storage and retrieval functions and defines the data structure.

The relational data model was first introduced by Ted Codd in 1970 in a classic paper Codd, 1970. Prior to this, there were other data models which were proposed in sixties such as, hierarchical data model and network data model. These models are also known as legacy-models due to their large existing user-base.

2. Hierarchical Data Model: This data model mainly consists of records and parent-child relationships. While, a record is regarded as a collection of values that provides information regarding an entity or a relationship instance, on the other hand, a parent-child relationship explains the relationship between the parent record and children record type. In this data model, the records are organised in a tree structure rather than as an arbitrary graph. The data is represented by a collection of records and relationship among data is represented by links.
3. Network Data Model: This type of data model is sometimes also known as DBTG model, as the original network model was presented in CODASYL Data Base Task Group’s 1971, i.e. (DBTG). This data model basically consists of records and sets. While, data is stored in records, which consists of a group of related data values, on the other hand, sets describes the relationship between two records types. In this data model, data is also represented by collection of records and the relationship among data is represented by sets. This model provides many-to-many relationships in data.

The union compatibility of relations implies that the participating relations must fulfil the following conditions:

1. Same degree, i.e. The two relations must have the same number (set) of attributes.
2. Same domain of each corresponding pair of attributes of relation A and relation B, that is

Dom (A) = Dom (B)
That is, the domain (stands for data type) for the corresponding attributes must be identical.
Hence, we can say any two relations say, relation A and relation B are union compatible, iff (if and only if), both the relations have the same number of attributes and the domains of their corresponding attributes are the identical (column by column).
The following are the various operations for which union compatibility of relations is required:

1. Union (A ∪ B): It contains all tuples from each of the relations.
2. Intersection (A ∩ B): It contains all the tuples that are contained in both the relations A and B.
3. Difference (A - B): It contains all the tuples that are contained in the relation A but are not present in the relation B.
4. Cartesian Product (A × B): It contains the set of all concatenated tuples (x, y), where x is a tuple in the relation A and y is the a tuple in the relation B. The tuples in the Cartesian product contains the product of each tuple of the relation A and each tuple of the relation B. (It must be noted that for performing Cartesian product it is not necessary that the two relations must be union compatible).

In order to run these operations, it is important that the two relations must be union compatible. It is because of the fact that the relations without being union compatible that is not of same degree of attributes and having same domain, may lead to difficulty in performing such operations.

The requisite to transform on ER model into various relations of the Relational Data Model is to have an ER design. Let following be the example of our ER model that we need to transform into Relational Data Model.





In the above diagram, the rectangular boxes are used to represent entities. The diamond shaped boxes are used to describe the relation type between the two entities. The double lined rectangular boxes are used to show the weak entities. The double-lined diamond shaped box is used to describe the identifying relationship type. In ovals, attribute names are enclosed and attached to their respective entity type through the straight lines.
The following section describes the procedure to transform ER model into Relational Data Model:

1. Creation of Relation for Every Strong Entity: A separate relation that includes all the simple attributes is to be created for each strong entity type in the ER schema. A primary key out of these attributes is chosen arbitrarily for easy and unique identification of the strong entity. For example, in the above ER design, Employee, Vouchers, Accounts and Account Type are the strong entities as they have primary key which is one of their unique attributes. These attributes are shown in the ovals and are attached to their respective entity type by the straight lines.

Separate relation is created for each strong entity. This is represented as:

• Employee (EmpId, Name, Type)
• Vouchers (VNo, SNo. Narration)
• Accounts (Type, Name, Code)
• Accounts Type (CatId, Category)

2. Creation of Relation for each Weak Entity Type: Weak entities do not have their own identities and are identified through the identifying relationship. So, we can say that every weak entity has its own entity that helps in its identification. A separate relation that includes the attributes is to be created for each weak entity. The primary key of this new relation is the combination of its unique attributes, along with the primary key attribute of the owner relation. For example, Support entity is the weak entity as it does have its own primary key; Voucher is the owner entity of Support entity.

Support entity has a partial key which is SNo Assigned to each document. Therefore, VNo, the primary key of the vouchers along with SNo is designed as composite key for the Support entity. The relation so formed can be represented as:

• Support (VNo, SNo, dName sDate)

3. Identity Entity Types Participating in Binary 1 : N Relationship Type: First of all, the first relation on n-side of the relationship and second on the 1-side of such relationships is to be identified. The primary key of the second relation should be included in the first relation as its foreign key. For example, in the example, an employee can authorize a number of vouchers. It means that Voucher entity participates in AuthBy relationship on the n-side, on the other hand, the Employee entity participates in the same relationship on the 1-side. Similarly, Prep. by relationship between employees and vouchers participates in binary 1 : N relationship.
4. Identify entity types participating in binary M : N relationship type: A new relation is to be created for each binary M : N relationship type. This new relation should include foreign keys to represent primary key of the new relation. For example, consider two entities namely, Voucher entity and Account entity. These entities have two relation debit and credit. The debit relationship has cordially ratio of N : 1 i.e. many debit voucher relates to one accounts. On the other hand, the credit relationship has cardinality ratio of M : N. For example, many credit vouchers are related to many accounts.

So, based on the diagram, the following two relations can be formed as:

• Credit (VNo, SNo, Code, Amount, Narration)
• Debit (VNo. SNo, Code, Amount, Narration)

In the Credit relation, Credit Code is included as foreign key to represent the primary key of the Accounts relation. VNo is included as foreign key to represent the primary key of the Vouchers relation. Both VNo. and the Code together constitute the primary key of the new relation Credit.

At the end, the following relations have been formed to formulate the relational data model for the above example of ER design:

• Employee (EmpId, Name, Type)
• Vouchers (VNo, SNo, Naration)
• Accounts (Type, Name, Code)
• Accounts Type (CatId, Category)

A database schema implies the structure and design of database. Database schema is regarded as a collective term for various properties of database such as, tables, constraints, relations, etc. A relational database schema consists of a set of relational database schemas and set of integrity constraints.
There are mainly four different integrity constraints that are imposed on a relational database. These are mentioned as:

1. Domain Constraints: These constraints in the database schema state the conditions that each relationa linstance must satisfy. The value of each attribute of a relation must be an indivisible value and must be drawn from the domain associated with that attribute. Hence, the value of an attribute must confirm to the data type associated with the domain.
2. Key Constraints and NULL Value: These constraints imply the existence of candidate keys. As per the Key Constraints, in every instance of a relational database schema, the tuples can be uniquely identified by their values for certain attributes. This implies that each data record corresponding to a tuple of relation in a table must be distinct. That is, in other words, no two tuples in a relation can have the same combination of values for all their attributes. Every relation has at least one key, which is the combination of all its attributes and is known as Super-Key. The Super-key specifies the uniqueness constraints.

However, a Super-key may contain some additional attributes that are not necessary for unique identification. Minimal super key is that part of super key from which removal of any attribute from the Super-key leaves a set of attributes that is not a Super-key. That is, if any attribute is removed, then the uniqueness constraint is disturbed. The Minimal Super-key is also known as Candidate key.

In case a relation has many Candidate keys, then out of them one is arbitrarily chosen and regarded as Primary key. The attributes of Primary key are represented as underlined in a schema. The other Candidate keys are regarded as Alternate or Unique keys.

3. Entity Integrity Constraints: A Primary key is used to identify individual tuple in a relation. As per the Entity Integrity Constraints, the value of Primary key cannot be null. If in case, the Primary key has null value, then it implies that we cannot identify such tuples as they are same. This implies that tuples are duplicated and the uniqueness constraints are violated.
4. Referential Integrity Constraints: Referential integrity constraint is specified between two or more relations to maintain consistency among the tuples of such relations. These are the constraints implies by the existence of foreign keys. Hence, a tuple in one relation that refers to another relation must refer to an existing tuple in that other relation.

The above mentioned integrity constraints are certain conditions that are imposed on a database schema. The importance of integrity constraints is that by imposing certain specifications, the integrity constraints restrict the data that is stored in a database instance. In case, the database instance satisfies all the integrity constraints, then it is regarded as a legal database instance and hence, can be stored in the database. In case, these constraints are violated, then it implies that database is not maintained in an efficient manner and the database contains illegal database instances.