Database Reference
In-Depth Information
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
primary 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,
deleting a customer that has orders would violate referential integrity. If deletes cascade, such a
customer can be deleted, in which case all orders for that customer will automatically be deleted.
15. Stored procedures are special files containing a collection of SQL statements that will be executed
frequently. The statements in a stored procedure are compiled and optimized, enabling the stored
procedure to execute as efficiently and as rapidly as possible. It also makes the execution of the
commands in the stored procedure simpler than if the user had to type the command each time
he or she wanted to use it.
362
CHAPTER 5
DATABASE DESIGN 1: NORMALIZATION
—
1. A column (attribute) B is functionally dependent on another column A (or possibly a collection of
columns) if each value for A in the database is associated with exactly one value of B.
3. Column A (or a collection of columns) is the primary key for a relation (table) R, if: Property 1
—
all
no subcollection of the columns in A
(assuming A is a collection of columns and not just a single column) also has Property 1.
5. A table is in first normal form if it does not contain a repeating group.
7. A table is in third normal form if it is in second normal form and the only determinants it con-
tains are candidate keys. If a table is not in third normal form, redundant data will cause wasted
space and update problems. Inconsistent data might also be a problem.
9. An interrelation constraint is a condition that involves two or more relations. Requiring the value
of a RepNum on a row in the Customer relation to match a value of RepNum in the Rep relation
is an example of an interrelation constraint. The interrelation constraints are addressed through
foreign keys.
columns in R are functionally dependent on A; and Property 2
—
