(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.
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