The compilation of these Structuring Database for Accounting Notes makes students exam preparation simpler and organised.
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.
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, the delivery vehicles entity is strong as it finds its primary key in Registration No. which is its unique characteristic.
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 the owner entity and the partial key of the weak entity.
3. Identification of entity types participating in binary 1: N relationship type
We need to identify first the relation on the n-side of the relationship and secondly on the 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 to 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.
Example:
Question:
Explain the queries that we need to consider while using the database design given in Model-I and Model-II.
Answer:
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”;
2. 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 the 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*”;