Oracle B. Run the following SQL: CREATE TABLE TestTable ( Column1 VARCHAR(20) );

Question

Oracle

B. Run the following SQL:

CREATE TABLE TestTable (

Column1 VARCHAR(20)

);

INSERT INTO TestTable VALUES ('Hello');

Submit a single screen shot that shows your open connection, and the output from the SQL. Use Print Screen. Do not take a picture of the computer monitor. I used Alt-Ctrl-PrtSc on Windows to take a snapshot of only the window. Example:

Part 2. Relational Model (32 points)

Answer all four questions given the following database schema and relational database instance containing three relations:

A. List the primary keys for each table.

B. List the foreign keys for each table.

C. For each table, construct a tuple that does not violate any constraints.

D. Which records can be removed from ITEM without violating any constraints?

Part 3. SQL DDL (48 points)

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 give me 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', '2019-09-20', 'SUCCESS'), ('TJ0845342', 'H021', '2019-10-23', '2019-10-28', 'PENDING'), ('TJ0887342', 'H022', '2019-12-10', '2019-12-15', 'PENDING');

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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