Structuring Database for Accounting
Data Processing Cycle
In order to understand the dynamics of database design, let us understand the data processing cycle in the context of accounting. Data processing involves the technique of collecting, sorting, relating, interpreting and computing data items in such a manner as to provide meaningful and useful information for decision-making.
The necessary steps involved in data processing cycle are data capturing, inputting, processing and generating information available to the user. Data processing cycle, when thought of in the context of accounting, requires a series of steps that have been described below briefly :
(i) Source Documents : The first step is to capture accounting data from transaction(s) so as to prepare a document, called voucher (as already stated earlier), that expresses and documents an accounting transaction. The relevant accounting data is set out in the voucher. These documents are so designed as to permit the recording of accounting data in a systematic manner.
(ii) Input of Data : The accounting data contained in vouchers is to be entered in a computer’s storage device. This is achieved by using a pre-designed Data Entry Form. This data entry form is designed in a manner that it is similar to physical voucher document. The data entry form is designed using software and it is made to appear on the computer monitor so that the data is entered.
(iii) Data Storage : A suitable data storage structure is required to provide for a blank data record as shown below:
The above blank record that is used for storing the input of data pertaining code of account, name of account and the category type to which it belongs is shown below as :
|
Code
|
Name
|
Type
|
|
11001
|
Capital Account
|
4
|
|
711001
|
Purchases Account
|
1
|
Hypothetically, the category type 4 above refers to Liabilities and the category type 1 indicates Expenses. The data storage structures (also called data tables) are created as a part of structuring database for accounting.
(iv) 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. Alternatively, the transformed data can be directly presented in the form of a report.
(v) Output of Data : The accounting reports such as ledger, trial balance, etc. are obtained in a pre-designed format by accessing the transformed data.
Now that you have understood the way data content is stored in structured manner, we shall discuss how the data structures are designed in consonance with the data content that emerges from accounting transactions.
Designing Database for Accounting
Both computerized and computer-based AIS require a definite data structure for storing the accounting data. As already mentioned, the databases are used for storing accounting data.
The process of designing database (for accounting) begins with a reality (or accounting reality) that is expressed using elements of a conceptual data model. The process of designing a database for accounting is best described through a flow chart.
Reality : It refers to some aspect of real world situation, for which database is to be designed. In the context of accounting, it is accounting reality that is to be expressed with complete description.
ER Design : This is a formal blue print, with a pictorial presentation, in which Entity Relationship (ER) Model concepts are used to represent description of reality.
Relational Data Model : It is representational data model through which ER design is transformed into inter-related data tables along with the restriction in the form of rules that are specified to ensure the consistency and integrity of stored data.
Normalization : This is process of refining a database design (that consists of inter-related data tables) through which the possibility of duplicate or redundant data items is reduced or eliminated.
Refinement : This is the outcome of the process of normalization as mentioned above. The final database design is arrived at after the process of normalization is completed.
Entity Relationship (ER) Model
- It 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 a database is to be designed.
- The model is best depicted with the help of ER symbols. While preparing an ER Diagram, the following symbols are used to represent different types of entities, attributes, identifiers and relationships :
- The elements of ER model that are meant to describe and display the reality are discussed in the context of an accounting reality given below :
- Using a hypothetical example of accounting system of an organization, following statements of reality becomes the starting point of discussion in describing the ER Model concepts.
Example Reality :
- Accounting Transactions of an organization are documented using a voucher.
- Each vouchers is assigned a serial number, which begins with “01” indicating first vouchers of the accounting period. There is only one simple transaction voucher used for documenting the transactions.
- Each voucherdocuments date of transaction, account name along with its account code for debit as well as credit entry.
- Each voucher indicates the amount and narration with respect to accounting transaction.
- Support documents such as bills, receipts, contracts, etc. also may be attached to an accounting voucher.
- Each Voucher is prepared by a particular Employeeand authorized by another employee.
- There is an exhaustive list of Accounts with respect to which the transactions are documented. Each Account carries a unique numeric code with its width equal to six digits.
- Each Account is classified as belonging to one of the Accounts Types: Expenditure, Income, Assets and Liabilities.
Entities
Anything in the real world with independent existence is called entity such as an object with physical existence (e.g. car, person, house) or conceptual existence (e.g. a company, job, university course, account, voucher). In the context of above accounting reality, there exist five entities: Accounts, Vouchers, Employees, Accounts Type and Support Documents.
The accounting data is captured through these entities.
Attributes
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. An entity has a value for each of its attributes, which is the data stored in the database.
There are several types of attributes of an entity that have been described as follows :
(i) Composite vs. Simple (or atomic) attributes :The composite attributes can be divided into smaller sub-parts to represent some more basic attributes with independent meanings. The simple attributes cannot be further sub-divided. For example, Name of a person that is normally sub-divided into First Name, Middle Name and Last Name is a composite attribute. Height of a person is a simple attribute as it is devoid of further sub-division.
(ii) Single-valued vs. Multi-valued Attributes : An attribute with a single value for an entity is single-valued as opposed to those which multiple values. For example, height of a person is single-valued attribute while qualifications of that person are a multi-valued attribute.
(iii) Stored vs. Derived Attributes : Two or more attributes may be related in such a way that one or more becomes basic while the other becomes dependent on that basic attribute. For example, date of birth of a person is a stored attribute while age of that person is derived attribute.
(iv) 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 values
- When a particular attribute does not apply to an entity;
- Value of an attribute is unknown, although it exists;
- Unknown because it does not exist.
(v) Complex Attributes : The composite and multi-valued attributes may be nested (or 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
In the context of the example on accounting reality, the following attributes specific to each entity types have been stated below as :
|
Entity Type
|
List of Attributes
|
|
Accounts Type
Accounts
Employees
Vouchers
Support Documents
|
Catld, Category
Code, Name, Type
EmpId, Fname, Minit, Lname, SuperId
Vno, Date, Debit, Credit, Amount, Narration, AuthBy, PrepBy
Sno, dDate, Name
|
Accounts Type is a conceptual entity that is meant to express the various categories of accounts in accounting system. The CatId is an attribute of AccountType entity, the value of which is used to identify the category of accounts.
Accounts is a conceptual entity that is meant to express various accounts, each one of which belongs to a particular category of accounts in Accounts Type Entity. Every account is assigned a unique code by which it is identified. The Name attribute specifies the name of account and Type refers to the type of account (or category of account) as mentioned above.
Employees is a physical entity that is meant to express the various employees who are in some way connected with the accounting system. The EmpId (Employee ID) attribute is meant to identify an Employee; Fname, Minit and Lname are respectively the first, middle and Last names of an employee; and SuperId refers to EmpId of the immediate boss of an employee.
Vouchers is an entity that expresses various transactions vouchers. It is attributes together provide the structure of transaction data.
Support Documents is an entity, which expresses various support documents that may be attached with a particular voucher of a transaction. Sno attribute of this entity specifies the serial number of support document attached, dDate specifies the document date and Name specifies the name of document that is attached with the voucher.
(vi) Entity Types and Entity Sets : An Entity Typeis 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 database. The values of attributes of an entity belonging to entity type are known as Entity Instance. For example, (110001 Capital Account 4) is an entity instance of an account whose code = 110001, Name = Capital Account and Type = 4. An Entity Set is a collection of all entity instances of a particular entity type. An Entity Type is described by a set of attributes called “schema”. The set of entities pertaining to a particular entity type share the same set of attributes. The collection of entities of a particular entity type is grouped into entity set, called the extension of the entity type. For example,
Entity Type : Accounts
Intension (or structure) of entity type
Entity Set: Collection of entity instances of an entity type “Accounts”
Extension (or instances) of entity type
|
110001
221019
221020
|
Capital Account
Jain & Co.
Jayram Bros.
|
4
4
4
|
(vii) Value Sets of Attributes : Each simple attribute is associated with a value set, which specifies the set of possible values that may be assigned to a particular attribute. For example, the value set of voucher date is all those dates that fall within the dates valid for a given accounting period. Similarly, if accounting reality states that each code of an account is numeric with its width equal to six digits, its possible value set shall be 000001 to 999999. The value set as described above is called domain of values.
Identifier (or Key Attributes of an Entity Type)
- Almost every entity type has one of its attributes, which contains unique values for identifying the entity instance. For example, RollNo as attribute of Entity type students has unique values through which a student instance can be identified.
- Similarly, Code is a key attribute of entity type Accounts because its data values are required to be unique.
- Some times two or more such attribute together (called composite key) may constitute such distinct values. For example, the student entity type that has entity instances across several sections of a class in a school shall require a composite key of attributes (Sections and RollNo).
- But in any case, it is a constraint that does not allow any two-entity instances from having the same value for the key attribute at a point of time. Some entities may have more than one Key attribute. The entity types, which do not have a key attribute at all are called weak entities.
Relationships
Relationship among two or more entity types represents an interaction among their respective entities. Whenever an attribute (say Debit) of one entity type (say vouchers) refers to another entity type (say Accounts), there exists a relationship between these entities (Vouchers and Account).
For example, vouchers and accounts are related in two ways: vouchers contain debit account(s) and vouchers contain credit account(s). In ER Model, these references are represented as explicit relationships rather than attributes.
(i) Types of relationships : Whenever entities from different entity types are related to one another in a particular manner, they constitute a relationship type. The relationship prepared by between the two entity types vouchers and employees associates each voucher with the employee who prepared it. Similarly, the relationship authorized by between the two entity types vouchers and employees associates each voucher with the employee who authorises it. Each relationship instance of prepared by (short named as PrepBy) associates one voucher entity with one employee entity. In ER diagrams, relationship types are displayed as diamond shaped boxes, connected by straight lines to the rectangular boxes, which represent the participating entity types.
(ii) Degree : The degree of a relationship type is the number of participating entity types. A relationship type of degree two is called binary and that of degree three is called ternary. A VOUCHER (entity), Authorised_by (relationship) and EMPLOYEES (entity) together signify a binary relationship. A SUPPLIER (entity) SUPPLY (relationship) PARTS (entity) to PROJECT (entity) signify a ternary relationship because three entities, namely supplier, parts and projects are participating in supply relationship in any transaction.
(iii) Role Names : 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 of an entity type plays in each relationship instance. In PREPARED BY relationship type, EMPLOYEE plays the role of document creator and voucher plays the role of document created.
(iv) Structural Constraints : The reality may impose certain constraints (or restrictions) that may limit the possible combinations of entities, participating in a given relationship set. These are of two types : Cardinality Ratio and participation.
- Cardinality Ratios for binary relationship specifies the number of relationship instances that an entity can participate in. In PREP_BY binary relationship type, VOUCHER:EMPLOYEE is of cardinality ratio N:1 implying thereby that a set of vouchers can be created by a particular employee. The possible cardinality ratios are one to one (1:1),one to many(1:N),many to one(N:1), and many to many(N:M).
- Participation constraint specifies as to whether the existence of an entity type depends on its being related to another entity via a relationship type or not. The two types of such constraints are: total and partial. Whenever semantics of reality require that every entity of an entity type must relate to another entity type, such an entity can exist only if it participates in that specific relationship. Such a participation is called total participation.
For example, the participation of ACCOUNTS in CLASSIFY relationship is total participation. This is because every account must refer to at least one of the accounts type or a category of accounts. This participation is also called existence dependency. Since every employee is not expected to prepare at least one of the vouchers, the participation of employee in PREPARED BY relationship is partial, implying that some of employee entities are related to the voucher entity via PREPARED BY relationship. In ER diagram, total participation is displayed as double line connecting the participating entity type to the relationship, whereas partial participation is represented by a single line.
Weak Entity Types
- Entity Types, which do not have identifier (or 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 values.
- These other entity types are called identifying or owner entity type. Accordingly, the relationship type that relates a weak entity type to its owner is called identifying relationshipof the weak entity.
- A weak entity type always has a total participation constraint (existence dependency) with respect to its identifying relationship because it cannot be identified without its owner entity.
- For example, a voucher may be accompanied by a set of support documents such as bills, issued by other parties to the transaction, details of which need be stored. Such SUPPORT’ DOCUMENT entity type which is used to keep track of support documents attached to each voucher via 1:N relationship, is a weak entity.
- This is because they are identified as distinct entities only after determining the particular voucher. A weak entity type normally has a partial key, which is a set of attribute that can uniquely identify weak entities that are related to the same owner entity.
- Assuming that two support documents of a voucher do not have the same document Id, the said Id can be a good partial key. Otherwise a composite attribute of all the weak entity’s attributes will be the partial key.
Initial Conceptual Design for an Example Reality : Using a hypothetical example of an accounting system, as already stated above in Fig: 14.6, following initial design based on ER Model concepts becomes the starting point of illustration.
Conceptual Design : According to the requirements listed in example reality, there exist five entities: Vouchers, Accounts, Employees, Support Documents and Accounts Type·
- An entity type Vouchers with attributes Voucher No, Serial No, Voucher Date, Debit Account, Credit Account, Amount, Narration, authorized by, prepared by are used for storing accounting data of a transactions. Debit and amount are multi-valued attributes for debit vouchers and credit and amount are multi-valued for credit vouchers. Voucher No and Snotogether constitutes the only key attribute of entity type vouchers. Therefore, it is specified to be unique.
- A Conceptual entity type Accounts with attributes Code, Name and Type is used for keeping and maintaining a record of all accounts. Both Code and Name qualify to be the key attributes because of being specified as unique.
- An Entity Type Employee with attributes Employee ID (EmpId), Name, Address, Phone, ID of immediate boss (SuperId) is used to maintain records of employees in the organization. Name is a composite attribute with its simple attributes as: First Name (Fname), Middle Initial (Minit) and Last Name (Lname). The EmpId, specified to be unique, is the key attribute. SuperId indicates the EmpId of the controlling officer, the immediate boss.
- An entity type, Accounts Type with attributes CatId and Category is used to maintain records of various categories of accounts so that each of the accounts as stored in accounts entity are able to find their suitable place in financial accounting reports: profit and loss account and also the balance sheet.
An entity type called Support with attributes Sno. and Name is used to maintain records of all the support documents, which are annexed to the accounting voucher.
Database Technology
It refers to a set of techniques that are used to design a database. These techniques
use certain concepts, which are crucial to the creation of structure and development of the design.
These concepts are: Reality, data, database, information, DBMS and database system. A brief description of these concepts is given below:
(a) Reality : It implies some aspect of the real world. It consists of an organization, its different components and the environment in which the organization exists and operates. Any organization includes people, facilities and other resources that are organised to achieve certain goals. Each organization operates within an environment. While operating, the organization interacts, influences and gets influenced by the environment.
This continuous interaction results in real world transactions. These transactions are analysed with a view to identify the components called data items. A data item is the smallest named unit of data in an information system. In a transaction, the names of accounts (or their accounting codes), date of transaction, amount, etc. is all data items.
(b) Data : Data are known facts that can be recorded and which have implicit meaning. Data represent facts concerning people, places, objects, entities, events or even concepts. Data can be quantitative and qualitative or they can be financial and non-financial in character. Consider the following transaction :
April 01, 2014 Commenced business with Cash Rs. 5,00,000.
This transaction, before being recorded through a Transaction Voucher, as shown in need be split up into its data contents as “01”, 01-Apr-14, 642001, Bank Account, 110001,Capital Account, Rs.5,00,000. Data are not useful for decision-making unless they are processed to suit to the requirements of decision-making situation.
(c) Database : The data, after being collected, has to be stored so that different people can use them. This requires the creation of a database. A database is a shared collection of interrelated data tables, files or structures, which are designed to meet the varied informational needs of an organization (See Example database in figure. It has two important properties (or characteristics): one it is integrated and second it is shared.
Integrated property implies that distinct data tables have been logically organised. The purpose is to reduce or eliminate redundancy (or duplicity) and also to facilitate better data access. The shared property means that all those who are authorized to use data/information have access to relevant data.
Thus, a database is a collection of related data that represents some aspect of the real world (called mini-world or Reality). Accordingly, accounting database is a collection of related accounting data to represent some aspect of an accounting information system. Database is designed, built and populated (or loaded) with data for a specific purpose.
(d) Information : refers to data that have been processed and organised in a form, which is suitable for decision-making. The raw data when processed in accordance with decision usefulness of a decision-maker becomes information. In other words, information is a data that have been processed and refined and then presented in a format that is convenient for decision-making or other organisational activities.
However, information may be viewed as data at one level. But when it is processed keeping in view the requirements of decision situation, it becomes information at another level. For example, accounting data at transaction level is processed to produce balances of each account. The balances are summarised to prepare the trial balance. The amounts given in trial balance constitute data to produce profit and loss account and balance sheet.
(e) Database management System (DBMS) is a collection of programs that enables users to create and maintain a database. Formally, it may be defined as a general-purpose software system that facilitates the processes of defining, constructing and manipulating (or processing) databases for various applications. General-purpose software is defined as a set of programs, which are designed and developed for a community of users and not for any particular application with respect to a particular user.
An Illustration of Accounting Database
Consider an example of ACCOUNTING database for maintaining data pertaining to accounting transactions, support documents, accounts and employees with which the students of accounting are familiar. Figure shows below the database structure and some sample data for this database, depicting the following transactions :
|
Date
|
Transactions
|
Amount Rs.
|
|
2014
Apr. 01
Apr. 01
Apr. 02
Apr. 02
Apr. 03
|
Commenced business with cash
Cash deposited Into bank
Goods purchased and payment made by Cheque No. 765421
Rent for the month of April, 2014 paid by Cheque No. 765423
Goods purchased for cash from R.S & Sons
|
5,00,000
4,00,000
1,50,000
9,000
50,000
|
Relational Data Model
- The relational data model represents the database as collection of relations, which resembles a table of values (or data table). Each row of the table, therefore, represents a collection of related data values and hence typically corresponds to real world entity or relationship.
- The table name and column names are used to help in interpreting the meaning of values in each row. Each row of a table is called a data record. All values in a column, which belong to a particular domain, are of same data type
- Consider the following table of data items, named as Accounts. The table has rows and columns. The column arrow points to a column called Name. The Row arrow points to a data record consisting of (110001, Capital Account and 4) each of which corresponds to Code, Name and Type, which are three different columns of the table.
Name of Table : Accounts
|
Code
|
Name
|
Type
|
|
110001
221019
221020
411001
|
Capital Account
Jain & Co.
Jayram Bros.
Furniture Account
|
4
4
4
3
|
Formally, a row is called a tuple, a column header is called an attribute and the table as such is called a relation. The data type describing the types of values (such as text value, numeric values, date values, currency value, etc.) that can appear in each column is called a domain. A domain is a set of indivisible values. Associated with every domain is a data type such as Number,
Text, Currency, Date/Time, etc. Each domain must also be named so as to help in interpreting its values. Besides this, a domain must be given a format and any additional information to enable correct interpretation of values. For example, a numeric domain such as distance should have units of measurement: Miles or Kilometers
(a) Relations : A relation schema is made up of a relation name and a list of its attributes. Each attribute is the name of role played by some domain in the relation schema. A relation is given an identity by its name and description by its schema. The degree of a relation is indicated by the number of attributes it contains. For example, the degree of a relation schema accounts is three as shown below :
ACCOUNTS (Code, Name, Type) ← Relation with attributes
ACCOUNTS is name of the relation which has three attributes;
Code = Identity of Account;
Name = Names of Account;
Type = Category of Account
A Relation represents an entity type. A relation (or relation state) is a set of tuples wherein each tuple is an ordered list of values corresponding to attributes of relation. Each of these values must belong to the domains of their respective attributes. Each tuple in this relation represents a particular entity. A relation schema may be interpreted as a declaration in the nature of an assertion. For example, the schema of accounts relation, as shown above, asserts that every account has a Code, Name and a Type. As a result, each tuple in accounts relation can be interpreted as a fact or an instance of assertion. Some relations represent facts about entities while others might represent facts about relationships.
(b) Values in Tuples : Each value in a tuple is an indivisible value to imply that it is not divisible into components within the framework of the basic relational model. This implies that composite and multi-valued attributes are not allowed. Composite attributes are represented by their simple components. The multi-valued attributes are represented by separate relations. A special value called Null is used to represent unknown or not applicable values of attributes in a tuple. It is also possible to devise different types of code values for different types of null value situation.
Relational Databases and Schemas
A relational database schema is a set of relation schemas and a set of integrity constraints. A relational database state is a set of relation states such that every relational database state satisfies the integrity constraints specified on relational database schema.
In this context the following points merit a special consideration :
(a) A particular attribute, which stands for the same real word concept, might appear in more than one relation with same or different name. For example, in vouchers relation, the account Number is represented as debit and credit whereas in accounts relation, it is represented as Code EmpId appearing in Employees relation is represented in Vouchers as Auth.By and Prep.By.
(b) The particular real world concept appearing more than once in a relation must be represented by different names. For example, in employees relation, employee is represented as subordinate, by using EmpId and as superior by using SuperId.
(c) The Integrity constraints, specified on database schema, must hold in every database state of that schema.
Constraints and Database Schemas
There are four different constraints, which can be specified on relational databases. These are: domain constraint; key constraint; entity integrity constraint; referential integrity constraints.
(a) Domain : The value of each attribute of a relation must be an indivisible value and drawn out of possible values associated with its domain. The value of an attribute, therefore, must conform to the data type associated with the domain.
(b) Key Constraints and NULL Values : Each data record, which corresponds to a tuple of a relation, in a table must be distinct. That means no two tuples (or rows) in a relation ( or table) 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 all 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 by default. Any such super-key, therefore, specifies uniqueness constraint. Such a combination, representing super-key, may have redundant attributes, implying thereby that a more useful concept is that of a key which has not redundancy.
(c) Entity integrity constraint : States that no primary key value can be null because it is used to identify individual tuple in a relation. Null value implies that we cannot identify such tuples or identify these as alike. A failure to distinguish them means they are duplicates.
(d) Referential integrity constraint : While key and entity constraints are specified on individual relation, the referential integrity constraint is specified between two or more relations. This constraint is specified to maintain consistency among the tuples of such relations. Accordingly, a tuple in one relation that refers to another relation must refer to an existing tuple in that other relation. In referencing Accounts Type, Accounts relation uses its attribute Type, which acts as foreign key to reference the tuples of relation Accounts Type through its primary key CatId.
First it references, Accounts (Code, Name, Type). In referencing Accounts, the Vouchers relation uses its attributes Debit and Credit, which act as Foreign Keys to reference the tuples of relation Accounts through its primary key, Code. The values of debit and credit cannot be null because of total participation of vouchers in debit and credit relationship.
Second, it references Employees (EmpId, Fname, Minit, Lname, Address, PhoneNo, SuperId). 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 EmpId. The values of PrepBy and AuthBy cannot be null because of total participation of vouchers in PrepBy and Authby relationships.
The referential integrity constraint stands violated in above example, if there is a debit or credit code in voucher relation, the tuple for which does not exist in Accounts relation. Similarly, referential integrity fails, if there exists a value corresponding to Auth.By or Prep.By attribute of vouchers, the tuple for which does not exist in employees relation.
Operations and Constraint Violations
There are two categories of operations on relational model : updates and retrieval The three basic types of updates are as given below :
(a) Insert : This operation is performed to add a new tuple in a relation. For example, an attempt to add 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 any of the four constraints discussed above.
(b) Delete : This operation is carried out to remove a tuple from a relation. A particular data record from a table can be removed by performing such a operation. The delete operation can violate only referential integrity, if tuple being removed is referenced by foreign key from other tuples in the database.
(c) Modify : The operation aims at causing a change in the values of some attributes in existing tuples. This is useful in modifying existing values of an accounting record in a data table. Usually, this operation does not cause problems provided the modification is directed on neither primary key nor foreign key.
Whenever applied, these operations must enforce integrity constraints specified on relational database schema.
Retrieval operation on Relational Data Model does not cause violation any integrity constraints.
Designing Relational Database Schema
The rules or 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 as shown in design the following specific steps are required to cause its transformation into relational data model :
(i) Create a relation for every strong entity : For each strong entity type (which has primary key) in ER schema, a separate relation that includes all the simple attributes of that entity is created. Either choose one of the key attributes of such an entity as the primary key for this relation, or choose a set of simple attributes that uniquely identify this entity as the primary key of the relation so created. For example, employee entity is strong because it finds its primary key in EmpId which is one of its unique attribute. Therefore, a separate relation for Employee has been created as shown below :
Employee (EmpId, Fname, Minit,Lname,Address, PhoneNo, SuperId)
Similarly, separate relations need be created for the following strong entities whose Primary Key attribute have been underlined.
Accounts (Code, Name, Type)
Vouchers (VNo,vDate, amount, narration)
Accounts Type (CatId, Category)
(ii) Create a separate relation for each weak entity type : Every weak entity has an owner entity and an identifying relationship through which such weak entity type is identified. For every weak entity type, a separate relation is created by including its attributes. The primary key of this new 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 relation. Furthermore, the primary key of owner entity is included as foreign key in such a relation key of owner entity and the partial key of weak entity.
For example, Support 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 Vouchers, Vno along with Sno is designed as composite key for support entity and the relation so formed is shown below as :
Support (vNo,Sno, dName,sDate)
(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 relation should be included in first relation as its foreign key. For Example, An employee can authorize a number of vouchers. It implies that Vouchers entity participates in Auth.By relationship on n-side while Employees entity participates in same relationship on 1-side.
(iv) Identify entity types 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 represent the participating entity types. For example, consider the following entities and relationships in the context of credit voucher shown in, which has one debit with multiple credit accounts :
In this case, relationship Credit has cardinality ratio of M:N between Vouchers and Accounts(many vouchers are related to many accounts), While relationship Debit has cardinality ratio of N:1 (many vouchers refer to one account). Further Credit relationship has Sno, amount and narration has its attributes. Accordingly, we create a new relation as follows :
Credit (vNo, Sno, Code, Amount, Narration)
In above relation credit Code is included as foreign key to represent primary key of accounts relation, Vno is included as foreign key to represent primary key of relation vouchers. (Vno,Code) constitute the primary key of this new relation credit. By analogy, we can arrive at the following relation for Debit voucher:
Debit (vNo, Sno, Code, Amount, Narration)
Finally, the following relations have been formed to constitute the relational data model for our example reality.
Employee (EmpId, Fname, Minit,Lname,Address, PhoneNo, SuperId)
Accounts (Code, Name, Type)
Vouchers (VNo,Vdate, debit, credit, amount, narration, AuthBy, PrepBy)
Accounts Type (CatType, Category)
Support (VNo,Sno,Dname,Sdate)
Vouchers (vNo,vDate, Credit, Auth.By, Prep.By)
Debit (vNo, Sno, Code, Amount, Narration)
Situation B : The schema given below is compatible with Credit voucher Vouchers (vNo,vDate, debit, AuthBy, PrepBy)
Credit (vNo, Sno, Code, Amount, Narration)
A generalised Schema for the two schemas shall be
Vouchers (vNo,vDate,Vtype, AccCode, vType, AuthBy, PrepBy)
Details ( vno, Sno, Code, Amount, Narration)
Where in another attribute vType has been introduced to indicate whether this generalised schema applies to Situation A(vType=0) or Situation B(vType=1). Debit and Credit attribute of vouchers relation have been renamed as AccCode to mean Debit and Credit, depending on the value of Vtype. Debit and Credit relations have been generalised into Details because both shared a set of common attributes.
Model-I : This is based on initial conceptual design
Model-II : The set relations given below are based on modified example reality that uses Credit and Debit vouchers.
Illustration No 1
Mr. Philips commenced business with cash and for that purpose opened a bank account on April 1, 2014. His transactions for the month are as given below :
|
Date
|
Transactions
|
Amount Rs.
|
|
2014
Apr. 01
Apr. 01
Apr. 02
Apr. 02
Apr. 03
Apr. 04
Apr. 05
Apr. 06
Apr. 08
Apr. 09
Apr. 10
Apr. 12
Apr. 15
Apr. 17
Apr. 18
Apr. 18
Apr. 19
Apr. 21
Apr. 23
Apr. 25
Apr. 27
Apr. 28
Apr. 29
Apr. 30
Apr. 30
Apr. 30
|
Commenced business with cash
Cash deposited Into bank
Goods purchased and payment made by Cheque No. 765421
Cheque No. 765422 issued to M/s Nahar Transports for carriage
Rent for the month April, 2014 paid by Cheque No. 765423
Goods purchased for cash from M/s R.S. & Sons
Paid for carriage to M/s Saini Transports
Goods sold to Kemp & Co.
Goods purchased from M/s Jayram Bros.
Sold goods for cash to M/s Kumbley & Co.
Paid for adverisement by Cheque No. 765424
to M/s ABN Cables
Received a bill of exchange from Kemp & Co.payable
after 3 months
Bill of exchange received from Kemp & Co. discounted for
Goods returned to Jayram Bros. being defective
Advance cash payment to salesman for marketing tour
Paid for insurance of godown Cheque No. 765425
Paid for fuel, power and electricity
Salary paid in advance to bimal
Accepted a bill of exchange payable after four months
in favour of Jay Ram Bros.
Returns from M/s Kumbley & Co., settled by
Cheque No. 765427
Cash withdrawn by proprietor for household expenses
Advance to salesman adjusted for cash after recording
expenses :
Entertainment
Travelling
Boarding and Lodging
Goods taken from stock for personal use
Furniture purchase from M/s S.N. Furnitures
by Cheque No. 765428
A part of existing stock set a side for usage as
office furniture
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
Payment of telephone bill by Cheque No. 765433
Paid for wages by cash
|
5,00,000
4,00,000
1,50,000
3,000
9,000
50,000
2,000
1,75,000
2,50,000
45,000
2,500
1,75,000
1,71,500
15,000
10,000
5,500
1,000
10,000
2,35,000
5,000
20,000
4,500
2,200
3,500
5,000
45,000
35,000
9,000
5,500
6,000
5,000
1,500
7,000
|
The database state pertaining to Accounts and Employees table is as given below :
Accounts
|
Code
|
Name
|
Type
|
|
110001
221019
221020
222001
411001
411002
412002
621001
621002
631001
632001
641001
641002
642001
651001
711001
711002
711003
711004
711011
712001
712002
712003
712004
712005
712006
712007
712008
712009
712010
811001
811002
|
Capital Account
Jain & Co.
Jayram Bros.
Bill Payables
Furniture Account
Office Fittings
Plant and Machinery Account
Kemp & Co. 3
Kumble & Sons
Cash account
Bank account
Salary in advance account
Advance to salesman
Bills receivable
Drawings
Purchases
Purchases returns
Carriage inwards
Fuel, power and electricity
Wages
General expenses
Rent account
Salaries account
Discount account
Advertisement
Entertainment
Travelling
Boarding and Lodging
Communication expenses
Insurance
Sales account
Sales returns
|
4
4
4
4
3
3
3
1
3
3
3
3
3
4
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
|
Account Type
|
Cat Id
|
Category
|
|
1
2
3
4
|
Expenditure
Income
Assets
Liabilities
|
Employees
|
Emp Id
|
Fname
|
Minit
|
LName
|
Address
|
Phone No
|
SuperId
|
|
A001
B001
S001
S002
|
Aditya
Bimal
Smith
Sunil
|
K
S
K
K
|
Bharti
Jalan
John
Sinha
|
|
|
A001
A001
B001
|
Solution
The solution based on Model-I which lends support to Transaction Voucher with one Debit and one Credit , shall appear as follows :
Vouchers
|
vNo
|
Debit
|
amount Rs.
|
vDate
|
Credit
|
narration
|
AuthBy
|
PrepBy
|
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
631001
632001
711001
711003
712002
711001
711003
621001
711001
631001
712005
642001
711002
712004
641002
712010
711004
641001
221020
811002
651001
712006
|
5,00,000
4,00,000
1,50,000
3,000
9,000
50,000
2,000
1,75,000
2,50,000
45,000
2,500
1,75,000
15,000
3,500
10,000
5,500
1,000
10,000
2,35,000
5,000
20,000
4,500
|
2014
Apr. 01
Apr. 01
Apr. 02
Apr. 02
Apr. 02
Apr. 03
Apr. 03
Apr. 04
Apr. 05
Apr. 06
Apr. 08
Apr. 09
Apr. 10
Apr. 12
Apr. 12
Apr. 17
Apr. 18
Apr. 18
Apr. 19
Apr. 21
Apr. 23
Apr. 25
|
110001
631001
632001
632001
632001
631001
631001
811001
221020
811001
632001
621001
221020
642001
631001
632001
631001
631001
222001
632001
631001
641002
|
Commenced business
with cash
Deposited into bank
Purchases from
R.S & Sons
Paid to M/s
Nahar Transport
Paid rent for April, 2014
Goods purchased from
R.S & Sons
Paid for carriage to
M/s Saini Transports
Goods sold
Invoice no. dated:
Goods sold to M/s
Kumbley & Co.
Paid to M/s. ABN Cables
Maturity Date:
July 12,2014
Goods returned
Note No. dated:
Discount on Bill of
exchange from Kemp
Advance payment to sales
for marketing tour
Insurance of godown
Payment for fuel, power
and electricity
Salary paid in advance to
Bimal
Sattlement by accepting
a bill of exchange
Goods returned by M/s
Kumbley & Co.
Withdrawal by proprietor
for household expenses
Expenses during tour:
Support vouchers 1-4
|
A001
A001
A001
A001
A001
A001
A001
A001
A001
B001
S001
A001
A001
A001
A001
B001
S001
S001
B001
B001
A001
A001
A001
|
B001
S001
B001
B001
B001
S001
S001
S001
S002
S002
S002
S002
S002
S002
S002
S001
B001
B001
B001
S001
S001
S001
S001
|
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 30, 2014 “Salary for the month paid by cheque” requires four vouchers 29 to 32. One transaction should be recorded possibly through one voucher only.
Solution
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 in
Vouchers
|
Vno
|
Vdate
|
Acc_code
|
Vtype
|
PrepBy
|
AuthBy
|
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
2005
Apr. 01
Apr. 01
Apr. 02
Apr. 02
Apr. 03
Apr. 04
Apr. 05
Apr. 06
Apr. 08
Apr. 09
Apr. 10
Apr. 10
Apr. 12
Apr. 12
Apr. 17
Apr. 18
Apr. 18
Apr. 19
Apr. 21
Apr. 23
Apr. 25
Apr. 25
Apr. 27
Apr. 28
Apr. 29
Apr. 30
Apr. 30
Apr. 30
|
631001
632001
632001
632001
631001
811001
221020
631001
632001
621001
632001
221020
642001
631001
632001
631001
631001
222001
632001
631001
641002
631001
711001
632001
711001
632001
632001
631001
|
1
1
0
0
0
0
0
1
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
|
B001
S001
B001
B001
S001
S002
S002
S002
S002
S002
S002
S002
S002
S002
S001
B001
B001
B001
S001
S001
S001
S001
S001
S002
S002
S001
B001
S001
|
A001
A001
A001
A001
A001
A001
B001
S001
A001
A001
A001
A001
A001
B001
S001
S001
B001
B001
A001
A001
A001
A001
A001
A001
A001
A001
A001
A001
|
Details
|
Vno
|
Sno
|
Code
|
Amount
|
Narration
|
|
01
02
03
03
04
05
05
06
07
08
09
10
12
13
14
15
16
17
18
19
20
21
21
21
22
23
24
25
26
26
26
|
1
1
1
2
1
1
2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
3
1
1
1
1
1
2
3
|
110001
631001
711001
711003
712002
711001
711003
621001
711001
811001
712005
642001
711002
712004
641002
712010
711004
641001
221020
811002
651001
712006
712007
712008
641002
651001
411001
411001
712001
712001
712001
|
5,00,000
4,00,000
1,50,000
3,000
9,000
50,000
2,000
1,75,000
2,50,000
45,000
2,500
1,75,000
15,000
3,500
10,000
5,500
1,000
10,000
2,35,000
5,000
20,000
4,500
2,200
3,500
200
5,000
45,000
35,000
9,000
5,500
6,000
|
Commenced business with cash
Deposited into bank
Purchases from R.S & Sons
Paid to M/s Nahar Transports
Paid rent for April, 2014
Goods purchased from R.S & Sons
Paid for carriage to M/s Saini Transports
Goods sold
Invoice No. dated:
Goods sold to M/s Kumbley & Co.
Paid to M/s ABN cables
Maturity date July 12, 2014
Goods returned Note No. dated
Discount on bill of exchange from Kemp & Co.
Advance payment to sales for marketing tour
Insurance of godown
Payment for fuel, power and electricity
Salary paid in advance to Bimal
Settlement by accepting a bill of exchange
Goods Returned by M/s Kumbley & Co.
Withdrawal by proprietor for household expenses
Expenses during tour: Support Vouchers 1-4
Expenses during tour: Support Vouchers 5-7
Expenses during tour: Support Vouchers 8-11
Final settlement of Refer to J.V no. 04/21
Goods taken for private use
Furniture purchased from S.N. Furniture
Goods purchased for trading put to office use
Salary to Aditya Apr. 2014
Salary to Bimal Apr. 2014 after adjustment
Salary to Smith Apr. 2014
|
Interacting with Databases
- One of the major reasons for the success of commercial databases is the SQL language support they enjoy. This is because SQL became standard for relational databases.
- As a result, users have become less concerned about migratingtheir database applications from one database to another database. Another advantage in using standard SQL is that users may write statements in a database application program that can access data stored in two or more relational DBMS without having to change the database sub-language (SQL) provided both the DBMS enjoy the support of a particular SQL standard.
- The name SQL stands for Structured Query Language, which was originally called SEQUEL (Structured English QUEry Language), designed and implemented at IBM Research as an interface for experimental relational database system called SYSTEM-R.
- Being a comprehensive database language, it has statements for data definition, query and update. Besides this, it has the capability to define user-oriented views of database, specify security and authorisation, define integrity constraints and various other operations.
- Many computer-programming languages can act as good host languages to incorporate the statements of SQL. In this sense, it can be used as a sub-language in a database-programming context.
Basic Queries in SQL : Data Query Language (DQL), which is a sub-set of SQL is widely used to answer most of the basic queries. The basic set of queries consists of those, for which the SELECT-FROM-WHERE Structure is put to use as described below :
- SELECT : This clause is used to specify the data or information that is desired to answer the query.
- FROM: This clause is used to specify the source of data for answering the query. It can be a data table, an existing query or both.
- WHERE: This clause is meant to specify the conditions that are used to narrow down the choice of data to extract the information desired in select clause.
The following queries have been considered using the database design given in Model-I and Model-II. The solution to queries has been given using MS ACCESS implementation.
- Query to retrieve all columns of data records from a table, subject to a condition :To project all the attribute values of selected tuples, an asterisk (*) need be specified. This asterisk stands for all the attributes.
(1) To retrieve all columns of voucher records whose voucher has been authorized by an employee whose EmpId is equal to “A001”.
Solution
(Model-I and Model-II)
SELECT *
FROM vouchers
WHERE AuthBy=”A001”;
- Query to retrieve selected columns of data records from a table, subject to a condition.
(2) To Retrieve vouchers with Vno, Vdate, AuthBy columns wherein the vouchers are dated “12/Apr/2014”
Solution
(Model-I and Model-II)
SELECT Vno, Vdate, AuthBy
FROM vouchers
WHERE Vdate = #04/12/2014#;
(3) To retrieve vouchers with Vno, Vdate, Auth_by columns, which are dated
“12/Apr/2014”. The columns of records retrieved by the query are to be renamed as Voucher, Date and Employee
Solution
(Model-I and Model-II)
SELECT Vno As Voucher, Vdate As Date, Prep_by As Employee
FROM vouchers
WHERE Vdate = # 04/12/2014#;
III. Unspecified WHERE Clause : Absence of WHERE clause in SELECT statement implies that the tuples from a relation are to be selected without applying any condition. This in turn means that all tuples of a relation specified in FROM clause qualify for being selected for the result of query. Consider the following query with reference to Model-I.
(4) Find out the list of accounts which have been debited Solution
(Model-I)
SELECT DISTINCT Debit As Code
FROM vouchers;
Solution
(Model-II)
SELECT AccCode As Code
FROM vouchers
WHERE vType = 0;
UNION
SELECT Details.Code
FROM vouchers, Details
WHERE vType = 1 AND vouchers.vNo = Details.vNo;
Save above query as DebitAccounts, and thereafter execute another query as given below to get the final results.
SELECT DISINCT *
FROM Debit Accounts ;
(5) Find out the list of accounts which have been credited
Solution
(Model-I)
SELECT DISTINCT Credit As Code
FROM vouchers ;
Solution
(Model-II)
SELECT AccCode As Code
FROM vouchers
WHERE Vtype = 1;
UNION
SELECT Details.Code
FROM vouchers, Details
WHERE vType = 0 AND vouchers.vNo = Details.vNo;
Save above query as CreditAccounts, and thereafter execute another query as given below to get the final results.
SELECT DISINCT *
FROM CreditAccounts;
(6) Find out the list of accounts which have been debited as well as credited
Solution
(Model-I)
SELECT DISTINCT Debit As Code
FROM vouchers
WHERE Debit IN (SELECT Credit As Code
FROM vouchers);
Solution
(Model-II)
SELECT *
FROM DebitAccounts
WHERE Code IN (SELECT *
FROM CreditAccounts);
Save above solution query as DebitCredit, both for Model-I and
Model-II
(7) Find out the list of accounts which have been debited but not credited
Solution
(Model-I)
SELECT DISTINCT Debit As Code
FROM vouchers
WHERE Debit NOT IN (SELECT Code
FROM DebitCredit);
Solution
(Model-II)
SELECT *
FROM DebitAccounts
WHERE Code NOT IN (SELECT *
FROM DebitCredit)
(8) Find out the list of accounts which have been credited but not debited
Solution
(Model-I)
SELECT DISTINCT Credit As Code
FROM vouchers
WHERE Credit NOT IN (SELECT Code
FROM DebitCredit);
Solution
(Model-II)
SELECT *
FROM CreditAccounts
WHERE Code NOT IN ( SELECT *
FROM DebitCredit)
- Ambiguous Attribute Names and Renaming(Aliasing) :SQL allows the use of homonyms (that is same name for two or more attributes) as long as such attributes are in different relations. If the use of a common attribute with a particular name across the relations prevails, it becomes necessary to qualify the attribute name with relation name in which it exits. This is achieved by prefixing the relation name to the attribute name and separating the two by a period symbol dot. In Model-II, the attribute Vno, referring to voucher number in vouchers relation, also exists in details relation. Whenever vouchers and details relations are used in a query, the use of Vno attribute must precede the name of relation or its alias name. For example,
(9) Retrieve a list of accounts and the amounts debited because of cash payments. The Cash Account code begins with “631”.
Solution
(Model-I)
SELECT Narration, Debit As Code, Amount
FROM Vouchers
WHERE Credit LIKE “631*”;
Solution
(Model-II)
SELECT Narration,Acc_code AS Code, Amount
FROM Vouchers AS V, Details AS D
WHERE tType=1 AND V.vNo=D.vNo
AND acc_code like “631*”
UNION
SELECT Narration,Code, Amount
FROM Vouchers AS V, Details AS D
WHERE tType = 0 AND V.vNo = D.vNo
AND code LIKE “631*”;
(10) To retrieve a detailed list of all accounts, giving their code, Name and category.
Solution
(Model-I and Model-II)
SELECT Code, Name, Category
FROM Accounts, AccountType
WHERE CatId = Type
(11) To retrieve a detailed list of all account, giving their code, Name and category, which have been debited
Solution
(Model-I)
SELECT DISTINCT Debit AS Code, Name, Category
FROM Vouchers AS V,Accounts AS A, AccountType
WHERE V.Debit = A.Code AND CatId = type
Solution
(Model-II by using query solution saved as DebitAccounts in Q.No: 4)
SELECT Code, Name, Category
FROM DebitAccounts AS D, Accounts AS A, Category
WHERE D.Code = A.Code AND Type = CatId
(12) To retrieve Code, Name and Category of Expense accounts which have been debited
Solution
(Model-I)
SELECT Debit AS Code, Name, Category
FROM Vouchers, Accounts, AccountType
WHERE Debit = Code AND Type = CatId
AND Category = “Expenses”
Solution
(Model-II by using query solution saved as Debit Accounts in Q.No: 4)
SELECT D.Code, Name, Category
FROM DebitAccounts AS D, Accounts AS A, AccountType
WHERE D.Code = A.code AND Type = CatId
AND Category = “Expenses”
(13) To retrieve Narration and Amount of transactions where Expense head “Carriage Inwards” has been debited.
Solution
(Model-I)
SELECT Narration, Amount
FROM Vouchers, Accounts
WHERE Debit = Code
AND Name LIKE “Carriage Inw*”;
Solution
(Model-II by using query solution saved as DebitAccounts in Q.No: 4)
SELECT Narration, Amount
FROM Details AS T,DebitAccounts AS D, Accounts AS A
WHERE T.Code = D.Code AND D.Code = A.Code
AND Name LIKE “Carriage Inw*”
- Sub-string Comparisons and Arithmetic Operators and Ordering and use of functions :SQL allows comparison on sub-strings (that are some parts of a character string). This can be achieved by use of LIKE Operator. This like operator instead of equal to (=) operator can be used when exact value of comparison is not known. Partial strings or sub-strings are specified by using * and range specification within rectangular brackets. For Example:
(14) To make a list of accounts pertaining to the assets of the company, given that each of the assets account code begins with “4”, following query need be executed:
Solution
(Model-I and Model-II)
SELECT Code, Name
FROM accounts
WHERE Code like “4*”
(15) To make a list of employees whose names start from a to k, following query need be executed :
Solution
(Model-I and Model-II)
SELECT Fname & “ “ & Minit & “ “ & Lname As ‘Name of Employee’
FROM Employees
WHERE Fname like “[a-e]*”
- Another comparison operator used in SQL isBETWEEN ....AND ....operator. This operator facilitates numeric range tests for selection of tuples. For Example:
(16) To retrieve vouchers with amount ranging between 5,000 and 10,000, following query need be formulated.
Solution
(Model-I)
SELECT Vno, Amount
FROM Vouchers
WHERE Amount BETWEEN 5000 AND 10000 ;
Solution
(Model-II)
SELECT Vno, Amount
FROM Vouchers AS V, Details AS D
WHERE. V.vno = D.vno AND Amount BETWEEN5,000 AND 10,000;
VII. Another feature of SQL permits the use of standard arithmetic operators, which can be directly applied to numeric values appearing in a query statement. Consider the following query:
(17) To find various amounts of sales during the month of April, 2014 and the amounts of such sales if the prices of products are allowed to be raised by 16%.
Solution
(Model-I)
SELECT Vdate, Credit, Amount, Amount*1.16 ASExpected
FROM Vouchers, Accounts
WHERE Credit = Code AND name LIKE “Sales Account*”
Solution
(Model-II)
SELECT Vdate, D.code, Amount, Amount*1.16 AS Expected
FROM Vouchers AS V, Details AS D, accounts ASA
WHERE V.vNo = D.vNo AND D.code = A.Code AND A.Name LIKE “Sales Account*” AND tType = 1
UNION
SELECT Vdate, V.Acc_code, Amount, Amount*1.16 AS Expected
FROM Vouchers AS V, Details AS D, accounts ASA
WHERE V.vno = D.vno AND V.acc_code = A.code AND A.name LIKE “Sales Account*” AND Ttype = 0;
VIII. SQL also allows ordering of resultant tuples according to some specified attribute, which may or may not form part of the resultant relation. Consider the following example:
(18) To retrieve list of Accounts in dictionary order of their Names :
Solution
(Model-I and Model-II)
SELECT *
FROM Accounts
ORDER BY Name
- SQL queries allow the use of supported functions within the query itself. List of these functions varies from one implementation to another depending on the specific RDBMS. Consider the following example :
(19) To List details of vouchers released during April, 2014.
Solution
(Model-I and Model-II)
SELECT *
FROM vouchers
WHERE Month(vDate) = 4
To execute above query, month() function is used which accepts within parenthesis the data a parameter and returns the numeric value of one month varying from 1 through
- In this case the relevant value to be compared for the month of April is 4.
- Explicit Sets and NULL in SQL :Query results can be retrieved even for rows in which value of an attribute is missing. This is achieved by using NULLin Where clause while specifying the condition. If more than one value is to be compared with an attribute, the value set can be given in Where clause by specifying IN operator.
(20) To retrieve Details of Accounts with following Codes: relating to “621001”, “632021” and “642002”.
Solution
(Model-I and Model-II)
SELECT *
FROM Accounts
WHERE Code IN(“621001”,”632001”,”642002”);
(21) To retrieve name of all employees who do not have supervisors.
Solution
(Model-I and Model-II)
SELECT *
FROM Employees
WHERE SuperId = NULL;
- Aggregate Functions and Grouping :The concept of aggregate functions as referred to in relational operations, is implemented by SQL. Five such functions commonly used for aggregate of data items are: COUNT,SUM, MAX, MINand AVG. These functions when applied on a set of numeric values, return respectively number of rows, the sum , maximum, minimum and average of these values. The GROUP BYclause is used for providing the basis of creating collection of data items on which these functions are to be applied. Consider the following examples.
(22) To find the sum, minimum and maximum of cash payment during April, 2014. The cash account code begins with “631”
Solution
(Model-I)
SELECT Debit AS Code, SUM(Amount) AS Total,
MIN(Amount) As Minimum, MAX(Amount) As Maximum
FROM Vouchers
WHERE Debit like “631*”
GROUP BY Debit
Solution
(Model-II)
SELECT Code, SUM(Amount) AS Total,
MIN(Amount) As Minimum, MAX(Amount) As Maximum
FROM Vouchers AS V, Details AS D
WHERE V.Vno=D.Vno, Ttype=0 and Code Like “631*”
GROUP BY D.Code