PLEASE HELP ME ANSWER ALL OF MY ITM-315(DATABASE SYSTEMS) questions. PLEASE USE

Question

PLEASE HELP ME ANSWER ALL OF MY ITM-315(DATABASE SYSTEMS) questions. PLEASE USE Microsoft Access to answer the questions.

For some queries, the save button may not appear after running the query. Use file>save where necessary to save the queries.

Create and save the following queries (save as query 1 through query 14) in the provided database: hotel spring 18 ch 10. Rename the database as hotel spring 18 ch 10lastname or hotel spring 18 ch10lastnameandlastname and submit via Blackboard by the due date/time.

Query1: Add an Amenity table to the database with the following columns: hotel_id and amenity_name (assume amenity names are unique). hotel_id should be designated as a foreign key referencing the hotel table. Hint: use integer as the data type for hotelno. Make sure to designate the primary key-which is the combination of hotel_ID and Amenity name.

Query 2: Add a column to the appropriate table to record the state the hotel is in.

query 3: change the name of the Holiday Inn in Tulsa to Holiday Inn Deluxe

query 4: delete room 123 in hotel 6 from the database

query 5: Add the following amenity to the database: hotel 6 has a swimming pool.

Query 6: Create a query that shows all of the guests and the bookings of those rooms. The result should include all guests, even those guests that have not booked any rooms. Show the guestNo, guestFirst, guestLast, HotelNo aand roomNo in the result.

HOTEL SPRING DATABASE

All Access Obje...og employee guest l hotelE room guestNo dateFrom dateTo roomNo ratebooked Click to Add Tables 8/3/2016 2/1/2015 8/4/2016 2/6/2015 13/13/2016 3/15/2016 8/9/2014 8/10/2014 2/8/2015 7/5/2016 3/1/2015 2/4/2015 6/4/2014 3/7/2015 23/10/2015 3/15/2015 2/6/2015 2/4/2014 9/8/2015 9/15/2015 3/9/2016 3/14/2016 2/2/2015 015 3/6/2016 3/26/2016 3/31/2016 13 5/12/2014 5/13/2014 2/8/2015 11 5/11/2014 5/12/2014 8 4/15/20164/18/2016 4/4/2015 4/4/2016 4 3/11/2016 3/15/2016 2/6/2015 2/7/2015 2/6/20152/10/2015 9 4/15/20164/18/2016 3 3/22/2016 3/25/2016 10 2/22/2015 2/24/2015 10 3/12/20143/13/2014 401 72 booking 101 E employee E guest 2/3/2015 7/3/2016 1 2/28/2015 2/3/2015 11 5/31/2014 3/5/2015 hotel room 101 212 91 100 100 100 100 100 110 110 2 2/4/2015 3 1/31/2014 401 2/1/2015 25/18/2015 5/23/2 2 3/4/2016 210 421 130 2/5/2015 135 4/1/2015 4/1/2016 160 160 160 212 214 2/5/2015 2/1/2015 412 411 321 410 312 312 4 190

Explanation / Answer

QUERY 1:

CREATE TABLE AMENITY (

HOTEL_ID INTEGER,

AMENITY_NAME CHAR (50) UNIQUE,

PRIMARY KEY (HOTEL_ID, AMENITY_NAME),

FOREIGN KEY (HOTEL_ID) REFERENCES HOTEL (HOTELNO)

);

QUERY 2:

ALTER TABLE HOTEL
ADD STATE CHAR (20);

QUERY 3:

UPDATE HOTEL
SET hotelName = 'Holiday Inn Delux'
WHERE hotelName = 'Holiday Inn’ AND hotelCity = ‘Tulsa’;

QUERY 4:

DELETE FROM ROOM

WHERE roomNo = 123 AND hotelNo = 6;

QUERY 5:

INSERT INTO AMENITY VALUES (6 , ‘Swimming Pool’);

QUERY 6:

SELECT guest.guestNo, guest.guestFirst, guest.guestLast, booking.hotelNo, booking.roomNo

FROM guest LEFT JOIN booking ON guest.guestNo = booking.guestNo;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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