Re: What databases have taught me

From: topmind <topmind_at_technologist.com>
Date: 23 Jun 2006 21:57:10 -0700
Message-ID: <1151125030.075597.209130_at_i40g2000cwc.googlegroups.com>


Neo wrote:
> > Focusing on the easy part relieves you of the burden of thinking about the difficult questions of integrity and manipulation. That must be a good thing, right? I mean, if you had to come up with a query mechanism that could handle arbitrary ad hoc queries as simply as even SQL can, wouldn't that, like, totally harsh the buzz you got from reintroducing nested structures?
>
> I would be interested to see if SQL's queries are simpler for highly
> variable data. Could someone post one equivalent to that in
> www.dbfordummies.com/Example/Ex117.asp
>
> This example models a real estate listing. It models a $200,000
> single-family house with MLS# A2868Z. The house has 3 bedrooms. The
> master bedroom is 25x30 and has biege Dupont carpet that was installed
> 1/1/2000. The second bedroom is 12x15 and has pink and purple carpet.
> The third bedroom is 12x15 and has hardwood flooring that was installed
> 1/2/1990 and needs resurfacing. The house has 3 bathrooms. The master
> bathroom has brass finished Moen faucets. The second is a hall bathroom
> and the third a half bathroom. The house has a 2-car attached garage.
> The house has 2 fireplaces, the first is made of brick and its hearth
> is made of stone. The second fireplace is made of stone and its hearth
> is made of stone also. The 15x20 kitchen has cork flooring and the
> following appliances: a white Maytag dishwasher, an Amana electric
> range, and a Sears side-by-side fridge that is brand new.
>
> The example query (shown below) finds single-family listings that have
> a bedroom with purple carpet and a kitchen with a Sears fridge.

SELECT * FROM houses
WHERE houseType="single family"
AND houseID in
(SELECT houseID FROM rooms WHERE carpetColor="purple") AND houseID in (SELECT houseID FROM furniture WHERE furnitureType="fridge" AND brand="Sears")

There may be a better way to do it with joins instead of IN. Note that I am assuming that the fridge can be anywhere in the house. People often put a 2nd fridge in their garage. In practice I would double check with the customer/requestor. Adding kitchen-only would add another clause or join to the query. Actually, I wonder how your query would look if the fridge could be in any room.

By the way, do any real-estate listings really track fridges?

>
> (and (select single-family instance *)
> (select * has (and (select bedroom instance *)
> (select * has (and (select carpet instance *)
> (select * color purple)))))
> (select * has (and (select kitchen instance *)
> (select * has (and (select fridge instance *)
> (select * mfg sears))))))
>
> If needed, more listing and queries can be added for comparison with
> SQL.
Most of your allegedly tough examples are based on physical modeling. Whether SQL stinks at physical modeling I won't really venture due to lack of experience in that domain. Some CAD experts once claimed relational does have performance problems in CAD.

-t- Received on Sat Jun 24 2006 - 06:57:10 CEST

Original text of this message