Create a star schema in Microsoft SQL
CIS 409 – Spring, 2024 Assignment #3 See Canvas for due date/time 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 ([email protected]) 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
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
