Join David Rivers for an in-depth discussion in this video Database concepts and terminology, part of Access 2007 Essential Training.
Right before we get into Microsoft Access 2007, get familiar with the environment, and start creating, we need to cover some database concepts. So let's talk about a database and exactly what it is. In its simplest terms, a database is an organized collection of information and we use organized collections of information every day. Take for example a telephone book, or a recipe book, or even a mailing list. Now in Microsoft Access a database is a little bit more than that. Yes, it is a set of data related to specific purposes or topics, but it also includes tools that you need to work with that data, such as tools for sorting and extracting data or even summarizing it in a nice little report.
Another piece of terminology that you need to be familiar with when working in Microsoft Access is a table. Now a table is just a list of related information that's sorted into columns and rows. Now each row in the table would be called a record. Think of the phone book and your particular information in that phone book would be considered a record. Each column in the table would be called a category, or in our case, a field. Now a column of phone numbers in that phone book would be considered the phone number field, for example. Now your actual phone number in the phonebook is one item of data, and we called that a data value.
Now let's talk about relational database concepts, because when we work with Microsoft Access we're creating what we call a relational database, and a relational database is not a flat file database, like a phone book or even a spreadsheet for example. Just think of a mail-order DVD club for a minute and take a look at this first table here where we've got the name of a person placing the order, we've got their information like address, city, state, zip. Then we've got an order number for their order, the date it was ordered, there's the DVD title and the price.
Now what if a customer orders more than one DVD? Well as a business we hope that would happen, but what then happens in a flat file like this is we get multiple records containing duplicate information. So take a look at this table now, where we see David Rivers four times, the address is repeated four times, so is the city, state, and zip. The order numbers change, but down here, you can see that we've got the same order number because the same DVD was ordered twice, maybe one for oneself then one as a gift. So here we've got a very inefficient design.
In Microsoft Access when we talk about relational databases, we mean multiple tables and each table will pertain to a specific topic. So look at this example here where I've got all the customers and their information in one table separate from all of the orders that go into their own table, and then we've got a DVD Inventory table here that has all the DVD titles and the prices. All we need to do now is simply add some fields to each of these tables that will contain data that uniquely identifies each record, and when we talk about a unique identifier, we're talking about the primary key field.
Now the primary key is a field containing unique data values to identify each record in a table. So if you look down here at the Customers table, we've added the Customer ID field. Over here, we've got the Order Number field, and in the DVD Inventory, each DVD will get an ID that is unique. So in other words we will not have two Customer IDs in the Customer tables that are the same. In the Orders table each order will have its own unique number, and same thing for the DVD Inventory table where each DVD has its own unique identifier.
So because each of these tables is linked by a common field, and you can see going from Customers over to Orders, we've got the Customer ID field in here, and then from the DVD Inventory table we've got the DVD ID showing up in the Orders table as well. This makes them related or will be called a relational database. Now relationships need to be explained a little bit too because each relationship will consist of a primary table, sometimes called the parent, and a related table that we often call the child. The primary table usually contains the primary key fields.
So over here we've got Customers table with the Customer ID as the primary key, and over here in the Orders table you see Customer ID does show up, but it's not a primary key and that means that this Customer ID can show up many times in the Orders table. That's exactly what we want. We want the same customer placing many orders. Now when we talk about relationships, that's how tables are related, there's another key concept you need to understand. Typical relationships include one-to-one and one-to-many. Those are the two main types of relationships.
A one-to-one relationship exists when the primary record has only one related record in that child or related table. So here you can see we've got customer information and their address and then we've got a billing table for example, where a billing address would show up for each customer, and they're only allowed one billing address, so you can see we've got the primary key here showing up in both tables. Now, the one-to-many relationship is actually the more related records in the related table. So here you can see where we've got Customers listed in our Customers table, the Customer ID exists only once in this table, and that's because Customer ID is the primary key, but we do see that same Customer ID repeated many times in the Orders table as the customer places each order.
Notice also that the Customer ID here is not a primary key, not in the Orders table. So now we need to understand something called normalization. We need to be able to organize our data into smaller tables. And that's part of normalization, and the reason we do that is to prevent redundant data. We saw that earlier where a name and addresses and so on were repeated throughout a table. By separating them into separate tables we avoid that redundancy. That improves our manageability of our database, improves the speed and efficiency that we can work with the data, and then it makes creating or designing queries, forms, and reports that much easier.
You'll see this when we get in to Microsoft Access. So the things we need to do in normalization is determine how many tables we're going to need, determine what fields we're going to need, and then focus in on that primary key. There's actually five forms of normalization, but we're going to talk about the main three, that usually allow us to achieve normalization. Database design theory includes standards and guidelines, and they're called normal forms. to help us create a normalized database. So once primary keys are determined, normalization can start to take affect.
There are five normal forms, like I mentioned, and they have to be created in order, or performed in order, but most databases like I said achieve normalization after the third normal form. So that's what we're going to focus on: normal forms 1 through 3. So normal form one here, is where we break each field down to its smallest meaningful value. You can see in this table here where I've got a Customer ID, I've got names, addresses, etc. Well in the Name field I've got a first and a last name. In the address I've got the street, the city, the state. This is the same information in a table that's been broken down into those smallest meaningful values in separate fields.
Here you can see I've got a first name and a last name. The address has the street address, the city, the state and zip, and look down here where we've got family members. We've got separate fields for Spouse, Child 1 and Child 2. One of these records has blank fields, so here's someone with no dependants. So we'll keep that in mind as we move into the next step in normal form 1. We want to remove repeating groups of data and create a separate table for each set of the related data. So those dependants for example, if we separate them from one table into their own table, you can see we can just refer to the Customer ID and keep track of their dependants.
For example, if this person here, Customer ID 433, Dave Rivers, was to add a new dependant, there wouldn't be room in the original table, but over here in this table, we simply add another record 433 and the name of the dependant. It also means that we don't have any blanks like we saw in the previous table. So that's the next step in normalization. Then we get to normal form 2, and here's where we create new tables for data that applies to more than one record in a table and we add a related field, that's called a foreign key, to the new tables.
So here's that example again, where we've got customers and then over here you can see they belong to a store and the stores each have their own ID, so all we need to do is put in a store ID. Normal form 3 is where we remove fields that do not relate to or provide a fact about the primary keys. So in a table, you look at that primary key, if you see fields that don't actually relate to the primary key, you can put them off in their own tables and link those tables through a common field. So take a look at this example here where we've got Customers and then the Store Code is in the Stores table, and there's the link from Store Code here to Store Code over here, and then Dependants, we saw that in the earlier example where the Customer ID is related to its various dependants.
So here you can see the types of relationships we're talking about. In the Customers table, Customer ID is the primary key, so it will always be unique. That means we've got a one-to-many relationship. In the Dependants table we'll see that customer ID repeated many times, for every order, for example, or for each dependant as they're added. Also down here, you can see Stores. The Store Code is going to exist once only in the Stores table, but we'll see the Stores Codes showing up with the various customers repeated many times in the Customers table, so there's the one-to-many relationship.
So the 1 and then the infinity symbol represents the many. All right let's talk about a real-life scenario that we're going to use as we go into Microsoft Access and use some of these concepts and create our own database or relational database. Let's say we currently own several albums on CD, tape and vinyl, and we want to get those organized so it's easy to find songs or albums. We also want to be able the search by media, genre, the artist, the year or the decade, and maybe apply a ranking that we can use to look up songs and albums.
Maybe we want to be able to search for albums based on a star rating as well, so our favorite albums. And we want able to add new albums of course, at any time as we acquire new CDs tapes etc. So that's what we're going to do in Microsoft Access. Let's move on to launching the actual program.