Base Tutorial: From Newbie to Advocate in a one, two three!

189 Pages·2010·1.07 MB·English

by  Mariano Casanova

Base Tutorial:
From Newbie to Advocate in a one, two... three!

B T :
ASE UTORIAL
From Newbie to Advocate in a one, two... three!
Step-by-step guide to producing fairly sophisticated
database applications with OpenOffice.org Base,
from initial problem to final product complete with
forms and reports.
by Mariano Casanova
Base Tutorial: From Newbie to Advocate in a one, two... three!
By Mariano Casanova
Copyright © 2010 Mariano Casanova. All rights reserved.
First Edition: August 2010
Second Edition: September 2010
All names of products and companies mentioned in this text
are the trademark of their respective owners and are mentioned
here with no intention of infringement and for the benefit of
those respective owners.
Please note that the author can not provide software support.
Please contact the appropriate software developers of Base or
HSQL at: www.openoffice.org and www.hsqldb.org or their
fantastic fan base and forum experts.
The author has taken every precaution possible to ensure the
correctness and appropriateness of the information provided in
this text, including the testing of the code supplied. However,
due to possible human or mechanical error from the sources,
the constant changing and evolution of the software described
and known and unknown issues in the code, its functioning and
compatibility, the author can assume no responsibility for er-
rors or omissions or for damages resulting from the use of the
information provided here. The author does not guarantee the
accuracy, adequacy or completeness of this information and
shall not be liable to any person, legal or natural, with respect
to any loss or damage caused or allegedly caused directly or
indirectly by the use of such information, including but not
limited to, business interruption, loss of profits or loss of data.
The information is provided "as is" with no warranties whatso-
ever of its appropriateness or fitness for any purpose. You use
this information at your own risk.
This digital edition can be distributed under the terms of the Creative Commons Attribution Non-Commercial Share Alike
license, as described in:
License, full text: http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
License, summary: http://creativecommons.org/licenses/by-nc-sa/3.0/
You can copy this electronic file and distribute this electronic file with no limitation for all non-commercial use. You can ad-
apt, expand or translate this work as long as you: a) Attribute the work by providing the name of the initial author and a link
to the original work or, if such link is not available, a reference to the source of your copy of the original work. Your attribu-
tion must not suggest that the initial author endorses you or your use of the work. b) Clearly state the nature and scope of
your contribution to the work. c) Distribute this work under the same or similar license and ensure that all derivatives will
also be non-commercial in nature. You can not use this work or its derivatives for commercial purposes.
Base Tutorial OOo.
To everyone involved in the creation, distribution
and documentation of free and open software
And in particular to the developers, writers and
administrators at OpenOffice.org
~Thanks!

