Database Management System using LibreOffice Base

Database Management System using LibreOffice Base

Class 10 | CBSE IT (Code 402) | Part B – Unit 3 | Session 2026–27


Introduction

Database Management System (DBMS) is software that allows you to create, manage, and manipulate databases efficiently. In Part B – Unit 3, you will learn to use LibreOffice Base, a free and open-source database tool, to create databases, tables, forms, queries, and reports.

These skills are essential for organizing large amounts of data, retrieving specific information quickly, and generating professional reports for school projects, future studies, and careers.

💡 Note: LibreOffice Base is completely free to download and use on Windows, macOS, and Linux. Download from: www.libreoffice.org


1. Introduction to Database Management System

1.1 Data and Information

Data:

  • Raw, unorganized facts and figures.

  • Examples: Numbers, text, images, sounds.

  • Example: 25, "Amit", "Pune", 95

Information:

  • Processed, organized, and meaningful data.

  • Provides context and understanding.

  • Example: "Amit is 25 years old, lives in Pune, and scored 95 marks."

    📊 Key Difference: Data becomes information when it is processed and given meaning.


1.2 Database

Database:

  • A structured collection of related data stored electronically.

  • Organized for easy access, management, and updating.

  • Examples: School student database, library book database, hospital patient database, bank account database.

Characteristics of a Good Database:

  • Organized: Data is structured logically (tables, fields, records).

  • Accessible: Easy to retrieve and update data.

  • Secure: Protected from unauthorized access.

  • Consistent: Data is accurate and free from errors.

  • Scalable: Can handle growing amounts of data.


1.3 Database Management System (DBMS)

DBMS:

  • Software that allows users to create, define, manipulate, and manage databases.

  • Acts as an interface between users and the database.

  • Examples: LibreOffice Base, MS Access, MySQL, Oracle, PostgreSQL, SQLite.

Functions of DBMS:

  1. Data Definition – Create database structure (tables, fields, relationships).

  2. Data Manipulation – Insert, update, delete, and retrieve data.

  3. Data Security – Control access and protect data.

  4. Data Integrity – Ensure accuracy and consistency.

  5. Data Backup and Recovery – Protect against data loss.

  6. Multi-user Access – Allow multiple users to work simultaneously.


1.4 Advantages of Database

AdvantageDescription
Data SharingMultiple users can access and share data simultaneously.
Data IntegrityEnsures accuracy and consistency through validation rules.
Data SecurityControls access through passwords and user permissions.
Reduced Data RedundancyMinimizes duplicate data, saving storage space.
Data ConsistencyChanges in one place reflect everywhere (no conflicting data).
Efficient Data AccessQuick retrieval using queries and indexes.
Data Backup and RecoveryBuilt-in tools to backup and restore data.
ScalabilityCan handle large volumes of data as organization grows.
Better Decision MakingGenerate reports for analysis and planning.

1.5 Data Models

Data Model:

  • A conceptual representation of how data is structured and related.

  • Defines how data is stored, accessed, and manipulated.

Types of Data Models:

1. Hierarchical Data Model

  • Data is organized in a tree-like structure (parent-child relationship).

  • Each parent can have multiple children, but each child has only one parent.

  • Example: Organization chart (CEO → Managers → Employees).

  • Limitation: Inflexible; difficult to represent many-to-many relationships.


2. Network Data Model

  • Similar to hierarchical but allows multiple parent-child relationships.

  • More flexible than hierarchical model.

  • Example: Student can enroll in multiple courses; courses can have multiple students.

  • Limitation: Complex to design and maintain.

3. Relational Data Model (Most Common)

  • Data is organized in tables (relations) with rows and columns.

  • Tables are related through common fields (keys).

  • Example: Student table, Course table, Enrollment table linked by Student_ID.

  • Advantages: Simple, flexible, widely used, supports complex queries.

  • Used by: LibreOffice Base, MS Access, MySQL, Oracle, PostgreSQL.

    📌 Note: LibreOffice Base uses the Relational Data Model.


1.6 Relational Database Management System (RDBMS)

RDBMS:

  • A DBMS based on the relational data model.

  • Stores data in tables with relationships between them.

  • Uses SQL (Structured Query Language) for data manipulation.

Key Features of RDBMS:

  • Tables (Relations) – Data stored in rows and columns.

  • Primary Key – Unique identifier for each record.

  • Foreign Key – Creates relationships between tables.

  • SQL Support – Standard language for queries.

  • ACID Properties – Atomicity, Consistency, Isolation, Durability (ensures reliable transactions).

Popular RDBMS Software:

  • LibreOffice Base (free, open-source)

  • MS Access (Microsoft, paid)

  • MySQL (free, open-source, web applications)

  • Oracle (enterprise-level, paid)

  • PostgreSQL (free, open-source, advanced features)

  • SQLite (lightweight, embedded databases)


1.7 RDBMS Terminology

TermDefinitionExample
Table (Relation)Collection of related data organized in rows and columns.Student table with columns: Roll_No, Name, Class, Marks
Field (Attribute/Column)A single piece of information in a table.Name, Class, Marks
Record (Tuple/Row)A complete set of fields for one entity.One student's complete information (Roll_No, Name, Class, Marks)
Primary KeyA field that uniquely identifies each record.Roll_No, Aadhaar_No, Employee_ID
Foreign KeyA field that creates a link between two tables.Class_ID in Student table links to Class table
Candidate KeyAny field that can serve as a primary key.Roll_No, Email, Phone_No (all unique)
Alternate KeyCandidate keys not chosen as primary key.If Roll_No is primary, Email and Phone_No are alternate keys
Composite KeyPrimary key made of two or more fields.(Student_ID, Course_ID) in Enrollment table
DegreeNumber of columns in a table.Student table with 5 columns → Degree = 5
CardinalityNumber of rows (records) in a table.Student table with 100 records → Cardinality = 100

