In this case, you are given conceptual and relational schema of an operational database in a credit card company for its customers as follows.

Figure 3. Creidt Card Company Website Log

CustName	CustID	CCId	AmountWon&Paid	DateOfSettlement	Type
Pam	2	22	$100	1.2.2006	No Trial

Figure 4. Credit card company legal department report on settlements won and paid

Q1: Create a star schema diagram that will enable the credit card company to analyze their revenue. The fact table will include: for every individual instance of revenuethe amount. The star schema will include all dimensions that can be useful for analyzing revenue. Based on the created star schema, create relational tables and populate those tables with data extracted from the underlying operational sources.

Q2: Create a star schema diagram that will enable the credit card company to analyze contacts with their customers. The fact table will include for every individual instance of contactthe duration of the contact. The star schema will include all dimensional that can be useful for analyzing contacts. Based on the star schema created, populate those tables with data extracted from the underlying operational sources.

Figure 2. Relational Schema and source data

BirthDay CreditLimitCCID DateDue Name DatePaid Address imaryHolde AnnualFee Amount 1:M CUSTOMERS Has CREDITCARDS DatePaid onducts With Makes Has MI 0:M] Date Time ID Date 0:M] TID Amount [0:M Type PID PHONECALLS TRANSACTIONS INFRACTIONS [0:M) TimeBegin [O:M 0:M TimeEnd andles oldVia SellVia CUSREP PRODUCT SERVICE MERCHANTS Name Name PSID Name PctgCharged RID MID CUS TOMER CUSTREP 123 Oak Dr 12.12.1975 Ty Pat Pam124 Pine Dr. Sue 11.11.1969 1.1.1976 123 Oak Dr. HOLDERS CREDITCARD 1.1.2006. 1.1.2006. $5,000 $10,000 $25 2 null null INFRACTION INCUREDINFRACTION Over Limit $25 1.2. 2006. TRANSACTION 1X 1.1.2006. 1.1.2006 1.1.2006 12.20 12.22 12:23 $100 $100 2 2 PRSERVICES MERCHANTS Entertainment Fuel Bo's Theatre Milton Fuel 3% PHCALL ime 0:01 0:01 ime 0:02 0:03 data 11p 1.1.2006. 1.1.2006. null UU.U4 UU 00:05:00 BirthDay CreditLimitCCID DateDue Name DatePaid Address imaryHolde AnnualFee Amount 1:M CUSTOMERS Has CREDITCARDS DatePaid onducts With Makes Has MI 0:M] Date Time ID Date 0:M] TID Amount [0:M Type PID PHONECALLS TRANSACTIONS INFRACTIONS [0:M) TimeBegin [O:M 0:M TimeEnd andles oldVia SellVia CUSREP PRODUCT SERVICE MERCHANTS Name Name PSID Name PctgCharged RID MID CUS TOMER CUSTREP 123 Oak Dr 12.12.1975 Ty Pat Pam124 Pine Dr. Sue 11.11.1969 1.1.1976 123 Oak Dr. HOLDERS CREDITCARD 1.1.2006. 1.1.2006. $5,000 $10,000 $25 2 null null INFRACTION INCUREDINFRACTION Over Limit $25 1.2. 2006. TRANSACTION 1X 1.1.2006. 1.1.2006 1.1.2006 12.20 12.22 12:23 $100 $100 2 2 PRSERVICES MERCHANTS Entertainment Fuel Bo's Theatre Milton Fuel 3% PHCALL ime 0:01 0:01 ime 0:02 0:03 data 11p 1.1.2006. 1.1.2006. null UU.U4 UU 00:05:00