Re: 3 value logic. Why is SQL so special?

paul c wrote:
> Cimode wrote:
> > A simple alternative solution WITHOUT using the god damn NULLS...
> > Flight entity is just equivalent to an assumed and recurring plane
> > departure being associated to a specific departure airport. 1:N
> > cardinality
> >
> > _DT: departure_time
> > _AT: arrival_time
> > _ADT: actual departure time
> > _AAT: actual arrival time
> > _DA: departure_airport
> > _AA: arrival_airport
> > _ADA: actual departure_airport
> > _AAA: actual arrival_airport
> > _LN: last name
> > _FN: first name
> > _DOB: date of birth
> >
> > Note: I use ONLY concatenated PRIMARY key (no surrogate, never liked
> > them)
> >
> > passenger:
> > passenger_LN, passenger_FN, passenger_DOB
> > plane_model:
> > plane_model (PK), plane_capacity
> > plane:
> > planeid, plane_model
> > book:
> > flight_DT, flight_DA, flight_AT, flight_AA, passengerlname,
> > passengerfname, passenger_birthdate
> > plane_flight:
> > planeid, flight_DT, flight_DA, flight_AT, flight_AA
> >
> > flight:
> > flight_DT, flight_DA, flight_AT, flight_AA
> >
> > plane_departure:
> > flight_DT, flight_DA, flight_AT, flight_AA, flight_ADT,
> > flight_ADA
> > plane_departure_check:
> > flight_DT, flight_DA, flight_AT, flight_AA, flight_ADT,
> > flight_ADA, passenger_LN, passenger_FN, passenger_DOB
> > plane_arrival:
> > flight_DT, flight_DA, flight_AT, flight_AA, flight_AAT,
> > flight_AAA
> >
> >
> >>From the above logical structure you can pretty much get anything you
> > want including:
> >
> >> How late/early are the planes on current schedules
> >> If the planes could land at their supposed destination (you may associate a reason for it: BAD WEATHER, HIJACK). You know if the plane actually landed where it was supposed to land or not...You need to create and additional entity for that...
> >> What plane model should be scheduled to do a flight depending on book counts
> >> Which passengers did not leave where there were supposed to...
> >
> > And many other information...
> > This is a purely pedagogical case (far from being complete) to
> > demonstrate that it is perfectly possible to build some logical design
> > in minutes (took me 20 of them) WITHOUT using NULLS...while sticking to
> > the God Damn Real World (lazyness) excuse...
My main point was to close the NULL debate by showing that doing without them is NOT as difficult as people think but as you seem interested here are the answers to your question...

> I haven't examined all of the above, but:
>
> 1) Where is flight number? This is IATA-mandated and perhaps the most
> common term used in the biz as well as by customers.
Just keep in mind that I purposely used only concatenated keys for the example's sake. It is easy to use a given IATA surrogate key kept in synch with the concatenated key of
flight: the final entity would look like...

             flight_DT, flight_DA, flight_AT, flight_AA, flight_number(SK)
and Flight number can replace occurrences of the concatenated keys in all other entities....

> An individual
> flight segment is identified by flight number (which these days also
> includes alpha characters), qualified by origin and destination airport
> codes and date and time - note the time includes only hours and minutes,
> not seconds, note also that a flight number can't be used twice in one
> day with the same origin and destination - 'lollipop' flights are
> illegal because they are a way to escape certain airport fees, also only
> certain apps, such as loading and gate assignment, have anything to do
> with plane identification).
>
> The main point I'm trying to make here is that much of what WE think
> doesn't matter and usually only adds to an inflated cost to make and run
> a system. The strictest interpretation of the biz rules (many of which
> are well-documented in this case) is the main avenue towards rightness
> and economy.
Your point is valid but it was not my intent to produce the best design (this would be impossible without knowing the details of the project) but rather to demonstrate that doing without NULLS is not an abstraction but a perfectly applicable rule in "REAL WORLD" example...Thank you for such comment.

> 2) Just as the DBA isn't finished if he includes nulls, he's not
> finished until he considers the frequent usages of his schemas.
> Personally, if I had a choice, I would never use all the above
> attributes as the usual 'key' as massive changes to many tables are
> needed whenever flights are re-scheduled and this is frequent and
> common. Better to have some kind of flight assignment table so that
> most of the app's deal only with a unique assigned internal number,
> whether most people would call this a surrogate, I don't know.
In the point provided, the modification (using surrogate key flight number) would not require to be implemented more than once. IN a word, it is a minor one shot adjustment.

But thanks for pointing the issue of having stable keys...

Regards...
> p
Received on Thu Sep 14 2006 - 21:44:19 CEST