Review Questions
14.1 Discuss the advantages and disadvantages of views.
See Section 14.1.7.
14.2 Describe how the process of view resolution works.
Described in Section 14.1.3.
14.3 What restrictions are necessary to ensure that a view is updatable?
ISO standard specifies the views that must be updatable in a system that conforms to the standard. Definition given in SQL-92 is that a view is updatable if and only if:
• DISTINCT is not specified; that is, duplicate rows must not be eliminated from the query results.
• Every element in the SELECT list of the defining query is a column name (rather than a constant, expression, or aggregate function) and no column appears more than once.
• The FROM clause specifies only one table; that is, the view must have a single source table for which the user has the required privileges. If the source table is itself a view, then that view must satisfy these conditions. This, therefore, excludes any views based on a join, union (UNION), intersection (INTERSECT), or difference (EXCEPT).
• The WHERE clause does not include any nested SELECTs that reference the table in the FROM clause.
• There is no GROUP BY or HAVING clause in the defining query.
In addition, every row that is added through the view must not violate the integrity constraints of the base table (Section 14.1.5).
14.4 Discuss the functionality and importance of the Integrity Enhancement Feature (IEF).
Required data: NOT NULL of CREATE/ALTER TABLE.
Domain constraint:CHECK clause of CREATE/ALTER TABLE and CREATE DOMAIN.
Entity integrity: PRIMARY KEY (and UNIQUE) clause of CREATE/ALTER TABLE.
Referential integrity: FOREIGN KEY clause of CREATE/ALTER TABLE.
Enterprise constraints: CHECK and UNIQUE clauses of CREATE/ALTER TABLE and (CREATE) ASSERTION.
14.5 Discuss how the Access Control mechanism of SQL works.
Each user has an authorization identifier (allocated by DBA).
Each object has an owner. Initially, only owner has access to an object but the owner can pass privileges to carry out certain actions on to other users via the GRANT statement and take away given privileges using REVOKE.
14.6 Discuss the difference between interactive SQL, static embedded SQL, and dynamic embedded
SQL.
Interactive SQL: SQL statements usually input interactively from a terminal.
Static and dynamic embedded SQL refers to the embedding of SQL statements in a high-level programming language such as C. Embedded SQL statements are changed by a preprocessor provided with the DBMS vendor into functions calls.
The basic difference between static and dynamic embedded SQL is that static SQL does not allow host variables to be used in place of table names or column names.
40 Exercises
Answer the following questions using the relational schema from the Exercises of Chapter 13.
14.7 Create a view containing the hotel name and the names of the guests staying at the hotel.
CREATE VIEW hotel_data(hotel_name, guest_name) AS SELECT h.name, g.name
FROM hotel h, guest g, booking b
WHERE h.hotel_no = b.hotel_no AND g.guest_no = b.guest_no AND b.date_from <= CURRENT_DATE AND
b.date_to >= CURRENT_DATE;
14.8 Create a view containing the account for each guest at the Grosvenor Hotel.
CREATE VIEW booking_out_today
AS SELECT g.guest_no, g.name, g.address, r.price*(b.date_to - b.date_from) FROM guest g, booking b, hotel h, room r
WHERE g.guest_no = b.guest_no AND r.room_no = b.room_no AND b.hotel_no = h.hotel_no AND h.name = 'Grosvenor Hotel' AND b.date_to = CURRENT_DATE;
14.9 Give the users Manager and Deputy full access to these views, with the privilege to pass the
access on to other users.
GRANT ALL PRIVILEGES ON hotel_data TO manager, deputy WITH GRANT OPTION; GRANT ALL PRIVILEGES ON booking_out_today TO manager, deputy WITH GRANT OPTION;
14.10 Give the user Accounts SELECT access to these views. Now revoke the access from this user.
GRANT SELECT ON hotel_data TO accounts;
GRANT SELECT ON booking_out_today TO accounts; REVOKE SELECT ON hotel_data FROM accounts;
REVOKE SELECT ON booking_out_today FROM accounts;
14.11 Create the Hotel table using the Integrity Enhancement Features of SQL.
CREATE DOMAIN HOTEL_NUMBER AS CHAR(4); CREATE TABLE hotel(
hotel_no HOTEL_NUMBER NOT NULL,
name VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
PRIMARY KEY (hotel_no));
14.12 Now create the Room, Booking, and Guest tables using the Integrity Enhancement Features of SQL with the following constraints:
(a) Type must be one of Single, Double, or Family.
(b) Price must be between £10 and £100.
(c) Rid must be between 1 and 100.
(d) Date_From and Date_To must be greater than today’s date.
(e) The same room cannot be double booked.
CREATE DOMAIN ROOM_TYPE AS CHAR(1) CHECK(VALUE IN ('S', 'F', 'D'));
CREATE DOMAIN HOTEL_NUMBERS AS HOTEL_NUMBER CHECK(VALUE IN (SELECT hotel_no FROM hotel)); CREATE DOMAIN ROOM_PRICE AS DECIMAL(5,2)
CHECK(VALUE BETWEEN 10 AND 100); CREATE DOMAIN ROOM_NUMBER AS VARCHAR(4)
CHECK(VALUE BETWEEN '1' AND '100'); CREATE TABLE room(
room_no ROOM_NUMBER NOT NULL,
hotel_no HOTEL_NUMBERS NOT NULL,
type ROOM_TYPE NOT NULL DEFAULT 'S'
price ROOM_PRICE NOT NULL,
PRIMARY KEY (room_no, hotel_no),
FOREIGN KEY (hotel_no) REFERENCES hotel
ON DELETE CASCADE ON UPDATE CASCADE); CREATE DOMAIN GUEST_NUMBER AS CHAR(4);
CREATE TABLE guest(
guest_no GUEST_NUMBER NOT NULL,
name VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL);
CREATE DOMAIN GUEST_NUMBERS AS GUEST_NUMBER CHECK(VALUE IN (SELECT guest_no FROM guest)); CREATE DOMAIN BOOKING_DATE AS DATETIME
CHECK(VALUE > CURRENT_DATE); CREATE TABLE booking(
hotel_no HOTEL_NUMBERS NOT NULL,
guest_no GUEST_NUMBERS NOT NULL,
date_from BOOKING_DATE NOT NULL,
date_to BOOKING_DATE NULL,
room_no ROOM_NUMBER NOT NULL,
PRIMARY KEY (hotel_no, guest_no, date_from), FOREIGN KEY (hotel_no) REFERENCES hotel
ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (guest_no) REFERENCES guest
ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (hotel_no, room_no) REFERENCES room
ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT room_booked
CHECK (NOT EXISTS (SELECT *
FROM booking b
WHERE b.date_to > booking.date_from AND b.date_from < booking.date_to AND
AND b.room_no = booking.room_no AND b.hotel_no = booking.hotel_no)),
CONSTRAINT guest_booked CHECK (NOT EXISTS (SELECT *
FROM booking b
WHERE b.date_to > booking.date_from AND b.date_from < booking.date_to AND
42
14.13 Investigate the embedded SQL functionality of any DBMS that you use. Determine the compliance of the DBMS with the ISO standard. Investigate the functionality of any extensions the DBMS supports. Are there any functions not supported?
This is a small student project, the result of which is dependent on the system analyzed.
14.14 Write a small program that prompts the user for guest details and inserts the record into the guest table.
#include <stdio.h> #include <stdlib.h>
EXEC SQL INCLUDE sqlca; main()
{
EXEC SQL BEGIN DECLARE SECTION;
char guest_no[5]; /* input guest number */
char name[21]; /* input guest name */
char address[51]; /* input address */
EXEC SQL END DECLARE SECTION; /* Connect to database */
EXEC SQL CONNECT 'hoteldb'; if (sqlca.sqlcode < 0) exit (-1); /* Prompt for data */
printf("Enter guest number: "); scanf("%s", guest_no); printf("Enter guest name: "); scanf("%s", name);
printf("Enter address: "); scanf("%s", address);
EXEC SQL INSERT INTO guest
VALUES :guest_no, :name, :address; /* Check success */
if (sqlca.sqlcode >= 0)
printf("Insert successful\n"); else
printf("Insert unsuccessful\n"); /* Finally, disconnect from the database */ EXEC SQL DISCONNECT;
14.15 Write a small program that prompts the user for booking details, checks that the specified hotel, guest and room exists and inserts the record into the booking table.
#include <stdio.h> #include <stdlib.h>
EXEC SQL INCLUDE sqlca; main()
{
EXEC SQL BEGIN DECLARE SECTION;
char hotel_no[5]; /* input hotel number */
char guest_no[5]; /* input guest number */
char date_from[26]; /* input from date */
char date_to[26]; /* input to date */
char room_no[5]; /* input room number */
char hname[21]; /* return hotel name */
char gname[21]; /* return guest name */
char type[2]; /* return room type */
EXEC SQL END DECLARE SECTION; /* Connect to database */
EXEC SQL CONNECT 'hoteldb'; if (sqlca.sqlcode < 0) exit (-1); /* Prompt for data */
printf("Enter hotel number: "); scanf("%s", hotel_no);
EXEC SQL SELECT name INTO :hname FROM hotel WHERE hotel_no = :hotel_no; if (sqlca.sqlcode < 0) {
printf("Hotel %s does not exist ... exiting\n", hotel_no); exit(-1);
}
printf("Enter guest number: "); scanf("%s", guest_no);
EXEC SQL SELECT name INTO :gname FROM guest WHERE guest_no = :guest_no; if (sqlca.sqlcode < 0) {
printf("Guest %s does not exist ... exiting\n", guest_no); exit(-1);
}
printf("Enter room number: "); scanf("%s", room_no);
EXEC SQL SELECT type INTO :type FROM room WHERE room_no = :room_no; if (sqlca.sqlcode < 0) {
printf("Room %s does not exist ... exiting\n", room_no); exit(-1);
}
printf("Enter from date: "); scanf("%s", date_from); printf("Enter to date: "); scanf("%s", date_to);
EXEC SQL INSERT INTO booking
VALUES :hotel_no, :guest_no, :date_from, :date_to, :room_no; /* Check success */
if (sqlca.sqlcode >= 0)
printf("Insert successful\n"); else
printf("Insert unsuccessful\n"); /* Finally, disconnect from the database */ EXEC SQL DISCONNECT;
}
14.16 Write a program that increases the price of every room by 5%.
#include <stdio.h> #include <stdlib.h>
44
main() {
/* Connect to database */ EXEC SQL CONNECT 'hoteldb'; if (sqlca.sqlcode < 0) exit(-1);
/* Display message for user and update the table */ printf("Updating ROOM table\n");
EXEC SQL UPDATE room SET price = price*0.05;
if (sqlca.sqlcode >= 0) /* Check success */
printf("Update successful\n"); else
printf("Update unsuccessful\n"); /* Commit the transaction */
EXEC SQL COMMIT;
/* Finally, disconnect from the database */ EXEC SQL DISCONNECT;
}
14.17 See overleaf.
14.18 Write a program that allows the user to insert data into any user-specified table.
Several ways to write this program. For example:
a) Could use static SQL and after user enters table name, have a switch that branches to the appropriate piece of code to prompt for the relevant data and perform an INSERT statement. This solution is not very elegant.
b) Use the program given in Example 14.17 of the book and type in the relevant INSERT statement.
14.17 Write a program that calculates the account for every guest checking out of the Grosvenor Hotel today.
#include <stdio.h> #include <stdlib.h>
EXEC SQL INCLUDE sqlca; main()
{
EXEC SQL BEGIN DECLARE SECTION;
char guest_no[5]; /* guest number */
char gname[21]; /* guest name */
char address[51]; /* guest address */
double balance; /* guest address */
EXEC SQL END DECLARE SECTION; /* Connect to database */
EXEC SQL CONNECT 'hoteldb'; if (sqlca.sqlcode < 0) exit (-1); /* Establish SQL error handling */
EXEC SQL WHENEVER SQLERROR GOTO error; EXEC SQL WHENEVER NOT FOUND GOTO done; /* Declare cursor for selection */
EXEC SQL DECLARE booking_out_cursor CURSOR FOR
SELECT g.guest_no, g.name, g.address, r.price*(b.date_to - b.date_from) FROM guest g, booking b, hotel h, room r
WHERE g.guest_no = b.guest_no AND r.room_no = b.room_no AND b.hotel_no = h.hotel_no AND h.name = 'Grosvenor Hotel' AND b.date_to = CURRENT_DATE
ORDER by g,name; /* Open the cursor to start of selection */ EXEC SQL OPEN booking_out_cursor;
printf("Guest Number\t Guest Name\t Guest Address\t Balance\n\n"); /* Loop to fetch each row of the result table */
for ( ; ; ) {
/* Fetch next row of the result table */ EXEC SQL FETCH booking_out_cursor
INTO :guest_no, :gname, :address, :balance; /* Display data */
printf("%s\t%s\t%s\t%f\n", guest_no, gname, address, balance); }
/* Error condition - print out error */ error:
printf("SQL error %d\n"); done:
/* Close the cursor before completing */
EXEC SQL WHENEVER SQLERROR continue; EXEC SQL CLOSE booking_out_cursor; EXEC SQL DISCONNECT;
46