NCERT Solutions for Class 11 Accountancy Chapter 14 Structuring Database for Accounting

Detailed, Step-by-Step NCERT Solutions for 11 Accountancy Chapter 14 Structuring Database for Accounting Questions and Answers were solved by Expert Teachers as per NCERT (CBSE) Book guidelines covering each topic in chapter to ensure complete preparation.

Structuring Database for Accounting NCERT Solutions for Class 11 Accountancy Chapter 14

Structuring Database for Accounting Questions and Answers Class 11 Accountancy Chapter 14

Test Your Understanding

A. Indicate against each of the following statements, True or False :
(a) Every relation has at least one super key by default, which is the combination of all its attributes.
(b) Data transformation is called information.
(c) Referential integrity constraint arises because of relationships between various entities.
(d) The complete absence of WHERE clause in SELECT statement implies that no tuples of a relation shall be selected.
(e) ER model is an example of representational data model.
Answer:
(a) True
(b) True
(c) True
(d) False

B. Fill in the blanks, an appropriate word(s) :
(a) A ………. does not have key attributes of its own.
(b) The ………. for binary relationship specifies the number of relationship instances that an entity can participate in.
(c) Each simple attribute of an entity type is associated with a value set called ………. of values.
(d) When structure of AIS is based on both human and computer resources. It is called ………. AIS.
(e) A ………. is a collection of all entities of a particular entity type.
(f) A weak entity type always has a ………. constraint with respect to its identifying relationship.
(g) When a relation has more than one attribute with unique values, each such attribute is called ……….
Answer:
(a) weak entity
(b) computer based
(c) timeware
(d) liveware
(e) total participation
(f) multi-valued
(g) full functional

Short Answer Type Questions

Question 1.
State main categories of data models.
Answer:
Data models is a collections of concepts used to describe the structure of database. The main categories of data models are following :
(i) Reality
(ii) Data
(iii) Database
(iv) Information
(v) DBMS

(i) Reality-It consists of different components of an organisation such as people, facilities and other resources. It also includes that environment in which the organisation exists and operates. The continuous interaction with the environment in which organisation operates result in real world transaction. These transaction are analysed to identify the data

(ii) Data – Data can be anything such as number, a person’s name, image, account-name, entities, places, objects, events or even concepts. So, it is a set of isolated and unrelated raw facts, represented by values, which have little or no meaning. They have little or no meaning because they lack a content for evaluation. Data may be quantitative or qualitative and they may be of financial or non-financial character.

(iii) Database – Database is a collection of related data. A database is an integrated and logically collection of data. Data is collected and stored in such a way that a common and controlled approach is sufficient in adding new data and modifying and retrieving existing data with in the database.

Characteristics of Database :
(a) Integrated Property
(b) Shared Property
(a) Integrated Property – Database is integrated because in it distinct data tables have been logically organised to reduce or eliminate duplicity and better access.

(b) Shared Property – Database is shared i.e., all those who are authorised to use data have access to relevant data.
Accounting database is a collection of related accounting data to represent some aspect of an accounting information system.

(iv) Information – When data is processed and converted into a meaningful and useful form, it is known as information. It is a set of organised and validated collection of data. Information is a data that have processed and reined and then presented in a format that is important for decision making. Following diagram shows that information may be viewed as data at one level. But when it is processed it becomes information at another level.

— Producing ledger accounts
— Generating trial balance
— Prepare Profit & Loss Account and Balance Sheet

(v) Database Management System (DBMS)-DBMS is essentially a collection of interrelated data and a set of programs to access this data. This collection of data is usually called the database. It can be regarded as an electronic filing cabinet. Database system are designed to maintain large volume of data.

Management of data involves :

• Defining the structure for the storage of data.
• Providing the mechanisms for the manipulation of the data.
• Providing for the security of the data against unauthorized access.

The DBMS is a general purpose software system that facilitates the process of defining, constructing and maintaining data bases for various applications. A database management system is a collection of programs that enables user to create and maintain a database.

Defining a database involves specifying the data type, structure and constraints for the data to be stored in a database. Constructing the database is the process of storing two data itself on same storage medium that is controlled by the DBMS.

Manipulating a database includes some functions as querying the data base to retrieve specific data. Updating the data base to reflect changes in the reality and generating the reports from the date.

Question 2.
How are computers useful in processing the accounting data?
Answer:
The computerised accounting system is based on the concept of database. It does involve the process of creativity and maintaining journals, ledgers etc. It is one of the database-oriented applications wherein the transaction data is stored in a well-organised database.

The user operates on such database using the required and desired interface and also takes the desired reports by suitable transformation of shared data into information. Computers plays a very significant role in processing accounting data. Thiseliminates duplication of data, improves the flow of information and facilitates proper inventory- control, cost control, cash flow etc.

