Google Web www.tincat-group.com

mewsings, a blog

--dawn

Friday, March 03, 2006

Data for Every 1

Queries and reports. Let's talk about 'em. My plan was to do an overview comparing the query language formerly known as GIRLS with SQL in this one blog entry and then move on to features of reporting tools in general. But I'm scratching that and blending the two. Instead of listing a whole bunch of differences, I'm going to take it nice, and easy, 1 thought at a time. Please recognize that this is not about trying to convince anyone to use my favorite query language; it is much more general than that. This is an effort to help get our collective heads around some real differences in data models through the eyes of query languages, starting with two I know a little bit about.

Terms that crop up with SQL include the RM, 1NF, and 3VL. Terms that relate to GIRLS are MV, Pick, NF2, and 2VL. When discussing these two query languages, I am also looking at more general concepts that might relate to XQuery or any other query languages as well as to the data models that correspond to these languages.

Let's dive into a contrived example of a school system booster club sale of frozen pizzas. Multiple pizzas sold through multiple school booster clubs in a single school system. If we had an information system, we might want to query it asking about the people who bought the pizzas, perhaps for a mailing list. We might also want information about the pizzas, maybe for forecasting of supplies for future events. We might ask which booster clubs sold which pizzas so we deliver the pizzas to the right places. Those are all 1 entity instance questions. We are asking a question about each 1 instance of people, pizzas, or clubs.

It is common to ask questions about one entity.

It is typical to want information about some entity or other, whether people, places, things, or events. Additionally, it is typical to want 1 chunk of information for every 1 instance of such an entity. Optionally, you might want to aggregate information into groupings bigger than 1 asking, for example, how many pizzas each booster club ordered. We might otherwise split out the instances by requesting information about a multivalued property. For example, we might ask how many pizzas have pepperoni as a topping.

In any case, starting with 1 and then listing, grouping, or splitting from there is a very common way to think. 1 might even suggest it is natural. If you walked into a business in the 1970's, or even today, you would find filing cabinets dedicated to 1 single entity, such as Customers or Orders, often with a folder for each instance of that 1 entity. Why? It has to do with how we think, I would think. 1 might be the loneliest number, but it is conceptually very powerful.

Pizza

A pizza chef team has been organized to assemble all of the uncooked pizzas and put them in boxes, with cooking instructions attached. They would like each box to have a Pizza Description Label, including a unique Pizza ID, the type of crust, the list of cheeses used, and the list of toppings. Sure, you might have mixed cheeses and toppings in a single attribute or split out meats and veggies into separate ones, but this stick with me and don't let your mind wander in that way. (I'll also hold off any discussion of list compared to set or bag for a rainy day. Let's just say that this list of toppings is rather like a shopping list. That description should prompt a smile from someone, I hope.)

Using GIRLS (please excuse all upper case in these examples, it's my age showing):

LIST PIZZAS CRUST CHEESES TOPPINGS WITH PIZZA_ID = "12345"

Pizza Description
PizzaID Crust Cheeses Toppings 12345 Chicago Mozzarella Peppers Provolone Mushrooms Pepperoni

Seemingly comparable SQL:

SELECT
  PIZZA_ID, CRUST, CHEESE, TOPPING
FROM
  PIZZA_VIEW
WHERE
  PIZZA_ID = "12345"
GROUP BY
  CHEESE

But this isn't going to be quite the same, is it? There are insignificant details such as whether column headings and unique id's come along for the ride, but there there is the matter of how to create the PIZZA_VIEW and what the output from the above SELECT would be like. The view might be produced with something like this:

CREATE VIEW PIZZA_VIEW AS (SELECT P1.PIZZA_ID, CRUST, CHEESE, TOPPING FROM (PIZZA AS P1 JOIN PIZZA_CHEESE AS P2 ON P1.PIZZA_ID = P2.PIZZA_ID) JOIN PIZZA_TOPPING AS P3 ON P2.PIZZA_ID = P3.PIZZA_ID)

If we take the above view and the previous SELECT on it, we might get a label something like the one below.

Pizza Description
12345 Chicago Mozzarella Peppers 12345 Chicago Mozzarella Mushrooms 12345 Chicago Mozzarella Pepperoni 12345 Chicago Provolone Peppers 12345 Chicago Provolone Mushrooms 12345 Chicago Provolone Pepperoni

I'm a bit rusty on SQL and I'm not doing anything clever here to handle the multiple 1-M (one-to-many) relationships, but feel free to add comments on how the view or query on this data would be set up better. I think the party line is that the data are all there and how it is displayed is a task for a reporting tool. But look at how the query language shows up this key difference in the data models. With GIRLS we ask questions while thinking about 1 thing, listing, aggregating, or splitting them all the while with a sense of 1-ness.

A significant issue for professionals who are learning SQL after only knowing languages that derived from GIRLS is the change to thinking about going from the many to the 1 in their thinking instead of from the 1 to the many. It didn't seem like they ever had to learn to start with 1, but they definitely do have to learn to start with many instead.

Moving the story forward, we have just found out that the green peppers are delayed and we have decided not to wait any longer. We are going to have the delivery team get all the pizzas without peppers to the right booster clubs now. The delivery team has requested a list of the Pizza IDs that need to be loaded in the delivery van. So, we need a listing of all Pizza IDs for pizzas without peppers as a topping.

GIRLS:

LIST PIZZAS WITH EVERY TOPPING <> "PEPPERS"

SQL:

SELECT P1.PIZZA_ID FROM PIZZA AS P1 WHERE P1.PIZZA_ID NOT IN (SELECT P2.PIZZA_ID FROM PIZZA_TOPPING AS P2 WHERE TOPPING = "PEPPERS")

It just doesn't matter to GIRLS if there is an attribute, such as topping, that has multiple values for a single pizza. We can still look at data for every 1.

See comments.

0 Comments:

Post a Comment

<< Home

Litter Box


Atom feed

Add to Google

Some of this is worth pawing through.