THE DUDE WITH THE TRICYCLE (A GREEK MOVIE OF 1972) AND THE GREEK REFERENDUM

Ο ΜΑΓΚΑΣ ΜΕ ΤΟ ΤΡΙΚΥΚΛΟ (ΕΛΛΗΝΙΚΗ ΤΑΙΝΙΑ ΤΟΥ 1972) ΚΑΙ ΤΟ ΔΗΜΟΨΗΦΙΣΜΑ


THE DUDE WITH THE TRICYCLE (A GREEK MOVIE OF 1972) AND THE GREEK REFERENDUM



ΤΡΟΠΟΠΟΙΗΜΕΝΟ ΚΕΙΜΕΝΟ (από το Δημήτριο Καλέμη)
MODIFIED SCRIPT (by Dimitrios Kalemis)

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Τι θες, παιδί μου, και φωνάζεις; Α, η Ευρωπαϊκή Ένωση! Χαίρετε. Τι κάνετε; Πώς από δω;
What’s all this commotion? Oh, it’s the European Union. How do you do? I didn’t expect you here.

ΕΥΡΩΠΑΪΚΗ ΕΝΩΣΗ – EUROPEAN UNION:
Ζητήσατε να επαναδιαπραγματευτείτε. Για αυτό και εγώ σας έφερα την αρμόδια για τις διαπραγματεύσεις.
You asked for a renegotiation. That’s why I brought here the person in charge for the negotiations.

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Α, χάρηκα πάρα πολύ!
Oh, pleased to meet you!

ΕΥΡΩΠΑΪΚΗ ΕΝΩΣΗ – EUROPEAN UNION:
Είναι ο πρωθυπουργός που σας έλεγα.
He is the Prime Minister I told you about.

ΑΡΜΟΔΙΑ ΕΥΡΩΠΑΪΚΗΣ ΕΝΩΣΗΣ – PERSON IN CHARGE OF EUROPEAN UNION:
Α! Περίεργο, όμως. Πολύ περίεργο.
Oh! Strange thing, though. Very strange.

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Τι πράμα;
What is?

ΑΡΜΟΔΙΑ ΕΥΡΩΠΑΪΚΗΣ ΕΝΩΣΗΣ – PERSON IN CHARGE OF EUROPEAN UNION:
Αφού δε θέλετε την Ευρωπαϊκή Ένωση και το Ευρώ, γιατί έρχεστε σε μας να επαναδιαπραγματευτείτε;
Since you do not want the European Union and the Euro, why do you come to us to renegotiate?

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Μην το λέτε αυτό! Μη με καίτε, σας παρακαλώ!
Don’t say that! Don’t embarrass me, please!

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Χα χα! Ρε Αλέξη! Ρε Αλέξη! Τι μου λένε εδώ τα μορτάκια; Στο δημοψήφισμα λέει που έκανες βγήκε ΟΧΙ;
Ha ha! Yo, Alexi! Yo, Alexi! What is this I hear from the guys? Is it true that the referendum result was “NO”?

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Μη βαράτε όλοι μαζί, ρε παιδιά! Φάε το φαγάκι σου, Σταυράκη μου! Άμα τρώνε δε μιλάνε. Το λέει και ο γιατρός.
Don’t laugh at me all at once, you guys! Eat your nice food, old pal Stavro! When people eat, they should not talk. Doctor’s advice.

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Όχι, γιατί πολύ το χάρηκα δηλαδή.
I’m asking this, because I’m really glad for you.

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Ωραία. Πιες τη μπυρίτσα σου. Θες μπυρίτσα;
Ok. Drink your nice cold beer. You want this nice cold beer, don’t you?

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Μμμ. Κερνάς να πούμε για τα καλορίζικα;
Mmm. I assume it’s on the house because you are celebrating the good news?

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Βρε, φάε και μη μιλάς.
Just eat and don’t speak.

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Όχι, γιατί τα παιδιά μου λέγανε ότι τους έλεγες ότι έμαθαν στη Ευρώπη για το δημοψήφισμα και πέσανε τάβλα με το ΟΧΙ.
I’m mentioning this, because the guys told me that you told them that the European Union were intimidated by the result of “NO”.

ΑΛEΞHΣ ΤΣΙΠΡΑΣ – ALEΧIS TSIPRAS:
Πολύ ζημιάρικο παιδί είσαι, ρε Σταύρο! Φάε τη μπουκίτσα σου και μη μιλάς εκεί πέρα.
You are a clumsy child that does a lot of damage, Stavro! Eat this nibble and don’t talk at all.

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Σιγά, βρε! Γιατί, δηλαδή! Τι έκανα; Έτσι μου είπανε, ότι έμαθαν λέει στην Ευρώπη για το ΟΧΙ και ταβλιαστήκανε όλοι!
Take it easy! Why? What did I do? That’s what they told me, that the European Union learned about the result and they were scared!


ΑΡΧΙΚΟ ΚΕΙΜΕΝΟ
INITIAL SCRIPT

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Τι θες, παιδί μου, και φωνάζεις; Α, ο κύριος διευθυντής! Χαίρετε. Τι κάνετε; Πώς από δω;
What’s all this commotion? Oh, it’s the Director at the modeling agency. How do you do? I didn’t expect you here.

ΚΩΣΤΑΣ ΡΗΓΟΠΟΥΛΟΣ – KOSTAS RIGOPOULOS:
Έ, τέλος του μηνός, βλέπετε. Ή όχι μάλλον. Έφερα τη δεσποινίδα Τόνια. Είναι η ιδιοκτήτρια της εταιρείας μας.
Oh, it’s the end of the month, you see. Or, I guess not. I brought miss Tonia with me. She is the owner of our modeling agency.

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Α, χάρηκα πάρα πολύ.
Oh, pleased to meet you!

ΚΩΣΤΑΣ ΡΗΓΟΠΟΥΛΟΣ – KOSTAS RIGOPOULOS:
Είναι η κοπέλα που σας έλεγα.
She is the young lady I told you about.

TONIA KAZIANH – TONIA KAZIANI:
Α! Περίεργο, όμως. Πολύ περίεργο.
Oh! Strange thing, though. Very strange.

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Τι πράμα;
What is?

TONIA KAZIANH – TONIA KAZIANI:
Αφού έχετε μια τόσο καλή δουλειά, γιατί έρχεστε σε μας να κάνετε την καθαρίστρια;
Since you have such a good job here in this restaurant, why did you come to us to be a cleaning lady?

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Μη το λέτε αυτό! Μη με καίτε, σας παρακαλώ!
Don’t say that! Don’t embarrass me, please!

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Χα χα! Ρε Μαρθάκι! Ρε Μαρθάκι! Τι μου λένε εδώ τα μορτάκια; Σε προσλάβανε λέει για φωτοτέτοιο;
Ha ha! Yo, Martha! Yo, Martha! What is this I hear from the guys? Is it true that you were hired as a model?

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Μη βαράτε όλοι μαζί, ρε παιδιά! Φάε το φαγάκι σου, Σταυράκη μου! Άμα τρώνε δε μιλάνε. Το λέει και ο γιατρός.
Don’t laugh at me all at once, you guys! Eat your nice food, old pal Stavro! When people eat, they should not talk. Doctor’s advice.

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Όχι, γιατί πολύ το χάρηκα δηλαδή.
I’m asking this, because I’m really glad for you.

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Ωραία. Πιες τη μπυρίτσα σου. Θες μπυρίτσα;
Great. Drink your nice cold beer. You want this nice cold beer, don’t you?

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Μμμ. Κερνάς να πούμε για τα καλορίζικα;
Mmm. I assume it’s on the house because you are celebrating the good news?

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Βρε, φάε και μη μιλάς.
Just eat and don’t speak.

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Όχι, γιατί τα παιδιά μου λέγανε ότι τους έλεγες ότι σε μπανίσανε και πέσανε τάβλα με την ομορφιά σου.
I’m mentioning this, because the guys told me that you told them that the modeling agency directors saw you and they were astonished by your beauty.

