Stairway to Database Design Level 4: Building a Schema

Question

Post reply

Stairway to Database Design Level 4: Building a Schema

Joe Celko

SSCertifiable

Points: 5749
More actions
March 26, 2019 at 2:27 pm

#2030948

Comments posted to this topic are about the item Stairway to Database Design Level 4: Building a Schema
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tom Wickerath Valued Member Points: 53 More actions · Answer 1

SQL Server 2017 (CU18)

I cannot get Joe Celko's example to work, as advertised!

create table Hotel
  (room_nbr integer not null,
   arrival_date date not null,
   departure_date date not null,
   guest_name char(30) not null,
   constraint schedule_right 
  check (arrival_date <= departure_date)
  );
go


create or alter view Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name)
  as
     select H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name
     from   Hotel as H1
     where not exists
        (select *
         from   Hotel as H2
         where  H1.room_nbr = H2.room_nbr
           and  H2.arrival_date < H1.arrival_date
           and  H1.arrival_date < H2.departure_date)
           and  H1.arrival_date <= H1.departure_date
         with check option;
go

insert into Valid_Hotel_Stays values (1, '2011-01-01', '2011-01-03', 'Ron Coe');

-- Followed by

insert into Valid_Hotel_Stays values (1, '2011-01-03', '2011-01-05', 'John Doe');

--will give a WITH CHECK OPTION clause violation on the second INSERT INTO statement, as we wanted.

Both rows insert without an error.

Tom Wickerath Valued Member Points: 53 More actions · Answer 2

A friend of mine, Microsoft Access MVP Ben Clothier, provided a fix. Change the following line of code from "less than" to "less than or equal to":

From:  and  H1.arrival_date < H2.departure_date)
to:    and  H1.arrival_date <= H2.departure_date)

In addition, one can use the DateTime data type, instead of Date, to create a more realistic example, where a guest usually checks out by 11:00 AM, and a new guest can check into the same room later in the afternoon. With a Date only datatype, this is not accommodated. Here are my modifications to the sample, which include "or alter" (introduced with SQL Server 2016) for the create view statement:

create table Hotel
  (room_nbr integer not null,
   arrival_date datetime not null,
   departure_date datetime not null,
   guest_name char(30) not null,
   constraint schedule_right 
  check (arrival_date <= departure_date)
  );
go

create or alter view Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name)
  as
     select H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name
     from   Hotel as H1
     where not exists
        (select *
         from   Hotel as H2
         where  H1.room_nbr = H2.room_nbr
           and  H2.arrival_date < H1.arrival_date
           and  H1.arrival_date <= H2.departure_date)
           and  H1.arrival_date <= H1.departure_date
         with check option;
go

insert into Valid_Hotel_Stays values (1, '2011-01-01 15:00', '2011-01-03 11:00', 'Ron Coe');
insert into Valid_Hotel_Stays values (1, '2011-01-03 15:00', '2011-01-05 11:00', 'John Doe');

insert into Valid_Hotel_Stays values (5, '2019-12-19 15:00', '2019-12-25 11:00', 'Bilbo Baggins');
insert into Valid_Hotel_Stays values (5, '2019-12-25 15:00', '2019-12-31 15:00', 'BoobooBear');

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

Just be mindful of the possible surprise such a change could cause if the departure data has a midnight time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tom Wickerath Valued Member Points: 53 More actions · Answer 4

Hi Jeff,

Care to provide an example INSERT query or two that demonstrates the possible surprise you mention--I assume in this case the possibility of double booking a room?

Thanks

This reply was modified 4 years, 10 months ago by Tom Wickerath.

Tom Wickerath Valued Member Points: 53 More actions · Answer 5

Additional information provided by my friend, Microsoft Access MVP Ben Clothier, via a private email (after I thanked him):

"You're welcome! To be honest it did puzzle me for a bit; though it seemed a simple change, it took me a while to remember that it was basically a double negation (because we are doing a NOT EXISTS + invalid condition, rather than NOT EXISTS + valid conditions).
It's probably the first example I've found where CHECK OPTION would actually have a use. I knew about it for long time but never actually used it in production. Futzed with it but found that it created more problems than it solved so just left it off."

This reply was modified 4 years, 10 months ago by Tom Wickerath.