To work on their projects, each student group (who sit in numbered tables, as described in HW1) can

Question

also (in addition to working at those numbered tables) reserve one of ten available rooms to work on their project.

The rooms can be reserved for a block of a few hours (eg. 3 hours), with a start time and end time, eg. 3pm-6pm.

9am-2pm etc. At the end of the day, everyone goes home, so there's no possibility of rooms being booked for

multiple days. The table structure you are asked to use, is this [you might need to change the syntax slightly, to make

it work on your specific platform - same for other questions that follow]:

CREATE TABLE ProjectRoomBookings

(roomNum INTEGER NOT NULL,

startTime INTEGER NOT NULL,

endTime INTEGER NOT NULL,

groupName CHAR(10) NOT NULL,

PRIMARY KEY (roomNum, startTime));

There are two issues with the above. First, the start time could be incorrectly entered to be later than the end time.

Second, a new entry (for a new group) could be accidentally put in to occupy a room, even before the existing group

in that room is done using that room. For simplicity, you can express times in the 24h military-style format, eg. 9 for

9AM, 17 for 5PM, etc. For further simplicity, all bookings start and end 'on the hour', so, ints between 7 (7AM) and

18 (6PM) should be sufficient.

How would you redesign the table to fix both these issues? For your answer, you can either provide a textual

explanation, and/or provide SQL statements. Hint - "do not be concerned with efficiency" - ANY working solution is

acceptable :) Another hint - no need to learn new techniques/syntax.