ΜΑΡΘΑ ΚΑΡΑΓΙΑΝΝΗ – MARTHA KARAGIANNI:
Πολύ ζημιάρικο παιδί είσαι, ρε Σταύρο! Φάε τη μπουκίτσα σου και μη μιλάς εκεί πέρα.
You are a clumsy child that does a lot of damage, Stavro! Eat this nibble and don’t talk at all.

ΣΤΑΥΡΟΣ ΠΑΡΑΒΑΣ – STAVROS PARAVAS:
Σιγά, βρε! Γιατί, δηλαδή! Τι έκανα; Έτσι μου είπανε, ότι σε είδανε λέει και ταβλιαστήκανε όλοι!
Take it easy! Why? What did I do? That’s what they told me, that the people in the modeling agency could not believe how beautiful you are!

Posted in Politics

Visualize the Services Graph of your Windows OS

Your Windows OS has a lot of services. You can list them in PowerShell using the command

Get-Service

You can find out how many there are using the command

Get-Service | Measure –Object

Some of these services are completely independent. Others require one or more other services and /or one or more other services depend upon them. Thus, multiple hierarchies are formed, where a service can have zero or more required services and where zero or more services depend upon it. The services with their dependencies form a structure called “graph”. A graph consists of vertices and edges. The following graph is a theoretical graph that I made and that depicts the inter-dependencies of the Windows OS services. Vertices are depicted with ovals and edges are depicted with arrows.

Example

An oval represents one service. An arrow from a service A to a service B means that service B depends upon service A. We can see that between two services there can be at most one arrow. Also, a service can depend upon zero or more services. In addition, a service can have zero or more dependent services.

In the example graph, Service 03 depends upon Service 01 and Service 02. Service 03 is required by Service 04 and Service 05. Service 03 is also required by service 06, since Service 06 depends upon Service 05.

Thus, the dependencies are transitive. Service 06 depends upon Service 01, Service 02, Service 03, and Service 05.

We also notice that not all services are interrelated among themselves; instead, independent subgraphs are formed. Service 01 to Service 06 form one isolated subgraph, Service 07 to Service 09 form another isolated subgraph, Service 10 forms a subgraph all by itself, since it is completely independent. The same goes for Service 11. Thus, in this theoretical example, we have four independent subgraphs.

A graph that has these properties is a directed, acyclic, disconnected graph.

It is a directed graph (a.k.a. digraph), because the relationships are directed. This is why we are using arrows.

It is acyclic, because loops (cycles) are not formed. If a service depends upon another service, then the opposite cannot happen. In our example, we have no loops. A loop would have been formed between Service 02, Service 03, and Service 05, had the arrow from Service 02 to Service 05 been drawn in the opposite direction. But this is something that does not happen in the case of Windows services.

It is disconnected, because isolated subgraphs are formed. There is a path from Service 03 to Service 06, but there is no path from Service 03 to Service 07 or to Service 10.

Now we know theoretically how the services of any Windows OS relate to each other. It would be nice to see this in practice. Thankfully, we can easily view the dependencies of the services. The Services snap-in is an excellent tool for this purpose. All services are listed and the properties for each service have a tab named “Dependencies”. There we can see the services (and system drivers) that the service depends upon and we can also see the services (and system drivers) that depend upon the service. Not only that, but we can view this information recursively. This means that we can see not only the dependencies, but also the dependencies of those dependencies and so on.

Is there a way to obtain this information using PowerShell? Certainly. To obtain only the first level dependencies of each service, we can use the following script, named Get-ServiceGraph.ps1:

$services = Get-Service 

foreach ($service in $services)
{ 
   $output = $service.DisplayName
   $output 

   if ($service.DependentServices)
   { 
      $output = "   The following services depend on " + $service.DisplayName
      $output

      foreach ($ds in $service.DependentServices)
      { 
         $output = "      " + $ds.DisplayName
         $output 
      }
   }

   if ($service.ServicesDependedOn)
   { 
      $output = "   The following services are required by " + $service.DisplayName
      $output

      foreach ($rs in $service.ServicesDependedOn)
      { 
         $output = "      " + $rs.DisplayName
         $output
      }
   }  
}

We can redirect the output of this script to a file, and then, we can read the file with our favorite text viewer.

To obtain the dependencies recursively, just like it is done on the “Dependencies” tab of the properties in the services snap-in, we can use the following script, named Get-ServiceGraphRecursively.ps1:

function List-DependentServices ($inputService, $inputPadding)
{
   if ($inputService.DependentServices)
   { 
      $padding = "   " + $inputPadding

      $output = $padding + "The following services depend on " + $inputService.DisplayName
      $output

      $padding = "   " + $padding

      foreach ($ds in $inputService.DependentServices)
      {
         $output = $padding + $ds.DisplayName
         $output

         List-DependentServices $ds $padding
      }
   }
}

function List-ServicesDependedOn ($inputService, $inputPadding)
{
   if ($inputService.ServicesDependedOn)
   { 
      $padding = "   " + $inputPadding

      $output = $padding + "The following services are required by " + $inputService.DisplayName
      $output

      $padding = "   " + $padding

      foreach ($rs in $inputService.ServicesDependedOn)
      {  
         $output = $padding + $rs.DisplayName
         $output

         List-ServicesDependedOn $rs $padding
      }
   } 
}

$services = Get-Service

foreach ($service in $services)
{
   $output = $service.DisplayName
   $output 

   List-DependentServices $service ""
   List-ServicesDependedOn $service ""
}

We can redirect the output of this script to a file, and then, we can read the file with our favorite text viewer.

For the two previous scripts, we can replace the DisplayName of each service with the Name of each service, if we are more comfortable with the short names of the services. Get-Service sorts the services by their Name instead of their DisplayName. Also, we can see that some of the services that are on the dependencies lists, are not originally listed by Get-Service. This is because these “phantom” services are system drivers. Get-Service does not list them, but they come up in the dependencies lists. Because we should be extra careful when working with system drivers, Get-Service does not display them. It is possible and easy to obtain a list of the system drivers, but this is beyond the scope of this post.

The last script operates recursively. I have created two recursive functions: List-DependentServices and List-ServicesDependedOn. These functions are recursive, because in their code they call themselves. This is how I am able to provide the full information that can also be obtained from the Services snap-in.

Reading the output from the previous two scripts can give us a good idea about the services graph of our Windows OS. But it would be great if we could also visualize it. This is what we are going to do for the rest of this post. Specifically, we are going to see how a few different graph viewers operate. For each graph viewer, we are going to use PowerShell to extract and transform the services graph data so that the data can be used by the particular graph viewer.

GraphViz

GraphViz is a very popular graph viewer, especially among scientists. GraphViz is free and you can find it at http://www.graphviz.org/. I find the Windows version of GraphViz to be a little buggy. Still, GraphViz is easy to learn. After you install GraphViz, you run the program gvedit.exe. Then, from gvedit.exe, you open a text file with a .gv extension that contains the information for the nodes and vertices of the graph.

