Tutorial 3: How to create linked tables on an existing table so the data can be more normalized?

I want to talk about what normalization is first before we go into great detail. According to Microsoft, normalization is is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. To understand what Microsoft is saying, imagine you have a database with one table (members) and one of the fields is city. Let's say that you only deal with 5 cities, but do you want the database to be recording the city every time you record an entry? That is really a waste of hard drive resources because instead of recording city with every record, we could just create a table called Cities and have it mapped to the members table. This means instead of recording the long string associated with city, we are only recording an integer value that maps back to the city table that will define the relationship. This is First Normal Form and for further understanding of normalization and its different forms, please go to: Microsoft. The other benefit to using normalization is that it creates a foreign key constraint so any value that is entered into city must also exist in the cities table. This keeps the data uniform and will maintain integrity so you will not see 10 different spellings of a city. Some degree of normalization should always be used in order to provide data integrity, increased resources, and increased speed of the database.

Now that you have a general idea of what normalization is, we are now going to take a look at how to normalize a field in an existing database. The database we are using is the same one from the database project so it just has the one table "members" and this table has information such as name, city, address, and so on of each member. Since city is repeated so often, this is a good candidate for normalization. Our goal is to create a separate table called Cities and a new members table where city is not being recorded, but rather just a lookup record that will do the mapping. Microsoft Access has a tool that helps with this kind of manipulation so that is what we are going to use today.



First of all. go ahead and click on database tools and then click on analyze table:

Click through till it asks you what table you want to use:

Make sure you select members and click next. The next screen will ask whether or not you want to decide what fields belong on what table. Pick the option that says, "No, I want to decide." Now you will get to a screen that looks like below:

Here, go ahead and select city. Then left click and drag it to the right open space. It will then ask you what you want to name the table, so name it "Cities." This is what you should see after you complete this step:

Click next, and then it will ask you if you need to make any changes to any of the city names. You do not, so click next. Then it will ask you if you want to create a query that looks like the old table. I recommend doing so because this will basically work just like the old members table so if there were any forms or reports based off of it, they would continue to work. Please look below:

Click finish and now it is all done. Now here is what you should have:

This now created a new table called Cities that has a relationship to Table 1 through a look up field. So now when you look at the cities table, you will see each city name and then the corresponding member information where each person matched that city:

Now, further manipulation will need to be done for the forms in order to use this approach, but this gets you the basic configuration of a table along with a related table.



I highly recommend visiting Microsoft and Access forums located at Access Forums for further information on Access and related tables.


**Pictures are self created content of Microsoft Access by Richard Ecker, 2011.
**Access Forums is a free public site where users can address all question related to Access.

Database Project

Today, I completed a database project where I had to convert a raw text file into a database table and then perform a few different operations with the imported data. The first thing I learned how to do was the import step. This was achieved simply by just clicking on the home tab, selecting external data, and then clicking on the text file. Since the data was stored in a comma separated file (csv), I selected delimited and continued on with the wizard. The wizard does a great job at making it very intuitive, and even if you are unsure of a few things, there is a help button that will answer all of your questions. I then proceeded to query the data to only show the pieces of data that we are interested in, and this type of query is known as a select statement. Once again, we just click create on the tab, and then select query wizard. It too is very intuitive, so just follow along with the wizard to pick what data you want to see. If further additions need to be made, you can always click the query design button, and here you can add/delete/sort/filter on any number of fields. The query is a great tool to analyze the data, but what if you want to print it out? This is where the report comes in hand because it can take the existing query that you set up, and then provide a printable version of that data. Just click the query you created, and select Report from the create menu. Now the report has been created, you can still add grouping options, delete fields you do not want, or just change the entire look of the page layout. Most of these options are accessible by clicking the view option and selecting design. Now we have a way to look at and report existing data, but what about if we want to enter in new data? There are two ways of doing this: manually entering it into the database table or creating a form. The form is the best way to do it since you only have to select the few fields necessary that need to be inputted. Calculated fields such as create time/auto generated ID’s do not need to be entered or sometimes seen so the form only presents what is necessary to create a new record. As with all of the other operations, just click create and click form wizard. In general, I learned how to take a basic text file and do some basic analysis on it by importing it into Access. I see this being very prevalent in the business world when two companies need to share data.

There are a couple ways that this database could have been improved on, but I am just going to talk about one today. There are a couple fields that have only 2 or 3 different options such as gender and membership type. The problem with the existing database is that it currently allows anything into those fields. For instance, I could put in “alien” where it says gender, and the database would allow that. What needs to happen is we need to put in constraints for these data types and this is accomplished by using the validation rule that is in the table design view. I am going to quickly go over how to put in a constraint on gender to only allow male or female.

First of all: Select the table that needs the constraint and select design view. You should see this:

 

Select the field that needs to be change and select the ellipsis next to where it says validation rule. You should now see this: 

This is where we type in our expression. If you are unsure how to use this click the help button, and it will go over all the different functions and ways to use this. For our analysis, type in: In ('female', 'male'). Hit ok and now save the table. Now when you go to the form and try to enter in a value other than that, it will provide an error saying that the constraint has not been met. This type of constraint should be used to ensure accuracy and better reporting capability.

**Pictures are user provided content by Richard Ecker, 2011. They were taken by using screenshots of   Microsoft Access.