FROM NEWBIE TO ADVOCATE IN A ONE, TWO... THREE!
Content overview:
Part I: Things you need to know before you create a database with Base.
Where we introduce this tutorial and its scope. We start by analyzing what a database is
and describe its different components: Tables, relationships, primary and foreign keys,
columns, column attributes, deletion attributes and relationship cardinalities and finally
we provide a definition of database and Base database. We later comment on forms and
reports and on modeling data and goals of proper design, after which we provide an over-
view of UML diagrams to use as a visual vocabulary. We then summarily review phases
in database design and the importance of Normal Form. We also review First, Second and
Third normal forms and how they aid in class extraction. After this we review the way
that Base records attributes and the nature of the variables it uses, analyzing text vari-
ables, numeric variables, date and time variables and object variables and how choosing
them properly will affect the performance of a Base application.
Part II: Things you need to do before you code a database with Base.
Where we offer a real case example and, using the elements presented in part I, we start
with a problem and end with a database design. We cover class formation, class extrac-
tion, the importance of atomization and descriptors. We apply normalization and other
tools to decide on data structure and finally come up with a complete UML diagram of
our database. We then stress the importance of using auxiliary elements like a Variables
Definition List for the aid they provide in coding our application with Base. After this we
analyze the problem of duplicity of roles in order to introduce the concept of super-class
formation. We then analyze the forms and reports that we will use in conjunction with the
design we have produced and describe the kind of features we will want them to have.
Now that we understand how to design our tables, its connections, forms and reports, and
what do the options that Base offers mean, we are ready to sit down in front of Base and
start coding our database.
Part III: Things you need to do while coding a database with Base.
Where we describe how to use Base to create the database it took us so long to design,
complete with forms and reports. We star by analyzing the setup and explain how to use
SQL commands for the creation of tables and relationships, analyzing the “Create Table”
and “Constraint” instructions in detail. We also review how to read their descriptions
when consulting the Wiki or other sources. Then we describe in depth how to create
forms to populate our tables and how to use radio buttons, sub-forms, list boxes and other
widgets to simplify data entry. We later take our time to analyze the “Select” command to
produce queries and extract information from our data and finally explain in detail how to
produce the reports using both the Report Wizard and the SUN Report Builder.
i
BASE TUTORIAL
Table of Contents
Introduction:....................................................................................................................v
Acknowledgments.........................................................................................................vii
Part I: Things you need to know before you create a database with Base.
Chapter 1..............................................................................................................................3
Introduction to databases.................................................................................................3
Anatomy of a database: Tables, attributes and relationships......................................3
Establishing relationships within tables.....................................................................7
Cardinality and optionality of a relationship..............................................................8
Managing relationships with column and delete options.........................................10
A definition of database and database design................................................................11
Talking about forms and reports....................................................................................12
Modeling data and goals of proper design....................................................................13
Visual vocabulary..........................................................................................................14
Phases in database design..............................................................................................16
Chapter 2............................................................................................................................19
Getting into normal form..............................................................................................19
First normal form......................................................................................................20
Second normal form.................................................................................................20
Third normal form....................................................................................................21
Aiming for simplicity....................................................................................................23
Chapter 3............................................................................................................................25
Recording attributes: Are you my variable?..................................................................25
Why is all this information relevant?............................................................................30
On logical Names and Physical Names........................................................................31
Part II: Things you need to do before you code a database with Base.
Chapter 4............................................................................................................................35
Let's get real!.................................................................................................................35
Case Example: Now we need a problem.......................................................................35
Possible solution............................................................................................................39
Data Modeling...............................................................................................................40
A little bit more: Duplicity of roles and super classes..................................................52
Attributes/Variable definition lists................................................................................54
Chapter 5............................................................................................................................57
The forms......................................................................................................................57
Chapter 6............................................................................................................................63
The reports....................................................................................................................63
ii
FROM NEWBIE TO ADVOCATE IN A ONE, TWO... THREE!
Turn on the computers...................................................................................................68
Part III: Things you need to do while coding a database with Base.
Chapter 7............................................................................................................................71
Creating tables in Base with SQL.................................................................................71
General Overview:........................................................................................................71
Creating tables and relationships with SQL commands................................................72
What SQL window are you, anyway?...........................................................................82
Chapter 8............................................................................................................................85
Producing our forms......................................................................................................85
The Form Wizard..........................................................................................................86
Design View and the Properties dialog box..................................................................88
Radio Buttons................................................................................................................90
Tab Stops (and Radio Buttons)......................................................................................92
Forms with Sub Forms..................................................................................................93
Drop Down lists............................................................................................................95
List Boxes with compound fields..................................................................................97
Default values..............................................................................................................100
Entering time and date................................................................................................100
Forms with two or more sub forms.............................................................................103
Chapter 9..........................................................................................................................111
Producing Queries.......................................................................................................111
SQL queries with Base................................................................................................112
Built-in Functions in HSQL........................................................................................114
Saving and Calling a Query.........................................................................................115
Where?.........................................................................................................................116
Compound queries.......................................................................................................117
Order in the room, please!...........................................................................................118
User defined parameters..............................................................................................118
Querying more than one table at a time......................................................................120
Aggregate Functions:..................................................................................................124
Some time functions:...................................................................................................131
Chapter 10........................................................................................................................133
Creating reports with Base..........................................................................................133
Our first Report with Base:.........................................................................................135
Steps in designing a report:.........................................................................................141
Creating a report with Report Builder (that is not in tabular form)............................141
Analyze the report's requirements and decide on the overall layout......................143
Select needed tables and columns..........................................................................143
Compose query.......................................................................................................143
Using the SUN Report Builder....................................................................................145
SUN Report Builder overview................................................................................145
iii
BASE TUTORIAL
Building a report with the SRB..............................................................................147
Using formulas and functions with SRB.....................................................................150
Using Formulas......................................................................................................151
Using Functions......................................................................................................154
The Report Navigator..................................................................................................159
Custom made functions...............................................................................................162
Conditional formatting................................................................................................165
A word of caution:.......................................................................................................166
Chapter 11........................................................................................................................167
Maintenance of a Database.........................................................................................167
Modifying data structure........................................................................................167
Modifying forms.....................................................................................................168
Defragmenting your Database:...............................................................................170
Backups:.................................................................................................................171
Chapter 12........................................................................................................................173
Some final words:.......................................................................................................173
iv