The example graph in the beginning of the post would be represented in GraphViz with an Example.gv text file as follows:

digraph Example {
"Service 01" -> "Service 03"
"Service 02" -> "Service 03"
"Service 03" -> "Service 04"
"Service 03" -> "Service 05"
"Service 02" -> "Service 05"
"Service 05" -> "Service 06"
"Service 07" -> "Service 08"
"Service 08" -> "Service 09"
"Service 10"
"Service 11"
}

GraphViz depicts this graph exactly as in the image in the beginning of this post.

The following script is named Get-ServiceGraphForGraphviz.ps1. If you direct the output of this script to a text file with the extension .gv, you will be able to open the .gv file with GraphViz and view the services graph of your Windows OS.

$output = 'digraph ServiceGraphForGraphviz {'
$output

$services = Get-Service 

foreach ($service in $services)
{ 
   if ($service.DependentServices)
   { 
      foreach ($ds in $service.DependentServices)
      { 
         $output = '"' + $service.DisplayName + '"' + ' -> ' + '"' + $ds.DisplayName + '"' 
         $output
      }
   }
   else
   {
      $output = '"' + $service.DisplayName + '"'
      $output
   }                              
}

$output = '}'
$output

The following script is named Get-ServiceGraphForGraphvizDependenciesOnly.ps1. If you direct its output to a file with the extension .gv, you will be able to open the .gv with GraphViz. The difference with the previous file is that this one does not contain the nodes that are completely independent. Indeed, these nodes usually do not need to be viewed in order for someone to understand the dependencies between the other services.

$output = 'digraph ServiceGraphForGraphvizDependenciesOnly {'
$output

$services = Get-Service 

foreach ($service in $services)
{ 
   if ($service.DependentServices)
   { 
      foreach ($ds in $service.DependentServices)
      { 
         $output = '"' + $service.DisplayName + '"' + ' -> ' + '"' + $ds.DisplayName + '"' 
         $output
      }
   }                         
}

$output = '}'
$output

A limitation of GraphViz is that it does not let you move the nodes in the graph. Moving the nodes around sometimes helps in better understanding the graph. Still, GraphViz lets you change the layout engine, if the layout does not satisfy you.

NodeXL Excel Template

NodeXL is a template for Excel, that helps in the creation and visualization of graphs. NodeXL is donationware and you can find it at http://nodexl.codeplex.com/. NodeXL lets you move the nodes of your graph and rearrange them in order to help you get a better “feel” and understanding for it. It also lets you easily zoom into and out from different areas of your graph.

After you setup NodeXL, you will find the NodeXL Excel Template right above your start menu. If you want to find the actual template file, its name is NodeXLGraph.xltx. Open it in Excel and a new workbook will be created. The first thing you have to do is to go to the ribbon, to the NodeXL tab and change the type of graph to “Directed”.

Directed

Then in the “Edges” sheet, you should put the beginning and end service for each dependency. Refreshing the graph will automatically create the corresponding vertices in the “Vertices” sheet. If there are any services that have no dependencies whatsoever, but you still want to display them, you should add them to the “Vertices” sheet. You should also explicitly set the Visibility cell to “Show” for those.

For the example graph, the Edges sheet will be as follows:

Edges

In the Vertex1 column we put the “parent” service. In the Vertex2 column, we put the “child” (dependent) service. I set the width to a subjective value.

The Vertices sheet will be as follows:

Vertices

The Vertex column was populated automatically from the first two columns of the Edges sheet. I added the last two lines, since these correspond to services that have no dependencies. I also had to set the Visibility for these two rows explicitly, in order for them to appear in the graph. I also copied the values of the Vertex column to the Label column, in order for each service’s name to appear in the graph. I also set the Color, Shape, and Label Fill Color columns to subjective values.

The corresponding graph, after I rearranged the vertices with the mouse, is as follows:

Graph

The PowerShell script that will provide the values for the first two columns of the Edges sheet is named Get-ServiceGraphForNodeXLDependenciesOnly.ps1. If you redirect the output of this script to a text file, it will provide two columns separated by tabs. The data can easily be copied in Excel.

$services = Get-Service 

foreach ($service in $services)
{ 
   if ($service.DependentServices)
   { 
      foreach ($ds in $service.DependentServices)
      { 
         $output = $service.DisplayName + "`t" + $ds.DisplayName
         $output 
      }
   }                           
}

If you want to add the independent services to the graph, the following script will provide their display names. It is named Get-ServiceGraphForNodeXLIndependentServices.ps1.

$services = Get-Service 

foreach ($service in $services)
{ 
   if (! $service.DependentServices)
   { 
      $output = $service.DisplayName
      $output 
   }                       
}

You can redirect the output of this script to a file and then copy its contents after the last value of the first column of the Vertices sheet. Remember to set the visibility explicitly to “Show” for these rows.

Lastly, if you just want to view everything (just data with no graph visualization) in a regular Excel sheet, you can redirect the output of the following script to a file and open it in Excel. The script is named Get-ServiceGraphForExcel.ps1:

$services = Get-Service 

foreach ($service in $services)
{ 
   if ($service.DependentServices)
   { 
      foreach ($ds in $service.DependentServices)
      { 
         $output = $service.DisplayName + "`t" + $ds.DisplayName
         $output 
      }
   }
   else
   {
      $output = $service.DisplayName
      $output 
   }                           
}

The services that have no dependencies occupy the first column only, whereas a service and its dependent service occupy the first and second column and are separated by a tab.

Neo4j

Neo4j is a graph database. You can find Neo4j at http://www.neo4j.org/. Neo4j is free for personal use.

A graph database lets you input vertices (Neo4j calls them nodes) and edges (Neo4j calls them relationships). It then lets you query the resulting graph. It can produce the nodes, the relationships, the relationships of a specific node one or more hops away, the full spectrum of nodes and relationships between two nodes (thus solving the so-called “Kevin Bacon problem”), and the shortest paths between two nodes. Thus, a graph database knows how to traverse your graph for you.

Neo4j excels as a graph database. And it has an excellent built-in viewer for your graph. Let us see how Neo4j will handle our small example graph.

You can use Neo4j by using your browser to navigate to http://localhost:7474. The dollar sign is the place where you can write or copy (use CTRL-V) one or more commands. There is an triangle/arrow next to the dollar sign that executes what you have pasted.

The command

MATCH (n) RETURN n

shows everything in the graph database.

The command

MATCH (n) OPTIONAL MATCH (n)-[r]-() DELETE n, r

deletes everything in the graph database. The same result can be achieved with the following two commands:

MATCH (n)-[r]-() DELETE r
MATCH (n) DELETE n

The first command deletes all relationships and the second command deletes all nodes. You have to run each one of these commands by itself. You cannot run them as part of the same execution. And you first have to run the first command, because all relationships of a node have to be deleted before that node can be deleted.

After you have made sure that your database contains nothing, you can run the following commands as part of one execution operation. You can copy them (as text) and paste them with CTRL-V to the dollar sign input prompt.

