380
Relations, Functions, and Matrices
Exercises 29–36 are all related to the same enterprise.
29. A corporation sponsors a yearly campaign to solicit monetary contributions from its employees for a local charity, and the company decides to use a database to keep track of the data. Employee data already
include employee ID, first name, last name, and department. Employees sign a contribution pledge on
a particular date, that specifies the total amount they wish to donate and the number of equal biweekly
payroll deductions (starting with the next pay period) they want to use to pay off the total. The payroll
department needs to know details about each payment, including the contribution pledge the payment is
for, the payment date and the amount deducted. An employee can make multiple pledges.
a. Do you agree that the following “data decomposition” is consistent with the enterprise description?
If not, what should be added or what should be removed?
entity
attributes
Employee
EmployeeID
FirstName
LastName
Department
Contribution ContributionID EmployeeID
ContributionDate TotalAmount NumberofPayments
Payment
ContributionID PaymentDate PaymentAmount
b. Identify a primary key for each of the Employee, Contribution, and Payment entities and explain your
choice.
30. Draw an E-R diagram based on Exercise 29.
31. A “universal relation” contains all the data values in one relation. The table represents a report that might
be distributed to the campaign manager. The universal relation as of 1/16/2014 is shown here.
Employee
iD
First
Name
last
Name
Department
contribution
iD
contribution
Date
Total
amount
Number
of
payments
payment
Date
payment
amount
1
Mary
Black Accounting
101
1/1/2013
$300.00
3
1/15/2013 $100.00
1
Mary
Black Accounting
101
1/1/2013
$300.00
3
1/31/2013 $100.00
1
Mary
Black Accounting
101
1/1/2013
$300.00
3
2/15/2013 $100.00
1
Mary
Black Accounting
105
6/1/2013
$210.00
3
6/15/2013 $70.00
1
Mary
Black Accounting
105
6/1/2013
$210.00
3
6/30/2013 $70.00
1
Mary
Black Accounting
105
6/1/2013
$210.00
3
7/15/2013 $70.00
2
June
Brown Payroll
107
6/1/2013
$300.00
2
6/15/2013 $150.00
2
June
Brown Payroll
107
6/1/2013
$300.00
2
6/30/2013 $150.00
2
June
Brown Payroll
108
1/1/2014
$600.00
12
1/15/2014 $50.00
3
Kevin
White Accounting
102
1/1/2013
$500.00
2
1/15/2013 $250.00
3
Kevin
White Accounting
102
1/1/2013
$500.00
2
1/31/2013 $250.00
3
Kevin
White Accounting
109
1/1/2014
$500.00
2
1/15/2014 $250.00
4
Kelly
Chen Payroll
104
4/15/2013 $100.00
1
4/30/2013 $100.00
6
Conner Smith Sales
103
1/1/2013
$150.00
2
1/15/2013 $75.00
6
Conner Smith Sales
103
1/1/2013
$150.00
2
1/31/2013 $75.00
Relations, Functions, and Matrices
Exercises 29–36 are all related to the same enterprise.
29. A corporation sponsors a yearly campaign to solicit monetary contributions from its employees for a local charity, and the company decides to use a database to keep track of the data. Employee data already
include employee ID, first name, last name, and department. Employees sign a contribution pledge on
a particular date, that specifies the total amount they wish to donate and the number of equal biweekly
payroll deductions (starting with the next pay period) they want to use to pay off the total. The payroll
department needs to know details about each payment, including the contribution pledge the payment is
for, the payment date and the amount deducted. An employee can make multiple pledges.
a. Do you agree that the following “data decomposition” is consistent with the enterprise description?
If not, what should be added or what should be removed?
entity
attributes
Employee
EmployeeID
FirstName
LastName
Department
Contribution ContributionID EmployeeID
ContributionDate TotalAmount NumberofPayments
Payment
ContributionID PaymentDate PaymentAmount
b. Identify a primary key for each of the Employee, Contribution, and Payment entities and explain your
choice.
30. Draw an E-R diagram based on Exercise 29.
31. A “universal relation” contains all the data values in one relation. The table represents a report that might
be distributed to the campaign manager. The universal relation as of 1/16/2014 is shown here.
Employee
iD
First
Name
last
Name
Department
contribution
iD
contribution
Date
Total
amount
Number
of
payments
payment
Date
payment
amount
1
Mary
Black Accounting
101
1/1/2013
$300.00
3
1/15/2013 $100.00
1
Mary
Black Accounting
101
1/1/2013
$300.00
3
1/31/2013 $100.00
1
Mary
Black Accounting
101
1/1/2013
$300.00
3
2/15/2013 $100.00
1
Mary
Black Accounting
105
6/1/2013
$210.00
3
6/15/2013 $70.00
1
Mary
Black Accounting
105
6/1/2013
$210.00
3
6/30/2013 $70.00
1
Mary
Black Accounting
105
6/1/2013
$210.00
3
7/15/2013 $70.00
2
June
Brown Payroll
107
6/1/2013
$300.00
2
6/15/2013 $150.00
2
June
Brown Payroll
107
6/1/2013
$300.00
2
6/30/2013 $150.00
2
June
Brown Payroll
108
1/1/2014
$600.00
12
1/15/2014 $50.00
3
Kevin
White Accounting
102
1/1/2013
$500.00
2
1/15/2013 $250.00
3
Kevin
White Accounting
102
1/1/2013
$500.00
2
1/31/2013 $250.00
3
Kevin
White Accounting
109
1/1/2014
$500.00
2
1/15/2014 $250.00
4
Kelly
Chen Payroll
104
4/15/2013 $100.00
1
4/30/2013 $100.00
6
Conner Smith Sales
103
1/1/2013
$150.00
2
1/15/2013 $75.00
6
Conner Smith Sales
103
1/1/2013
$150.00
2
1/31/2013 $75.00