AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS Open Office database tutorials.
Delicious.Com Bookmark this on Delicious     StumbleUpon.Com Recommend toStumbleUpon

Referential Integrity: Open Office 2 Base (database) Tutorials

(Minor brush up, 02 Mar 17)

You may find that the database included in OpenOffice delights you as much as it has me. This page tries to help you use it.

Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!

There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 6/07- 3/17.



In this tutorial you will see how to set up a data entry form for two related tables. I tried what follows myself again, from scratch, 2/15, OO version 4.0.1, and tried to update any dated material from back when it was first drafted.

In isolation, what we do may see a bit pointless, but...

Although it is not light or lively reading, writing the following was even harder than reading it. If you take a deep breath, and can force your way thought all that follows, I believe it will strengthen your database building skills. Please do what is needed to make all my work something more than a waste of time?

There is some overlap between the contents of this tutorial and two others I've published, but all of them are talking about important concepts, and I believe that it can be helpful to look at things from multiple directions, when trying to master them. (The other tutorials were Getting started with multiple tables, relationships and "Using multiple tables and relations")

In fact, I've written several tutorials with "relationships" (as in "relational database") at their core, and so have produced a sub-menu page for these related essays.

Part of my difficulty was that, in my mind, two concepts were not sufficiently distinct. I was blurring the distinction between relationships and joins, two superficially rather similar "linking" mechanisms. In this essay we talk only of relationships. (Joins come up when you start building queries (or reports) drawing from multiple tables.)


