Given the Relational Model Notation below (in pages 156-157 of your book), 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 extend the Relational Model Notation to define UNIQUE and NON-UNIQUE indexes for relations, we need to introduce additional notation for indexes.
- UNIQUE index: A UNIQUE index ensures that the values in the indexed attribute(s) of a relation are unique, meaning no two tuples can have the same value(s) in the indexed attribute(s). It is denoted by underlining the attribute(s) in the relation schema.
For example, if we have a relation schema STUDENT(Name, Ssn, Age) and we want to create a UNIQUE index on the Ssn attribute, we would represent it as:
STUDENT(Name, Ssn, Age) [UNIQUE INDEX(Ssn)]
This indicates that the Ssn attribute has a UNIQUE index, ensuring uniqueness of values in the Ssn attribute.
- NON-UNIQUE index: A NON-UNIQUE index allows duplicate values in the indexed attribute(s) of a relation. It is denoted by enclosing the attribute(s) in curly braces.
For example, if we have a relation schema STUDENT(Name, Ssn, Age) and we want to create a NON-UNIQUE index on the Age attribute, we would represent it as:
STUDENT(Name, Ssn, Age) [INDEX{Age}]
This indicates that the Age attribute has a NON-UNIQUE index, allowing duplicate values in the Age attribute.
By using this additional notation, we can specify whether an index is UNIQUE or NON-UNIQUE for the attributes of a relation.