1.8 Objects of an RDBMS (LibreOffice Base)

LibreOffice Base provides four main objects for database management:

1. Tables

  • Store actual data in rows and columns.

  • Foundation of any database.

  • Example: Student table, Teacher table, Product table.

2. Queries

  • Retrieve specific data from one or more tables based on criteria.

  • Can perform calculations, sorting, filtering, and joining tables.

  • Example: "Show all students with marks > 80."


3. Forms

  • User-friendly interface for entering, viewing, and editing data.

  • Easier than working directly with tables.

  • Example: Data entry form for adding new students.

4. Reports

  • Formatted output of data for printing or sharing.

  • Summarize and present data professionally.

  • Example: Student mark sheet report, Sales summary report.

    📊 Relationship: Tables store data → Queries retrieve data → Forms input data → Reports present data.


2. Getting Started with LibreOffice Base

2.1 What is LibreOffice Base?

LibreOffice Base is the database component of the LibreOffice suite. It is a free, open-source DBMS that allows you to create and manage databases, tables, forms, queries, and reports.

Key Features:

  • Free and Open-Source – No cost, no license fees.

  • Cross-Platform – Works on Windows, macOS, Linux.

  • File Compatibility – Saves as .odb (Base), can link to external databases (MySQL, PostgreSQL, Access, etc.).

  • Wizards – Guided tools for creating tables, forms, queries, and reports.

  • SQL Support – Write and execute SQL queries directly.

  • Relationships – Define relationships between tables (one-to-many, many-to-many).

  • Forms and Reports – Create professional data entry forms and reports.


2.2 LibreOffice Base Interface

When you open LibreOffice Base, you see:

  1. Database Pane (Left) – Navigation panel with icons for:

    • Tables – View and manage tables.

    • Queries – Create and run queries.

    • Forms – Design and use forms.

    • Reports – Generate reports.

  2. Tasks Pane (Right) – Quick actions:

    • Create Table in Design View – Manually define table structure.

    • Use Wizard to Create Table – Guided table creation.

    • Open Existing Database – Open saved databases.

  3. Menu Bar – File, Edit, View, Insert, Tools, Window, Help.

  4. Toolbar – Standard tools (Save, Print, Cut, Copy, Paste, Undo, Redo).


2.3 Creating a New Database

Step-by-Step Procedure:

Step 1: Launch LibreOffice Base

  • Windows: Start Menu → LibreOffice → LibreOffice Base.

  • macOS: Applications → LibreOffice → Base.

  • Linux: Applications → Office → LibreOffice Base.

Step 2: Database Wizard Opens

  • On startup, the Database Wizard appears automatically.

  • Or: File → New → Database.

Step 3: Select Database Type

  • Choose: Create a new database.

  • Click Next.


Step 4: Registration Options

  • Register the database for me: Check if you want other LibreOffice apps (Writer, Calc) to access this database.

  • Open the database for editing: Keep checked to start working immediately.

  • Click Next.

Step 5: Save and Proceed

  • Click Finish.

  • Choose save location and filename (e.g., "School_Database.odb").

  • Click Save.

Step 6: Password Protection (Optional)

  • If you want to password-protect the database:

    • Tools → Options → LibreOffice Base → General → Set password.

  • Or: File → Properties → Security → Set password.

    📌 Tip: Always save your database in an organized folder (e.g., "IT_Projects/School_Database.odb").


2.4 Opening an Existing Database

  1. Launch LibreOffice Base.

  2. Go to File → Open (or press Ctrl+O).

  3. Browse to the database file (.odb).

  4. Select and click Open.

  5. If password-protected, enter the password.


2.5 Saving and Closing Database

Saving:

  • Base auto-saves changes, but you can manually save: File → Save (or Ctrl+S).

Closing:

  • File → Close (or click the X button).

  • Or: File → Exit to close LibreOffice completely.

Compact and Repair (if database is slow or corrupted):

  • Tools → Database → Compact.


3. Working with Tables

3.1 What is a Table?

A Table is the fundamental object in a database that stores data in rows (records) and columns (fields).

Example: Student Table

Roll_NoNameClassSectionMarks
101Amit KumarXA85
102Priya SharmaXB92
103Rahul VermaXA78
  • Fields (Columns): Roll_No, Name, Class, Section, Marks.

  • Records (Rows): Each student's complete information.


3.2 Data Types in LibreOffice Base

When creating a table, you must define the data type for each field. Common data types:

Data TypeDescriptionExampleSize
VARCHAR (Text)Alphanumeric characters (letters, numbers, symbols).Name, Address, EmailUp to 255 characters (default)
INTEGER (Number - Whole)Whole numbers (no decimals).Roll_No, Age, Quantity4 bytes
DECIMAL/NUMERIC (Number - Fixed Decimal)Numbers with fixed decimal places.Price, Marks, SalaryUser-defined precision
DATEDates in various formats.DOB, Joining_Date3 bytes
TIMETime values.Start_Time, End_Time3 bytes
TIMESTAMPDate and time combined.Created_At, Updated_At8 bytes
BOOLEAN (Yes/No)True/False, Yes/No, On/Off.IsActive, IsPassed1 bit
MEMO (Long Text)Large text (more than 255 characters).Description, Remarks, AddressUp to 2 GB
BINARYBinary data (images, files).Photo, DocumentVariable
AUTOVALUE (Auto Number)Automatically increments for each new record.ID, Roll_No (auto-generated)4 bytes