The computer makes the job of accounting department very simple and interesting. Maintaining subsidiary books, ledger, preparation of trial balance and preparation of financial statements etc. can be performed by computers quickly and very efficiently. Computers are very useful in processing the accounting data.

It becomes clear from the following :
(1) Processing with high speed – Computer require far less time than human beings in performing a task that is why it process accounting data very fast.

(2) Accurate Processing – Computer system eliminates duplication of data because the primary accounting data is entered once for all subsequent usage. This eliminates the possibility of error.

(3) Reliable Processing – Computers are immune to tiredness, boredom or fatigue. So, it is well-adapted to performing repetitive operations. The extent of reliability of processing accounting data by a computer is immense.

(4) Up-to-date Processing – All the related records in a computer get automatically update once the data is entered and stored.

(5) Efficiency in Processing – The computer based accounting system ensure better use of resources and time.

(6) Automate Document Production – Computer have standardised, user defined format of accounting report that generates automatically. The accounting report such as Cash Book, Trial Balance etc. are obtained just by click.

(7) Quality Processing – The input checks and untouchable features of data handling facilitate hygienic and true accounting processing that provides true accounting reports that are highly objectives and can be relied upon.
So, it is clear from the above points that computer is an electronic device which calculates, compares, modifies, stores and retrieves fact and figures, is being widely used in the field of accounting.

It has an inbuilt system of making calculations, collecting and processing accounting data and present the result in the form reports useful in decision making. It performs the functions of recording storing, posting, classification summarisation the accounting data.

Question 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?
Answer:
Data are facts and figures that $re not currently being used an a decision process and usually take the form of historical records that are recorded and field without immediate intext to retrieve for decision-making. Data are raw facts about a business and its business transactions.

So, we can say that accounting data are known facts that  can be recorded and which have implicit meaning. It may represent facts concerning people, places, objects, entities, events or even concepts. It may be quantitative and qualitative or they may be of financial character or non-financial character.

For example, consider the transaction :
April 01,2006 Ravi started business with Cash Rs. 1,00,000. This transaction has various data context like, ‘01’, 01-Apr-2006, Ravi Capital, Rs. 1,00,000 but these are not useful for decision making unless they are processed to suit to the requirements of decision making situations.

Accounting data is data related to the accounting transactions and used for making the financial statements. Anything which facilitates the accounting process is called accounting data. It may be people,  business transaction, environment, events etc. Accounting data therefore processed to get accounting information which is used for accounting processes i.e., to prepare vouchers, ledger, financial statements which ‘s, are very helpful in the decision making process.

Data Processing involves the techniques of collecting sorting, relating, interpreting and computing accounting data in such a manner as to provide meaningful and useful information or presenting financial statements. Data capturing, inputing, processing and generating information are the necessary steps involved in data processing cycle.

(1) Source Documents – The first step is to capture accounting data from accounting transactions so as to prepare voucher, that expresses and documents an accounting transactions. Vouchers are so designed as to permit the recording of a data in to a systematic manner.

(2) Input of Data – By using a pre-designed Data Entry Form, accounting data contained in vouchers is to be entered in a computer storage device. Data entry, form is designed in a manner that is similar to physical voucher document.

(3) Data Storage – A suitable data storage structure is required to provide for a blank data record. The above blank record is used for storing the input of data pertaining code of account, name of the account and category type to which it belongs.

(4) Manipulation of Data – The stored data is manipulated for necessary transformation to generate final reports. Such transformed data may be stored separately and subsequently used for generating final reports.

(5) Output of Data – The accounting reports such as ledger, trial balance etc. are obtained in a pre-designed format by accessing the transformed data.

Question 4.
What do you understand by database? How does it differ from DBMS?
Answer:
A database is a collection of related data. It is a collection of data flies integrated and organised into single comprehensive file system, which is arranged to minimum duplication of data and to provide conveninent access to information within that system to satisfy’ a variety of user needs. The data is collected and stored in such a way that a common and controlled approach is sufficient in adding new data and modifying and retriving existing data within the database. It is a shared collection of interrelated data tables, files or structure which are designed to meet the varied informational need of an organisation.

Database has two important properties :
(i) Integrated Property
(ii) Shared Property
(iii) Integrated Property – Database is integrated because in it distinct data tables have been logically organised to reduce or eliminate duplicity and better access.
(iv) Shared Property – Database is shared i.’e., all those who are authorised to use data have access to relevant data.

