Your assignment: Create a star schema in Microsoft SQL Server in your personal d

Your assignment: Create a star schema in Microsoft SQL Server in your personal
database area based on the subject area of Human Resources. You may choose any
particular “sub-area” (e.g., hiring a new employee; out-processing an employee who
resigned; promoting an employee; handling annual reviews; etc.).
HOWEVER: IF YOU SUBMIT YOUR STAR SCHEMA WITH SOMETHING OTHER
THAN HUMAN RESOURCES AS YOUR SUBJECT AREA, YOU WILL RECEIVE A ZERO
SCORE FOR YOUR ASSIGNMENT, EVEN IF YOUR MODEL AND SQL SERVER WORK
ARE SYNTACTIALLY AND SEMANTICALLY CORRECT!
IF YOU WANT TO BE 100% CERTAIN YOUR SELECTED “TOPIC” AND “SUBTOPIC”
WILL BE ACCEPTABLE WHEN GRADED, EMAIL YOUR INSTRUCTOR
(arsimon@asu.edu) FOR APPROVAL.
Assignment Details (25 points total – see grading rubric at end of this document
for details):
Your database must have exactly 2 fact tables. (Recall from our lecture and the
textbook that two fact tables means 2 different “business processes” as defined
by the Kimball methodology, with both related to the assigned topic)
Your database must have exactly 3 dimension tables that are each relevant to
and connected via primary-foreign key relationships to both of your fact
tables…no more than 3 dimension tables!
In each dimension table:
o You must have a minimum of 10 non-key columns/fields relevant to
that subject. Example: if you have an Employee dimension table, your
non-key columns would include Employee Last Name, Employee First
Name, Employee Address, … Employee Age, …
In each fact table:
o You could have 1 fact, or more than 1 fact. If you have more than 1 fact,
then those facts must be part of the same business process and
managed at the same grain; see the lecture material and the textbook.
Just 1 fact in each is fine, though. Make sure you are crystal clear what
a data warehousing “fact” is – i.e., a quantifiable measurement. Also
make sure you are crystal clear how to establish and designate the
primary key in a fact table, per the course material, versus how you
designate the PK in a dimension table.
o You should have no “attributes” – i.e., a database column that is neither
a foreign key nor a fact – in a fact table.
o Ref: one of the solutions for the practice Assignment #2, do not include
natural keys from your connected dimensions as non-PK columns in
your fact table
You will:
1. Draw your conceptual design on paper first (strongly recommended; but you
don’t need to submit any draft or preliminary models as part of your
assignment)
2. “Translate” your conceptual design into SQL syntax for your dimension and
fact tables
3. Log into Microsoft SQL Server as you did when verifying your connectivity and
database access
4. Create your dimension tables inside SQL Server, using properly named
surrogate keys as the properly identified primary key of each table
5. Create your fact tables inside SQL Server, using properly named surrogate
keys for your foreign keys, and those foreign keys for your primary key
6. Take a screenshot of each of your fact and dimension tables and on a
PowerPoint document, paste those screenshots onto a page and then
draw the appropriate lines to diagram your star schema. SEE
STRUCTURAL EXAMPLE BELOW FOR GUIDANCE.
7. If applicable, on a separate page in your PowerPoint deliverable document list
any assumptions or notes that you think are relevant to us grading your
submission
_____________________
Your model should visually look something like the following (also posted on Canvas
with the assignment for reference), ref: the pasted SQL statements from SQL Server
into your dimensional model. (Beware though: the following model is incorrect ref:
surrogate keys that aren’t INT data types, a business order subject area, etc. – just use
this as visual guidance for how your deliverable should look, versus what you did for
practice Assignment #2 where you created what was essentially a dimensional
version of an entity-relationship model.)
ADDITIONALLY: YOU MUST FOLLOW THE SYNTAX FROM THE COURSE
CONTENT AND EXAMPLES, PARTICULARLY REGARDING DATABASE TABLES’
PRIMARY KEYS. SPECIFICALLY: YOU MUST USE A SEPARATE PRIMARY KEY
CONSTRAINT AS SHOWN DURING THE COURSE, RATHER THAN ADDING
“PRIMARY KEY” TO A COLUMN DESIGNATOR. THE REASON IS THAT A FACT
TABLE HAS A COMPOSITE PRIMARY KEY (SEE COURSE MATERIALS) AND YOU
MUST BE CONSISTENT IN HOW YOU DEFINE AND DESIGNATE A PRIMARY KEY
BETWEEN FACT AND DIMENSION TABLES. IF YOU DO NOT USE A SEPARATE
PRIMARY KEY CONSTRAINT CLAUSE, EVEN FOR A DIMENSION TABLE WITH A
SINGLE COLUMN PK, YOU WILL LOSE POINTS.
Grading Rubric:
3 dimension tables and 2 fact tables: 5 points for each
For each dimension table:
o 1 point for overall syntactical correctness, with at least 10 non-key
columns, each with correct data types (e.g., no FIRST_NAME INT)
o 2 points for correct primary key syntax: 1 point for using a surrogate
key and naming that column/field properly, 1 point for correct data
type – see “warning” above re: needing to use a separate PRIMARY
KEY constraint clause
o 2 points for correct usage of NOT NULL in table, particularly the
primary key
For each fact table:
o 1 point for correct fact(s) including numeric data type, and fully
syntactically correct
o 1 point for table’s primary key being fully correct
o 3 points for all foreign key constraints correct
Requirements: | .doc file