CODEWITHSUNDEEP
sqloracle-databaseentity-relationship
user4252348
user4252348

Reputation:

Oracle SQL queries for subclass-table - Inheritance

I have a Staff table with staffNo as the primary key and staff details present in two other tables inheriting from it: FTLecturer and PTLecturer. I used the Optional Or relationship there.

My question is: how to write Oracle SQL queries for these sub-classes? I think I do not need a PK for them again because they inherit from `Staff'. Is it so?

This is the Staff table

CREATE TABLE Staff
(staffNo number(10) NOT NULL,
 firstName varchar2(50) NOT NULL,
 lastName number(2) ,
address varchar2(50) NOT NULL,
CONSTRAINT courseID_pk PRIMARY KEY (staffNo),
CONSTRAINT fk_Module
FOREIGN KEY (moduleID)
REFERENCES Module(Module Module ID)
);

I need to create the FTLecturer table with the Optional Or relationship.

Upvotes: 0

Views: 2357

Answers (1)

APC
APC

Reputation: 146229

Inheritance is a concept from Object-Oriented Programming: it has no meaning in relational databases. The most important impact of this is that child tables do need a primary key.

"I need to create the FTLecturer table with the Optional Or relationship."

What you're describing is called an Arc in relational databases: a lecturer can be full-time or part-time but not both. We can enforce this with a decent set of constraints.

First add a column to the parent table to identify the Lecturer type. 

alter table staff add staffType varchar2(10);

This should be validated with a foreign key against a reference data table, and at a pinch a check constraint. Then we add a unique constraint (yes, as well as a primary key):

alter table staff add constraint staff_uk unique(staffNo, staffType);

We can use this to enforce the arc on the child tables.

create table FTLecturer (
    staffNo number not null,
    staffType varchar2(10) not null,
    tenure varchar2(3) not null,
    constraint FTLecturer_pk primary key (staffNo),
    constraint FTLecturer_ck check (staffType = 'FTL'), 
    constraint FTLecturer_Staff_fk foreign key (staffNo, staffType)
       references staff (staffNo, staffType);

Note that the foreign key means we can only insert rows in this table which have a parent of the correct type in the STAFF table. This is why we need the StaffType column and that unique constraint on the STAFF table.

Likewise for Part-Time Lecturers:

create table PTLecturer (
    staffNo number not null,
    staffType varchar2(10) not null,
    hours number not null,
    constraint PTLecturer_pk primary key (staffNo),
    constraint PTLecturer_ck check (staffType = 'PTL'), 
    constraint PTLecturer_Staff_fk foreign key (staffNo, staffType)
       references staff (staffNo, staffType);

Since Oracle 11g we can use a virtual column to apply a constant value for the staffType on the child tables. This avoids the need for the check constraints. Find out more.

To create a record for a specific member of staff it is good practice to populate the parent and child in the same action; we can use the INSERT ALL syntax to populate multiple tables. Find out more.

Finally, you can build a helpful API with views. For instance:

create or replace view fulltimeLecturer as
     select staff.*
            , ftl.tenure
     from staff
          join ftlecturer ftl
               on staff.staffno = ftl.staffno
                  and staff.stafftype = ftl.stafftype;

create or replace view parttimeLecturer as
     select staff.*
            , ptl.hours
     from staff
          join ptlecturer ptl
               on staff.staffno = ptl.staffno
                  and staff.stafftype = ptl.stafftype;

This may strike you as a lot of work, and inflexible to boot. Here is the difference between Object Programming and Relational Databases. OOP is primarily driven by helping developers to write code; RDBMS is focused on guaranteeing the integrity of the stored data.

Upvotes: 2

Related Questions