I'm having trouble understanding the difference between partial keys/weak entities and foreign keys. I feel like an idiot for not being able to understand this stuff.
As I understand it:
Weak Entity: An entity that is dependent on another entity.
Partial Key: Specifies a key that that is only partially unique. Used for weak entities.
vs
Foreign Key: A key that is used to establish and enforce a relation between data in different tables.
These don't seem like they're the same thing, but I'm having trouble distinguishing their uses.
Take the [very] simple example:
We have employees specified by an empid. We also have children specified by name. A
child is uniquely specified by name when the parent (employee) is known.
Would the child entity be a weak identity where the partial key is the name (partially unique)? Or should I be using a foreign key because I'm trying to establish and enforce a relation between employee and child? I feel like I can justify both, but I also feel like I'm missing something here. Any insight is appreciated, and I apologize for the stupid questions.
A weak entity type is one whose primary key includes some attribute(s) that reference another entity. In other words a foreign key is a subset of the primary key. Therefore the entity cannot exist without its parent.
A partial key means just part of a key - some proper subset of the key attributes.
In your example if the primary key of a Child was (Empid, ChildName) with Empid as a foreign key referencing the Employee then Child is a weak entity. If Empid was not part of the primary key then Child would be a strong entity.
It's worth bearing in mind that the weak/strong distinction is purely an ER modelling concept. In relational database terms it doesn't make much difference. In particular the relational model doesn't make any distinction between primary keys and other candidate keys so for all practical purposes it doesn't make any difference to single out primary key attributes as being a "special" case when they reference other tables.