Database

1. Database Ed Milne

2. Theme • An introduction to databases • Using the Base component of LibreOffice

3. Database • A database is a structured set of data held in a computer

4. SQL • Structured Query Language (SQL) is a programming language used to create, maintain and extract data from databases • It is an ISO standard • e.g. SELECT isbn, title, price, price * 0.14 AS sales_tax FROM book WHERE price > 100 ORDER BY title

5. 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

6. Database Engines • The appropriate database engine depends on the performance of your computer and the amount of data in the database • As a rough guide • Spreadsheets – up to 1,000 records • HSQL – up to 50,000 records • MYSQL etc. - over 50,000 records

7. 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 employee's computer

8. 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

9. 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 database

10. Tables • The data in a database is organized in tables which look much like a spreadsheet • LibreOffice has a wizard for creating tables

11. Fields • A table consists of fields • Each field is the equivalent of a column in a spreadsheet

12. 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

13. 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

14. Field Data Type • Date and Time • Date • Time – HH:MM:SS • Timestamp – date and time • Boolean • Yes/No

15. Field Data Type • Binary - up to 2,147,483,647 bytes • Image • Binary • Binary (fix) • Other • Up to 2,147,483,647 bytes

16. 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 number for the record

17. Relations • You can relate tables together by • Adding a field for the primary index in another table to a table • Linking these fields together

18. One-to-Many Relationship • In the example shown, each media record can be linked to many books

19. 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 to many books

20. 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

21. Forms • Forms let you add or edit the information in a database • LibreOffice has a wizard for creating forms from a table or view

22. Forms • The forms editor is the word processing component of LibreOffice • After creation, you can modify the form

23. 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 “Magazine” appear as you browse through the records

24. Datasheet Form • A datasheet form appears like a spreadsheet

25. Subforms • Subforms are forms within a form • Typically a datasheet form within a field form

26. 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

27. Check Box • A check box accepts a Boolean (Yes/No) value

28. Currency Control • Accepts a currency value • The spin box control on the right increments or decrements the value by $1

29. 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

30. Dropdown Menus • Lets you select a value from • a related table • A list of constants embedded in the form

31. 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

32. Buttons • Buttons can be used to • execute a macro for the form • set a value in the record

33. Macros • Macros are subroutines in the programming language used by the application • In this case OpenOffice Basic • You can create macros by • Learning the programming language and writing the code • Recording a macro • Searching the web • From books

34. Macro Example

35. 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

36. Form Events

37. Push Button Events

38. 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

39. 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

41. Queries • The GUI generates SQL code like

42. 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' WHERE "SubjectID" = '7'

43. 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

44. Reports

45. 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

46. References • LibreOffice Base Guide • Base Tutorial