I have a
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),
FOREIGN KEY (moduleID)
REFERENCES Module(Module Module ID)
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.