ICDL Module 5

A database is a collection of related information which is organised into a series of rows (called records) and columns (called fields) that are populated with data. A well constructed database should make it very easy to manage information quickly and effectively.

Data is the raw information that is entered and makes up the records in the tables while the resulting information is the processed data.

For example, the conversion of data to information takes place when a number of records are retrieved according to certain criteria and calculations are performed or the sequence of displaying them is changed to output in a certain way.

Databases comprise of many different components which are constructed from tables of information which contain numerous records. In turn each record contains many fields pertaining to that record.

For example, in a simple database for a small business, you would want to record information pertaining to various aspects of the business such as customer, supplier and product details. You could start by creating a table called Customers and entering information into it such as the Customer's Name, Address, Telephone No, Fax No etc. Each row of information (record) would be all the details belonging to that particular customer. The fields making up each record would be Customer's Name, Address , Telephone No, Fax No.

Each field can have a different data type specification, eg the Customer's Name field could be defined as a Text field type while the Telephone No could be defined as a numerical field.

Each field can also be assigned properties such as the maximum length of characters it must contain and whether it is compulsory to enter data into it before the capturer is allowed to continue.

A primary key is used to identify the records in the table and must be unique for each record. You can choose whether you want to make a specific field the primary key or whether you want a separate ID field created. You can also choose whether you want to autogenerate the number or type it in manually for each record.

An index allows quick access to a database as long as you query the data in the selection that was defined through the index. When you design a table you can define the indexes on the Indexes tab page. You can do this by Clicking Tools > Index Design and then click on New Index.

There are two types of databases – flat file and relational.

Flat file databases store all the information in one table. This type of database is easy to set up, but is not very flexible or efficient when it comes to storing and manipulating large amounts of data.

Relational databases store information in a number of separate tables and then relationships are created between the tables to combine the information. Relational databases are used for managing large amounts of information as they are faster and easier to maintain than the flat file database.

It is useful to be able to set rules when entering data to ensure the correct data is entered.

For example, you can specify the Postal Code field to be only four characters long. So if the data capturer typed in more than three, OOBase will not allow it. Another example is the Name field. You could set a rule ensuring that all data entered in this field is text and not in a numerical format. This helps ensure that the capturer doesn't insert data in the wrong field.

It is important to set rules in the Design View of the table to make sure that the important data is captured properly and the risk of errors is reduced. There may be many different people capturing the data into the database.

To open an existing database click on File > Open and click on the database you want to open (Figure 1).

Figure 1.1. File Open

To close the database click on File > Close (Figure 2).

Figure 1.2. Close Database

You will be prompted to save any unsaved data. Be careful which option you choose (Figure 3).

Figure 1.3. Save/Discard

In 5.1.2.2 we looked at opening an existing OOBase database. In this section we are going to look at how to connect to an existing database, eg MySQL.

Click on File > New. (Figure 4)

Figure 1.4. Connection to MySQL DB screen 1

Once you have clicked on the option you want click on the Next button which will take you to screen 2 (Figure 5).

Figure 1.5. Connection to MySQL DB screen 2

	This screen will differ depending on which Database type you selected in screen 1. In this example we selected MySQL, but there are others available. The other options are : Adabas D, Dbase, Evolution Address Book, JDBC, LDAP Address Book, Mozilla Address Book, ODBC, Oracle JDBC, Spreadsheet, Text.

Choose how you want to connect your database and then click Next.

Figure 1.6. Connection to MySQL DB Screen 3

You need to enter the name of the database and the server URL. Note that to connect using JDBC a JDBC driver class must be installed on the system and registered with OpenOffice.org (Figure 6). Click Next.

Figure 1.7. Connection to MySQL DB Screen 4

You then need to enter the user name if this is applicable (Figure 7) and click on Finish to save and proceed (Figure 8).

Figure 1.8. Connection to MySQL DB Final Screen

To create a new database click on File > New Database (Figure 4).

You need to save regularly. When you create your database initially you will be asked to choose a filename and location. After that you can simply use the File > Save or click on the save button on the toolbar to update the database.

OpenOffice.org Base comes with a Help feature which is accessed by clicking on Help on the toolbar and then clicking on OpenOffice.org Help.

