CS614 Data Warehousing Assignment 1 Solution Fall 2013

Question#1:

Consider the following Many-to-Many relation between “Project” and “Developer”:

Each project is assigned many developers and each developer may be assigned many projects at the same time. Apply the “Table Collapsing” technique to denormalize this relation.

          Solution: Three tables are created here.

Project_table

->project_id number primary key,

->project_name varchar(45)

Project_developer_table

->project_dev_id number primary key,

-> project_id number,

->developer_id number
FOREIGN KEY (project_id ) REFERENCES project_table(project_id )

FOREIGN KEY (developer_id ) REFERENCES developer_table(developer_id )

Developer_table

->developer_id number primary key,

->developer_name varchar(45)

Question#2:

Consider the following One-to-Many relation between “Project Backlog” and “Story”:

“Project Backlog” has many stories and each story belongs only to one “Project Backlog”. Apply the pre-joining table collapsing technique to denormalize the relation.

 Solution: Two tables are created here.

Story_table

story_id number primary key,

story varchar(5450)

project_backlog_table

project_backlog_id number primary key,

project_backlog varchar(40)

story_id number FOREIGN KEY (story_id ) REFERENCES project_backlog_table(story_id )

DOWNLOAD SOLUTION HERE
loading...