Type Inheritance vs Table Inheritance

Question

I am reading about object-based databases from Silberschatz book. It explains Type Inheritance with the following example:

create type Person
(
    name varchar(20),
    address varchar(20)
);

Now we can use type inheritance like:

create type Student under Person 
(
    degree varchar(20),
    department varchar(20)
);

I think I understood upto this point. Now I create a table of type Person as:

create table people of Person;

But now if I want to create a table of type student. Should I do this:

create table students of Student;

OR

create table students of Student under people;
/*which is described in the book as table inheritance*/

What is the difference between the above two? Since we already inherited Person while defining type Student is it necessary to inherit a table of type Person (person) while creating a table of type Student?

If someone could explain this with an example it will be a lot clearer.

Answer 1

Unfortunately the world of classes and inheritance and the world of databases don't always map onto each other well.

In this example, one approach is to create 2 tables:

Person

• Person_Id = primary key of the table
• Name
• Address

Student

• Student_Id = primary key of the table
• Degree
• Department
• Person_Id = foreign key to the Person table

If you make Student.Person_Id not null, it means that every student has a row in the Person table. You would need something else, an index or constraint (in the database) or logic (in code that manipulates the database) to enforce that Student records didn't share Person records if that was important to you.

Example data for these tables:

Person

• Person_Id: 12, Name: Mr. Not-a-student, Address: 999 Letsbe Avenue
• Person_Id: 13, Name: Ms. Grad-student, Address: A Swanky Campus
• Person_Id: 14, Name: Mr. Undergrad, Address: Not-so-swanky Campus
• Person_Id: 15, Name: Ms. Not-a-student-either, Address: 2B Ornottoobee Road

Student

• Student_Id: 859, Degree: Tricky Maths, Department: Maths, Person_Id: 13
• Student_Id: 860: Degree: Rap Lyrics, Department: English, Person_Id: 14

People 12 and 15 are people but not students. People 13 and 14 and linked to students 859 and 860 respectively.

Note that if you were actually building this, you would probably separate things out a bit more (in database-speak, normalise the things):

Address

• Address_Id = primary key
• Address_line_1
• etc.

Person

• Person_Id = primary key
• Name
• Address_Id = foreign key to Address table

Degree

• Degree_Id = primary key
• Subject
• etc.

Department

• Department_Id = primary key
• Name
• Address_Id = foreign key to Address (where the department is)
• Boss = foreign key to Person

Student

• Student_Id = primary key
• Degree_Id = foreign key to Degree
• Department_Id = foreign key to Department
• Person_Id = foreign key to Person