Part II. Problem Solving [60 points] Please solve the following problems. SHOWING YOUR PROCESS IS AS IMPORTANT AS GIVING YOUR ANSWERS.

 

• The following is the meta-data of a call center database. It records information about all calls made by the customers to call center agents regarding certain products. The agents’ jobs are to take the calls, help customer figuring out what to do, and to take actions by directing customers a certain web page where the agent and the customer could conduct online chat and run diagnostic sessions.

 

Table Name	Attributes	Description
Customer	CID	Â
Name	Â
Gender	Â
City	Â
State	Â
Country	Â
Income Level	 Value: 1 - 10
Education level	 Value: 1 - 10
Internet usage level	 Value: 1 - 10
Product	PID	Â
PName	Â
PCID	Â
Agent	AID	Â
AName	Â
Agender	Â
Abranch	Â
Postion_ID	Â
Experience_Level	Â
Hiring_Date	Â
Manager_ID	Â
        Task	            TID	Â
AID	Â
CID	Â
PID	Â
Journal_Entry	Â
Begin_Time	Â
End_Time	Â
TC_ID	Â
  TaskCategory	      TC_ID	Â
TC_Name	Â
Â	Â
TaskCategory_Action	TC_ID	Â
Action_Order	Â
AC_ID	Â
Action	AC_ID	Â
Page_URL	Â
Description	Â
WebPage	PageURL	URL of a web page
PageType	customer support page for a product, search page, product description, corporate information and order page etc.
Agent_Click	TID	Â
PageURL	Â
Entry_Time	Â
Exit_Time	Â
Status	Â

 

 

 

 

 

 

• Identify the measures and constraints in the following business question:  Report how often agents in different positions access web pages of each page type to perform customer support tasks for customers from each state in each month 

 

 

 

 

 

 

 

 

 

 

 

• Given the relational schema of the source DB above, design a star schema that helps you to answer question a.