Foreign Keys vs. Partial Keys and their E-R representations

Question

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.

Answer 1

The problem is not you, it is that the ancient textbook or whatever you are using is pure excreta, the "definitions" are not clear, and there have been standard definitions for Relational Databases in use for over 30 years, which are much more clear. The "definitions" you have posted are in fact quite the opposite, non-intuitive, and it is no surprise that people would get confused.

1. A Foreign Key in a child row, is the value that references its parent Primary Key (in the parent table).

2. Using IDEF1X terminology. An Identifying Relation is one in which the FK (the parent Pk in the child) is also used to form the child PK. It is unique in the parent, but not unique in the child, you need to add some column to make it unique. Hence the stupid term "Partial Key". Either it is a Key (unique) or it is not a Key; the concept of a "partial Key" is too stupid to contemplate.

3. In a properly Normalised and standard-compliant database, there will be very few Independent entities. All the rest will be Dependent on some Independent entity. Such entities are not "weak", except in the sense that they cannot exist without the entity that they are Dependent upon.

   The use of Identifying Relations (as opposed to Non-identifying) is actually strong; it gives the Dependent ("weak") entities their Identifier. So silly terms like "weak" and "strong" should not be used in a science that demands precision.

   Use standard terms.

4. But to answer your explicit question:

   • assuming that Employee is "strong" and has a Primary Key (EmployeeId)
   • then the "weak" EmployeeChild table would need a FK (EmployeeId) to identify the Employee
   • which would be the perfect first component of the EmployeeChild table, the adorable "partial key"
   • to which you might add ChildNo, in order to make an ordinary Relational Primary Key
   • but it is not really "partial" because it is the full Primary Key of the Parent.

Readers who are unfamiliar with the Standard for Modelling Relational Databases may find ▶IDEF1X Notation◀ useful.

Answer 2

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.