4

We know, a super key of an entity set is a set of one or more attributes whose values uniquely determine each entity A candidate key of an entity set is a minimal super key Although several candidate keys may exist, one of the candidate keys is selected to be the primary key.

Again we know, The combination of primary keys of the participating entity sets forms a super key of a relationship set. Can anyone help me to understand the relationship among them with an easy example?

nvogel
  • 24,981
  • 1
  • 44
  • 82
Light
  • 199
  • 1
  • 3
  • 9
  • Relation: Employees {EmployeeNum, LoginName}. Superkeys: {EmployeeNum}, {LoginName}, {EmployeeNum, LoginName}. Candidate keys: {EmployeeNum}, {LoginName}. Does that help? – nvogel Oct 20 '13 at 17:14
  • 1
    It sounds like notions from the ER and relational data models are getting mixed up a bit here. @Niladri Are you asking for an example of how to translate an ER model into a relational model? E.g., mapping the ER model Person(_Name_, Address) - - Project(_ProjectName_, Description) to the three relations Person(_Name_, Adress), worksFor(_Name_,_ProjectName_), Project(_ProjectName_, Address) (Key attributes in italics) – Fabian Oct 20 '13 at 17:30
  • a school can have any number of students. However, if we know grade and roll number, then we can uniquely identify a student in that school. But can {LoginName} here uniquely identify a employee?@sqlvogel – Light Oct 20 '13 at 17:55
  • @Niladri, Of course LoginName *could* identify an employee if it is a business rule that it be unique and if its uniqueness is enforced with a key constraint in the Employees table. – nvogel Oct 20 '13 at 19:16

1 Answers1

0

Person(id, SocialInsuranceNumber, name, family)

Super key examples:

{id, name} or

{SocialInsuranceNumber, name, family} or

{id, SocialInsuranceNumber, name, family}

the {id, name} is unique. No two persons have the same values for the two mentioned fields. But this set is not minimal. If we drop the "name", then the remaining is a super key again. So this is not a candidate key.

Candidate keys:

{id},

{SocialInsuranceNumber}

They are minimal and unique.

Primary key:

{id}

this is the shortest length (only one field and "int" instead of varchar(9) that is the length of the other candidate key).

Also PK is better to be dataless (nothing that may be seen or changed by the user at all, because it may cause problems).

Community
  • 1
  • 1
Alisa
  • 2,892
  • 3
  • 31
  • 44
  • I am new to dbms , question might be silly . Why do we need super key and candidate key? when we can get the unique row by primary key? –  Sep 16 '20 at 12:00
  • 1
    In today's DB systems, developers usually use the primary keys for ensuring constraints. So, the notions of super-key and candidate-key are used in theoretic definitions and theorems. Also, there are applications in normalizing the database (both practical and theoretical aspects). – Alisa Oct 25 '20 at 21:42