OpenOffice.org/Base

OpenOffice User's Manual:
OpenOffice.org Base

OpenOffice.org Base, is a relational database management system, a competitor to Microsoft Access and even suitable for beginners to databases. Much documentation and books for Microsoft Access will apply similarly (but not necessarily identically) for OpenOffice.org Base. It is a member of the OpenOffice.org suite of applications. OpenOffice.org users can choose to connect to external full-featured SQL database such as MySQL, PostgreSQL and even Oracle through ODBC or JDBC drivers. OpenOffice.org Base can hence act as a GUI frontend for SQL views, table design and query. In addition, OpenOffice.org has its own Form wizard to create dialog windows for form filling and updates.

Base may not be included with your operating system, but you should be able to get it relatively easily, as it is free software. You will also need Java installed (and not disabled in OpenOffice.org) in order for Base to run.

Data

A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model. The model in most common use today is the relational model.

A database management system (DBMS) is computer software that manages databases. DBMSes may use any of a variety of database models, such as the network model or relational model. In large systems, a DBMS allows users and other software to store and retrieve data in a structured way.

Connecting to Databases

If you have an existing database already created, you can connect to it either with the first screen you see in OpenOffice.org Base or by clicking File, New, Database then Connect to existing Database. There is limited support for Microsoft Access databases.

Relations

Imagine you want to record the products that customers order from your company. You could use a spreadsheet with one customer per row, but one customer might order many products. You could use a spreadsheet with one order per row, but you may have to type in customer name and address repetitively for many orders. In situations such as this, you are better off using a database rather than a spreadsheet.

The difference between databases and spreadsheets is that while spreadsheets can contain records (often a row in a spreadsheet) and data fields (which is just a particular type of data - often a column header in a spreadsheet), databases can also contain relationships between records. These can be one-to-many relationships or many-to-many relationships.

When you specify the relationships between tables you can also specify referential integrity so that data can't be entered which contradicts related data in another table.

Wizards

Wizards are simply a way of describing a step-by-step approach in making software easier for the user, usually choosing an option and clicking Next. On launching Base you are presented with a Database Wizard which offers you the choice of creating a database, opening or connecting to an existing one. Choose Create a Database to begin with and click Next. Choose to register the database, don't worry this is only done locally and can help other modules of OpenOffice.org (such as Calc) work with your database. Don't choose the Table wizard just yet and save (and name) your database in a convenient location. For now, use OpenDocument Database format, though this will probably be already automatically selected as the default. Why save it already? Because data such as records will be saved automatically as it is being entered into a database.

Databases do not use files in the normal sense, however a good database can output its content structured with SQL (Structured Query Language) – an ANSI/ISO standard. It is also important that it supports ODBC (Open Data Base Connectivity)

You should now be presented with the main interface. On the left is the Database column which contains four options (sometimes called modules); Tables, Queries, Forms and Reports.

Tables

A table will be the closest aspect of database to resemble an ordinary spreadsheet. Click on tables (it may already be selected) and you will be presented with a choice of Tasks (in the upper part); Create Table in Design View, Use Wizard to Create Table and Create View.... Choose Use Wizard to Create Table. There are four steps Select fields, Set types and formats, Set primary key and Create table. Choose a category Business or Personal then choose a Sample table then click the double arrows >> which will copy all the fields into Selected fields on the right. Click next and you can adjust the field characteristics. Click next and make sure Create a primary key is checked and Automatically add a primary key. Click next and the table name should read whatever Sample table you chose. Ensure Insert data immediately is checked and click Finish. Congratulations, you have created your first table. You should now be able to see it looks rather like a spreadsheet. There is not yet any data in it, it is usually better to add data using Forms so close it for now. You will notice that although you have not yet clicked to save it, the table has now automatically been saved. You can see it when you return to the main window and even if you close and reopen the database.

Design View

Creating a table in design view is a more specific way of creating a table. Click on it and you will see a spreadsheet-like layout but this might be deceptive (don't confuse it with the table view). The left column is for the Field name which you choose (you can choose anything, this will be for your reference). The second column is for Field Type and contains a drop-down menu for the type of data that will be stored in this field. For example a name would be Text [VARCHAR], a date would be Date [DATE] and so on. When the Field type is selected, you will see a window at the bottom which is the Field Properties where can you specify things like whether you require data to be entered into that field type when logging records, the permitted character length of the data and decimal places. You can specify validation here which checks that data entered is among the permitted data. Unlike in the Table wizard you will need to save any changes in Design view that you want to keep, before closing it to return to the main window.

Forms

Forms are used to enter data easily and quickly. Click on Forms on the left hand side of the screen (in Database column) and Use Wizard to Create Form... (Writer may briefly pop up but also the Form Wizard should be on top). Click on the double arrow >> to add all fields to the right hand column called Fields in the form, and then, (unless you want to change the layout and colors) just click Finish. To build a query for the data, first close the Form in the Writer window.

Controls are aspects of forms to configure each fields particular properties. The form is edited in a Writer window and the Form controls are accessed in the same way as in Writer, View, Toolbars then Form Controls.

Basic switchboards (as in Microsoft Access) can only be created in OpenOffice.org using Forms.

Queries

Queries are a way of querying your data according to certain conditions or criteria. Complex calculations can be performed with queries.

Note: There are two modes of queries they can run in: Native and normal. In normal mode the query is processed by the gui that rewrites it for the specific SQL engine you use. In Native mode it does not. In Native mode you can use server-specific commands, in normal mode you can't.

Reports

Reports are like queries but offer more layout options for outputting your query results. In the Report Wizard, you are offered to group your results, then you are offered to sort the results. You can change the formatting and set page breaks too.

External links

General Database Documentation