business analytics
1. The Sooner Development Fund collects donations for various programs. When donors pledge to Sooner the system records the date of the pledge. Some donors may wish to target their gift to a specific program. Some donors also have company matches available from corporate sponsors. The operational database for this system looks like the following:
2. The OU Business Association is conducting a babysitter service as a fundraiser for different clubs in the college. When a customer is entered into the system, the OU Club coordinator gets name, address, and phone. The coordinator also records each babysitting job, the amount paid for it and the sitter assigned to the job. Each person may sign up to credit only one club and the system keeps the contact person and phone number for each participating club.
The treasurer wants a data warehouse for this. He would like to be able to determine how much each customer was billed by week, month or year. How much each employee earned, also summed by time periods. He is interested in how much work is done on weekends, holidays or other special days. The transaction E-R diagram is below.
a) Draw a dimensional model for this problem .
3. The DPMA Club at XU operates a small bookstore for professional books. The club orders a selection of books for display and records them in a catalog. When members order a book, they pay for it and their name and the books they order are added to the order list. When the book arrives the member is notified to pick up the book. The Entity-Relationship diagram for this problem is below.
The club wants to track member activity including amount spent, cost of books, time for delivery, etc.
a) Draw a dimensional model for this problem .
PLEDGE PledgeID (pk) DonorID (fk) ProgramID (fk) Amount Date
DONOR DonorID (pk) CompanyID (fk) Name Address Match
COMPANY CompanyID (pk) CompanyName
PROGRAM ProgramID (pk) Description
PLEDGE
PledgeID(pk)
DonorID(fk)
ProgramID(fk)
Amount
Date
DONOR
DonorID(pk)
CompanyID(fk)
Name
Address
Match
COMPANY
CompanyID(pk)
CompanyName
PROGRAM
ProgramID(pk)
Description
JOB Jobnum(pk) Date StartTime EndTime Amount
SITTER Enum (pk) Phone Address
CLUB ClubName (pk) Contact ContactPhone
CUSTOMER CustNum (pk) Name Address Phone
JOB
Jobnum(pk)
Date
StartTime
EndTime
Amount
SITTER
Enum(pk)
Phone
Address
CLUB
ClubName(pk)
Contact
ContactPhone
CUSTOMER
CustNum(pk)
Name
Address
Phone
BOOK ISBN (pk) Title WholesalePrice AuthorNames SupplierID (fk)
ORDER OrderNum (pk) MemberID (fk) ISBN (fk) DateOrdered PurchasePrice DateDelivered
MEMBERS MemberID (pk) MemberName MemberPhone
SUPPLIER SupplierID (pk) Name ContactPerson Phone
BOOK
ISBN (pk)
Title
WholesalePrice
AuthorNames
SupplierID(fk)
ORDER
OrderNum(pk)
MemberID(fk)
ISBN (fk)
DateOrdered
PurchasePrice
DateDelivered
MEMBERS
MemberID(pk)
MemberName
MemberPhone
SUPPLIER
SupplierID(pk)
Name
ContactPerson
Phone