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.