Joe,
Thanks for the lesson.
A question:
Regarding "assembly language bits", we have a case where we need to track historic medical data for patients (problems), and track if it's resolved or not. We may not have a date (it could have been 20 years ago, and the patient does not remember). I am guessing that you are promoting a look-up table, with two values (Resolved, Unresolved).
Also, "Your life will be easier if you learn to use row constructors: " - I guess it depends on the version of the DBMS the author is using. Some people are still on SQL Server 2005.
David
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.