Kerala Plus Two Computerised Accounting Notes Chapter 6 Data Base Management System for Accounting
Database/ Data source – Introduction
A database is a collection of related data. It is organised in such a way that its contents can easily be accessed, managed and updated. In LibreOffice, database is also called data source. Database consists of interrelated data tables that are structured in a manner that ensures-data consistency and integrity. LibreOffice base, MS Access, Oracle, SQL server, etc. are the commonly used softwares for data base management.
Database Management System (DBMS)
DBMS is a collection of programs. It enables the users to create and maintain a data base. It is a general purpose software system that facilitates the process of defining, constructing and manipulating database for various applications.
Advantage of database/data source
- All of the information is together
- The information is portable
- Information can be accessed at any time
- Many users can access the same database at the same time.
- Reduced data entry, storage and retrieval cost.
Disadvantages of database/Data source
- Designing of database is a complex and time consuming process
- Initial training is required for all the users
- Installation cost is high
Basic concepts of LibreOffice Base
- Entities: Anything in the real world is called entities. It may be person, place or things.
Eg: Employee is an entity, Orange is an entry - Attributes: These.define the characteristics of an entity.
Eg: Name, Age, Caste, Salary etc. - Identifiers: The unique attribute of an entity is called identifier. This is also called primary key.
Eg: Admission number of a student, Aadhar Number of a person etc. - Relationships: These are the logical links between two entities or tables.
Components /Elements of LibreOffice Base
- Fields: Individual pieces of data in a database are called fields.
- Table: rows and columns to present fields in a database is called table. When creating a table, the characteristics of each field to be defined.
- Forms: Forms are used to enter or modify data (fields) in to tables. Forms allow the user to display the data in a Table or Query.
- Query: Query is a question. Queries are used to view, change and analyse data in different ways. It creates a new table from the existing tables based upon the question/ request asked to the data base.
- Reports: It is used to create and present information based on queries in a easily readable format.
Planning (or designing) a database/ Data source
The first step in creating a database is to list down the various fields which are necessary for creating a database. The listed fields are used to create tables of the database. While entering fields into Tables, a primary key or an identifier is to be set for each table.
The primary key field cannot be left blank. The relationships of entities or tables can be created with the support of primary key. The relationships may be
- One -to-One
- One -to-Many
- Many-to-Many
The database created on the basis of relationships between different data tables is called relational database. The database design can be used to describe the structure of different parts of the overall data base. Avoiding the duplication of attributes/ fields is key criteria of database design.
Creating a new database
To Create a new database, select File → New → Database from the menubar, or click the arrow next to the New icon on the standard tool bar and select Database from the drop-down menu. Both methods open the Database wizard. On the first page of the Database wizard, Select create → a new databases → click Next.
The second page has two questions
- Yes, register the data base for me
- Open the database for editing.
Choose any one from the above and click Finish. In LibreOffice Base, the entire database is encompassed in a file with extension .odb. This file format is actually a container of all elements of the database, including Fields, Tables, Forms, Queries and Reports.
Creating database Tables
In DBMS, data is organised in Tables. A Table is a collection of data about a specific topic. Tables organise data into columns and rows. Each table is given a name. This is used to refer the table. The name depicts the content of the table. A database must have at least one table and may have several.
To work with Table, clicks the Tables icon in the Database list or Press Alt+A. The three tasks that we can perform on a table are in the Task list given below.
- Using the wizard to create a Table
- Creating a table by copying an existing table
- Creating tables in Design view.
1. Using the wizard to create a new Table:
Step 1: Open LiberOffice Base
Application → Office → LibreOfficeBase
Step 2: From the Data base wizard Screen, Create a database file or open an existing database file.
Select Database → Select Create a new database option and click on Finish button → then we get save dialogue box.
Step 3: Type appropriate file name. The default extension ‘.odb’ will be automatically added.
Step 4: Select location and click on Save
2. Using the wizard to open an Existing database:
Step 1: Open LibreOffice Base. From Database
wizard → Select open an existing database file option and click on Open button.
Step 2: Choose the file from the destination and click open.
3. Creating a table by Copying an existing table:
Step 1: Open LibreOffice Base
Application → Office → LibreOffice Base
Step 2: Click on the Tables icon in the database pane to see the existing tables
Select Database → Click on the Table icon → Right click on the Table from the Existing Tables.
Step 3: Choose Copy form the pop-up menu
Step4: Move the mouse pointer below the table, → right click → Select Paste. The copy Table dialogue opens.
Step 5: Change table name and click Next
Step 6:
Step 7: Click Create the new table is created
Step 8: Click the Save button at the top of the main database window.
4. Creating Tables in Design View:
Step 1: Click Tables from Data base Pane Database Pane → click Tables.
Step 2: Click Create Tables in design view in Tasks area. The design view of the new table will appear in the work area of the window.
Step 3: In design view, we can see three columns like Field Name, Field Type and Description. Create required field for the Table. Click Field Name cell → center Field names from top to bottom.
Step 4: Right click on the Field Name required to set as unique identifier for the tables, Select Primary Key option from the pop-up menu.
Step 5: In the Field Type, select appropriate field type from the combo box.
Step 6: In Description Field, we canenter appropriate description for each attributes.
Step 7: Save the table by providing table name.
5. Defining Relationships between Tables:
Relationships are used for connecting tables in database to get the advantage of data redundancy. Having completed the designs of all data tables, the next step is to establish relationships between different tables.
Step1: Click on the Tools menu and then Relationships
Step 2: Relation Design window opens and in the work area, a Add Tables dialogue box will appear.
Step 3: Select a Table and clik Add bottom to add it in the relationships.
Step 4: Add two tables in this manner after that click the close button.
Step 5: Create a relationship between two tables, Position the mouse pointer over desired field in table object, hold down the left mouse button, drag the pointer right to targeted field of the next table object and then release the mouse button.
This can also be done Insert → New Relation menu.
Creation of Forms in LibreOffice Base
Forms are used to input data into the database. In the language of database, a Form is a front end for data entry and editing. A Form is a window or screen that contain numerous fields or spaces to enter data. Each field holds a field label so that any user gets an idea of its contents.
The following two methods are used to create forms in LibreOffice Base
- Create Form in Design View
- Use wizard to create Form. The easy way to create Forms is use wizard to create Form.
1. Use Wizard to Create Form:
Step 1: Select Forms options from Database Pane
Step 2: Click on Use Wizard to Create Form Then. Form wizard window will appear.
Step3: Under the Table or queries, select Tables. The fields in the selected tables are listed in Available Fields list. Select the required field on by one and click on
Step 4 – After selecting the required field proceed by clicking Next.
Step 5 – Add sub Form fields. This step is similar to step 3
Step 6 – Get joined Fields.
This step is for tables and queries for which no relationships has been defined. The wizard skip this step. Because we have already defined the relationship.
Step 7: Arrange controls: A control in a Form consist of two parts: label and field. This step in creating the Form determines where a controls label and field are placed relative each other. Four choices are available.
Step 8: Set data entry: It is better to accept the default settings. Click Next.
Step 9: Apply styles: The background colour, field boarder etc. can be selected from this options.
Step 10: Set the name of the form: we can give the name of the Form we are creating. The name must be unique and must have a relation with the data to be stored → click Finish. The Form opens in Edit mode.
Entering data in a form
The text box can be used to add data in the Form. Click on the text Box Icon, and click on the work area. The cursor will be positioned on the Top left of the work area. Then the matter is to be added. The text entered can be formatted. Images can also be inserted in the Form by selecting Image Insert Icon.
Creation of Query in LibreOffice Base
Queries are used to get specific information from the database. Queries are also used to manipulate the database content. Structured Query Language (SQL) is the most widely-used query language. LibreOffice Base also uses SQL command for querying its database. The query operations can be done in two different ways.
- Using the wizard to create a query
- Using the design view
1. Using the wizard to create a query:
Step 1: Selection of Fields
The first step in Query wizard is field selection. All the tables included in the data base can be seen in the Table list select the appropriate Table. All the fields of the selected table can be seen in the Available Fields list.
The user can select the fields needed from the list using the tools arranged right to Available fields list. The selected fields can be seen in Fields in the Query list. The order of the selected fields can change using the tools (∧ and ∨) right to Fields in the query list. Then, press Next button or Finish button.
Step 2: Select the sorting order: In this step, the field name to sort the query result can be selected. (Skip this step, if no sorting is needed).
Step 3: Select the search conditions: This step specifies the search conditions to filter the query. (Skip the step, if no filtering is needed).
Step 4: Details of summary: This page specifies whetherto display all records of the query, or only the results of aggregate functions. This page is only displayed when there are numerical fields in the query that allow the use of aggregate functions.
Step 5: Grouping conditions: Specfies wheter to group the query. The data source must support the SQL statement “order by clause” to enable this page of the wizard.
Step 6: Assign aliases if desired: – Thisjsage helps to assign aliases to field name. Aliases are optional, and can provide more user- friendly names, which are displayed in place of field names.
Step 7: Overview: This wizard page gives an overview of the query made. It helps to enter a name of the query, and specifies whether to display or to modify the query afterthe wizard is finished.
Step 8: Press Finish button after the completion of Query wizard entry. The Query will be saved. The user can run this query at any time.
Step 9: Run the saved Query: Select Queries option from the left panel of the LibreOffice Base window. The saved query can be seen in the right side. Double click on the query name to run the query.
2. To create query in design view:
- Step 1: Use the option Create Query in Design view from Base window to create query in design view.
- Step 2: Use Add Table or Query dialogue to include table(s) to query design.
- Step 3: Include the fields and formula in the top row and give aliases in the second now, if needed.
- Step 4: Press Run Query button to execute the query.
Creation or Reports in LibreOffice Base
Information from a database can be generated through the Reports in LibreOffice Base. The reports can be printed and formatted as perthe requirements of user. The reports can be edited, printed and exported to PDF format.
The reports can be created by the following two ways.
- Create Report in Design view
- Use wizard to ere ate Report
1. Use wizard to create Report in LibreOffice Base:
- Step 1: Click the icon Reports in Database pane
- Step 2: Click on Use wizard to create Report option in Task area.
- Step 3: Select table or query from the drop down option for which reports need to be created.
- Step 4: Select the required fields
- Step 5: Enter title for the report in the field Title of the report and click on Finish button.
- Step 6: The report generated is in the read only mode. It can be edited by clicking on Edit Document option.
In short, When the Report wizard is opened, the Report Builder is also opened. As we make our selections in the wizard these appear in layout in the Report Builder. After finishing the selections, save the report, name it and then close it.
Accessing other Data Sources
LibreOffice Base allows other data sources to be accessed and linked into LibreOffice documents. To acess a data source which is not a .odb file.
- Step 1: File → New → Database opens the Data base wizard window.
- Step 2: Select Connect to exisiting data base. Click the arrow next to Data base type field and select the Database type from drop down list. Click Next.
- Step 3: Click Browse and select the database click Next.
- Step 4: Accept the default settings: Register the data base for me, and open the database for editing. Click Finish. Name and save the database in the location of our choice.
1. Accessing a spreadsheet as a data source:
- Step 1: Choose File → New → Data base.
- Step 2: Select Connect to an existing database. Select Spread sheet as a database type.
- Step 3: Click Browse to locate the spreadsheet we want to access. If spreadsheet is password protected, Check the password required box, click Next.
- Step 4: If the spreadsheet requires a users name, enter it. If a password is also required, check its box. Click Next → save the file.
2. Registering *.odb databases:
Databases created by LibreOffice base are in the *.odb format. Other programs can also produce database in this format. Registering a*.odb data base is simple.
- Step 1: Select Tools → Options → Libre Office Base → Database
- Step 2: Under Registered data Bases, Click New.
- Step 3: Browse to where the database is located.
- Step 4: Make sure the registered name is correct .Odb
- Step 5: Click OK.
Using data sources in LibreOffice
Any data source registered in spreadsheet or text document can use in other LiberOffice components including writer and calc.
1. Viewing data sources:
Open a document in writer or calc. To view the data sources available, Press F4 or select View → Data sources from the pull down menu. This brings up a list of registered databases. To view each data base, click on the arrow to the left of the database’s name.
2. Editing Data sources:
Some data sources can be edited in the Data view window. A record can be edited, added or deleted. Editing the data requires only a click in the cell whose data should be changed. To delete the record, right click on the gray box to the left of row to highlight the entire row, and select Delete row to . remove the selected row.
3. Launching Base to work on data sources:
We can launch LibreOffice Base at any time from the Data source window. Just right click on a database, or its tables or Queries icons and select Edit Database File. In Base, we can edit, add, and delete tables, queries, forms and reports.
4. Using Datasources in writer and Calc.:
Data can be placed into writer and Calc documents from the tables in the data source window. In writer, values from individual fields can be inserted. Or a complete table can be created in the writer document. One common way to use a data source is to perform a mail merge.