Secure Email Project

I completed a procedure of sending and receiving digitally signed and encrypted emails. The end result of the process is shown below:

Encrypting emails is one way to set up a barrier between a business and potential hackers. Emails are just plain text that anyone can intercept at any given time so it is important to put in a system that guards their contents.. In the article listed here: Hackers Reveal Offers to Spy on Corporate Rivals, it discusses the attack of hackers on the security company HBGary Federal. Some of the emails that were intercepted described ways of undermining competitors/adversaries of other large institutions such as Bank of America and the US Chamber of Commerce for millions of dollars. One email that was intercepted even suggested a way in which WikiLeaks could be discredited. Now, Bank of America and the US Chamber of Commerce are under attack and under ridicule for the emails that HBGary had on their server. If they had used encryption to guard the attacks of hackers, the emails would never have been jeopardized and the reputations of both institutions would have been protected.

Picture is user created content by Richard Ecker, April 10, 2011.
Article is provided by New York Times Online February 11,2011.

Extra Credit : Creating an avatar using DoppelMe

Today, I created an avatar by using the site DoppelMe and here it is below:

While the process was very straightforward, the amount of customization was a little limited in comparison to other sites. Basically, you only have a few different styles of expressions, wardrobe, accessories, and hair styles to choose from to try to make it like your self.

Anyways, to get started just click on the link I listed above and just hit create. It will then ask you what eye color and skin color you want so just make the selection and continue on. Afterwards, it will lead you through facial expressions, shoes, shirts, bottoms, and so on so just make the selections you like and continue on. If you want additional options, go ahead and click on create account which opens up a few more customizations. However, you still are very limited on what you can choose even if you create an account. Afterwards, it will provide several links to the avatar you created where you can then embed them into your blog or email signature. Just copy the link and post it to your blog to use it.

If you want a simple and very easy to create avatar, DoppelMe is your answer; however, for heavy customizations, I would look elsewhere.




Sources of picture: User created by Richard Ecker using www.doppelme.com.

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.

Tutorial 2: How to establish a connection to SQL Server using Excel 2007-2010.

Most people know how to use excel, but usually the data is provided to them in a file. What if the data is not provided and it is only accessible by connecting to a SQL server instance? This is common in businesses where a data warehouse is set up, and business users are forced to analyze the data by connecting to either an OLAP cube (click for more info) or a table in SQL server. I am not going to talk about OLAP cubes today, so I am just going to focus on how to use Excel with a direct SQL Server source.

A few things to consider:

1. Make sure you have access to the SQL Server. Usually, Database Administrators will limit the access of the databases to a few key people. Typically, business users will have access only to the data warehouse database for deeper analysis, so make sure you communicate with the DBA to ensure you have rights.
2. While most data warehouses are designed with the business user in mind, sometimes table names and column names are not named in an easy to understand manner. Make sure you understand what the definition of every measure and dimension is in the database.
3. Excel always needs to be refreshed to see new data, so if you establish a connection to SQL Server and then save the file, that will be data at the time you initially created the file. If you need to see current data, then you will need to refresh.

So let's get started!

First, open Excel and click on the Data tab. Now click on "From Other Sources," and click on "From SQL Server." You should see a dialog box that looks like this:

This is always the standard connection box for establishing a connection to SQL. 

Now put in the information for the server name and the authentication, and if you do not know this information, please get with your Database Administrator to find this out.

Next, it will ask for what database and table that you wish to connect to. This information should be provided to you by the Database Administrator. Once you know, go ahead and make the appropriate selections and click next. For my example, I am connecting the DataWarehouse database and am accessing the rpt_PLRV2_ALLSites table.

Now, it will ask where you would like to save the connection file. I recommend storing it in the default location it brings up: C:\Users\richecker\Documents\My Data Sources.

Once saved, you will then be prompted with the following dialog box:

If you wish to perform analysis on aggregations of the data, I would recommend using a pivot table, but if you just need to export the data, the table option is all you will need. For purposes of this tutorial, I am going to use the Pivot table option

Select Pivot table, and now it will start transferring all of the data from the server to Excel. Depending on how large the table is, this could take some time. I usually recommend using this approach on small data tables.

Now you will be presented with the usual pivot table editor and now you can start massaging the data you best see fit.

