What is an attribute or group of attributes that uniquely identify a tuple in a relation?

The relational model is very simple and elegant; a database is a collection of one or more relations, where each relation is a table with rows and columns. This simple tabular representation enables even novice users to understand the contents of a database and it permits the use of simple, high-level languages to query the data. The major advantages of the relational model over the older data models are its simple data representation and the ease with which even complex queries can be expressed. The relational model represents the database as a collection of relations (or tables). Informally each relation resembles a table of values or, to some extent, a “flat” file of records. One of the main advantages of relational model is that it is conceptually simple and more important. It also frees the users from detail of storage and access methods.

A relational model was proposed by Ted Codd of IBM Research in 1970. Basic concept in relational model is that the relation can be viewed as a table which has the following properties

1. It is column homogenous in other words in any given column of the table all items are of same kind
2. Each item is a simple character string or a number
3. All rows of a table or relation are distinct
4. The ordering of rows within a table is a immaterial
5. The column of a table are assigned distinct name and ordering of these columns is immaterial.

Attributes

In relational model terminology all the column headers are called attributes. Consider a table STUDENT. In this table there are three column headers, it means this table has three attributes RollNo, Name, Address

Domain

“The set of permitted values for each attribute is called domain” or “A domain is referred to in a relation schema by the attribute name and has a set of associated values”. A domain D is a set of atomic values. By Atomic we mean that each value in the domain is individual as far as the relational model is concerned. “The data type describing the types of values that can appear in each column is represented by a domain of possible values.” For example Set_phone_number can be declared as of character strings. The data type for Employee_ages is an integer number between 15 and 80. For academic_deaprtment_names, the data type is the set of all characters strings that represent valid department names. A domain is thus given a name, data type, and format.

Tuples / Records

In relational model terminology all the rows are called tuples or records in the relation. Consider a table STUDENT. In this table there are six rows, it means there six tuples or records in this table

Relation schema

“The relation schema describes the column headers for the table or relation”. A relation schema R denoted by R (A1, A2, A3…An), is made up of a relation name R and a list of attributes A1, A2, A3… An. Each attribute Aj, is the name of role played by some domain D in the relation schema R. D is called domain of Aj and is denoted by dom (Aj). A relation schema is used to describe a relation R, and R is called the name of this relation. “The degree (or arity) of a relation is the number of attributes of its relation schema”.
An example of a relation schema for a relation degree seven, which describes university students, is the following

STUDENT (Name, RollNo, HomePhone, Address, OfficePhone, Age, GPA)
Using the data type of each attribute, the definition is sometimes written as:
STUDENT (Name: string, RollNo: string, HomePhone: string, Address: string, OfficePhone: string, Age: number, GPA: real)

Relation

The main construct for representing data in the relational model is a relation. “A relation consists of a relation schema and a relation instance. The relation instance is a table, and the relation schema describes the column heads for the table”. A relation (or relation state) r of the relation schema R (A1, A2, A3…An), is a set of n–tuples r = {t1, t2, t3, . . . .tn}, which is denoted by r (R). Each tuple t is an ordered list of n values t = , where each value vi (1in) is an element of domain or is a special null value.

Characteristics of relations

1. Ordering of tuples in a relation
   A relation is defined as a set of tuples. Tuples in a relation do not have any particular order. Tuple ordering is not a part of relation definition, because a relation attempts to represent facts at a logical or abstract level. For example tuples in the STUDENT relation could be logically ordered by name, roll no, address, and age or by some other attribute.
   
2. Ordering of values within a Tuple
   According to the preceding definition of a relation the ordering of values in tuple is important. However at a logical level, the order of attributes and their values is not that important as long as the correspondence between attributes and values is maintained.


3. Values and Nulls in the tuples
   Each value in a tuple is an atomic value. It means it is not divisible into components within the framework of the basic relational model. Hence, composite and multivelued attributes are not allowed. This model is some times called the flat relational model.

An important concept is that of nulls, which are used to represent the values of attributes that may be unknown or may not apply to a tuple. A special value, called null is used for these cases.

