{"id":82056,"date":"2009-06-17T18:25:20","date_gmt":"2009-06-17T18:25:20","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73251"},"modified":"2018-12-12T12:21:47","modified_gmt":"2018-12-12T12:21:47","slug":"sql-quiz-gilligans-island","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-quiz-gilligans-island\/","title":{"rendered":"SQL Quiz: Gilligan\u2019s Island"},"content":{"rendered":"

Tagged, I was by the Rambling SQLSarg, Jonathan Kehayias<\/a>, to answer the following question:<\/p>\n

So You\u2019re On A Deserted Island With WiFi and you\u2019re still on the clock at work. Okay, so not a very good situational exercise here, but let\u2019s roll with it; we\u2019ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you\u2019ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?<\/em><\/p>\n

The funny part is that I have almost always been isolated like this at any of my jobs, unless a big project was underway and I was the roadblock that other people had to get their part of the project done.  I have at times had teammates that I worked with closely, one of them was an exceptional dba that had only been a dba for a year or two, and the others were more project managery, and once I worked with a UI guy who I worked with to build an excellent UI that was generated from a call to a stored procedure to get the metadata for the object. <\/p>\n

So often I actually do this, planning what I think would be the best way to implement our current systems or future ones and how best they would be could be built using my own personal ways of doing stuff. Ironically, more often than not, it isn\u2019t feasible to do in my own little world, but it becomes good advice for others (hopefully who don\u2019t have large amounts of legacy code piled up to deal with.  In my real world, reality becomes that we have meetings and meetings to discuss and committee things until we eventually fix the legacy stuff enough to go for a good long time.  Sadly reality has a lot to do with money, available resources (human and computer), etc whereas in the mind of an architect, the only barrier during the pure design part of a project is physics.  <\/p>\n

For example, take the following two designs of a house that an house architect might think up (perhaps after taking some oxycodone for a back (or hip) problem:<\/p>\n

\"image\"<\/a> <\/p>\n

Clearly the idea in B would be nixed because even an architect knows enough about physics to know that this would tip over, where as A would be much safer.  (These images should also explain why I did not finish my degree in Civil Engineering.)<\/p>\n

So, assuming that I am on this deserted island alone, with what would I research? Well, let\u2019s just use a Genie clause and ignore the fact that just like Genie wouldn\u2019t allow Aladin to wish for more wishes, I won\u2019t state the obvious that I would study up on boat making and praying for rescue. And even assuming that I knew that someone was coming back for me (hopefully not the crazy Dr. Boris Balinkoff coming to change my brain with a duck), I would spend a good amount of my off time looking for something to surf with\/something to avoid sunburn, and probably something good for lunch\u2026<\/p>\n

1. A really good explanation for Fifth Normal Form that didn\u2019t include mind numbing technical language that makes you brain hurt.<\/strong><\/p>\n

I get the basic idea behind the fifth normal form, but unlike even the fourth normal form, I can\u2019t come up with an explanation that isn\u2019t basically the same as how your mom and dad explained why not to mess with the little red haired girl with the bad reputation.  \u201cDon\u2019t violate this rule, or else bad things will occur\u201d  <\/p>\n

\u201cBad things?\u201d, you ask.<\/p>\n

\u201cLike babies, and shotguns\u201d but at this point, you aren\u2019t really sure how pulling the hair of the this little girl will really hurt things. And every explanation of the entire problem you can find on the internet (with safe search turned on!) is like a college course in anatomy. You of course simply know<\/em> you will never do that<\/em> so you ignore it.<\/p>\n

All explanations for 5th Normal Form are either of the \u201cdon\u2019t do it\u201d, \u201cdoesn\u2019t matter\u201d, \u201cor 30 pages of terse text with lots of formulas that look nothing like the CREATE TABLE statement you have grown to love over the years.  <\/p>\n

2. Work on\/Design\/Create a data access layer tool that allows you to set up the objects that you want and generates procedures and functional code<\/strong><\/p>\n

Look, I understand your pain programmers.  You like things to be easy.  You don\u2019t like to write a bunch of \u201ccode\u201d to solve problems.  You like it when some tool dictates to you how things are going to work internal to your programs and you want to just let it happen.  We relational types are not really like that.  Sure, most compiled code nowadays will either run in 10 or 100 ns depending on how you write it, and no one really cares about 90 ns unless they are building a website along the scale of ESPN or MySpace. But in the relational world, we are dealing with fetching data from physical storage which is inherently s l o w. It gets better and better every year, but most companies react to this increase in performance by increasing their desire to store data at a pace that is slightly faster than the improvement in technology.<\/p>\n

However, there are still a majority of companies out there that could still be using SQL Server 1.0 on 16 bit OS\/2 (okay, I am exaggerating, maybe SQL 6.0) that don\u2019t care so much about performance and just want to get programming done fast.  Tools often cater to these entities and not to to the larger systems needs.  As relational types, a majority of us constantly bellow \u201cstored procedures\u2026stored procedures\u201d on the corner like the town crier.  The problem is, tools generally want to ignore this layer, primarily for time and programmer experience.  Why write SQL code when you can let the tool do it? <\/p>\n

But if a tool can generate SQL, why can\u2019t an object layer be built that automatically creates procedures? And like any generated code, if you don\u2019t monkey with it, the generator can create a new version as you need it.  If you do have to make changes, likely to use some query technique that is not easily automated, the code generator just throws up it\u2019s hands and says \u201cI am trusting you brother\u201d (or sister.)<\/p>\n

Maybe then the topic of stored procs could finally be retired and we could just leave it alone.  This tool, the best on the market, gives you both in a manner that is EASY to do, FAST to build, and POWERFUL enough to do extremely complex configurations\u2026<\/p>\n

3.  Getting better at data warehousing. <\/strong><\/p>\n

I have designed and implemented a data warehouse, including ETL (both with the assistance of my friend and fellow Data Architect, Frank Castora). It was quite the mind bending experience, as normalization becomes a \u201cbad thing\u201d and denormalizaiton is a \u201cgood thing\u201d.  From 15 years of OLTP development and writing, this felt like a sin that was going to end up with me getting a pitchfork to the tush for eternity.  But as the project went on, it started to feel natural and the dimensionally modeled tables stored in the SQL database started to feel semi-natural and really started to work for me.  But there were quite a few things that we did \u201cwrong\u201d as well as many that we did \u201cnot really wrong but not exactly correct either\u201d that had to do with different scenarios that didn\u2019t fit the examples that we could find on the web.  <\/p>\n

Add to that that Analysis Services and the language MDX both were quite foreign to me that the project didn\u2019t exactly \u201cfail\u201d, but it certainly didn\u2019t make the \u201csucceed\u201d column either.  The project is more or less ongoing yet in a pattern of stasis that Dave Lister (not the guy I work with but the guy from Red Dwarf who was frozen for a million years only to wake up with everybody gone except for electronic holograms and computers) would appreciate. If I knew more about all of the processes\/tools, I could go far towards making things better and getting things straight.<\/p>\n

 <\/p>\n

So, whom would I like to see deserted on an island? Um, I mean, whom would I like to see how they would respond to this challenge\u2026 hmm  Paul Nielsen<\/a> doesn\u2019t have anything to do these days (ha!), and Allen White<\/a> is a very interesting guy who I would like to see his feelings too.<\/p>\n","protected":false},"excerpt":{"rendered":"

Tagged, I was by the Rambling SQLSarg, Jonathan Kehayias, to answer the following question: So You\u2019re On A Deserted Island With WiFi and you\u2019re still on the clock at work. Okay, so not a very good situational exercise here, but let\u2019s roll with it; we\u2019ll call it a virtual deserted island. Perhaps what I should……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82056","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82056","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82056"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82056\/revisions"}],"predecessor-version":[{"id":82304,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82056\/revisions\/82304"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82056"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82056"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}