Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Stairway to Database Design STEP 2: Domains, Constraints and Defaults Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, May 4, 2010 12:04 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 5:50 PM Points: 1,945, Visits: 3,509
 Comments posted to this topic are about the item Stairway to Database Design STEP 2: Domains, Constraints and Defaults Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #915087
 Posted Tuesday, May 4, 2010 8:08 AM
 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 GarthVertical 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
Post #915334
 Posted Tuesday, May 4, 2010 9:29 AM
 SSC Journeyman Group: General Forum Members Last Login: Monday, September 22, 2014 12:12 PM Points: 86, Visits: 142
 Two comments on the comment1. 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 1-5 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.
Post #915409
 Posted Tuesday, May 4, 2010 10:22 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 5:50 PM Points: 1,945, Visits: 3,509
 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 five-point preference scale (aka Likert scale) has another problem; people can have non-transitivity when presented in pairs (ever play "scissor, paper, stone" ?). Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #915457
 Posted Tuesday, May 4, 2010 1:30 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 7:46 AM Points: 998, Visits: 7,839
 Temperature always needs mass or volume, pressure, time and maybe something else for math to make sense.This seems to be a non-sequiter. You said in the articleWhile 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
Post #915610
 Posted Tuesday, May 4, 2010 3:05 PM
 SSC-Enthusiastic 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 NULLCHECK (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 NULLCHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F' WHEN floob_score = 42 AND fuzz_nbr = 17 THEN 'T' ELSE 'F' END)`
Post #915682
 Posted Wednesday, May 5, 2010 11:28 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 5:50 PM Points: 1,945, Visits: 3,509
 -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 Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #916309
 Posted Wednesday, May 5, 2010 12:43 PM
 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 NULLCHECK (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 single-quote 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 single-column constraint. At the very least, this requires a comma after the NOT NULL on the first line, making the check constraint be an un-named 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'),`
Post #916359
 Posted Wednesday, May 5, 2010 2:25 PM
 SSC-Enthusiastic 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')`
Post #916473
 Posted Tuesday, August 24, 2010 9:36 AM
 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 non-proprietary. Can you offer any explanation?Thanks,Tammy
Post #974232

 Permissions