Star Schema from Relational Database

Question

I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.

In all the examples I've seen the fact table references the dim tables, so I'm assuming the questionId and responseId would be part of the fact table? Any advice would be much appreciated.

score 3 · Accepted Answer · answered Aug 08 '16 at 21:49

Ok, based on your very limited documentation, I would do the following:

Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension tables, typically at a point in time. In your diagram,

RESPONSES is your fact table.
OPTIONS could be a fact table. If you denormalize it you could have dimensions associated with it.

Dimension Tables - Dimension tables contain the textual context associated with a measurement event. It describes the “who, what, where, when, how, and why.” Based on your diagram:

SAMPLE would be a dimension table, describing the people who make a response. It would be the closest you have to a "customer" dimension table.
QUESTIONS would be a dimension table, describing the question in detail. You could extend this dimension to include more data on the question type, such as length, IsMultipleChoice, IsFreeText, IsPersonal, isPrivate, etc. I would think of this dimension as a "product" dimension table, based on the limited schema you have.
OPTIONS could be a dimension table. It looks like it is something you base the response field on. The possibility of responses for a given question.

Personally, I think you need to read up on Data Warehousing. The Data Warehouse Toolkit by Kimball is an invaluable resource.

score 1 · Answer 2 · edited Nov 27 '23 at 23:05

CREATE TABLE atudent
(
  student_id_ INT NOT NULL,
  address INT NOT NULL,
  Gender INT NOT NULL,
  Birthday INT NOT NULL,
  Program INT NOT NULL,
  Class INT NOT NULL,
  Name INT NOT NULL,
  Phone INT NOT NULL,
  Ssn INT NOT NULL,
  PRIMARY KEY (student_id_)
);

CREATE TABLE Department
(
  Department_id INT NOT NULL,
  Name INT NOT NULL,
  Code INT NOT NULL,
  College INT NOT NULL,
  PRIMARY KEY (Department_id)
);

CREATE TABLE instructor
(
  instructor_id INT NOT NULL,
  Name INT NOT NULL,
  title INT NOT NULL,
  Ssn INT NOT NULL,
  Department_id INT NOT NULL,
  PRIMARY KEY (instructor_id),
  FOREIGN KEY (Department_id) REFERENCES Department(Department_id)
);

CREATE TABLE Course
(
  Course_id INT NOT NULL,
  description INT NOT NULL,
  prerequisite INT NOT NULL,
  Name INT NOT NULL,
  Number INT NOT NULL,
  PRIMARY KEY (Course_id)
);

CREATE TABLE section_
(
  section_id INT NOT NULL,
  semester INT NOT NULL,
  Year INT NOT NULL,
  Numer INT NOT NULL,
  instructor_id INT NOT NULL,
  Course_id INT NOT NULL,
  PRIMARY KEY (section_id),
  FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id),
  FOREIGN KEY (Course_id) REFERENCES Course(Course_id)
);

CREATE TABLE Enrolls
(
  Grade INT NOT NULL,
  student_id_ INT NOT NULL,
  section_id INT NOT NULL,
  FOREIGN KEY (student_id_) REFERENCES atudent(student_id_),
  FOREIGN KEY (section_id) REFERENCES section_(section_id)
);

Star Schema from Relational Database

2 Answers2