On Friday 05 September 2008 23:59:25 Igor Tandetnik wrote:
> Andreas Ntaflos
>
> <[EMAIL PROTECTED]> wrote:
> > My problem statement remains partially valid, though.
> > Auto-incrementing the roomID in the Room table would make each room
> > have its very own room number but the primary key still would need to
> > consist of both buildingID and roomID
>
> Why?
Because only then the room entity is truly modelled as a weak entity.
Otherwise it is not weak.
> > or else the modelling of a room
> > as a weak entity wouldn't work correctly.
>
> Define "weak entity". Precisely what properties does it have that are
> not represented in the model?
A weak entity has no possible combination of attributes that would uniquely
identify it in the relational model. It's existence depends on another
entity. The building-room-example illustrates this nicely. There's nothing to
hinder two separate, uniquely identifiable buildings to have the same room
number scheme, so to identify a single room the key of the building is needed
in combination with the room's numer.
That's what the theory on relational modelling defines. And it makes sense,
too. You could work around that by having all rooms of all buildings in the
city (or the country or the world) in a single table and ignoring the
existential dependency on the building that contains the room. This would of
course not scale well and not correctly represent the real world.
The property the desired model has that are not represented in this model we
discuss is that a room's primary key does not contain a reference to the
primary key of the owning entity. It is globally and uniquely identifiable
without ever looking at the building.
> > The buildingID is still a
> > foreign key so SELECTing all rooms of one particular building is
> > still possible but that's only part of the conceptual model.
>
> In what way having a unique roomID violates your conceptual model?
As I described above, the model no longer represents the existential
dependency of the room on the building it is in.
> > I suppose it's not something the authors of books on database systems
> > would approve of.
>
> To the best of my knowledge, they are not the ones paying my salary, so
> personally I'm not too worried about their disapproval.
Of course not, and when practicality dictates it there's no reason not to
ignore a few theoretical concepts as long as the results stay favourable.
Nevertheless I want to try to keep the theory in mind when creating the model
I need and it happens that weak entities are quite an integral part of it.
That's why I asked the question in the first place, although, as I explained,
I didn't realise that auto-incrementing a key value would make it unique in
and on itself.
This is a practical consequence and thus the model you propose is possible and
probably even feasible, but it is no longer the model I wanted to implement.
Plus I don't really see why it would be wrong to use an auto-incrementing key
for the roomID while keeping the tuple (roomID, buildingID) as a primary key.
Granted, no roomID could be duplicated this way, anyway, but I'd prefer to
implement the desired model as faithfully as possible while still being a
lazy typist (this means implementing as little magic as possible to retrieve
the next bigger number to use as room number).
It seems that there are two choices available: go with your model, which is
simpler and certainly more efficient, but loses an aspect of the desired
model (namely that rooms are by themselves not uniquely identifiable).
Or implement the model the way Nicolas proposed [1] (provided I understood it
correctly). This would include some (database-internal) redundancy but the
property of the combined primary key for a room in a building could be kept
valid.
In PostgreSQL the latter model could be implemented like this:
CREATE TABLE Building (
buildingID SERIAL8 PRIMARY KEY
);
CREATE TABLE Room (
buildingID BIGINT,
room_number SERIAL8,
FOREIGN KEY (buildingID) REFERENCES Building(buildingID) ON DELETE
CASCADE,
PRIMARY KEY (buildingID, room_number)
);
This is what I initially wanted to accomplish in SQLite. I thought I could
substitute SERIAL8 (which is really "just" some magic with sequences and
nextval()) for AUTOINCREMENT, which is not possible. I guess SERIAL is one
step higher on the abstraction ladder than AUTOINCREMENT?
Anyway, thanks for your help and the discussion :)
Andreas
[1]
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008-September/005650.html
--
Andreas Ntaflos
Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