📌 Exam Tip: Understand the difference between VARCHAR (text) and INTEGER (whole numbers), and when to use each.


3.3 Creating a Table Using Wizard

Step-by-Step Procedure:

Step 1: Open Database

  • Open your LibreOffice Base database file.

Step 2: Select Tables

  • In the Database Pane (left), click Tables.

Step 3: Use Wizard

  • In the Tasks Pane (right), click Use Wizard to Create Table.

Step 4: Sample Tables (Optional)

  • The wizard shows sample tables (Personal, Business, etc.).

  • You can select a sample and modify it, or choose <none> to create from scratch.

  • Click Next.

Step 5: Select Fields

  • Available fields: Predefined fields from sample tables.

  • Selected fields: Fields you want in your table.

  • Use > or >> buttons to move fields to "Selected fields".

  • Or: Create custom fields manually:

    • Field Name: Enter name (e.g., Roll_No, Name, Class).

    • Field Type: Choose data type (VARCHAR, INTEGER, DATE, etc.).

    • Length: Set maximum characters (for text fields).

    • Example: Roll_No (INTEGER, Length 10), Name (VARCHAR, Length 50).

  • Click Next.


Step 6: Set Primary Key

  • Choose a field as Primary Key (unique identifier).

  • Options:

    • Create primary key: Select a field (e.g., Roll_No).

    • Auto value: Automatically increments (recommended for ID fields).

    • No primary key: Not recommended (can lead to duplicate records).

  • Click Next.

Step 7: Create Table

  • Table name: Enter a name (e.g., "Student").

  • Insert data immediately: Check to add records right away.

  • Click Finish.

Step 8: Enter Data (if selected)

  • A table view opens.

  • Enter data row by row.

  • Press Tab to move to the next field.

  • Press Enter to move to the next record.


3.4 Creating a Table in Design View (Manual Method)

Step-by-Step Procedure:

Step 1: Select Tables

  • In the Database Pane, click Tables.

Step 2: Create Table in Design View

  • In the Tasks Pane, click Create Table in Design View.

Step 3: Define Fields

  • A grid appears with columns: Field Name, Field Type, Description, Default Value, Auto Value.

  • Enter each field:

    • Field Name: Type name (e.g., Roll_No).

    • Field Type: Choose from dropdown (INTEGER, VARCHAR, DATE, etc.).

    • Length: Set for text fields (e.g., 50 for Name).

    • Description (optional): Add notes about the field.

Example: Student Table

Field NameField TypeLengthDescription
Roll_NoINTEGER-Primary Key, Auto Value
NameVARCHAR50Student's full name
ClassVARCHAR10Class (e.g., X, XI, XII)
SectionVARCHAR5Section (A, B, C)
DOBDATE-Date of Birth
MarksDECIMAL5,2Total marks (e.g., 95.50)
IsActiveBOOLEAN-Active student (Yes/No)


Step 4: Set Primary Key

  • Right-click the field you want as primary key (e.g., Roll_No).

  • Select Primary Key.

  • A key icon appears next to the field.

Step 5: Set Auto Value (for Primary Key)

  • In the Auto Value column, select Yes for the primary key field.

  • This ensures automatic numbering for new records.

Step 6: Save Table

  • Click the Save icon (or press Ctrl+S).

  • Enter table name (e.g., "Student").

  • Click OK.

Step 7: Enter Data

  • Double-click the table name in the Database Pane.

  • Enter records row by row.

  • Auto-value fields (e.g., Roll_No) fill automatically.


3.5 Editing Table Structure

To Modify Table Design:

  1. Right-click the table name → Edit.

  2. Make changes (add/delete fields, change data types).

  3. Save changes (Ctrl+S).

To Add a New Field:

  1. Open table in Design View.

  2. Click a blank row.

  3. Enter Field Name, Type, Length.

  4. Save.

To Delete a Field:

  1. Open table in Design View.

  2. Right-click the field row → Delete Rows.

  3. Save.

    ⚠️ Warning: Deleting a field permanently removes all data in that field.


3.6 Table Relationships (Optional Advanced Topic)

Relationships link tables together through common fields (keys).

Types of Relationships:

1. One-to-One (1:1)

  • One record in Table A relates to one record in Table B.

  • Example: Student table ↔ Student_Address table (one student has one address).

2. One-to-Many (1:N) – Most Common

  • One record in Table A relates to many records in Table B.

  • Example: Class table ↔ Student table (one class has many students).


3. Many-to-Many (M:N)

  • Many records in Table A relate to many records in Table B.

  • Requires a junction table.

  • Example: Student table ↔ Course table (students enroll in multiple courses; courses have multiple students).

  • Junction table: Enrollment table (Student_ID, Course_ID).

Creating Relationships in Base:

  1. Tools → Relationships.

  2. Add tables to the relationship view.

  3. Drag the primary key from one table to the foreign key in another table.

  4. Define relationship type (1:1, 1:N).

  5. Enable Referential Integrity (prevents orphan records).

  6. Click Create.

    📌 Note: Relationships are an advanced topic; focus on creating single tables for Class 10 exams.


4. Working with Forms

4.1 What is a Form?

