Design a Database from an Idea to Application | Andrew Mehri | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Design a Database from an Idea to Application

teacher avatar Andrew Mehri

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

18 Lessons (4h 8m)
    • 1. Introduction to Database

      8:25
    • 2. Lesson 1: Database Needs Assessment

      2:55
    • 3. Lesson 2: Getting Started with Tables in MS Access

      20:59
    • 4. Lesson 2: Testing and Improving the Tables

      20:00
    • 5. Lesson 3: Creating Interfaces for Data Entry

      19:25
    • 6. Lesson 4: Improving and Designing Custom Forms

      13:37
    • 7. Lesson 5: Adding Flow Control for the Database Application

      23:15
    • 8. Lesson 6: Improving The Application Interface

      17:51
    • 9. Lesson 7: Exporting Data to another Database

      19:33
    • 10. Lesson 8: Improving the Design with a Query

      11:27
    • 11. Lesson 9: Refining Tables to Work with Queries

      9:28
    • 12. Lesson 10: Queries to Find Records

      19:26
    • 13. Lesson 11: Multiple Tables Query

      14:30
    • 14. Lesson 12: Queries for Calculating Fields

      8:13
    • 15. Lesson 13: Business Related Queries

      10:26
    • 16. Bonus: Building the Initial ERD from an Idea

      7:10
    • 17. Bonus: Improving the ERD for Ease of Implementation

      18:39
    • 18. Student Class Exercise

      3:04
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

18

Students

--

Projects

About This Class

In this course, you will build a transactional database from scratch.  You will be guided in a step-by-step manner on how to build the database based on a prepared model (ERD).  As a bonus, you will have access to how the ERD model was put together.

This database will keep track of products bought by clients and will keep track of the running inventory.  The database will allow for transactions and access to the inventory level and its monetary value.

You will learn how to build tables, assign field and their properties, manage primary and foreign keys to establish relationships between records, all based on the ERD model.

Most importantly, you will learn how to run queries to obtain custom results that you would want the database to accomplish.  As a bonus, during the design, you will also learn how to create interfaces (GUIs) that will facilitate data insertion or access.

Once the course is completed, there will be a course exercise where you will modify or redesign the database based on a shift in paradigm in terms of the nature of the products being tracked.  It will be fun!

e51fd134.jpg

Meet Your Teacher

Teacher Profile Image

