Structuring Database for Accounting

Designing Relational Database Schema and Interaction with Databases   

A database is a collection of interrelated data files or structures. It is designed to meet the various information needs of the organization and is integrated and shared. Thus, a relational database schema is an arrangement of relation states in such a manner that every relational database state fulfills the integrity constraints set on the relational database schema. Let us now discuss RDBMS in detail.

Suggested Videos

Play
Play
previous arrow
next arrow
previous arrownext arrow
Slider

 

RDBMS: Designing Relational Database Schema

The guidelines or rules to design the relational database schema provide a step-by-step procedure. This procedure converts the ER design into a Relational Data model design in order to form the desired database. Following are the steps to convert the ER design into a Relational Data model design:

1. Creation of relation for each substantial entity

For every strong or substantial entity type in ER schema, we need to create a separate relation including all the simple characteristics of that entity. This entity shall have a primary key.

We can choose either a key characteristic of the entity or a set of simple characteristics that will unambiguously identify this entity as the primary key of this relation.

For example, delivery vehicles entity is strong as it finds its primary key in Registration No. which is its unique characteristic.

Learn more about Relational Database and Schema here in detail.

2. Creation of a separate relation for each weak entity type

Each weak entity needs to have an owner entity. It also has an identifying relationship by which we can identify this weak entity type. We need to create a separate relation for every weak entity including its characteristics.

A combination of its unique characteristics and a primary key characteristic of such owner relation form its primary key. Further, we include the primary key of the owner entity as the foreign key in this relation key of owner entity and the partial key of the weak entity.

rdbms

3. Identification of entity types participating in binary 1: N relationship type

We need to identify first the relation on n-side of relationship and secondly on 1-side of this relationship. We include the primary key of the second relation in the first relation as its foreign key.

For example, a single employee can pass a number of purchase vouchers. It indicates that Purchase Vouchers entity participates in AuthBy relationship on n-side whereas Employees entity is participating in the same relationship on 1-side.

4. Identification of entity types participating in binary M: N relationship type

We need to create a new relation for each binary M: N relationship type to represent this relationship. This relation needs to include as foreign keys, the primary keys of the relations representing the participating entity types.

 

Interaction with Databases

The Structured Query Language or SQL makes it easy for the user to interact with the database. It is a comprehensive database language and contains statements for data definition, query, and update.

SQL also facilitates the migration of the users from one database application to another database application. Data Query Language (DQL) is a subset of SQL and is mostly in use to get the answer of most of the basic queries. The basic set of queries consists of:

  1. SELECT: We use this clause to specify the data or information that we require to answer the query.
  2. FROM: We use this clause to specify the source of data for answering the query which can be a data table, an existing query or a combination of both.
  3. WHERE: We use this clause to specify the conditions that we apply to narrow down the choice of data in order to extract the data information that is desirable in the SELECT clause.

Solved Example For You

Explain the queries that we need to consider while using the database design given in Model-I and Model-II.

Ans.

We generally use MS ACCESS implementation to give the solution to these queries. The queries and their solutions are as follows:

1. To recover all columns of data from a table, subject to a specific condition

In order to project all the characteristic values of the selected tuples, we need to specify an asterisk sign (*). It stands for all the characteristics. For example, the query is to recover all the columns of purchase vouchers passed by an employee whose employee id is B002.

Solution:

SELECT *

FROM             Purchase Vouchers

WHERE          AuthBy= “B002”;

3. To recover selected columns of data from a table, subject to a specific condition

For example, the query is to recover purchase vouchers with voucher number, voucher date and authorized by columns where the vouchers are dated 6 April 2018.

Solution:

SELECT          Vno, Vdate, AuthBy

FROM             Purchase Vouchers

WHERE          Vdate = #4/6/2018#:

3. WHERE clause is unspecified

The absence of the WHERE clause indicates that we need to select the tuples from a relation without applying any condition. For example, the query is to find out the list of all the accounts that have been credited under Model I.

Solution:

SELECT DISTINCT       Credit As Code

FROM                              vouchers:

4. Unique characteristic names and renaming (aliasing):

The SQL allows the use of the same name for two or more characteristics as long as such characteristics are in different relations.

Therefore, in a case where the use of a common characteristic with a particular name across the relations persists, it is essential to qualify the characteristic name with relation name in which it exists.

For example, the query is to recover a list of accounts and the amounts credited due to bank receipts. The Bank A/c Code is 420.

Solution:

Model I

SELECT          Narration, Credit As Code, Amount

FROM             Vouchers

WHERE          Debit LIKE “420*”;

Share with friends

Customize your course in 30 seconds

Which class are you in?
5th
6th
7th
8th
9th
10th
11th
12th
Get ready for all-new Live Classes!
Now learn Live with India's best teachers. Join courses with the best schedule and enjoy fun and interactive classes.
tutor
tutor
Ashhar Firdausi
IIT Roorkee
Biology
tutor
tutor
Dr. Nazma Shaik
VTU
Chemistry
tutor
tutor
Gaurav Tiwari
APJAKTU
Physics
Get Started

Leave a Reply

Your email address will not be published. Required fields are marked *

Download the App

Watch lectures, practise questions and take tests on the go.

Customize your course in 30 seconds

No thanks.