Join David Rivers for an in-depth discussion in this video Database concepts and terminology, part of OpenOffice.org 2 Base Getting Started.
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.
- Planning and designing a database Working with tables, fields, and records Creating relationships between tables Refining query results with sorting and filters Printing, emailing, and exporting reports as PDFs