Andrew Mehri

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction to Database: Welcome to the database course. I am hoping that you will enjoy the experience of designing a database that is useful. And at the same time allows you to get familiar with a special tools such as access in this case. And by the way, the skills you learn here in this course will apply if you were using MySQL or Oracle. So any relational database management system or platform will apply to any of the skills you will learn from this exercise. So I hope you will enjoy this and I will see you again in this session. So why databases? Databases are tools that allow us to, or mechanisms that allow us to keep records of information related to allow a lot of things. For example, in our lives, we deal with databases every day. So for example, when you go and purchase something than your purchases tract, uh, the the products associated with that purchase are also tract, which means the influent inventory associated with that product is also updated. So why is it important? Well, in case there are decisions to be made around these transactions or around this inventory being so dynamic. What's going to happen is sometimes you want to know when to order new products in your store. Sometimes somebody might audit the the transactions that were done at the store. So they might require or request the transactional side of the database, which will be associated to some date and a specific transaction or a collection of them. So the information held in the database could fall into many categories. One could be describing the product, the other one describing the transaction as an event. So what do we see databases again? So if you go on Amazon or eBay, there are databases in the backend. And what you're using is a web interface to access these databases. And you can create transactions from there. Once you have an account that is active, there are databases behind software that you run on your computer. For example. Again, if you're running a game, there is a database for the scenery. There is a database that keeps your scores. So when you come back to the game, let's say you, you, you, you start again wherever you left off. Okay, So that's why databases are very important. So what are we going to learn from this course? So I'm going to use for you MS Access as a tool. Wherever the learning that we get out of this will not be limited to MS Access. So MS Access is a platform which we call a relational database management system. So it's an RDBMS. So what we're going to learn is how we develop a database from scratch based on an idea. An idea. That idea is modeled with a diagram which we call the ERD, or the entity relational or relationship diagram. And so what are we going do with that database once we design it? What we can ask good questions. And those questions are going to be called queries. So what skills would you gain? Well, you're going to build your skills first by replicating what I'm doing or what I will be doing. And then you will expand on that through the exercise that's at the end of this course. Now this remind you it's not a full database course that will take a whole semester. In a typical course you will take in college with me or anyone. And it's not specifically about MS Access. However, we will be using MS Access as our main tool for developing this database. So what tools can you use if you're simply replicating what we will be doing in class MS axis will be sufficient. If you want to replicate the concept, however, which I prefer, means you were to have to do redo all of this using a different platform like MySQL, Oracle, or any relational database system that will walk. Suggestion do both. Of course, will be based on an exercise that will lead us to a hands-on approach to solving a problem. We will do our transactional database, where we will track products that were purchased by customers. So to design the database, you're going to need a tool. The tool that I will be using again will be MS Access. And what else we will be doing. We're going to have to define what the problem is. So why do we need this database? And then we're going to have to figure out what does the database we need to do for us. So why do we need the database and what we wanted to do for us, okay? So one is the problem domain, the other one is the solution domain. In the meantime, after you understand what the problem domain is, you should be able, typically an irregular course, complete course, a database, develop an ERD diagram. Now luckily for this course, I've given you a two bonus videos on how to develop an ERD. And at same time I'm providing the ERD. So this is the ERD diagram or the entity relationship diagram. As we can see here, these will be the entities or tables that we will design for this database. And key concepts that we will learn as well when we implement that ERD are, what is a table or an entity? What are tuples or fields? And what do we mean by primary keys or foreign keys or non-key fields? And what is a record or a collection of records, and what is a relationship. Now this is fundamental to relational database. So you will learn how to do a one-to-many. And then we will discuss cases of one-to-one or many-to-many as we go through the course. And lastly, we will definitely deal with queries which are related to asking questions to the database. Or now they're different kinds of queries. You could use SQL or structured query language or simply queries to build databases if you wanted to it. But in our case, we're going to use it too. Ask questions to an existing set of data or a dinosaur get to extract or filter information that meets our requirements and provides a solution that is adequate to our question. So let's get cracking. So go ahead and use MS Access. If you don't have MS Access, that is fine. You use MySQL or use Oracle. They all have a GUI or graphical user interface that is similar to MS Access to develop this database. 2. Lesson 1: Database Needs Assessment: Hey, welcome back. So let's take what the problem domain is. What we want is a database that will keep track of our clients and the products that we sell. And specifically the transactions that the clients went through to purchase some of these products. In other words, we have a moving inventory because of purchases. So let's say we start out with five toothbrushes. If a client bought, bought two of them, now we have three. So what do we want our database to do? We would like our database to be able to monitor our inventory level. And how about monitoring also the value of our inventory? In terms of, let's say we want to ensure our inventory in the warehouse at any given time. So we could at any given time, based on all the transactions that had happened, figure out how many items we still have in the warehouse and what is its value. Okay, So, or the value of the inventory. So basically that is what we will be doing with this database. So a few things. For example, some basic stuff we're going to be doing with this database is finding a client within our client database. So this will be a typical search. All of these, by the way, we'll be done with queries. So when we get to that level, will learn how to do queries. Let's say we want to find a product or we want to find out what our inventory level is or the value of our inventory. All of these are done with queries. Prior to all this, we're going to go through a whole exercise of building this database by building the tables needed to save all the records. The key here, we don't do it randomly. We have to have a design in place. And that's where your ERD diagram comes into place. Now I have two bonus videos on how to design the ERD diagram from an idea coming out out of from scratch. Basically, let's say you're sitting in a cafe, you have a napkin, you have an idea on how to build a database. You jotted down, and then later on you formally start figuring out, okay, how do I model this properly with using standards used in database design? And that's where the ERD diagram comes in. I will supply you that at the beginning of the lessons. As if they are called clear, it's already been done. However, if you're interested in the process, how it was done, there are two bonus videos at the end of the course that you can use to see how an ERD diagram was developed. Based on that ERD diagram. We will design our whole database accordingly. 3. Lesson 2: Getting Started with Tables in MS Access: So let's get busy with the actual database design and see how these challenges comes into play. So let me just get to my virtual machine in windows here. And I'm going to start Microsoft Access. Not notice that here I have two variations of it. You can choose whichever. So I'm just going to go for this one. Let me bring my keyboard down here. As you can see, I have the chores for a blank database, and that is indeed what I'm gonna do. I'm gonna pick a blank database, going to ask me what do you want to call it? I'm going to call it transaction version one. Okay. Where do you want to save it? And I'm going to go over here and I'm going to say under Desktop usually I have a directory where I save my stuff here. It will be under database. So you can see I prepared already a directory for this. I'm going to call it transactional version one right here. So I'm going to say okay, and I'm going to hit Create. Okay? Right, So let me just maximize this. And just to show you, if you go over here and you go to the Desktop and you go to code testing, you would see that under database, right? He had databases, transactional version one. I have an Access database already there. Okay. Created and modified on this date. Okay. That is today. Let me see. Yeah, today's daylight saving time, so it caught up with that, so it's not a problem. Okay, so now notice that the first thing it does, it creates an actual table, a default table called table one. Okay? We're going to rename this and actually make it more to reflect more RD, design the EID. So we're going to start with the client. So the way to do this, you're going to go over here on the left upper hand side where it says view here not you see this proc, this pencil here and the triangle in here. So n a ruler and so on. So that's your design view. So I'm going to click on it. Okay, right here for this table. Click here, and as you can see, it's asking me to save the table. And I'm going to call it client. Okay? Soon as I do that, it goes into this view which allows me to actually enter the field names, right, or the attributes of this entity. And if you look at here, what I want at minimum is client ID, firstName and lastName. And notice that it automatically puts a key here that is a primary key. Now I can turn it on or off like this by hitting these primary key here. And notice it automatically sets it to auto number. Now, if I click here, I have choices of datatypes right here. I'm going to leave it as an order number and not just call it ID. I'm going to call it Client ID. Let me just lowercase the client here, client ID. And just, and under description you could type PK here. This will have no effect on your database other than a comment next to what you're doing over here. And then here we're going to put the firstName so that our next attribute is firstname. Next attribute is LastName. So firstname. Now, some people like to put a spacing between these at when you're naming a field like this is fine. In most modern databases. If you had an older system, it's probably a good idea to either join them like this or put an underscore, okay? Especially if you have if you have to exchange some of these tables or explore them to another database that does not support as space for a field name between two words. Okay, So always a good idea to do that. And notice here it automatically takes it in as short text. Again, if you click here, you can see I have a whole set of data types that I could choose. I'm just going to leave it as a short text. If you go below here, it shows you that it's 255 characters for that field. We have a whole bunch of other properties below here, including it here. If I click on this area, more controls over here. So again, we will get back to this once we improve this design. Once we do certain things like validation rules, uniqueness of the field, all sorts of fine tuning that we may need for any of the fields or any of the attributes for this database. So at this stage, I'm just going to keep it as simple as possible. Just taken the default settings that the RDBMS throws at me. Okay, So next is lastName. Ok, and leave that also as a short text. Okay, so I'm good with that. That's pretty much what I have here. Client ID, LastName, so hit Save. Okay. All right, so in a fight, click on this to see what view do I have noticed now have a table that looks like an Excel spreadsheet that it's got columns, right? It's got choline ID, FirstName, LastName, right? So you can actually enter some clients here, right? So for example, let me enter myself here and under lastName, something like this, right? Notice it automatically assigned me an idea of one. Okay. So can I add more records? Absolutely. You could go ahead and do something like this, Henry. And then forward or you hear, right? Oops. You can go ahead and add more clients if you want. John. And this would be Glenn here. Okay. Et cetera, et cetera. Right. So Jane Goodall, et cetera, et cetera. Right. I'm not sure if I spell her first name correctly, but got the picture here. It's easy. As you can see, you have a field for firstName, you have a field for last name, and the ID is automatically generated. That's what the auto number is, four. So I have less of a field to track or to update at anytime it's done for me, if your business permits that, should your ID be unique or you have to enter them as a policy, then you're going to have to remove that element number and leave it easy either as an integer or a string, which could be a combination of numbers and characters. Okay? So we have client. Great, how about creating another one for product, okay? Okay, So we're way to do this. Do you see where it says Create? You're going to go to Create. Okay? And it's got a little, it's got whole bunch of create tools here. So if tables, queries, forms, write reports. This is typical to a Microsoft Access from its inception. It always had these three items or these four items, table, query form, and report. So with that, it's got different variations off or, or sub tools on how to design this. Okay, for our case, at this stage, we're simply going to click on table because we're creating a new entity. So I'm going to go over here. Notice it creates another tab here, my client is still on. You can turn it off. So that means I can go over here, close it, so it's not interfering with what I'm doing. Click here again. Notice initially it calls it Table 1, change it to product. Product. Okay? So that is the product table. And notice here, again it has an auto number. You could leave it as normal number. You could put an ID for the product if you want. Let's not do an auto number for product, okay? Let's do something like As Product ID. And let's make it a string, which means you have to enter the product every time you get it in your database, right? And that is okay if you have a limited spectrum of products which will be fine. So long QT, short texts is fine, store text is 255 characters. So we're good description for the product. Okay? Short text will be fine. Okay. Or you could call it product name. Okay. Ups, product name, underscore name. Okay. You could say a description here. Now, for the description, you could leave it as a short tax. You could also, some people might write a whole paragraph of things and in that case you may want to go to, let's see here, either long text or memo. I don't think this version anymore supports memo, but at one time, Microsoft taxes had a field called Nemo. Okay. If I go wrong text, Let's see. Then you could do something like that where you can actually enter more. I'm not sure if I can see here, there you the restriction, how much data or how many characters you can put in. It doesn't say anything here, but it does have a format. Let's click on it for a second just for curiosity, I don't see anything here. Again, you could press F1 to see what kind of formats are available for that. I'm going to just leave it as a long text is fine. And then how about price? Okay? So unit price. Now. In here, we have currency, so you could actually assign it a currency. Now, that's going to default to dollars unless you want to change it to something else. Again, you're going to have to go to the properties here and we could do that. Okay, so for now we're just going to leave it the way it is currency. Let's see what other fields are we missing? Unit price. Notice I added another one, which is product name, right? Description, unit price, and quantity on hand might be a good idea. But you're going to have to run a query against it to update it at all times. So every time there's a purchase, then the quantity on hand will have to be modified. We'll leave that for another version to make it more interesting. So for now I'm going to keep it this way. So I'm going to have to go ahead and save this. Great, as you can see, how my products, okay, so I could have a product ID, gizmo 100. Right? So as you can see you, but because I have a text so I can actually enter whatever I want. And that would be product name, would be PlayStation or play platform description, video game, something like this. And then the price, maybe a $100, okay, oops, that's 2000 hours. That would be an expensive one. Let's say you have another one, Gizmo to 100. This would be probably, let's say joystick. And that would be Description, game interface. Okay, We're gonna do it at $30, okay? Something like that. So let's say that's what I'm selling. Okay? Right. So I can keep going, but you have to make sure that this one here is unique. Now watch what happens if I do this gizmo to a 100 again, okay? And if I tap whatever, right, So whatever, whatever, whatever prices, I don't care, 100. And now I try to move my mouse to the next record and then I get an error. That's good because the RDBMS where it's doing. When I say RDBMS, by the way, folks, that means relational database management system. It's picking up an error and says, Hey, you know, you've designed the product ID as a unique key while you're trying to enter a number that already exist or a value that already exist. That's what it's saying. Okay, So we can't have this, so I'm gonna go ahead and delete this if it allows me to do things. So I'm just going to delete or simply close this. Hopefully, it won't save it. Yes. So let me just open it again. Okay. Perfect. So it didn't save it for me. So we have product. It's got two items. We have clients, Scott for people. Okay. We can close all. Okay. Now we're going to go ahead and design the transaction and the line item. So let's start with the transaction entity or table. Okay, to do this, again, you go to create and you're gonna create table, and you're going to go to Design. And you're going to call this one transaction. Okay? Now some people might make them plural, like client's products transactions, okay. So the transaction ID, I'm going to leave it as an auto number as I said, otherwise, you're going to run on it's going to run out of your hands, your case, you're going to lose control. So trans ID, I'm going to leave it that way. This would be a PK here. Okay, so I need a foreign key, client ID. Now remember my client ID in the primary table was an auto number. So in this case here, it should be simply a number. And just write f k for foreign key here. Okay, Excellent, So now what else? Well, we need a date. When was this transaction done? Right. Okay. So you could type simply date right here and pick and I it so it's saying you can't do this because it's a, it's a, a U, a unique name, right? So I'm gonna say, okay, you can leave it the way it is, but it's just giving you a warning. So I'm going to call it transaction date so that Randall get these errors again, a gap. So no a problem here. So now under this one, I'm just going to go ahead and say date and time. If you want to make this interesting, save the table at this stage like this, and then go to the input mask right here under the Properties, click on this button that has three dots on it. And notice here it's got variations of the date formats that you could pick. I'm going to pick short date and just hit Finish. So that means anytime I get into that field, it will automatically format that field as a short date, okay. So let's look at what else we have. So we have order ID, client ID, order date. So I've got the three fields. And again, we might find out later on that I need more fields. Remember this design process is iterative. So this is our initial start to it, and then we'll see what happens afterward. So I'm going to save this again and close it so I don't need it, so close it. And next is line item. So I'm gonna go ahead and design line item. So just again, go to Create, go to table. Notice it automatically creates a table for me. However I want this to be. Okay. So it's just type home here. I want this to be called line-item as a table. Okay, so I'm going to go over here. And again, this would be line item ID and leave it as an auto number. Okay? And so that's that field right here as your primary key. Okay? Excellent, So we need Order ID and product ID as foreign keys. So let's do those first. So the order in which you put things isn't that important? They are all depends how are you going to insert data later on, okay? So order ID, this would be a foreign key. Remember it was an auto number. So this has to be a number. Remember this one is a primary key, this one is a foreign key. Okay? What else? We have a product ID. Product ID was a string. So make sure that this one also is a short text. And again, this is a foreign key. Okay, Excellent. So what else? Well, this will link them all together. Now, we need to know how much of those products did you buy? Quantity. Okay? So for that particular product, for that particular order line, we want a quantity here. Now this quantity is simply going to be a number right here that we could use, right? Because we're going to do some math with that in our queries and aggregate query that figures out aha, okay, so what's the total, right? So in case we want to print an invoice. So we could use that to generate an invoice. Perfect. So let's save this, and let's close this. And let's see now how these tables are related to each other. And the way to do this is you're gonna go to Database Tools. Okay, And where did you see relationships here? You're going to click on that. And notice it automatically shows this window here where you can select the tables that you want to create the relationships between them. Just like we did here. This will enforce the referential integrity between these records. So let's go ahead and do this or these relations. So I'm going to add all of them. So what I did is I held the Shift, Shift key and click on the last one. So I'm going to add all the staples here and close. Okay. I'm going to make it look like my drawing. So I'm just going to move line item over here. And we're gonna move client over here. And I'd watch what I do, how I create the relationship. I'm going to take client ID and move it to here and match it out decline ID here. And notice it automatically assigns client ID decline ID, enforce referential integrity, do this. So you can actually see that you're going to get a one-to-many relationship. And sure enough, if you see, if you look carefully here I have a one and infinity. Now, infinity is what Microsoft uses as their symbol for many. Next, we're gonna do the same thing with product ID and line item. So I'm just going to move the product id to product id and enforce referential integrity Create. Now we're going to take the trans ID. And even though I call it order ID here, but the same thing. Notice it's called trans ID here and order ID, it's the same. So you primary key and foreign key as a lesson, do not have to have the same name as long as they are, they're of the same datatype and they reference each other. And then hit enforce, referential integrity, create, as you can see, not a problem there. Rdbms understood that the relationship between transaction in line item is a one-to-many. Okay, on that, we conclude this stage where now you can see that the prototype is ready to go. And the next video we're going to populate this database with some data to make sure that we don't have any anomalies. And then we'll start improving this database as we move forward. Thank you. 4. Lesson 2: Testing and Improving the Tables: For this part, we will make some improvements on our previous database that we created in part 3. So here's the file again. Let's go ahead and open it. Okay, let's look at the relationships between. So you go to database tools, relationships. This is where we last left it. As we can see, we have clients can have many transactions. Transactions can have many line items. Each line item will have a product, which means a product could have many line items. So that's the narrative for this, however, would like to improve this database a little bit on. Maybe as you're working with your client, your client might say, well, under product, I would like to have the manufacturer of the product in there to identify the product D furthermore, as it That could be an additional field. Okay. Or it could be a separate entity related to products. Okay. So that's one possibility. Other improvements, we'll be in the way the line items will handle the order ID in the product ID, where they will automate the insertion of these two fields. So all you have to worry about is, Okay, well, what is this order? And which product does it belong to? All you have to do after that is update the quantity purchased to complete the transaction. Let's look at the behavior of this database as it stands. So if I open client, the client table, at this stage, notice there are additional and additional column here that has pluses. This occurred after we've established the relationships in the database tools, which means after we've done this, we told the database, especially here in Microsoft Access, how these entities are related. And that is when you get this column here that has all these pluses. So how does that going to help you? If you click on any of them? Notice a goes down to the transaction, okay? So it is associated to a specific transaction. Okay? So for example, this one here is a transaction date. So this person here could have a transaction, let's say today, what is did a 03092020? Okay. That would be a transaction and as soon as I do that, notice I get a plus sign here. What it's saying is, under this transaction, do you have any line items? And if you click here sure enough, I have a bunch of line items. Except I would like to make the product idea little more useful right here, where if I click on it, I can pick my products. So I don't have to go back to the product table, open it and look at product IDs in there, right? So if I go to the product, I'll have to look at the IDs that are in here and fill the blank that's in here. Otherwise you're going to have a referential integrity issue. So let's suppose you bought and let's make a mistake on purpose. Let's say you bought tea 55, 56, 100 as a product ID, and you bought five of these. Now, we know we don't have T5, T6, 100. If you open the Product table, I only have gizmo 100 and gizmo Tuan, Right? So that would be a referential integrity issue. Now what happens if I hit Enter? There you go. It's going to say you cannot add or do this because you don't have such a product, T5, T6, 100. Okay, so I have to modify this to make this work. So let's look at the product. So only existing product ID. So as you can see, I have this back and forth going back to the product, looking at the id, identify the ID that I need to get this to work. So this could be gizmo and you going to have to spell it right to 100. Now let's see if I hit Enter, what happens? And hit Enter, fine, everything is fine now no more error message. And that's because this product ID exists. Now at loom nicer, if I had a feature, if I click here, I can actually pick a product from here. Okay, Excellent. So a couple of improvements at this stage. So we're going to include the product from here, right? So we want this field under, remember this table right here is item, line item, right? So we're going to make changes in line item where we're going to be doing what we call a lookup for the product ID that we need. That's one. Number 2, we're going to have to do a little improvement on the product table where we could have the manufacturer in there as well. Just in case we have a couple products similar but two different manufacturers. A price variance between them. So we could do some sort of research or in our own database in terms of what products are more popular from what manufacturer, and so on and so forth, if it's needed. So it just gives you more flexibility in terms of your data analysis. So let me close this, right? So it's asking me to save these, I'm going to say yes, right? So I'm going to close this. I'm going to show you, if you go to line item, you have an order ID which is one, which is the first-order ID to associate it to clients. So if I go to orders transaction, notice that order ID is for client one and it was done on this date. Okay. And that's how your database tracks everything. So you're not going to see, for example, under transaction FirstName, LastName of the client, you're going to see what references client, which is their client ID. So this is fundamental in a relational database. Okay, so let's go ahead and make some improvements. So first you need to close everything. Okay, you can leave the relationship window open. So I wanted to create another table where I have manufacturers. So first let me make sure I hit Save. I'm going to go create table. We're going to go over here. Manufacturer. Okay. And under manufacturer, we could leave a manufacturer ID. Not the manufacturer ID does not have to be an order number. In my case, I'm not going to have tons. Unless you have tons of them. You could make it a string or what we call a short texts, which means you're gonna have to enter it yourself. And this is definitely a primary key, as you can see the keys right here, maybe right here, manufacturer name, name. We could even have the address where that name is supplied as reserve or you can leave it alone. Or you could say manufacturer name. Not that it won't matter that much if you left it as name, even though you've got that morning and will still work. Manufacturer name, address, and maybe city, state, zip, maybe phone. So this way you can send him requests. You could have email, et cetera, et cetera. So as you can see, any information related to manufacturer could be here. Okay. So let's save that. And let's put some manufacturers. Okay. Let's say we have IBM 100 manufacturer, IBM International Business Machines. I don't know what city or state there are. I'm just going to say New York. Now some fields, as you can see it then not necessarily required. Okay. Let's say we have MSFT 100. This would be Microsoft. Let me spell it properly. Mike Crowe soft. Okay. That could be Washington. Next. Let's say we have this could be Cisco. Okay. Not sure what's take the rain. I'm going to type Maryland where I'm at. Okay. So let's suppose we have these three manufacturers, okay, great. Now remember, my manufacturers are not related to clients at all at this stage. So let me save the data that I have. Let's go to relationships and add manufacturer here just so you can see. So you could say show table and here's manufacturer. So I'm going to double-click on it as you can see here. Bring it here. I can look at all the fields. My idea is that the manufacturer will have many products. I don't have a reference here to manufacture the product, or what we call a foreign key. Okay? Remember your manufacturer IDs a string or text field. So we're gonna do the same thing over here. And it happened to be a short texts build off 255 characters. So you need to do the same thing on the product, okay? Except you already have some products. So you may have to force some IDs on the existing products for the manufacturer. Otherwise you're going to have a referential integrity. Okay, so we're gonna start with that. So I'm going to go to the product table and add a field. So I'm gonna go to design mode and add a field. You can insert anywhere you want. So I could do, I could insert it right above it. Okay, right mouse click, Insert Rows. And this would be manufacturer ID. And this would be a short texts as well. So again, product ID will be a primary key. This now will be your foreign key for the, for that references to manufacturer. I'm going to make it interesting where I'm gonna make this a lookup, which means it will, what you will do is you're going to have to look up the manufacturing that field. So if I run this, I'm going to save this here. As you can see, these are empty at this stage. Notice I am not going to establish the relationship between these two because if I do, I'll get an error. And what let's see if I do. And the reason is because I already have some products without a manufacturer. So if I do this and I say create, well, right now I didn't do the enforce referential integrity. As soon as I do this, then I get an error. So I'm going to say, okay, cancel lists, leave that line in there. I can come back, double-click on it, and then enforce referential integrity. So we know this is a tentative relationship between these two. That's the intent. Now we're going to have to make it happen by making sure the product table has some many factors. Whenever you have an actual product. Okay, so I'm gonna go back to design mode, and I'm going to focus on the manufacturer ID, where I am going to make it a lookup. A look up means instead of me entering it, it'll be nice if I could click on a button. And the field will, in the field, I will have selection of manufacturer I days. Okay. So how do you do that? You're going to go over here and you can say combo box. Do you say where I went? So first of all, let me show you where you will be. It will be something somewhere here. You're going to click on lookup. And then you're going to say, Okay, what do I want? I wanted to leave it as a text box, list box, combo box. These two typically is what you would do. Combo box is the way to go. Let's see how it works. Notice it says, where does the source is at a table or query? It's definitely a table, so leave it alone. Now you gotta tell it where. So you click here and you're going to say manufacturer. Excellent. So now you're gonna say What do you bounded to? While in many factor, the first column, number one is the manufacturer ID. How many columns do you want to display? Maybe two. I want to see the manufacturer ID and the name, just in case the manufacturer ID is too cryptic. However, it will only store column one in the product table. Okay? All right, so let me save this and see what it looks like. So I'm going to go to View here. And if you click here, there you go. Let me just even widen the field here. And you can see two fields here, Cisco, IBM, Microsoft, what it will store, however, is these the first field that you see here? So if I do MSFT, even if I click on Microsoft, it saves MFA, MSF 100, right? So that's the primary key that it stores. How about the joystick? Let's make it IBM and you go, okay, so now I have manufacturer IDs for our My, each one of my products. Now, let's save that. And let's go back to the relationship in if I double-click on that line. And this time I want to enforce the referential integrity. I should have no issues whatsoever. You just click on it here, right? And enforce referential integrity. And okay, I gotta close the table. That's what it's asking, is fine. So we're going to close this close manufacturer. Okay, double-click here. Enforce referential integrity, voila, and we're in good shape. So the manufacturer now has many products. So this is typically if you need to massage a new feature into this, you have to be a little careful because of their referential integrity issues. Okay? All right, so the same idea, we're gonna do the same thing with the product ID in the line item. Except this time they're referential integrity is not an issue. We just need to do a lookup under product ID for the line-item table. So let's go to line item table. Okay, we want this to be a lookup. So let's go to design mode. And we're going to go to order a product ID. And we want that to be a lookup. So watch how you do it. I'll go back here again. Very similar technique. You're going to go to a combo box, table or query indeed, and I'm gonna go to product. So this is going to come from product bounded to column 1. This is why your primary key should always be column 1. It makes it easy. And at the end, show me. You could, you could, let's look at product. You in the product we want. Column one, column 23. You could, well, it depends how big your table you don't want to over overdoing. So maybe 3. So it shows who the manufacturer, what the product ideas with a manufacturer is, the product name is. So you can do something like this. By the way, just close product or leave it alone. So column-count, I'm going to say three. Okay? So that way it will show me these specific columns, the three that I have there. So let's see how it works. So if I go over here, Let's see if I saved it under line item lookup. And it is a combo box indeed. Okay, so save it. Over here. It's under product id ego. As you can see. So let me just widen it up. When you click here. You can actually select whichever product you want to sell at that time for that order. Okay, good. So let's go ahead this time. Complete a transaction again. So let's close. Yes, save this. Let's close this. Let's go ahead and start with client. And let's have John Glenn purchase a joystick. Okay, we're gonna go over here. So this is a new transaction we're going to put today's date, which is 00309, 2020. Okay. And I'm going to hit Enter. And notice now I have a plus sign here, so I'm going to click here. And it's going to say, okay, what product do you want? I'm going to click here. I want a joystick and I want two of them. Right? So we can put that on the, on the lam lunar landing module. Hey, go. Alright. So there you go. Now let's suppose I want to purchase more items for, for John Glenn. You can't, you can click here. And I can go ahead and do our play platform. And right, so for some flight simulation and you go one. Alright, so now John Glenn bought two products. So he has one transaction, data 39 2020 with two products. So just working with tables and these little plus signs you have here makes data entry little easier. In our next video, we're going to use forms to enter data to it to, to do something very similar to this, but guide the user through some sort of an easier interface. Something there are no more familiar with, buttons, navigation arrows, and things of that nature. To commit data into a database. This requires that you kinda understand the structure of the database. So we make it easier for mere mortals to enter data into your database. So that will be the objective on the next video. Thank you. 5. Lesson 3: Creating Interfaces for Data Entry: Okay, In this part we're going to create forms so we can create an interface for our users to be able to insert records into this database. Let's look at the database tools here so we can see the relations between our entities as a review from what we've done last time. So for this part, I am going to create a couple forms. So it's not going to be the complete form set for the whole database. So I'm going to limit it to where we have a form that allows us to enter clients. And I am going to also create a set of forms that will allow us to update products based on the manufacturer for that particular product. So what is it I am not going to do right now, the transaction form or to actually manage the event of a transaction. We will leave that for the next part. At this stage, I want to keep it simple. I just need a form for client and I will need a form for the updating of products, either to modify or add a new product or remove a product. But at the same time keeping track of who the manufacturer is. Okay. So that is my objective for now. Okay, Well, let's get started. So the way you're gonna do this now you have 12345 tables. We're going to start with the client. We're going to create an interface for client. Let's look at client as a table. So we have six records in there. And so I would like to create a form that allows me to see those records, navigate through these records, and update these records. Okay? All right, so the way to do this, you go to Create. And notice here you have all your utilities starting with tables, which we've done before, queries, which we will do in another video. But we're going to be in forms. And forms. Really is where this application is really, really good. You have so many ways to create forms. What I'm going to do is go to Form Wizard. So I'm going to keep it really simple. So if you're not sure, a lot of times you're better off starting with the Form Wizard that has some default settings. And it will do most of the work that you want. And we're going to see for this instance that I'm going to modify it in any way. So first let's go ahead and create a default form using the form wizard based on client, based on its fields. And then we'll do some filtering to make sure that we only need, we only need the fields that we're going to that are editable. In our case, think about it. Firstname, LastName, our fields, we want to edit, client ID. We don't really want to touch because that will be generated since it is an auto number, right? That was in our design prior to this. So if I go to Design view for this one, you can see that the client ID is an auto number. All right, so we don't want as a user to be entering values in the client ID. Let the system handle that. So let me just go ahead and close all for now. Let's go ahead and create our form again. So Create and I'm going to go to form wizard. And as you can see, it picks up on which table you want. So you can click here if you want another table. In our case, we want client table, right? So table client, and let's include all these three fields. So you have this double arrow here that allows you to do that. So if I hit finish, all right, that will simply use the default settings for setting up this form. Otherwise you can hit Next and start customizing here in there. How do you want this form to look like? So on and so forth. Right now I'm just going to hit Finish. Okay, so we're gonna create a raw, simple default form, and let's do that. And as you can see, here you go, It's generated. So we have a form that has three fields and three labels. And it is called clients. Client. And notice that here under forms now I have a new group. I have client. And if you look below here where my mouse is, you will see that I also have one of six current records. So I now have navigation buttons, right? So you could click on Next, as you can see, it's moving through my records next, the next record next, the next record. Next the next record. So I have 56 66. Now what if I wanted to add a new record? So I'm going to click here or you can click here. So next record will give you a blank. Or new blank record. Let's do that, right? But notice here the the order of preference for the focus on a field is on the client ID. We don't want that because you could easily by mistake, enter a value that you're not supposed to enter. And remember the client ID is a primary key, that is an auto key. So we want the system to do that for us. So I'm going to have to modify the form. So this does not happen as it stands. If you give your database to someone to use, they might not know that and then create a problem. So for now, let's go ahead and create a new record, assuming we know what we're doing. So I'm going to leave Client ID alone. I'm going to go to firstName and I am going to do rubber. And last name would be Mitchum. Right? So if I hit Next, now, I'm going to eight of eight, but at least I know I have seven records. What can we prove that? Let's go to client table. Let me double-click on that. And sure enough, my seventh record is Robert Mitchum. Okay. So I'm going to close that. I can close the client now right there. Now if you want to open it, This is the way you would open it. Now, I'm not happy with the fact that I have a field here that can be edited and it could create confusion. So can we solve this? Can we actually customize this form at least at this level to make sure that this field, now it's up to you. Do you want it to appear or not appear? And if you wanted to appear, Can you make it read-only? And the answer is yes, you can make it appear here so you can actually see the ID is associated with the records, right? Or your clients. But you cannot edit. Okay, while you need to edit the form itself. So you can right mouse click on the form and go to Design View. Okay, Good. So as you can see here in design view now we're going to spend some time designing forms from scratch in another video. As you can see here, the system itself created, since I used the form wizard, created the forms for me, organize them and so on. But it assumed that the client ID field is editable. So notice I know I'm clicking on it here, right? And notice it's got some properties here, okay, on their Sandia formatting properties, data properties, events and so on. So again, we will get to see all of this, but right now, here's what I'm gonna do. I'm not gonna make it enabled. I'm going to leave it visible, but not enabled, which means you cannot edit this field. So I'm going to click here and say No. Now notice how it got highlighted in gray, so it's a read-only field. Okay, well, let's see if that took effect. So I'm going to right mouse click, go to Form View. And there you go. So if I try to click here, my mouse does not, has no effect on that field. Okay, Well, let's go ahead and add a new record. So I'm gonna go ahead and at the bottom here, add a new record. Notice the keyword new here. So if you didn't want this to appear, so it does not confuse people. You could have made it not visible, but right now it's a read-only. So even if I click on it, nothing happens. Let's go ahead and add a new records. Parents Hill. Okay, tell I watch a lot of Spaghetti Westerns. So here you go. So I'm going to click here. Let's see if terraces in there. And sure enough. And notice their ID is eight. It was auto-generated. Perfect. So now that's that part. So let me close it here. We don't need it open. Let's close this one also. And remember, it's asked me, Do you want to save the changes? Absolutely. Otherwise, the client ID field, we'll come back the way it was. So I'm going to say yes. Just double-click on it, double-click on it just to check sure enough, it's still read-only. So now let's go ahead and close it. Now I'd like to create a form, sub-form. And again, let me go back to my database tools, the relationship that includes manufacturing and product. So in this case, this form that I'm going to create will include two tables, or as long as they're related, we can do this. Okay? So this is going to be a form, sub-form. And I can do this with the wizard. So as I said for today for this video, I am simply going to use the Form Wizard. So nothing sophisticated. Maybe a few adjustments here and there, and that's about it. Okay? All right, so I'm going to leave the relationships here. Diagram the ERD. And let's go ahead and create. And I'm going to go to form wizard. And this time I'm going to pick on the one side, the manufacturer. Now what do I want from the manufacturer to appear? Okay, I can pick all of them. Okay, so this allows me at any given time to update a new manufacturer and then while I'm at it, insert some products. I ha, so notice here I have all the fields that I need for many factor. However, now I'm going to pick another table and it's going to be the product. And what do I want from the product? Now, it depends on the product ID here was it are auto-generated or not? I'll check. So that would be the product here. Let me just hit a cancel. I'll come back to it. Go to product and go to Design View. And no, so it is something you have to enter manually for the product ID. So that was important because I needed to know if I needed to include the product ID at all in there as a part of the sub-form. If it's auto-generated, it like you saw before with the client ID is just adds confusion. Unless you go the extra step and make it a read-only. Okay, so let's go back to Create, go to form wizard. And again, so we're starting over manufacturer. I'm going to pick all the fields, That's good. And I'm going to pick the product. And for the product, I don't need the manufacturer ID since they will be synchronized based on that. So I definitely need a product ID, product name, description, and unit price. Okay. So notice the only one that I opted out is the manufacturer ID, since it's a foreign key. Inside the product table, that is related or references to the primary key, manufacturer ID and the manufacturer table. It is there There's synchronized. I just don't need it to appear that as redundant. Okay. In now I can hit Finish. That would be formed subform automatically. Otherwise they will ask you, is that what you want if you hit next? Notice the first thing it's asking is, do you want this to be a form, subform, right? Form with subforums. Otherwise it will create two forms. There are really two forms yet, but, uh, two separate forms that are called linked forms. And again, it depends on your design and what you want it to look like. So I'm happy with this. How do you want to view your data by manufacturer? Absolutely. And not byproduct UK. You can go ahead and do it backwards if you want to test it out. So I'm going to leave it there and I'm going to hit Finish. So I don't spend too much time in the details that might throw you off at this stage. Remember, my objective is to keep it simple at this stage. So I'm gonna hit Finish. Okay? So it's going to generate the form. It takes a little bit. Go. There you go. Now, I'm going to have to make some visual adjustments for this. So because I have a scroll bar that looks like I have to scroll all the way. So I've got a lot of room on the right-hand side to make this little wider. So what it's doing, it's showing me that I have a manufacturer, like in this case, Cisco. Okay. I don't have any products, so if I have a remember the navigation at the bottom here, the next record shows me which other manufacturer do I have? I ha, but IBM, I do have a product called gizmo 200. Under the IBM. This is great because now the data is linked right, with that one-to-many relationship. Let's go to Microsoft. Same thing. Okay. So he's got Gizmo 100 in there, right? I don't have more manufacturers than that, but I'm going to make the product. Notice that this is the sub-form here a little wider. So I am still going to edit this form to make it includes some of the fields so I don't have to scroll like this. Okay, so I can go to Design View, right? And as you can see, let me line up. Let me just move this guy a little bit to the side. Here you go. And just make my form little wider. And look at this form right here, which is really this one. And let's see if I can make it. Actually. If I can drag this one right here, a go about this far for now. Now let's go ahead and to view mode. Okay, so we're gonna go to form. We can click here actually. Let's say Form View. And you go, I went a little too far, so I have extra fields here so I can narrow it down. But notice all the fields that I need. Product ID, product name, description, and unit price are there. So let me just make a small adjustment. So go back to form design, right? And just make this one a little smaller. Just by a little bit, just about right here. Now let's see how that works. So I'm gonna go ahead and form view. Okay. I'm I'm okay with that. I'm not going to sit here and unless you get your project, go ahead and sit down and do some quality work here. I'm just trying to push the idea here. What's going on? Now let's say Microsoft has one more product, cannot add more product from Microsoft. Yeah, Which is more gizmo 230. Another play platform. Maybe product name or call it mouse. If you want a go write IO device. Okay, then go ahead and put a price. And that would be, let's say $4. I'm good with that. Okay. All right. So these are your products. Now let's say I want a new manufacturer. I can click here. And let's put a new manufacturer. And let's say Intel. So this would be Intel not going to put addresses. I can update that later. But let's say we have a product ID, G 23, product name, micro processor. And then you can do a 64-bit wide processor, something like this, and then give it a price here of $145, right? So it's fine. Okay, good. So I was able to create a new manufacturer and a product under the manufacturer, I could do more products if needs be. But as you can see here, I can have a logical interface, right? Where I can actually update products at products, remove products if needs be, et cetera, et cetera, for any manufacturer or I don't have a manufacturer, I can add them here. So let me hit Save. And now what we're gonna do is we're going to conclude by verifying that indeed we have these products under the product table. So I'm going to go to a product table. And sure enough the Mouse's, they're IO device and I have microprocessor right there. Right. That is under ion TC. Notice it automatically sets up the manufacturer ID. Let's go to manufacturers and L. So I have a new manufacturer, Intel, right? And didn't add any others. So as you can see, it automatically updates for you as needed. So forms are really a great tool to manage your database. So as you can see, I have three forms really. I have one for client and two forms that work together called manufacturer and product sub form. This is the form subform. So the way you use the manufacturer of form is you're going to have to click on the manufacturer of form. Do not click on the product subform. Okay? So avoid double-clicking on products are formed, okay? Because what would happen that you will get something like this, right? That is not what we want. We want to make sure that it is related to the manufacturers. So you want to do this that way. You have the manufacturer plus the products that belong under that manufacturer. Okay, So on that I conclude this video. The next video, we're going to improve on these designs even more. 6. Lesson 4: Improving and Designing Custom Forms: Okay, so for this version, we're going to go ahead and complete the forms that we need so we can associate some transactions for existing clients. Notice in this case here, since my last recording app to lead it the client I'll put it back. The client form that is so let me go ahead and this will be a good review. I just use the Form Wizard for this. So here's the client and chose all the fields, and here they are. So I hit finish. And what you have is the client table being reflected in this form in which you can enter the clients. Now what I did last time is I also made this. So if we go to design mode, I made it where it's a read-only so the users do not change it. Okay. So enabled, just say no a go. And now if I play it again, as you can see, I have eight clients so far. Okay. And but if I try to click on client ID, nothing habits can't, you can't change that. And once you're ready to add a new client, Okay, it will automatically generated key for you. So let's have Kelly, the GAO for our next user. And we're good to go. So let's go back. Here. I, our users. Perfect. So now what I would like to do, Let's go back. Let's save this obviously. So we'll, let's go to the database tools score to look at relationships. Here, we want to create a form subform For transactions and line items. Then we would like to tie that up to client. Okay. So now can we have a form, subform sub-form? And yes, we can. Except you'll have to pay attention to how I do it. It's a little tricky. The form subform is not a problem. That's will do exactly what we did with the manufacturer and the product subform. So it's going to be very similar. So we go to Create, we're going to go to Form Wizard. And in our case we're going to pick on transaction. Let's pick all the fields for now. We can always adjust this. And then we will pick on line item, pick all the fields for now. We can always adjust that and hit finish. Okay? So what we have now is a transaction has many line items and I can scroll through my transactions. You can see I have two transactions. One that has two items were bought, I mean, at least two orders, right? By some client client ID3. So not all my clients have had transactions, only client one and client three. Fine. So now let me save this. I want to create another form. Now notice what I'm gonna do. I'm gonna go to Create and I'm going to go to form design. I'm going to go over here and try to insert the foreman subform right here. Okay. Except this form here, this one right here would be based on would be based on client. So notice what I'm clicking where my mouse is. And I'm going to say record source client. Okay, for my main form. Now I go over here and I can choose a field where I can go ahead and display my clients. What I decided to do is actually use a dropdown combo box right here. Okay. And let me just hit Cancel. I'm going to do it manually and let me just expanded. And in here, Let's leave it as combo 0. For now we can change its name as to what this is. If you click here, the name of the object is called combo 0. Okay? So we can leave it as combo 0. That wouldn't change even if you change the label here. That shouldn't be a problem. You could say client or choose a client. Okay, you could do something like this for the label. This is still, you know, as far as we're concern, combo 0 and you can change its name. Okay, combo for combination. Now what we would like to do is make sure that combo 0 allows us to see our clients, at least the ID, firstName and lastName. So first, make sure you know where it's coming from. Row source should be client, and it's going to be bound by column 1. Column 1 is the ID. Okay? Go to Format now and change the column count to three. Okay, now let's see the effect of doing that. What would happen? So if I click here and choose a client, this is what I get. I can choose any of these clients here. And any of these clients would have a transaction or a history of transactions. So to do that, we're going to take in the transaction form that we've done. Notice what I'm gonna do is just drag and drop right there. Okay? I can even move it a little higher. Let's see if we can do that. And a go. See if I can move this up. Oops, lost my transactions. Just put him here and then move this guy right here. Good. Now, what happens here? If you run this? They're not related yet, so you can pick any client you want. Nothing is going on here. You're not relating you haven't unrelated any of this soft forms to any of these clients. So that's our next step, is whatever client we choose, we want the transaction for that client plus their line items. Okay, so we're going to have to go back to design. Okay? So now that we're here and we have the sub-form, which is really a main form, the transaction highlighted here in orange. We can go to where it says data. And notice where it says link master field. Okay? So link master field, you're going to tell it that it's going to be combos. Well, so it's going to be this one, this object, right? Combo 0, this one. So combo 0. So let's go back here. And the link master field would be Campbell 0. The Link Child Field, leave that as client ID. That's what basically links these two together. Let's see how this works now. So let me just say for a second here, Let's call this form client transactions. And I'm going to hit okay. And now let's go ahead and I made a little mistake here. Let's see. I'm must have written out the name wrong way. Let's go back to Design and see if I combo with an L. That's why. Okay, Let's go back. And this is combo. This is why. It's asking me. For a parameter. It says what is amble, is combo. All right, so now let's see what happens. Let's go to Form View. Okay, good. So as you can see, client number for weapons to be Jane Goodall doesn't have any transactions. Let's go to Henry Ford. Same thing. Let's go to the first one. Now that one bought a gizmo on a certain date. And I believe number three also bought items. All others didn't. Perfect, So now it's all synchronized. Okay? So you can, from here choose an existing client and go ahead and do transactions for them. So the last one we had Kelly rip up. As you can see, the client ID is nine, Klein ID is nine. And we can go ahead and do our transaction. Today is 003. And what is it? 16 2020. And we can go ahead and assign a product. Let's say she bought a mouse. Let's say she bought one of them. Let's say she buys another thing, another item. And let's say a play platform, and she buys three of them. Perfect. All right, so hit Save. And now let's go ahead and see if indeed under transactions or we have that. And sure enough, Client ID number nine, on this date, I had had a transaction. Line-item will give us even more details. Okay. So these transactions were Order ID number five. And we have this item which happens to be a mouse. And we have this item which happens to be a play platform. So very much synchronized. So let me close all and let's start again. So for now, I'm just going to go ahead and type on client transactions. Okay. Everything is blank. I have no client selected, no transactions. I can go ahead here and pick any of these clients. Let's pick me here and add another item on the same order or add a new order. Let's say I have a new order. Okay. So today's date and that would be let's say what is today again? 0316 and 2020. So I'm not bound by the previous order. We're at a different date. And let's say I buy a mouse too. And this time I buy a 100 mice for my lap, a gal pair. Okay, So as you can see, so that user drew Prof. So the client ID here is synchronized. So when I refine this design, I'm going to remove the trans ID and client ID from being viewed. And I'm going to leave here the client name showing instead of just the ID. So there are a lot of improvements you can do. What I'm interested right now is the mechanic side of it. So that way I know that my data integrity follows the design that I had in mind. So here are my clients. Okay, and let's close that. Let's open the tables actually. So let's say here are my clients. And if I look under Drew, I have two orders at two different dates. And if I look at the last one, it was a mouse. If I look at the previous one, it was just gizmo 100. Let's see what that is. There was a play platform. Okay. So you can see I had two orders then. Let's go to Robert Mitchum. Robert Mitchum had nothing. Robert Mitchum has an idea of salmon. Can we enter a transaction here and go to the form and see if the result shows on that side. Yeah, sure We can we can go ahead and do 03 again, today's 16 2020. Okay. And so we've added it. So now we're gonna go over here and go ahead and select a product. And let's say Robert buys three microprocessors. Perfect. Okay, all done. Okay, so let's close this client table here, and let's go to the client transaction. And let's go to Robert Mitchum. And here there, as you can see, on that date, there is a there is an order that has one line item in the product. Okay, so I hope this helped configure. How do you do a little more, uh, one extra layer nested or how should I say form subform. So you have a form within a form? Within a form. Right. So yes, Three two subforums within one main flow. 7. Lesson 5: Adding Flow Control for the Database Application: Okay, So I'd like to get to queries or asking questions against the database. However, however, before I do that, I need enough samples or enough records so that the query's will make a lot of sense in terms of when I see the results. And if there are any anomalies, I can detect them and then make adjustments. So what I'm gonna do right now is a transitional phase in my design in which I'm going to improve the forearms we've designed in the last spot. Now, a quick review. Let's go to Database Tools and see what kind of raw entities we're dealing with and how they're related. So we have clients that can have many transactions, okay? These transactions will have many line items. Where in each line item you'll have a product. And that product comes in from the product table. Okay? Where a manufacturer has many of those products. In other words, a product will also have many line items. So that was the ERD or the entity relational diagram we came up with for this design. Then what we did is we went ahead and created some forms to be able to enter new clients, enter new transactions, updates per update products. But right now I want to consolidate them and create more controls within the forms. So they're a little more efficient when I'm ready to put some data in there. And the reason is, is I want to put data in the tables before I get into the, the queries, the part where I'm going to do a lot of queries. Okay, so let me close this one. Don't need this anymore. Let me show you what the client form look like. Obviously, we have navigations here, but I would like to create a button to close this. Instead of me going over here, you'll see it's a lot more efficient, lot quicker. Especially later on when I have a form calling another floor, we're going to have some sort of a main form with buttons in it that we'll call these various forums for us. Okay. A form of like switchboard. Okay. All right, so for this one, let's go ahead and, and design mode for this form here. So I go to Design View. We're going to create a button to exit. I could put it right here. Okay, so we can go over here, are here are my tools right here, right? So we can pick a button, right? And when I click on that button, what it will do, it will allow me to do a form operations. So I'm going to click on this button, bring it here. And it's asking me, what do you want? In my case, I want a form operation. What do I wanna do? I want to close this form. Okay? So notice that it has an exit door. We can leave that or you could say close or whatever you want. And I'm going to move it to the end here a little bit. So let me just move it right here. So if I go in view, here he is, and I click on this, it's going to ask me to save right there. So let me just double-click here again. And you can see that I have a quick close that. Can I do another thing? Let's go to the form again. Instead of scrolling all the way down to here where I'm going to type. Click on these to get to. Couldn't I have buttons here for navigation? Could nice setup some navigation buttons, or maybe below here. We can do it right here, under here. Let's go ahead and do that. Let's see. So we could do design view. Okay, and I'm just going to drag this one a little bit here. And we'll have, we'll insert some buttons here. So let me click on button. And this would be next. So this would be a record navigation. So I would say go to next right here. I notice it's got an hour. So we get yeah, that's a nice one. I'm just going to go over here and let's see if we could move it closer. There you go. And I wanna do another one that goes back. So let's go over here. Put it right here, record navigation. And we're going to go on record navigation right here. And we're gonna go back. So go to next, go to previous record right there. And notice the arrow is pointing this way, I'm going to hit Finish. Good. So it's matter lining it up with the other one. Okay, we can test them quickly. I'm going to add some new buttons in there for. So let's go ahead to Form View. So much, much easier to go through my records this way. Okay, here, that means I've reached the end of the line. Okay? So here I can go as far as I want. And if I click one more time, I get into a new field. Now, wouldn't it be nice to have a button here that says create new so we don't have to navigate everybody. So in case I want to create a new record, we could do that. Or we can actually control the behavior of the form and say on Open, go to a new record. So there are several things you can do here to do that sort of thing. So let me go back to Design View. So you could on Event here, unload right here, write a macro that says, if you want to do something like this, right? You could open up a macro here and say what you wanna do. Okay? I'm not doing this. So let me close it. I'm just gonna put a button here. So I'm going to make it really easy to remember this example. I'm trying to avoid writing code. Okay, so let's go back to here and let's go to the create buttons were in the forms. Let's see. Design hago buttons. And I'm going to click here and this would be record operation. Add. New record may go and I'm going to hit Finish. So this one will allow me to add a new record. So many shouldn't move this one right here. Okay. And could I have one that moves back and forth? Well, this is fine. You know, to the end or at one end or the other. I think this is fine. So let's go ahead and see what the design view looks. I mean that your form view looks like here they are. Okay, so this allows me to add a new record. This allows me to navigate. Okay, so let me save that. As you could see. But if I click here, it allows me to add a new record. And I can type a new record here. Let's say arrow. I'm not sure if I'm spelling Arrow correctly. Flick. Yeah, so now we have a new user and safe. Now, if I close this form, Let's go to the client table. And sure enough, I have a new record, so I have 10 clients in there. Excellent. So we're gonna do the same thing for the transactions and the manufacturer. So let's go to the manufacturer. Okay? So we could do something like that right here where we move from one manufacturer to the other. And notice here I have Cisco what? No products. Let's just move here. I have IBM. I have one product item that I sell. I have Intel, one product item, Microsoft, I've got a couple of items that I sell. And I have those are the only manufacturers I have. Now, since this form is not going to be used that much, you need to really just five, you're going to need those buttons here or not. I'm going to leave it alone. How the saw this one I'm going to leave alone. Let's go to the transactions. Now. This one, yes. This one every time. So notice here and it's opening in a new, which means it's waiting for you to enter. So unless I go to my clients, so here these are the transactions that I have already. I can go to my clients. And here are the transactions they had. Okay. So right now I have 10 transactions as, as, as you can see. Let's get back to the beginning. This is one transaction, one transaction to let me see what's going on here. This is not updating. Shouldn't have a go. Let's go to the beginning. These, these buttons here are not really working well, so that's probably a good reason to get rid of it or not use them. And so let me just close here and make sure that our transactions are not messed up. So let's close. Get back here. So we're gonna go from here and do our transactions. And if we go to Henry, he had no transactions. Let's go to John Glenn. Sure. John had transactions. And these other transactions, these 10 here are the clients. So we want to get rid of that. So really, what we wanna do is if you select and do anything here. I really want to close this form. That's all. But we can remove this part so we'd not confuse which client. Okay. Because as you can see, it's confusing and I'm still stuck on client three here. So even this here, it almost has no bearing on this field, so it's probably a good idea to remove that. So let's see if we can do that. So let's go to the form design. And let's see if we can remove the navigation and not make it visible for this one. Let's see if the navigation is in here. Navigation buttons right here. We're going to take it out because it leads to issues. So I'm going to just say no. I, let's quickly see if that helps. And let's go to Form View. And there you go. So no more navigation buttons at the bottom. That way you're not going to get confused. So the only way you're going to move use this form is you're going to have to pick a client. Okay? Since this was not synchronized to the previous navigation buttons. Okay, so here I can go to my various clients and actually do transactions for that. Now, a particular client might have a single transactions, multiple transactions. Let's see, whoever has multiple transactions, I don't have anybody here. I can navigate here. So let's see. I had one trip, I had two transactions, a dome. So for example, the client with ID1, which is Drew Prof, right, had two transactions. Now we can navigate with these right here, so we can leave this here. Here's one transaction and that's the second 12 different dates, right? Right there. Perfect. So what I would like to do is simply a button here to close this form. That's all I want to do here. So you can see when you're designing these things, you have to make some decisions. Not everything has to be uniform. I mean, it's nice if all your forms kinda look like each other. But there are some times rules that you want to enforce and or remove confusion. So you just want to make sure that you're forcing the user to do one particular task, would that form and nothing else so they don't get confused or you get callbacks after your design. So I'm going to go over here and I'm going to say this one will be a form operation. And it's basically closed form. Right there, that little door. Now, again, you could put a text here if you wanted to. So let me save this. Let's go to View. And here you go. So this allows me to close this cone. Perfect. Now, I would like to create a form that I would call a switchboard that allows me to select. Because at the end of the day, what's going to happen is you're going to have a lot of forms right here. When it be nice if you can centralize some form of access like a menu and decide what you wanna do. Answer is yes. So you could go over here and we're going to create you're going to create a form right here. And let's go into design mode. Now, why is it doing this? I pick the wrong, wrong thing, so let me just close it. It's going to say Save now, I'm going to say that let's go to Create and form design, not foreign form design. All right, So with this form design, what I'm gonna do is put some buttons in here. And I could put a label on top that says database switchboard or what does this transactional database or whatever you wanna call it. So you can go over here. Under design, you could actually have a label here that says what this is about. You could type it up in here. It's up to you what you wanna do here, client transactional database. Okay? So what's going to happen is that's just going to show as a title here. So if I do a view, that's what you get. Okay. So let's go to Design View. Let's put some buttons here. And these buttons are going to have a form. They're going to open forms, right? So we're gonna go over here, select the button. And this would be a form operation. And we're going to open a form this time and we're going to tell it which one. It's going to say which one I want client. And I'm going to say next. Okay. 8. Lesson 6: Improving The Application Interface: Okay, so for this part, we're going to repair some flaws that existed in the previous example or the previous version. So let's identify these flaws. Let's try to fix them. And a lot of them have to do with the way the forms were built. And most importantly, the button here for the switchboard. Let me show you here for a second. Or my intention at that time was to click on client at clients at products based on their manufacturer and then do some client transactions. This button here actually opened the wrong form. So if I click on it right here, as you can see, there is no way for me here to select the appropriate client. As you can see, it's opening just clients here. And that is the wrong form. So let me just click here. That is the wrong form. So we want to fix that. Let's click on design mode here. And what we'll do, we'll change the properties of this button right here. So you could click here design view, okay? And then click on the button itself. And then onclick, as you can see under the Properties you will select. So onclick, I can click here, what is it that you want to open? So the form name is right here, so you can change that at will as to which form you really want to open. And what I intended to open is the client transactions. It's matter of fact, I made even a bigger mistake earlier where I only had transactions and it was this way. Let me show you the problem was what it was. So let me close this and save this and open this. And then if I click here, did transactions, but I couldn't still cannot select my client. So let's go back, go back to Design. But I truly want this time the client transactions, not just merely the transaction, transaction for them. So I urge you to go to the previous part to see what I did to get through this point. And y, i, this stage I'm choosing client transactions. So let's fix some of these forms. Okay? So let me save this, close. It's going to ask me to save it. I'm gonna say yes. We need to save this as well. Let's go into View mode. Okay, everything is saved. So click on client transactions and out, that's much better. Okay? So as you can see here, I can pick any client. Okay. Like Frank Williams. And I can see Frank Williams had had some order on March 25th where he ordered a respirator 2000. If I click here, I can see what it is. Okay. And who the manufacturer is, is transmitted, okay. And purchase one of these. Okay. So some improvement I would like to do. So let me close here. Let me close here again. Let me open the switchboard again. So what I would like to do in the forums client, I want to add more buttons. Yeah, I want to go to the last record, first record. And I want this form to automatically open where I'm going to add a new client instead of just pointing to the first record. As you can see, I have 23 records at the bottom here. And I'm going to get rid of rate of this navigation at the bottom, since I'm going to have all the buttons you, okay, So we've got quite a work where it a bit of work to do here, so let's do it. So I'm going to go ahead to design view. Okay. I'm going to start with the last record. First record, or first record, last record here. We already have new record here. And then I'll change the form that when it opens, it goes to a new record. Okay. So first, let's go to the first record. So I'm going to click a button here. And I'm going to say this is a record operation or record navigation, I'm sorry. And go to first record. And you can see here you have an arrow where the vertical line that goes all the way to the beginning. So I'm going to just hit Finish. And the line up looks good There. May get it a little closer he go. Alright, so let's see if we could do the last record. So I'm gonna do the same thing. Just bring the button here and record navigation. The last record finish. Okay, So let me just move that button nicely in between water. And now let me save everything and just double-check and make sure that it works. So I'm gonna go over to review. My buttons. I'm going to go to the last record. Perfect. That's 23rd of 23. I'm going to go to the first one. I'm going to step one at a time. I'm going to step one at a time. Okay? And here I've reached the end of the list. Perfect. Okay, so, but if I click on this button here, I get a chance to enter a new record, but I would like to. So let me just save here, close let me show you what I want when you open client that I am not pointing at the first record, I want this to automatically goes into edit mode where you ready to enter a new client. So the client ID should say something like new. So to do this, let's go back to Design View. And I'm going to click here, which is the area for the form. And I believe I will say onLoad, we could do something like this or unopen. Let's see here. If it's going to be onLoad or on open, I'm not sure which one it's going to be. Let's see here. And we could do a macro builder and let's see if that would work. So we're going to pick, Go to record, maybe that. And it's going to say what kind. And here it says next, I'm going to pick new. There. Let's see if that will walk. So let me save that. Ok, close it. Go back to Design View mode. And there you have it. So let me close, Save. So I'm going to go to a client and there you have it. So that allows me to, again, anytime it opens, that I get a window here where I am willing to enter the new client. So it saves me a lot of timing to, instead of pushing buttons and doing all sorts of things. Now after that, if I want to add a new record, I can click on this button. So let's go ahead and add a new client. Jimmy Granger, nega. Okay. Now if I click on this, it's going to go ahead and create another new one. And then you can see at the bottom I have 25, 25. Okay, so let me just go to the beginning. Let me go to the end. Okay. So as you can see, I have a record. Now, just to eliminate confusion, I'm going to remove these navigation buttons here. So I'm going to go ahead and to design mode. Let's see if I can do that. And let's see. So we would have let's see, fits in here. All I'm just going to go over here. All just pulls out all of the all of them. Format might be the one to be specific. Record selector Diego. And then you can say no. And navigation buttons, you could say no. Okay, So no to these two. Let's see what happens. Okay? Alright, so I'm gonna go to Design view. And there you have it. It's nice and clean. No, no. When navigations at the bottom, you could still use the navigations over here. And that's the intent is to focus the user to use the buttons that you've designed here. So let's save this. Let's inspect the other forums and make sure everything is good product. I want to do the same thing with manufacturer. I want to be able to move across manufacturers and add products for these manufacturers. Ones are the same idea. So this could be, could be a good review here to there. What I did to improve these forms. So I want these buttons to be right here smack in the middle of the screen to make it easy instead of going all the way, I'm using a trackball. So it takes time for me to go from the center of the screen down to here. So I would like to keep everything kind of in the center. Okay. So either I'm typing either navigating, so I don't have to have wide motion to do anything and make things a lot more efficient. So let me go to design mode here again. So we're going to do all the buttons for navigation. And we're going to remove the at the bottom. So let me just go over realists start backwards. I'm going to remove the record selector and remove the navigation buttons. Okay, now we're gonna put some buttons right here. And this would be. Record, Operation. Go to first record. Okay, we can do that. And then we're going to do another one. Step backwards. Go to Previous a go, this one. So I'm going to put it right here and line it up and go to next go to next record. And that looks good. And we can put it here. Oops. And then I'm going to do go to last. You can always rearrange them. Get him closer. Good to last record. Finish right there. And next we're going to do a new record if needs be. And I think that's under record operation. And that would be add new records. So we're gonna go over here, say finish a go. Now we got little buttons here that allow us to do some of this navigation and adding new record. So let me just get this guy a little closer here and just buy little bit. Let's see how we doing with the others would be this one little closer, this one little closer. This one little closer here. I think I might have a Mac we distant out. I'm not sure. We'll see. So let's go over here. That looks good. Okay, so as you can see, I can go to the Transmit was my last manufacturer. We don't have a navigation at the bottom, which is good. I can go to the first one and I can step through all of them. And as you can see, I can step through my manufacturers and all the products they have. Okay, perfect. So if I click here, it will add a new manufacturer and I can add products under them and so on. So let me save this and let's make sure the next one is good as well. Client transactions. Perfect, That's all I need because here your selection is going to be based on the client that you choose. We will improve on this form may be where it would be a query, where you enter a client ID or a name. And then it will pull them out instead. Because if you have hundreds of clients, this can be very unwieldy. Another issue now with the design, so that's something that we can resolve for this form here. But let's say we're satisfied with all of this now. It's time to check and see if indeed we need to make any changes to this database or improve it even more. And I believe there's a lot of room for improvement. Let me explain. Let's look at the relationships between the so the relationships are fine as far as I'm concerned, except I don't have a lot of information on the client other than firstName and lastName. I would like to have addresses or contact information for client. And the reason for that is it would, if I'm doing a marketing research and I would like to know what product is selling best, in what area. Okay, So kinda get a history and trend so I can focus my marketing a little better. See who buys more, what kind of products and so on. Speaking of products. And the products are here, so are they belong to manufacturers. And I have quantities being purchased for each line item. However, wouldn't it be a great idea to initially, whenever I have products, to actually have quantity on hand here that gets updated every time a quantity gets assigned to a line item through a transaction for a client, then it will be a true how should I say a tracking database for your inventory control? And that could really work out. So maybe we could come up with a query, an update query that runs every time you commit a transaction, that means you're going to have to finish the transaction and do that. So an idea I have in mind is possibly, let me go to switchboard. When you do a transaction, you're ready to go. So it's not committed until you're ready to go. If you click here, I might add a macro for this button that runs the update query. And it will take this quantity that's in here, in this field, okay? And subtracted from the field under the product, which, as you can see here, I don't have quantity on hand here, but I would like to design one with a quantity on hand. So this tells me that we have some improvements to do. We have some things to do that's gonna take me to the next level. How would you go about can I redesign this? Can I make a copy of this database? Yeah. I mean, you could do a whole bunch of other ways to solve this. But for the sake of learning, what I'm gonna do is transfer one data from this database to a novel one that I would create separately. Transfer objects like tables and forms into another database. So that's going to be a really great learning opportunity for the next video, where we get to transfer data, pure data, into a new table. You create yourself and, or transfer an actual full table and forms and, and, and queries or any, any entity or objects that was designed in Microsoft Access over here, you can move it to another Microsoft Access. Another technique also I, again, like I said, what if I just wanted the data, right? Because my table structure might be different than the original table. However, some of the data bits in there is going to be important. I'm going to do that on the client. And we're gonna see what I'm going to do is transfer the client into a spreadsheet maybe, okay, into a Microsoft Excel spreadsheet. And then I'm going to design a new database that will extract the data back from the spreadsheet. So that's going to be a really fun exercise to do. And then value-added, we're going to have to transfer some tables out of the original database, this one into the new database, and then we'll take care of the design. So certain things that I'm looking for is that for client, I would like to add more fields For product, or these fields for the client will be the address cities of code and so on. For product, I want to add quantity on hand. Now, I could add them here and be done. But for, again, for the sake of our exercise to learn how to do migration and redesign based on a previous database without messing up with the other database or messing it up. This would be an excellent exercise snaps for the next, the next part. The next video. 9. Lesson 7: Exporting Data to another Database: So for this part, we will transfer data to another database and objects to that database. So this is our original database. Obviously I mentioned that I needed more attributes for the client so I can have better queries later on for marketing reasons in terms of analyzing what product sells, where, cell, where, the basket and so on. So that would be a nice one to have. Also, if I wanted to do an inventory control may be under product, I'm going to have quantity on hand is another attribute that will be updated every time we commit a line item as a transaction. Okay, So this is the structure or over our initial database design. Let's look at the files that we've created so far. So let me just minimize this for a second year. And we're in Transaction 3. This here just means the file is open. So I'm going to create another database and call it transaction version 4. I'm going to create in it my own version of the client's table that includes all the fields that I need. However, I'm just going to need that data out that in transaction V3, not the object client. Okay? All right, so to do that, the first thing you might want to consider is move the data that's in transaction V3 into a temporary storage area. That could be a text file, or it could be simply a spreadsheet, ideally, Microsoft Access or Microsoft Excel spreadsheet. So let's get to that. Let's move the data first. So I'm going to go ahead close this relationships. Close up, highlight client right here, and go to external data. And where it says, you see an arrow here, this white export to Excel spreadsheet. I'm going to do that and I'm going to export now, notice here it goes to my document, That's not what I want. And I am going to store it right here where I have coding testing. And this would be database, this is transactional. And it will be right here. And I'm going to call it client XLS. So we know it's a spreadsheet. It will save it with an extension as an Excel workbook anyway, I'm going to say Save. Okay, that's where it's going to be. I am simply exporting data, formatting layout and so on. I'm not that concerned, I'm just going to say Okay, and you go and then I'm going to close. Okay. Now, let's close this application right here. Notice now I have a client XLS. So if I double-click here, it's going to give me an error, but that's okay. I still have a spreadsheet. So all of my clients are right here, right in a spreadsheet. Okay, Perfect. So I am going to close it. Don't need this error message is not going to affect anything. I'm going to open a new database and import just the data into an existing table. All right, so I'm going to create a table and import data to it. So I'm going to go over here. I'm going to create a blank database. I'm going to call it transactions version four. Ok, and I'm going to tell it where to save it. It will be in the same directory. I again, I'm going, I'm gonna go over here databases. I see I saved it as a lowercase, so let me just do that transaction. Just to be consistent transaction and remove that plural there, a gift transaction version four. And I'm going to hit Okay and create. Now this is a blank database. So by default it creates a table for you. Go to design mode for that table and call it client. This would be my official client table. And for this new design. So I'm going to hit Okay. Now I want, this is important. Now, if you want to import anything from that spreadsheet, the field names kinda have to match. So this will have to be client ID. This would be firstName, lastName. First, let me go back to the spreadsheet just just to double-check. And I see I capitalize firstName and lastName. Client ID is a lowercase. Okay. It's just going to minimize the errors you might get. I mean, it's always, it's, it's doable to transfer even if the names are not the same. But I prefer that they are the same, so we're not going to be prompted with adjustments to make. So I'm going to say first lastName, and I know they're short text 255 characters, last name here. So this is as far as a resemblance is concerned right here. Next will be additional fields that I am going to design in my new database. Address. Just capitalize address here. City. So these fields did not exist in the previous database or in the, more specifically in the previous client table. I in the transaction version for state, zip, and a phone number, and an email. Okay, So here we go. I have all sorts of, so let me save that. And let's make phone properties to look like a phone. So I can go over it over here where it says input mask. As you can see, you have a phone number input mask. I can hit Finish. Perfect, save it. So that way when you enter a phone, the parentheses and the dash will be put in there. Not sure if they have an input mask for emails. It's not that important. Me just check. I don't see anything like that. Definitely zip codes. If you want to do the extended zip codes, if you wanted to, passwords will make it. So when you enter quickly, the character will be out a star, which is nice in case you want to create a password field. So you've got short dates, long dates on all sorts of things. So I'm going to cancel here. We're good. Now, if I run this, obviously is there's no data here. Okay? So I'm going to close this. Make sure if you have several tables, you're highlighting the table that you need the data imported to, to come to. So I'm going to go to external data to Stein. And I'm gonna see where it says Excel going in. That's the one. And I'm going to tell it where the file is and what I have is saved it under code testing, under Databases, under transactional know here he is. This is where I like to name my files in a way that I can quickly see them. Case I have tons of them. But I'm going to say open. Okay. It's going to say import source data into a new table. So source data into a new table in the current database, yes, that's exactly what I want or I could append. Okay. So I could append to client right here. Okay, so let's do that. Let's append to the existing client right now. Okay, and I'm gonna hit OK. It's going to analyze the fields that I have. It sees client ID FirstName, LastName sees this data. I'm going to hit Next. And let's see. First row contains column heading on it. Let's see, It's not showing that. Let me go back 1 second. It is client. Let's try this for a second. Next, under Client finish. Let's see if everything goes and let's close. Let's open client. Hallelujah, it worked. So I'll date I got transferred to the database. So just use the default settings. Except the what I did is I appended the data to client. Obviously, I had no data in client, so it appended to nothing. So if you already had data already in this client, that would have been perfect. So you're not writing over, you're just appending new data. Okay. So perfect. So we have a client ID that is a primary key. So let me go back to my design. So if I go over here to Design view, that is a primary key, so nothing has changed structurally. Okay, that looks good. Excellent. Now, what about importing other items? And this time I want objects not necessarily raw data. Okay? So we can go to external data. And we're gonna go to access right here. And we're going to tell it in this, we want transaction version three. That's the one we want. Import table queries, forms, report Macros and Modules Into the current database. There is another button here for link, which means any changes you do here affects the other database. That's not what we want. We want these two databases to be distinct. I just need the tables that are already there. So I'm going to say, okay, it's gonna ask me, what do you want? So notice here this is nice. I already have clients, so I don't need that line item, manufacturer, product, and transaction definitely on one of these tables. So let me hit the Shift key and select these. Okay, So that's great. So I'm going to say, Okay, and close. Here they are. Now let's see if it actually imported the relationship. Otherwise, I'll have to do it. So I'm going to go to the relationship here that everything is there. So it related everything for me. So let me just move the products and manufacturers for the imported items. The only thing you didn't do is obviously to the existing table that I have, which is client. So you're going to have to do that. So in my case, I'm going to go ahead and do show table. Poor client out of here. And you go now establish and enforce the referential integrity between these two entities. So a client decline ID, enforce referential integrity, create, we're good to go. Perfect. So now the, we're going to go ahead and import the forums we had. So unless you want to design those forms again from scratch, which is fine as well. Okay? But at least from a relational view, everything works. So let's test it out. Let me open client. And notice I have pluses here. So if I go to any of these and I believe I had Robert Mitchum ahead had some transactions. Here you go. I can see the transactions. I can see the line item. So everything works as if. So this is great in case you want to update, upgrade without copying and selectively choose what you want to import and go from there. And or improving an existing table, or import just raw data to it and so on. So hopefully this was helpful. Let's go ahead and try to see if we can import the forms. If they work great, if they don't, we can take them out and put new forms. It's not that expensive to produce. So let me just close this. Let's close this for a second. And yes, I'm going to save the relationships. Let's go to external data again. Good access. Same database, which is transactional version three, open. And I'm going to say yes, I want objects. So I want forms. Notice I don't have a lot of queries or anything like that. I don't have any any additional items in there. And I'm going to go over here and I'm going to say forms. I'm going to import all the forms. So I'm going to go from here, here, here, here, here, here. I went, everybody select all basically ok. And I'm gonna say okay. Okay. And I'm going to hit close it out. Now let's see if this switchboard will work. So I'm going to go to the switchboard and you go open clients. Perfect. And notice it opens it where I can go to a new client right away if I want to see my first client, everything looks good. My last client, everything is fine. I want to see products. Perfect. Same thing. One thing I said about this, maybe I need to modify it where I go to the last record in here. We'll do that in a minute. But for now, as you can see, I can go from here to here, navigate all my manufacturers in the products that I have. I will add a new feature for this table in the next video, which is adding a line item, I'm sorry, quantity on hand. Let's try it now anyway. So let's see if we can actually see if it will reflect on the sub-form. If not, we're going to have to redesign the form. So we can see that. So I'm going to go to the products and go to Design view and add quantity on hand. And what I'm gonna do is make that a number. Okay? And save this. Okay. This was never included in all forms, so I'm not sure. If I go to the switchboard and open the client transactions. I does not. That's just the quantity, the products. And as you can see, it doesn't show the field for quantity on hand. So what I'm gonna do is go to the sub-form and add that field if you want to see it right here. So if you don't want to do a major modifications to these forums, go to this sub-form design, which is this one. Okay, we're going to open it by itself so it's close. Everybody else. And let's go to the product sub form right here. And add a field right here for quantity on hand. Okay? So what I'm gonna do is this is still a form. And let's see if we can go to form design. As you can see here. So what I'm gonna do is add right here. So under design, we're going to add a field. So I'm going to pull a textbook here at texts, a text box right here. And bounded. So right now it's unbound and make this quantity on hand. Okay? So we could do maybe so quantity on hand. So here you could write quantity on hand. Okay? So that's the quantity on hand. And then make this right here, gets its data from the quantity on hand right here. Notice under data quantity on hand, let's quickly test it out. And you can see, obviously I haven't set any values for it, but it's shown. That's perfect. So let's save that. Close to this sub-form. Open the switchboard again and see if we can get it and go to products. And you can see quantity on hand now is showing, but what I'm gonna do is extend this a little more. So the quantity on hand would show. So one way to do this is to increase the size of the form a little bit. And it will be this one right here. And if I can get to highlight this, It's a little tricky getting the mouse pig right here. So I'm just going to move it little more, maybe to the end here, see if that's enough. Just go over here. Perfect. I can see it right there. So because I didn't want to have a scroll bar at the bottom here to go horizontally. Okay, so as you can see, quantity on hand, perfect lack an update L1 and put some data in there. Since this was newly created in here. So I'm going to save all this. Close it. Again. Products when you open it, quantity on hand will be there. Okay? So that means anytime you get a product, you can update your quantity on hand by hand manually. Okay? Eventually what I'm gonna do is I'm going to have queries that will update this for us anytime there is a transaction. That way I'll have a good inventory control system as well. Okay, so I hope you've learned a lot from this and lets the next improvement, we will start with queries. 10. Lesson 8: Improving the Design with a Query : Okay, let's make some improvements for this database. Not remember what we have added. For the product table, we added quantity on hand, and we also added these fields right here. So you have now an address, city, state, zip, and phone and email. Now, could we have done this using SQL statement or SQL? The answer is yes. We could have created a query, okay, to alter a table structure by adding more fields. Okay? When we get delve back into more queries laid one, I might go ahead and remove the quantity on hand and create a query that will create that field for you. Writing, writing it with SQL statement. At this stage, what we had done for the product table is we had gone to the actual design view and added the field right here. Okay? A lot of times you may not have an interface like this. And the only thing you have is a prompt where you can enter SQL statements to either create a table, alter it, drop it, whatever manipulation on tables are needed, change a datatype on any field properties, et cetera, et cetera. So for that, let's say the best way to see that exercise is to actually take this field out and create a query that will add this field right here. Okay? Alright, so I'll do that later. But for now what I'm gonna do is show you something interesting. So I'm going to start with my first query called an update query. So here's where it will do. Not notice that the quantity on hand, I ended up with blanks because I just created that field. Now, what if I wanted a query that quickly updates the quantity on hand for me to get started with this database. Okay. But because I don't want to go one at a time and start updating how many of these items I have for the purpose of testing. Now, if it is for the purpose of operations, then you want to be as accurate as possible as to what is or how much or how many items of that particular product you have. But for me and forth to create later on queries that will, for example, do some sort of transactions where the quantity on hand will be updated based on a transaction you've done on a client. Which means it will lower the number of items you have if you purchase two. So it will be, let's say you had 10, it'll be 10 minus 2, et cetera. So again, you're going to have to have an update query for that as well. But right now I'm just gonna do a fancy little update query that will automatically fat anytime I use it. During the design phase of my application is just so I can put some dummy data here right away. Okay? This is okay to do in case you have, you know, like, you know, hundreds or thousands of records, it probably will be a good idea while you're doing the testing phase of this with dummy data is to push one button or run one query that populates this automatically. And so hence I'm going to introduce you to an update query. Okay, So that would be the objective of this video. It's literally to help you populate for testing purposes any field you need, but all at once. So there are special a filters that I need. Is it a particular product, for example? Is it a set of particular products within a manufacturer? Not I'm not making a complicated, I'm doing it like for all. So that means I am going to use the same number for all of me. Right? Now. I could in the design right away, if I went into design where it says number here, I could have said that the default value instead of being 0, right? I could have said that it's 10. So right away every time you have a new product, you have 10 on hand. But that would create a problem later on. For the operational database that you're going to end up with if you don't, if you forget about this, and you forget about this default value and you leave it there. They're going to have an accurate count of there. So you really want something outside of this particular design here to update the values. So definitely I'm not gonna do it here. Okay, so I hope I've made my case not to do it here. Just leave it at 0. You just needed an external query that automatically adds ten items right away for testing purposes. Okay, So let me just close this. All right, so now we're going to create, so I don't need any, save any changes. Again, this is my ERD for this database. So let's go ahead and create this new query based on product that will automatically update that particular field with ten items. Okay, so I'm gonna go over here where it says Create. Again, record a query design. And this is what you get. I want the product table. I'm going to add that up, close everything. Now, notice the selections that I have above here. What kind of queries do you want this to be? Okay? This is a basic select query. This is a make table query. This is an append data or records. This is the update query. This is what I want. I want an update query. So as soon as I do that, notice a few things changed. It's asking me for a field, okay, I want quantity on hand. I want to update that one from product table. Update to what? I could put a 10 here right away, or I could say. So this is good in case you want to keep updating, right? So every time you're going to add 10 to whatever you already had. So this will be fun. I could say just simply 10, update to 10. And it would do that. So let me just show you what will happen when you run this. And it's going to say you're about to update ten rows, I mean 20 rows, I'm going to say yes, Just, just for giggles, what apps? Let me open the Product table. Let me double-click on it. And notice now I have 10 items right there for each quantity. Okay? But if you run this query again, right? So let, let's close again. And let's run this query again, right? So this is the query. And this is what I did, right? And this is the SQL statement, by the way, for it, Update Product and set product quantity on hand equal 10 semicolon. That's the query behind this. Okay, Let's, let me just save it for now. And let's call it add products. Now. As you can see now, this here goes under queries. Okay? So if I run it again, so let me just close it here and let me close the ERD so we don't get confused here. If I run it again, you're about to run an update query that will modify data in your table, you to say yes, okay, you're about to update 20 rose. And I'm going to say yes. Now if I go back to product, I still have ten. So it's not adding or just not accumulating. Wouldn't it be nice to accumulate just in case you've made changes to a particular quantity or a particular product, you dropped a five, you just want to add 10 again for everybody, just for testing purpose to make sure you have more, more, more products. Okay, Well, if you wanna do something like that, go back to that query and edit it. So I'm going to go back to here, and I'm going to go to Design view right here. And for this particular one, I'm going to go to this design view right here. Now, instead of just saying ten, okay? You could specify what you want. I could say product. Now, as soon as I say product, that's a table. So I'm going to click on it. And then dot that's going to tell me what is with item do you want from product? And I want quantity on hand. Plus, let's say we, from this point on, we only add five at a time. So this is what we call an accumulation, right? So this allows me to accumulate five items on top of whatever I had. Now remember I have 10. So when I run this, I should end up with 15. Now if I run it again, I shouldn't end up with 20. So this is going to behave totally different than my initial design for this update query. And that is because I update it using an accumulation method. So let me just show you here, zoom here what I did. So product, it's the table dot. Quantity on hand is the field plus this integer, which is your offset, right? And you're accumulating back based on the previous value. Okay? I'm going to say, Okay, I'm going to save this. Let's just close it. I could run it from here, but I'm just going to simply show you the product table right now. I haven't run it yet. So remember we have 10. And I'm going to go ahead to this, double-click on it. And I'm going to say yes. And I'm going to say yes. Now remember here, I need to refresh this. So I'm going to close it, open it again. And voila, now I have 15 for each one. Now what happens if I run it again? So let me just close this because you need to refresh your table to see what's going on. And here you go. I run it again. Yes. Yes. And now go back to product. And voila, I have 20. So whatever was able to accomplish here is with the update query, is to create some sort of an accumulation within this field. Now, I am doing this temporarily. This is not going to be part of the final design. This is simply so I can populate my quantity on hand at any given time. I'm testing my database so I don't go to 0, right? Or, you know, especially more enough created a lot of fake transactions to see how my mechanics of my whole database is working. And then I can clean it up and go back to the update query and update back everybody to 0 before I deliver this product to my client. 11. Lesson 9: Refining Tables to Work with Queries: Earlier what we're gonna do now is alter the product table. Either add a new column or remove the quantity on hand and show you how to bring it back. Okay? So let's say we want to remove it and then bring it back. Now we're going to do this in a query. Okay, Now I could easily go to the product table, double-click on it right here, and go to design mode. Right? And just delete this line here and we'll be done. Okay? Like I said before, you may not have access to this interface to alter a table. So the way to do it in as a query is to go ahead and create a new query. So let's go ahead and, and do that. So let me close the relationship diagram. Go to Create and go to Query Design. Okay? Now we already know which table it is, but let me just close here. Right now as you can see, I'm not including anything, so I'm going to write a SQL statement to actually alter the field that we have that which is quantity on hand and dropping. Okay, so we're going to drop that column. So we're gonna go over here. I'm gonna go to the SQL editor right here where my mouse is. When I click there, notice by default it gives me a select and semicolon. That's not what we want. We want to alter. Ok, table. And this happens to be product. And for that, we want to drop for now column. And we can say that column would be quantity on hand. Me see how I spelled it. And that would be yep. Capitalize. I like to make sure that I spell everything the same way. Some systems may be case-sensitive, some aren't. Okay? And let's put a semicolon here and see if this will do it. So the way to do it go to Design here. Now you will see a run button. So if I click on it, notice no news is good news. Okay, So when I clicked on it, nothing happened. The point here is I didn't get an error message. Let's go to the product this time. And notice now I have a missing the column quantity on hand. Now let's say I want to bring it back. Okay? So I want to bring back in a query, this a new column now since it doesn't exist. And I'm going to go to the query again. And instead of saying drop, you going to say Add. Okay, It is that simple. So you're going to say Add, OK. Now, when you add, do me one favor, add one more thing to that. It needs to be a datatype. Okay? It could be an integer number. So let's see how that works. Let me go to Design and click on this. The database engine could not update, so it's already existing. The alter table product at column quantity on hand numbers. So let's see what's going on. So let me just go over here. No, it hasn't done it. So let's go back to that query that I'm playing with. Close this table before you do anything. And let's try it again. So I'm going to go to under design. Hit run. Okay, good at work. The table was open, that's why. So now let's see what happened. I'm going to go to the product. And now I have quantity on hand as you can see. Okay, that's awesome. So now that I have quantity on hand, notice that the values are all empty. And remember I created a query that will add values to the right-hand this field. So assuming it's all 0 as a default value, let's see if the add product update query will actually add at least five items, since that is how we set it up in the previous video, right? So right now, I am just don't need to run this query anymore so I can, I can kill it. So I'm going to say Close, Don't Save it. Okay? I have quantity on hand as a field that was created by using a query. Okay, I can close that table now. And I'm going to say yes, change everything. And I'm gonna go to add products. So I'm going to double-click on this one and just can say you're about to run an update and I'm going to say yes. I'm just going to say it's going to update 20, rose, and I'm going to say yes. Now let's go to the product. That is because I have a problem. It never set it up where we had a 0 here, so we don't know if this was an integer. So let's go ahead and look. So I am going to go over here. It does say it's a number. Okay? Now, maybe I should alter this to be a default value of 0. Okay? Maybe it shouldn't be null, and that's probably what the issue is. I should have put a constraint there. So let's save that. And let's look at the values, okay? And as you can see, now, I get a 0 right here. I just want to make sure that all previous values will still work. So let me run it again. On I have to do is update the product. And I'm going to say yes. And now let's see what happens to the product. Okay, so these probably needed to be 0 to begin with. And I can go to the Update and set it to 0 and then add another value to what? So let's do that. Let's alter the update. So I'm going to close this. So I'm going to go over here and edited design view. And I am simply going to put 0 right here. And let's see what happens. So we're going to enter 0, make sure it's a 0 and I don't know. And run it and say yes. Now let's go to the product. And all of them are 0. Perfect. Now, let's close this again. I'm going to alter this where now r I'm going to increment by certain numbers. So again, I'm going to go back to the design that I had. I had a couple of choices. I could close now and not save what I've just done. Which means I'll automatically get back the update two to whatever I had written in there. But I can rewrite it right now. So let me just zoom here, show you what I'm going to be doing. And I can zoom right here and type what is it I wanted to do? So one is, you want the product table dot notice here it does not necessarily work the same way if I did product. Okay, it's a lot better here. It's much more interactive. And I'm going to click dot. Oops. Product. I should have selected what I saw their product. Let me try it again. Select it. Dot quantity on hand, plus 5. Okay, So whatever was in there, we're going to add five to it. So let's see how this works. So I'm going to go to design run. And I'm going to say yes. Now let's see what the product is going to do. And there you have it. So the lesson out of this is you needed a default value in initial value at least of 0. So just creating a field does not necessarily even know it's a number. Even though it's an integer, does not necessarily mean that you had a value in it to begin with. Okay? And we discovered that with the update query, it was not able to update these fields because these fields were not, they were not even 0. Okay, so I hope you'll learn a lot in terms of the behavior of how these fields properties are set and what you can do with your queries to make sure that they're running properly. Okay, so for the next videos, we're going to look at more queries related this time to the day that in our database. And basically, we will craft some queries that will make sense of the data we have in the database. 12. Lesson 10: Queries to Find Records: So for this exercise, we will start with some simple queries based on a single table to find records based on specific criteria or filters. So let's suppose this is our client table. Now remember if I go to Database Tools here, look at the relationship between the entities. I'm looking at this table right here. Okay? So let's close that back. And here it is, with some data. You can see there are some fields that are empty. We don't have probably information on the phone and e-mail. And but we definitely have client IDs which are primary keys, so those are mandatory. We have first names and last names, we have addresses, city, state, and zip at minimum as information related to each one of these clients. Okay? Now if you look at the table structure itself, if we go to design mode here, we look at the firstName and lastName. And I believe I should have made this required, but I didn't. Let me see here. Okay. This should have been a required field, which means you cannot have a client without a last name at minimum. We can change that right now just to make sure that is a constraint that is within that table. And I'm going to say at minimum you need to have a first name and last name. So let me just do that. And I'm going to say, OK, this one also will be required right here. Just making sure that these two fields are required. That's not going to alter the data that we already have. So I'm going to say yes. It's just means simply if I'm going to enter a new client and I skip, and let's go to the address here, whatever I type. And somehow I want to move to the next record. It's not going to allow me what this is good because this keeps the data integrity as good as I can based on what I can do with this table right here. So this is good. You must enter firstName and lastName for this to work. So I'm going to say no, I don't want this field. You can delete it. And let's see here, let's just do this. They just hit the arrow up here and remove that field so we're done with it. Okay, So now let me just hit Save, make sure everything is good. We're going to run our queries now based on this table. Okay, so now there are tools here in access to create a query. We've seen it using the create. And what you have here are, we've used the create a form before create a table. This time we're going to use a, create a query. So let me just close the table here. And there are several ways to do this. You could do a form, I mean, I'm sorry, a query design right here. Okay? And we could do, okay. All right, so let's go there. And this is what we call a cube query by example. So you're going to tell it what kind of query you want. Right now, it defaults to a select query. Okay, that's fine. What do we want? We want the table client, so let's add it and then close. That's all we want for now. So let me expand this a little more. These are the fields for client, okay? Now, if I run this query right now there's no fields have been selected. So if I say run, you must at least have one destination field. Okay? So as you can see, Could I have the FirstName as a destination field? Notice I've double-clicked here and it appeared here. The answer is yes. That would show all of the first names of all my clients. Okay. Now, if that is all you want from this query just to show the firstName of all your clients, right? This would have been an appropriate query. From a business point of view does make a lot of sense other than knowing your people on there on a first-name basis. Is there an SQL statement related to what I just did here with a query by example. Yeah. So if you go here and you go to SQL View, here he is. Select, Okay, Client dot firstName from client. So notice that the structure of your SQL statement, table name dot field name from client. And that's all. That's the only field you selected. Now let's suppose I wanted to select more fields. And I would say client. Dot last name. So I can, I can edit things right here. Last name. And let's see what happens when you do something like this. So let me run it. And there you have it. So you can modify the query from the SQL editor if you wanted to do that. Okay. Can we do it in design view instead? Yes, we can go over here. And notice now it automatically added the field LastName year. Can I add a city by double-clicking? Yes, we've done that with the firstName. Can I do it in a different way from here? Yes, you can click here and choose the field that you want. So you could do City. Now let's see how this works here. So I could go ahead and run this and you can see I have FirstName, LastName, and city. Perfect. So let's say I don't want any of these. You can just remove them. Okay. You could count them and you're done. Okay, now you could start over. Now, if you double-click on the Asterix, what happens? And you run this. This will show all the fields. So this is a quick way to bring down all of the fields with the asterix here. So basically it's saying Client dot asterix here. So if we look at the query for this area has client.read asterix, which means all the fields from client. And obviously that's where we get. We get a dynasty set. This is what is called a dynasty. It looks like a table, but it's the result of a query that has all of the fields that you want here. If that is what you want, if you want absolute o table here. So that is not what I want. Let's go ahead and be very low, more selective. So let me just cut it here. And I want firstName. Again, you can also drag, here's an element LastName here. Okay? I want city, state, okay? And I want to see people who live in Maryland for now. If I run this query the way it is, it's going to show everybody where all the states they live in. Okay. So if I want to I want to focus particular on clients that live in mountain. I've got quite a few year. Now. You could insert what we call a criteria right here. Okay? So right here you have a field where you can enter criteria. Now, the way I entered Maryland as data, it was basically something like this MD, right? So it was the abbreviated letters for the state all caps. So let's see if that will work. So if I run this and here it is, right now, head I typed Maryland, then that would not have extracted Maryland. Because it doesn't know what you're talking about. I don't have any field that actually have the full word Maryland in the state. And notice here when you get a blank like this, it means there are no records that match this criteria. Okay. But let's say your data entry was inconsistent, which means you may have typed Maryland or you may have typed Md, then that's an or statement. So you can go back here and you could say something like this. State again here. And then MD. Just in case you may have typed Maryland instead of MD, or you could do it. Now this is an end, I'm sorry. So let me just clean that up, cut and put it right below it, MD to see what it says right there. That's if we want an end. So I'll get back to end in a minute. So MD, so Maryland or Md. Let's see how it works. And there you have it. Now let's look at the query statement for this or the SQL query. Right? The structured query language associated to that. And notice the or statement here. It says where client state is equal to Maryland or client state equal to MD. So this is a good way to save. If you wanted a filter for all of the clients that lived in Maryland, this would be a good one. This gives you the option of incase user had entered the full state name in the field state as Maryland. That you should be you should still be able to extract records that match that criteria. And for my table, I've entered only MD. But this gives me a little more flexibility. An or statement. So again, let me go back here. In the design view. You just put it below it. That's an order. Okay. What if I wanted to see another one where people live in Virginia as well? So notice I said or VA below it so you can keep going. Well, let's see how that works. Perfect, so that USA, you could expand this query where you can find people who live in Maryland or Virginia. Now, obviously you could have written also Virginia right here, full, um, something like this in case a user had entered the word Virginia instead of VA. So I get the results will be exactly the same as you can see. If I go to the query, it's going a little bit, a little longer. Because my Where statement has to take into account all of these or statements right here. So every possibility and permutation of the state value for these two particular states. Okay? All right, so again, the syntax would have been select whatever fields you want to show. In my case was FirstName, LastName and city and state. From. Again, this is important because you can, okay, where did these fields come from? From this particular table? Why? Because these fields could exist in another table, the same name, naming convention you used. Let's say you have salespeople table that has first name and last name as well. We want to make sure we're not confusing firstName and lastName off salespeople with our clients. Okay. So you have to be very specific as to where or where are those fields. And then the next line, by the way, you could have written all of this in one line. But it makes, it makes it nice and clear as to how you're structuring this query here. Okay? So basically the selections, the four and the where statements, each one is in a separate line. And notice everything ends with a semicolon at the end, indicating that is truly the end of the whole line or this query right here. Okay. Again, rhyming it as you can see, I could get the state of Maryland and Virginia with any permutation off the stew, either you write the full state Maryland or this full state, Virginia. Okay, so let's see what else can we do? So let me just clean it up. I can take all of this stuff out. Again here. And let's say I want also zipcode. And I want to find clients who live in 21218. Alright, well, let's see how many clients do I have? There you go. And that zip code, who happened to be in Maryland. Okay. So you've got three people who live in Baltimore at 21218. Can we make this query now a parameter query? What do you mean by that? What I mean by that is, can we have the query ask the user to enter the zip code at runtime when you run the query. Okay? And the answer is yes, you could do something where the parameter is set up this way. So notice here I'm typing. Let me see if I can zoom here. So you can see what I just did. Open bracket, close bracket. Let me see what happens here. As you can see, enter parameter value, and I'm going to type 21218. Before I do that, let me just cancel it here. Let me just remove now this value here, we don't need that. We only need this. So let's run it. And I'm going to say 21, 21, 8 is my parameter, right? And sure enough it shows me anybody living on 21218. Actually, this is a mistake because right now what it's doing, it's assigning 21 21, 8 to all my records. So let's fix it. Let's fix this here where the criteria actually is done down here. Right there. Okay, so notice the brackets, it's down right here. So let's see what happens. Okay, So enter the parameter 21218, exactly where you want it to be. Okay? And there you haven't much better. Okay. So you saw the first mistake I made is I tried to put the bracket here. Indeed, you're going to get a perimeter. You will be asked to enter parameter value. But it's for the wrong thing. Okay, So it's good. I have to be right here. Okay. So and that is based on this criteria. Could I do it for state as well? Could I go over here and do a bracket? Bracket? Okay, Again, this is called a parameter quake. So let me run it. And in this time I'm going to tie DC. I want to see people who live in DC. And I'm going to hit Enter. Sure enough, I have two people who live in DC. Okay. So again, I'm going to run it again. So let me just go ahead and now. Okay. Oh, for the, for the, for the SQL statement, as you can see, the brackets are still there. Client.stop equal whatever parameter you are going to enter. So let me run it again. This time I'm going to type California. Okay, so let's see how many people live in California and sure enough it filters out. All right, I get a split Los Angeles better, but as you can see, but there are in California. Okay, let's go back to the design mode here. Okay? Now, let's say I'm going to insert client ID here, right? And I'm gonna do this right here for client ID, where I am going to run this query based on the client ID, right? Let's run this. It's going to say enter parameter value. Now in here I can say what you want in terms of. I can actually adjust this where it says enter an ID instead of enter parameter value. This is a default value for this window. But anyway, let's say I type five, I know I have a user five. And here she is. Now let me go back here and type. I believe this is where you type it. Enter client ID. Let's see if that is indeed where it will appear. Invalid syntax. So let me see where to do this. So this, what happens when I do these on the fly? I am sure there is a way. Enter ID. And I'm going to do it right here. Enter a client ID. Let's see if that is how it's going to work. So let me just run this datatype mismatch. So it's looking for specific datatype here, so we just do it here, enter a client ID. Let's see if that will work. Hey, you got very good, so I've figured it out. So enter a client ID, just put it right here. So let's put five again just to see if we get lined up. And here she is. Alright, so let's go ahead and run it again and put somebody else, ate and see what we get. Excellent. All right, so it's working just fine. So all this, I conclude this part. We're going to add more queries as we go forward. And we're going to have to decide on queries we're going to be saving, right? So for example, let me save this one and then we're going to, in the next video, we're going to save a lot more queries than this one. Let's say we save this one and we're going to name it, find client by ID. And that's how it's going to just do by ID. So let me just go ahead and save that. Let me close it again. And here he is, right here. Fine. Kline by d Phi. Double-click here, it's going to say Enter a client ID. Fantastic, so I'm going to enter three. And here's that client. Okay. 13. Lesson 11: Multiple Tables Query: For this part, we will develop a query that will use several tables. Okay? So it will be a multi-table query. We will first create a query that will associate transactions to clients so we get to see which clients at least had had transactions. And then the second query after that is, we'll include a third table called The line item. So this way we get to see how the joint command or the joint syntax within the SQL statement for that query works when you have three tables or two tables. And then we get to see what is or the where statement or the where clause within that SQL statement that controls the relationship between them. In terms of synchronizing data from all these tables. So there's got to be a common field that can be used synchronize such data. And indeed, that is the purpose, again of a primary key, foreign key, primary key here and again as a foreign key here. So what we're going to find out is that we will have corresponding records as long as the foreign key exists in the many side, because we have a one-to-many relationship here. So this would be the money and the same thing. We have a one-to-many relationship and this would be the many. In other words, you have to have a client ID that already is in here and here for records between these two tables to show. Okay, so now let's go ahead and demonstrate that. So we'll go ahead and do a Create. And we're going to go to Query Design. We're going to pick on client first. I'm just going to add that table for now. Okay? So now are you, I mean what we call the QBE or query by example. Okay? We haven't done anything. If I just double-click on the asterix here, Let's see what happens. Okay, So if you run this, this will run all fields. So this is a select star from client and there's no where clause. So let's look at the SQL statement again. And sure enough it's select star, or in this case client.stop, which means all fields from client semicolon. Okay, now let's go back to the QBE or query by example and redesign it. Where I would like to see more specifically a FirstName, LastName. And that's good. So if I run this again as a review, this would be a select Client dot first name, client dot last name. Oops, I have to first names here, so let me just adjust that last name. A guy from client. So if you run this again, you will get all the records by selecting only these two columns. Again, if you look at the SQL statement associated to this, that is indeed what you have here. Okay, now if we were in Oracle, you will see Select FirstName, LastName from client, okay? Except here on the, on this platform there Lu more specific. It's Client dot first name, client dot last name. Okay, That's the only slight difference there. So let's go back to design view and this time. Okay, So we saw that it pulls all the records. I'm going to add an additional table. And let me go ahead and say Show Table. And I'm going to pick on transactions. Click Add, and close. Notice it automatically establishes a relationship between them. Okay? Right, that links these two table as a one-to-many. So this primary key has a foreign key here. You cannot have a record here that does not exist here. So this in a way, when we create this joint field between them, will limit the number of records. You can see. Aha. So if I run this the way it is, without adding any other fields, I'm going to have a limited number of clients showing up in here. And and and indeed, those clients will be the only ones who have had transactions. Well, let's see Let's see if that's true. Sure enough, I got the list shrunk. Okay. So these are people who had had transactions. That's the only reason they're here. Okay. So this is a good way to show okay. Which people bought stuff from me. Okay. So if you had to answer that question, all right. So we can save that and say. We name this this. Notice. I'm I'm not even worried about the day. If you want the date, you could add it. And it shows when they had purchases with you. Now, had they had more than one purchase that then that will be several lines of these people. So let me just run here. Here it looks like each one had one big transaction and probably multiple line items. Now, you know what, I am going to go back to the application and pick one of these, let's say Kelly rip up and have her start a whole new transaction and see if we get a new record here. So I'm not going to kill this query right now, just leave it alone. I'm going to go back to my application. So go to the switchboard, go to client transactions. Pick on Kelly rip out here, and I'm going to create a new transaction. I'm going to put today's date. And what is today's date? 0411. Oops, 04 here. And that would be an 11 2020. Let's say she bought a couple of products here. And let's say she bought a router and let's say she bought two of them. Okay. And let's say she's she wants to do some networking. She want bought a switch. And let's say you bought three. That's pretty sizable network right there. Okay. So then so we've committed her to a transaction on 411 with two line items. Now let's see what effect that has on our query. So I'm going to close this, close the application altogether. Obviously I need to refresh this. So I'm gonna go back, go back to Design View, run it again. And notice now I have two entries for Kelly Whipple. I have one here for 316 and I have one here for for a living. Okay. So yes, when you have a diner set out of a query, you could have replicate records here that might look like replica records. This is probably why it's a good idea to maybe add a field to show why. In our case there is a date associated with that particular purchase. Okay. So but if you're simply interested in people who had just purchased stuff, not necessarily when did they purchase. Then you can go back to design view and just take out this thing here, which is the date. So let me just cut it up and let's see if we run it. What happens? It still showing here. So it's silent. Okay, but if you do want the date, just put it in there. Now. We could filter it out where this is all unique later on as a career or do or count of these people. But right now let's keep it simple. Let's go back to Design View. It makes sense to leave a date here. So the data makes sense. So again, notice here it's giving me choices, would 11, which fields from which client or transaction fields. And I want, I can double-click here or right here where it says transaction date and it will automatically be here. So this makes more sense, so I'll leave it here. Okay, good. So while if you wanted these to be unique, you could. So that means there won't be any repeated lines here. That's another query later on, we could do just to find as long as you encounter one instance of that particular record or that particular ID, then that's it. For that case, probably it's good idea to include the client ID. So that way you can filter based on one item. So you're not all over the planet, so you can go back here, bring up the client ID, put it right in the front here. Okay? And now if you run it, obviously you're going to see it twice 9 and 9 here. But we can concentrate on this field, right? To make, to make the whole dataset. Just show an remove the date and won't need that just to show just clients who have had transactions. I don't want to see him again right now. I don't want to see him in a second line. That can be done, but not now. So now we're good here. So this shows what transactions, what date they were There were done. Can we go further and see what line items? And the answer is yes, you can go back here, you can add another table. Go to show table. And go to line item and watch what happens. So line items, let me just close this. Just move it over here. Again, just like our ERD, right? It's a slice of our ERD as seen here. Now, this is going to be interesting. So you're going to see more repeating lines because you have several line items, a gum. All right, so for example, rip up had had two line items on that date. So again, it will repeat here. Okay? And again, this would repeat here. Okay? So if you're gonna do something like this, you might as well show what item items were purchased, okay, so go back to the design and then you may want to have a product ID and maybe should show a quantity here. Okay, so let's see how that works. And there you have it. So this will give you a nice little report that will show you who your clients were that had a transaction. What product did they buy and how much of it. Okay, now this query is a little more complete and we can save this one now as products or purchased products. Okay, so you can call it that and save it. And it will update every time. So here he is, purchase product. Let's go ahead and see if it will update. So let me close and let me close the ERD here and run the application again. Now know pick somebody else that I didn't have before. Let me just see who didn't die, not have. So let me just so I am going to pick maybe 1919. I don't see Client ID 19 here. So we're going to sew whose client ID 19. 19 is Mike Mark, earnings. Okay. Got it. Okay. So let's close all Let's go to our switchboard. Let's go ahead and do a transaction. We're going to make a pick on 19 right here, Mark earnings, and we'll have a new transaction today, right? So again, 0 for what is today 11, 2020. And let's have marked purchase. We could expand this a little bit to see what, what you're buying. A hub, right? So we're into networking. So we pick a hub and let's say that person purchases one hub. That okay, Now let's close everything. Let's go to the purchase products. Right? The, the query purchase products. And there you have it. The last update. Okay. It shows that we have client ID 19 marker rulings on this date, purchased this product and purchase one of them. Okay, The next part, we're going to investigate even more queries. But before I do that, let me just show you the SQL for this. And there you have it. This is what we call the joint inner join right here. And inner joint right here. So again, we're joining based on specific where conditions where the transaction client ID equal to client, client ID. Same thing here. Line item order ID equal to the transaction trans ID right there. Save. And let's close it. 14. Lesson 12: Queries for Calculating Fields: Okay, so now we're going to design a query that will calculate the total, uh, first of all, the subtotals for the product purchased per clients, and then the full total. So we'd like, for example, to know per product, how much did we sell and then the full total of all products based on the fact that we have a line item that is related to a single each collection of line items related to a single transaction. So clients can have many transactions. Each transaction can have many line items. Within the line item, there is a specific product that comes from the product table that has a unit prize. Okay? And we can, by looking at this model is says that we can actually figure out what will be the subtotal per line item k by multiplying unit price by quantity. And then go ahead and sum all of the line items. Based on the line items, whatever we've multiplied quantity and unit price by. Okay, so we'll add all these up. And that will tell us for if you're transactions are cyclic, let's say every quarter, you're making an assessment. Why? What is your total revenue? We could figure that out, Okay. Okay, Well, let's go ahead and build a query that will assess these two financial items. Okay, So now we're gonna go to Create. We're going to go to Query Design. This time I'm going to pick product and line-item. Okay, let me close this here. And what I'm gonna do is pick the product name and description so I can see what they are. Okay? Product name, description. I will pick unit price to see what each one costs. Okay? And then I want to see what quantity was first purchased, how much of that was purchased. So the fact that we're tying up the product to align items, what's going to happen is only products that were purchased would show up here for this query. So let's run this first. Okay, so as you can see, there are quantities associated to these products. And this is not the list of all of our products. So if I open up products itself, notice I have a lot more products than what's, what this query is showing. And the reason for that is some of these products. Well, we're never purchased. They were never in any transaction. Let's look at an example. Let's look at, for example, the RMD respirator. If there was a product purchase, yes, there was one. You can see quantity of one. Let's look at this product here. From Pfizer. Nothing, no orders. So I definitely don't have a PFE in this query as you can see, okay? All right, so that tells you that by adding these two tables together, just like we did with the client and transactions, we will only see records that exist in both tables. At simple query for that is based on a joint field. I'm so yes, he go joint. And you're joining these two with these two fields. One is the product ID in the product table and the other one is the product ID in the line-item table. Fantastic. So now let's modify this query, Where we will write an expression that will give us a subtotal. So right here, I would like to create a field, okay? If I run this, it will be right next to it that shows the subtotal on each line item what it will do. It will multiply five by 200 and multiply two by 30, et cetera, et cetera. And so the quantity by the unit price. So I know how much money I'm made out of each product. And then eventually I want a full total to know what my revenue, where it was based on all of these transactions. Okay? So in here, in this field right here, I'm going to write an expression that multiplies unit price by quantity. Okay? So what you would do is unit. You could specify the table for us where it came from, product dot unit. And notice it gives me the unit price there. And let me just go to the end of it times. Let me just zoom in here so you can see what I'm doing. Okay? Times line, item, dot, and that would be quantity. Okay? I'm gonna say okay. Alright, so now this automatically shows that it, it wants to be seen. That's fine. You could turn it off, turn it on. We want to see that. And notice we have EXP R1, so that's going to be the column heading. We will fix that in a minute. Let's see if we actually got a calculated field. This is called a calculated field. And sure enough I have 5 times a 100 is 1, 0, 0, 0, 0, 0, 0, 2 times 30 is 60, et cetera, et cetera, right? So here's a big one. I have a 100 quantities at $4 a pop, $400 store. Okay, So this is working just fine, except you have Vx beyond one that does not necessarily tell you what you're looking at. Maybe I should write here subtotal. Okay, so go back to here where it says EXP R1, right here. Write down what you want to say subtotal. Okay, let's run this now and see if the field name has changed. And sure enough, now we have a word that says Subtotal. Great. So now can we add all these up right here? You could do it right now. From here, you could actually say total right here. And you click on this field here. And you're going to say sum. And that water. Now you have a total of all these fields. So that means we made 22000, an $89 total from all the sales based on all the transactions we had. Okay, so let's go back to the query. Let's go and look at its SQL. And notice here the keyword associated with the, the, the, the inner join, which is the product ID from the product table, equal the product ID from the line item. However, we have an extra field, so select these other fields, but we have one which is a calculated field right here. And it has a name as septal. So it has a name called as SAP total. So this allows you to alter and create a new field, right? Or what we call a calculated field. Okay, let me run this again. 15. Lesson 13: Business Related Queries: Okay, so let's save this query that we did in Part 13. And we're going to call this one quarterly revenue. That could be weekly revenue, whatever you wanna call it. I so for me it's a quarterly revenue. So you could do this every three months, a year, figuring out how much your revenue was. You can even see it per item and total. Okay, so I'm going to save it here. Now. Let's suppose for insurance purposes, let me just close this. I'm asked in any given time to supply what my product inventory is worth. Sorry if my warehouse is damaged or a burn down by a fire or something like that, I can always have a number I could claim to get compensated. Okay, so now what we're gonna do is a single table query and review again this query to figure out what is, what is my inventory was. Okay, so let's go ahead and now go to Design query. This one. We simply need the product table. Now, if you want, you could also include the manufacturer to show with the manufacturers are shorter. If you want to do a multi-table query, you could do manufacturer product, okay, close it. Let's make these little bigger so we can actually see what fields we're picking. Okay. I want the manufacturer name, I want the product name, I wanted description. I want a unit price, and I want the quantity on hand. Okay? So if I ran this by itself right here, okay? All right. So I will have all of the products that I have in my warehouse with their quantity on hand. Now remember, this one here was untarred with an ad product update query that we designed a few parts ago, right? So we're assuming that everything we have is ten of each. Okay? Alright, so can we figure out what is our subtotal and total? Again, we can do that, right? So you could do a subtotal here. So the field name will be called subtotal. And then let me just zoom in as to what I'm doing here. Okay. So TO tall. And it will be based on manufacturer. Actually just product, product unit price and quantity on hand, right? So product dot unit price times quantity on hand. Okay? Well let's see if it works. Let's run it. And there you go. Okay, so we've got 10 of each xi multiplying ten by these prices. Great. Now can we figure out what is the total worth of our inventory? Answer is yes. We could go over here where it says totals, right? And we're going to click here and select some. Okay. And there you have it. We have what is this? So 188,980, okay, In terms of value. Okay? So now let's save this as inventory value. And there you have it. Okay? Now, can we find, for example, based on the same query, what is the average value? Right? So we could close this, create another query. Or based on the same query, I can copy this where it says inventory value, I'm going to copy it. Or is it says Copy? And I am going to right mouse click here and paste a new query. And it says copy of inventory value. I'm going to call this one average value, inventory value. Okay? And I'm going to open it. So you can actually base your query based on another query that you already had. I'm going to open it. The results are going to be the same as the previous one except here. Okay, So we've multiplied each. So let's go back to the view here. And instead of sum, Let's see if we could do an average. And that's about the average amount I might have per, per product. So it tells me that on average, my products about $9 thousand as a group, you know, per, per individual line. Okay. Can we do this instead right here? What is the average price? Okay? So, yeah, we could do something here where the average price is here. And let's remove this one out. Okay? See if we can do this. Let me just remove the totals and put it back here. It's still saving what I had here. So let me just delete this. It's just remove it. Okay. And do it again. And here, I don't want anything, I want none a go. Here's my average price. Okay? Now, obviously I got that because you're multiplying by 10, so it's your average price times 10 gave you this average sum here. So if you do average, that's what you're going to get. Okay, so mathematically makes sense. That's because I have even tens in here for each one. So I'm just going to say None. Right? So this here tells me what the average is. So if you did an average here, that would be a 10 die. If you didn't average for quantities, just really doesn't make any sense. So I'm going to say no. Okay? Can we do a sum here? So we can have a combination? So we could do our sum here, okay? Right, so we have a sum and an average, right? Okay. Let's see. Can we make something out of this now? It won't make any business sense here. We can deduce for each line here, what we have. I think this is fine. Okay? And this one for the average price is fine as well. Okay? So this here is an interesting query where we have the average price and what is our total. Okay? All right, let's save this and let's close it. Okay? Now let's do another query. This time. We want to find or sort our product based on price. Now that's a single table query. We could do this, create. So Query Design, Product, close. Now what are the fields you want to see? I want to see the per product name, okay? Description, unit price. And then we could sort from here, ascending. Now let's see what we have. Okay, so we start with the lowest price down to the highest price. If you do it in another form, which means descending, right? So now you're going to start from the highest price down to the lowest price lists. Leave it this way. So we're going to save this one as sorted. Inventory value or sorted. These are product prices. That's more like it. Okay, so we know what's our most expensive item down to our cheapest item. Okay. 16. Bonus: Building the Initial ERD from an Idea: For part two, we're going to improve on the previous design that we had. So just as a reminder, the previous design we had, we had the entity client and we had product. And they were interrelated to each other through an event called transaction. And what had happened is we've established a relationship of one-to-many between client and transactions. And the same thing for products as a one-to-many. Okay? Now, from a model point of view, this looks good in terms of explaining what a many-to-many relationship is, logically speaking in this manner. Okay? So what I'd like to do now is improve on this design. And again, using a classical design that's already out there in terms of the transactions that might have multiple orders and M, for example, that would be a much better model for this to, to, to improve. So what would this look like? So we would still have client okay. And we would still have transactions. But a transaction could have many line items. Okay. And then your product would come into play in this manner as a one-to-many. So we still have a one-to-many relationship between client and transaction. By transaction may have many line items. An example of that will be a purchase order, for example. So let's look at it visually, what a purchase order might look like. So you'd have a client name or name or order date. For example, when you order or the transaction date. And then from under it, you might, you know, right here as part of it, you might have lines of products that you've purchased. P1 quantity. So this will be a description, quantity and price here, for example. And then P2. And let's see 11 of these two or these whatever the price is, whenever the price is, et cetera. And then you would have a total here that add all this up. Okay, So now what you have here, a line items. So what this allows us to do now for one transaction which is the based on that order date, we could have many, many, many line items there. And each one of these line items is tied to a product. So each one of them is tied up to a product right here. Okay? So this would be the, the, the, the idea here of improving this design into this. Okay, so now let's formalize this design in a much better EID. So let me just go ahead and redraw now that we have the general idea. Okay. So let me just go ahead and do File New. Okay? And this time we're going to go ahead and put all the attributes we're going to need for client. We would have client ID. We would have FirstName. Lastname. Next will be the transaction or you could call it the order if you want. You could have an order ID for this transaction. A particular order will have many line items. For example, again, for the client ID, this will be your primary key. For the order ID, this would be also your primary key. After that, we would have the client ID as a foreign key. And this would be the order date. Okay? And what we would have after that is let's say we would have a line item. Okay? So this would have a line item ID for example. And this would be its own primary key, unique key. And then it will have the order ID is a foreign key. Okay? And under that it will be quantity. For example, how much of that product did you purchase? A lot, obviously, so we need a product, so we need a product ID as a foreign key as well. So yes, you can have multiple foreign keys in an entity or what we call a table. So here's your product. It would have a product ID as a primary key here. This would be a primary key. Okay? This would be a description. Maybe unit price here. Okay? So anything that describes the product might be in here. Okay, So now the relationships, so we're going to take this client ID, connect it to this client ID, and make this a one-to-many. We're going to be doing the same thing. I'm going to take this order ID and connect it to this order ID and make this a one-to-many. You're going to do the same thing here. You're going to take you a primary key here and line it up with your foreign key here. And this will be a one-to-many. Hence, you have a simple ERD for a transactional database system that would track any purchases done within a certain date. And all of these purchases will be under that order for various product for a particular client. And that is what we will model in the next plot. 17. Bonus: Improving the ERD for Ease of Implementation: Okay, so what I'd like to do right now is a database that will keep track of client transactions whenever they buy some products. So this is a classic design, but I would like to see the investigate the product development lifecycle for this until we come up with an actual database we're going to use eventually Microsoft Access, time permitting. I'll do another video with Oracle as well. So let's get going. Let's say we have a client here is, and this client wants to buy a whole bunch of products, okay, so we have products here. So these are all instances of products, right? So here's the client wants to buy maybe two or three of these may be one, and so on and so forth. So what's going to happen is for this for this purchase to happen, you have to have an event of some form, right? So this event could be your transaction. And it within that event, it you will associate the client to that event. And then it will associate with, let's say, these two products to that event. Since it is an event, you may want to also put maybe a date and time when that actual transaction happened. Okay? So that's typically how you would start the modelling and start thinking about, okay, how does this work? And then you move from there and you start actually using some modelling tools to come up with what we call an ERD or an entity relationship diagram. Okay? So this is a rough idea. Like let's say you have a napkin and that's what you're doing. And you wanted to build a database and you have some idea on how to track these things. And that's typically how you would go about it. Okay. All right, so Let's move on to the next level, which is the, you will try to attempt to create an ERD. So how does that work? Well, these are instances, that means these are actual objects that exist, okay? But there are based on some form of a plan. What is a product? What defines a product, right? Same thing. This is an instance of a client. But what defines a client? So we would like an entity that represents the components of a client. So what could that be? Maybe client ID here that makes sure that your client is unique. Maybe firstName that identifies the client's attributes, like lastName. Okay. All right. How about the product? Well, the product also is probably modeled out of an entity as well. And we're going to call this entity here, client, okay? And this here, this entity here. We're going to call it product without the S. Now, since products are instances of product as an entity. So what could that have client, sorry about that product ID. Or it could be a VIN number or it could be any unique identifier that identifies a product. So product name, maybe a description, maybe a price or unit price may be on that product, something like that. Okay. You could, I mean, you could expand this and say quantity on hand. How much of that product do you have? Okay, That's if it's a group of products that are all identified as this, like let's say milk. Well, you only sell what kind of milk? So it has a specific ID. And then you could say, okay, I have 15 gallons of this or something of that nature. And the price per gallon, this is what you would do here. Okay? If you have, if you're selling vehicles, on the other hand, if this happens to be a VIN number, then quantity on hands will not make sense. Because that is the whole thing. He represents. That vehicle, right? In in itself is one entity. So you have to be really careful as to what these attributes relationship with the actual entity is, right? So that's when you get into another concept later on, in terms of evaluating the dependency of these attributes to the entity or the attribute to other attributes. Okay? At that's called normalization. So that's a process in which you're going to streamline your entities here to have just the right amount of attributes to describe that entity as efficiently as possible. And then what's going to happen is you're going to now use some sort of a relationship between entities to make your model complete. So what's going to force the war is going to enforce these relationships. What's going to make them happen? Well, first of all, we've already established that a client associated to an event. So there is a relationship between a client and event. So, so our products. So how's that going to happen? Well, what an event? I did mention that there is a date and time for an event, but who were involved in the event? Well, I could have a representative of the client right here and right here that says this event belongs to this client. By fantastic, How about the product? Same thing. I could have a product ID here. Also. That happens to be a unique key right here in the product table. But it's now a referenced, referenced here as part of the event. Okay, so that leads us to another situation. So if these are identifiers that are unique by client ID and product ID to the entities client and product respectively. What are they for the event? What we have two situations here. If that is the only type of purchase and the only time you're going to be this client buying this product ID. Then you can combine these together and make them a composite, what we call a composite key for this particular event. Which means that same client cannot later won by the same product. Well, that's not feasible. Now, there are designs who required that you create a composite key of this man. What if I had an event ID in itself? Yeah, that's fine. So this could be the unique item. And now you're no longer tied up to having these two together as a unique combination. Okay, so that's another design issue you have to tackle as you move forward. Okay, So what I'm gonna do is for my design, I am going to have the event ID, the unique key or the unique identifier for any event instance. Now notice here I'm making a circle, just like these guys are instances of the entity. So the event as an entity. Okay, we'll have an event to ID. We'll have a client ID, will have a product ID. We'll have a date. When the event happened, maybe date and time. How about other things? Like how much of that product did you buy? Quantity purchased may be quantity purchased of that particular product. Right? So what can happen is based on this quantity purchased, you could subtract it from quantity on hand and hence have a good quality control. I mean, I'm sorry, inventory control database, which means that you can query the database laid wanted to find out a, you know, I my low on this product. Now, after a few transactions, do I need to reorder? Okay? So that's sometimes the main objective of some of these databases to strap is to track your inventory, right? So this is a case where you could assign a quantity purchased. For that particular case, where you can subtract it from quantity on hand and track now your inventory, your inventory levels, okay? All right, so how are these entities going to be related? And this is where the ERD or entity relationship diagram comes into play. So let me go ahead and create a new. So let's create the and to decline. Right here. Next. I'm going to call that event transaction. Okay? Then I'm going to have products. Okay? So a client will be related to transaction in one way or the other. In what manner? While you cannot have a transaction without a client. But a client may or may not have any transactions that is usually referred to as a one to many. So M stands for many relationship. So N1, this is one. So we have a one-to-many relationship between client and transaction. How about the products? Same thing. Your product. You know, you cannot have a transaction with our product. So technically speaking, you have also a one-to-many hear relationship between product and transaction. Okay. So what's a, what's the relationship between product and client? Well, indirectly, what we have here is a many to many through the transaction table. So that is a many-to-many relationship. A client can have many products. Products can have many clients, right? But this is done through a third entity right here called a transaction. Okay? So there is no way to create what we call a cardinality of a many-to-many. Physically speaking, that is, between these two. But you could logically say that these two are related many-to-many. So notice these Croft feet here. This is what you would do in some other ERD standard diagram that shows the cardinality between these entities. So you can see the crow feet here. So I'm keeping it with keywords like one, many, right? So why? Because I could have entities that could be one-to-one. All right. So it's not everything is a one-to-many. Right. So I could have one-to-one relationships. Okay. So but in our case, how about a many-to-many? What? A many-to-many physically, you're not going to do this. So if you're gonna do many-to-many, yes, you could say logically that your clients have many products on Cloud and products have many clients. But physically we're going to find out we're not going to be able to do this unless you have a third table. So what you're going to have is a one-to-many here and a one-to-many here to create the effect of many-to-many. Okay, so I'm condensing a lot of concepts with these examples. Okay? So I hope this explains now the one-to-many and the many to many. Now how about the one-to-one? What, how does that work? Typically to implement this and this can be implemented physically in a database, is if you have, let's say what we call a primary key or an identifier that uniquely identifies this entity. Let's say this is client, okay? And we have a client ID. Okay. Now, let's say the client has an office. Just one office. Well, that office we could identify by the client ID as well. Since it's only one office. So the office is really an extension of the client entity. You're just splitting it and putting it in a separate entity. Now, what does that mean? Technically, that means if you had name here, you could have had the office number here. But you chose to have the office number here in a separate entity. So we will not do it here. Now, why is that? Because the office number does not necessarily represent a client. So it is an office, so it is more related to the office, but it belongs to the client. While that's fine. In that case, this would be a much better design. Extend this relationship as a one-to-one. And the key here is now notice that both entities use this same unique identifier. Okay, So for the one-to-many, let's go back. So we have a client ID here. Well, here you have a transaction ID, but you will have a client ID here and you're going to say, okay, well, what kind of, what kind of, what kind of field is that? What kind of attribute is that? If it is here, a unique identifier, which we call a PK or a primary key. In here it will be called a foreign key. Same thing here. If you have a product ID here. And this would be a PK, which is a primary key. Then you would have a product ID here as a foreign key. And we're gonna do FK for foreign key here. Okay, good. So other attributes you're going to have here, Maybe quantity purchased, Q purchased. Here will be the names, FirstName, LastName. This here would be the product and product name, product description, unit price, things of that nature. Okay. To describe. So each one of these will describe the entity. So is it, so are these, they are describing the entity. Client, okay? And they're supported by their primary key. Okay? This is what we call a form of normalization that we will in another video see what is one and F for first normal form. Second normal form. Third normal form. Fourth normal form, boys, god, and so on. So there's tons of these that are rules that decide what do you need to have here. What do you need to have here? Okay, so that's another lecture all on its own, which this techniques here allow you to efficiently figure out, okay, what do I need here? And I used it here actually to decide that the office shouldn't be here, but it should be in another entity. Okay. So what I did is what we call normalized or this is Norma lies Asian, right? So this is where you can first normal form, second normal form, third normal form, fourth normal form. Each one has a rule on how to move forward, so you cannot be, for example, the second normal form if you're not already a first normal form. So the progression to a normalization goes this way. You have to be in first normal form to become a second normal form. Third normal form, you could stop there depending on your design and, or you can continue depending on your requirements for the design. Okay, So I hope this crash presentation, I gave you some ideas on how to do this. And for some of you, this is a review. And I hope you enjoyed this because the next video I'm going to, I'm going to do is actually build this database. Thank you. 18. Student Class Exercise: Graduations, if you're at this stage, that means you have completed the class exercises. We built our application around. This time. It will be your version of the class exercise. In this version, you will rebuild this database to follow these requirements. So think about how we built the database. Originally, we had products that had names, but they were not identified as unique products. So it's basically categories. Now what happens if you let's say you had a car dealership or you had appliances. Well, these items that you're selling, how are uniquely identified with serial numbers or VIN numbers. Okay. So they themself are entities. So how would you or record a complete record? How would you build a database around that model or so? It's going to be slightly different. So I'm get, I'm going to give you some hints here and how to get this done. But realized that the parameters for your have changed a little bit for this exercise. Okay? So if, for example you sold an item, it's no longer a matter of quantity. It's the actual item that's been sold. So that is a fundamental difference between this example versus the prior example we developed our application or route. So you're going to have to read, massage your database or start from scratch, which is even a better idea with the putting in mind that your product when you sell it, okay? It's not a matter of quantity. It's the actual product that should be out of the database or should be stored in a table, separate table for soiled items. Or maybe it has an associated field for that record That's Boolean, that's either true or false in terms of representing the item as being sold or not sold. Make sure that you also added date to an event, especially if you sell an item. It's a probably advisable also to add a date when the item came into the inventory, especially if you're dealing with a car dealership. So that way you could do a query L1, and assessment of how long you keep your inventory around. Okay? That's important in terms of performance measurements. A lot of managers want to know what's the turnover. So that could be a really nice thing to do at the end. To really hone in on the skills that you've learned from this exercise. Okay, good luck. And thank you so much for taking this course.