“A database is a computer file system that uses a particular for organisation to facilitate rapid updating of individual records, simultaneously updating of related records, easy access to all records, by all applications programs and rapid access to all stored data which must be brought together for a particular routine report or inquiry or a special purpose report inquiry. ” – GM. Scott

However, a database management system (DBMS) is a software system that allows access to data contained in database. It’s objective is to provide a convenient and effective method of defining, storing and retrieving the information contained in database. This is DBMS who makes possible to share data in the database among the multiple applications or users. DBMS is essentially a collection of inter-related data and a set of programs to access of this data. This data is collected in a database. DBMS are designed to maintain large volume of data.

Management of data involves :

• Defining the structure for the storage of data in database.
• Providing the mechanisms for the manipulation of the data stored in database.
• Providing for the security of the data against unauthorized access.

Database is different from DBMS because DBMS is a general purpose software system that facilitates the process of defining, constructing and maintaining data bases for various applications. A DBMS is a collection of programs that enables user to create and maintain a database.

Question 5.
What is meant by entity type? How is it different from entity set? Illustrate by giving suitable example from accounting reality.
Answer:
Entity – A “Thing” in the real world with an independent existence is called entity. An entity may be an object with a physical existence (for example, a particular person, car, house or employee) or it may be an object with a conceptual existence (for example : a company, a job etc.)

Entity Types and Entity Sets – A database usually contains groups of entities that are similar. An entity type defines a set of entities that have the same attributes. Each entity type in the database is described by its name and attributes.

In other words, 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 data base. The value of attributes of an entity belonging to entity type are known as Entity Instance.

The collection of all entities of a particular entity type in the database at any point of time is called as entity set. The entity set is usually referred to using the same name as the entity type. An entity set is a collection of all entity instances of a particular entity type.

An entity set is a collection of all entity instances of a particular entity type. The set of entities pertaining to a particular entity type share the same set of attributes.
For example:
Entity Type : Accounts
Structure of entity type

Entity Set : Collection of entity instances of an entity type “Accounts”.

Question 6.
What do you understand by relationship type? How is it different from relationship instance and relationship set?
Answer:
Relationship among two or more entity types represents an interaction among their respective entities. In fact, whenever an attribute-” of are entity type refers to an other entity type, some relationship exists. Whenever an attribute (say debit) of are entity type (say vouchers) refers to another entity type (say accounts), there exists a relationship between these entities (voucher and accounts).

Relationship Type,- Whenever entities from different entity types are related to one another in a particular manner, they constitute a relationship type. In ER diagrams, relationship types are displayed as diamond shaped boxes, connected by straight lines to be rectangular boxes, which represent the participating entity types. A collection of relationships of the same type is called a relationship set.

A relationship type R among n entity types E₁, E₂, E_n defines a set of associations – or a relationship set – among entities from these entity types. As for the case of entity type, entity sets, a relationship . type and its corresponding relationship set are customarily referred to by the some name, R. Mathematically, the relationship set R is a set of relationship instance r₁ where each r. associates n individual entities (e₁, e_2, e_n) and entity e₁ in r is a member of entity type E_i ≤ 1 ≤ n.

Question 7.
What do you understand by multi-valued attribute? How is it different from complex and composite attribute? Illustrate by giving suitable example.
Answer:
Attributes are some properties of interest or characteristics that further describe the entity such as height, weight and date of birth in case of a person and code and name in case of accounts.

Multi-valued attribute – Most attributes have a single value for a particular entity, such attributes are called single valued. For example, age is a single-valued attribute of a person, in some cases an attribute can have a set of values for the some entity – For example, education of person, colour of car etc. Such attributes are called multi-valued attribute. A multi-valued attribute may have lower and upper bounds to constrain the number of values allowed for each individual entity.

Composite attributes – The composite attributes can be divided into smaller sub-parts to represent some more basic attributes with independent meanings. For example, name of a person that is normally sub-divided into First Name, Middle Name and Last Name is a composite attribute.

Complex Attributes – The multi-valued and composite attributes may be grouped to constitute complex ones. The parenthesis () are used for showing grouping of components of composite attributes. The braces {} are used for showing the multi-valued attributes.

