Build 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.  you CANNOT use "operating room procedures" (ref: the SQL Server instructions slides and the practice exercise) for your subject area. You can do hospital pharmacy and medications, or admissions/discharges, or radiology services, or something along those lines...just not operating room procedures/surgeries. CAUTION: IF YOU SUBMIT YOUR STAR SCHEMA WITH SOMETHING OTHER THAN HOSPITAL SYSTEMS/MEDICAL CARE AS YOUR SUBJECT AREA, OR IF YOU USE OPERATING ROOM PROCEDURES/SURGERIES AS THE EMPHASIS OF YOUR HOSPITAL MODEL, YOU WILL RECEIVE A ZERO SCORE FOR YOUR ASSIGNMENT, EVEN IF YOUR MODEL AND SQL SERVER WORK ARE SYNTACTIALLY AND SEMANTICALLY CORRECT

  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: o 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: o 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. 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. your dimension tables inside SQL Server, using properly identified surrogate keys as the primary key of each table 5. Build your fact tables inside SQL Server, using properly identified foreign keys for the surrogate keys 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