As a hotel clerk how can I create table using basic SQL commands
Create table Hotel (rommno smallint not null, arrival date not null,
departure date not null, guest char (30), Primary Key (roomno,
arrival),
Check (departure >= arrival)).
Check() clause enforces the data integrity constraint. But I cannot
leave before I arrived.  How do I enforce the rule that I cannot add a
reservation that has given an arrival date conflicting with the prior
departure date for a given room?

---

Request for Question Clarification by willie-ga on 26 Oct 2002 10:47 PDT

Hi again

Do you need to do this with just the one table or are you allowed to
create other tables to suppport the constraint?

Willie

Hello again

I can see several ways of doing this without actually resorting to an
extra table.

Option 1
_______

It can be done by creating a trigger on the table, and using an
integrity check in the trigger that checks the two rows against each
other .

Also note that for clarity it is useful to add another field to the
table, res# , a reservation number that is unique to each reservation.
This stops the trigger checking a record against itself

Step 1: Create your table

CREATE TABLE hotel
(res# int AUTO_INCREMENT, 
roomno SMALLINT NOT NULL, 
arrival DATETIME NOT NULL,
departure DATETIME NOT NULL,
guest char(30),
PRIMARY KEY (roomno,arrival )
);

Step 2: Create your trigger

CREATE TRIGGER no_overlap
ON hotel
FOR INSERT,UPDATE
AS BEGIN
IF EXISTS (SELECT *
FROM hotel y, hotel x
WHERE y.roomno = x.roomno
AND y.departure > x.arrival
AND y.arrival < x.departure
AND y.res# <> x.res#))
ROLLBACK
END 
;

Step 3: Try some inserts and updates. When you try to create an
overlapped reservation, you’ll get a message telling you that you
cant.

You’ll find a full, fairly technical, explanation at 
NULLS, PROCEDURALITY AND 'NO-OVERLAP' CONSTRAINTS IN SQL 
( http://www.pgro.uk7.net/fp2a.htm )



Option 2
_______


It is also possible to do it with a User Defined Function, and you’ll
find an explanation and example of that at
Function in check constraint (and in Default definition) 
http://www.sqlnet.cz/sql_server/tsql/udf_in_check.htm


Option 3
_______

You can create two more fields on your table, check-in date and
check-out date, which are allowed to be NULL and are completed at
actual check-in and check-out eg

CREATE TABLE hotel
(res# int AUTO_INCREMENT, 
roomno SMALLINT NOT NULL, 
arrival DATETIME NOT NULL,
departure DATETIME NOT NULL,
CHECKIN  DATE,
CHECKOUT DATE,
guest char(30),
PRIMARY KEY (roomno,arrival )
CHECK (ARRIVE < DEPART AND CHECKIN <= CHECKOUT)
);

You can see an Example and explanation of this option at the bottom of
"Creating Tables"
(http://developer.mimer.se/documentation/html_82/Mimer_SQL_User_Manual/Defining_Database4.html
)

Willie-ga

---

Request for Answer Clarification by fahd-ga on 30 Oct 2002 11:16 PST

Thank you for all your help in providing me with the information and
options
I am using MS Access '97 version, I would appreciated very much if you
could give me some directions and steps to follow to apply the formula
using SQL and to be able to test the Result.

Truly,

f-ga

---

Clarification of Answer by willie-ga on 30 Oct 2002 13:17 PST

Hi there

Unfortunately I do not have access to MS Access 97, so I can't help
you there, but the CREATE TABLE commands should work in any SQL
environment. Not sure about CREATE TRIGGER, but the third option I
gave in the answer should definitely work.

willie-ga