Your BCNF breakdown bears little or no resemblance to the original
data - you have lost lots of information, but the decomposition
process is supposed to be lossless.
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?
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.
You still have not given us all the information we need to do your
homework for you, I'm afraid.
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.
Jonathan Leffler #include <disclaimer.h>
Email: XXXX@XXXXX.COM , XXXX@XXXXX.COM
Guardian of DBD::Informix v2003.04 -- http://www.yqcomputer.com/