The database used for illustrating what is taught in this tutorial happens to be for managing stock market investments. Fear not! The same objects we use here- tables and forms- are used in many applications. Tables are made of fields. Those other applications are only different in the names on the fields, and perhaps the data-type of the field. But whatever the application we are building, we will still be using fields/ tables/ forms, etc. (So don't go away, just because you're not doing a stock market application!(And forgive me for not editing out the repetition of that which arises soon?)

Underlying the example are two tables. One lists the names of some stocks, and the other table lists economic sectors into which the stock market can be split up.

With that modest database we will have made a useful start towards a package which can generate useful information for the investor holding the stocks recorded in the database.

"But I'm not a stock market investor!" you cry...

Don't worry...

Humor me? Pretend you want to start investing? Just for a while?

You may have come to this tutorial from a longer essay that I'm working on. If you have, you may already have a database called PDB058. If not, create it now, and create the following two tables. (See my "getting started" tutorial if you want help with "create the database and two tables".)

Our schema

The two tables we're going to use should be set up as follows. It really helps if you actually DO what is described as we go along. Use my names, if you do, with capital letters and lower case in the same places... and your life following the tutorial will be easier.

First Table: StockNames, consisting of 4 fields:

The primary key for this table will be created from a combination of the first two fields. (I.e. it will have a "compound" key.)

The name of the first field is merely a shortened version of "ticker", the term used in the financial world for an (almost) unambiguous abbreviation of a company's name. You can say "I have shares in Pfizer", or you can say "I have shares in PFE".

Tick, type Text(fix)[CHAR], length 10
Dis, type Text(fix)[CHAR], length 1
Sect, type Text(fix)[CHAR], length 3
Name, type Text[VARCHAR], length 32

(The last field will hold UP TO 32 characters. Not vital to today's story. Pedantic? Moi?

The primary key for this table is a compound key, made from the first two fields. (I'll explain what the fields are for in a moment.) If you didn't set up the primary key as you created the table, just open the table for edit, select the first two rows, and put the little key in the gray cell of the left hand column with a right-click.

You really must have something designated as primary key in anything you do in Open Office. I would guess that a compound key, such as we've discussed here is not terribly common. (Nor rare.) Often, the primary key will be just the contents of one column of the table, one set of fields. We'll see that configuration in our second table.

(Things might seem to "work" for a bit, at first, if you have no primary key... but the tears will soon begin.)

Just before we move on, a word about the fields in StockNames. The last field (Name) is the everyday name for the company a given record is about. The first field ("Tick") is the tickertape abbreviation for that company, an abbreviation used in many contexts across the investing world, and carefully managed by the stock exchanges, so that you have a good chance of finding what you want with nothing more than the ticker code. There is a slight fly in the ointment. Take "S". That was the ticker for Sears & Roebuck. That company is gone. Since at least June 2007, "S" has been used to say "Sprint".

At any one time, only one company is currently identified by, say, "S". But in different eras, which company is "S" can change.

So... Because we want to use our database not only for current holdings, but for records of past holdings and transactions, we need a way to DIStinguish between, say, different "S"s. Which is what the "Dis" field is for. We'll put a code there, to distinguish one "S" from another. Sears, might, for instance have "S" in the first field, and "e" in the second. (From SEars). Sprint could have "S" in the first field, and "p" in the second, in the "Dis" field. Note that these codes are not universally agreed.

The second field in StockNames holds a "disambiguation" code.

Even if there's no problem like the Sears/ Spring problem, which I just tried to explain, some companies have multiple classes of stock, often called "class A" or "class B". For instance, I once own some Hubble Class B. (Hubble has since done away with the different classes, but what was the case still makes a satisfactory example:

The ticker for Hubble was "HUB". If my disambiguation code for my Hubble B was "b", then those shares were stored as tick="HUB" + Dis="b" in my records.

Hubble traded (and still trades) on the NYSE. NYSE stocks three letter tickers, like "HUB". But sometimes, some places, you saw "HUBB", meaning Company: Hubble; Class: "B".

And it gets worse! Depending on what system you are working in, you use HUBB, HUB.B or HUB-B to ask about Hubble Class B stock. Pity "the big boys" can't seem to agree on a standard way of dealing with multiple classes of a given company's stock. But that's the reality. Deal with it. ("Dealing with things" like this is often an issue for database designers, by the way.)

Onward...

Second Table: CodesSectors. Consists of two fields. ("Sec" in these names for "Security", i.e. "stock". Not because this is SECond table.)

This table's primary key is a simple key, based on just the first field. The "PK" below is shorthand for "This field is the the primary key for this table.")

SecID, type Text(fix)[CHAR], length 3, PK
SecDescrip, type Text[VARCHAR]), max length 10

For the sake of the tutorial, enter the following data. Contrary to general good working practice, just access the tables directly to put this data in. (In general, you should create a form, and enter data through that.)

A detail: It doesn't really matter what case you use when entering tickers. You could use, say, PFE, pfe or Pfe. I've made a considered choice in the case conventions I've suggested, but you don't have to do things as I have... but at least be consistent.

Put some sample data in the tables

StockNames:

PFE   a   dru   Pfizer
IBM   a   tec   International Business Machines
GOOG  a   tec   Google

CodesSectors:

dru  Drugs
tec  Technology

That's a good start!

The sector field is the interesting one. Before we talk about how that field is built into our database, perhaps I should say what a "sector" is? It's what you probably expect: Investors expect companies which do similar things to have similar characteristics. Two energy sector stocks, say BP and Exxon, are likely to have similarities. Mr. Bush takes us to war, and the share prices of most energy sector stocks go up. At the same time, the share price of companies in the airlines sector go down. The investor worries that fuel prices will rise, and the airlines will have trouble raising their prices enough to maintain their previous profits. It is interesting that the market assumes that the oil companies will do better if oil becomes harder to obtain. Of course the oil companies are selling oil, and the airlines are buying it. So: A company's "sector" indicates the general part of the economic ecology the company is part of.

In our database, we have a table listing the names of the sectors we choose to split everything up into, and the abbreviations we've chosen for those sectors. We have a field in the StockNames table where we will use one of those abbreviations to characterize a given company. This approach will save us a lot of typing, and chances for typos.

What we have done may seem "obvious", but it is at the heart of a big part of what is wonderful about relational databases. What are "relational databases"? The meaning of "relational" is something that you will gradually absorb as we go along. To get you started: There is a relationship between our two tables, isn't there? The "sector" information in the StockNames table records relates to information in the CodesSectors table's records. (Many people do, by the way, use "a relational database" without using the features that make it more than a simple, non-relational database. Using the extra features is optional... but it brings many benefits.)

(There's more on relationships and joins in a short(er) page I created which talks about these important concepts in general terms. They are not the same thing, but the differences constantly blur in my mind, and I hope to help you fight that problem.

Moving on: Close any ooBase things you may have open, apart from the main ooBase project management window (see next paragraph) and (optionally) the "help" window. But leave "the database" open.

(What's the "main ooBase project manager window"? It has 3 panes: One fills the left side, is headed "Database", and allows you to select Tables, Queries, Forms and Reports. The other two are one above each other on the right, one is Tasks and the other lists the available entities for whatever part of the database system you are working on. Details available. Opens in own tab.)

Next, we'll create two forms which can be used for looking at, or editing, the records in the tables.

Select "Forms" in the left hand column of the main ooBase project management window. Then click on "Use Wizard to Create Form." Don't be alarmed by the OpenOffice Writer page that opens... your "Database Form" will be a fancy ooWriter "Document"! Note that the Wizard's steps are indicated in its left-hand column.

Step 1: Select Table: CodesSectors, and move both of the available fields to the "Fields In The Form" panel. (The easy way is to click on the ">>"). And Click Next.

Step 2: Don't set up a sub-form. Just click next to go on. You will skip over steps 3 and 4, and find yourself at...

Step 5: You want to arrange the data controls as a datasheet. The appropriate selection has probably already been made. (You'll see "as datasheet" where it is needed.) And Click Next.

Step 6: Again, what you want is probably already selected: "The form is to display all data". Don't disallow anything. And Click Next.

Step 7: Apply Styles: Play with this if you must. I just took the defaults. And Click Next.

Step 8: Give it the name CodesSectorsDataEntry; leave the setting on "Work with the form", and click "Finish". (If "next" is not grayed, you've probably made a mistake. In this case, click "Next", and you may be taken back to where the problem was first important. Note: That may be after the place where you did what needs changing.)

If all's well, you should now have a form which looks very like the table looked when you manipulated it directly.

Try adding another record:

SecID:ene
Sec:Descrip:Energy

And, for reasons which should become clear, another...

SecID: mis
SecDescrip: Misc (for "Miscellaneous")

Be sure to leave the current record when you have finished filling in all of its fields... as usual. Until you leave the record (or use the "Save Record" toolbar button) your new data is only on the screen, not in the database. You've "filled in a form" (sort of), but not sent the data anywhere.

Adjust the size of the window that form is in. Leave it open.

Follow similar steps to create a form called StockNamesDataEntry which displays all of the fields of the StockNames table.


Hurrah! You're two steps down the road to becoming the next stock market tycoon!

We have a small database that will hold the names of companies, and a little bit of information about them.

Let's make a deliberate mistake.

Enter a record in the StockNames table with...

TEST / a / xxx / TestRecord.

What's this "xxx" sector? (Companies in the porn industry are not, as a rule, listed on the stock exchanges.) It is a test, to see what happens if we enter "bad" data. All of the entries in the sector field should be abbreviations present in the records that constitute the CodesSectors table. Checking that no unspecified sectors are in use is a large part of the reason for the CodesSectors table. Either the data entry operator has mistyped something, or the person who said that the company was in sector "xxx" is imagining that a sector exists for which there has no proper provision. It isn't hard to "properly provide" for a sector, even a new sector, but the system is built in a way that encourages discipline.

At the moment... and we're going to "fix" this... the system allows you to enter the "bad" sector "xxx".

(Delete this test record, to avoid problems which will otherwise arise in a moment. (To do this: Right click on the rectangle just to the left of "TEST" in the "Tick" column, and then click on the "Delete Rows" which you ought to see.))

Our database "works", on a superficial level. However, good programs for managing data allow you to set up rules which prevent the entry of something like "xxx"... a sector abbreviation not defined in the CodesSectors table. And we're going to set those rules up in a moment.

There's another thing that a good program for managing data will let you set up: Something to stop you, say, deleting the "dru / Drugs" record from the CodesSectors table as long as there are any records in the StockNames table which use that code. "Referential integrity" is the name for what is ensured by such rules.

Close anything that is open, apart from the main ooBase project management window and (optionally) the help window.

On the menu bar of the main ooBase project management window, click Tools | Relationships. The "Relation design" window should open.

Use the "Add tables" window to add, one after the other, StockNames and CodeSectors to the main "Relation Design" window.

Arrange the representations of the tables so that StockNames' is to the left of CodesSectors'.

Before we go further: A little information. If you see "CodesSectors.SecID", that is just an efficient way of saying "the SecID field of the CodesSectors table".

Put your mouse pointer in the middle of "Sect" (in StockNames). Depress, and keep depressed, the left mouse button. Move the pointer to the middle of "SecID" in CodesSectors. Release the mouse button. (It is as if you were trying to drag "Sect" onto "SecID".)

Be sure you drag from StockNames.Sect to CodesSectors.SecID, or you will get a complicated dialog to deal with, and it is easy to fluff the necessary settings. (Bring it up, have a look at it... but delete any relationship that arises, and re-do it by dragging in the right direction. When you drag in the right direction, a line should appear between the two field names, with "1" at one end, and "n" at the other. (More on these labels in a moment.)

Get an error message? You will if you forgot to delete the TestRecord record, sector "xxx", as you were told to above. The system won't let you set up a relationship if existing data violates the "rule" established by the relationship. Finding the offending data can be tiresome. Thus, it is best to set up your relationships early in the database's development. If you have, bad data doesn't get in, so you don't have to find it, take it out!

Forgive a little digression? I hope so....

Once you have things set up, there's a "gotcha" that still gets me. Suppose you're working with the database we're discussing here. You've had it "done" for a while, you're just filling it with data. And you want to add a stock from a sector you haven't used before. Let's say the stock is BIDU, and you want to define a sector called "ifs" (InFormation Services), and you haven't entered any stocks from this sector before.

You can't enter the stock until you've made an entry in the CodesSectors table. Fine. You make that entry. Then you go back to the StockNames table, and try to enter BIDU. It seems happy, but you can't leave the line! Either the system just refuses, or an error message pops up.

What's going on?

Probably the following....

After you entered the "ifs / Information Services" record into CodesSectors, did you use your mouse or cursor to move to a different record? If you didn't, the new record is still merely on your screen, not yet "in" the database. Even if you were only editing an existing record, until you either click "Save Current Record", or simply move to a different record (my usual practice) the new data is not "in" the database! Thus, when you went over to the StockNames table, tried to enter the BIDU record, the database would not accept it, because it didn't yet "know" about the ifs sector.

I still make the mistake of failing to move out of a record after changing it... but I do recognize the symptoms arising pretty quickly these days, and it is easy enough to go back and do what needs to be done. So I suppose I am learning something. I just wish I didn't learn so slowly. Sigh. End of diversion!

After you've done the things I told you to before I started the diversion, you should see a line between the representations of the two tables. (Move the tables around. See how it can make the line sensible? I think that the "rubber banding" procedures built into the GUI are pretty neat!)

At the left hand end, you'll see a little "n", and at the right hand end a "1". The line says that there is a "one- to- many" relationship between CodesSectors.SecID and StockNames.Sect.

"One- to- many?" you may be asking.....

It means that any given value for the field will be found in only one record in the CodesSectors table, but there may be many, any number ("n") of, records in the other table where that value appears.

Think about it. It would be silly to have a given sector code stand for more than one sector, but there are many companies in the same sector, so the sector code in many of the company names' records will, for example, be "ene".

If a field is a table's primary key, you cannot enter the same data in two records OF THAT TABLE. That protects us from inadvertently creating a new sector which re-uses a code which we've already allocated to another sector. We cannot, say, make an entry in CodesSectors like "ele / electric utilities" if we already have "ele / electronics". The program for managing our data is giving us protection against something we might do by accident. BUT: (don't let this fact confuse you) You CAN have "ele" in many of the records in the StockNames table. In THAT table, the "ele" isn't going into a field that is part of the table's primary key. (It is simple, really. You will "get it"... eventually. And for a while after you think you understand, you will trip over being confused, and after a bit of that you will really understand it! It is just the nature of database work. Are you tough enough to "get there"?)

Previously, we could enter "bad data" in the Stocknames table. We could enter "TEST / a / xxx / TestRecord". (This would be bad because there should no sector with "xxx" as its code.)

Close the "Relation Design" window.

Re-open the StockNamesDataEntry form (or the StockNames table... but you wouldn't normally work directly with that, would you?)

Try entering "TEST / a / xxx / TestRecord". It will let you enter that... but as soon as you try to leave the record, ooBase will complain, as it should, alerting you to the fact that you are trying to enter bad data. ooBase puts it more formally: "Error inserting new record: Integrity constraint violation"... I.e., the integrity of your data would be gone if this record became part of the table. Change the "xxx" to a code that IS present in the CodesSectors table, and the new record will be accepted. Brilliant! This is Really Clever. Don't let your by- now- glazed eyes fail to notice how marvelous this is.

I hope you remember that we created a sector called Misc, code "mis"? This was so that we can always enter a new record in the StockNames table, even when we aren't sure what sector to use to characterize the company. We just say, for the moment, that it is in the "mis" sector. From time to time, we would probably ask ooBase to list all companies in the "mis" sector, and we'd try to assign at least some of them to a more meaningful sector. (It is easy to ask ooBase to show just the "mis" companies.) If we wanted to put them in a not- yet- provided- for sector, we could... we just have to go to the CodesSectors table first, create the new sector and assign its abbreviation, and then we can change any company's sector code to the new sector.


A few odds and ends....

To quote the ooBase help file: "If the Relation Design window is open, the selected tables cannot be modified, even in table Design Mode. This ensures that tables are not changed while the relations are being created."

What I just quoted shouldn't come as a surprise, if you think about it. Don't give ooBase too much to cope with. If you are working on setting up relationships, it would probably be best if the tables were closed. And vice version. Do one thing at a time. It shouldn't significantly cramp your style.


A detail: You can edit the appearance of the forms. You don't need to, so skip past this if it doesn't come easily.

Close anything that is open apart from the main ooBase project management window and (optionally) the help window.

Click on "Forms" in the project manager's left hand pane; right click on "StockNamesDataEntry", select "Edit". Your form will open in ooWriter, because the form is "just" a (complex) ooWriter "document". Now we get to the tricky bit. Click somewhere along an edge of the table (Tick/Dis/Sect/Name) which is an object within the form. What's tricky is clicking in the right spot. If you get the right spot, you will select the table, which will be shown by the fact that it now has little green squares at its corners, and in the middle of each side. If you drag these, you can re-size and reposition the table on the form. You can also resize the whole window. Save what you've done and close the window. I know that your changes to the table's position and size will be saved. I think that the state of the window when you closed it will be saved. Now use the ooBase project manager to open the form normally, i.e. for mere data entry. (Do this by double-clicking on it's name.) Table a better size? I hope so! As I said... if not, don't worry about it.


I had my form open for data entry, not form editing, and managed to get things in a tangle. I had some rows that I didn't want, and right clicking didn't bring up the "Delete row?" option. I just closed and re-opened the table, and all was well. I'd been opening and closing all sorts of things, any one of which could have made the table "write only". (A controllable "write only" option is probably available. It would be useful, for instance, in a stock control program where you wanted junior employees to be able to enter data, but not remove it.)

I have every confidence that during normal operations you won't get into the tangle I was in. And it was easy to get out of, in any case.

By the way: The "read only" that you see in the window's title bar doesn't mean that you can't add or remove records to the database. It means that you can't (at this point) alter the form you are using to edit the data.


Remember why we hoped that ooBase incorporated referential integrity tools? What happens, say, if we try to delete the "dru / Drugs" record from the CodesSectors table while we have a record in the StocksNames table consisting of "PFE / a / dru / Pfizer"? Try it! Hurrah! ooBase says, in more complex terms, "No, won't.".

Concluding remarks

This may all seem complex, dry, unnecessary. Take my word for it: It is wonderful, and using the tools presented in this tutorial will pay dividends. For a little project like a list of telephone numbers, you are unlikely to have any need of these tools. But if you want to go further with database design, these tools will be part of what you need. With them, you create robust, reliable, efficient databases.

I've written several tutorials with "relationships" (as in "relational database") at their core, and so have produced a sub-menu page for these related essays.



Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in the Google+ button code. Sigh.


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .