Transcript Document

Database
Ed Milne
Theme
An introduction to databases
Using the Base component of LibreOffice
Database
A database is a structured set of data held in a computer
SQL
Structured Query Language (SQL) is a programming language used to create, maintain and
It is an ISO standard
e.g. SELECT isbn, title, price, price * 0.14 AS sales_tax FROM book WHERE price > 100 OR
Base
Base, a component of the LibreOffice suite is a front end to a database
The back end, the database engine, can be
A spreadsheet (Calc)
HSQL – open source freeware embedded in base
External sophisticated database engines such as PostgreSQL, MySQL and MariaDB
Database Engines
The appropriate database engine depends on the performance of your computer and the am
As a rough guide
Spreadsheets – up to 1,000 records
HSQL – up to 50,000 records
MYSQL etc. - over 50,000 records
Database Engines
Using HSQL creates a database in a single file
Using other engines creates two objects
The Base front end
The DB engine containing the data
This can be useful when more than one person can be accessing the data at the same time
In an organization, the engine can be on a server and a copy of the front end is on each
Database Engines
Sophisticated engines prevent two people from changing the same record at the same time
Separate engines also make maintenance easier
Changes to the front end can be copied to each user
e.g. New forms or reports
Changes to the engine only have to be made at a single location
Database Engines
A separate front end and engine is also used on the web
e.g. When you register at a web site, the form on the web page is the front end of a dat
Tables
The data in a database is organized in tables which look much like a spreadsheet
LibreOffice has a wizard for creating tables
Fields
A table consists of fields
Each field is the equivalent of a column in a spreadsheet
Field Data Type
Each field can only accept a specific type of data
cf. a spreadsheet where you can enter any type of data in any cell
Integer
Big integer – up to 19 digits
Integer – up to 10 digits
Small integer – up to 5 digits
Tiny integer – up to 3 digits
Field Data Type
Numbers
Decimal – 10 digits
Fixed number of decimal places
Float, Real, Double
17 digits
e.g. 1.23E+017
Text
Text – only uses the space required for the data
Text (fix)
Text (ignore case)
Long text (memo) – up to 2,147,483,647 characters
Field Data Type
Date and Time
Date
Time – HH:MM:SS
Timestamp – date and time
Boolean
Yes/No
Field Data Type
Binary - up to 2,147,483,647 bytes
Image
Binary
Binary (fix)
Other
Up to 2,147,483,647 bytes
Indexes
Each table must have a primary index to uniquely identify each record
cf. The row number in a spreadsheet
The integer data type has an autonumber feature which automatically creates a unique
Relations
You can relate tables together by
Adding a field for the primary index in another table to a table
Linking these fields together
One-to-Many Relationship
In the example shown, each media record can be linked to many books
Many-to-Many Relationship
A many-to-many relationship can be made using an intermediate table
In the example shown,a book can have more than one subject and a subject can relate
Views
You can create a View of a table which contains
a subset of the fields in a table
a set of fields from related tables
A view is a virtual table which can be used much like a table
Forms
Forms let you add or edit the information in a database
LibreOffice has a wizard for creating forms from a table or view
Forms
The forms editor is the word processing component of LibreOffice
After creation, you can modify the form
Filters
You can filter the data on a form so only records with certain values appear
e.g. You can filter the data in a book form so that only records with the medium “Magaz
Datasheet Form
A datasheet form appears like a spreadsheet
Subforms
Subforms are forms within a form
Typically a datasheet form within a field form
Form Controls
Check box
Text box
Numeric field
Currency field
Date field
Time field
Pattern field
Spin button
Push button
Option button
Image button (icon)
List box
Combo box
Label
Image
Scroll bar
Frame
Navigation bar
Check Box
A check box accepts a Boolean (Yes/No) value
Currency Control
Accepts a currency value
The spin box control on the right increments or decrements the value by $1
Date and Time Controls
There are various ways to format the date and time
The large down arrow displays a calendar
The spin buttons change the value selected by the position of the cursor
E.g year, month or day
Dropdown Menus
Lets you select a value from
a related table
A list of constants embedded in the form
Dropdown Menus
A list box saves the index of the record in the related table in the primary record
A combo box saves the displayed text in the primary record
Either control can save the selected value in the form rather than the record
Buttons
Buttons can be used to
execute a macro for the form
set a value in the record
Macros
Macros are subroutines in the programming language used by the application
In this case OpenOffice Basic
You can create macros by
1Learning the programming language and writing the code
2Recording a macro
3Searching the web
4From books
Macro Example
Executing Macros
You can execute a macro by
Using the Run Macro option in the menus
Assigning a shortcut key to the macro
Linking the macro to an event for a form or control
Form Events
Push Button Events
Queries
Queries let you select, organize and manipulate data from the database
Queries are used to select and organize the fields for
dropdown menus
reports
Queries
Base provides a Graphical User Interface (GUI) for select queries to
Select fields
Determine which fields should be visible
Select records with specific values in a field
Sort the extracted data
Apply functions like Count or Average to the fields
Queries
The GUI generates SQL code like
Queries
You can use other queries as well as tables as the data source
i.e. You can created nested queries
In Base, you hand code other types of queries in SQL
e.g. An Update query to make a mass change to the data
UPDATE "tblSubjectWork" SET "SubjectID" = '5' WHE
Reports
Base has a report generator to create reports from queries or tables
Reports can be
Grouped
Group headers and footers
Sorted
Output to text documents or spreadsheets
Reports
Database for Spreadsheets
Why use a spreadsheet as the back end of a database?
You already have the data in a spreadsheet
You want to use some features of a spreadsheet like charts and graphs
A database allows strict data typing and limits
r.g. A value must be present and within a specific range
Forms avoid horizontal scrolling
Database reports
References
LibreOffice Base Guide
Base Tutorial