Object-Relational Databases:Inheritance

Inheritance

Inheritance can be at the level of types, or at the level of tables. We first consider inheritance of types, then inheritance at the level of tables.

Type Inheritance

Suppose that we have the following type definition for people:

We may want to store extra information in the database about people who are students, and about people who are teachers. Since students and teachers are also people, we can use inheritance to define the student and teacher types in SQL:1999:

Both Student and Teacher inherit the attributes of Person — namely, name and address. Student and Teacher are said to be subtypes of Person, and Person is a supertype of Student, as well as of Teacher.

Methods of a structured type are inherited by its subtypes, just as attributes are. However, a subtype can redefine the effect of a method by declaring the method again, using overriding method in place of method in the method declaration.

Now suppose that we want to store information about teaching assistants, who are simultaneously students and teachers, perhaps even in different departments.

We can do this by using multiple inheritance, which we studied in Chapter 8. The SQL:1999 standard does not support multiple inheritance. However, draft versions of the SQL:1999 standard provided for multiple inheritance, and although the final SQL:1999 omitted it, future versions of the SQL standard may introduce it. We base our discussion on the draft versions of the SQL:1999 standard.

For instance, if our type system supports multiple inheritance, we can define a type for teaching assistant as follows:

create type TeachingAssistant

under Student, Teacher

Teaching Assistant would inherit all the attributes of Student and Teacher. There is a problem, however, since the attributes name, address, and department are present in Student, as well as in Teacher.

The attributes name and address are actually inherited from a common source, Per- son. So there is no conflict caused by inheriting them from Student as well as Teacher. However, the attribute department is defined separately in Student and Teacher. In fact, a teaching assistant may be a student of one department and a teacher in another department. To avoid a conflict between the two occurrences of department, we can rename them by using an as clause, as in this definition of the type Teaching Assistant:

create type Teaching Assistant

under Student with (department as student-dept),

Teacher with (department as teacher-dept)

We note that SQL:1999 supports only single inheritance — that is, a type can inherit from only a single type; the syntax used is as in our earlier examples. Multiple inheritance as in the Teaching Assistant example is not supported in SQL:1999. The SQL:1999 standard also requires an extra field at the end of the type definition, whose value is either final or not final. The keyword final says that subtypes may not be created from the given type, while not final says that subtypes may be created.

In SQL as in most other languages, a value of a structured type must have exactly one “most-specific type.” That is, each value must be associated with one specific type, called its most-specific type, when it is created. By means of inheritance, it is also associated with each of the super types of its most specific type. For example, suppose that an entity has the type Person, as well as the type Student. Then, the most- specific type of the entity is Student, since Student is a subtype of Person. However, an entity cannot have the type Student, as well as the type Teacher, unless it has a type, such as Teaching Assistant, that is a subtype of Teacher, as well as of Student.

Table Inheritance

Subtables in SQL:1999 correspond to the E-R notion of specialization/generalization. For instance, suppose we define the people table as follows:

create table people of Person

We can then define tables students and teachers as subtables of people, as follows:

create table students of Student

under people

create table teachers of Teacher

under people

The types of the subtables must be subtypes of the type of the parent table. Thereby, every attribute present in people is also present in the subtables.

Further, when we declare students and teachers as subtables of people, every tuple present in students or teachers becomes also implicitly present in people. Thus, if a query uses the table people, it will find not only tuples directly inserted into that table, but also tuples inserted into its subtables, namely students and teachers. However, only those attributes that are present in people can be accessed.

Multiple inheritance is possible with tables, just as it is possible with types. (We note, however, that multiple inheritance of tables is not supported by SQL:1999.) For example, we can create a table of type Teaching Assistant:

create table teaching-assistants

of Teaching Assistant

under students, teachers

As a result of the declaration, every tuple present in the teaching-assistants table is also implicitly present in the teachers and in the students table, and in turn in the people table.

SQL:1999 permits us to find tuples that are in people but not in its subtables by using “only people” in place of people in a query.

There are some consistency requirements for subtables. Before we state the constraints, we need a definition: We say that tuples in a subtable corresponds to tuples in a parent table if they have the same values for all inherited attributes. Thus, corresponding tuples represent the same entity.

The consistency requirements for subtables are:

1. Each tuple of the supertable can correspond to at most one tuple in each of its immediate subtables.

2. SQL:1999 has an additional constraint that all the tuples corresponding to each other must be derived from one tuple (inserted into one table).

For example, without the first condition, we could have two tuples in students (or teachers) that correspond to the same person.

The second condition rules out a tuple in people corresponding to both a tuple in students and a tuple in teachers, unless all these tuples are implicitly present because a tuple was inserted in a table teaching-assistants, which is a subtable of both teachers and students.

Since SQL:1999 does not support multiple inheritance, the second condition actually prevents a person from being both a teacher and a student. The same problem would arise if the subtable teaching-assistants is absent, even if multiple inheritance were supported. Obviously it would be useful to model a situation where a person can be a teacher and a student, even if a common subtable teaching-assistants is not present. Thus, it can be useful to remove the second consistency constraint. We return to this issue in Section 9.3.3.

Subtables can be stored in an efficient manner without replication of all inherited fields, in one of two ways:

• Each table stores the primary key (which may be inherited from a parent table) and the attributes defined locally. Inherited attributes (other than the primary key) do not need to be stored, and can be derived by means of a join with the supertable, based on the primary key.

• Each table stores all inherited and locally defined attributes. When a tuple is inserted, it is stored only in the table in which it is inserted, and its presence is inferred in each of the super tables. Access to all attributes of a tuple is faster, since a join is not required. However, in case the second consistency constraint is absent — that is, an entity can be represented in two sub tables without being present in a common sub table of both — this representation can result in replication of information.

Overlapping Subtables

Inheritance of types should be used with care. A university database may have many subtypes of Person, such as Student, Teacher, Football Player, Foreign Citizen, and so on. Student may itself have subtypes such as Under graduate Student, Graduate Student, and Part Time Student. Clearly, a person can belong to several of these categories at once. As Chapter 8 mentions, each of these categories is sometimes called a role.

For each entity to have exactly one most-specific type, we would have to create a subtype for every possible combination of the super types. In the preceding example, we would have subtypes such as Foreign Undergraduate Student, Foreign Graduate Student Football Player, and so on. Unfortunately, we would end up with an enormous number of subtypes of Person.

A better approach in the context of database systems is to allow an object to have multiple types, without having a most-specific type. Object-relational systems can model such a feature by using inheritance at the level of tables, rather than of types, and allowing an entity to exist in more than one table at once.

For example, suppose we again have the type Person, with subtypes Student and Teacher, and the corresponding table people, with subtables teachers and students. We can then have a tuple in teachers and a tuple in students corresponding to the same tuple in people.

There is no need to have a type Teaching Assistant that is a subtype of both Student and Teacher. We need not create a type Teaching Assistant unless we wish to store extra attributes or redefine methods in a manner specific to people who are both students and teachers.

We note, however, that SQL:1999 prohibits such a situation, because of consistency requirement 2 from Section 9.3.2. Since SQL:1999 also does not support multiple in-

heritance, we cannot use inheritance to model a situation where a person can be both a student and a teacher. We can of course create separate tables to represent the Data base Data bases information without using inheritance. We would have to add appropriate referential integrity constraints to ensure that students and teachers are also represented in the people table.