For example, if a person can have more than one residence and each residence can have multiple phone, an attribute address phone for a person can be shown as :
(Address Phone ({Phone (Area Code, Phone Number)}, Address (Street Address(Number, Street, Apartment Number) City, State, Zip)} A complex attribute, Address Phone

Question 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.
Answer:
Entity types, which do not have key attributes of their own are, called weak entity types. Such entity types are identified by being related to specific entities from another entity type in combination with some of their attribute value. Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in combination with one of their attribute values.

We call this other entity type the identifying or owner entity type and we call the relationship type that relates a weak entity type to its owner the identifying relationship of the weak entity type.

A weak entity type always has a total participation constraints (existence depency) with respect to its identifying relationships, because a weak entity cannot be identified without an owner entity. A weak entity type normally has a partial key, which is the set of attributes that can uniquely identify weak entities that are related to the same owner entity.

Consider the entity type Dependent, related to Employee which is used to keep track of the dependents of each employee via 1 : N relationship. The attributes of Dependent are Name (the first name of the dependent), Birth Date, Sex and Relationship (to the employee), Twp dependents of two distinct employees may, by chance, have the same value for Name, Birth date.

Sex and Relationship, but they are still distinct entities. They are identified as distinct entities only after determining the particular employee entity to which each dependent is related. Each employee entity is said to own the dependent entities that are related to it.

In ER diagrams, both a weak entity type and its identifying relationship are distinguished by surroundings their boxes and diamonds with double lines. The partial key attribute is underlined with a dashed or dotted line.

Question 9.
What is a participation role? State the circumstances under which the use of role name becomes necessary in description of relationship types.
Answer:
Each entity type that participates in a relationship type plays a particular role in the relationship. The role name signifies the role* that a participating entity from the entity type plays in each relationship instance and helps to explain what the relationship means. For example, in the works .

For relationship type, Employee plays the role of employee or workers and Department plays the role of department or employer. The participation role specifies the existence of an entity depends on its being related to another entity via the relationship type.

Role name are not technically necessary in relationship types where all the participating entity types are distinct, since each participating entity type name can be used as the role name. However, in some case entity type participates more than once in a relationship type in different roles. In such cases the role name becomes essential for distinguishing the meaning of each participation.

Such relationship types are called recursive relationships. The supervision relationship type relates an employee to a supervisor, where both employee and supervisor entities are members of the same Employee entity type. Hence, the Employee entity type participates twice in Supervision. Once in the role of supervisor and once in the role of supervisory.

Question 10.
Define foreign key. How is this concept useful in relational data model? Illustrate with suitable example.
Answer:
Relational data model represents the database of collection of tables comprising different volumes.. It consists of rows and columns. The table name and column name are used to help in interpreting the meaning of volumes of each row. Each row of table is called a data record.

A key is a part of table that uniquely distinguishes it from other tables in a given relation. Keys will typically be used as links, i.e. Key value will appear in other relation to represent their associated table.

Foreign key – An attribute or combination of attribute in one table whose values must either match the parimary key in another table or be null. For example :
Table name: Employees
Primary key: EMPID
Foreign key:

Table name: Vouchers
Primary Name : V. No.
Foreign key : Prep. By and Auth. By

While referencing Employees, the vouchers relations makes use of its other attributes Prep By and Auth By. These attributes act as foreign keys to reference the topics of relation. Employees through its key attribute Empld. The values of Prep By and Auth By cannot be null because of total participation of vouchers in Prep By and Auth By relationship.

Question 11.
What is meant by NULL value? What are the reasons that lead to their occurrence in database relations?
Answer:
NULL values – Absence of a data item is represented by a special value called NULL value. There are three situation which may require the use of NULL value.

• When a particular attribute does not apply to an entity.
• Unknown because4t does not exist.
• Value of an attribute is unknown, although it exists.

In some cases a particular entity may not have an applicable value for an attribute. For such situation, a special value called NULL value is created. NULL can also be used if we do not know the value of an attribute for a particular entity.

An important is that of nulls, which are used to represent the value of attributes that may be unknown or may not apply to a tuple. A special value, called null, is used for these cases. In general, we can have several meanings for NULL values, such as :

• Value unknown.
• Value exists but is not available.
• Attribute does not apply to this tuple.

From the above discussion about the NULL value, it is clear that why it occurs in database relations.

Question 12.
Why are duplicate tuples not allowed in a relation?
Answer:
Each data record, which corresponds to a tuple of a relation, in a table must be distinct. That means no two tuples in a relation can have the same combination of values for all their data items. This is because that a relation, as set of tuples, has to have aH its tuples distinct by definition.

Every relation has at least one key by default, which is the combination of all its attributes. This is called super key. Any such ’ super key, therefore, specifies uniqueness constraint. Such combination, representing super key, may have redundant attributes, implying there by that a more useful concept is that of a key which has no redundancy. This can be shown by following diagram :

Question 13.
For which operations such compatibility is required and why?
Answer:
Relation Data Model represents the database as collection of relations (table) comprising different volumes. It consists of rows and columns. The table name and column name are used to help in interpreting the meaning of volumes of each row. Each row of a table is called a data record. Relational model is concept of an entity and attribute. An entity is like a noun and attribute is a property of an entity, in a relation a table is also called ‘relation’. Each table is made up of records.

Horizontal rows, known as tuple and vertical columns also known as attribute. A domain is a collection of all possible Values from which values for a given column are drawn. The number of attribute determine the degree of relation. The number of tuples in a relation are known as cardinality of the relation. Each relation can be identified by a unique name.

RDBMS organizes the data in the form of table (relation) and primary key field are identified. All the data manipulation are carried out on those relations. Related information may be derived from the user perspective in a meaningful way as new relation from these base relations. The final fundamental of term in relational DB is relationship.

All the entries in relation are single valued. Entries in column are all of the same kind, each column here has unique name. No two tuples in a relation are identical. Order of tuples does not matter. A relation is a set of tuples where in each tuple is an ordered list of values corresponding to attributes of relation. A relation scheme may be interpreted as a declaration in the nature of an assertion. Some relations represents facts about entries while other might represent facts about relationships.

Question 14.
What is the need for database normalisation?
Answer:
Normalisation is the process of refining the data model built by the ER diagram. The normalization technique, logically groups the data over a number of tables, with minimum redundancy of data. The entities or tables resulting from normalisation certain data items with relationship being represented by replication of key data items. The goal of a relational data base design is to generate a set of relation schemes that allow us to store information with minimum redundancy of data and allow us to retrieve information easily and efficiently. The approach followed is to design schemes that are in an appropriate form, one of the so called Normal Form.

Need for Normalisation :

• Improve database design.
• Ensures minimum redundancy of data.
• Reduces need to reorganise data when design is modified/ enhanced.
• Remove anomalies for data base activities.

Long Answer Type Questions

Question 1.
Discuss the basic concepts of Entity Relationship (ER) Model. Illustrate as to how an ER model is diagrammed.
Answer:
ER design is a formal blue print, with a pictorial presentation, in which Entity Relationship (ER) Model concepts are used to represent description of reality.

ER model 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 database is to be designed.

While preparing an ER diagram the following symbols are used to represent different types of entities, attributes, identifier and relationships.


E – R model is commonly used for the following purpose :
(1) To translate different views of data among managers user and programmers to fit into a common framework.
(2) To define data processing and constraint requirements for meeting the different views.
(3) To help in the implementation of the database.
Entities – Anything in the real world with independent existence is called entity. For example, Accounts, Vouchers, Employees, Account type etc. are entities.

Attributes – Attributes are the characteristics which describes the entities. An entity has a value for each of its attributes which is the data stored in the database.

Identifier or Key Attributes – Normally every entity type has one of its attribute, which contain unique values for identifying instance. For example, code is the key attribute of entity type account because its data value are required to be unique.

Relationships – Relationship among two or more entity types represents an interaction among their respective entities. For example, whenever an attribute (say Debit) of one entity type (say Voucher) refers to another entity type (say Account), there exists a relationship between these entities (Voucher and Account).

Weak Entity Types – Entity Types which do not have identifier of their own are, called weak entity type. Such entity type are identified by being related to specific entities from another entity type in combination with some of their attributes value.

By using a hypothetical example of an accounting system following initial design based on ER Model concepts becomes the starting point.

Entities – Voucher, Accounts, Employees, Support Document and Account Type.

Vouchers : Voucher No., Serial No., Voucher Data, Debit A/c, Credit A/c, Amount, Narration, Authorized By, Prepared By, are the attributes.

• Debit and Amount are multi-valued attribute.
• Credit and Amount are multi-valued attribute.
• Voucher No. and Serial No. are key attribute.

Accounts: Code Name, Type are attributes.

• Code and Name are key attributes.

Employees: Employee ID, Name, Address, Phone, ID of Immediate Boss are the attribute.

• Name is a composite attribute.
• Employee ID is the key attribute.

Account Type : Cat ID and Category are the attributes.

• Cat ID is key attribute.

Support : S.No. and Name are the attributes.

• S.No. is a key attribute.

ER Model Representation:
(i) Accounting Database:

(ii) Account Type :

(iii) Employee

(iv) Account :

(v) Vouchers :

(vi) support:

Question 2.
What integrity constraints are specified on database schema? Why is each considered important?
Answer:
There are two integrity constraints which are specified on database schema.
(i) Entity Integrity Constraint
(ii) Referential Integrity Constraint

(i) Entity Integrity Constraint – The entity integrity constraint states that no primary key value can be null. This is because the primary’ key value is used to identity individual tuples in a relation. Having NULL value for the primary key implies that we cannot identify some tuples. For example, if two or more’tuples had null for their primary keep, we might not be able to distinguish them if we tried to reference them from other relations.

(ii) Referential Integrity Constraint – Key constraint and entity integrity constraint are specified an individual relations. The referential integrity constraint is specified between two relations and is used to maintain the consistency among tuple in the two relations. Informally, the referential integrity constraint states that tuple in one relation that refers to another relation must refer to an existing tuple in that relation.

In referencing Account Type, Accounts relation uses its attribute Type, which acts as foreign key to reference the tuples of relations. Account type cannot be null because of total participation of Accounts in classify relationship. Similarly, consider another example in which the relation Vouchers (V.No., S.No., V. date, Debit Amount, Credit Amount, Prep. By, Auth. By, Narration) reference two other relations as shown in figure.

First, it reference. Accounts (Code, Name, Type). In referencing Accounts the Voucher relation uses its attributes Debit and Credit, which act as a Foreign key to reference the tuples of relation. Accounts through its primary key, Code. The value of Debit and Credit cannot be null because of total participation of vouchers in debit and credit relationship.



Second, it references Employees (Emp ID, FName, MName, LName, Address, Phone No. Super ID) while referencing Employees, the Vouchers relation makes use of its other attributes Prep. By and Auth. By. These attributes act as foreign keys to reference the tuples of relation Employees through its key attribute Emp ID. The vaiue of Prep. By and Auth. By cannot be null because of total participation of vouchers in Prep, by and Auth. by relationships.

All integrity constraints should be specified on the relational database scheme if we want to enforce these constraints on the database states. Hence, the DDL includes provisions for specifying the various types of constraints so that the DBMS can automatically enforce them.

Most relational DBMS’s support key and entity integrity constraints and make provisions to support referential integrity. These constraints are specified as a part of data definition. That is why each integrity constraints are important.

Question 3.
Discuss the different types of update operations in relation to the integrity constraints which must be satisfied in a relational database model.
Answer:
The operation of the relational model can be categorized into retrievals and updates.
There are three basic update operations on relations :
(i) Insert,
(ii) Delete
(iii) Modify

(i) Insert – Insert is used to insert a new tuple or tuples in a relation. For example, an attempt to insert 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 the integrating constraints.

(ii) Delete – Delete is used to delete tuples from relation. A particular data record from a table can be removed by performing such an operation. The delete operation can violate only referential integrity, if the tuple being removed is referenced by foreign key from other tuples in the database.

(iii) Update or Modify –  Update is used to change the value of some attributes in existing tuple. This is useful in modifying existing values of an accounting record in a relation. Usually this operation does not cause problems provided the modification is directed or neither primary key nor foreign key. Whenever these operations are applied, the integrity constraints specified on the relational database scheme should not be violated. We must enforce integrity constraints specified on the relational data scheme.

Question 4.
Discuss the steps you would take to transform on ER ‘ Model into various relations of Relational Data Model. Give suitable examples.
Answer:
The guidelines required to design the relational database schema attempt to provide a step-by-step procedure that transforms ER design into Relational Data Model design to constitute the desired database. In the context of ER Model given below, the following steps are required to cause its transformation into relational data model :

(i) Creating a relation for every strong entity – For each strong entity type in ER Model, a separate relation that includes all the simple attribute of that entity is created. Choose Primary Key from Key attributes Separate Relation for strong entities have created with primary key shown below’:

• Employee
  (Emp Id, Fname, Lname, Address, Phone No., Super ID)
• Accounts
  (Code, Name, Type)
• Vouchers
  (Vno, V Date, Debit, Amount, Credit, Narration, Prep. By, Auth. By)
• Accounts Type
  (Cat ID, Category)

(ii) Create a separate relation for each weak entity type –  For every weak entity type, a separate relation is created by including its attributes. The Primary Key of this relation is the combination of its unique attribute(s) for a particular tuple of the owner relation along with Primary Key attribute of such owner relations.

For example, Supprt Entity, with Vouchers as its owner entity, does not have a primary key of its own. It has partial key which is the Sno assigned to each document. Therefore, the primary key of voucher, Vno along with Sno is designed as composite key for support entity and the relation so formed is given below :

• Support
  (Vno, Sno, D. Name, S. Date)

(iii) Identify entity types participating in binary 1: N relationship type – Identify, the first relation on n-side of relationship and second on 1 – side of such relationship. The primary key of second should be included in first relation as its foreign key.

(iv) Identify entity type participating in binary M: N relationship type – For each binary M : N relationship type, create a new relation to represent such relationship. This new relation should include as foreign keys, the primary keys of the relations that represents the participating entity types.
Finally, the following relations have been formed to constitute the relational data model for one example reality.

• Employee
  (Emp Id, F name, L name, Address, Phone No., Super ID)

Project Work

Question 1.
Consider the following reality in a business enterprise, which is engaged in trading activity.
* It buys and sells a given number of items each of which is uniquely identifiable. Each unit of item is expressed in numbers or Kilograms.
* It procures its supplies from a given number of suppliers who can supply any number of items at a time. Each transaction is on credit for a particular period of time expressed in days.
* It sells various items to its customers on credit for a definite period of time expressed in days.
* Each purchase is made through a regular invoice, which has its distinct number for the supplier. It is duly dated, mentions the items being transacted, their quantities and prices and total amount of invoice.
* Design an ER schema for a database application for purchase and sales accounting and also show as to how it shall be transformed into various relations of a relational data model.
Answer:
ER Schema
Entities – Items, Suppliers, Customer, Purchase Invoice

Attributes
Items – Item Code, Item Name are the attribute.
* Item Code is the Key attribute.

Suppliers – Vendor Code, Vendor Name, Address, Phone
Item Supplied, Credit period are the attribute.

* Vendor Code is the Key attribute.
Customers – Customers Code, Customer Name, Address, Phone
Item Purchase, Credit period are the attributes.

* Customer Code is the Key attribute.
Purchase Invoice – Invoice No., Invoice Date, Quantity, Rate, Amount
* Invoice No. is the Key attribute.
Answer:

Question 2.
Following transactions of M/s Soumya Enterprises are given to you for the period ending March 31,2002.

All these transactions have been stored in database tables as shown below under (Model-I of database design). Data in Accounts table appears as follows :

Show how will these transactions appear as accounting data in following vouchers table.

V no. – identify of a transaction stored through a voucher
V date – to date of transaction
Debit – to code of account being debited
Amount – Amount of transaction
Credit – Code of account being credited
Narration – Narration of transaction.
Answer:

Question 3.
M/s Soumya Exports set up a garment export business on March 1,2002. Their transactions for the month ending March 31,2002 are given below :


All these transactions have been stored in database tables as shown below under (Model-I of database design). Data in Accounts table appears as follows :

Show how will these transactions appear as accounting data in following accounting data tables

V. no: Identify of a transaction stored through a voucher
V date : Date of transaction
Acc_code : Code of account, being debited or credited
Code : Codes of accounts being credited or debited, depending on value of Vtype (= 0, means codes being debited, 1 means codes being credited)
S.no.: Serial number of accounts being debited in debit voucher and those being credited in credit voucher
Vtype: 0 = means debit voucher, 1 = credit voucher
Amount : Amount of transaction
Narration : Narration of transaction.
Answer:

Question 4.
Write relational operation expressions and relevant SQL statements for following queries using Database Design Model-I and Model-II:
(a) Retrieve the voucher details and type of voucher authorised by a particular employee.
(b) Retrieve every bank payment voucher details, account name, amount. You are given that bank account code = “632001”.
(c) Find details of cash vouchers pertaining to an expense account whose account code = “711003”. You are given that cash account code = “631001”. ’
(d) Make a list of accounts and amount with respect to which a voucher has been either prepared or authorised by a particular employee.
(e) Retrieve details of vouchers without support documents.
(I) List details of documents with at least one support documents.
(g) Find all vouchers with total amounts raised during a particular month.
(h) Retrieve all vouchers prepared by an employee whose First name is “Smith”.
Ans.
(a) Relational Operation expression SELECT MAJOR = “A001” from the relation (table) VOUCHERS SQL Statement SELECT* .
FROM VOUCHERS WHERE Auth By = ‘A001’

(b) Relational Operation expression
JOIN VOUCHER table and ACCOUNTS table > Over Credit = “632001”
SQL Statement SELECT Details, Name FROM VOUCHERS, ACCOUNTS WHERE Credit = “632001”

(c) Relational Operation expression
JOIN VOUCHER fcjble and ACCOUNTS table Over Debit = “711003” and Credit = “631001” SQL Statement SELECT*
FROM VOUCHERS, ACCOUNTS
WHERE Debit = “711003” and Credit = “632001”

(d) Relational Operation expression
JOIN VOUCHERS table and ACCOUNTS table
Over Prep. By = “S001” and Auth. By = “A001”
SQL Statement
SELECT Name, Amount
FROM VOUCHERS, ACCOUNTS
WHERE Prep. By = “S001” or Auth. By = “A001”

(e) Relational Operation expression
JOIN VOUCHERS table, and ACCOUNTS table Over Vno ^ Vno SQL Statement SELECf*
FROM VOUCHERS, SUPPORTS WHERE Vno * Vio ‘

(f) Relational Operation.expression
JOIN VOUCHERS table and SUPPORT table Over Vno = Vno SQL Statement
SELECT!*
FROM VOUCHERS, SUPPORTS WHERE Vno = Vno

(g) Relational Operation expression PROJECT Vno, Amount > *
from the relation (Table) VOUCHERS SQL Statement SELECT Vno, Amount > *
FROM VOUCHERS

(h) Relational Operation expression
JOIN VOUCHER table and EMPLOYEES table Over Name = “Smith”
SQL Statement
SELECT Vno, V date, Amount, Narration , FROM VOUCHERS, EMPLOYEES WHERE F Name = “Smith”.

Question 5.
Write relational operation expression and relevant SQL statements for following queries using Database Design Model-l and Model-II:
(a) Retrieve all vouchers pertaining to a particular account with amounts ranging between Rs. 10,000 to Rs:20,000.
(b) Retrieve details of each voucher whose support docu ment has the same date as that of the voucher itself,
(c) Retrieve details of voucher authorised by employees who do not have supervisors.
(d) Find sum of cash payments, maximum payments, minimum payments and average.
(e) Find sum of cash payment, maximum and minimum amount with respect to a particular account code.
(f) Retrieve every bank payment voucher details, account name, amount pertaining to a particular period pertaining to a particular expense account.
(g) Find details of cash vouchers pertaining to a particular expense account.
(h) Make a list of accounts and amount with respect to which a voucher has been either prepared or authorised raised during a particular month.
(i) Find all vouchers with total amounts raised during a particular month.
(j) Retrieve all vouchers prepared by an employee whose last name is Dev.
(k) Retrieve details of each voucher whose support document has the same date as that of the voucher itself.
Answer:
(a) Relational Operation expression
SELECT MAJOR = “Amount” between Rs. 10,000 to Rs. 20,000
from the relation (tables) VOUCHERS SQL Statement SELECT Vno Amount FROM Vouchers .
WHERE Amount between Rs. 10,000 and Rs. 20,000

(b) Relational Operation, expression
JOIN VOUCHERS table and SUPPORT table Over V Date = V date * SQL Statement SELECT * ‘
FROM Vouchers, Supports WHERE V Date = V date

(c) Relational Operation expression SELECT MAJOR = “Auth. By = A001” from the relation (table) VOUCHERS SQL Statement
SELECT *
FROM Vouchers WHERE Auth. By = “A001 ”

(d) Relational Operation expression
SELECT Debit As Code, SUM (Amount) As Total Min (Amount) As Minimum Arg (Amount) As Average, Max. (Aifiount) As Maximum From the table VOUCHERS
SQL Statement
SELECT Debit as Code, Sum (Amount) As Total, Min (Amount). As Minimum, Max (Amount). As Maximum, Avg. (Amount) As Average.
FROM Vouchers .
WHERE Debit like “631”
Group By Debit

(e) Relational Operation expression
‘ SELECT Debit As Code = “711001”, Sum (Amount) As Total, Min. (Amount) As Minimum, Avg. (Amount) Average, Max. (Amount) As Maximum from the table Vouchers SQL Statement
SELECT Debit As Code, Sum (Amount) As Total, Min. (Amount) As Minimum, Max. (Amount) As Maximum, Avg. (Amount) As Average.
FROM Vouchers WHERE Debit like “631”

(f) Relational Operation expression
JOIN VOUCHERS table and ACCOUNTS table Over V Date between I and 2 SQL Statement
SELECT V No, V Date, Amount, Name, Code FROM Vouchers, Accounts
WHERE Code = “632001 ” and V date between Date 1 and Date 2

(g) Relational Operation expression
JOIN VOUCHERS table and ACCOUNTS table over Debit = “711” and Credit = “631 ”
SQL Statement SELECT *
FROM Vouchers, Accounts WHERE Debit = “711*” and “631 *”

(h) Relational Operation expression
JOIN VOUCHERS table and ACCOUNTS table
Over Prep, by = “S001 ” or Auth. by = “A001 ”
SQL Statement
SELECT Name, Amount
FROM Vouchers, Accounts
WHERE Prep. By = “S001” or Auth. By = “A001”

(i) Relational Operation expression PROJECT Vno. Amount > * from the relation (table) Vouchers SQL Statement
SELECT V No, Amounts > *
FROM Vouchers

(j) Relational Operation expression
JOIN VOUCHERS table and EMPLOYEES table Over L Name = “Dev”
SQL Statement
SELECT Vno, V date, Amount, Narration, L Name FROM Vouchers, Employees WHERE L Name = “Dev”

(k) Relational Operation expression
JOIN VOUCHERS table and SUPPORTS tabie Over V date = S date SQL Statement
SELECT V No, V Date, S No, S Date, Amount FROM Vouchers, Supports WHERE V Date = S Date.