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.

Presentation Project

Self Critique of Presentation Project : RIchard's Crepe Company

After reviewing the presentation, I saw that there was a general flow that went from profile, to opportunity, to strategy, to risk, and then finally ended with a conclusion. I think the presentation could have been a little bit more outlined in the beginning, but it did follow a logical flow. The presenter asked questions such as, “How am I going to accomplish this,” or “How will I do things differently?” which preempted the audience from asking those questions. I felt that the presenter’s attention grabber could be stronger. In the first 30-60 seconds, he mentioned, “How would you like to make a few million dollars in the next couple years?”

The presenter usually asked questions and then answered them, but only a few of them sparked curiosity. The presenter did an excellent job at providing an introduction of what he was going to talk about. Within the first few minutes, the audience knew exactly what the presentation was about. The presenter validated a claim by providing a bar graph of survey results to help substantiate his claim. The presenter also seemed to avoid most clutter and errors limiting most slides to only a couple bullet points and/or pictures. The presenter used large bold fonts for the titles of every header, and he also made the bodies of the slides large enough to ensure readability. The presenter would sometimes show a slide and then talk about it, and other times he summarized the slides. There were a few slides that he read, but these were quotes. In summary, the presenter did an overall good job of presenting his ideas.

Reflection on critiquing other presentation
After reviewing another presentation, I definitely picked up some ideas that would have helped mine. While mine was still a business themed presentation, I probably could have inserted a few more colorful elements into the presentation to make it more lively. I also realized that the reader wants a general outline of how the presentation is going to go. They want to be alerted when its the beginning, middle, and end so they can easily follow along.

What I learned:
From this project, I learned how to effectively create a powerpoint presentation, and then turn it into a screencast with an audio commentary. I searched the internet for an effective tool to create a screencast, and stumbled upon ScreenCast-O-Matic . This tool allow me to record everything I do on my computer instead of having to use a video camcorder. To see how to use screencast-o-matic, go ahead and click here: http://www.screencast-o-matic.com/watch/c6lIFqX3H. This tool combined with powerpoint can be used in business to perform presentations when you are not available to be in person. I already plan on using this to draft a proposal to my new job to demand that we switch over to Reporting Services from Excel for their reporting needs.