A Form is a user-friendly interface for entering, viewing, and editing data in tables. Forms make data entry easier and more intuitive than working directly with tables.

Advantages of Forms:

  • User-Friendly – Clear labels, organized layout.

  • Data Validation – Restrict input to valid values.

  • Navigation – Easy to move between records.

  • Professional Look – Customizable design with logos, colors, fonts.

  • Reduced Errors – Dropdowns, checkboxes, and input masks prevent mistakes.


4.2 Creating a Form Using Wizard

Step-by-Step Procedure:

Step 1: Select Forms

  • In the Database Pane, click Forms.

Step 2: Use Wizard

  • In the Tasks Pane, click Use Wizard to Create Form.

Step 3: Select Fields

  • Tables or queries: Choose the table for which you're creating the form (e.g., "Student").

  • Available fields: List of all fields in the table.

  • Fields in the form: Fields to include in the form.

  • Use > or >> buttons to move fields.

  • Click Next.

Step 4: Set Up Subform (Optional)

  • Add subform: Check if you want a form within a form (e.g., Student form with embedded Marks form).

  • For simple forms, leave unchecked.

  • Click Next.


Step 5: Arrange Controls

  • Choose layout:

    • Columnar – Fields arranged vertically (one below another).

    • Row-based – Fields arranged horizontally (left to right).

    • In Blocks – Fields grouped in boxes.

  • Choose alignment (left, right, center).

  • Click Next.

Step 6: Set Data Entry Options

  • Create a form to display all fields: Shows all records.

  • The form is to be used for data entry: Optimized for adding new records.

  • Click Next.

Step 7: Apply Styles

  • Choose visual style (colors, fonts, borders).

  • Preview styles on the right.

  • Click Next.

Step 8: Name the Form

  • Enter form name (e.g., "Student_Entry_Form").

  • Open form for editing: Check to customize further.

  • Click Finish.


4.3 Using a Form

To Open a Form:

  1. In the Database Pane, click Forms.

  2. Double-click the form name.

  3. The form opens in a new window.

To Add a New Record:

  • Click the New Record icon (star symbol) on the form toolbar.

  • Fill in the fields.

  • Click Save Record (disk icon) or move to another record (auto-saves).


To Navigate Records:

  • Use navigation buttons at the bottom:

    • First Record (|◀)

    • Previous Record (◀)

    • Next Record (▶)

    • Last Record (▶|)

    • Go to Record – Enter record number.

To Edit a Record:

  • Navigate to the record.

  • Click in the field and modify the value.

  • Save (auto-saves when you move to another record).

To Delete a Record:

  • Navigate to the record.

  • Click the Delete Record icon (X symbol).

  • Confirm deletion.


4.4 Customizing Forms (Design View)

To Edit Form Design:

  1. Right-click the form name → Edit.

  2. Form opens in Design View.

Common Customizations:

Add Labels:

  • Click the Label tool on the Form Controls toolbar.

  • Click on the form and type text (e.g., "Student Information Form").

  • Click the Image tool.

  • Click on the form → Browse and select image file.

  • Resize and position.

Change Colors and Fonts:

  • Right-click a control (field, label, button) → Control Properties.

  • In the Properties dialog:

    • Font tab: Change font, size, style.

    • Colors tab: Change background, text color.

    • Border tab: Add or remove borders.


Add Dropdown Lists:

  • Click the Combo Box tool.

  • Draw on the form.

  • In Control Properties:

    • Data tab: Enter list items (one per line).

    • Example:

      text
      X XI XII

Add Checkboxes:

  • Click the Check Box tool.

  • Draw on the form.

  • In Control Properties:

    • Data tab: Link to a BOOLEAN field (Yes/No).

    • General tab: Set label (e.g., "Active Student?").

Add Buttons:

  • Click the Button tool.

  • Draw on the form.

  • In Control Properties:

    • Action tab: Choose action (Save, Delete, Navigate, etc.).

Resize and Move Controls:

  • Click a control to select it.

  • Drag to move.

  • Drag edges/corners to resize.

Save Form Design:

  • Click Save (Ctrl+S).

  • Close Design View.

    🎨 Tip: Keep forms clean and organized. Use consistent fonts, colors, and spacing.


5. Working with Queries

5.1 What is a Query?

A Query is a request for specific data from one or more tables based on criteria. Queries allow you to filter, sort, calculate, and join data without modifying the original tables.

Uses of Queries:

  • Retrieve specific records (e.g., "Students with marks > 80").

  • Sort data (e.g., "List students alphabetically").

  • Perform calculations (e.g., "Calculate average marks per class").

  • Join multiple tables (e.g., "Show student names with their class teacher").

  • Update or delete records in bulk.


5.2 Creating a Query Using Wizard

Step-by-Step Procedure:

Step 1: Select Queries

  • In the Database Pane, click Queries.

Step 2: Use Wizard

  • In the Tasks Pane, click Use Wizard to Create Query.

Step 3: Select Fields

  • Tables: Choose the table (e.g., "Student").

  • Available fields: List of fields in the table.

  • Fields in the query: Fields to include in query results.

  • Use > or >> buttons to move fields.

  • Click Next.


Step 4: Set Sorting Order

  • Choose up to 4 fields to sort by.

  • Select Ascending or Descending for each.

  • Example: Sort by Name (Ascending), then by Marks (Descending).

  • Click Next.

