Structuring Database for Accounting

the basis of creating collection of data items on which these functions are to be applied. Consider the following examples. ( ) To find the sum, minimum and maximum of cash payment during April, . The cash account code begins with “ ” ( Model-I ) Debit AS Code, SUM( Amount ) AS Total, MIN( Amount ) As Minimum, MAX( Amount) As Maximum Vouchers Debit like “ *” GROUP BY Debit ( Model-II ) Code, SUM( Amount ) AS Total, MIN (Amount) As Minimum, MAX( Amount ) As Maximum Vouchers AS V, Details AS D V.Vno=D.Vno, Ttype= and Code Like “ *” GROUP BY D.Code Key Terms Introduced in the Chapter Database System Entity Relationship (ER) Model Reality Database Rational Data Model Accounting Intermedia Transaction Voucher Credit Voucher Debit Voucher Attributes Interacting with Database Designing Database for Accounting Summary with Reference to Learning Objectives ( ) Database Concepts Reality : It consists of different components of an organisation such as people, facilities and other resources.

Data : It represent data concerning people, places, objects entities, events, etc. and non-financial nature. Database : It was a shared collection of inter-related data tables, tiles or structures which are designed to most varied information needs of all organisation. International : Processed data organisation in a form that is suitable for decision- making.

DBMS : A collection of programmes that enable users to create and maintain a database. ( ) Database System Concepts and Architecture Data model : Collection of concepts used to describe the structure of a database. Database Schemes : The description of a database is called its scheme. Data Base State and Instances : Data in a database at a particular movement is called database state.

( ) Entity Relationship (ER) Model An important concept of data model mostly used in data base oriented application. The major elements of ER model are entities, Attributes, identities and relationship that are used to express reality for which a data base is to be designed. ( ) Relation Data Model (RDM) It represent the database at 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. Questions for Practice Short Answers . State main categories of data models.

. How are computers useful in processing the accounting data? . What do you understand by accounting data?

Discuss the stages through which it is finally transformed for being presented as information in financial statements. . What do you understand by database. How does it differ from DBMS?

. What is meant by entity type? How it is different from entity set? Illustrate by giving suitable example from accounting reality.

. What do you understand by relationship type? How is it different from relationship instance and relationship set? .

What do you understand by multi-valued attribute? How is it different from complex and composite attribute? Illustrate by giving suitable example. .

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. . What is a participation role?

State the circumstances under which the use of role names becomes necessary in description of relationship types. . Define foreign key. How is this concept useful in relational data model?

Illustrate with suitable example. . What is meant by NULL value? What are the reasons that lead to their occurrence in database relations?

. Why are duplicate tuples not allowed in a relation? . What do you understand union compatibility of relations?

For which operations such compatibility is required and why? . What is the need for database normalisation? Long Answers .

Discuss the basic concepts of Entity Relationship (ER) Model. Illustrate as to how an ER model is diagrammed. . What integrity constraints are specified on database schema?

Why is each considered important? . Discuss the different types of update operations in relation to the integrity constraints which must be satisfied in a relational database model. .

Discuss the steps you would take to transform an ER Model into various relations of Relational Data Model. Give suitable examples. Project Work (i) 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. (ii) Following transactions of M/s Soumya Enterprises are given to you for the period ending March , .

March Additional capital brought in cash by proprietor, Rs. , , , out of which deposited into a bank account Rs. , , Received Cheque for Rs. , from K & Co.

on account Issued Cheque for Rs. , in favour of Jain & Sons Payment of rent for the month Rs. , Goods purchased Rs. , by Cash Goods sold to R & Co Rs.

, Purchased furniture for office use Rs. , Paid fire insurance premium by Cheque Rs. , Paid cash to Jayram Bros. Rs.

, in full settlement of their account standing at Rs. , Payment of salary to staff Rs. , 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: Accounts Code Name 110001 Capital Account 221019 Jain & Sons 411001 Furniture Account 411002 Fixtures & Fittings Account 621001 K & Co 631001 Cash Account 632001 Bank Account 641001 Salary in Advance Account 711001 Cartage Account 711002 Salaries Account 711003 Rent Account 711005 Insurance Premium 711006 Discount Account 811001 Sales Account Show how will these transactions appear as accounting data in following vouchers table.

Vno : Identity of a transaction stored through a voucher. Vdate : 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. (iii) M/s Soumya Exports set up a garments export business on March1, . Their transactions for the month ending March , are given below : March Capital brought in cash by proprietor, Rs.

, , , out of which deposited into a bank account Rs. , , Received Cheque for Rs. , from Kailash Nath & Co. as advance account Issued Cheque for Rs.

, to Jackson Bros. as advance for supplies Payment of rent for the month Rs. , Purchased Computer system for office use Rs. , , payment for which made by Cheque Goods purchased Rs.

, , , payment made by Cheque. Goods purchased from Jackson and Bros. for Rs. , Goods sold to Rajeshwar & Sons Rs.

, Purchased Furniture for office use Rs. , Paid fire insurance premium by Cheque Rs. , Paid Cash To Jackson Bros. Rs.

, in full settlement of their outstanding balance of Rs. , Payment of salary to staff Rs. , 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: Accounts Code Name 110001 Capital Account 221019 Jackson Bros.

411001 Furniture Account 413001 Office Equipment 621001 Kailash Nath & Co 621002 Rajeshwar & Sons 631001 Cash Account 632001 Bank Account 641001 Salary in Advance Account 711001 Cartage Account 711002 Salaries Account 711003 Rent Account 711005 Insurance Premium 711006 Discount Account 811001 Sales Account Show how will these transactions appear as accounting data in following accounting data tables. Vno : Identity of a transaction stored through a voucher Vdate : date of transaction : code of account being debited or credited Code : Codes of accounts being credited or debited, depending on value of Vtype( = , means codes being debited, means codes being credited) Sno : Serial number of accounts being debited in debit voucher and those being credited in credit voucher Vtype : = means debit voucher, = credit voucher Amount : Amount of transaction Narration : Narration of transaction (iv) 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.

(f) List details of documents with at least one support document. (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”. (iv) Write relational operation expressions and relevant SQL statements for following queries using Database Design Model-I and Model-II.

(a) Retrieve all vouchers pertaining to a particular account with amounts ranging between Rs. , to Rs. , . (b) Retrieve details of each voucher whose support document 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 ranging from Date1 to Date .

(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 by a particular employee. (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. Checklist to Test Your Understanding A. (a) T (b) T (c) T (d) F (e) F B. (a) Weak entity (b) Computer based (c) Timeware (d) Liveware (e) Total participation (f) Multi-valued (g) Full functional