Links – Database Theory

December 31, 2016

A few interesting links of database information I’ve run across:
Armstrong’s Axioms: Rules on functional dependencies.
Logic For Serious Database Folks: Chapters from a work in progress on Logic as it pertains to relational databases, no release date set for the complete work.
A Simple Guide to Five Normal Forms in Relational Database Theory: Covering the normal forms, most resources don’t go past Third Normal Form.
Comparison of different SQL implementations: Very comprehensive list of how several relational database products adhere or stray from the ANSI SQL standard.

Links – Computer Science Basics

December 31, 2016

Now that I’ve completed my Bachelor’s (non Computer Science) degree, I’m going back to fill in some gaps in my knowledge of the basics of Computer Science. Here are some interesting links I’ve run across. A lot of these are lists of items that a good programmer should know, and some actually cover some basic material.

Oregon State U – Post-Baccalaureate: Oregon State has a one year online program for students to earn a 2nd Bachelor’s degree. A bit pricey at 28K, but still interesting to see the curriculum.
Programmer Competency Matrix: A good list of what a good programmer should know at various levels of their career.
Interview Cake: The ‘Glossary’ link at the top of the page has links to good explainations of basic terms in Computer Science.
aGupieWare – Bachelor’s Curriculum: A list of the general areas that should be covered by a basic Computer Science education, with links to recommended resources to cover those areas.
Syncfusion: Data Structures I and Data Structures II: Two free e-Books from Syncfusion that cover the basic data structures (Registration required).
Steve Yegge – Five Essential Phone Screen Questions: A good list of the skills an interviewer expects during an interview.
GitHub Gist: Technical Interview Cheet Sheet: A short list to cover basic data structures and algorithms.
Computer Science From The Bottom Up: Good online book (PDF and ePub available) on Computer Science basics.
Rob Conery – The Imposter’s Handbook: A great book that covers Computer Science basics, like Big O notation, data structures, and so on. The e-book is $30, a printed version isn’t yet available.

Degree Completion

November 30, 2016

This post won’t be a technical one, but I recently went back and completed my bachelor’s degree, 29 years after I first entered college. I wanted to put together a write up of the path I took to complete, in case this information was useful to anyone else trying to complete their degree.

My Educational Background
Out of high school, I attended two different schools for 2 years each, so I had plenty of credits built up. I studied political science at the first school and philosophy at the second. I also had some accounting classes from a local tech school as well. To be honest, I was looking for the shortest route to completion, as I was working full time and was already deep into my career. Most schools have a residency requirement, meaning that you need to complete the last 1-2 years or so of your education at that school in order to graduate.

Selecting A School
Reputable US schools will have a regional accreditation. Georgia is covered by the Southern Association of Colleges and Schools. Most schools will require a school to be regionally accredited in order to accept transfer credits from them, or to enter a graduate program. So I was looking for a school with regional accreditation.
I also hoped to find a school that didn’t have a residency requirement, so that I would be able to use as many of my previously earned credits as possible, as well as shorten that amount of time I would spend in completion.
I ended up finding two schools that met these requirements and applied to both of those:
Excelsior College
Thomas Edison State University (TESU)

When I applied, the schools gave me a rough idea of which classes would be accepted as transfer, as well as which category they would be credited as. Once I enrolled at the school, I got an official evaluation of my credits.
Thomas Edison offered to transfer more credits so I enrolled with them.
I later learned of a third school that only requires two classes to be taken at their institution: Charter Oak State College

Once I enrolled at TESU, I met with a guidance counselor to go over my requirements. Everything laid out in my initial evaluation was approved. I had to pay an enrollment fee good for one year, as well as a fee for each class taken.

Selecting A Major
My credits were spread across several areas in the liberal arts, so after checking Thomas Edison’s requirements, I was the closest to earning a Liberal Studies degree.

Earning Credits
There was one Capstone class that I had to take at TESU, but I was free to earn the rest of the credits any way that I could, I didn’t necessarily have to take them from the school.
I had most of my pre-requisites completed, but one good resource for these classes is Georgia’s ECore site.
I ended up taking several classes from a third party company, Straighterline. Here you pay a monthly subscription fee ($99 when I took classes) plus a fee per class($50 or so). You take classes at your own pace, taking as long as you want or working through as quickly as you like. Each class used a textbook, which were easily bought used online. Readings in the text were assigned, and each module had a quiz that was administered online. Most classes had a midterm as well. Each class had a final exam, which was proctored by a third party company. You would take a final exam at home, but the proctor would monitor you via webcam as you tested, as well as monitor your desktop while you tested. You would schedule a time with the proctor, setup with your assigned proctor, and then take the test. Everything was graded immediately. Their site posted a list of all of their classes that TESU would accept, along with the class that it would be credited as, which was very useful in planning. Once you pass a class, you can have StraighterLine send a transcript to the school. Even though you are given a letter grade for the class, the credits will be transferred in as pass/fail. I found this the easiest and cheapest way to earn credits.
There are also several ways to earn credits by exam. For these, you’ll travel to a testing center to take the exam. The testing centers are mostly colleges that open their testing center to outside students (not all schools allow that). I took exams from two agencies:
DSST(formerly known as DANTES)