Step 5: Set Search Conditions (Filter)

  • Choose a field to filter (e.g., Marks).

  • Choose a condition:

    • is greater than

    • is less than

    • is equal to

    • contains

    • starts with

    • ends with

  • Enter value (e.g., 80).

  • Add multiple conditions with AND (all must be true) or OR (at least one true).

  • Example: Marks > 80 AND Class = 'X'.

  • Click Next.

Step 6: Replace Field Name (Optional)

  • Rename fields in query results (e.g., "Roll_No" → "Roll Number").

  • Click Next.

Step 7: Name the Query

  • Enter query name (e.g., "Top_Students").

  • Open query for editing: Check to view results immediately.

  • Click Finish.

Step 8: View Query Results

  • Query results display in a table-like view.

  • Only records matching your criteria are shown.


5.3 Creating a Query in Design View (SQL)

Step-by-Step Procedure:

Step 1: Select Queries

  • In the Database Pane, click Queries.

Step 2: Create Query in Design View

  • In the Tasks Pane, click Create Query in Design View.

Step 3: Add Tables

  • A dialog shows all tables in the database.

  • Select the table(s) you want to query.

  • Click Add → Close.


Step 4: Select Fields

  • In the lower grid, select fields from the dropdown:

    • Field: Choose field name (e.g., Roll_No, Name, Marks).

    • Table: Auto-filled with table name.

    • Visible: Check to show in results.

    • Sort: Choose Ascending or Descending.

    • Criteria: Enter filter conditions.

Example: Show students with marks > 80, sorted by Name

FieldTableVisibleSortCriteria
Roll_NoStudentYes--
NameStudentYesAscending-
MarksStudentYes->80
ClassStudentYes--

Step 5: Run Query

  • Click the Run Query icon (red exclamation mark) or press F5.

  • Results display in a new window.

Step 6: Save Query

  • Click Save (Ctrl+S).

  • Enter query name (e.g., "Top_Students").

  • Click OK.


5.4 SQL Queries (Advanced)

LibreOffice Base supports SQL (Structured Query Language) for writing custom queries.

Basic SQL Commands:

SELECT – Retrieve Data

sql
SELECT * FROM Student;

Retrieves all fields and all records from Student table.

sql
SELECT Name, Marks FROM Student WHERE Marks > 80;

Retrieves Name and Marks for students with marks > 80.

sql
SELECT Name, Class, Marks FROM Student ORDER BY Name ASC;

Retrieves data sorted by Name (ascending).

INSERT – Add New Record

sql
INSERT INTO Student (Name, Class, Section, Marks) VALUES ('Amit Kumar', 'X', 'A', 85);

UPDATE – Modify Existing Record

sql
UPDATE Student SET Marks = 90 WHERE Roll_No = 101;

DELETE – Remove Record

sql
DELETE FROM Student WHERE Roll_No = 103;


Aggregate Functions

sql
SELECT COUNT(*) FROM Student;

Counts total number of students.

sql
SELECT AVG(Marks) FROM Student;

Calculates average marks.

sql
SELECT SUM(Marks) FROM Student WHERE Class = 'X';

Calculates total marks for Class X.

sql
SELECT MAX(Marks), MIN(Marks) FROM Student;

Finds highest and lowest marks.

GROUP BY – Group Results

sql
SELECT Class, AVG(Marks) FROM Student GROUP BY Class;

Calculates average marks for each class.

JOIN – Combine Tables

sql
SELECT Student.Name, Class.Class_Name FROM Student INNER JOIN Class ON Student.Class_ID = Class.Class_ID;

Joins Student and Class tables on Class_ID.

📌 Exam Tip: For Class 10, focus on SELECT with WHERE, ORDER BY, and simple aggregate functions (COUNT, AVG, SUM, MAX, MIN).


5.5 Query Criteria Examples

CriteriaExampleMeaning
Exact Match= 'X'Class is exactly 'X'
Greater Than> 80Marks greater than 80
Less Than< 50Marks less than 50
BetweenBETWEEN 60 AND 80Marks between 60 and 80 (inclusive)
ContainsLIKE '*Kumar*'Name contains "Kumar"
Starts WithLIKE 'A*'Name starts with "A"
Ends WithLIKE '*sh'Name ends with "sh"
Not Equal<> 'X'Class is not 'X'
Is NullIS NULLField is empty
Is Not NullIS NOT NULLField is not empty

📊 Tip: Use single quotes for text values ('X', 'Amit') and no quotes for numbers (80, 101).


6. Working with Reports

6.1 What is a Report?

A Report is a formatted, professional output of data from tables or queries, designed for printing or sharing. Reports summarize and present data in a readable, attractive format.

Uses of Reports:

  • Generate mark sheets, certificates, invoices, summaries.

  • Present data to management, teachers, or clients.

  • Archive records in printed format.

  • Share data in PDF or printed form.


6.2 Creating a Report Using Wizard

Step-by-Step Procedure:

Step 1: Select Reports

  • In the Database Pane, click Reports.

Step 2: Use Wizard

  • In the Tasks Pane, click Use Wizard to Create Report.

Step 3: Select Fields

  • Tables or queries: Choose the table or query (e.g., "Student" or "Top_Students" query).

  • Available fields: List of fields.

  • Fields in the report: Fields to include in the report.

  • Use > or >> buttons to move fields.

  • Click Next.


Step 4: Add Grouping (Optional)

  • Group records by a field (e.g., group by Class).

  • Select field → Click >.

  • Multiple grouping levels possible (e.g., Class → Section).

  • Click Next.

