Re: BCNF and relational algebra

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:Grf_b.5791$aT1.1280_at_newsread1.news.pas.earthlink.net...

> What are the unspoken rules about the data? Can a single patient see
> more than one doctor on a given date? Can they see the same doctor
> several times at the same clinic, in the same ward on the same date?

All yes.

> Given the example data, it seems we can infer that both clinics 3 and
> 4 have a ward 2. We can clearly create a 1NF relation by replicating
> the patient ID, patient name, and ward values for the second row. We
> are then missing all the information that would help us deduce how
> this data can be normalized further.
>
> The chances are good that Patient ID --> Patient Name.
>
> Every other FD in your initial solution is disputable. A given doctor
> may work at several clinics. There is unlikely to be a rule that once
> a patient has been admitted to ward 2 at some clinic, thereafter, he
> must always be admitted to ward 2 at every clinic. As already
> discussed in other postings, the unary relation date is not significant.

I actually mentioned in my first post that a doctor is only associated with one clinic, and that patients will not change wards during their stay (and that doctor names are unique). These were not an assumption on my part, but facts that came with the data. Sorry if I wasn't clearer.

> Note that relational databases work very largely by comparing data in
> relations (tables) on the basis of comparable attributes (columns).
> Depending on the ideological purity of your RTD (relational theory of
> data), the column names and types of the attributes (columns) should
> be the same in the two relations being compared. In your breakdown
> into 3 relations, there are no common attributes between any pair of
> the relations. As Codd delighted in saying, a database is
> partitionable into subsets c and C if the relations in c use no common
> domains (types) with those in C. Your initial solution is fully
> partionable into three relations. As you said in your original post,
> it is not possible to frame any queries between such disjoint tables.

Yes exactly, which is why this is all so confusing. If I left the data in 1NF then the FDs would be:

Patient Id, Date, Doctor -> Patient Name
Patient Id, Date, Doctor -> Ward
Patient Id, Date, Doctor -> Clinic

since (Patient Id, Date, Doctor) must be the primary key. But also I can deduce that Patient Id -> Patient Name, and Patient Id -> Ward and finally Doctor -> Clinic from the info I mentioned a couple of lines above. Also Date on its own doesn't determine anything. And basically that was how I decomposed my data into BCNF, by having three relations where Patient Id -> Patient Name, Ward and Doctor -> Clinic and Date which followed from the above observations. Clearly this is BCNF because the determinants are candidate keys (and obviously 3NF since full functional dependence and no transitivity). The puzzling thing is that I can't make any query on this (as mentioned), so either I've missed a functional dependence somewhere or there is an alternate way to express the data in BCNF (but I can't see how at all). Or perhaps I can make a query if I do a cross product of date with the other two relations and then join the results over date--is this possible/make sense (since the cross product operaton does not require common attributes)?

Thank you again. Received on Mon Feb 23 2004 - 19:43:15 CET