1)Consider the following E-R model for the ‘Beta University’ problem given in Ho

Question

1)Consider the following E-R model for the ‘Beta University’ problem given in Homework1. Examine the E-R model and write the complete relational database schema by following the rules described in class (make sure you underline primary keys and also indicate foreign keys in the schema using arrows as shown in class):

Entities and corresponding attributes:

Potential Donor- donorId, donorName, street, city, state, zip, country, phone, donor Category(multi- valued), donorCircle, graduationYear(multi-valued), amountDonatedLastYear, amountPledgedThisYear, amountPaidThisYear, donorMatchCorp, spouseName, spouseMatchCorp

Matching Corp- matchCorpName, street, city, state, zip, country
Event – eventName, eventDate, eventTime, eventLocation, eventOrganizer, eventTotalPledged

eventTotalPaid

ClassRep – graduationYear, classRepName, street, city, state, zip, country, classRepPhoneNumber

Volunteer – volunteerName, street, city, state, zip, country, volunteerPhoneNumber

Pledge – pledgeNumber, pledgeAmount, pledgeDate, numPaymentsChosen, pledgeAmountPaid, numPaymentsMade

Payment - datePaid, amount Paid, paymentMethod, creditCardType, credit Card Number

2. Using your ‘Beta University’ relational database schema, write relational algebra statements (combination of SELECT, PROJECT, JOIN, and SET operations) to perform the following database operations:

a)Find the names and phone numbers of donors who donated more this year

compared to last year

b)Find the names and phone numbers of donors who belong to the category

‘Alumnus’

c)Find the id numbers of donors who donated more than $500 during the event

‘Bake Sale’ on 12/15/2013

d)Find the names and graduation year of donors who sent their pledge amount in the

year 2013 using Visa credit cards

e)Delete all pledge records from donor bearing donorId 12463

Explanation / Answer

Hi,

Below is the solution-

Ans 2 a) select donorname,phone from potential_donor where amountPaidThisYear>amountDonatedLastYear;

Ans 2 b) select donorname,phone from potential_donor where donorCategory='Alumnus';

Ans 2 c) Need to have a foreign key(donorId) in the Event table
select donorID from potential_donor pd
join Event e on
pd.donorId=e.donorId
and pd.amountPaidThisYear>500
and e.eventname='Bake Sale'
and e.eventdate='12/15/2013';

Ans 2 d)
To solve it we need to have below-
a) A foreign key donorId in pledge table
b) A foreign key pledgenumber in payment table

select donorname,graduationYear from potential_donor pd join Pledge p
on pd.donorid=p.donorId
and to_char(pledgedate,'YYYY')='2013'
join payment pmt
on p.pledgenumber=pmt.pledgenumber
and pmt.creditcardtype='Visa Credit Cards';

Ans 2 e) delete from potential_donor where donorid='12463';

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.