Step 5: Set Sorting Order

  • Choose up to 4 fields to sort by.

  • Select Ascending or Descending.

  • Example: Sort by Name (Ascending).

  • Click Next.

Step 6: Choose Layout

  • Select report layout:

    • Portrait or Landscape.

    • Columnar, Tabular, Justified.

    • Number of columns (for multi-column reports).

  • Click Next.

Step 7: Set Background and Colors

  • Choose background color or image.

  • Select color scheme for text and borders.

  • Click Next.


Step 8: Name the Report

  • Enter report name (e.g., "Student_Mark_Report").

  • Open report for editing: Check to customize further.

  • Click Finish.

Step 9: View Report

  • Report opens in a new window.

  • Data is formatted professionally.

  • Use navigation buttons to browse pages.


6.3 Customizing Reports (Design View)

To Edit Report Design:

  1. Right-click the report name → Edit.

  2. Report opens in Design View.

Common Customizations:

Add Labels/Titles:

  • Click the Label tool.

  • Click on the report and type text (e.g., "Pioneer International School – Student Mark Sheet").

  • Click the Image tool.

  • Click on the report → Browse and select logo file.

  • Resize and position.


Change Fonts and Colors:

  • Right-click a field or label → Control Properties.

  • In the Properties dialog:

    • Font tab: Change font, size, style.

    • Colors tab: Change background, text color.

    • Border tab: Add or remove borders.

Add Page Numbers:

  • Insert → Field → Page Number.

  • Position in header or footer.

Add Date and Time:

  • Insert → Field → Date or Time.

  • Choose format (e.g., DD/MM/YYYY).

Add Lines and Shapes:

  • Use Line or Shape tools to add borders, separators, or decorative elements.

Adjust Field Positions:

  • Click and drag fields to reposition.

  • Resize fields by dragging edges.

Set Page Margins and Size:

  • Format → Page.

  • Set margins, paper size (A4, Letter), orientation.

Save Report Design:

  • Click Save (Ctrl+S).

  • Close Design View.


6.4 Exporting and Printing Reports

To Print a Report:

  1. Open the report.

  2. File → Print (or press Ctrl+P).

  3. Select printer, page range, copies.

  4. Click Print.

To Export as PDF:

  1. Open the report.

  2. File → Export As → Export as PDF (or Ctrl+Shift+E).

  3. Configure options (page range, security, compression).

  4. Click Export.

  5. Choose save location and filename → Click Save.

To Export as Other Formats:

  • File → Export → Choose format (HTML, RTF, XLS, DOC, etc.).

  • Save file.

    📄 Tip: Always export important reports as PDF for sharing (preserves formatting).


7. Practical Examples and Case Studies

7.1 Case Study 1: Student Management System

Scenario:
Your school wants to maintain a database of students with their personal and academic details.

Database Objects:

Table: Student

Field NameData TypeDescription
Roll_NoINTEGER (Auto Value)Primary Key
NameVARCHAR(50)Student's full name
ClassVARCHAR(10)Class (X, XI, XII)
SectionVARCHAR(5)Section (A, B, C)
DOBDATEDate of Birth
GenderVARCHAR(10)Male/Female/Other
AddressMEMOFull address
PhoneVARCHAR(15)Contact number
EmailVARCHAR(50)Email address
MarksDECIMAL(5,2)Total marks
IsActiveBOOLEANActive student (Yes/No)

Form: Student_Entry_Form

  • All fields from Student table.

  • Dropdown for Class (X, XI, XII) and Section (A, B, C).

  • Checkbox for IsActive.

  • Logo of school at the top.

Query: Top_Students

  • Fields: Roll_No, Name, Class, Section, Marks.

  • Criteria: Marks > 80.

  • Sort: Marks (Descending).

Query: Class_Wise_Average

  • Fields: Class, AVG(Marks).

  • Group By: Class.

Report: Student_Mark_Report

  • Title: "Pioneer International School – Student Mark Sheet".

  • Fields: Roll_No, Name, Class, Section, Marks.

  • Group By: Class.

  • Sorted By: Name (Ascending).

  • School logo at the top.

  • Page numbers at the bottom.


7.2 Case Study 2: Library Management System

Scenario:
Your school library needs a database to track books, members, and transactions.

Database Objects:

Table: Books

Field NameData TypeDescription
Book_IDINTEGER (Auto Value)Primary Key
TitleVARCHAR(100)Book title
AuthorVARCHAR(50)Author name
PublisherVARCHAR(50)Publisher
YearINTEGERPublication year
CategoryVARCHAR(30)Fiction, Science, History, etc.
PriceDECIMAL(8,2)Price in rupees
IsAvailableBOOLEANAvailable (Yes/No)


Table: Members

Field NameData TypeDescription
Member_IDINTEGER (Auto Value)Primary Key
NameVARCHAR(50)Member's name
ClassVARCHAR(10)Class (for students)
PhoneVARCHAR(15)Contact number
EmailVARCHAR(50)Email address
Join_DateDATEMembership start date

Table: Transactions

Field NameData TypeDescription
Transaction_IDINTEGER (Auto Value)Primary Key
Book_IDINTEGER (Foreign Key)Links to Books table
Member_IDINTEGER (Foreign Key)Links to Members table
Issue_DateDATEDate book was issued
Return_DateDATEDate book was returned
FineDECIMAL(6,2)Fine amount (if overdue)

Form: Book_Entry_Form

  • All fields from Books table.

  • Dropdown for Category.

  • Checkbox for IsAvailable.

