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个与两个事实表都相关的维度表
•在每个维度表中：
o您必须具有至少10个与该主题相关的非关键列/字段。例如：如果您有一个“患者”维，则您的非关键列将包括“患者的姓氏”，“患者的名字”，“患者的地址”，“患者的年龄……”
•在每个事实表中：
o您可以有一个或多个事实。如果您有多个事实，那么这些事实必须是同一业务流程的一部分，并且必须以相同的粒度进行管理；请参阅讲座材料和教科书。不过，只有1个事实是可以的。
You will:你会：
• 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)
• “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语法
• 3按照课堂演示中的步骤登录Microsoft SQL Server。
• 4.使用正确标识的代理键作为每个表的主键，在SQL Server中创建维度表。
• Create your fact tables inside SQL Server, using properly identified foreign keys for the surrogate keys
5，使用正确标识的代用外键在SQL 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 9，如果适用，请在PowerPoint可交付文档的单独页面上列出您认为与我们相关的所有假设或注释，以对您的提交进行评分
_____________________
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 aren’t 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 Server粘贴到维度模型中的SQL语句。 （但是请注意：以下模型是不正确的引用：替代不是INT数据类型，业务订单主题区域等的键-只需以此为指导，以了解交付物的外观，以及您对分配2所做的操作）
Grading Rubric:评分标准
• 3 dimension tables and 2 fact tables: 5 points for each•3个维度表和2个事实表：每个5点
• 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)总体句法正确性为o1分，至少有10个非关键列，每列具有正确的数据类型（例如，没有FIRST_NAME INT）
• 2 points for correct primary key syntax: 1 point for using a surrogate key, 1 point for correct data type。 2点表示正确的主键语法：1点表示使用代理键，1点表示正确的数据类型
• 2 points for correct usage of NOT NULL in table, particularly the primary key。2点用于正确使用表中的NOT NULL，尤其是主键
• For each fact table:
• 1 point for correct fact(s) including numeric data type, and fully syntactically correct
• 1 point for table’s primary key being correct
• 3 points for all foreign key constraints correct