Click on File > Close. See above in 5.1.2.1

FIX ME

You can display or hide the built-in toolbars by clicking on View > Toolbars from the menu bar. If there is a tick next to the toolbar it means it is visible. If you want to hide it click on it to remove the tick.

The first step to a database is to create the tables that are needed. You can design a table from scratch or you can activate the Table Wizard which allows you to choose formats that have been pre-defined and are available to use.

To do this click on the Tables icon on the left hand side of the screen (Figure 9).

Figure 2.1. Tables

If you choose to Create Table in Design View, the following screen will appear (Figure 10).

Figure 2.2. New Table in Design Mode

Here you enter the field names underneath each other as per the example in Figure 10. The green arrow shows the active row. You can specify a Field Type in the next column which can be either a Text field or a Date, Time, Number, various types of Integer etc. If you do not specify a Field type it will default to Text.

If you look down at the Field Properties below you will see other options that are available, eg if Entry is required for that field. This is so that you can force the person who will be capturing the data into the table to enter information. If no information is entered you will not be allowed to continue until something has been entered.

Once you have decided on the fields that a table will contain you will need to enter the records. In a table containing information about customers names and addresses each customers information is one record. For example.

This is one record for the customer JD Wholesalers – it contains all details pertaining to that company. The record is made up of five fields, Name, Address, Town, PostalCode, TelNo. Of course this is an example so you would probably have more fields than what have been used here.

Once you have entered this information you will have entered a record.

To enter the information click in the cell below the first field (as you would when entering data into a spreadsheet). Type into the cell. Move to the next cell by using the arrows on the keyboard or by using your mouse and enter the information for each field until it is complete.

Once you have created a table you may find that you wish to add another field at a later stage. This you do by right-clicking on the table name and choosing Edit. If you want the field at the end then simply click in the row underneath the last field and enter the new field. Click on the Save button to save the change. If you forget to save the table you will prompted to do so when you close it.

If you want the new field to appear within the current fields, ie not at the end, then click on the grey button to the left of the field that you want to be after the new field and right click. A pop up menu will appear allowing you to choose to insert rows (Figure 11). Click on Insert Rows and you have a new row in which to enter your field.

Figure 2.3. Table PopUp Menu

Once the records have been entered into the table it may be necessary to change it. Right-click on the table name and choose Open.

Click on the field you want to change and type in the new information. (Figure 12)

Figure 2.4. Update an existing record

To delete a record right click on the table name and choose Open. Click on the grey button next to the record you want to delete and right-click to activate the pop up menu. Click on Delete and the record will be deleted.

If you make a mistake you can click on the Undo button or you can click on Edit > Undo Data Input.

You can navigate within the table to various records. There are two ways of doing this. By using the Find Record button on the toolbar

or by using the Table toolbar at the bottom of the screen (Figure 13)

Figure 2.5. Navigate to another record

If you click on the first arrow with the line to the left of the arrow it will take you to the first record. The button next to that (left arrow) takes you back 1 record, ie if you are on record 3 and you click this arrow you will go to record 2. The next button (right arrow) will take you one forward, ie if you are on record 3 you will go to record 4. The last arrow with the line to the right will take you to the very end.

Click on the table and right-click to activate the popup menu. Choose Delete. You will be asked to confirm the deletion. Be careful when choosing that you really want to delete the table.

When you are designing a table make sure you click on the save button as you make changes. When you are finished click on the close button (X). If you have not yet saved you will be prompted.

Every table in OOBase needs to have a primary key defined. The is a field (or fields) that uniquely identify a record. In other words no two records can contain the same number in the primary key field. In the Customers table we have a field called CustomerID which should be set to the primary key in this case. This means that each customer will have it's own unique identifying number. If you use a field such as the Name as a primary key it will probably not work as you may find companies with the same name. Sometimes you can use an existing field such as ID number as a primary key field. If you do not have a field that is suitable to use as the primary key then you will need to create one such as CustomerID to act as the primary key.

To set a field as a primary key, click on the grey button next to the field name in and right click to activate the pop up menu. Click on Primary Key. You will see a small yellow key appearing next to the field name.