Query: Available_Books

  • Fields: Book_ID, Title, Author, Category, Price.

  • Criteria: IsAvailable = 'Yes'.

Query: Overdue_Books

  • Fields: Transaction_ID, Member_Name, Book_Title, Issue_Date, Return_Date, Fine.

  • Criteria: Return_Date IS NULL AND Issue_Date < (Current Date - 14 days).

Report: Library_Summary_Report

  • Title: "School Library – Book Summary".

  • Fields: Category, COUNT(Book_ID) as Total_Books, AVG(Price) as Average_Price.

  • Group By: Category.

  • Sorted By: Total_Books (Descending).


7.3 Case Study 3: Staff Management System

Scenario:
Your school wants to maintain a database of teaching and non-teaching staff.

Database Objects:

Table: Staff

Field NameData TypeDescription
Staff_IDINTEGER (Auto Value)Primary Key
NameVARCHAR(50)Staff member's name
DesignationVARCHAR(30)Teacher, Principal, Clerk, etc.
DepartmentVARCHAR(30)Science, Maths, Admin, etc.
DOJDATEDate of Joining
SalaryDECIMAL(10,2)Monthly salary
PhoneVARCHAR(15)Contact number
EmailVARCHAR(50)Email address
AddressMEMOFull address
IsActiveBOOLEANActive staff (Yes/No)

Form: Staff_Entry_Form

  • All fields from Staff table.

  • Dropdown for Designation and Department.

  • Checkbox for IsActive.

Query: Department_Wise_Salary

  • Fields: Department, COUNT(Staff_ID) as Total_Staff, AVG(Salary) as Average_Salary, SUM(Salary) as Total_Salary.

  • Group By: Department.

Query: Senior_Staff

  • Fields: Staff_ID, Name, Designation, Department, DOJ, Salary.

  • Criteria: DOJ < (Current Date - 10 years).

  • Sort: DOJ (Ascending).

Report: Staff_Directory_Report

  • Title: "Pioneer International School – Staff Directory".

  • Fields: Staff_ID, Name, Designation, Department, Phone, Email.

  • Group By: Department.

  • Sorted By: Name (Ascending).

  • School logo at the top.


8. Keyboard Shortcuts and Tips

Essential Keyboard Shortcuts in LibreOffice Base:

ShortcutAction
Ctrl+NNew database object (table, query, form, report)
Ctrl+OOpen database
Ctrl+SSave current object
F12Save As
Ctrl+PPrint
Ctrl+ZUndo
Ctrl+YRedo
Ctrl+CCopy
Ctrl+XCut
Ctrl+VPaste
F4Toggle Data Sources view
F5Run Query
F7Spell Check (in forms/reports)
Ctrl+Shift+EExport as PDF
DeleteDelete selected record (in table/form)
Ctrl+Arrow KeysNavigate within tables/forms
TabMove to next field (in forms/tables)
Shift+TabMove to previous field

Best Practices for Database Design:

  1. Plan Before Creating:

    • List all entities (tables) you need.

    • Identify fields for each table.

    • Decide primary keys and relationships.

  2. Use Meaningful Names:

    • Table names: Student, Teacher, Product (not Table1, Table2).

    • Field names: Roll_No, Name, Marks (not Field1, Field2).

  3. Choose Appropriate Data Types:

    • Use INTEGER for whole numbers, DECIMAL for currency/marks.

    • Use VARCHAR for text, DATE for dates.

    • Avoid using MEMO unless necessary (slower performance).

  1. Set Primary Keys:

    • Every table should have a primary key.

    • Use Auto Value for automatic numbering.

  2. Normalize Data (Advanced):

    • Avoid redundancy (don't repeat data in multiple tables).

    • Split large tables into smaller, related tables.

  3. Backup Regularly:

    • Copy your .odb file to a safe location (USB, cloud, external drive).

    • Use Tools → Database → Backup.

  4. Test Queries and Forms:

    • Run queries with different criteria to ensure accuracy.

    • Test forms by adding, editing, and deleting records.

  5. Document Your Database:

    • Add descriptions to fields in Design View.

    • Keep a separate document explaining table structures and relationships.


Quick Revision Points

TopicKey Points
Data vs InformationData = raw facts; Information = processed, meaningful data.
DatabaseStructured collection of related data.
DBMSSoftware to create and manage databases (e.g., LibreOffice Base).
RDBMSDBMS based on relational model (tables with relationships).
TableStores data in rows (records) and columns (fields).
FieldSingle piece of information (column).
RecordComplete set of fields for one entity (row).
Primary KeyUniquely identifies each record (e.g., Roll_No, Aadhaar_No).
Foreign KeyCreates relationships between tables.
Data TypesVARCHAR (text), INTEGER (whole number), DECIMAL (fixed decimal), DATE, BOOLEAN, MEMO.
QueryRetrieves specific data based on criteria.
FormUser-friendly interface for data entry.
ReportFormatted output for printing/sharing.
SQLStructured Query Language for writing custom queries.
SELECTRetrieve data from tables.
WHEREFilter records based on conditions.
ORDER BYSort results (ASC/DESC).
Aggregate FunctionsCOUNT, SUM, AVG, MAX, MIN.
GROUP BYGroup results by a field.

Practice Questions

Q1. Define data and information. Give one example of each.
Q2. What is a database? List any four advantages of using a database.
Q3. Define DBMS and RDBMS. Give two examples of each.
Q4. Explain the three types of data models with examples.
Q5. Define the following terms: Table, Field, Record, Primary Key, Foreign Key.
Q6. What are the four main objects of an RDBMS? Explain each briefly.
Q7. What is LibreOffice Base? List any four key features.
Q8. Describe the steps to create a new database in LibreOffice Base.
Q9. What are data types? Explain any five data types used in LibreOffice Base.
Q10. Describe the steps to create a table using the Table Wizard.
Q11. How do you create a table in Design View? Explain with an example.
Q12. What is a primary key? Why is it important?
Q13. Define a form. List any four advantages of using forms.
Q14. Describe the steps to create a form using the Form Wizard.
Q15. How do you customize a form in Design View?
Q16. What is a query? Explain any four uses of queries.
Q17. Describe the steps to create a query using the Query Wizard.
Q18. Write the SQL query to:
a) Retrieve all records from Student table.
b) Show names and marks of students with marks > 80.
c) Count the total number of students.
d) Calculate average marks.
e) List students sorted by name (ascending).
Q19. What is a report? Why are reports useful?
Q20. Describe the steps to create a report using the Report Wizard.
Q21. How do you export a report as PDF?
Q22. Explain the difference between a table, a query, a form, and a report.
Q23. What is the purpose of the WHERE clause in SQL? Give two examples.
Q24. Explain the GROUP BY clause with an example.
Q25. Create a database schema for a Library Management System with at least three tables. Define fields, data types, and primary keys for each table.