Both sets of exams were under $100 an exam. As long as you receive a passing grade, you will receive credit. Since the exams are pass/fail you don’t get a letter grade, it will just show up as credit on your transcript.
TESU publishes lists of the exams that they accepted for credit, and the class that you would get credit for.
To study for the exams, I used a site Instacert to prepare for the exams. They are a subscription site ($20 a month when I used them) that have practice questions with answers, I found them very useful. The site Free CLEP Prep also had links to other resources as well.
The exams were tough, but with a good bit of studying I was able to pass every attempt. I stuck to areas that I already knew very well, like history.
TESU and Exclesior also offer their own sets of exams for credit, but I didn’t take any of those.
Most exams are credited for lower level (100 or 200 level) classes. I did find a few that would transfer in for upper level credit. The History of the Vietnam War DSST exam was one, as well as the Introduction to Religion StraighterLine class.

After filling in my requirements, I was left with one class to take at TESU, their capstone class. Here, the sole activity of the class was to write a research paper, kind of a mini-thesis. We were free to select any topic in our major area. Computer Science fell under the Liberal Studies umbrella here, so I was free to select a programming-related topic. Each class has a professor assigned, who will help to formulate a topic. Every two weeks I would submit a chapter and would receive feedback on it. At the end of the class we would submit the full paper. It wasn’t a typical research paper, a lot of the writing was about my research methods as well. The paper had to be at least 25 pages, not including the bibliography and other supporting writings. A minimum grade of ‘C’ was needed.

Before the class was over, I was eligible to go ahead and apply for graduation, of course pending on getting a high enough grade. Once I completed the class, I was eligible to graduate in September of 2016.

SQL Code Guard

March 13, 2015

I ran across a free SSMS add-in SQL Code Guard. The add-in can run analysis on scripts to detect possible code issues, as well as display the dependencies for a database object. The add-in is also available through Redgate(along with some other free add-ins).

After downloading, unzip the file and run the EXE. The add-in will be installed in SSMS(Any version from 2005 onward). Both Chrome and Norton identified the file as a possible risk, since it doesn’t have a large number of downloads. Also, I needed to add a line to the SSMS.exe.config file under configuration/runtime(otherwise SSMS takes a long time to open).

Add-Ins: This is just a link to the Redgate site to list the other SSMS add-ins.
Run Analyze: This was the most useful function to me. This will analyze a script (or database object) for possible issues, things like an object reference without a schema name, an asterisk in a SELECT, deprecated SET options, etc.
Show Dependencies: Once a database is selected in the Object Explorer, this will list each database object with the objects it depends on as well as objects that refer to it. For a table, you can see the referring objects broken down by insert, update and so on.
Code Outline: This will break down a script into the operations that it performs. For example, if a table is being created, the outline will show CREATE TABLE dbo.Table. Clicking on that item will take you to that section of the script. So if you have a complicated script, this will help you to navigate to certain operations.

Database Unit Testing With tSQLt

February 25, 2015

Most .Net developers have probably written unit tests to test their code. Once tests are in place, refactoring and adding functionality can be done with more confidence, because the coder will be able to demonstrate that a given method will still function correctly. However, these unit tests remove the dependency on the database, so we still would want to set up tests for the database and database objects. Unit testing a database proves to be a little more difficult, since the data can change so frequently and we can’t count on the data to be in a given state.
tSQLt provides a framework for creating and running database-level tests. The tests are set up in T-SQL, so database developers are still able to work with the language that they’re most efficient in.
This framework will provide a way to create stored procedures that will test specific aspects in the database. The framework can execute all of the testing procs and alert us to any errors that arise. tSQLt will use transactions for the tests and will roll back any data changes, so the user can re-run tests without collision.

The tSQLt home page provides a link to download a set of tSQL scripts that will set up the testing framework. On the testing database, run the SetClrEnabled.sql first, which will set the database to TRUSTWORTHY and will enable CLR functions to be executed. The tSQLt.class.sql will set up the testing framework. The package also includes a Sample.sql script with some sample code.

Sample Database:
There are two scripts on GitHub: One to create tables, stored procedures and data for a sample database and a second to create the testing procs.

There is plenty of documentation on The tSQLt site on using the framework. In the UnitTest_Tests.sql script there are some simple examples.
First, we create a test class that will server as a namespace for the testing procedures. This will create a class TestClass.

exec tSQLt.NewTestClass 'TestClass';

Then we’ll create our stored procedure to test code. We’ll use the test class we created as a namespace for the proc.

create procedure TestClass.[TestDB_InsertPlayer]

Within the testing proc, we’ll use an assertion to compare the data we expect to the actual data. In one case, we’re testing an insert proc, so we’ll create the table as we expect it to the actual table. Within the test class, we’ll create an ‘Expected’ table with a schema that matches the table we’re testing. It will be compared to the ActualTable. If they don’t match, then the framework will throw an exception that will let us know the rows that don’t match.