CREATE (Service_01:Service {displayName: "Service 01"})
CREATE (Service_02:Service {displayName: "Service 02"})
CREATE (Service_03:Service {displayName: "Service 03"})
CREATE (Service_04:Service {displayName: "Service 04"})
CREATE (Service_05:Service {displayName: "Service 05"})
CREATE (Service_06:Service {displayName: "Service 06"})
CREATE (Service_07:Service {displayName: "Service 07"})
CREATE (Service_08:Service {displayName: "Service 08"})
CREATE (Service_09:Service {displayName: "Service 09"})
CREATE (Service_10:Service {displayName: "Service 10"})
CREATE (Service_11:Service {displayName: "Service 11"})
CREATE (Service_01)-[:IS_REQUIRED_BY]->(Service_03)
CREATE (Service_02)-[:IS_REQUIRED_BY]->(Service_03)
CREATE (Service_03)-[:IS_REQUIRED_BY]->(Service_04)
CREATE (Service_03)-[:IS_REQUIRED_BY]->(Service_05)
CREATE (Service_02)-[:IS_REQUIRED_BY]->(Service_05)
CREATE (Service_05)-[:IS_REQUIRED_BY]->(Service_06)
CREATE (Service_07)-[:IS_REQUIRED_BY]->(Service_08)
CREATE (Service_08)-[:IS_REQUIRED_BY]->(Service_09)

Now your graph has been inserted into Neo4j. Please note that you must execute these commands only once. If you execute them a second time, new nodes and relationships will be created. And so on. So, if you need to re-execute these commands, be sure to delete everything from the database first.

Use the command:

MATCH (n) RETURN n

to see everything in your graph. The following command does exactly the same:

MATCH (s) RETURN s

This is because it actually is the same command. All we did was change the variable’s name from n to s. In my mind, n stands for “node” and s stands for “service”.

Immediately, you will notice that Neo4j displays its internal number that it allocates for each node. It is each to change that and display the displayName of each node. In the graph that Neo4j returns, double click one of the nodes. A little popup window appears with details for the node. There are two tabs in that popup window. One is named “Properties”. The tab next to it is named “Style”, but you can only see its icon, that looks like an eye.

Properties

Click the icon that looks like an eye and the style tab will appear. Here you can specify that Neo4j will display the displayName (instead of Neo4j’s internal ID) for each node. As you can see, Neo4j starts its internal ID from zero.

Style

The two previous commands display the whole graph. After I rearranged the nodes with my mouse, here is what the graph looked like:

ExampleGraphNeo4j

The following command returns all nodes that have relationships (and their relationships). Thus, Service 10 and Service 11 are excluded. Please note that the pattern ()-[]-() means node-relationship-node without having to specify the direction of the relationship.

MATCH (s)-[]-() RETURN s

The following command returns all relationships (and the corresponding nodes). Thus, its output graph is the same as the previous command.

MATCH ()-[r]-() RETURN r

The following command returns all immediate relationships of the Service 03.

MATCH (Service_03:Service {displayName: "Service 03"})-[r]-() RETURN r

The following command returns everything that has a relationship with Service 01, no matter how many hops away this is. Thus, this command solves the “Kevin Bacon problem”. The asterisk denotes that the graph will be traversed up to infinite hops. If we wanted to traverse the graph up to, say, 6 hops (degrees of separation), then in place of the asterisk we would write *1..6.

MATCH (Service_01:Service {displayName: "Service 01"})-[*]-(s) RETURN Service_01, s

The following command returns the shortest paths between two nodes.

MATCH (Service_04:Service {displayName: "Service 04"}), (Service_06:Service {displayName: "Service 06"}),
  p = allShortestPaths((Service_04)-[*]-(Service_06))
RETURN p

Neo4j has a site/subdomain at http://console.neo4j.org that you can directly do everything we did here without ever installing Neo4j. Just visit this site with your browser, press Clear DB and in the lower part of the page, delete the command that may be there, Then copy the batch of our example CREATE commands there. You will immediately get our example graph. But the viewer in the real Neo4j installation is way better. Just for comparison, here is what the graph looks like in the Neo4j site:

ExampleGraphNeo4jConsole

We are now ready to extract, transform and load our real data. Keep in mind that you first have to remove everything from the database first, if you do not want the example data to coexist with the real data.

So, use the command :

MATCH (n) OPTIONAL MATCH (n)-[r]-() DELETE n, r

to delete everything in the graph database. Alternatively, as I already mentioned, you can use the following two commands

MATCH (n)-[r]-() DELETE r
MATCH (n) DELETE n

one by one and in that order.

You can check that database has been cleared by running the command

MATCH (n) RETURN n

The following PowerShell script is called Get-ServiceGraphForNeo4j.ps1 and creates the CREATE commands that we need in order for our Windows OS services and their relationships to be inserted into Neo4j. Just redirect the output of this script to a text file, then copy the contents of the file to the Neo4j dollar sign command prompt, and then press the execute triangle/arrow.

function Sanitize ($inputString)
{
   $outputString = $inputString

   $outputString = $outputString.Replace(' ','_')
   $outputString = $outputString.Replace('.','_')
   $outputString = $outputString.Replace(',','_')
   $outputString = $outputString.Replace('+','_')
   $outputString = $outputString.Replace('-','_')
   $outputString = $outputString.Replace('(','_')
   $outputString = $outputString.Replace(')','_')
   $outputString = $outputString.Replace('/','_')

   return $outputString
}

$services = Get-Service 

foreach ($service in $services)
{ 
   $s = Sanitize($service.DisplayName)

   $output = 'CREATE (' + $s + ':Service {displayName: ' + '"' + $service.DisplayName + '"' + '})'
   $output 
}

foreach ($service in $services)
{ 
   if ($service.DependentServices)
   { 
      $s1 = Sanitize($service.DisplayName)

      foreach ($ds in $service.DependentServices)
      { 

         $s2 = Sanitize($ds.DisplayName) 

         $output = 'CREATE (' + $s1 + ')-[:IS_REQUIRED_BY]->(' + $s2 + ')' 
         $output 
      }
   }
}

For the CREATE command of a node, Neo4j needs us to provide a “name id” for the node. Valid characters for this name are letters, numbers and underscores. Spaces are illegal, dots also, as well as lots of other characters. One would think that we could use the service’s name (instead of its display name) for this name id. Unfortunately, there are services that in their name have spaces and other characters that are inappropriate for a Neo4j name id.

To overcome this problem, I chose to use each service’s display name, but replace the unwanted (by Neo4j) characters with underscores. The Sanitize function does this work. If you find more unwanted characters in your services, add appropriate lines in the Sanitize function. Or, you could change the Sanitize function to replace each function’s name or display name with a hash. This is a very useful and scientific approach. Or you could come up with another solution. Feel free to think and implement.

Continuously finding “illegal” characters and replacing them all with underscores is not a very useful approach and an error prone one. This is because a service with the display name of “Test.0.1” and a service with the display name of “Test 0 1” will end up having the same name id of “Test_0_1” .

But, by following this method both for service names and for service display names, I learned a lot about them. For example, one would think that a service’s name (as opposed to the service’s display name) would be short and would contain no spaces. Instead, there is a service from F-Secure with the following properties:

Service name: F-Secure Gatekeeper Handler Starter
Display name: FSGKHS
Description:  FSGKHS

I think things are a little backwards here. What should have been the service name is the display name and vice versa. And the description… OK, you get it.

So, there are service names with characters that Neo4j would not like for a name id. And the same holds true for service display names. There are spaces to separate words, dots to separate version and subversion numbers , + for COM+, – for F-Secure, parentheses from Google services, / for TCP/IP, and who knows what else.

So, either implement a strategy that will help you create sane identifiers for Neo4j, or just experiment and see what characters Neo4j “spits out”. Then, you can add them in the Sanitize function.

Epilogue

The PowerShell scripts I provide in this post can be run with every version of PowerShell, even PowerShell 1.0. In addition, the different software that I used can be installed even as far back as Windows XP. Yes, GraphViz, NodeXL,and Neo4j can be installed even on downlevel clients as far back as Windows XP.

