ANSWERS TO ODD-NUMBERED REVIEW QUESTIONS - Concepts of Database Management - page 359

11. To qualify the name of a field in an SQL query, precede the field with the name of the table to which
it belongs, followed by a period. It is necessary to qualify a field if the field name occurs in more
than one of the tables listed in the FROM clause.
13. The update commands in SQL are INSERT, which inserts new rows in a table; UPDATE, which
changes all the rows in a table that satisfy some condition; and DELETE, which deletes all the rows
in a table that satisfy some condition.

359

CHAPTER 4—THE RELATIONAL MODEL 3:
ADVANCED TOPICS

1. A view is an individual user's picture of the database. It is defined using a defining query. The data
in the view never actually exists in the form described in the view. Rather, when a user accesses
the view, his or her query is merged with the defining query of the view to form a query that per-
tains to the whole database.
3. a.
CREATE VIEW PartOrder AS
SELECT Part.PartNum, Description, Price, OrderNum, OrderDate,
NumOrdered, QuotedPrice
FROM Part, OrderLine, Orders
WHERE Part.PartNum = OrderLine.PartNum
AND Orders.OrderNum = OrderLine.OrderNum;
b.
SELECT PartNum, Description, OrderNum, QuotedPrice
FROM PartOrder
WHERE QuotedPrice > 100;
c.
SELECT Part.PartNum, Description, OrderNum, QuotedPrice
FROM Part, OrderLine
WHERE Part.PartNum = OrderLine.PartNum
AND QuotedPrice > 100;
5. The GRANT statement is used to assign privileges to users of a database. It relates to security
because a user who does not have the privilege of accessing a certain portion of a database can-
not access that portion of the database. The privileges that can be assigned include the privi-
lege of selecting rows from a table, inserting new rows, and updating existing rows. The REVOKE
command is used to revoke privileges.
7. REVOKE SELECT ON Part FROM Stillwell;
9. a.
SELECT Name
FROM Systables
WHERE Creator = 'your name';
b.
SELECT Colname, Coltype
FROM Syscolumns
WHERE Tbname = 'Customer';
c.
SELECT Tbname
FROM Syscolumns
WHERE Colname = 'PartNum';
11. Null is a special value that represents missing information. Nulls are used when a value is either
unknown or inapplicable. The primary key cannot accept null values. With a null value in the pri-
mary key, the primary key could not fulfill its main purpose of being the unique identifier for
records in a table.
13. Adding an order to the Orders table on which the customer number does not match a customer
number in the Customer table would violate referential integrity. In addition, changing the cus-
tomer number on a record in the Orders table to a number that does not match a customer num-
ber in the Customer table would also violate referential integrity. If deletes do not cascade,