COMP9311 24T1 数据库处理

COMP9311 24T1:项目1截止时间：3月29日星期五16:59:59（悉尼时间）

1.目的

这个项目旨在让你在

阅读和理解一个适度大的关系模式（MyMyUNSW）。
实现SQL查询和视图以满足信息请求。
实现PL/pgSQL函数以帮助满足信息请求。
目标是在MyMySUNSW数据库上构建一些有用的数据访问操作。数据可能包含一些数据不一致；然而，它们不会影响你对这个项目的回答。

2.如何进行此项目：

仔细完整地阅读本规范。
熟悉数据库模式（描述、SQL模式、摘要）。
为该项目创建一个专用目录，并在其中放置proj1.sql模板的副本。
在定义解决方案时，必须使用proj1.sql中的create语句。
查看作为check.sql文件的一部分加载的expected_qX表中的预期输出。
解决“任务”部分中的每个问题，并将您完成的解决方案放入proj1.sql中。
通过对照示例输出进行验证并使用Check_qX（）函数（遵循“自动测试检查”部分）来检查您的解决方案是否正确。
测试您的proj1.sql文件是否会无错误地加载到仅包含原始MyMyUNSW数据的数据库中。
仔细检查您的proj1.sql文件是否在一次过程中加载到仅包含原始MyUNSW数据的数据库中。
通过moodle提交项目。
对于每个问题，您必须在120秒内在nw-syd-vxdb服务器上输出结果。
硬编码是严格禁止的。

3.介绍

所有大学都需要一个重要的信息基础设施来管理它们的事务。这通常涉及大型商业DBMS安装。新南威尔士大学的学生信息系统位于MyUNSW网站的后面。MySUNSW提供了一个到PeopleSoft企业管理系统的接口，该系统具有底层Oracle数据库。这个后端系统（Peoplesoft/Oracle）通常被称为NSS。

新南威尔士大学在MyUNSW/NSS系统上花费了相当多的资金（8000万美元以上），它似乎很好地处理了大部分教育管理工作。大多数人抱怨MySUNSW界面的质量，但该系统确实允许您在线执行最基本的注册任务。

尽管取得了成功，但MySNSW/NSS仍有一些不足之处，包括：

没有课程或班级注册的等候名单。
没有学位课程结构的表示。

与新南威尔士大学在线手册的整合不力。

第一点是不方便的，因为这意味着注册一门完整的课程或课程变成了一系列试错尝试，希望有人在你尝试注册之前就辍学了，而没有其他人占据了可用的名额。

第二点阻止MyUNSW/NSS用于三个对学生管理入学极有帮助的重要操作：

了解他们在学位课程中取得了多大进步，还有什么需要完成。
检查他们下学期的入学选择（例如，获取可用课程列表）。
确定他们何时完成了学位课程的所有要求并有资格毕业。

NSS包含有关学生、课程、班级、先决条件、配额等的数据，但不包含新南威尔士大学学位课程结构的任何表示。如果NSS数据库中没有这些信息，就不可能执行上述三项中的任何一项。因此，在2007年，COMP9311类设计了一个数据模型，代 写COMP9311 24T1可以表示新南威尔士大学学位的课程要求和规则。这是建立在现有的模式之上的，该模式代表了NSS的所有核心数据（学生、员工、课程、班级等）。增强的数据模型被命名为MyMyUNSW模式。

MyMyUNSW数据库包括包含NSS、UNSW在线手册和CATS（房间分配）数据库功能的信息。MyMyUNSW数据模型、模式和数据库在单独的文档中进行了描述。

4.设置

要在nw-syd-vxdb服务器下安装MyMyUNSW数据库，只需运行以下两个命令：

$createdb项目1
$psql proj1-f/home/cs9311/web/24T1/proj/proj1/mymyunsund.dump

如果您已经在template1数据库中设置了PLpgSQL，那么在数据库开始加载时，您将收到一条错误消息：