For graph viewing, I chose three of the many software programs for graphs. I chose these three because they are well known, I like them and they are inexpensive or free. There are other programs just as good and just as free and inexpensive. Feel free to use whichever you like. If you can afford it, you may also want to look for more expensive programs. Microsoft Automatic Graph Layout (MSAGL) and Microsoft Visio are two of those.

There is also a huge amount of JavaScript libraries that can help in the creation of graphs, but this is really a solution for JavaScript developers, not administrators. Still, if you want to go down that road, I recommend the JavaScript InfoVis Toolkit at http://philogb.github.io/jit/ and the awesome Data-Driven Documents at http://d3js.org/. With JavaScript, you have the capability to create highly interactive graphs. For example, you can create a graph that lets you click on each node (service) and change its color (denoting the status of the service) to green (running) or red (stopped). Then you can program the graph to cascade the change to subordinate nodes. This way, you can have a nice what if analysis for the times you are planning to stop a service.

All this talk about graphs may be useful, but even if you never perform such an analysis, you can get by just by using the Services MMC snap-in. It can give you all the information you may need. If you are planning to study a service for whatever reason (i.e. you may plan to disable it and worry about the impact) all you need to know are the service’s dependencies. The Services MMC snap-in can give you recursively all the services that depend on a particular service and all the services that the particular service depends upon. The same goes for my “recursive” script that I presented at the beginning of the post. This is all the information you may ever need concerning the hierarchy of services (the services graph) of your Windows OS.

Still, it is nice to able to see the big picture. You get a better feel for the services graph overall, if you spend some time analyzing that graph. So, I guess, the most important lesson from this post is not how to view the services graph per se, but that PowerShell is a great tool that can help in the extraction of data from a Windows OS and in the subsequent transformation of that data to the format that your analysis tool needs.

Posted in Administration

Visualizing eight elementary vector fields

I used Wolfram Alpha to draw eight vector fields that fascinate me, albeit or because of their simplicity. In Wolfram Alpha, I entered (x,y) and the engine plotted the integral curves for the corresponding vector field. Then I entered (x,-y) and the engine plotted the corresponding integral curves for that corresponding vector field. And so on. Vector fields You can tell that it is not the vector fields that are depicted here, but their integral curves. This is because, in the plots above, each arrow has the same length as the others. If the actual vector field was plotted, each arrow’s length would be equal or proportional to the magnitude of the vector field function F(x, y) at that point. In the case of all eight vector fields studied here, the magnitude of the arrows would be greater as x and y increase. Specifically, in all cases, the magnitude of each arrow is sqrt((f1(x,y))^2 + (f2(x,y))^2) = sqrt(x^2+y^2).

Update, September 11, 2014: Using Kevin Mehall‘s awesome Vector Field Online Graphing, I was able to depict the above vector fields with great ease. Here they are:

1.(+x,+y)F(x, y) = + x i + y j

2.(+x,-y)F(x, y) = + x i – y j

3.(-x,+y)F(x, y) = – x i + y j

4.(-x,-y)F(x, y) = – x i – y j

5.(+y,+x)F(x, y) = + y i + x j

6.(+y,-x)F(x, y) = + y i – x j

7.(-y,+x)F(x, y) = – y i + x j

8.(-y,-x)F(x, y) = – y i – x j

Posted in Science

Using SQL Server to solve Einstein’s puzzles

A particular category of logic puzzles have to do with the satisfaction of constraints. Among those are the so called “Einstein’s puzzles”, also called “zebra puzzles“. The first time I saw such a puzzle was about 10 years ago. I immediately thought that such puzzles could be solved using SQL Server.

Indeed, I thought that it would be easy to use a SELECT statement without a WHERE clause to get the Cartesian product from tables that would list all the entities involved. This SELECT statement would populate a “main table” that would keep all possibilities. Then, we would translate each constraint to DELETE statements that would eliminate from the main table the rows that do not fit the particular constraint. When all constraints would have been accounted for, in the main table there would remain only the rows that correspond to the solution of the puzzle.

The problem I faced early on was that most Einstein puzzles have some constraints that are difficult to “translate” to straightforward DELETE statements. Instead, these constraints can be handled by writing very specific programs that examine the rows of the main table and perform deletions based on the particular rules of the constraint statement.

Again, in order to make Einstein puzzles more challenging, their creators devise most constraints in a way that makes it impossible to “translate” them to simple and straightforward DELETE statements from a main table that contains all possibilities. Even the original (i.e. the first) Einstein puzzle exhibits this practice.

In order to provide a proof-of-concept solution to such a puzzle, I needed a puzzle that provided straightforward constraints. The existence of complex constraints that cannot be represented by DELETE statements (and instead need specific programming) would complicate my analysis and would obscure its main points.

Thus, for this analysis, I chose to create my own puzzle (based on a “story” and on “entities” from my own imagination). Of course, the story and the entities are not what is important here; what is important are the constraints that I will choose to introduce. All the constraints that I will introduce in this puzzle lack complexity and “translate” to straightforward DELETE statements. I believe that this practice will help showcase the concepts that are needed in order to understand how a relational database like SQL Server can be used to solve these kind of puzzles.

The puzzle I devised is the following:

In an airport, five different couples showed up. Each couple was heading to a different destination. Each couple also had a suitcase with them. Each suitcase had a different color and a different dangerous item within. Your mission, should you decide to accept it, is to find the exact details: For each couple, you have to find the husband, the wife, their destination, the color of their suitcase and the dangerous item within the suitcase.

The names of the husbands in alphabetical order are: Adam, Bill, Chad, Dave, Eric. The names of the wives in alphabetical order are: Annabell, Beatrice, Christin, Dorothea, Elleonor. The destinations in alphabetical order are: China, France, Greece, Italy, Japan. The colors of the suitcases in alphabetical order are: Blue, Green, Red, White, Yellow. The dangerous items in alphabetical order are: Cutter, Hammer, Knife, Pistol, Wrench.

I will go ahead and give you the solution that I prearranged. The constraints that I will provide will come straight from this solution.

Adam Dorothea Greece Green  Cutter
Bill Elleonor Japan  Red    Wrench
Chad Christin Italy  Yellow Hammer
Dave Annabell China  White  Pistol
Eric Beatrice France Blue   Knife

After I arranged the solution, I wrote a SQL script that creates a database. In that database, it creates one table that lists the husbands, one that lists the wives and so on. It then uses a SELECT statement FROM those tables without a WHERE clause. In effect, this produces the Cartesian product of the available combinations/possibilities. The rows from the SELECT statement populate the main table. Then, I devise constraints. I am careful when I create the constraints, so that each constraint is not redundant. I check that each constraint leads to the elimination of rows from  the main table. Here are the constraints I came up with:

01. The couple that headed to Italy had a hammer in their suitcase.
02. Elleonor and her husband were going to Japan.
03. There was a pistol in the white suitcase.
04. Eric and Beatrice are married to each other.
05. Adam and his wife carried a green suitcase.
06. Bill and his wife had a wrench in their suitcase.
07. The couple that was going to Greece had a green suitcase.
08. Annabell and her husband were heading to China.
09. The red suitcase contained a wrench.
10. Chad and Christin are married.
11. Beatrice and her husband were carrying a knife in their suitcase.
12. Chad and his wife carried a yellow suitcase.
13. Dave and Annabell are married.
14. The cutter was in the green suitcase.
15. Dorothea and her husband were not going to France.

