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
Reviews
There are no reviews yet.