CHAPTER 6 DATABASE MANAGEMENT SYSTEM FOR ACCOUNTING

Chapter 6: Database Management System (LibreOffice Base)

📌 Chapter 6 – Important Topics:

Database – Definition, Need, Advantages
DBMS – Definition, Components
LibreOffice Base – Introduction
Key Terms – Table, Field, Record, Entity, Attribute, Primary Key, Foreign Key, Relationship, Query, Form, Report
Steps to Create a Database
Creating Tables (Design View, Data Types, Primary Key)
Defining Relationships
Creating Forms (Wizard, Design View, Controls, Subforms)
Creating Queries (Wizard, Design View, Criteria, Computational Fields)
Creating Reports (Wizard, Static vs Dynamic)
Advanced Example – Ledger Creation & Voucher Entry Form
Previous Year Exam Questions (2018–2024)

1. What is a Database?

A database is a collection of related data that is organised so that you can easily view, access, manage and update the data.

Simple Explanation: Think of a database as a smart digital filing cabinet. Instead of paper files, you store information in tables, and you can instantly search, sort, and combine data from different tables.

Complementary Explanation: Databases are everywhere – your school’s student records, the library catalogue, online shopping websites, even your mobile phone contacts are stored in databases.

📌 Live Example 1 – School Database: Your school uses “Sampoorna” software to store details of all students: name, admission number, class, date of birth, address. This is a database.

📌 Live Example 2 – Bank Database: A bank maintains a database of all customers, their account numbers, balances, transactions – so you can withdraw money from any branch or ATM.

💭 Think: What problems would arise if a hospital stored patient records only on paper?
Simple Hints: Slow search, risk of losing files, only one person can access at a time, no easy backup.

Database Management System (DBMS)

DBMS is software that enables users to create and maintain a database. It helps in collection, storage, manipulation, retrieval and management of data. Popular DBMS software: LibreOffice Base, MS Access, Oracle, MySQL, SQL Server.

Simple Explanation: DBMS is like the operating system for your data – it gives you tools to build tables, ask questions (queries), create user‑friendly forms, and print reports.

Advantages of DBMS

Centralised management of data
Data is portable – can be filtered as needed
Multiple users can access simultaneously
Reduced data redundancy
Timely availability of information
Helps management take correct decisions

📌 Live Example – Redundancy Elimination: In a spreadsheet, if you store student name and class in both fee table and attendance table, data repeats. In a database, you keep student details once, and link other tables using Admission Number – no repetition.

💭 Think: Why is “reducing data redundancy” important?
Simple Hints: Saves storage space, avoids inconsistencies (if a student changes class, you update only one place).

Components of DBMS

Data: Raw facts – e.g., “Ravi”, “15”, “Palakkad”.
Hardware: Physical devices – computer, hard disk, RAM.
Software: The DBMS program – LibreOffice Base, etc.
Users: People who interact with the database – administrators, data entry operators, end users.

Exam Practice (2 marks): Name any four DBMS software available for data manipulation by business firms.
Answer: 1. Microsoft Access 2. Oracle Database 3. MySQL 4. LibreOffice Base 5. PostgreSQL (any four).
Hint: Think of software used to create and manage databases.

2. LibreOffice Base

LibreOffice Base is a free and open source DBMS included in the LibreOffice suite. It allows you to create databases, tables, queries, forms, and reports. The default file extension is .odb.

Simple Explanation: Base is the database component of LibreOffice. It is like MS Access but completely free. You can design tables, enter data through forms, and get answers using queries.

📌 Live Example 1 – Student Database: A teacher creates a database “Class12.odb” with tables for students, marks, and attendance. She uses a form to enter marks, and a query to find students who scored above 90%.

📌 Live Example 2 – Library Management: A small library uses Base to store book details, member details, and issue records. They generate a report of overdue books.

💭 Think: Why do many government schools in Kerala use LibreOffice Base instead of MS Access?
Simple Hints: It is free (FOSS), no license cost, can be installed on any number of computers legally.

3. Important Terms in Database

