CEO of Entertainment Inc. want to keep track with the life cycle of engagement c

Question

CEO of Entertainment Inc. want to keep track with the life cycle of engagement contract. Particularly, he want to know the total numbers of entertainers participated, the contract price, commission rate when contract is created and authorized, negotiated, executed and monitored, and closeout. He is also interested in how many customers, and agents engaged for each contract life cycle phrase. Build data warehouse tables in SQL to provide this information,

How should I go about providing this fact table? There is only Engagement, Customer, Agent, and an entertainer table to use when creating the DIm Tables.

Explanation / Answer

CREATE TABLE AGENT
(
AGENTID NUMBER NOT NULL
, AGENTNAME VARCHAR2(20 BYTE)
, CID VARCHAR2(20 BYTE)
, CONTRACTID VARCHAR2(20 BYTE)
, EID NUMBER
, CONSTRAINT AGENT_PK PRIMARY KEY
(
AGENTID
)
ENABLE
)
LOGGING
TABLESPACE "SYSTEM"
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);

ALTER TABLE AGENT
ADD CONSTRAINT CID FOREIGN KEY
(
AGENTID
)
REFERENCES CUSTOMER_TBL
(
CID
)
ENABLE;

ALTER TABLE AGENT
ADD CONSTRAINT CONTRACTID FOREIGN KEY
(
AGENTID
)
REFERENCES CONTRACT
(
CONTRACTID
)
ENABLE;

CREATE TABLE CUSTOMER_TBL
(
CID NUMBER NOT NULL
, CUSTNAME VARCHAR2(20 BYTE)
, CONTRACTID NUMBER
, CONSTRAINT CUSTOMER_TBL_PK PRIMARY KEY
(
CID
)
ENABLE
)
LOGGING
TABLESPACE "SYSTEM"
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);

ALTER TABLE CUSTOMER_TBL
ADD CONSTRAINT CUSTOMERID FOREIGN KEY
(
CID
)
REFERENCES CUSTOMER_TBL
(
CID
)
ENABLE;

CREATE TABLE CONTRACT
(
CONTRACTID NUMBER NOT NULL
, CONTRACTNAME VARCHAR2(20 BYTE)
, PRICE NUMBER
, CONSTRAINT CONTRACT_PK PRIMARY KEY
(
CONTRACTID
)
ENABLE
)
LOGGING
TABLESPACE "SYSTEM"
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);

CREATE TABLE ENGAGEMENT
(
EID NUMBER NOT NULL
, ENAME VARCHAR2(20 BYTE)
, CONTRACTID NUMBER
, CONSTRAINT ENGAGEMENT_PK PRIMARY KEY
(
EID
)
ENABLE
)
LOGGING
TABLESPACE "SYSTEM"
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);

ALTER TABLE ENGAGEMENT
ADD CONSTRAINT AGENTID FOREIGN KEY
(
EID
)
REFERENCES AGENT
(
AGENTID
)
ENABLE;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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