Candidate keys

The distinction of tuples implies that, for some given attribute(s), no two tuples will have the same value for those given attribute(s). Such given attribute(s) will be Candidate keys, if and only if there are properties of uniqueness and minimality.

For uniqueness it is meant that no two tuples will have the same value for the given attribute(s).

For minimality it is meant that from the given attributes, no attribute can be discarded without destroying the uniqueness.

Primary keys

The primary key of a relation is one particular key chosen from the candidate keys. Every primary key is a candidate key.

Alternate keys

Where a relation has more than one candidate, only one will be designated as the primary key. The remaining candidate keys are known as alternate keys. If only one candidate key exists that will obviously be the primary key and there will be no alternate keys.

Representation of relationships is a matter of inference from the attributes that different relationships have in common.

model University
domains
   IdentifiersOfStudents = s01 .. s99	
   PersonNames = string
   Years = Yearnumber	
   StaffNumbers = 1000 .. 9999
   Regions = 1 ..9	
   CodesOfCourses = c1 .. c9
   TitlesOfCourses = string	
   CreditValues = (30, 60)
   AssignmentNumbers = 1 .. 5	
   Grades = 0 .. 100

relation Student
   StudentId: IdentifiersOfStudents	
   Name: PersonNames
   Registered: Years	
   CounsellorNo: StaffNumbers
   Region: Regions	
   primary key StudentId
   foreign key CounsellorNo references Staff

relation Course
   CourseCode: CodesOfCourses	
   Title: TitlesOfCourses
   Credit: CredutValues		
   primary key CourseCode

relation Enrolment
   StudentId: IdentifiersOfStudents	
   CourseCode: CodesOfCourses
   TutorNo: StaffNumbers		
   primary key (StudentId, CourseCode)
   foreign key StudentId references Student
   foreign key CourseCode references Course
   foreign key TutorNo references Staff

relation Staff
   StaffNo: StaffNumbers	
   Name: PersonNames
   Region: Regions		
   primary key StaffNo

relation Assignment
   StudentId: IdentifiersOfStudents	
   CourseCode: CodesOfCourses
   AssignmentNo: AssignmentNumbers
   Grade: Grades		
   primary key (StudentId, CourseCode, AssignmentNo)
   foreign key (StudentId, CourseCode references Enrolment

Examining the relations Assignment and Student show that both have the attribute StudentId. It is therefore inferred that a relationship exists between the two relations. Such relationships are implicit and there is no naming convention for these relationships. Their properties and degree can only be inferred from the properties of the attributes of the relations.

The domain construct is more precise. Common attributes have to to have values that are comparable. These values must be declared on the same domain.

Qualified names and the dot notation

Attributes of the same name within different relationships can be distinguished by using dot notation. In the relation Student, this would be Student.StudentId. In the relation Enrolment this would be Enrolment.StudentId. Such attribute naming are referred to as qualified attribute names.

In the Staff and Enrolment relations there are two attributes, StaffNo and TutorNo that each draw their attribute values from the domain of StaffNumbers. These values are comparable and therefore imply a relationship between StaffNo and TutorNo. The values of Staff.StaffNo and Enrolment.TutorNo will match by virtue of shared attribute values.

Foreign keys and relationships

The definition of a foreign key is:

A foreign key is an attribute (or combination of attributes) in one relation, R2, whose values are the same as values of a candidate key (usually the primary key) of some relation, R1, (where R1 and R2 are not necessarily distinct).

The attributes of both the foreign key and the associated primary key must be defined in the same domain. The degree of a relation can be determined by the foreign key mechanism. A 1:n relationship exists where an instance on the 1 side is related to many instances on the n side. The primary/foreign key mechanism is the mechanism for representing these 1:n relationships.

Comments, suggestions, ideas to
Stuart Banner