Term	Meaning
Data	Raw facts – e.g., "Aneesh", 3031, "Male".
Table	Collection of rows and columns that stores data about a specific subject (e.g., Student table).
Field	A column in a table – stores one type of information (e.g., Student_Name).
Record / Tuple	A row in a table – contains all fields about one person/item.
Entity	A real‑world object – e.g., Student, Book, Employee.
Attribute	Characteristics of an entity – e.g., Name, Date of Birth.
Primary Key	A field (or combination) that uniquely identifies each record – e.g., Admission No.
Foreign Key	A field in one table that refers to the primary key of another table – creates a link.
Relationship	A connection between tables based on common fields.
Query	A question asked to the database – retrieves specific data.
Form	A graphical interface for easy data entry and editing.
Report	A formatted presentation of data for printing or viewing.

Simple Explanation: Table = spreadsheet, Field = column header, Record = one row, Primary Key = unique ID (like admission number), Foreign Key = ID that points to another table.

Complementary Explanation: A database can have many tables. Relationships connect them so you can combine information (e.g., show student name and bus fee together).

📌 Live Example – Primary Key: In a student table, “Admission Number” is perfect as primary key because no two students share the same admission number.

📌 Live Example – Foreign Key: In a fee table, “Admission Number” is a foreign key – it links each fee record to a specific student in the student table.

💭 Think: Can we use “Name” as primary key? Why not?
Simple Hints: Names can be duplicated – two students may have same name. Primary key must be unique.

