Saturday, August 5, 2017

This Week



1. Database Truth of the Week

"Semantic correctness: every interpretation of the symbols (meaning assignment and truth value assignment) that makes the axioms true, makes the theorems true. When we extend a logical data model with semantics (specific to the subject matter and its "business" rules) via constraints, those constraints become axioms that must be true." --David McGoveran


2. What's Wrong With This Database Picture?

"In a book I am reading the author talks about theory of how databases work. He mentions relations, attributes and tuples etc. He frequently stresses the fact that some aspect of T-SQL is not relational. Like in the following excerpt:
"Unlike in previous phases where the result was relational, the output of this phase isn’t relational because it has a guaranteed order. The result of this phase is what standard SQL calls a cursor. Note that the use of the term cursor here is conceptual. T-SQL also supports an object called a cursor that is defined based on a result of a query, and that allows fetching rows one at a time in a specified order. You might care about returning the result of a query in a specific order for presentation purposes or if the caller needs to consume the result in that manner through some cursor mechanism that fetches the rows one at a time. But remember that such processing isn’t relational. If you need to process the query result in a relational manner—for example, define a table expression like a view based on the query (details later in Chapter 4)—the result will need to be relational. Also, sorting data can add cost to the query processing. If you don’t care about the order in which the result rows are returned, you can avoid this unnecessary cost by not adding an ORDER BY clause."
I would like to know, since every implementation of SQL pretty much has an ORDER BY clause which makes it non-relational, why does it even matter that (the set after ORDER BY is used) its not relational anymore since its like that everywhere? I can understand if he said it was non standard, for example using != instead of <> for inequality because that affects portability etc., but I do not understand why something is better being relational. Please enlighten." --Why is being relational important in SQL implementations? stackoverflow.com

3. To Laugh or Cry?

"I am working on Data Modeling for a database (DB2). I need help on deciding keys for the table. Table T1 has columns Col1 Col2 Col3 Col4 Col5, etc. The natural key is a composite key (Col1, Col2, Col3, Col4). In most of the scenarios, Col1,2 and 3 can be the same, and they can be distinguished apart by Col4. So in order to normalize the table, I split the tables as
  • Table T1 --> Col1,Col2, Col3 P.Key-->Surrogate Key, make the combination unique(Unique Key/index)
  • Table T2--> T1.Surrogate KEy, Col4, Col5, etc P.Key-->(T1.SK, Col4)
My questions are:
  • Are Surrogate Keys better than natural (composite) PKs in this scenario?
  • Will index rebuilding/maintenance become a major issue, because this is a transactional table, and will be hit a lot of times during the day?
  • This table will be joined with multiple tables, so will surrogate keys cause overheads while hashing? Which one is better for joins in this case? Surrogate Key as a PK or composite natural key as PK?
Please advise which is a better approach, Composite PK or Surrogate Key for this scenario." --StackOverflow.com
Anybody who can make any sense of this, please email me via the ABOUT page. 

 

4. Publications


Reviews 

 

5. Oldies but Goodies

Fabian Pascal Loves Me

6. Interesting

 

And Now for Something Completely Different

@The PostWest: How I Know America (and Western Civilization) Is Finished!

  • The President Is a Fraud
  • Internal Collapse
  • External Demise
  • Soft Target: The Only Acceptable Racism Left
  • The Myth of a "Palestinian Nation": Nice People, Let's Give Them a[n ISIS] State
  • Upside Down and Backwards
  • Pinch Me's of the Week
  • Book of the Week

Technology Corporations: The Mechanism of Tyranny and Destruction of Free Civilized Society


Facebook

Amazon

Google

Other

 

Book of the Week (Order via this link to support this site)

Jonathan Taplin, MOVE FAST AND BREAK THINGS How Facebook, Google, and Amazon Cornered Culture

Review: The ‘attention economy’ created by Silicon Valley is bankrupting us


Note: I will not publish or respond to anonymous comments. If you want to say something, stand behind it. Otherwise don't bother, it'll be ignored.
Posted by at

1 comment:

  1. Erwin SmoutAugust 7, 2017 at 2:24 PM

    About Cosette. They illustrate using an example involving UNION ALL and claim that "These two queries will produce the exact same results no matter what the input relations r and s contain, and no matter what the predicate p is.". That claim is wrong imo. If the columns in r and s do not "match" ***by ordinal position*** then there are name mismatches and in that case, the standard says that the column names in the result of the union are "implementation-dependent" and furthermore, the standard does not prescribe that an implementation must generate the same name again for the same scenario of name mismatches.

    ReplyDelete

Subscribe to: Post Comments (Atom)
View My Stats