Question

Relational Model

Motel(mID, mName, city)

Guest(gID, gName, address)

Room(mID, roomNum, price)

Books(gID, mID, roomNum, startDate, endDate)

Using sqlite3, write the following query (remember there is no direct way to express division in SQL)

Select guests who have booked a room in every motel

Answer 1

In order to get the results of the query we are going to use joins on the related table.Listed below the query used to create,insert rows in the tables and the final query to obtain the results.In addition to that have listed a step by step procedure followed below

Step by Step procedure followed

1. Create the table named "Motel" with columns (mID, mName, city) with mID column as the primary key
2. Insert the rows into the table.We insert 10 Motels into the table with values (Motel1,Motel2,Motel3....Motel10)
3. Create the table named "Guest" with columns (gID, gName, address) and gID column as primary key
4. Insert the rows into the Guest table.We insert 10 Guests into the table with values (Guest1,Guest2,Guest3....Guest10)
5. Create the table named Room with columns (mID, roomNum, price) with roomNum column as the Primary key and mID column as the foreign key referencing the primary mID column in the Motel table.
6. Insert rows into the Room table.We insert 3 rooms each per Motel.
7. Create the Books table with colum (gID, mID, roomNum, startDate, endDate) with the foreign keys gID,mID,roomNum referencing the primary column gID in the Guest table,mID in the Motel table and roomNum in Room table respectively
8. Insert rows into the table such that Guest1,Guest2,Guest3 have booked rooms in every motel and the remaining Guests book rooms in only one motel.
9. Use the sample query provided to retrieve the required results.Listing guests who have booked rooms in every motel

SQLLite query used to create and insert rows in "Motel" table

CREATE TABLE Motel(
   mID INTEGER NOT NULL  PRIMARY KEY,
   mName char(50) NOT NULL,
   mCity CHAR(50)   
);

INSERT INTO Motel (mID,mName,mcity)
VALUES( 1,'Motel1','City1'),
( 2,'Motel2','City2'),
( 3,'Motel3','City3'),
( 4,'Motel4','City4'),
( 5,'Motel5','City5'),
( 6,'Motel6','City6'),
( 7,'Motel7','City7'),
( 8,'Motel8','City8'),
( 9,'Motel9','City9'),
( 10,'Motel10','City10')

SQLLite used to create and insert rows in "Guest" table

CREATE TABLE Guest(
        gID INTEGER NOT NULL PRIMARY KEY,
        gName CHAR(50) NOT NULL,
        address TEXT NOT NULL
);

INSERT INTO Guest(gID,gName,address)
VALUES( 1,'Guest1','Address1'),
( 2,'Guest2','Address2'),
( 3,'Guest3','Address3'),
( 4,'Guest4','Address4'),
( 5,'Guest5','Address5'),
( 6,'Guest6','Address6'),
( 7,'Guest7','Address7'),
( 8,'Guest8','Address8'),
( 9,'Guest9','Address9'),
( 10,'Guest10','Address10')

SQLLite used to create and insert rows in "Room " table

CREATE TABLE Room (
    roomNum   INTEGER PRIMARY KEY,
    price REAL, 
    mID      INTEGER NOT NULL,
    FOREIGN KEY (mID)
    REFERENCES Motel (mID)
       ON UPDATE SET NULL
       ON DELETE SET NULL
);


INSERT INTO Room(mID,roomNum,price)
VALUES(1,1,10.1),
(1,2,20.2),
(1,3,30.3),
(2,4,10.1),
(2,5,20.2),
(2,6,30.3),
(3,7,10.1),
(3,8,20.2),
(3,9,30.3),
(4,10,10.1),
(4,11,20.2),
(4,12,30.3),
(5,13,10.1),
(5,14,20.2),
(5,15,30.3),
(6,16,10.1),
(6,17,20.2),
(6,18,30.3),
(7,19,10.1),
(7,20,20.2),
(7,21,30.3),
(8,22,10.1),
(8,23,20.2),
(8,24,30.3),
(9,25,10.1),
(9,26,20.2),
(9,27,30.3),
(10,28,10.1),
(10,29,20.2),
(10,30,30.3)

SQLLite used to create and insert rows in "Book" table

CREATE TABLE Books (
    mID INTEGER NOT NULL,
    gID   INTEGER NOT NULL,
    roomNum INTEGER NOT NULL,
    startDate TEXT,
    endDate TEXT,
    FOREIGN KEY (mID)
    REFERENCES Motel (mID)
       ON UPDATE SET NULL
       ON DELETE SET NULL,
    FOREIGN KEY (gID)
    REFERENCES Guest (gID)
       ON UPDATE SET NULL
       ON DELETE SET NULL,
    FOREIGN KEY (roomNum)
    REFERENCES Room(roomNum)
       ON UPDATE SET NULL
       ON DELETE SET NULL
)

INSERT INTO Books(gID, mID, roomNum, startDate, endDate)
VALUES(1,1,1,'01-01-2020','01-02-2020'),
(1,2,4,'01-02-2020','01-03-2020'),
(1,3,7,'01-03-2020','01-04-2020'),
(1,4,10,'01-04-2020','01-05-2020'),
(1,5,13,'01-05-2020','01-06-2020'),
(1,6,16,'01-06-2020','01-07-2020'),
(1,7,19,'01-07-2020','01-08-2020'),
(1,8,22,'01-08-2020','01-09-2020'),
(1,9,25,'01-09-2020','01-10-2020'),
(1,10,28,'01-10-2020','01-11-2020'),
(2,1,2,'01-01-2020','01-02-2020'),
(2,2,5,'01-02-2020','01-03-2020'),
(2,3,8,'01-03-2020','01-04-2020'),
(2,4,11,'01-04-2020','01-05-2020'),
(2,5,14,'01-05-2020','01-06-2020'),
(2,6,17,'01-06-2020','01-07-2020'),
(2,7,20,'01-07-2020','01-08-2020'),
(2,8,23,'01-08-2020','01-09-2020'),
(2,9,26,'01-09-2020','01-10-2020'),
(2,10,29,'01-10-2020','01-11-2020'),
(3,1,3,'01-01-2020','01-02-2020'),
(3,2,6,'01-02-2020','01-03-2020'),
(3,3,9,'01-03-2020','01-04-2020'),
(3,4,12,'01-04-2020','01-05-2020'),
(3,5,15,'01-05-2020','01-06-2020'),
(3,6,18,'01-06-2020','01-07-2020'),
(3,7,21,'01-07-2020','01-08-2020'),
(3,8,24,'01-08-2020','01-09-2020'),
(3,9,27,'01-09-2020','01-10-2020'),
(3,10,30,'01-10-2020','01-11-2020'),
(4,1,3,'01-01-2020','01-02-2020'),
(5,2,6,'01-02-2020','01-03-2020'),
(6,3,9,'01-03-2020','01-04-2020'),
(7,4,12,'01-04-2020','01-05-2020'),
(8,5,15,'01-05-2020','01-06-2020'),
(9,6,18,'01-06-2020','01-07-2020'),
(10,7,21,'01-07-2020','01-08-2020')

Final SQLLite to retrieve the results

select Guest.gID,Guest.gName,Room.roomNum,Motel.mName
from Motel 
INNER JOIN Room,Books,Guest ON 
Motel.mID = Room.mID AND 
Motel.mID = Books.mID AND
Guest.gID = Books.gID AND 
Books.roomNum = Room.roomNum
GROUP BY Room.roomNum
ORDER BY Guest.gid