s
Section 5.4 Functions
381
a. Given this universal relation, create and populate with data the three relation tables for the three entities
described in Exercise 29. Underline the primary key in each table.
b. Describe any foreign keys in the relation tables.
c. Consider the form of the Employee IDs. This is probably what kind of key?
32. a. If Mary Black moves from the Accounting Department to the Sales Department, how many tuples must
be updated in the universal relation?
b. The three relation tables from Exercise 31 should follow the “one fact, one place” rule (see Exercise
26). With the same change to Mary Black’s department, how many tuples must be updated in the database using the three relation tables of Exercise 31?
Exercises 33–36 make use of the three relation tables from Exercise 31.
33. Write an SQL query to give the employee ID, pay dates, and payment amounts for all pay dates with
amounts > $100. Give the result of the query.
34. Write an SQL query to give the contribution ID, pay date, and payment amount for all payments by Mary
Black. Give the result of the query.
35. Write an SQL query to give the first and last names and payment amount of all employees who had a
payroll deduction on 1/15/2013. Give the result of the query.
36. Write an SQL query to reproduce the universal relation of Exercise 31 from the three relation tables.
Figure 5.11
S e c t I o n 5 . 4 FunCtions
In this section we discuss functions, which are really special cases of binary relations from a set S to a set T. This view of a function is a rather sophisticated one,
however, and we will work up to it gradually.
definition
Function is a common enough word even in nontechnical contexts. A newspaper
may have an article on how starting salaries for this year’s college graduates have
increased over those for last year’s graduates. The article might say something
like, “The salary increase varies depending on the degree program,” or, “The
salary increase is a function of the degree program.” It may illustrate this functional relationship with a graph like Figure 5.11. The graph shows that each degree
program has some figure for the salary increase associated with it, that no degree
program has more than one figure associated with it, and that both the physical
sciences and the liberal arts have the same figure, 1.5%.
Engineering
Physical
science
Computer
science
Liberal arts
Business
3.0%
2.5%
2.0%
1.5%
1.0%
0.5%
0%
Section 5.4 Functions
381
a. Given this universal relation, create and populate with data the three relation tables for the three entities
described in Exercise 29. Underline the primary key in each table.
b. Describe any foreign keys in the relation tables.
c. Consider the form of the Employee IDs. This is probably what kind of key?
32. a. If Mary Black moves from the Accounting Department to the Sales Department, how many tuples must
be updated in the universal relation?
b. The three relation tables from Exercise 31 should follow the “one fact, one place” rule (see Exercise
26). With the same change to Mary Black’s department, how many tuples must be updated in the database using the three relation tables of Exercise 31?
Exercises 33–36 make use of the three relation tables from Exercise 31.
33. Write an SQL query to give the employee ID, pay dates, and payment amounts for all pay dates with
amounts > $100. Give the result of the query.
34. Write an SQL query to give the contribution ID, pay date, and payment amount for all payments by Mary
Black. Give the result of the query.
35. Write an SQL query to give the first and last names and payment amount of all employees who had a
payroll deduction on 1/15/2013. Give the result of the query.
36. Write an SQL query to reproduce the universal relation of Exercise 31 from the three relation tables.
Figure 5.11
S e c t I o n 5 . 4 FunCtions
In this section we discuss functions, which are really special cases of binary relations from a set S to a set T. This view of a function is a rather sophisticated one,
however, and we will work up to it gradually.
definition
Function is a common enough word even in nontechnical contexts. A newspaper
may have an article on how starting salaries for this year’s college graduates have
increased over those for last year’s graduates. The article might say something
like, “The salary increase varies depending on the degree program,” or, “The
salary increase is a function of the degree program.” It may illustrate this functional relationship with a graph like Figure 5.11. The graph shows that each degree
program has some figure for the salary increase associated with it, that no degree
program has more than one figure associated with it, and that both the physical
sciences and the liberal arts have the same figure, 1.5%.
Engineering
Physical
science
Computer
science
Liberal arts
Business
3.0%
2.5%
2.0%
1.5%
1.0%
0.5%
0%