For each constraint I devised, I created corresponding DELETE statements that enforced those constraints, by eliminating the rows that did not apply. I kept adding constraints until only 5 rows remained. The 5 rows that remained are the solution rows that I just presented above. It took me 15 simple constraint statements to eliminate all invalid rows.

The SQL script that I wrote is presented below. I run it using a Microsoft SQL Server 2005 Express instance that I happened to have in my PC for another reason. After each constraint is applied, I provide the row count of the main table. This way, we can see the row count diminishing after each constraint is acknowledged.

PRINT 'In an airport, five different couples showed up.'
PRINT 'Each couple was heading to a different destination.'
PRINT 'Each couple also had a suitcase with them.'
PRINT 'Each suitcase had a different color and a different dangerous item within.'
PRINT 'Your mission, should you decide to accept it, is to find the exact details:'
PRINT 'For each couple, you have to find the husband, the wife, their destination,'
PRINT 'the color of their suitcase and the dangerous item within the suitcase.'

USE master
GO

CREATE DATABASE PuzzleDatabase
GO

USE PuzzleDatabase
GO

CREATE TABLE PuzzleTable
(
   Husband     VARCHAR(10),
   Wife        VARCHAR(10),
   Destination VARCHAR(10),
   Color       VARCHAR(10),
   Item        VARCHAR(10)
)
GO

CREATE TABLE HusbandTable     (HusbandColumn     VARCHAR(10))
GO
CREATE TABLE WifeTable        (WifeColumn        VARCHAR(10))
GO
CREATE TABLE DestinationTable (DestinationColumn VARCHAR(10))
GO
CREATE TABLE ColorTable       (ColorColumn       VARCHAR(10))
GO
CREATE TABLE ItemTable        (ItemColumn        VARCHAR(10))
GO

PRINT ''
PRINT 'The names of the husbands in alphabetical order are:'
PRINT 'Adam, Bill, Chad, Dave, Eric.'

INSERT INTO HusbandTable (HusbandColumn) VALUES ('Adam')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Bill')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Chad')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Dave')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Eric')
GO

PRINT ''
PRINT 'The names of the wives in alphabetical order are:'
PRINT 'Annabell, Beatrice, Christin, Dorothea, Elleonor.'

INSERT INTO WifeTable (WifeColumn) VALUES ('Annabell')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Beatrice')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Christin')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Dorothea')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Elleonor')
GO

PRINT ''
PRINT 'The destinations in alphabetical order are:'
PRINT 'China, France, Greece, Italy, Japan.'

INSERT INTO DestinationTable (DestinationColumn) VALUES ('China')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('France')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Greece')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Italy')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Japan')
GO

PRINT ''
PRINT 'The colors of the suitcases in alphabetical order are:'
PRINT 'Blue, Green, Red, White, Yellow.'

INSERT INTO ColorTable (ColorColumn) VALUES ('Blue')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Green')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Red')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('White')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Yellow')
GO

PRINT ''
PRINT 'The dangerous items in alphabetical order are:'
PRINT 'Cutter, Hammer, Knife, Pistol, Wrench.'

INSERT INTO ItemTable (ItemColumn) VALUES ('Cutter')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Hammer')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Knife')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Pistol')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Wrench')
GO

PRINT ''
PRINT 'The following step is the most important one.'
PRINT 'We calculate all posibilities (i.e. the Cartesian product).'
PRINT '(5 husbands) x (5 wives) x (5 destinations) x (5 suitcase colors) x (5 dangerours items) ='
PRINT '= 5^5 possibilities = 3125 possibilities = 3125 rows.'
PRINT 'The SELECT statement does this calculation automatically for us,'
PRINT 'if we do not specify a WHERE clause.'

INSERT INTO PuzzleTable (Husband, Wife, Destination, Color, Item)
SELECT HusbandColumn, WifeColumn, DestinationColumn, ColorColumn, ItemColumn
FROM HusbandTable, WifeTable, DestinationTable, ColorTable, ItemTable
GO

PRINT ''
PRINT 'By monitoring the row count, we will be able to see'
PRINT 'how each restriction diminishes the number of rows/possibilities.'
PRINT 'When all of the restrictions have been acknowledged,'
PRINT 'only 5 rows should remain on the table.'
PRINT 'These 5 rows should correspond to the solution.'

SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '01. The couple that headed to Italy had a hammer in their suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Italy' AND Item != 'Hammer'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Italy' AND Item = 'Hammer'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '02. Elleonor and her husband were going to Japan.'

DELETE FROM PuzzleTable
WHERE Wife = 'Elleonor' AND Destination != 'Japan'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Elleonor' AND Destination = 'Japan'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '03. There was a pistol in the white suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'White' AND Item != 'Pistol'
GO
DELETE FROM PuzzleTable
WHERE Color != 'White' AND Item = 'Pistol'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '04. Eric and Beatrice are married to each other.'

DELETE FROM PuzzleTable
WHERE Husband = 'Eric' AND Wife != 'Beatrice'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Eric' AND Wife = 'Beatrice'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '05. Adam and his wife carried a green suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Adam' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Adam' AND Color = 'Green'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '06. Bill and his wife had a wrench in their suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Bill' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Bill' AND Item = 'Wrench'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '07. The couple that was going to Greece had a green suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Greece' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Greece' AND Color = 'Green'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '08. Annabell and her husband were heading to China.'

DELETE FROM PuzzleTable
WHERE Wife = 'Annabell' AND Destination != 'China'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Annabell' AND Destination = 'China'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '09. The red suitcase contained a wrench.'

DELETE FROM PuzzleTable
WHERE Color = 'Red' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Red' AND Item = 'Wrench'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '10. Chad and Christin are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' AND Wife != 'Christin'  
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' AND Wife = 'Christin'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '11. Beatrice and her husband were carrying a knife in their suitcase.'

DELETE FROM PuzzleTable
WHERE Wife = 'Beatrice' AND Item != 'Knife'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Beatrice' AND Item = 'Knife'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '12. Chad and his wife carried a yellow suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' and Color != 'Yellow'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' and Color = 'Yellow'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '13. Dave and Annabell are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Dave' and Wife != 'Annabell'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Dave' and Wife = 'Annabell'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '14. The cutter was in the green suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'Green' and Item != 'Cutter'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Green' and Item = 'Cutter'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '15. Dorothea and her husband were not going to France.'

DELETE FROM PuzzleTable
WHERE Wife = 'Dorothea' and Destination = 'France'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT 'The 5 rows that should remain on the table,'
PRINT 'i.e. the solution, should be:'
PRINT '----------------------------------'
PRINT 'Adam Dorothea Greece Green  Cutter'
PRINT 'Bill Elleonor Japan  Red    Wrench'  
PRINT 'Chad Christin Italy  Yellow Hammer'
PRINT 'Dave Annabell China  White  Pistol'
PRINT 'Eric Beatrice France Blue   Knife'
PRINT '----------------------------------'

SELECT * FROM PuzzleTable
GO

USE master
GO

DROP DATABASE PuzzleDatabase
GO

Here is the results tab after running the previous script:

SQL1

Although we eliminated all invalid rows, leaving only the 5 correct ones, it took us as much as 15 simple constraint statements to do that. Now, that may not be a problem in and of itself. What is a problem is that some of these constraints are redundant. We could have reached the solution using less constraints. To understand why, we have to realize that relying only on the DELETE statements is not enough. We have to be more thorough.

Translating a constraint statement to DELETE statements is not enough. After a deletion, we also have to check for the formation of unique relationships and use them to further eliminate invalid rows. Let me give you an example.

