1

6 Database management System for Accounting

Database

A database is a systematic collection of data. It is a collection of data that is organized so

that its contents can easily be accessed, managed and updated.

For example, Kerala University database might contain information about entities such
as students, teachers, courses; colleges etc.Phone book, telephone book etc are also
example for database.

We want to manage these data base for storing, modifying and retrieving information.
For this firms use Database Management System (DBMS) to manage Database.

Database Management System (DBMS)

Database Management System (DBMS) is software that enables users to create and
maintain a database. DBMS helps in collection of data, their storing, manipulation,
retrieval etc. MS Access, Oracle, LibreOffice Base, SQL Server etc examples of DBMS
software’s.

Components of DBMS
Database Management system consists of Data, hardware, software and users.

1. Data: Data means facts. It is the raw material for information i.e processed data
are called information.

2. Hardware: Hardware consists of input/output devices, memory, processors etc.

2

3. Software: It operates the hardware and interacts with users. With the help of
hardware we store data into hard disk, update it, edit it and retrieve it.

4. Users: Users are the persons who seek the information from the database.

LibreOffice Base
It is one of the popularly used Database Management System (DBMS) to create, store
and manage database. The default extension of LibreOffice Base files is.odb.

Terms in DBMS/LibreOffice Base

 Data: Data means facts. It is the raw materials for information. It may be a
number,name,mark etc.

 Table: Tables are used to store data in a database. It is the combination of Field
and Record (columns and rows). A column of the table in Access is called field.
Row of a table in Access is called record. A table is a collection of data about a
specific topic.Table helps in organizing data in a structured manner on the basis of
data types.
 Field: A column (its value) in LibreOffice Base Table is called Field. Individual
data with similar features is put in a single field (Admission numbers of
students, names of students etc). It is a small piece of information. Each
column represents data having same characteristics. E.g. In an employee
table, there may be different fields like Employee ID,EmployeeName,
Designation etc.
3

 Record/Tuple: A row (its value) in LibreOffice Base is called Record. A record

contains all information about one person or item. Eg.(In the above Student Table)
Full information about a student-Si No-1,admission no-5678,Lalu,M,14-11-
2004,Kalayanthani,9,5678905623 etc.

 Entity: Anything in the real world with independent existence is called an entity.
Entity may be a thing like car, person (in our example Student),a concept like
account, place, unit, object or any item about which the data should be captured
and stored in the form of properties like tables. Entity is the key element in all
relational databases.

 Weak Entity: A weak entity is one that can only exist when owned by another one.
For example: The scope for a Room exists only if there is a Building. On the other
hand, a TYRE might be considered as a strong entity because it also can exist
without being attached to a CAR. Another example is bank account of a particular
bank has no existence if the bank doesn't exist anymore.

 Attributes:-These are additional characteristics that further describe an enity.Eg.

Height,Weight,Date of birth etc of a person. Another example emp id,emp
name,emp sex etc.The columns or fields of a relation(Table) are called attributes.

 Primary key: A primary key is a special column of a table designated to uniquely

identify all table records (Items in the row). Main features of Primary Key are:
a) It must contain a unique value for each row of data.
b) It cannot contain null values.
c) If we assign a column as primary key, items in it cannot be repeated.

In order to manage the data, we distribute the data in many tables.The main
table is known as master (Parent) table and the lower level table in which
related data is stored is the transaction (Child) table.The master table and
4

child table is related and must be linked based on a field which is

common.This common key field in the master table is known as Primary key
and the linked field in the child table is known as foreign key.
Steps to set primary key
To select a particular field( For eg EMP ID)as primary key right click on the
row sector of that field(EMP ID)and select the “Primary Key” from the drop
down menu. A key symbol will appear next to selected field.

 Foreign key: In relational databases, a foreign key is a field in one table that
uniquely identifies a row of other table .In simpler words, the foreign key is defined
in a second table, but it refers to the primary key or a unique key in the first table. It
defining the relationship between two tables.

 Query: A Query is an enquiry sent to a database to get some data, to calculate

result or to bring together data from different Tables. Queries help to retrieve data
on the basis of certain criteria. It is used to manipulate database content. There are
three methods for creating queries in LibreOffice:

Data Types:
Data type define the particular characteristic of data that we intend to store in
table.LibreOffice Base supports different data types, Some useful data types are :

1. Text [VARCHAR]: It is used to store words or numbers that are not to be used in
any arithmetic calculations.

There are different text data types such as

5

2. Memo [LONGVARCHAR]: A Memo field can be used for storing large amounts
alphanumeric information. Some typical uses for this data type would be a note,
comments, description, or address field.

 Number: It is meant to store numbers.

 Date: Used to store date into Database table.
 Time: It is used to store time only.
 Date/Time: It is used to store combination of both.
 Currency: It is used for storing numbers in terms of Dollars, Rupees or other
Currencies.
 Yes/No: It is to declare a logical field which may have only one of the two
opposite values alternatively given as: Yes or No, On or Off, True or False.

Forms: In order to use databases, the user must be provided with an interface that
allows to input, view and edit data based on certain criteria. Form is a front end interface
for data entry and edting.Through a Form user can add new data to the table, delete or
edit an existing data. It helps to minimizing errors in data entry.

Report: A Report is used to present a selected set of contents from the database in a
format that is easily readable. Reports are useful to present and summarize data from
the tables or queries. Normally they are created to be printed out, but they can be seen
on screen or exported to other programs.

Steps in the process of design a database

1. Determine the purpose of your database.

2. Find and organize the information required. Gather all types of information you
might want to record in the database.
6

3. Divide the information into tables:-Divide your information into major

entities/subjects. Each subject then becomes a table.

4. Divide information’s in a table into columns:- Each item in a table becomes a field
,and is displayed as a column in the table.

5. Specify the primary key:- Primary key is a column that is used to

uniquely(distinctively) identify each row.

6. Set up the table relationship:-Look at each table and decide how the data in one
table is related to the data in other tables.

Procedure:
1. Step-1(Create and name the database).
Application---Office----LibreOffice Base------Create a new Database-----Next---------
Finish

2. Step-2(Create and name the Table)

Select “Create table in design view”------Enter the field name and appropriate Field
Type.

3. Step-3(Set Primary Key)

To set EMP ID as Primary Key right click on the row sector of the EMP ID and
select the “Primary Key” from the drop down menu.

4. Step-4(Save the table by giving suitable name)

Click on the save Button to save the Tale.In Save as dialog box enter Table emp
details as Table name and click OK button.Then Close the screen by click.
7

5. Step-5 (Enter data in the newly created Table)

Double click on the newly created table”Table Emp Details”to open and enter
data.Then enter the details.

6. Step-6(Design query )
Select the icon queries and select “create query in design view”-----select “Table
emp details”----add---close----double click in the required field(emp id,emp
name,emp bp)

7. Step-7 Against the criterion in the column, enter the criteria. Then press F5 or run
query button to display the results.

8. Step-8(Design Query to get the list of name of employees beginning with “A”)
Select icon Queries and select”Create Query in Design View”----select “Table Emp
Details”---- Add----Close-------double click on the required fields.

9. Step-9 Against the criterion in the EMP NAME column,enter the criteria
like’A*’.Then press F5or RUN Querry button to display the result.