exec tSQLt.assertEqualsTable 'TestClass.expected', 'ActualTable';

In other cases, we may have a function or a procedure that returns a value. We can compare that actual value to the expected value with an assertion.

exec tSQLt.AssertEquals @ExpectedValue, @ActualValue;

The tSQLt User Guide lists other assertions that can be used.
Finally, we execute all of the tests within our test class:

exec tSQLt.Run 'TestClass';

The tests won’t be executed in any particular order, so we can’t arrange the tests that depend on changes made by another test(the data changes are rolled back in any case).

Redgate GUI:
Redgate has created a GUI called SQL Test that provides a test runner user interface for the tSQLt framework. It is a paid application, but there is a 28 day free trial available.
I haven’t used the SQL Test program, but Troy Hunt has a good post on using it.

Work Sabbatical and Continuing Education

January 28, 2015

Over the Summer of 2014 my employer decided to close down their US Office. After several months of transitional work, I decided to take some time away from the work force and pursue some other goals. Some of those goals involved continuing education.

My biggest goal was to complete the Microsoft certification for SQL Server (MCSE: Data Platform). I wrote about that experience a little more in depth here. I took the upgrade path since I already held a SQL Server 2008 certification. There aren’t many books or articles that specifically address the upgrade exams, so in studying for the exams I relied heavily on MSDN and some posts on the regular track exams.

A lot has been written over the last year on Hadoop and big data, so I wanted to get some hands-on experience with this platform. I found a class on Udacity that covered the basics of Hadoop and Map Reduce. The course is available for free, but I decided to take the paid version which included a final project and a verified certificate upon completion. The Map-Reduce functions were written in Python, so I got an introduction to that as well. The class consisted of video lectures with quizzes to gauge your progress. The course took 20-25 hours to complete (you can work at your own pace) including a final project where you write functions and process some data. To pass, you’ll meet with a reviewer online who will go over the project and ask questions about it before you are verified.

Data Science:
I took a series of classes from Coursera on Data Science. This was 9 classes plus a project. The classes could be taken for free, but I signed up for the paid track, which gave you a verified certificate for each class, and then a specialization certification if you successfully completed the final (capstone) project. The classes covered R programming, machine learning, statistics and regression analysis. Each class was meant to run over one month, about 20 hours to complete. You could work at your own pace but there were deadlines to meet each week to keep you on track. The final class was over 7 weeks (you had to complete the first 9 classes first) where we constructed a web page and created an algorithm to predict the next word for a given set of words.
I found the R programming fairly easy to pickup (a lot of it is set-based like SQL) but the statistics classes were very challenging to me. I got a lot out of this specialization. None of the classes carry any college credit, but they are sponsored by John Hopkins.

Currently I’m taking a MongoDB for Developers class from MongoDB. I’m taking the Python class, although a .Net version will be available starting in March 2015. This is a free class that goes over 7 weeks. Each week the assignments are released, so you can work on each week’s assignments at your own pace, but only one week at a time. So far, it’s taken me 4-5 hours a week to watch the video lectures and complete the assignments. The 7th week is a project that counts as the final exam.
(Updated 4/8): I completed the course in February. I certainly recommend the course to anyone looking to learn about MongoDB.

College Coursework:
I never finished my degree when I went to college out of high school so I’ve looked into getting back on track to complete that. I’ve taken and passed a CLEP exam in Sociology, and I’m currently taking a class through Straighterline which can be transferred to a degree-granting institution.
(Updated 4/8): I ended up taking 4 classes from Straighterline. Their final exams are proctored, but you can take them at home, and have a proctor monitor you via webcam. They offer a very convenient way to take classes. I’m also taking some DSST exams(similar to CLEP exams) for some upper level credit.

Non-Educational Activities:
I took up running a couple of years ago, so I’ve been able to dedicate more time to that. Currently I’m training for a marathon and I’m targeting the Publix Georgia Marathon in March 2015.
(Updated 3/22): I completed the marathon, so now I’m looking forward to the Peachtree Road Race on the 4th of July.

E-Books, Podcasts and NoSQL

January 12, 2015

Some database odds and ends:
1) The DotNet Rocks podcast recently had an episode with David Simons. He went over 10 different database systems, ranging from relational to NoSQL. Slides from his presentation are available on Slideshare.

2) A couple of free E-books – Free but they requires setting up a login or filling out your contact information:

A. Time Series Databases: New Ways to Store and Access Data by Ted Dunning and Ellen Friedman
An intro to storing Time-Series data (recording a value over a period of time)

B. MarkLogic – Enterprise NOSQL For Dummies
A basic overview of NoSQL and how it compares to a RDBMS

C. Some database-related books from Syncfusion – These are 100 page books (PDF or Mobi) that are great introductions to a topic:

3) I’ve added a Database Systems page to list and link to open source relational and NoSQL database systems. I’ll update this list periodically.