An index improves performance by providing an alternate path to access data and speeds up searches, sorting, queries etc.

To create an index click on Tables and open in Edit mode. Click on Tools > Index Design or click on the icon on the toolbar. Figure 14 shows an example of the first Index Design screen.

Figure 2.6. Creating a new index

Click on the first icon to create a New Index. A new index is created on the left hand side called Index1 which you can rename. In the example in Figure 15 the index has been renamed to Surname. On the right hand side you will need to select the field you want to create the index on, in this case it is the surname field and the sort order is Ascending.

Figure 2.7. Renaming the index

Notice that there is an option to make this a unique index. In other words if you do not want duplicate entries here you would click on Unique. This would not work here as there are going to be instances where many people have the same surname. However, if you were creating the index on a field like an account number you would then choose not to allow duplicates by activating the Unique option.

Remember to save the index by clicking on the save button. If you have not saved and you click on the close button you will get the following prompt “Do you want to save the changes made to the current index? At this point you could save.

When you create the fields you will notice a section at the bottom of the screen that allows you to change attributes such as the size of the field, the number and date format. To change attributes for a field you will need to click on the field at the top which will give you access to it's attributes displayed in the bottom section of the screen. (Figure 16)

Figure 2.8. Change the attributes of a field

To change the field size of the PostalCode field click on it to select it. As in Figure 16 above a green arrow will appear to indicate that it has been selected. Then click in the white box next to Length. Type in the number of characters you want the field to be restricted to. In this case it is 4.

If you want to change the number or date format click on the grey button next to Format example. This will bring up a dialogue box which will allow you to choose a format for the number or date.

It is important to note that if you change the field size in a table and there is already data captured you may lose some of that data. For example, if you change a field size from 50 characters long to 10 characters long and there is a record with 20 characters in that particular field you will lose the data so be very careful.

Rules can be created for various fields, for example, you may want to restrict a field to be no bigger than 10 characters. This is done by creating a validation rule. Click on Tables and Edit the table.

Click on the field you want to set a rule for. The attributes are displayed at the bottom of the screen.

Sometimes the columns containing the fields are not wide enough to display the data fully. You can widen the columns by opening the table. Click on the table, right click and select Open from the pop-up menu. Move your mouse up between the field names in the grey area (as you would to widen columns in a spreadsheet), double click on the vertical line separator.

FIX ME

A relationship is created by linking a field in one table to identical fields in another one. For example in the Customers table each customer has a unique ID number in the field CustomerID. There is also a field in the Orders table called CustomerID. By linking the two and creating a relationship you can display all the orders for a certain customer based on that customer's number.

To create a relationship between two tables click on Tools > Relationships and the following dialogue box will appear (Figure 17).

Figure 2.9. Relationships - adding tables

Click on the Customers table and click the Add button. Click on the Orders table and click on the Add button. Both tables will appear. Click on the CustomerID field in the Customers table and drag it across and drop it on the CustomerID field in the Orders table. A relationship is created between the two tables based on the CustomerID field.

To delete the relationship between the tables, click on Tools > Relationships. Right-click on the line linking the tables (Figure 18) and click on Delete.

Figure 2.10. Delete a relationship

You want to make sure that the record between tables remains accurate once a relationship has been created. The way to do this is to enforce referential integrity. Click on Tools > New Relation (Figure 19).

Figure 2.11. Referential Integrity

By clicking on Update cascade you are ensuring that if a change is made in the Primary Key field it will make the changes in the other tables that that field is linked to.

Clicking on Delete cascade ensures that Base will delete all the records that are linked to that field. If this option is not selected you will not be allowed to delete a record that is linked to other records.

To open an existing form click on the forms icon on the left hand side of the screen. Then double click on the name of the form you wish to open. Alternatively you can right click on the name of the form and then click on Open.

To create a form based on a table, first click on the forms icon on the left hand side. Then click on Use Wizard to Create Form. Figure 20 illustrates the first screen of the wizard.

Figure 6.1. Screen 1 of the Form Wizard

Select the table you wish to base the form on by selecting it from the drop down box under Tables or queries. Next select the fields you want the form to contain by clicking on them and clicking on the > to move them across. If you want to select all fields click on the double arrow >> and this will move all the fields across. Click the Next button. The second screen of the wizard is to do with creating a subform. As we do not want to do this at this stage leave it as it is and click the Next button.

