Luis Cazares (9/10/2012)

---

Can you explain us these terms? I can't find any reference on the web.

thisisfutile (9/11/2012)

---

While I'm sure Joe has a deeper, ANSI definition for these terms, I believe their simplest usage is as a reference point. In a LEFT join the LEFT table is the preserved table and the RIGHT is unpreserved. The reverse would be true in a RIGHT join. So in discussion it doesn't matter if we're talking about LEFT or RIGHT joins, we'll know which table is which using these two terms. Like in a car, we don't reference left and right sides because that's relevant to whether or not I'm looking at it from the back or from the front. Of course, referencing driver-side and passenger-side is different based on the country you live in. Now that I think about it, like boats, I'm all for using port and starboard for cars.

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.

"We" do use BITs quite often. They are a nice small data type where I can fit 8 of them into a byte. Unless you have a good reason not to use them that I've never heard of I think I'll continue to not waste disk space

pg53 (9/12/2012)

---

Kenneth
Just to pick up on this point you made:

My understanding is that this is the behaviour of the Bit field type anyway - is there a reason why you would do your own packing into a Tinyint?

thisisfutile (9/11/2012)

---

While I'm sure Joe has a deeper, ANSI definition for these terms, I believe their simplest usage is as a reference point. In a LEFT join the LEFT table is the preserved table and the RIGHT is unpreserved. The reverse would be true in a RIGHT join. So in discussion it doesn't matter if we're talking about LEFT or RIGHT joins, we'll know which table is which using these two terms. Like in a car, we don't reference left and right sides because that's relevant to whether or not I'm looking at it from the back or from the front. Of course, referencing driver-side and passenger-side is different based on the country you live in. Now that I think about it, like boats, I'm all for using port and starboard for cars.

CELKO (9/9/2012)

---

...we do not use BIT flags in SQL...