I wanted the report by Product ID, Product Code ID, and dates of how many confirmed leads we had. Therefore, I made the selections and this produced the final product:

Note: you can do the same manipulation through excel as if it were just a regular excel document where all the data was located on a worksheet. The only difference is the data is not located on a worksheet; it is located on a SQL Server instance.

**WARNING**

This data only updates when you choose to refresh. If you want the latest data available when working with this sheet, you must go to the data tab and click the refresh button:

 

Please note that in Excel 2010, PowerPivot is available as a plugin for excel, and I would recommend using this plug in if you have Excel 2010. For more information on PowerPivot and how to get started, please check out this video below:

*Pictures provided by Richard Ecker, 2010.

*Video provided by YouTube user: sqlserver 

Excel Project

Today, I completed an excel analysis project that addressed the effectiveness of a particular workout. The data was broken out by age and gender, and the goal was to see if the heart rate of these people ever hit the target heart rate. To do this type of analysis, I needed some data such as max heart rate, target heart rate, target achieved, and the percentage increase. The problem was, I only had the raw data so I had to calculate the data. To get the max heart rate, all I had to do was enter in the cell =max(startingcell: endingcell) and I copied that formula for all rows. To get target heart rate, I just had to enter the target percentage on the worksheet and then come up with a formula that addressed that cell absolutely. So under the target heart column I entered in my calculation as = $E$1*D3. Also, I learned to make a cell reference absolute instead of relative, all you have to do is hit the F4 in the formula right before the cell. This inserts the $ signs automatically. I copied this cell for the rest of the entries to get the target heart rate of each individual. All that was left was to come up with a check to see if the max heart rate was greater than or equal to the target heart rate. I learned that this statement (=IF(F3>=E3,"Yes","No")). This was copied to the rest of the cells. To get average increase, I just made the formula =(F3-I3)/I3 where F3 is the max column, and I3 is the starting column, and then I just copied that formula for the rest of the entries.

Now we have the data that we want such as target heart rate, max heart rate, and whether or not the workout was effective. However, it was only for each person and there was no aggregations being done with the data. I learned that a pivot table will provide all of the different aggregations for this type of data. It was as simple as clicking on the Insert tab and selecting Pivot table. It defaulted to the correct range of data, and I hit ok. It now was in a new worksheet, and all I had to do was drag and drop the fields I wanted to either columns, rows, or aggregation. Since we wanted it broken down by gender, that can be inserted into the rows, the target achieved field could be dragged into the column portion, and the id column can be dragged to the aggregate block since we want a count of people. The end result of the pivot table options should look like the following:

Please note that you have to click on the aggregate field in the pivot table options and click value field settings. This needs to be changed to a count instead of a sum. Once this is performed, we now get a table that shows the number of people broken down by gender of whether or not they achieved their target heart rate.

The final result of the analysis is displayed here:

The last piece of analysis was to create another pivot table which was done the same way as before. Only, the pivot table options should look like this instead:

 Now, on the pivot table itself, we want the data grouped by age by decade, so just right click the age column, click group, and put in the number 10 to break it down into decades. I also learned to get rid of the grand total column, you just have to right click the pivot table, click on pivot table options, select the totals and filters options, and then just uncheck the grand totals boxes.

The end result looks like this:











Overall, I now feel a lot more comfortable working with pivot tables, and I learned that in some cases it does not matter if you put the fields you want to group by in either the columns tab or the row tab. I also learned that in the page layout options, you can put in a header or footer there instead of having to put the header on the actual worksheet and specifying that particular row to repeat every page. This project has definitely boosted my business analysis capabilities.

Sources for pictures:

Richard Ecker via Snipping tool

How to Protect Personal Data When Using a Public Computer?

There are times that everyone has to use a public computer for some reason. Either we are traveling and we are using the computer at the hotel, we have to go into the computer lab to finish an assignment, or we have to use a friend's computer because ours is unavailable. We are usually looking up our bank information, email, or other personal data that could become available to another user of that computer if we do not safeguard it. For example, users often will click on remember me when they are presented a prompt to remember their passwords. Now, the next person that uses the computer could get access to your information. Even if we are diligent in making sure we sign out of all of our accounts and we make sure that none of the accounts are remembered, there could be a software application known as a “keylogger” installed on the machine that could record all of our keyboard strokes. This means that whatever keystrokes were entered into the computer are being recorded into a file that the hijacker could use for malicious intent. So, the bottom line is don’t use a public computer right? Well, that is the ideal option but it is not the feasible option since you cannot be expected to have your computer everywhere. So how can we use these public computers and still feel safe?