psql:mymysunsw.dump:NN:错误：语言“plpgsql”已存在。

您可以忽略上述错误消息，但需要调查加载过程中出现的所有其他错误。

如果一切正常进行，加载输出应该如下所示：

集合集合集合

集合
psql:mymysunsw.dump:NN:错误：语言“plpgsql”已存在
…如果PLpgSQL尚未定义。。。上面的ERROR将被CREATE LANGUAGE SET替换
集合集合
创建表创建表
…一大堆CREATE TABLE
更改表格更改表格
…一大堆ALTER TABLE

除了可能的与plpgsql相关的消息之外，您不应该得到任何错误消息。

假设nw-syd-vxdb没有处于重负载下，在nw-syd-vxdb上加载数据库的时间应小于60秒。（如果你把项目拖到最后一刻，在nw-syd-vxdb上加载数据库的速度会慢得多，从而更加延迟你的工作。解决方案是：至少现在加载数据库，即使你有一段时间没有开始使用它。）（请注意，mymyunsto.dump文件的大小为50MB；将其复制到主目录或“/srvr”目录下不是一个好主意）。

如果在nw-syd-vxdb上的PostgreSQL服务器下有其他大型数据库，或者在“/srvr/you/”目录下有大型文件，则可能会耗尽nw-syd-vxdb磁盘配额。无论如何，可以肯定的是，您将无法在nw-syd-vxdb服务器下存储MyMyUNSW数据库的两个副本。解决方案：在加载MyMyUNSW数据库之前删除所有现有数据库。

入门摘要

要为此项目设置数据库，请按提供的顺序运行以下命令：

$createdb项目1
$psql proj1-f/home/cs9311/web/24T1/proj/proj1/mymyunsund.dump
$psql项目1
…运行一些检查以确保数据库正常
$mkdir项目1目录
…为项目1创建一个工作目录
$cp/home/cs9311/web/24T1/proj/proj1/proj1.sql项目1目录

这些命令产生的唯一错误消息应该是上面提到的那些。如果你省略了任何一个步骤，那么事情就不会按计划进行。

5.开始前的重要建议

您得到的数据库实例不是一个小实例。您应该做的第一件事是了解数据库中的数据。这将帮助您更好地理解模式，并使任务更容易理解。提示：研究每个表的模式，看看表是如何相关的，并尝试编写一些查询来探索/了解每个表存储的内容。

$psql项目1
proj1=#\d
…研究模式。。。
proj1=#从学生中选择；
…查看学生表中的数据。。。
proj1=#从人员p中选择p.unswid，p.name加入学生s on（p.id=s.id）；
…看看所有学生的名字和新南威尔士大学ID。。。
proj1=#从人员p中选择p.unsid、p.name、s.phone加入员工s on（p.id=s.id）；
…看看所有员工的姓名、员工ID和电话号码。。。proj1=#从Course_Enrolments中选择计数（）；
…了解每个表的记录数。。。proj1=#select*from dbpop（）；
…所有表中有多少条记录。。。proj1=#。。。等等等等。
proj1=#\q

在开始练习之前，请阅读以下内容：

分数反映了每个问题的相对难度/长度。
在提供的proj1.sql模板文件上处理项目。
确保您的查询在MyMyUNSW模式的任何实例上都能工作；不要将它们自定义为仅用于此数据库；我们可以在不同的数据库实例上测试它们。
不要假设任何查询都只返回一个结果；即使它的措辞是“最多”或“最大”，数据库中也可能有两个或多个同样“大”的实例。
当查询要求提供人名时，请使用Person.name字段；它正是为了产生可显示的名称。
当查询要求提供学生ID时，请使用People.sunsid字段；People.id字段是一个内部数字键，数据库之外的任何人都不感兴趣。
除非练习中特别提到，否则元组在结果中的顺序无关紧要；它总是可以使用order by进行调整。事实上，我们的check.sql会自动对您的结果进行排序以进行比较。
结果元组中字段的精确格式很重要，例如，如果使用to_char将数字转换为字符串，则它可能不再与包含相同值的数字字段匹配，即使这两个字段看起来可能相似。
我们建议分阶段开发查询；在最终视图/函数的查询中使用子查询或子联接之前，请确保使用的任何子查询或个子联接都能正常工作
您可以根据需要定义任意多的其他视图，前提是（a）保留proj1.sql中的定义，（b）遵循每个问题中允许定义的要求。
如果遇到类似“无法更改视图列的名称”的错误，可以使用命令“drop-view VIEWNAME cascade；”删除刚刚创建的视图，然后再次创建新视图。

