Indexes in Relational Model

An index is an orderly arrangement used to logically access rows in a table. Indexes in the relational database environment work like the indexes described in the preceding paragraphs. From a conceptual point of view, an index is composed of an index key and a set of pointers. The index key is, in effect, the index’s reference point. More formally, an index is an ordered arrangement of keys and pointers. Each key points to the location of the data identified by the key.

Read more ...

Degree of Relationship

A relationship degree indicates the number of entities or participants associated with a relationship. A unary relationship exists when an association is maintained within a single entity. A binary relationship exists when two entities are associated. A ternary relationship exists when three entities are associated. Although higher degrees exist, they are rare and are not specifically named. (For example, an association of four entities is described simply as a four-degree relationship.)

Read more ...

Database Design Challenges

Database designers often must make design compromises that are triggered by conflicting goals, such as adherence to design standards (design elegance), processing speed, and information requirements.

Design standards

Read more ...

Cardinality Constraints:

Suppose there are two entity types, A and B, that are connected by a relationship. A cardinality constraint specifies the number of instances of entity B that can (or must) be associated with each instance of entity A. For example, consider a video store that rents videotapes of movies. Because the store may stock more than one videotape for each movie.

Minimum Cardinality:

Read more ...

Entity Supertypes and Subtypes:

Because most employees possess a wide range of skills and special qualifications, data modelers must find a variety of ways to group employees based on employee characteristics. For instance, a retail company could group employees as salaried and hourly employees, while a university could group employees as faculty, staff, and administrators.

The grouping of employees to create various types of employees provides two important benefits:

Read more ...

Specialization Hierarchy

Entity super types and subtypes are organized in a specialization hierarchy, which depicts the arrangement of higher-level entity super types (parent entities) and lower-level entity subtypes (child entities).

The following Figure shows the specialization hierarchy formed by an EMPLOYEE super type and three entity subtypes—PILOT, MECHANIC, and ACCOUNTANT.

 Specialization Hierarchy

Read more ...

Attribute Inheritance in EEE diagrams:

The property of inheritance enables an entity subtype to inherit the attributes and relationships of the supertype. As discussed earlier, a supertype contains those attributes that are common to all of its subtypes. In contrast, subtypes contain only the attributes that are unique to the subtype.

For example, the following Figure illustrates that pilots, mechanics, and accountants all inherit the employee number, last name, first name, middle initial, hire date, and so on from the EMPLOYEE entity.

Read more ...

Subtype Discriminator

A subtype discriminator is the attribute in the supertype entity that determines to which subtype the supertype occurrence is related.

In the following figure, the subtype discriminator is the employee type (EMP_TYPE).It is common practice to show the subtype discriminator and its value for each subtype in the ER diagram, as seen in the Figure.

Read more ...