SQL

Compare All Attributes of Two Tables

Recently I had to compare lots of sets of data. Determining if there are any differences at all, or if there are rows that exist in one but not the other, is easy. But I needed to compare every column, calculate match percentages, and report every difference.

I was able to take what would have been a lot of tedious query writing and create a standard way to do it. All you have to do is a little bit of prep on your two data sets, and these queries will compare, quantify, and report all mismatches. (more…)

First Normal Form (1NF)

Definition

Each row contains one value, of the appropriate type, for each column, and there are no duplicate rows.

This is a simple rewording of the formal definition:

Let relation r have attributes A1, …, An, of types T1, …, Tn, respectively. Then r is in first normal form (1NF) if and only if, for all tuples t appearing in r, the value of attribute Ai in t is of type Ti (i= 1, …, n).”
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)

(more…)

How to Prevent Overlapping Date Ranges in a Table

Have you ever seen a table where each row had a ‘start date’ and an ‘end date’? I’m sure most of us have seen one or created one.

These tables tend to pose a key problem (no pun intended) if the following business logic applies: “If two or more rows represent different date ranges for a single item, the ranges cannot overlap.” In other words, the date range must be unique from the start date until the end date for any particular item represented by the table. (more…)