Structuring Database for Accounting

Model-II : The set relations given below are based on modified example reality that uses Credit and Debit vouchers shown in figures . and . . Fig.

. : Schema diagram for the accounting system relational database Schema Illustration No Mr. Philips commenced business with cash and for that purpose opened a bank account on April , . His transactions for the month are as given below : Date Transactions Amount Rs .

Apr. Commenced business with cash , , Apr. Cash deposited Into bank , , Apr. Goods purchased and payment made by Cheque No.

765421 , , Cheque No. 765422 issued to M/s Nahar Transports for carriage , Apr. Rent for the month April, paid by Cheque No. 765423 , Apr.

Goods purchased for cash from M/s R.S. & Sons , Paid for carriage to M/s Saini Transports , Apr. Goods sold to Kemp & Co. , , Apr.

Goods purchased from M/s Jayram Bros. , , Apr. Sold goods for cash to M/s Kumbley & Co. , Apr.

Paid for adverisement by Cheque No. 765424 , to M/s ABN Cables Apr. Received a bill of exchange from Kemp & Co.payable , , after months Apr. Bill of exchange received from Kemp & Co.

discounted for , , Apr. Goods returned to Jayram Bros. being defective , Apr. Advance cash payment to salesman for marketing tour , Apr.

Paid for insurance of godown Cheque No. 765425 , Apr. Paid for fuel, power and electricity , Apr. Salary paid in advance to bimal , Apr.

Accepted a bill of exchange payable after four months , , in favour of Jay Ram Bros. Apr. Returns from M/s Kumbley & Co., settled by , Cheque No. 765427 Apr.

Cash withdrawn by proprietor for household expenses , Apr. Advance to salesman adjusted for cash after recording expenses : Entertainment , Travelling , Boarding and Lodging , Apr. Goods taken from stock for personal use , Apr. Furniture purchase from M/s S.N.

Furnitures , by Cheque No. 765428 Apr. A part of existing stock set a side for usage as , office furniture Apr. Salary for the month paid by Cheques Cheque No.

765429 to Aditya , Cheque No. 765430 to Bimal ( one-fourth of advance , adjusted) Cheque No. 765431 to Smith , Cheque No. 765432 to Sunil , Apr.

Payment of telephone bill by Cheque No. 765433 , Apr. Paid for wages by cash , The database state pertaining to Accounts and Employees table is as given below : Accounts Code Name Type 110001 Capital Account 221019 Jain & Co. 221020 Jayram Bros.

222001 Bill Payables 411001 Furniture Account 411002 Office Fittings 412002 Plant and Machinery Account 621001 Kemp & Co. 621002 Kumble & Sons 631001 Cash account 632001 Bank account 641001 Salary in advance account 641002 Advance to salesman 642001 Bills receivable 651001 Drawings 711001 Purchases 711002 Purchases returns 711003 Carriage inwards 711004 Fuel, power and electricity 711011 Wages 712001 General expenses 712002 Rent account 712003 Salaries account 712004 Discount account 712005 Adverisement 712006 Entertainment 712007 Travelling 712008 Boarding and Lodging 712009 Communication expenses 712010 Insurance 811001 Sales account 811002 Sales returns Account Type CatId Category Expenditure Income Assets Liabilities Employees EmpId Fname Minit LName Address PhoneNo SuperId Aditya K Bharti Bimal S Jalan Smith K John Sunil K Sinha The solution based on Model-I which lends support to Transaction Voucher with one Debit and one Credit as shown in figure . , shall appear as follows : Vouchers vNo Debit amount vDate Credit narration AuthBy PrepBy Rs. 631001 , , Apr.

110001 Commenced business with cash 632001 , , Apr. 631001 Deposited into bank 711001 , , Apr. 632001 Purchases from R.S & Sons 711003 , Apr. 632001 Paid to M/s Nahar Transports 712002 , Apr.

632001 Paid rent for April, 711001 , Apr. 631001 Goods purchased from R.S. & Sons 711003 , Apr. 631001 Paid for carriage to M/s Saini Transports 621001 , , Apr, 811001 Goods sold 711001 , , Apr.

221020 Invoice no. dated : 631001 , Apr. 811001 Goods sold to M/s Kumbley & Co. 712005 , Apr.

