 Hi, welcome to Microsoft Office Access 2013, Part 2. My name is Sandra Botegas, and I'll be your instructor for this intermediate version of Access. Now, I've been working with databases since the beginning of my career. One of the first programs I ever sat in front of a desk and tried to use was some data entry on Debase 2. So yes, this does date me back a little more than 25 years, but I've had the pleasure of working with databases for most of my career. And I've been able to see them to grow from very simple lists that we basically sorted and did a little data entry for, all the way up through the great tools that we have now in Access 2013. So let's take a little bit of that hands on knowledge and see what we can do with it. In this lesson, we have a chance to design a relational database. This means we go into the actual design components of a database in order to make all the relationships between tables and queries. We'll create a table, and then we'll relate it to some of the other tables. Topic A, relational database design. In this topic, we'll get a chance to look at database relationships, and we'll look at the design process behind it. All databases need a statement of purpose, some existing data that you're trying to organize. Based on that, we get to determine some fields and group everything together. So with that being said, we'll take this topic and move forward. A list is just a list. In order to make it a database, we want to take more from that list. Not just sorting it, but being able to query and attach it to other lists. So in this particular case, we're looking at a customer table. A customer table is the customer ID and all of their contact information. Well, we have customers because we're selling something, which means we have orders. So now we have an invoice table which tells us an invoice number to which customer the invoice went to, how much it was, and of course the order date. Well, based on all of this, with the invoice table, notice we talk about a customer ID. So we don't say here that this is Denise Sanders. We say customer 1107, we always ask them about their customer ID. So based on that, we're going to have to find that common field which is our customer ID field in order to find the rest of the information on our customers. So when you do a relational database, it's all about taking lists, just like you see here, tables and bringing them together when you find that common field. When you're designing a relational database, there actually is a design process. Now, anytime I've ever sat down to not design a database, meaning pen and paper or whiteboard, whatever I've got, I've always gone and regretted it. So in order to design a relational database, you want to make sure that you're very clear on the purpose of the database. Now, when I say the purpose of the database, I'm not looking at a database for customer-based information. You really want to drill out what types of information you want for each customer. Because when you start listing the types of information that you want from each customer, you're going to get a better idea of how to organize your tables, your queries, your data entry forms, and any relationships that might need to be there. So once you have the actual purpose written, you want to look at the queries, forms, and reports that will be needed, the fields that will be needed, the tables that we will store them in, and what's going to make each one of them unique. So when I do a statement of purpose, I always want to know who's going to use a database. Is it just for me? Is it just for my team? Is it something that's public? You got to know who's going to use it, because for one, you can start thinking about security of the database, who has access to what types of information, and that all has to be part of the design process. What kind of questions do they want answered? Well, you might have a sales team who want to take orders. You might have an accounting team who might want to track money, meaning what's been invoiced and what's been received. So you got to know who is using the database and what it is they're looking for. And of course, what types of reports do they want generated? So this statement of purpose is a very clear statement of, we're doing a customer database that manages orders, revenue, and maybe inventory even. But then you want to take each one of these and further define it. Once you have your statement of purpose, the next question is existing data. Do you have any information anywhere that will be used in the database? Now, it might be that you have an accounting information and an Excel spreadsheet. You could have a list of customers over in a different Excel spreadsheet. There's all different places that you can actually pull data from. So if you already have it electronically, then make sure that that's organized and looked at closely because you can certainly import it in. Sometimes you might have a paper version of it, which means it does require a little bit of data entry. But look at the paper very closely first and determine what fields exactly you're going to need. When you determine fields, these fields are going to be per record. So if this happened to be a customer database, then I absolutely want a customer ID, which is going to make them unique. I would want all of their contact information. But remember, we also have determined that this might be used for ordering or for accounting. So we have an invoice number, an order date, product quantity, subtotal, total. Well, all of this might be something that the accounting group needs. And then we have invoice number, product code, product name, and price to track the actual order itself. And maybe this is going to be handled by customer service. So the first thing is you got to make sure you define every single field that you think you might want to track information on. And then you want to help determine who's going to use that field or how it's going to be used. And that'll help you further break up the table. You always want to make sure that you would do it here to business rules. Now business rules means the data has to go through policies and procedures. But you want to make sure you also have business rules that are written for the database. Exactly what kind of information is expected in, what kind of information is expected out. You can write in a lot of constraints into this design process by determining what types of information is absolutely mandatory. And then once they start typing, what type of information would even be accepted. Once you have a full list of fields, once you have your business rules all adhered to, you want to make sure that you group fields by subject. You want to make sure your customer information is in one subject, your invoice information is in another, product information is in another. What you want to make sure that you do is when you're building tables of information, you do not want to have to repeat data. So an example, if every time someone orders a product, I have to put in the product number, then physically type in the name of the product, then type in the product description, and then type in the price. Well, the database isn't saving me any time. I should be able to take a product number, put it in the entry form, and as soon as I move to the next field, have all the other information populated that I needed. A primary key is what marks a record is unique. They are very, very strongly suggested, but they're not required. So if you have a database, let's just start with a customer table. And in the customer table, you want to make sure that every single customer is not repeated and cannot be duplicated, then you would want to have a primary key. Now a primary key might be the company name, it might be an actual customer name, it could be any of those, but it's whatever makes that record unique. You do not want to have two customers with a customer number of one, two, three, and you certainly don't want to have two companies in there spelled exactly the same. So in order to avoid this, we set a primary key. When you set a primary key, you will see here that David O'Leary is customer ID 1105. That's a primary key field, so there's no other customer that will be allowed to have that customer ID number. Think of it just like a student ID or an employee ID or even your social security number, one per customer. Well, that's your primary key field, but if you want to pull up all of a customer's invoices, then we have this foreign key. So you would relate the primary key in the customer's table to the customer ID key in the invoice table. And you will see that based on that, I can now look for Denise Sanders, who's 1107, and I can see that here we have an invoice for $104, and here we have an invoice for $421. Once in a while, a primary key is not enough, meaning what is going to actually make that record unique. So if the order ID isn't enough and you need a combination of the order and the product, then you would set the two primary key fields so that you make sure you don't have any duplicate values. So you'll see here that's called a composite key. So instead of just having one primary key, in this case, it's a combination of two. A composite key is two or more so that we don't have duplicate values. When you have primary keys, you can use those in order to link fields. So we now have two tables in front of us. We have a table for products and we have a table for product details. Both of them have the same primary key. There are no composite keys and it's not like I have product ID in orders. So we will see that under products. There's only one product ID with a description of the product itself and in product IDs, it's gonna be related. So based on that, we have what is known as a one-to-one relationship. It'll never be one product ID to many occurrences of it. Now an example of a one-to-many would be a product ID from the products table and then the product ID from an orders table which means more than once someone ordered product number five. Maybe it was different customers or different times. So that's an example of a one-to-many and that happens because they're not the same primary key. This is only when both tables have the exact same primary key. Remember the primary key ensures that that record is unique. So here we see a one-to-many where the supplier ID in the suppliers table is the primary key. It's used in the products table but it is not a primary key. So it's very clear that we now have a primary key and a foreign key and that's called a one-to-many relationship. So we'll see here it's the supplier ID. So the supplier ID, maybe it's Harris Incorporated but they supply us with product A, product B, product C, product D. So over here in this table, yes, we're gonna have a whole bunch of products that all come from the same supplier. So it's a primary to a foreign key, it's a one-to-many relationship. We also know that is a parent table and a child table.