CELKO (9/9/2012)

---

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,

tenure_date DATE);

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

REFERENCES Catalog,

semester_name CHAR(7) NOT NULL

REFERENCES Semesters,

PRIMARY KEY (course_nbr, semester_name)

emp_id INTEGER

REFERENCES Professors);

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 '[12][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),

Etc.

Now try your queries with a proper schema.