The correct terms are "preserved table " and "unpreserved table" and your design is wrong. You have no keys, we do not use BIT flags in SQL, you believe in a magical "id" that changes from table to table, becoming a professor, a squid, a class, an automobile, etc. That is Kabahlah magice and nto RDBMS.
You had only one professor, no key, a name that violates USPS standards and an assembly language bit flag. IDENTITY properties? And no DRI because of the bad non-schema. Yes, without keys and DRI, this is not RDBMS. You also do not understand temporal data.
CREATE TABLE Professors
(emp_id INTEGER NOT NULL PRIMARY KEY,
professor_name VARCHAR(35) NOT NULL,
Your life will be easier if you learn to use row constructors:
INSET INTO Professors
VALUES (1, 'Dr Coke', '2011-01-01'),
(2, 'Dr Sprite', '2011-01-01'),
(3, 'Dr 7-up', '2011-01-01'),
(4, 'Mr Pepper', NULL),
(5, 'Mr Fanta', NULL);
Notice the use of a NULL for non-tenured professors. This still stinks and we ought to have a status and date range. But let's skip that design flaw for now.
CREATE TABLE Classes
(course_nbr INTEGER NOT NULL
semester_name CHAR(7) NOT NULL
PRIMARY KEY (course_nbr, semester_name)
The common temporal idiom in SQL is a look up table for reporting periods. I prefer a name that can be sorted with ISO-8601 data:
CREATE TABLE Semesters
(semester_name CHAR(7) NOT NULL PRIMARY KEY
CHECK (semester_name LIKEB '[0-9][0-9][0-9]-S[1-3]'),
semester_start_date DATE NOT NULL,
semester_end_date DATE NOT NULL,
CHECK(semester_start_date < semester_end_date);
You can get the class name from the catalog and not waste space and time on it. What you had was not normalized. Courses have numbers; look at a college catalog. The NULL is my favorite instructor “TBD” (I do adjunct teaching and that means work).
INSERT INTO Class_Schedule (class_nbr, semester_name, emp_id)
VALUES (101 '2010-S1', 4),
(101, '2010-S2', 4),
(101, '2011-S1', 4),
(101, '2012-S1', NULL),
(101, '2012-S2', NULL),
Now try your queries with a proper schema.