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

From: paul c <toledobythesea_at_oohay.ac>
Date: Thu, 14 Sep 2006 13:30:47 GMT
Message-ID: <bQcOg.540922$iF6.314593@pd7tw2no>

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...

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. 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.

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.

p Received on Thu Sep 14 2006 - 08:30:47 CDT