CELKO (9/27/2012)
First, let's fix your DDL. There is no such thing a “type_id” in RDBMS; read ISO-11170 or any book on data modeling. Would you say “blood_type_id”? Of course not! A type and an id are totally different kinds of attribute properties. But why put it in the skeleton since we do not need it for the problem?The occupancy_end_date has to be NULL-able to show the guests who are still here (basic temporal idiom in SQL).
--snip
The system from which the data is taken deals with room types (single, double, family with view of wildebeest etc), not individual rooms. These are held in a master table which has an Id column.
The table does not represent room occupancy: it represents room types at hotels which are on, or will be on, stop-sale. This is never an open interval.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.