4. Interpretation (Meaning) of a Relation
   The relation schema can be interpreted as a declaration or as a type of assertion. For example the schema of the STUDENT relation as given below asserts that a student entity has a Name, RollNo, HomePhone, Address, OfficePhone, Age, and GPA. Each tuple in the relation can be interpreted as a fact or a particular instance of the assertion. For example in the following figure first tuple asserts the fact that there is a student whose name is Ramesh, RollNo is 305612435, age is 19 and so on. An alternative interpretation of a relation schema is as a predicate in this case the values in each tuple are interpreted as values that satisfy the predicate.

Key

“A key is a subset of one or more fields that allows us to identify a set of attributes that suffice to distinguish tuples from each other”. It is used to implement the concept of key constraints in the relations.

Superkey

Superkey is a subset of one or more attributes that allows us to identify uniquely a tuple in the relation. For example in the above relation the sid attribute of the relation STUDENT is sufficient to distinguish one student entity or a tuple from another. Each relation contains a default Superkey which is a set of all the attributes. In above relation STUDENT {sid}, {login}, {sid, name}, {name, login}, {sid, name, login, age} is a Superkey, and set of all attribute {sid, name, login, age, gpa} is also a Superkey, which is default Superkey for this relation.

Candidate key

A minimal subset of fields that uniquely identifies tuples in a relation is called a candidate key for the relation. It is also called a minimal Superkey for a relation. A relation may have several candidate keys.

Primary key

Primary key is a key or a key attribute in a relation which allows only unique values to be accepted by a relation. It does not allow a relation to accept null values. Primary key is a key by which all the tuples can be identified uniquely. It restricts the duplicate rows in a relation.

Foreign key

When a primary key of a relation (or a table) is used as a primary key in another relation (or table), it is called foreign key. For example there are two relations EMPLOYEE and SALARY which contain employee details and salary details of employee respectively. An attribute {emp_id} which is present in both the relation can be considered as a primary key in EMPLOYEE relation and as a foreign key in SALARY relation.

Unique key

Unique key is just like a primary key with a little difference that primary key enforces the NOT NULL constraint but unique key do not enforce NOT NULL constraint in the relation. It means a unique key is a key which allows a relation to accept only unique values and null value. A unique value can accept only one null value in a relation.

Alternate key

A relation may contain more then one candidate key. If one candidate key has been chosen as a primary key than another candidate key is called alternate key in that relation. For example a STUDENT relation has two attributes {s_di} and {login_id}. In this case both attributes serve as a unique identifier for the relation. Hence, both of them are called the candidate keys. If suppose {s_id} has been chosen as primary key then {login_id} would become alternate key

Composite key

In certain tables a single attribute can not be used to identify rows uniquely then a combination of two or more attributes is used to as a primary key. Such keys are called composite keys.

The main types of constraints that can be expressed in the relational model are as

1. Domain constraint
2. Key constraints or null constraint
3. Integrity constraint
• Entity integrity constraint
• Referential integrity constraint

1. Domain Constratins
   Domain constraint specifies that within each tuple of a relation the value of each attribute must be an atomic value. By atomic means it can not be further divided into sub components
2. Key Constraints
   A relation is defined as a set of tuples. By definition all the elements of a set are distinct; hence, all the tuples in a relation must also be distinct. This means that no two tuples can have the same combination of values for all their attributes. A key constraint is a statement that a certain subset of the fields of a relation is a unique identifier for a tuple.
   

3. Integrity Constraints
   Integrity constraints are such constraints which are specified on the database schema and are expected to hold on every valid database state of that schema. In addition to domain, key, and NOT NULL constraints, two other types of constraints are considered which are entity integrity constraint and referential integrity constraint.
1. Entity Integrity constraint
   The entity constraint specifies that no primary key value can be null. This is because the primary key value is used to identify individual tuples in a relation. Having null values for the primary key implies that we cannot identify some tuples.
2. Referential Integrity constraint
   The referential integrity constraint is specified between two relations and is used to maintain the consistency among the tuples in two relations. The condition for a foreign key specifies a referential integrity constraint between two relations.

Which attribute used to uniquely identify tuples in a relation?

What is used to uniquely identify each tuple in a table?

Is an attribute or a set of attributes that uniquely identifies the relation?