Write a script to do the following. (Do this one part at a time, testing the par

Question

Write a script to do the following. (Do this one part at a time, testing the partial results by inspecting the tables in SQLDeveloper, and not going on the next part until you have the previous part working.)

A. Define the following database, representing hotels, travelers who may stay in the hotels, and reservations that the travelers make at hotels.

    TRAVELER(ID, FirstName, LastName, Phone)

HOTEL(ID, HotelName, City)

RESERVATION(TravelerID, HotelID, StartDate, EndDate, Confirmation)

Traveler IDs should be exactly nine characters long, hotel IDs should be exactly four characters long, and phone numbers should be exactly ten characters long; reservation confirmation codes should be at most ten characters long. You may decide on appropriate maximum lengths for names of customers, hotels, and cities.

Define the primary keys as indicated in the schemas above. Also define TravelerID in RESERVATION to be a foreign key referencing ID in TRAVELER, and HotelID in RESERVATION to be a foreign key referencing ID in HOTEL. (Thus you will have to create TRAVELER and HOTEL before RESERVATION.) We can’t assume that the confirmation codes are unique, since they are supplied by the hotels when reservations are made we can’t be sure that different hotels won’t use the same codes. (All of the confirmation codes supplied by a single hotel will be distinct, but verifying that would require a more complicated constraint than we can implement right now…)

In order to avoid conflicts, start your script file with DROP TABLE commands for all three tables (since RESERVATION contains foreign keys, you will have to drop it first). Run your script and look at the columns and constraints of each table to verify that they have been created correctly.

B. Populate the HOTEL table with names and cities of at least four actual hotels anywhere in the world (you can make up the IDs, as they are internal to the database). Then populate the TRAVELER table with information for yourself and at least two of your friends. (For privacy reasons, IDs and phone numbers should be made up.) Look at the data in each table to verify that they have been populated correctly.

C. Next, insert at least five records into the RESERVATION table. There should be at least one record for each traveler, and at least one traveler should have two or more records in the RESERVATION table. Be sure that each traveler is staying at only one hotel at a time – that is, a traveler’s reservation at a second hotel can’t begin before their previous reservation at the first hotel ends. (This particular constraint – insuring that reservations do not overlap – is also too complicated for us to implement right now.) Every record in RESERVATION must have a starting date, but the ending date can be NULL for the traveler’s last reservation in the table. Look at the data in the table to verify that it has been populated correctly.

Explanation / Answer

If you have any doubts, please giv eme comment...

CREATE TABLE TRAVELER(

ID CHAR(9) NOT NULL PRIMARY KEY,

FirstName VARCHAR(100),

LastName VARCHAR(100),

Phone CHAR(10)

);

CREATE TABLE HOTEL(

ID CHAR(4) NOT NULL PRIMARY KEY,

HotelName VARCHAR(100),

City VARCHAR(100)

);

CREATE TABLE RESERVATION(

TravelerID CHAR(9),

HotelID CHAR(4),

StartDate DATE,

EndDate DATE,

Confirmation VARCHAR(10),

PRIMARY KEY(TravelerID, HotelID, StartDate, EndDate, Confirmation),

FOREIGN KEY(TravelerID) REFERENCES TRAVELER(ID),

FOREIGN KEY(HotelID) REFERENCES Hotel(ID)

);

INSERT INTO TRAVELER VALUES('TJ0887342', 'ANU', 'NAGA', '1237648736'), ('TJ0845342', 'NAGA', 'RAJU', '1847648736');

INSERT INTO HOTEL VALUES('H018', 'AVASA', 'Hyderabad'),('H014', 'Kokila', 'Kakinada'), ('H021', 'Haveli', 'Rajamahndry'), ('H022', 'Vijaya', 'Guntur');

INSERT INTO RESERVATIONS VALUES ('TJ0887342', 'H018', '2019-09-14', '2019-09-20', 'SUCCESS'), ('TJ0887342', 'H014', '2019-10-23', '2019-10-28', 'PENDING'), ('TJ0845342', 'H018', '2019-09-14', NULL, 'SUCCESS'), ('TJ0845342', 'H021', '2019-10-23', '2019-10-28', 'PENDING'), ('TJ0887342', 'H022', '2019-12-10', NULL, 'PENDING');

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.