(Solved): CIS 355 Assignment 4 ...

Using the same subject area as you did for Assignment #3 (unless directed by your instructor to choose another subject area), you will create a star schema SQL Server model, with tables pasted as in Assignment 3, containing both types of factless fact tables, as discussed in the course lecture:

Case 1: business activity ("something happened”), but nothing to measure

Case 2: “conditions, coverage, or eligibility” – a “state of being” – but nothing to measure

If you had any points deducted for your dimension tables in Assignment #3, you will need to "fix" any issues for Assignment #4.

You will remove your transaction-grained fact tables from your model and replace them with factless fact tables, as described above.

You also may need to add a DATE/TIME-related dimension table if you didn't have one in Assignment #3; if you do, you can EITHER delete one of your Assignment 3 dimension tables and replace with DATE/TIME, or just add the new DATE/TIME one to the existing 3; your choice based on what makes sense for your model.

You will upload your PPTX (or PDF) deliverable that contains your model to this link before the due date/time.

GRADING RUBRIC:

25 points total: 9 points for dimension tables, 8 points for each of your faceless fact tables

• Dimension tables: 3 points for each one (including DATE/TIME dimension; if you have 4 total, all 4 will factor into this portion of the grading), "all or nothing" correctness: 
  • surrogate key as primary key with correct data type and NOT NULL clause
  • correct PRIMARY KEY clause
  • no FOREIGN KEY clause or other syntax violations
• Fact tables: 8 points for each, based on:
  • SQL syntax: 4 points, "all or nothing" including PRIMARY and FOREIGN KEY clauses/constraints; correct primary key designation; no facts/measurements, only keys; other syntax
  • Correct fact table usage: 4 points
    • For both fact tables: your dimensions are correctly and fully related to the business subject area that you selected - (example: if you were to do "SEASON TICKETS" then you must have one or more appropriate dimensions related to SEASON TICKETS  - in other words, you need to have more than just syntactically correct PK and FK columns, your model needs to "make sense")
    • "Business event with nothing to measure" must be correct and related to health care/hospital
    • "Coverage/eligiblity/'state of being'" must be correct and related to ASU ATHLETICS - make sure that your relation(s) to your date/time dimensions are syntactically and semantically correct