Entities, functional dependencies, referential integrity anomalies, normalization Entity Relationship diagrams or primary key foreign keys cardinality, quite a list. Are you confused with all the concepts and the terminology? Well, I did promise you and we are now at a point in the course where we can weld all the pieces together. Before long, you'll be talking just like a database geek. We have reached a point where we can show you how things work physically. And this will occur in something called a database.

Like a car. The engine that drives this database is a database management system. We have selected Microsoft Access version 2016. If you have an early version, say office two 2013 try it out. There will be some adaptation, but you should be able to follow. If you do not have Microsoft Access, it might be interesting to listen without doing.

But you may just find it confusing. And if so I would skip this lecture. Sounds exciting. Let's now take a test drive and enjoy the journey. First, so a quick review of the ID that we created in the last lecture that we will use as an implementation. We call it student courses, and we will use this name for our database.

We've mentioned three entities course, student grade, and we'll we'll use these names as our tables in the database in our era, where they identified a course number as a primary key, in the sample data, we input courses b d 210, ps 130. And others. These course numbers are specific and identify the courses in our calendar of offerings. That is, we do not want to enter a course that is not in the calendar of courses. We have just now identified a business rule that we would like to implement. Also, a business rule is so is associated with a grade.

It can only be an A or B, or C or a D or an F. Keep this in mind, and we will discuss it later. Microsoft Access has much to offer, and it's probably the most widely used database for small and medium sized businesses. Let's take the tour of access. Are you ready for a test drive with Microsoft Access? We will implement our student courses database that we designed earlier. When you first open access, the screen that appears is called a backstage view and provides several ways that you can create a new database either by utilizing a blank database, or else a template that is already provided by with Microsoft Access.

And a third option is by doing a search for an online template. We will start our database by clicking on the blank database. We will change the default file name database one to our database which is student courses. We now have the option of creating a new folder for this database or going with the default folder. We will go with the default folder. Now let us click the Create button.

We now want to add tables to our database if access is already open need to go back to the backstage view by clicking File Open. Here we notice that our database has previously been created. And that database we named a student courses, click on the student courses database followed by CREATE TABLE, which is what we want you to note that the default name for this table is table one, and has two columns, an ID column, and a click to add column. The first thing we want to do is double click the ID column and name this column. The course number ventually. This course number will become our primary key and the next column click to add Get the downward arrow.

And the menu allows us to select the data type for this particular field called course number. And the selections are short text number, large number of currency, date and time yes or no etc. We will go through some of these as we proceed along with each lecture. The selection that we will choose for this course number is short text. This short text can hold up to 256 characters of information. And course number as we remember, is comprised of courses such as B eight to 10 and b A to 10 is an alphanumeric listing comprised of two uppercase letters and three numbers.

Certainly there is enough in 256 characters to hold this particular field. We now see that the emphasis is on field one and field one we are going to enter the course description. I can we will hit the downward arrow and choose short text for this course description. at this particular point perhaps we want to save this table, this course number, course description table and name it the course table. We do that by holding down the Ctrl key and hitting s and it asks us to substitute the table one name by the appropriate table that we want to call this course number course description. We will enter course as the table name and press OK. Notice the objects in the left hand pane.

Has been changed to course, which is what we wanted. We have now completed the initial setup for the course table. But we call it an entity relationship diagram. There were three fields, course number, course description, and concentration. What happens if we forgot to enter one of these fields or we need to change one of these fields? It's quite a simple matter really.

Let's go back into our student courses database. and identify in this particular case the course table, and in the course table, we have already entered the course number, course description, but completely forgot the last field, the concentration, well double click this field. And let's now change this field one to concentration Again, we need to add the data type and the data type is simply short texts. So now we have a three fields, of course number, of course description and concentration. And we may just want to change the size of this concentration so we can see it a little more carefully. And now we have not only completed the course table, but added the extra field, we need to save this Ctrl S. Ah, simply hit the Save button.

Go back to file open and let's look at the student courses database now and is comprised of in the left pane. Course table course number, course description and concentration. Hence we have adjusted our course table to incorporate concentration. Let us now create our next table student using an alternate tool supplied by Microsoft Access table designer, instead of the datasheet view that we use previously with the cost table. We usually start off with an initial splash screen or backstage view, which we have and then click on the database which we will do it opens our student courses database. And to get to the new table designer creation.

We have to go to the Create and click table design. Now this view certainly is more elaborate than the datasheet view that we had before, and provides many, many benefits. Let's not discount though the datasheet view. It was primarily because it's like working in Excel and can be used to input data, which we'll see later. However, this table design view has the advantage of inserting, deleting, updating rows designating a primary key and giving a full narrative description. So let's start off by inputting the student number for the student table.

