程序代写代做代考 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个与两个事实表都相关的维度表
•在每个维度表中:
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

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *