keys in DBMS
Differences between entities must be expressed in terms of attributes.
- A superkey is a set of one or more attributes which, taken collectively, allow us to identify uniquely an entity in the entity set.
- For example, in the entity set customer, customer-name and S.I.N. is a superkey.
- Note that customer-name alone is not, as two customers could have the same name.
- A superkey may contain extraneous attributes, and we are often interested in the smallest superkey. A superkey for which no subset is a superkey is called a candidate key.
- In the example above, S.I.N. is a candidate key, as it is minimal, and uniquely identifies a customer entity.
- A primary key is a candidate key (there may be more than one) chosen by the DB designer to identify entities in an entity set.
An entity set that does not possess sufficient attributes to form a primary key is called a weak entity set. One that does have a primary key is called a strong entity set.
- The entity set transaction has attributes transaction-number, date and amount.
- Different transactions on different accounts could share the same number.
- These are not sufficient to form a primary key (uniquely identify a transaction).
- Thus transaction is a weak entity set.
For a weak entity set to be meaningful, it must be part of a one-to-many relationship set. This relationship set should have no descriptive attributes. (Why?)
The idea of strong and weak entity sets is related to the existence dependencies seen earlier.
- Member of a strong entity set is a dominant entity.
- Member of a weak entity set is a subordinate entity.
A weak entity set does not have a primary key, but we need a means of distinguishing among the entities.
The discriminator of a weak entity set is a set of attributes that allows this distinction to be made.
The primary key of a weak entity set is formed by taking the primary key of the strong entity set on which its existence depends (see Mapping Constraints) plus its discriminator.
- transaction is a weak entity. It is existence-dependent on account.
- The primary key of account is account-number.
- transaction-number distinguishes transaction entities within the same account (and is thus the discriminator).
- So the primary key for transaction would be (account-number, transaction-number).
Just Remember: The primary key of a weak entity is found by taking the primary key of the strong entity on which it is existence-dependent, plus the discriminator of the weak entity set.