[SOLVED] database SQL CIS 355 Fall, 2020

CIS 355 Fall, 2020

Assignment #3
Due date/time: Monday, November 9, 2020
No later than 11:00 PM AZ time

Your assignment: Create a star schema in Microsoft SQL Server in your personal database area based on our running example of a hospital system and medical care. The specific subject areas are yours to choose, but must be related to a hospital/medical care setting. Ideally, you should use your previous assignments, including practice assignments, as the foundation for Assignment #3.
Microsoft SQL Server/3

Assignment Details (25 points total see grading rubric at end of this document for details):25-
Your database must have 2 fact tables. (Recall from our lecture and the textbook that two fact tables means 2 different business processes related to your area of interest)

Your database must have 3 dimension tables that are relevant to both of your fact tables
In each dimension table:

You must have a minimum of 10 non-key columns/fields relevant to that subject. Example: if you have a PATIENT dimension, your non-key columns would include Patient Last Name, Patient First Name, Patient Address, Patient Age,

In each fact table:

You could have 1 or more facts. 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 is fine, though.
2 2
3

o10/

o1

You will:

Draw your conceptual design on paper first (strongly recommended; but you dont need to submit any draft or preliminary models as part of your assignment)

Translate your conceptual design into SQL syntax for your dimension and fact tables

Log into Microsoft SQL Server as we have done during our classroom demonstrations

Create your dimension tables inside SQL Server, using properly identified surrogate keys as the primary key of each table

SQL
3Microsoft SQL Server
4.SQL Server
Create your fact tables inside SQL Server, using properly identified foreign keys for the surrogate keys
5SQL Server

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 AT END FOR GUIDANCE.
6.PowerPoint

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 9PowerPoint

_____________________

Your model should structurally look something like the following, ref: the pasted

SQL statements from SQL Server into your dimensional model. (Beware though: the

following model is incorrect ref: surrogate keys that arent INT data types, a business order subject area, etc. just use this as guidance for how your deliverable should look, versus what you did for Assignment #2)SQL ServerSQL INT-2

Grading Rubric:

3 dimension tables and 2 fact tables: 5 points for each325

For each dimension table:

1 point for overall syntactical correctness, with at least 10 non-key columns, each with correct data types (e.g., no FIRST_NAME INT)o110FIRST_NAME INT
2 points for correct primary key syntax: 1 point for using a surrogate key, 1 point for correct data type 211

2 points for correct usage of NOT NULL in table, particularly the primary key2NOT NULL

For each fact table:
1 point for correct fact(s) including numeric data type, and fully syntactically correct
1 point for tables primary key being correct
3 points for all foreign key constraints correct