

SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 5:33 AM
Points: 1,945,
Visits: 3,497


Comments posted to this topic are about the item Stairway to Database Design STEP 2: Domains, Constraints and Defaults
Books in Celko Series for MorganKaufmann Publishing Analytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQL SQL for Smarties SQL Programming Style SQL Puzzles and Answers Thinking in Sets Trees and Hierarchies in SQL




SSC Eights!
Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977,
Visits: 1,499


Good article Joe. I think you might want to add a link to your Step 1 article.
This is a note and not a criticism: Many people do math with temperatures. Add 1 BTU to 1 pound of water at 32 degrees and the new temperature will be 32 degrees + 1 degree. However math isn't done on "Customer Numbers" or "Item Numbers".
Thanks.
Tom Garth Vertical Solutions
"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves."  Will Rogers




SSC Journeyman
Group: General Forum Members
Last Login: Monday, September 22, 2014 12:12 PM
Points: 86,
Visits: 142


Two comments on the comment 1. There are easier examples of how much legitimately can be done with temperatures. First, in some ways temperature is like the date domain  it makes perfect sense to subtract two temperatures (an interval result), while other arithmetic operations make less sense. Second, there are plenty of places in thermodynamics where arithmetic is done using temperatures (in degrees Kelvin). The maximum efficiency of a heat engine operating between two temperatures is (T2  T1)/T2. The ideal gas law is PV=nRT; here temperature takes part in multiplication (although not by other temperatures.)
Thinking about it, I have a hard time coming up with a legitimate numeric domain that does not allow at least something lilke interval calculations. Something less amenable to arithmetic would be 15 ranking for like/dislike. Though we often compute an average, that is probably not legitimate unless one assumes that like/dislike is a linear scale.
2. In my opinion, Customer Numbers and Items Numbers don't belong to the numeric domain. I usually argue that these are string values which are restricted to digit characters. As just one argument, there are generally minimum and maximum length restrictions on such values which are hard to specify for integers, but are obvious for strings.




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 5:33 AM
Points: 1,945,
Visits: 3,497


Many people do math with temperatures. Add 1 BTU to 1 pound of water at 32 degrees and the new temperature will be 32 degrees + 1 degree. However math isn't done on "Customer Numbers" or "Item Numbers".
Temperature always needs mass or volume, pressure, time and maybe something else for math to make sense. The fivepoint preference scale (aka Likert scale) has another problem; people can have nontransitivity when presented in pairs (ever play "scissor, paper, stone" ?).
Books in Celko Series for MorganKaufmann Publishing Analytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQL SQL for Smarties SQL Programming Style SQL Puzzles and Answers Thinking in Sets Trees and Hierarchies in SQL




SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 21, 2015 4:09 PM
Points: 998,
Visits: 7,837


Temperature always needs mass or volume, pressure, time and maybe something else for math to make sense.
This seems to be a nonsequiter. You said in the article
While I can add, subtract, multiply and divide integers, it makes no sense to do math like that on temperatures. Adding in criteria like that after the fact indicates the original statement was lacking. And needing other criteria doesn't mean "it makes no sense to do math like that on temperatures", it means you need other criteria and then you DO do math like that on temperatures. Average high temp for a given day, average body temp for patients presenting specific conditions, average boiling temp for a substance given a constant starting mass, volume, etc... all of these are simply averages of temperatures, the other variables are there, but don't actually affect the math done to average the temperatures.
 When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.  It’s unpleasantly like being drunk. What’s so unpleasant about being drunk? You ask a glass of water.  Douglas Adams




SSCEnthusiastic
Group: General Forum Members
Last Login: Wednesday, April 22, 2015 1:43 PM
Points: 117,
Visits: 855


Nice article.
Can you explain what this means? Is there a typo somewhere?
floob_score INTEGER CHAR(5) NOT NULL
CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
WHEN floob_score = 42
AND fuzz_nbr = 17
THEN 'T' ELSE 'F' END = T'),
Is it supposed to be:
floob_score INTEGER, fuzz_nbr INTEGER, valid_floob CHAR(5) NOT NULL
CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
WHEN floob_score = 42
AND fuzz_nbr = 17
THEN 'T' ELSE 'F' END)




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 5:33 AM
Points: 1,945,
Visits: 3,497


No, CASE is an expression and returns a scalar value. That is they we need a theta operator to get a truth value in the CHECK() constraint:
CHECK ( CASE WHEN .. THEN 'T' ELSE 'F' END = 'T')
Books in Celko Series for MorganKaufmann Publishing Analytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQL SQL for Smarties SQL Programming Style SQL Puzzles and Answers Thinking in Sets Trees and Hierarchies in SQL




SSC Journeyman
Group: General Forum Members
Last Login: Monday, September 22, 2014 12:12 PM
Points: 86,
Visits: 142


I think there are points made by each poster that are missed by the other in the last two posts. Here's the code from the article:
floob_score INTEGER CHAR(5) NOT NULL CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F' WHEN floob_score = 42 AND fuzz_nbr = 17 THEN 'T' ELSE 'F' END = T'), 1. Joe  The first line looks like something is extra. It is a declaration for floob_score, but has both integer and char(5) for the data type. It would be OK if the char(5) was not there. This is part of the confusion with the example.
2. Tom  The CHECK() constraint is the intended expression (except for the missing singlequote in front of the final T). Joe is correct in that you can't drop the final ='T'. But Joe  the CASE expression involves two variables  floob_score and fuzz_nbr  making it a table constraint, not a singlecolumn constraint. At the very least, this requires a comma after the NOT NULL on the first line, making the check constraint be an unnamed table constraint, and fuzz_nbr just not appearing in the example.
My suggestion for what it is meant to look like is the following.
fuzz_nbr INTEGER NOT NULL, floob_score INTEGER NOT NULL, CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F' WHEN floob_score = 42 AND fuzz_nbr = 17 THEN 'T' ELSE 'F' END = 'T'),




SSCEnthusiastic
Group: General Forum Members
Last Login: Wednesday, April 22, 2015 1:43 PM
Points: 117,
Visits: 855


Thanks. It was that extra CHAR(5) that was throwing me off.
And for readability, would it help to put some extra () like this:
CHECK ((CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F' WHEN floob_score = 42 AND fuzz_nbr = 17 THEN 'T' ELSE 'F' END) = 'T')




Forum Newbie
Group: General Forum Members
Last Login: Monday, October 22, 2012 6:30 AM
Points: 7,
Visits: 33


Excellent series! A good look at many basic principles. Excuse the late post, but I'm reading all four articles together.
In Step 2, you mention the three major SQL data types: numeric, string & temporal. You mention that there are other data types in SQL, but refer to these as the "Big Three".
As a GIS (Geographic Information Systems) professional who is only just migrating our database environment from MS Access (gasp!) to SQL Server 2008, I was a little sorry not to see any reference to the newly implemented Spatial data type. This is something that our office is excited about taking advantage of in order to make our data truly nonproprietary. Can you offer any explanation?
Thanks, Tammy