Suppose that we delete some rows. Then, checking the remaining rows, we observe that Adam only has Dorothea as his wife. There are no rows with Adam and another of the wives. Thus, we understand that Adam and Dorothea are a couple. This is great. And we owe it to the DELETE statements that we have been able to reach this conclusion. But as we continue to observe other rows, we find that other husbands have wives with Dorothea being one of them. For example, there are rows that list Bill with Dorothea, as well as with other wives as well. The same may happen, say, for Eric.

But, since we know that Adam and Dorothea are a couple, we can DELETE the rows that corresponds to Dorothea with other husbands.

The same goes for any combination of two different entitles. If, for example, we find that the rows containing the Destination Italy only have the Item Hammer and no other Items, then we immediately know that we should eliminate all rows that correspond to other destinations and hammers.

Thus, to extract the maximum information and to do the maximum elimination possible from the constraints, after the straightforward deletion statements, we should search for unique relationships and use them to further eliminate invalid rows.

In order to do that, I created a lengthy procedure that I call after each constraint has been acknowledged and handled. This procedure checks each and every pair of different entities, in order to find a unique correspondence. If it finds such a correspondence, it “propagates” this information, meaning that deletes the rows that are invalid due to this unique correspondence.

The procedure that checks each and every pair of different entities for a unique correspondence, also keeps track of whether it made any deletions or not. If it indeed made any deletions, it runs again, because there might be new unique correspondences that came up because of the deletions. And it continues to run again, as long as it makes any deletions in its previous run.

This procedure is lengthy (code-wise). It is a shame that I created it using such a verbose technique, especially when SQL Server scripts can be very versatile and dynamic. Programming a SQL Server script allows you to use dynamic SQL statements, thus forming your code on the fly. Still, I concentrated on the main concepts. Creating an elaborate, dynamic statement generation would have obscure the main points of this analysis.

Calling this procedure after each constraint has been handled, helps in the reduction of the constraints needed. Indeed, when we run the SQL script, we see that we reach the solution with no need for the last two constraints.

The SQL script, that contains the procedure that accounts for unique correspondences, and thus, “converges” faster (“faster” meaning “using less constraints”), is listed below:

PRINT 'In an airport, five different couples showed up.'
PRINT 'Each couple was heading to a different destination.'
PRINT 'Each couple also had a suitcase with them.'
PRINT 'Each suitcase had a different color and a different dangerous item within.'
PRINT 'Your mission, should you decide to accept it, is to find the exact details:'
PRINT 'For each couple, you have to find the husband, the wife, their destination,'
PRINT 'the color of their suitcase and the dangerous item within the suitcase.'

USE master
GO

CREATE DATABASE PuzzleDatabase
GO

USE PuzzleDatabase
GO

CREATE PROCEDURE PropagateUniqueInformation
AS
BEGIN
   DECLARE @myExecuteAgainFlag BIT

   DECLARE @myDistinctCount INT
   DECLARE @myDistinctValue VARCHAR(10)

   SET @myExecuteAgainFlag = 0

   -- Husbands and Wives

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Husbands and Destinations

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Husbands and Colors

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Husbands and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Wives and Destinations

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Annabell'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Annabell'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Annabell' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Beatrice'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Beatrice'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Beatrice' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Christin'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Christin'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Christin' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Dorothea'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Dorothea'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Dorothea' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Elleonor'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Elleonor'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Elleonor' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Wives and Colors

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Annabell'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Annabell'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Annabell' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Beatrice'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Beatrice'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Beatrice' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Christin'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Christin'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Christin' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Dorothea'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Dorothea'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Dorothea' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Elleonor'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Elleonor'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Elleonor' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Wives and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Annabell'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Annabell'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Annabell' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Beatrice'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Beatrice'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Beatrice' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Christin'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Christin'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Christin' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Dorothea'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Dorothea'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Dorothea' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Elleonor'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Elleonor'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Elleonor' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Destinations and Colors

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'China'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'China'
      DELETE FROM PuzzleTable
      WHERE Destination != 'China' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'France'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'France'
      DELETE FROM PuzzleTable
      WHERE Destination != 'France' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'Greece'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'Greece'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Greece' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'Italy'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'Italy'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Italy' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'Japan'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'Japan'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Japan' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Destinations and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'China'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'China'
      DELETE FROM PuzzleTable
      WHERE Destination != 'China' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'France'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'France'
      DELETE FROM PuzzleTable
      WHERE Destination != 'France' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'Greece'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'Greece'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Greece' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'Italy'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'Italy'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Italy' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'Japan'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'Japan'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Japan' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Colors and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Blue'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Blue'
      DELETE FROM PuzzleTable
      WHERE Color != 'Blue' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Green'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Green'
      DELETE FROM PuzzleTable
      WHERE Color != 'Green' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Red'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Red'
      DELETE FROM PuzzleTable
      WHERE Color != 'Red' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'White'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'White'
      DELETE FROM PuzzleTable
      WHERE Color != 'White' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Yellow'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Yellow'
      DELETE FROM PuzzleTable
      WHERE Color != 'Yellow' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Epilogue

   IF @myExecuteAgainFlag = 0
   BEGIN
      RETURN
   END

   EXEC PropagateUniqueInformation
END
GO

CREATE TABLE PuzzleTable
(
   Husband     VARCHAR(10),
   Wife        VARCHAR(10),
   Destination VARCHAR(10),
   Color       VARCHAR(10),
   Item        VARCHAR(10)
)
GO

CREATE TABLE HusbandTable     (HusbandColumn     VARCHAR(10))
GO
CREATE TABLE WifeTable        (WifeColumn        VARCHAR(10))
GO
CREATE TABLE DestinationTable (DestinationColumn VARCHAR(10))
GO
CREATE TABLE ColorTable       (ColorColumn       VARCHAR(10))
GO
CREATE TABLE ItemTable        (ItemColumn        VARCHAR(10))
GO

PRINT ''
PRINT 'The names of the husbands in alphabetical order are:'
PRINT 'Adam, Bill, Chad, Dave, Eric.'

INSERT INTO HusbandTable (HusbandColumn) VALUES ('Adam')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Bill')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Chad')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Dave')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Eric')
GO

PRINT ''
PRINT 'The names of the wives in alphabetical order are:'
PRINT 'Annabell, Beatrice, Christin, Dorothea, Elleonor.'

INSERT INTO WifeTable (WifeColumn) VALUES ('Annabell')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Beatrice')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Christin')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Dorothea')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Elleonor')
GO

PRINT ''
PRINT 'The destinations in alphabetical order are:'
PRINT 'China, France, Greece, Italy, Japan.'

INSERT INTO DestinationTable (DestinationColumn) VALUES ('China')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('France')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Greece')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Italy')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Japan')
GO

PRINT ''
PRINT 'The colors of the suitcases in alphabetical order are:'
PRINT 'Blue, Green, Red, White, Yellow.'

INSERT INTO ColorTable (ColorColumn) VALUES ('Blue')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Green')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Red')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('White')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Yellow')
GO

PRINT ''
PRINT 'The dangerous items in alphabetical order are:'
PRINT 'Cutter, Hammer, Knife, Pistol, Wrench.'

INSERT INTO ItemTable (ItemColumn) VALUES ('Cutter')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Hammer')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Knife')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Pistol')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Wrench')
GO

