The following relations (based on chapter 4, DS textbook) form part of a relational database schema:

Hotel (hotelNo, hotelName, hotel_address, city, province, country)

Room (roomID, roomNo, hotelNo, type, price)

Booking (BookingID, guestNo, dateFrom, dateTo, roomID)

Guest (guestNo, guestName, guestAddress, comments)

where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and roomID is the primary key; Room type specifies single, double or family. Booking contains details of the bookings and BookingID is the primary key; and the Guest table contains guest details and guestNo is the primary key. Comments are a string of characters and digits stored a variable character type with maximum 128 characters.

Preliminary task:

Create required tables (use prefix A2_ for their names) and add data. You can use CASE tool or SQLDeveloper.

Problem: The database designer has forgotten to create an attribute for the phone numbers. The hotel staff was using the comments (attribute) to enter textual comments and also phone numbers. Ooops.. The hotel manager is very upset and your task is to provide a temporarily solution. The manager wants to have a list of the guests (guestName, and possible phone number extracted from the comments. This has to be done within one hour (before the manager gets back from lunch).

Hints for solution:

Create an SQL statement to list the guests (guest names) and the parts of the comments matching phone patterns. The examples of phone numbers: (250) 333 9999, 250-371-5592, 371-5592 250.333.4444. Good news: Oracle supports Regular Expressions (!). Idea: Use the regular expressions to find potential phone numbers in comments