Exam Practice (2 marks): Explain the terms 'Entity' and 'Attribute' with an example.
Answer: Entity: A real‑world object (e.g., 'Student'). Attribute: Property of an entity (e.g., Student's 'Roll No', 'Name').
Hint: Entity is the whole object; attribute is its characteristic.

4. Steps to Create a Database in LibreOffice Base

Create Blank Database: Open Base → “Create a new database” → Next → Finish → Name and save the file (.odb).
Create Tables: Use Design View or Wizard.
Define Relationships: Tools → Relationships.
Create Forms: For easy data entry.
Create Queries: To retrieve specific information.
Create Reports: To present data in printed format.

Simple Explanation: First you build the empty file, then you create the tables (like designing the shelves), then you link them, then you make forms (like a data entry screen), then you ask questions (queries), and finally you print reports.

Complementary Explanation: You don’t have to do all steps in strict order – you can always go back and add more tables or modify forms.

📌 Live Example – School Bus Database: Step 1: Create “SchoolBus.odb”. Step 2: Create tblStudent, tblFee. Step 3: Link them by Admission No. Step 4: Create frmStudent to enter details. Step 5: Query to find students who haven’t paid fee. Step 6: Report of defaulters.

📌 Live Example – Employee Database: Create “Payroll.odb”. Tables: tblEmployee, tblSalary. Relationship: EmpID. Form: frmEmployee. Query: calculate DA, Gross. Report: Salary slip.

💭 Think: What happens if you try to enter data in a form before creating the table?
Simple Hints: The form needs a table to store data; you must create the table first or the form will have no place to save.

5. Creating Tables in Design View

Path: In Base main window → Tables → Create Table in Design View.

You specify:

Field Name: Name of the column (e.g., STUD_ID).
Field Type: Data type – Text [VARCHAR], Number [NUMERIC], Date [DATE], etc.
Description: Optional.
Field Properties: Entry required (Yes/No), Length, Default value, Format example.

Setting Primary Key

Right‑click on the gray box to the left of the field name → Choose “Primary Key”. A key symbol appears.

Saving the Table

Press Ctrl+S → Give a table name (e.g., tblSTUDENT) → OK.

Simple Explanation: Design View is where you build the structure of your table – you decide what columns to have and what kind of data they will hold.

Complementary Explanation: You can also create tables using the Wizard, which gives ready‑made templates (e.g., Employees, Students). Design View gives you full control.

📌 Live Example – Student Table: Fields: ADM_NO (Text, primary key), NAME (Text), DOB (Date), CLASS (Text), FEE (Number).

📌 Live Example – Employee Table: Fields: EMP_ID (Text, primary key), EMP_NAME (Text), EMP_DEPT (Text), BASIC_PAY (Number).

💭 Think: What data type would you choose for “Phone Number”?
Simple Hints: Text [VARCHAR], because phone numbers are not used in calculations and may have leading zeros or special characters.

6. Data Types in Base

Text [VARCHAR]: Stores letters, numbers, symbols – no arithmetic. Max length 100 (can be changed).
Memo [LONGVARCHAR]: Stores large amounts of text – notes, comments, descriptions.
Number [NUMERIC]: Stores numeric values for calculations – integers, decimals.
Date [DATE]: Stores date (day, month, year).
Time [TIME]: Stores time only.
Date/Time [TIMESTAMP]: Stores both date and time.
Currency [DECIMAL]: Stores monetary values with fixed decimal places.
Yes/No [BOOLEAN]: Stores logical values – Yes/No, True/False, On/Off.

Simple Explanation: Choosing the correct data type is very important. If you store a number as text, you cannot add it. If you store a date as text, you cannot sort it by date.

Complementary Explanation: The “Length” property for Text fields limits how many characters can be entered – this saves space and prevents errors.

📌 Live Example – Right Choice: Admission Number → Text (even though it looks like a number, we never do math on it). Basic Salary → Number (we need to calculate PF, DA). Date of Birth → Date. Gender → Yes/No? Actually better to use Text with values “Male”/“Female” or a list box.

📌 Live Example – Wrong Choice: If you store Date of Birth as Text, the query “find all students born after 2005” will not work correctly – it will compare alphabetically, not chronologically.

💭 Think: What data type is suitable for storing a student’s total marks (out of 500)?
Simple Hints: Number [NUMERIC] – because you may want to calculate average, percentage, etc.

7. Defining Relationships

Path: Tools → Relationships → Add Tables → Select tables → Add → Close.

To create a relationship:

Drag the primary key field from one table to the corresponding foreign key field in the other table.
The “Relations” dialog appears – click OK.
A line connects the tables.

Simple Explanation: Relationships are the “glue” that joins tables. You link the primary key of one table to the foreign key of another – just like using VLOOKUP in Calc, but permanent.

Complementary Explanation: Relationships allow you to create queries that pull data from multiple tables at once – e.g., show student name from tblStudent and fee amount from tblFee.

📌 Live Example – One‑to‑Many: One student can have many fee records (each quarter). Link tblStudent.STUD_ADMNO (primary) to tblFee.STUD_ADNO (foreign). One side (student) is “one”, many side (fee) is “many”.

📌 Live Example – One‑to‑One: A table of student personal details and a table of student medical information – both use same Admission No as primary key – one‑to‑one relationship.

💭 Think: What happens if you try to delete a student who has fee records in the fee table?
Simple Hints: By default, the database prevents deletion (referential integrity) – you must delete the fee records first or set “Cascade Delete”.

Exam Practice (1 mark): The tool used to connect two tables in LibreOffice Base.
(a) Report. (b) Queries (c) Form (d) Relationship
Answer: (d) Relationship.
Hint: It is under the Tools menu.

8. Creating Forms

Forms are used for easy data entry and editing. Two methods:

Use Wizard to Create Form – step‑by‑step, user friendly.
Create Form in Design View – manual, more control.

Form Wizard Steps

Select table/query and add fields.
Add subform (optional).
Arrange controls (layout).
Set data entry mode (new data only or display all).
Apply styles (background, border).
Set name and finish.

Modifying a Form (Design Mode)

Right‑click form name → Edit.
Change labels: Ctrl+click on label → Right‑click → Control → General tab → Label.
Change font, size, colour: via Properties.
Align controls: Select multiple → Right‑click → Align.
Add heading: Use Label tool.

Adding Controls in Design View

List Box: Shows values from another table/query – user selects one.
Push Button: Executes an action (Save, Delete, Close, etc.).
Check Box: For Yes/No fields.
Option Button (Radio): Mutually exclusive choices.

Simple Explanation: Forms are like the screen you see when you open an app – you type into boxes, select from lists, and click buttons. Base creates these screens for your database.

Complementary Explanation: You can make forms look professional – with titles, colours, and even your company logo.

📌 Live Example – Student Data Entry Form: A form with fields for Admission No, Name, Class, Date of Birth. A list box to select Place (values from a separate Place table). Buttons: New, Save, Delete, Close.

📌 Live Example – Fee Entry Form with Subform: Main form shows student details; subform shows all fee transactions for that student. As you select a student, the subform updates instantly.

💭 Think: Why is a list box better than a simple text box for entering “Place”?
Simple Hints: Prevents spelling mistakes, ensures consistency, faster data entry.

Exam Practice (2 marks): Explain the different methods of creating 'FORMS' in a database software.
Answer: 1. Using Form Wizard: Step‑by‑step guided process. 2. In Design View: Manually drag and drop fields for custom layout.
Hint: Wizard is easy; Design View gives full control.

9. Creating Queries

A query is a question asked to the database to retrieve, calculate, or manipulate data. Two methods:

Query Wizard – simple, guided.
Query Design View – full control, multiple tables, criteria, calculations.

Query Wizard Steps

Select table/query and add fields.
Set sorting order.
Set search conditions (criteria).
Select type – Detailed or Summary.
If Summary, set grouping and grouping conditions.
Give alias names (optional).
Name the query and finish.

Query Design View

You can add multiple tables; the join lines appear automatically if relationships are defined.

Field: Select field from table.
Alias: Display name for column.
Table: Automatically filled.
Sort: Ascending/Descending.
Visible: Check to show column.
Function: Sum, Avg, Count, etc. (for summary queries).
Criterion: Condition to filter records (e.g., "MALE", ">5000", "LIKE 'A%'").

Computational Field

You can create calculated columns, e.g., "tblBUSFEE.QUARTER_FEE" - 500 to show outstanding fee. Give an alias like "Outstanding".

Simple Explanation: Queries are like asking the database: “Show me only the students from Palakkad” or “Calculate total fee collected”. You type the condition, and the database answers instantly.

Complementary Explanation: Queries can be saved and reused. They are the most powerful feature of a database – they turn raw data into useful information.

📌 Live Example – Criteria: In the STUDENT table, to see only male students, set criterion for STUD_SEX = "MALE". To see students born after 2005, set criterion for DOB > "31-12-2005".

📌 Live Example – Computational Field: If basic pay is stored, create a query with column: "tblSalary.BASIC_PAY" * 0.4 as "DA". Now whenever you run the query, DA is calculated live.

💭 Think: What is the difference between a query and a filter?
Simple Hints: A filter is temporary; a query is saved and can include calculations, multiple tables, and can be used as the source for forms/reports.

Exam Practice (1 mark): ______ is used to retrieve information from a database.
Answer: Query.
Hint: It asks a question and returns the answer.

10. Creating Reports

Reports present data in a formatted, printable layout. Two types:

Static Report: Data is fixed at the time of creation – does not change later.
Dynamic Report: Shows live data – updates when the report is opened.

Report Wizard Steps

Select table/query and add fields.
Label fields (give meaningful headings).
Grouping (optional) – e.g., group by Class.
Sorting – e.g., by Name.
Choose layout (columnar, in blocks, etc.) and orientation.
Set report title and choose Static or Dynamic.
Finish.

Simple Explanation: A report is the final output – like a printed list, a salary slip, or a fee summary. You design it once, and the software fills it with current data.

Complementary Explanation: Reports can have headers, footers, page numbers, and even images. They are what managers and clients see.

📌 Live Example – Fee Dues Report: A report grouped by class, showing student name, admission number, total fee paid, and outstanding amount. Printed and given to class teachers.

📌 Live Example – Employee Salary Slip: A report based on a query that calculates net pay. Each employee gets a printed slip with earnings and deductions.

💭 Think: When would you choose a static report instead of a dynamic report?
Simple Hints: For archival purposes – e.g., final audited financial statements that should not change. Also for regulatory submissions.

11. Advanced Example: Ledger Creation and Voucher Entry Forms

The textbook demonstrates creating a complete accounting database in Base:

Table TBLADD_ACCOUNTS: Stores ledger account names, groups, types.
Table tbTransactionMaster: Stores all transactions (voucher entries).
Form frmCreateLedger: To add new accounts – uses text boxes, check boxes, option buttons, push buttons.
Form frmTransactionMaster: Voucher entry screen – combo boxes to select debit/credit accounts, text boxes for amount, push buttons.
Queries: Payment Query, Receipt Query – filter by voucher type.
Reports: Payment Report, Receipt Report.

This shows how LibreOffice Base can be used to build a mini‑accounting system.

Simple Explanation: Using tables, forms, queries, and reports, you can create your own custom accounting software in Base – no programming required!

Complementary Explanation: This example integrates all the concepts you have learned – it is a capstone project for the chapter.

📌 Live Example – Ledger Creation Form: Screen with fields: Code, Category, Account Name, Group. Radio buttons for Assets/Liabilities/Capital/Revenue/Expenses. Push buttons: Create Ledger, Save Ledger, Delete Ledger, Undo.

📌 Live Example – Voucher Entry Form: Dropdown to select Voucher Type (Receipts, Payments, Contra, etc.), fields for Date, Debit Account, Credit Account, Amount, Narration. Buttons: New, Save, Delete.

💭 Think: Why does the Voucher Entry Form need a “Narration” field?
Simple Hints: To record the purpose of the transaction – essential for audit and understanding later.

📘 Important Exam Questions – Chapter 6 (2018–2024)

Q1 (2020 Mar – 1 Mark): The tool used to connect two tables in LibreOffice Base.
(a) Report. (b) Queries (c) Form (d) Relationship
Answer: (d) Relationship.

Q2 (2020 Say – 1 Mark): ______ is used to retrieve information from a database.
Answer: Query.

Q3 (2018 Mar – 2 Marks): Name any Four DBMS software available for data manipulation by business firms.
Answer: 1. Microsoft Access 2. Oracle Database 3. MySQL 4. LibreOffice Base 5. PostgreSQL (any four).

Q4 (2018 Say – 2 Marks): Explain the different methods of creating 'FORMS' in a database software.
Answer: 1. Using Form Wizard: Step‑by‑step guided process. 2. In Design View: Manually drag and drop fields for custom layout.

Q5 (2019 Mar – 2 Marks): A DBMS has different components. List out any Four of them.
Answer: 1. Tables 2. Queries 3. Forms 4. Reports 5. Relationships (any four).

Q6 (2019 Say – 2 Marks): Explain the terms 'Entity' and 'Attribute' with an example.
Answer: Entity: A real‑world object (e.g., 'Student'). Attribute: Property of an entity (e.g., Student's 'Roll No', 'Name').

അധ്യായം 6: ഡാറ്റാബേസ് മാനേജ്മെന്റ് സിസ്റ്റം (ലിബ്രെ ഓഫീസ് ബേസ്) – മലയാളം

📌 പ്രധാന പാഠഭാഗങ്ങൾ:

ഡാറ്റാബേസ് – നിർവ്വചനം, ആവശ്യകത, ഗുണങ്ങൾ
ഡിബിഎംഎസ് – നിർവ്വചനം, ഘടകങ്ങൾ
ലിബ്രെ ഓഫീസ് ബേസ് – ആമുഖം
പ്രധാന പദങ്ങൾ – പട്ടിക, ഫീൽഡ്, റെക്കോർഡ്, എന്റിറ്റി, ആട്രിബ്യൂട്ട്, പ്രൈമറി കീ, ഫോറിൻ കീ, ബന്ധം, ക്വറി, ഫോം, റിപ്പോർട്ട്
ഡാറ്റാബേസ് സൃഷ്ടിക്കാനുള്ള ഘട്ടങ്ങൾ
പട്ടികകൾ സൃഷ്ടിക്കൽ (ഡിസൈൻ വ്യൂ, ഡാറ്റാ തരങ്ങൾ, പ്രൈമറി കീ)
ബന്ധങ്ങൾ (റിലേഷൻഷിപ്പ്) നിർവ്വചിക്കൽ
ഫോമുകൾ സൃഷ്ടിക്കൽ (വിസാർഡ്, ഡിസൈൻ വ്യൂ, കൺട്രോളുകൾ, സബ്ഫോം)
ക്വറികൾ സൃഷ്ടിക്കൽ (വിസാർഡ്, ഡിസൈൻ വ്യൂ, മാനദണ്ഡങ്ങൾ, കണക്കുകൂട്ടൽ ഫീൽഡുകൾ)
റിപ്പോർട്ടുകൾ സൃഷ്ടിക്കൽ (വിസാർഡ്, സ്റ്റാറ്റിക് vs ഡൈനാമിക്)
വിപുല ഉദാഹരണം – ലെഡ്ജർ രൂപീകരണവും വൗച്ചർ എൻട്രി ഫോമും
മുൻവർഷ പരീക്ഷാ ചോദ്യങ്ങൾ (2018–2024)

🔍 ഈ അധ്യായത്തിലെ എല്ലാ ആശയങ്ങളും ലളിതമായ വിശദീകരണം, രണ്ട് ലൈവ് ഉദാഹരണങ്ങൾ, ഒരു ചിന്താ ചോദ്യം, പരീക്ഷാ പരിശീലനം എന്നിവയോടെ വിശദീകരിച്ചിരിക്കുന്നു. LibreOffice Base പരിശീലിക്കുക.

SimonMash.com | Kerala Plus One & Plus Two Study Notes, Question Papers, Answer Keys, Resources