Introduction to LibreOffice Base Basics

Overview

• This unit covers the basics of DBMS (Database Management System), data models, relationships, queries, forms, and reports.
• Includes examples, definitions, advantages, key terms, and practical steps in LibreOffice Base.

Data, Information, Database and DBMS

• Data: Raw facts/numbers (e.g., 25, 45, January) that have no meaning by themselves.
• Information: Processed data from which decisions can be made.
• Database: Organized collection of data, stored/managed electronically.
• DBMS: Creating, inserting, modifying, deleting databases; planning and maintenance (e.g., MySQL, LibreOffice Base).

Benefits of DBMS

• Easy data retrieval; sharing across multiple applications possible.
• Reduces duplication; saves memory; improves consistency and accuracy.
• Validation rules prevent invalid entries (e.g., age > 0).
• Access control, passwords, encryption ensure security.

Data Models

• Hierarchical Model: Tree structure; One-to-One/One-to-Many, no multiple parents for a child.
• Network Model: Graph structure; node=record, edge=relation; Many-to-Many possible.
• Relational Model: Multiple tables linked by common fields; most widely used.

Relational Model: Terms and Structure

• Entity: Real-world object (e.g., Student).
• Attribute: Qualities of entity (age, height, marks).
• Table: Collection of rows+columns; fields=columns, records=row/tuple.
• Field: Smallest unit; one data type; cell value = entered data.

Types of Keys

• Super Key: Set of columns that uniquely identify every record.
• Candidate Key: Minimal super key; cannot be reduced further.
• Primary Key: Chosen candidate key; unique, non-null; can be composite.
• Alternate Key: Candidate keys not chosen as primary.
• Foreign Key: Refers primary key of another table to link.

DBMS Objects

• Tables: Store data.
• Queries: Retrieve/update/delete data, sort/filter.
• Forms: User-friendly data entry interface.
• Reports: Formatted, analyzable presentation of data.

Data Types (LibreOffice Base)

• Text: Letters, digits, symbols; no math operations; Longvarchar, Char, Varchar.
• Numeric: Numbers; TinyInt (0–255), Integer, Decimal.
• Currency: Monetary values.
• Date/Time: Date/time.
• Boolean: Yes/No, True/False, 0/1.
• Binary: Data based on 0/1 strings.

LibreOffice Base: Interface and Basic Operations

• Title bar, Menu bar (7 menus), Standard toolbar, Status bar.
• Database pane: Tables, Queries, Forms, Reports; related Task pane and Object area.
• Open Database: File > Open or Ctrl+O.

Creating and Managing Tables

• Using Wizard: Create Table using Wizard > choose template > fields/data types/properties > Primary Key > Finish.
• Design View: Field Name, Field Type, Description; Entry Required, Default; set Primary/Composite Key; Save (Ctrl+S).
• Data Entry/Edit: Datasheet view; move using navigation box; Esc cancels changes.
• Delete Record: Select record > Delete / Delete Row from menu.
• Sorting: On single/multiple fields A→Z, Z→A or via Sort dialog.
• Modify/Rename/Delete Table: Right-click table name > Edit/Rename/Delete.

Table Relationships

• Master (Reference) Table: Table with Primary Key.
• Transaction (Referencing) Table: References via Foreign Key.
• Types:
  • One-to-One: One record ↔ one record (Citizen–Aadhaar).
  • One-to-Many: One ↔ many (Department–Employees).
  • Many-to-Many: Many ↔ many (Student–Courses).
• Benefits: Reduces duplication, cascade update/delete possible, prevents invalid data, saves time/effort.
• Setup: Tools > Relationships > Add Tables > drag link common fields; indicates 1 and n.

Referential Integrity

• Principle: Foreign Key values in transaction table must exist in master.
• On delete/update in master, appropriate handling needed in transaction.
• Options:
  • No Action: Master delete/update disallowed if related records exist.
  • Update/Delete Cascade: Related records also updated/deleted.
  • Set Null: Related FK fields set to null.
  • Set Default: Related FK fields set to default value.
• Click relation line > Relations dialog > choose options.

Queries in Base

• Purpose: Retrieve, sort, filter data from one/multiple tables; results tabular.
• Methods: Wizard, Design View (SQL View not covered in this chapter).

Query: Using Wizard

• Queries > Use Wizard to Create Query.
• Select fields, sorting, conditions (e.g., CategoryID = C001), set alias, name, choose Display/Modify, Finish.
• Example: From Events table, Event Name, Winner where CategoryID C001.

Query: Using Design View

• Queries > Create Query in Design View > Add Tables.
• Add fields; set Visible, Alias, Sort.
• Criteria row for conditions (>, <, = etc.).
• Run (F5), Save; Right-click > Edit to modify.

Numerical Functions in Queries

• Functions: COUNT, SUM, MAX, MIN, AVG.
• Grouping: Function on a field = Group; aggregate on another field (e.g., AVG Points by Category).

Forms in Base

• Use: User-friendly data entry; each field control has Label and Value Text Box.
• Create (Wizard): Forms > Use Wizard to Create Form > select table > fields > subform options > layout (label/text box arrangement) > data mode (show existing/add/modify/delete permissions) > style/color > name > Finish.
• Design modifications:
  • Background Color: Edit > Page Styles > Area Color.
  • Label/Edit: Ctrl+Click > Control Properties > Label Field.
  • Move Controls: Ctrl+Click > Drag.
  • Resize Text Box: Ctrl+Click > drag corner handles.
  • Tooltip/Help Text: Control Properties > Text properties > Help text.
• Add Date Picker:
  • Date Field Properties > Date Format (Standard long) > Dropdown = Yes; calendar arrow appears on field.
• Add Title/Headings:
  • Report/Form Controls > Label > draw > Properties > set Text/Font.
• Working in Form View:
  • Turn design mode off > Add Record button to add new record; Save.
  • Delete Record button to remove.

Reports in Base

• Purpose: Show query/table data in custom, attractive presentations.
• Create (Wizard): Reports > Use Wizard to Create Report > select table/fields > change labels > grouping (optional) > sorting > layout and orientation > type (dynamic/static) > name > Finish.
• Other controls:
  • Title/Headings: Report Builder > draw Label > set Text/Font in Properties.
  • Date/Time: Page Header > Insert > Date and Time > set format > adjust position.

Key Terms & Definitions

Action Items / Next Steps

• Memorize DBMS definitions, benefits, keys and relationship conditions.
• Practice steps for tables, relations, queries, forms, reports in LibreOffice Base.
• Practice query Criteria, Sorting, Alias and Aggregate Functions.
• Practice adding form/report controls (Label, Date Picker, Tooltip).