每一个问题都有一个关于所需内容的简短描述。如果您想要获得预期输出的全部详细信息，请查看检查脚本（check.sql）中提供的expected_qX表。

6.任务
为了便于半自动标记，请将所有SQL解决方案打包到每个问题中定义的视图/函数中（请参阅我们提供的解决方案模板中的详细信息）。

问题1（3分）

定义一个SQL视图Q1（subject_code），该视图提供类型为学校的组织提供的所有7级科目。学校名称中应包含“信息”。
subject_code应取自Subjects.code字段。
学校是指包含“学校”的Orgunit_types.name字段。
学校名称指的是orgunits.longname字段。
Level-7表示subject_code格式为“XXXX7**”，其中“X”表示字母，“”表示数字。

问题2（3分）

定义一个SQL视图Q2（course_id），该视图提供COMP课程的id，该课程只提供“讲座”和“实验室”课程（该课程只有两种不同类型的课程）。

course_id是指Courses.id字段。
讲座和实验室请参阅Class_types.name字段。
COMP课程是指相关科目代码以“COMP”开头的课程。

问题3（4分）

定义一个SQL视图Q3（unslid_id），该视图提供2008年至2012年间每年至少注册五门课程的学生的unslid。只考虑至少有两名教授作为教职员工的课程和以320开头的学生。
unswl_id应取自people.unswid字段。
Professor表示People.title中的“Prof”。
年份是指Semeters.Year字段。

问题4（5分）

定义一个SQL视图Q4（course_id，avg_mark），该视图提供课程id以及每门课程获得“以上成绩”的学生的平均分数。该观点仅包括与同一教师在2012年同一学期内提供的其他课程相比，平均“高于标准”分数最高的课程。如果有多个课程共享相同的最大平均分数，请列出这些课程的所有course_id及其avg_mark。

注：在数字类型中，将avg_mark四舍五入到最接近的0.01英寸（即85.014≈85.01，85.016≈85.02，85≈85.00）。

course_id应取自Courses.id。
Faculty是指其Orgunit_types.name为“Faculty”的组织单位。
“上述区别”是指学生的课程成绩为“DN”或“HD”。

问题5（5分）

定义SQL视图Q5（course_id，staff_name），该视图提供2005年至2015年间招收了500多名学生并至少有两名教授担任教职的课程的id。在staff_name部分显示按顺序排列的教授姓名。
注意：将给定名称与“；”连接（即Jack；Michele）。
Professor是指People.title字段中的“Prof”。
给定名称指的是People.gived字段。

问题6（5分）

定义SQL视图Q6（room_id，subject_code），该视图给出了2012年不同类使用最频繁的房间的id，以及占用最多的主题代码。如果有多个房间或主题共享相同的最大使用量，请列出所有房间或主题。

room_id应取自rooms.id字段。
subject_code应取自subject.code字段。

问题7（5分）

定义SQL视图Q7（student_id，program_id），该视图提供已完成同一组织提供的两个或多个程序的学生的id。此外，同一组织的所有项目都在1000天内完成。program_id列应该列出这些程序的id。