Figure 21 shows the next screen of the wizard.

Figure 6.2. Arranging the controls on the form

Choose the label placement and layout that you want and click Next (Figure 22).

Figure 6.3. Select the data entry mode

Click on Next (Figure 23).

Figure 6.4. Apply a style to the form

Click Next to go to the final screen of the wizard (Figure 24).

Figure 6.5. Set the name of the form

Click on Finish to complete the setup of the form.

Once the form has been created you can use it to enter data.

Double-click the form to open it for entering data. Enter the data by clicking in the box next to the field name and entering the relevant information. If you wish to make changes to a field, double-click in that field and make the necessary changes. If you want to delete a record click on the delete record icon on the Form toolbar at the bottom of the screen.

To navigate between the various records in the form, use the arrows on the Form toolbar at the bottom of the screen (Figure 25). These arrows look and function in the same way as those in the editing table screen.

Figure 6.6. Form Toolbar

FIX ME

If you want to delete an existing form right-click on it and select Delete from the drop-down options.

When you have finished with the form click on the save button. Close the form by clicking on the close icon (X) or by clicking on File > Close.

In table mode, you can search for a specific word, number or date in a field by opening the table and then clicking on the Find Record button.

The following dialogue box will appear and you can enter the word, number or date you want to search for and select which field to look in (Figure 26).

Figure 9.1. Searching for specific text in a field

Click on the Search button and a red box will appear around the word when it is found.

Filtering allows you to work with selected records. In other words the rest of the database remains hidden while only the records you want to work with are visible.

There are four icons for filtering on the Standard Toolbar (Figure 27). The third one from the left is the Default Filter button.

Figure 9.2. Filter Icons

Click on the Default Filter button and the dialogue box appears as in Figure 28.

Figure 9.3. Default Filter

You may now select the criteria for the filter as shown above. This will filter out the records with Cape Town as the town and JD Wholesalers as the Name.

To remove the filter from a table, click on the last filter icon with the red X as per Figure 27 above.

Queries allow you to extract specific data from one or more tables and create a new, separate table containing the selected data. For example, if you wanted to extract all the customers in the Cape Town area from the database. To do this, first click on the Queries icon on the left hand side. Click on Create Query in Design View. The Add tables dialogue box appears. Click on Customers and click on the Add button.

Double-click on each field that you want to appear in the table. Next to criterion in the Town column type in Cape Town see Figure 29.

Click on the Run Query icon on the toolbar.

Figure 29 illustrates what the end result should look like.

Figure 9.4. Single Table Query

You can also use more than one table. For example you may want to list all the orders along with the customer name and ID's. This would require information from both the Customers table and the Orders table. Remember these tables have a relationship between them based on the CustomerID field.

Create the query as above, but this time when you add the Customer table, click on the Orders table and add it as well.

Now you can double-click on the fields you require from both tables and they will be inserted accordingly. When you run the query it will look as in Figure 30.

Figure 9.5. Run a query using two tables

                    
            <(Less than), <= (Less than or equals), > (Greater than), >= (Greater than or equals), = (Equals), <> (Not equal to), And, Or
                    
                

In the previous example we used the = criterion by typing Cape Town into the Town column. Other criterions as listed above can also be used. The example in Figure 31 illustrates a query with the town = Cape Town and the Order amount greater than or equal to >= R200.

Figure 9.6. Run a query using two tables

Once the query has been created it can easily be edited. Right-click on the query name and select Edit. This will open up the query in edit mode allowing you to make the necessary changes. Once done save and run the query.

If you want to add or remove a field, right-click on the query name and select Edit to take you into edit mode. Add or remove a field as you wish.

You will notice from the examples in Figures 29, 30 and 31 above you have a checkbox that allows you to make a field Visible or, if you remove the tick in the checkbox, hides that particular field.

As explained above, once you have created your query click on the Run query button to execute it.

If you wish to delete a query, right-click on it and select delete.

When you have finished the design of your query click on the save button and give it a name. Click on File > Close to exit the query.