And identifying this data type as number. I give a brief description about this particular field and we'll mention that it is the primary key for the student table We want to make this row a key. And the way we do that is by clicking the Design tab, followed by the primary key object. And if we look back at our table, we'll see a little small key against the field names to number to indicate that it is a primary key. Let's continue and add the other fields. The second one being student description.

I will use the data type short text followed by student address. And this brings up a question whether or not it should be a long text or a short checks. We noticed that a short text allows us allows a field size of up to 255 characters unless certainly seems sufficient for an address. So Let's go with it. Now we want to save the table. And if you recall Ctrl S allows us to change the default table name table one to the table name that we would like.

And I'll change it to the student table and click OK. Let's take a look again at our initial spreadsheet, our er D diagram, as we consider entering the grade table into our database. Recall that we have a business rule associated with the field grade. The only values allowed A or B, or C or D or F. Let's see if we can incorporate this rule into Microsoft Access. This time assume that the database is already open with the two tables displayed in the left hand pane, course and student what we need here is to create Create a new table in table design, and that eventually is going to be a great table with the first field name being great. Now this grade table holds a business rule and contains only values A, B, C, D, or F. The data type is a little bit unusual. For data type, we need to create a lookup wizard and it will prompt us into filling out some questions.

First, I will type the values that I want next, and it's going to be comprised of five columns that is column one a column to be counted C three, etc. So let's look at each one of these columns and give what the values are. So column one is an A, and we will restrict that size. Column B, column two amps, so it would be a B. Again, let's restrict the size on the display of this column three, we'll have a C to choose from. Column four will have a D selection and column five will be our F value.

And again, let's restrict once we are set That's fine with that, we'll click Next. Next, and we will limit the list to the input values that we provided. So let's click this and finish. Okay. And we will save this table with the Ctrl S key and call this table, fifth grade table. And what we have now is three tables, a student table, a course table and a great table.

Let us now turn our attention to the relationships between our three tables. Course, student and grade. The cardinality rule states that tables can be in a one to one or one to many or many to many relationship in our case, the relationship between student and course is many to many that is, a student can take many courses and the course is offered to many students. relational tables however, cannot support many to many relationships. And this relationship must be broken to form a one to many relationship between a junction also sometimes called a bridge or link or joining table. Our bridge table is the grade table and requires adding foreign keys to this table.

Not only is this necessary, but it is also logically correct as in a grade is given to a student student number for a particular course course number. Let us right click the grade table and make the necessary adjustments. So I will right click the grade table and go into design view. Conveniently, I've already added the two foreign keys. So for the great table, I've added student number and the course number as foreign keys to this great table. The data type is important.

The data type number for the student number is the same data type in the student table. Similarly, in the course table, I have a short text, which is short text in this great table. And this is important that the two data types student number and course number must match the parents student number and course number. Let us now analyze further in Microsoft Access. Well we'll find more information. And the way we do that is by clicking But first of all, let us come out of this pain and this is very important to x out before we go into database tools and click on relationship.

Here we have a relationship pane. Now, as we've formed the relationship pane, it is important to keep the parent or the one relation to the left or top of the many our child table. So let's do that. So the student table which is a parent table will place at the top and the course table, click and drag, we will also place that at the top as well. However, the child grade is below the two parents and we'll play set immediately in between but below student and course. To create the cardinality.

We need to click the student number in the student table and match it with the student number in the Grade table. And then release and what appears is three relationship Type settings which affect the table and any related records, referential integrity, cascade update or cascade delete the first term we've discussed previously where we review referential integrity as the means to synchronize records in both the tables. So in most circumstances including this one, we will turn this on, and we do that by clicking the box. cascade update means any change on the parent side table will be cascaded in the child on many table. For example, if we were switching over to the course table, the course number changes in the course table. We probably want this to be reflected in the Grade table.

And equally important is any changes in with student number in the parent table should also be affected in the student number of the great table. So we will turn this on cascading delete means if this option is turned on deleting a parent record is also deleted in the related data of the child on many table. As an example, if a student is removed from the student parent table, all associated records are also deleted in the child or grade table. In our case, this is turned off, which means we cannot delete a record in the parent. Let us now create the relationship between the student table and the grade table. And what appears is a one to many relationship a one to many relationship between student and grade.

Likewise, we have a relationship between students and courses as many to many. We want to break that relationship and make a one to many relationship between course and grade. Again, let us left click course number, drag it down to the painter to the child course number and the same questions come up, we will enforce referential integrity and cascade update and turn that on, but leave the cascade delete off and create hopefully a one to many relationship between the course and the great table. And so, subsequently it does happen. So, now you know a lot about table relationships including working with access. And we do this in the relationship pane to ensure that we have created well formed relational set of tables, allowing us to efficiently access query report manage our database, which we will cover in the next section.