student_id应取自people.unswid字段。
组织是指orgunits.id字段。
program_id是指programs.id字段。
假设学生每学期最多可以注册一个课程。笔记
如果学生的成绩≥50分，她/他将通过该课程并获得UOC。
如果学生在该项目（提示：subjects.UOC）中获得的UOC总收入不低于该项目所需的UOC（请参阅programs.UOC），则该学生毕业有效。
如果一名学生参加了多个不同的课程，则需要根据不同的课程分别计算UOC。如果学生在同一学期同时注册了该课程和该项目，则该课程被视为该项目的一部分。

对于每个学生来说，一个课程的持续时间是她/他的所有课程报名中最早的日期（提示：学期开始）和最晚的日期（暗示：学期结束）之间的天数。
“1000天”是指学生在同一组织完成的所有课程中，从第一个课程开始（提示：学期开始）到最后一个课程结束（提示：学年结束）的持续时间不超过1000天。program_id字段应该枚举所涉及的程序的id。

问题8（6分）

定义SQL视图Q8（staff_id、sum_roles、hdn_rate），该视图给出了员工的id、员工过去或当前在所有组织中担任的角色（附属机构）的总数，以及2012年该员工担任课程召集人的所有课程的总“以上优异”率。该视图仅显示以前在同一组织中担任三个或三个以上职位（附属机构）的员工，显示基于hdn_rate的前20名结果。

注：按照与问题4相同的规则，将比率四舍五入到最接近的0.01。

上述区别意味着Course_enrolments.mark≥75。
如果员工姓名为
Course_staff中的“课程召集人”。
一个组织中员工角色（隶属关系）的数量指的是她/他的员工_职位.id
在affiliations.orgunit字段中。
staff_id应取自People.sunsid字段。
hdr_rate应为数字类型。

问题9（6分）

定义一个PL/pgSQL函数Q9（unscid integer），该函数获取学生的unscid，并返回给定学生注册并以有效分数完成的课程的主题代码，以及该学生在该课程中的排名。只考虑至少有一个相同前缀课程作为先决条件的课程。

应从People.sunwid字段中获取一个unswid。
前提条件是指相关表格中的_prereq。
相同前缀的课程是指前四个字符相同的课程。
排名基于学生在本课程中获得的分数（参见Course_enrolments.mark），从高到低。如果多个学生取得了相同的成绩，他们应该被分配相同的排名。PostgreSQL中的Rank（）函数将能够为您生成排名列。
输出的每一行（文本类型）都应包含以下两个元素，并用空格连接：

科目代码：课程的科目代码应取自subjects.code字段。

等级：课程等级应为整数。如果她/他在课程中排名第二，则结果为2。特殊输出：
如果学生没有以有效分数完成任何必修课程，请按以下格式返回一行
的“警告：无效的学生输入[X]”，其中“X”表示所提供的未锁定。

问题10（8分）

定义一个PL/pgSQL函数Q10（unscid整数），它接受学生的unscid。输出该学生注册的所有项目的学生WAM。

应从People.sunwid字段中获取一个unswid。
与问题7不同的是，在这个问题中，如果学生在setpass={SY，PT，PC，PS，CR，DN，HD，a，B，C，XE，T，PE，RC，RS}中获得了一个分数，那么她就通过了一门课程。
setpass≠setsy={SY，XE，T，PE}或setpass≈（课程标记=null）中的所有成绩，表示学生通过但不包括在WAM计算中的课程。
所有不在setpass中的分数，不包括课程分数=null，表示不及格。这些不及格的课程仍然包括在WAM计算中，而零分的课程则被排除在外。
如果一个学生已经注册了几个不同的项目，你需要根据不同的项目分别计算WAM。如果学生在同一学期同时注册了该课程和该项目，则该课程被视为该项目的一部分。假设学生每学期最多可以注册一个课程。

WAM根据以下公式计算：
∑（M×U）
WAM= ∑U

式中：M=在一门课程中获得的分数，U=一门课程的学分单位。