632001 Paid to M/s ABN Cables 642001 , , Apr. 621001 Maturity Date : July , 711002 , Apr. 221020 Goods returned Note No. dated : 712004 , Apr.

642001 Discount on Bill of exchange from Kemp & Co. 641002 , Apr. 631001 Advance payment to sales for marketing tour 712010 , Apr. 632001 Insurance of godown 711004 , Apr.

631001 Payment for fuel, power and electricity 641001 , Apr. 631001 Salary paid in advance to Bimal 221020 , , Apr. 222001 Settlement by accepting a bill of exchange 811002 , Apr. 632001 Goods returned by M/s Kumbley & Co.

651001 , Apr. 631001 Withdrawal by proprietor for household expenses 712006 , Apr. 641002 Expenses during tour : Support vouchers - 712007 , Apr. 641002 Expenses during tour : Support vouchers - 712008 , Apr.

641002 Expenses during tour : Support vouchers - 641002 Apr. 631001 Final settlement of Refer to J.V No : / 651001 , Apr. 711001 Goods taken for private use 411001 , Apr. 632001 Furniture purchased from S.N.

Furniture 411001 , Apr. 711001 Goods purchased for trading put to office use 712001 , Apr. 632001 Salary to Aditya- Apr, 712001 , Apr. 632001 Salary to Bimal-April, after adjustment 712001 , Apr.

632001 Salary to Smith- April 712001 , Apr. 632001 Salary to Sunil- April, 712009 , Apr. 632001 Telephone bill 711011 , Apr. 631001 Payment of Wages Shortcomings The above solution, being based on transaction voucher with one debit and one credit in a transaction requires multiple vouchers for one real transaction.

For example, a transaction dated April , “Salary for the month paid by cheque” requires four vouchers to . One transaction should be recorded possibly through one voucher only. The solution based on Model-II which lends support to Debit Voucher (with Multiple Debits and one Credit) and Credit voucher (with one Debit and multiple Credits) as shown in Figure: . and figure .

shall appear as follows : Vouchers Vno Vdate Vtype PrepBy AuthBy Apr. 631001 Apr. 632001 Apr. 632001 Apr.

632001 Apr. 631001 Apr. 811001 Apr. 221020 Apr.

631001 Apr. 632001 Apr. 621001 Apr. 632001 Apr.

221020 Apr. 642001 Apr. 631001 Apr. 632001 Apr.

631001 Apr. 631001 Apr. 222001 Apr. 632001 Apr.

631001 Apr. 641002 Apr. 631001 Apr. 711001 Apr.

632001 Apr. 711001 Apr. 632001 Apr. 632001 Apr.

631001 Details Vno Sno Code Amount Narration 110001 , , Commenced business with cash 631001 , , Deposited into bank 711001 , , Purchases from R.S & Sons 711003 , Paid to M/s Nahar Transports 712002 , Paid rent for April, 711001 , Goods purchased from R.S. & Sons 711003 , Paid for carriage to M/s Saini Transports 621001 , , Goods sold 711001 , , Invoice No. dated: 811001 , Goods sold to M/s Kumbley & Co. 712005 , Paid to M/s ABN cables 642001 , , Maturity date July , 711002 , Goods returned Note No.

dated. 712004 , Discount on bill of exchange from Kemp & Co. 641002 , Advance payment to sales for marketing tour 712010 , Insurance of godown 711004 , Payment for fuel, power and electricity 641001 , Salary paid in advance to Bimal 221020 , , Settlement by accepting a bill of exchange 811002 , Goods Returned by M/s Kumbley & Co. 651001 , Withdrawal by proprietor for household expenses 712006 , Expenses during tour: Support Vouchers - 712007 , Expenses during tour: Support Vouchers - 712008 , Expenses during tour: Support Vouchers - 641002 Final settlement of Refer to J.V no.

/ 651001 , Goods taken for private use 411001 , Furniture purchased from S.N. Furniture 411001 , Goods purchased for trading put to office use 712001 , Salary to Aditya Apr. 712001 , Salary to Bimal Apr. after adjustment 712001 , Salary to Smith Apr.

712001 , Salary to Sunil Apr. 712009 , Telephone bill 711011 , Payment of Wages 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. 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) An ........... 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 ............ After appreciating the way accounting data is presented in above database models, let us understand as to how the queries on such databases are expressed as relational operations.