Practical Exercises

Exercise 1: Student Database

  • Create a database named "School_DB.odb".

  • Create a table "Student" with fields: Roll_No (INTEGER, Auto Value, PK), Name (VARCHAR 50), Class (VARCHAR 10), Section (VARCHAR 5), DOB (DATE), Marks (DECIMAL 5,2), IsActive (BOOLEAN).

  • Enter at least 10 student records.

  • Create a form "Student_Entry_Form" with all fields, dropdown for Class, checkbox for IsActive.

  • Create a query "Top_Students" to show students with Marks > 80, sorted by Marks (Descending).

  • Create a report "Student_Mark_Report" grouped by Class, sorted by Name.

Exercise 2: Library Database

  • Create a database named "Library_DB.odb".

  • Create three tables: Books, Members, Transactions (as described in Case Study 2).

  • Enter sample data (at least 5 books, 5 members, 5 transactions).

  • Create a form "Book_Entry_Form" with dropdown for Category.

  • Create a query "Available_Books" to show books where IsAvailable = 'Yes'.

  • Create a report "Library_Summary_Report" grouped by Category.


Exercise 3: Staff Database with SQL Queries

  • Create a database named "Staff_DB.odb".

  • Create a table "Staff" with fields: Staff_ID, Name, Designation, Department, DOJ, Salary, Phone, Email, IsActive.

  • Enter at least 10 staff records.

  • Write SQL queries to:
    a) Count total staff in each department.
    b) Find average salary by department.
    c) List staff who joined before 2020.
    d) Show staff with salary > 50000.

  • Create a report "Staff_Directory_Report" grouped by Department.

Exercise 4: Advanced Queries and Reports

  • Using the Student database from Exercise 1:

    • Create a query to calculate class-wise average marks.

    • Create a query to find the student with the highest marks.

    • Create a report showing class-wise summary (Class, Total_Students, Avg_Marks, Max_Marks, Min_Marks).

Exercise 5: Form Customization

  • Create a custom form for the Staff table with:

    • School logo at the top.

    • Dropdown lists for Designation and Department.

    • Checkbox for IsActive.

    • Custom colors and fonts.

    • Buttons for Save, Delete, Navigate.

Exercise 6: Report Design

  • Design a professional mark sheet report for students:

    • School name and logo at the top.

    • Student details (Roll_No, Name, Class, Section).

    • Marks obtained and grade (use a calculated field or query).

    • Signature lines for teacher and principal.

    • Page numbers and date.

  • Export the report as PDF.


Teacher's Note

  • Part B – Unit 3 is heavily practical—ensure students practice in the computer lab.

  • Focus on creating tables, forms, queries, and reports as they are frequently asked in exams.

  • Encourage students to create real-world databases (student management, library, staff, inventory) for better understanding.

  • Demonstrate SQL queries live in class—students find writing queries engaging.

  • Remind students to save their work frequently and organize database files properly.

  • Emphasize that LibreOffice Base is free and open-source—a valuable tool for students who may not have access to paid software.

  • Provide sample datasets for students to practice creating databases, forms, queries, and reports.

  • Assign group projects where students collaborate on designing a complete database system for a hypothetical school, library, or shop.


Final Thoughts

Mastering LibreOffice Base transforms you from a basic computer user to a database manager who can organize, analyze, and present data professionally—without spending a rupee on software. Whether you're managing student records, library books, staff details, or inventory, these skills will save you time and impress your teachers, colleagues, and future employers.

Practice regularly, experiment with queries and forms, and don't hesitate to explore Base's features on your own.


End of Part B – Unit 3 Notes: Database Management System using LibreOffice Base
CBSE Class 10 IT (Code 402) | Aligned with 2026–27 Syllabus


About the Author:
These notes are created by Ravindra Khillare, a Computer Science Teacher at Pioneer International School, Pune. With a focus on CBSE IT 402 curriculum, these resources aim to support students, teachers, and educators in mastering essential database management skills using free and open-source tools.

Feel free to share, adapt, and use these notes for classroom teaching, blog posts, or student handouts. For queries or suggestions, connect via your school's IT department or educational networks.



Comments