Data can be sorted according to numeric or alphabetical order. Open the table and click on the Sort button.

The dialogue box (Figure 32) appears. You can select your sort criteria from here as shown.

Figure 9.7. Sorting Data in a Table

Reports are very similar to queries except that when you create a report it is laid out in a printable format (hard copy).

To create a report in OOBase click on the Reports icon on the left hand side and then click on 'Use Wizard to Create Report'.

The first dialogue box of the Report Wizard appears as in Figure 33.

Figure 13.1. The First Screen of the Report Wizard

This screen allows you to select the Table or Query you want to use to base your report on. Click on the arrow in the box underneath Tables of Queries and select the appropriate one.

Next you need to select the fields. You will see them listed in the Available Fields box. Click on the first field you want in your report and click the arrow > to put it into the box with the heading Fields in Report. Click on the next field and do the same until all the fields you want are displayed on the right hand side.

Click on the Next button to go to the second screen of the Wizard (Figure ).

Figure 13.2. Labelling the fields

This screen checks to see how you want to label the fields in the report. You can leave the labels as they are and click Next.

Figure 13.3. Add Grouping Levels

Figure 35 gives you the option to add grouping levels. This means that you can group records according to the values in one or more fields. You select the fields by clicking on them and clicking the arrow > to move them into the Groupings box. You can group up to four fields in a report. If you do not wanting Grouping leave this box as it is and click on Next.

Figure 13.4. Sorting the data

The screens lets you choose how you want the data to be sorted based on specific fields. For example in Figure 36 above the data will be sorted according to the Name field from A-Z (Ascending). A second field could be specified and so on with up to four fields. If you don't need to sort the data into any particular order then leave this screen as it is and click on Next.

Figure 13.5. The appearance of the report

You can choose an appearance for your report by selecting the layout of the data aqnd the layout of the headers and footers as per Figure 37 above.

You can also choose whether you want the report to print in Portrait or Landscape by clicking on the relevant option. When you have made your decision click on Next.

Figure 13.6. The final screen of the report wizard

The screen in Figure 38 shows the final screen of the report wizard. All you need to do here is to select whether you want a static or a dynamic report and how you want to proceed after creating the report. A Static report will create a document that will contain the same data from the time the report was created every time you open it whereas a Dynamic report will create a template which, when opened, will create a template which it will fill with the current data contents.

If there is nothing you want to change click on Finish and OOBase will create the report.

You can change the arrangements of the data fields and headings when you are creating the report in the Report Wizard and also when you have finished creating it.

In the wizard you have the option to move fields around in the first screen as in Figure 39.

Figure 13.7. Rearranging fields in a report

Sort data in a table, form,

If you click on the field name and then click on the up or down arrows on the right you can change the position of that field in the report.

When you get to the last screen of the creation of the report you also have the option to Modify the report before you click on Finish (see Figure 38). This enables you to edit the page styles for the first and subsequent pages, paragraph styles, number formats, printed label fields etc.

Alternatively, if you have already created the report, simply right-click on the report name and choose Edit. This will open up the report in writer format and allow you to change the design as in the previous paragraph.

Grouping data was covered in 5.5.1.1. See Figures 35 and 36.

FIX ME

Modifying the text in the headers and footers would be done by right-clicking on the report and choosing edit. The same procedure as mentioned in 5.5.1.2.

You delete a report the same way as a table, form or query by right-clicking on the report name and choosing delete.

To save the report click on the save button. Once you have saved it and can click on File > Close.

You may wish to preview a report before you print it. To do this open the report and click on File > Page Preview. This will give you an accurate representation of what the report will look like when printed. Click on Close Preview to exit and go back to the normal view of the report.

To change the orientation of the report right-click on the report name and choose Edit. This will open the report in Writer which allows you to make the necessary page layout changes. Click on Format > Page > Page to make the desired changes.

To print selected records in OpenOffice.org you would have to run a report that would select the required records and then print it. The same would apply if you wanted to print the complete table.

FIX ME

To print the result of a query you would have to create a report based on that query and then print it.

First generate the report and then print as normal by clicking on File > Print. If you want to print a specific page, type it into the dialog box and click OK or else simply click on OK to print the entire report.