Tip:
Highlight text to annotate it
X
If you are new to database applications in general, not just OpenOffice Base,
you will definitely benefit from what we are going to cover in this lesson.
I am going to talk about some general database concepts as well as terminology
that will help you through the rest of this title. We are going to start with
the term database. What is it? Well, in its simplest terms, a database is an
organized collection of information. Think about everyday items you use like a
telephone book, recipe books, a mailing list. Those are all examples of a
database. Now a database is even more when we go into an application like
OpenOffice Base. Here you get a set of data related to a specific purpose or
topic. Think about the things you need to accomplish and then set them up in
your database application. You get all kinds of tools that you need to work
with that data. For example, if you need to sort the data or extract certain
pieces of that data, summarize it, you can do that with a computer application
like OpenOffice Base. You can't necessarily do that with a printed out phone book.
Now let's start with some of the terminology. The base of any database is
the table. It's the foundation. Now a table is a list of related information in
columns and rows. Now each row in the table could be considered what we call a
record. So, if you were to go to a phone book and look up your name, your
address and phone number that in there would be considered one record. You are
in there with tons of other people; they each have their own records.
Each record is going to be made up of fields. Each column in the table would be
considered the category or field, so if we think about that phone book again,
a column of phone numbers in the phone book would be the phone number field. Now,
within a record you'll have your information. For example, your name, and you
probably see your first name and your last name, your address, your phone
number will be in there. One item of data in a record like that such as your
actual phone number in the phone book is called a Data Value. So, those three
pieces make up a table and a table as I mention is the foundation of any
database file. Now, let's talk about a Relational database and here is the
beauty of a relational database application like OpenOffice Base or other ones
like Microsoft Access for example. It's not just a flat file database like your
phone book or spreadsheet for example. I know a lot of people use spreadsheets
to track data. Now, imagine for a second, we are signing up for a mail order
DVD club. Well as soon as we sign up we provide information like our name, our
address, city, state, zip. As soon as we order something there is an order
number attached to that. The date that we ordered the DVD, what's the title,
the price. This is an example of what we would call one record. Now, what if a
customer orders more than one DVD, I mean, any mail-order DVD club is hoping
that customers will order more than one DVD, in fact you usually forced to. So,
multiple records would contain duplicate information if we didn't have what we
call Relational database. Check out down below here where you see my name
repeated several times. My address, city, state, zip, they are all repeated
multiple times. The order numbers change, the dates change, the DVD title may
change and the price my change. Any down at the every bottom, you will notice
that I go all my information repeated there. I have got the same date, the same
DVD title and the same price. In that example, I have ordered two copies of the
same movie. So when I get all this repetition, all these records that are kind
of building up and storing information, I get into these very large files, I've
got multiple pieces of information and becomes a very cumbersome to work with.
So, that's where relational database comes in. In OpenOffice, a relational
database can contain multiple tables and each table pertains to a specific
topic. So, for example I might have a Customers table that contains all my
information name, address, city, state, zip. Then, I might have an Orders
table, which has order numbers, the date and the quantity and then I've got a
DVD inventory, which contains things like the title and the price. All we need
to do now, to make these tables related to one another, is to insert a field
that will allow us to uniquely identify each record and here is what it might
look like. Using a primary key, a field containing unique data values to
identify each record in that table, check out the Customers table. The Customer
ID, each customer will have their own identification number in this example.
So, I would have my own number and you would have your own number and then your
information. Then, we use that field over in the Orders table so the Customer
ID is all you need in the order. You don't have to repeat the name, address,
city, state, and zip. Just that Customer ID and then we also add over in the
DVD Inventory, a DVD ID number, which can be used in the order table as well.
Notice the arrows going from one table to the other. This makes them all
related to each other. So because they are linked by a common field,
information is what we call well organized and the tables are now related. This
is a Relational database. Now let's talk about those relationships for a
second. Each relationship consists of that primary table and a related table,
sometimes called the parent and the child tables. The primary table typically
contains the Primary Key field. So look at the Customers table for example. The
Customer ID is what we will call our primary key. In other words you could not
have two Customer IDs that are identical; each one is unique. So with the
Primary table and the Orders table being a related table you'll see the
Customer ID will appear over there in the Orders table, but it could be
repeated there, it's not the primary key in the Orders table. Now how tables
are related to each other also represents another key concept in Relational
databases. There is one-to-one, there is one-to-many. These are the two main
types of relationships that we'll talk about. Check out the Customers table and
the Orders table. Here is an example where we hopefully have multiple orders by
the same customer. So this would be one customer having many orders or a
one-to-many relationship. So one-to- one relationship would be a little bit
different, that is when the Primary record has only one related record. And
here's an example where we would have customer information as well as billing
information in a separate table. So Customer ID would appear once in both of
these tables. And on the one-to-many as I was mentioning, we would have a
single customer placing many orders. So in the Orders table over there on the
right-hand side you'll see the Customer ID is repeated, it's is not the primary
key. It is over on the left hand side in the Customers table. And then we'll
see order numbers and order dates. So, one Customer can order many DVD's.
Customer ID not a Primary Key over there on the right-hand side in the Orders
table. This is the most common relationship by the way, the one-to-many
relationship. All right, so it's time to start exploring these concepts and
this terminology in OpenOffice 2 Base. In the next lesson, we are going to
launch the application and take a tour.