PRINT ''
PRINT 'The following step is the most important one.'
PRINT 'We calculate all posibilities (i.e. the Cartesian product).'
PRINT '(5 husbands) x (5 wives) x (5 destinations) x (5 suitcase colors) x (5 dangerours items) ='
PRINT '= 5^5 possibilities = 3125 possibilities = 3125 rows.'
PRINT 'The SELECT statement does this calculation automatically for us,'
PRINT 'if we do not specify a WHERE clause.'

INSERT INTO PuzzleTable (Husband, Wife, Destination, Color, Item)
SELECT HusbandColumn, WifeColumn, DestinationColumn, ColorColumn, ItemColumn
FROM HusbandTable, WifeTable, DestinationTable, ColorTable, ItemTable
GO

PRINT ''
PRINT 'By monitoring the row count, we will be able to see'
PRINT 'how each restriction diminishes the number of rows/possibilities.'
PRINT 'When all of the restrictions have been acknowledged,'
PRINT 'only 5 rows should remain on the table.'
PRINT 'These 5 rows should correspond to the solution.'

SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '01. The couple that headed to Italy had a hammer in their suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Italy' AND Item != 'Hammer'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Italy' AND Item = 'Hammer'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '02. Elleonor and her husband were going to Japan.'

DELETE FROM PuzzleTable
WHERE Wife = 'Elleonor' AND Destination != 'Japan'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Elleonor' AND Destination = 'Japan'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '03. There was a pistol in the white suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'White' AND Item != 'Pistol'
GO
DELETE FROM PuzzleTable
WHERE Color != 'White' AND Item = 'Pistol'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '04. Eric and Beatrice are married to each other.'

DELETE FROM PuzzleTable
WHERE Husband = 'Eric' AND Wife != 'Beatrice'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Eric' AND Wife = 'Beatrice'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '05. Adam and his wife carried a green suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Adam' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Adam' AND Color = 'Green'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '06. Bill and his wife had a wrench in their suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Bill' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Bill' AND Item = 'Wrench'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '07. The couple that was going to Greece had a green suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Greece' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Greece' AND Color = 'Green'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '08. Annabell and her husband were heading to China.'

DELETE FROM PuzzleTable
WHERE Wife = 'Annabell' AND Destination != 'China'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Annabell' AND Destination = 'China'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '09. The red suitcase contained a wrench.'

DELETE FROM PuzzleTable
WHERE Color = 'Red' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Red' AND Item = 'Wrench'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '10. Chad and Christin are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' AND Wife != 'Christin'  
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' AND Wife = 'Christin'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '11. Beatrice and her husband were carrying a knife in their suitcase.'

DELETE FROM PuzzleTable
WHERE Wife = 'Beatrice' AND Item != 'Knife'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Beatrice' AND Item = 'Knife'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '12. Chad and his wife carried a yellow suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' and Color != 'Yellow'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' and Color = 'Yellow'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '13. Dave and Annabell are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Dave' and Wife != 'Annabell'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Dave' and Wife = 'Annabell'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '14. The cutter was in the green suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'Green' and Item != 'Cutter'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Green' and Item = 'Cutter'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '15. Dorothea and her husband were not going to France.'

DELETE FROM PuzzleTable
WHERE Wife = 'Dorothea' and Destination = 'France'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT 'The 5 rows that should remain on the table,'
PRINT 'i.e. the solution, should be:'
PRINT '----------------------------------'
PRINT 'Adam Dorothea Greece Green  Cutter'
PRINT 'Bill Elleonor Japan  Red    Wrench'  
PRINT 'Chad Christin Italy  Yellow Hammer'
PRINT 'Dave Annabell China  White  Pistol'
PRINT 'Eric Beatrice France Blue   Knife'
PRINT '----------------------------------'

SELECT * FROM PuzzleTable
GO

USE master
GO

DROP DATABASE PuzzleDatabase
GO

Here is the results tab after running the previous script:

SQL2

Posted in SQL Server

How jectivity corresponds to morphisms

“Jectivity” may or may not be a real word in the dictionary and my command of the English language is not adequate for me to make such a discussion.

But “jectivity” is indeed a valid mathematical word and it concerns the classification of functions into injective, surjective, and bijective ones.

But before we discuss jectivity, let us discuss functions. What is a function and what is not a function?

A function maps all elements from its domain to its codomain. Any element from the domain is mapped to only one element from the codomain.

This is a function:

Slide1

This is not a function, because an element from the domain is not mapped:

Slide2

This is not a function, because an element from the domain is mapped to more than one element in the codomain:

Slide3

Now let us classify functions into injective, surjective, and bijective ones.

Please note that the following analysis is based on numerous Wikipedia articles on functions and morphisms.

An injective function (or injection) maps any element of the domain to a different element in the codomain.

A surjective function (or surjection) maps to all elements of the codomain.

A bijective function (or bijection) is both injective and surjective.

Example:

Slide4

A function f: A -> B is injective if and only if f is left-invertible; that is, there is a function g: f(A) -> A such that g o f = identity function on A.
A function f: X -> Y is injective if and only if it is left-cancellative; that is, given any functions g1 ,g2 : Y -> Z, f o g1 = f o g2 => g1 = g2.

A function f: A -> B is surjective if and only if it is right-invertible; that is, there is a function g: B -> A such that f o g = identity function on B.
A function f: X -> Y is surjective if and only if it is right-cancellative; that is, given any functions g1, g2 : Y -> Z, g1 o f = g2 o f => g1 = g2.

A function f: A -> B is bijective if and only if it is invertible; that is, there is a function g: B -> A such that g o f = identity function on A and f o g = identity function on B.

The composition of two bijections is again a bijection, but if g o f is a bijection, then it can only be concluded that f is injective and g is surjective.

Injections, surjections, and bijections loosely correspond to Category Theory’s monomorphisms, epimorphisms, and isomorphisms, respectively.

A monomorphism (also called a monic morphism or a mono) is a morphism f: X -> Y that is left-cancellative in the sense that, for all morphisms g1, g2 : Z -> X, f o g1 = f o g2 => g1 = g2.

An epimorphism (also called an epic morphism or an epi) is a morphism f: X -> Y that is right-cancellative in the sense that, for all morphisms g1, g2 : Y -> Z, g1 o f = g2 o f => g1 = g2.

An isomorphism is both a monomorphism and an epimorphism.

A morphism f : X -> Y in a category is an isomorphism if it has a two-sided inverse; that is, there is another morphism g : Y -> X in that category such that g o f = 1X and f o g = 1Y, where 1X and 1Y are the identity morphisms of X and Y, respectively.

I think that this analysis could be used as the introduction to a primer in Category Theory.

Posted in Science

Absolute URLs vs. relative URLs vs. protocol relative URLs

Examples

Absolute URL:
http://yoursite.com/folder/subfolder/file.html
Relative URL:
/folder/subfolder/file.html
Protocol relative URL:
//anothersite.com/anotherfolder/anothersubfolder/anotherfile.html

Tips

  • Try to avoid absolute URLs.
  • Try to use relative URLs for links inside your site.
  • Try to use protocol relative URLs for links outside your site.
Posted in Web design

Venn diagrams vs. Euler diagrams

In Venn diagrams, all areas (subsets) are shown. Areas that contain no elements (empty subsets) are filled with black color.

In Euler diagrams, only areas that contain elements (non-empty subsets) are shown.

If we want to depict all areas, irrespectively of whether they contain elements or not, We should choose to make a Venn diagram.

If we want to avoid depicting areas that contain no elements, and instead focus only on the areas that are non-empty, we should choose to make an Euler diagram.

Venn diagrams vs. Euler diagrams

Posted in Science