例如，学生收到的课程成绩如下：80、81、82、83、84。这些课程的前三门是6 UOC，后两门是3 UOC。WAM计算如下：
[(80×6)+(81×6)+ (82×6)+(83×3)+(84×3)] = 81.625 ≈ 81.63 .
(6 + 6 + 6 + 3 + 3)

输出的每一行（文本类型）都应包含以下三个元素，并用空格连接：

Unwid：取自People.sunwid字段的一个Unswid学生。
程序名称：取自programs.name字段的程序名称。
WAM：WAM结果，四舍五入到最接近的0.01。使用与问题4相同的规则。

特殊输出：

如果学生已经注册了一个课程，但她/他没有注册任何课程，或者注册的所有课程都不包括在该课程的WAM计算中，即除数为零，则在返回行的WAM部分返回“无WAM可用”。
如果学生没有注册任何课程（找不到该学生的任何课程），请以“警告：无效的学生输入[X]”的格式返回一行，其中“X”表示所提供的未输入。

7.自动测试检查

在提交解决方案之前，应使用以下操作检查和测试其正确性。对于每个PostgreSQL问题，我们提供了六个测试用例（例如，对于问题9，它们是从q9a到q9f）。测试用例可以从check.sql文件中的第222行找到：

$dropdb proj1 …删除任何现有的DB
$createdb-proj1…创建一个空数据库
$psql proj1-f/home/cs9311/web/24T1/proj/proj1/mymyunsund.dump
…加载MyMyUNSW架构和数据
$psql-proj1-f/home/cs9311/web/24T1/proj/proj1/check.sql
…加载检查代码
$psql-proj1-fproj1.sql。。。加载您的解决方案
$psql项目1
proj1=#选择check_q1（）； …检查问题1的解决方案
…
proj1=#选择check_q6（）； …检查问题的解决方案6
…
proj1=#选择check_q9a（）； …检查问题9（a）的解决方案
…
proj1=#选择check_q10e（）； …检查问题10（e）的解决方案
…
proj1=#选择check_all（）； …检查所有解决方案

笔记：

1.您必须确保您提交的proj1.sql文件将被正确加载和运行（即，它没有语法错误，并且以正确的顺序包含所有视图定义）。
a.如果数据库中包含的任何视图在某个文件中不可用，则应在删除数据库之前将其放入文件中。
b.对于所有提交文件，您必须确保在使用上面提供的自动测试时没有发生错误。如果我们需要手动修复您的proj1.sql文件中的问题来测试它（例如，更改某些定义的顺序），您将被罚款一半作为每个问题的惩罚。
2.此外，编写效率合理的查询。
a.对于每个问题，必须在120秒内在nw-syd-vxdb服务器上生成结果。否则将受到惩罚，扣除一半的分数。此时间限制适用于执行命令“select*from check_Qn（）”。

8.项目提交

您可以通过以下操作提交此项目：

您需要通过Moodle提交电子版的答案。
我们只接受.sql格式。请按以下格式命名您的文件以提交：proj1_zID.sql（例如，proj1_zm5000000.sql）。
仅标记“最新提交”。逾期提交的最新资料将被处以逾期罚款。
如果系统工作不正常，请确保采取以下步骤：在CSE服务器上保留您提交的文件的副本，不得在截止日期后进行任何修改。如果你不确定如何做到这一点，请参阅Taggi上提供的指南。
proj1_zID.sql文件应该包含该项目所有练习的答案。它应该是完全独立的，并且能够在一次通过中加载，以便可以按以下方式进行自动测试：

将创建MyMyUNSW数据库的新副本（使用MyMyUNSW.dump中的架构）。
此数据库中的数据可能与您用于测试的数据库不同。
可以加载一个新的check.sql文件（预期结果适合数据库）。
将加载proj1_zID.sql文件的内容。
将执行每个检查功能，并记录结果。

9.逾期提交罚款

每增加一天，将扣除总分数（50分）的5%。
逾期超过五天的提交将不会被标记。