1. Buy a flash drive of at least 8gb or higher. 

I personally recommend the Patriot XT 16gb since it is large enough and it offers increased read/write performance over most other flash drives for a reasonable cost. You can buy the drive here: Patriot XT.

Now you are probably asking yourself, what does a flash drive have to do with securing your personal data? Well, the flash drive is going to be used to store all of the applications we run instead of having to use what is installed on the public computer. This means that all of the history information will be written to the flash drive and not the public computer.

 2. Download the LiberKey software located here: LiberKey Download

This is the software that will install on to your thumbdrive that will allow you to run applications from your thumbdrive instead of the computer. For example, even if the public computer you are running does not have Firefox on it, by plugging in the thumb drive, you will now be able to run it without having to go to the internet and install it.

3. So after you finished downloading it, plug your thumb drive in and then run the software installation.  

It will ask you to accept the EULA, and click ok. Keep clicking through until you get here: 

 Click on Browse and you need to select whichever drive your thumb drive is. 

 4. It will then tell you that the directory is not created, and would you like to create a new one. 

 Go Ahead and click ok. It will then ask if you would like a shortcut placed on the desktop, so just select which ever option you choose, and then click finish. Congratulations, you are just about halfway there, and now you just need to get the applications on to the thumbdrive.

5. How do we get the applications on the thumbdrive? 

When you plug in the drive, navigate to the drive using windows explorer and open up the LiberKey Folder:

 

6. And then double click on the liberkey.exe icon.

When you run it, you will see a picture that looks like this:

Click Download list of available software suites.

For most people, the Basic suite is what you are going to want to install. This includes Firefox and a password manager so go ahead and click basic suite and then click the install button at the bottom. 

7. Once everything is installed, you are ready to go and you can start using this on any computer. 

However, I recommend first setting up the password portion before ever using it on another computer. Once everything is installed, go ahead and open up liberkey by clicking on the button on the right portion of your task bar:

 8. When you click on it a menu comes up with a list of options. Go ahead and click security and then click password manager.  

 9. You will now click new and create a master password.

Make sure it is something you can remember, but I recommend using some special characters and symbols for added security. Then click the option for using master keypass file and select the drive where the thumbdrive is located.

10. Click through the options until you get here: 

11. Now create entries for all of your personal data such as mobile banking, email, school information and so on. 

It is as easy as clicking one of the groups and right clicking and selecting  new entry on the right pane. When you do so you should see a box asking for title, username, and password. Now give it a meaningful title such as banking information, put in the username that you use for the site, and then the password that you use for the site and click ok.

 

13. Now all that is left is to save the key pass information and this is achieved by hititng the save icon at the top of the screen. 

Now choose the thumbdrive for where this information should be saved and there you have it. 

14. Now when you use this application on another computer, all you have to do is plug in the thumb drive and open up through windows explorer the liberkey folder as previously described.  

Open up Liberkey.exe and it will place an icon on your system tray. Always elect to use Firefox from here instead of launching from the start menu on the computer. When you need to use a password, open up keypass and specify your password. Then Find the entry and just right click the entry and select copy password instead of ever typing it. This will guard against any key loggers on any system. The purpose of using firefox on the thumbdrive is so that none of the internet history is stored on the machine. The reason you always do a copy and paste for passwords is so that a keylogger will never be able to find out your password. If you use these steps, you will not have to worry about personal data being stolen from you on public computers.

For More information check out this site: LiberKey Support Forum.

Just FInally posted

Hey, so I finally started a blog which I thought I would never do. I am a SQL Deviant.... what is that right? That means that I am always looking for uncommon solutions to common problems when dealing with SQL. I cluster foreign key indexes, I write procedures on the reporting services database, and other things that most other DBA's will probably strangle me for. Anyways, stay tuned for some deviant solutions to common database problems...