Given the Relational Model Notation below, extend it to define
UNIQUE and NON-UNIQUE indexes for relations.
The uppercase letters Q, R, S denote relation names.
■ The lowercase letters q, r, s denote relation states.
■ The letters t, u, v denote tuples.
■ In general, the name of a relation schema such as STUDENT also indicates
the current set of tuples in that relation—the current relation state—whereas
STUDENT(Name, Ssn, …) refers only to the relation schema.
■ An attribute A can be qualified with the relation name R to which it belongs by using the dot notation R.A—for example, STUDENT.Name or
STUDENT.Age. This is because the same name may be used for two attributes in different relations. However, all attribute names in a particular
relation must be distinct.
■ An n-tuple t in a relation r(R) is denoted by t = <v1, v2, … , vn>, where vi is the value corresponding to attribute Ai. The following notation refers to
component values of tuples: Both t[Ai] and t.Ai (and sometimes t[i]) refer to the value vi in t for attribute Ai. Both t[Au, Aw, … , Az] and t.(Au, Aw, … , Az), where Au, Aw, … , Az is a list of attributes from R, refer to the subtuple of values <vu, vw, … , vz> from t corresponding to the attributes specified in the list.
As an example, consider the tuple t = <’Barbara Benson’, ‘533-69-1238’,
‘(817)839-8461’, ‘7384 Fontana Lane’, NULL, 19, 3.25> from the STUDENT relation in Figure 5.1; we have t[Name] = <‘Barbara Benson’>, and t[Ssn, Gpa, Age] = <‘533-69-1238’, 3.25, 19>.
To define UNIQUE and NON-UNIQUE indexes for relations, we need to understand what these terms mean in the context of the Relational Model.
In the Relational Model, an index is a data structure that improves the efficiency of data retrieval operations on a relation. It allows the database management system to quickly locate the tuples that satisfy certain conditions.
1. UNIQUE Index:
A UNIQUE index enforces the uniqueness of values in one or more attributes of a relation. It ensures that no two tuples in the relation have the same values for the indexed attributes. In other words, it guarantees that the indexed attributes form a unique key for each tuple.
To define a UNIQUE index for a relation, you would specify the attributes that make up the unique index. Here's an example using the STUDENT relation:
STUDENT(Name, Ssn, Gpa, Age, ...)
UNIQUE_INDEX STUDENT(Name, Ssn)
This UNIQUE_INDEX statement indicates that the combination of the Name and Ssn attributes uniquely identifies each tuple in the STUDENT relation.
2. NON-UNIQUE Index:
A NON-UNIQUE index, also known as a regular index, does not enforce uniqueness. It allows duplicate values in the indexed attributes. The purpose of a NON-UNIQUE index is to improve the efficiency of data retrieval operations by providing faster access to tuples based on the indexed attributes.
To define a NON-UNIQUE index for a relation, you would specify the attributes that make up the index. Here's an example using the STUDENT relation:
STUDENT(Name, Ssn, Gpa, Age, ...)
NON_UNIQUE_INDEX STUDENT(Name)
This NON_UNIQUE_INDEX statement indicates that an index should be created on the Name attribute of the STUDENT relation. This allows faster retrieval of tuples based on the Name attribute, but duplicate values are allowed.
In summary, to define UNIQUE and NON-UNIQUE indexes for relations, you specify the attributes that make up the index. UNIQUE indexes enforce uniqueness, while NON-UNIQUE indexes improve data retrieval performance without enforcing uniqueness.