COMPSCI 351 S1 C-Assignment2——The University Database——讲解分析

数据库的描述

原文

译文

这个问题上是使用一个简化的大学课程管理数据库，这个数据库主要是由下面七个关系构成：

• department：这个表记录了关于部门的信息。
• student：这个表记录了有关于学生的所有信息。
• instructor：这个表记录了导师的信息。
• course：这个表记录课程信息。
• section：这个表记录了每个学期和每一年提供的课程。
• time_slot:这个表记录了上课时间具体信息。
• classroom：这个表记录了上课教室的信息。
• teaches：这个表记录了导师教学部份的信息。
• takes：这张表记录了学生注册课程的信息。
• advisor：这张表记录了导师和学生的推荐人关系。
• prerep：这张表记录了课程的先决条件信息。

关于具体的表的结构和表的约束信息，请参见“university_data.sql”数据库文件。

在PhyMyAdmin中创建一个叫做“stu_upi_COMPSCI_351_C_S1_2021_A2”的数据库来解决下述的问题。运行已经给的SQL描述语句，并将数据插入数据库。请注意，upi是UPI的样例。请在数据库中和作业分配中使用自己的upi。

重要：在回答下述问题之前，你需要将给定的数据填入A2数据库中。请不要改变表中的任何数据，因为这些数据是用来验证你的回答是否生成正确的结果的标准。

在数据被灌入A2数据库中，我们可以运行SQL查询语句从其中检索有用的信息。例如：下述的查询语句就是检索所有学生的ID，名称和在“Comp.Sci”部门的学生的总学分。

查询结果被展示的在下面的表格中，通过点击SQL界面的“Print view”按钮实现对数据的展示。

• 注意：
  • 上述的输出结果不仅仅展示了SQL的语句和最终的查询结果，同时也展示了包含你的UPI的正在工作的数据库名称。你应该使用layout去作为下述问题的回答。
  • 你需要使用PhyMyAdmin工具去完成这个任务。创建一个PDF文件，这个PDF文件包含了的上述所有问题的答案，提交这个PDF文件作为你对问题的回答。使用“Print View”执行后的截屏作为你的答案来回答问题。

表格分析

• classroom：
  – classroom表存放上课教室的信息
  – 包括：教室所在教学楼、教室号、教室最大学生数量
  – 主码：教室所在教学楼、教室号

• department：
  – department表存放某系的信息
  – 包括：系名、系所在教学楼、年预算
  – 主码：系名

• course：
  – course表存放了有关课程的信息
  – 包括：课程ID、课程名、系名、学分
  – 主码：课程ID

• instructor：
  – instructor表存放了有关教师的信息
  – 包括：教师ID、姓名、系名、年薪
  – 主码：教师ID

• section：
  – section表存放了课程具体的授课情况
  – 包括：课程ID、课程段ID、学期、年、教学楼、教室号、课程节次
  – 主码：课程ID、课程段ID、学期、年

• teaches：
  – teaches表存放了有关教师授课的信息
  – 包括：教师ID、课程ID、课程段ID、学期、年
  – 主码：教师ID、课程ID、课程段ID、学期、年

• student：
  – student表存放了有关学生的信息
  – 包括：学生ID、学生姓名、系名、总绩点
  – 主码：学生ID

• takes：
  – takes表存放了有关学生每门课程的信息
  – 包括：学生ID、课程ID、课程段ID、学期、年、分数
  – 主码：学生ID、课程ID、课程段ID、学期、年

• advisor：
  – advisor表存放了有关学生导师的信息
  – 包括：学生ID、教师ID
  – 主码：学生ID

• time_slot：
  – time_slot表存放了课程上课时间的信息
  – 包括：课程节次、课程上课星期、开始时间（小时）、开始时间（分钟）、结束时间（小时）、结束时间（分钟）
  – 主码：课程节次、课程上课星期、开始时间（小时）、开始时间（分钟）

• prereq：
  – prereq表存放每门课程的先修课程信息
  – 包括：当前课程ID、先修课程ID
  – 主码：当前课程ID、先修课程ID

执行源码

# 第一题，首先找出被老师教的和被老师推荐的
# 首先找出kat推荐的授课id
select ID,name,dept_name
from student
where ID in (select s_IDfrom advisor,instructorwhere instructor.ID = advisor.i_IDand instructor.name = "Katz"unionselect IDfrom takeswhere course_id in (select course_idfrom instructor,teacheswhere instructor.ID = teaches.IDand instructor.name = "Katz"));# 第二题，找出所有和提供section系楼不在同一个地方
select temp.c_id,temp.d_name,temp.sec_id,temp.semester,temp.`year`,temp.a_build
from (select section.course_id AS c_id,course.dept_name AS d_name,section.sec_id,section.semester,section.`year`,section.building AS a_buildfrom section,coursewhere section.course_id = course.course_id) temp,department
where temp.d_name = department.dept_name
AND temp.a_build != department.building;# 第三题，使用join操作去定义一个检索符合下列条件得查询：这门课在2020年可以上，输出应该使用系名去分组
# 要列出得信息：dept_name,course_id,title,sec_id,semester
# dept_name,course_id,title在course中存在
# sec_id,semester在section表中有select dept_name,course.course_id,title,sec_id,semester
from course inner join section
on section.course_id = course.course_id
where section.`year` = 2020
GROUP BY dept_name;# 第四题，检索至今为止没有教任何课得老师
# instructor：ID,name,dept_name,salary
# teaches:ID,course_id,sec_id,semester,year
select instructor.ID,name,dept_name,COUNT(*)
from instructor LEFT JOIN teaches
ON instructor.ID = teaches.ID
GROUP BY instructor.ID
having COUNT(*) = 0;# 第五题，检索同一年上了两门课及以上的学生
select student.ID,student.`name`
from student,takes
where student.ID = takes.ID
GROUP BY student.ID,`year`
HAVING COUNT(*) >= 2;# 第六题，使用join操作和合计函数去定义下列的sql语句。
# 对于每一个系来说，检索dept_name，导师编号，年预算，导师工资总和，最大最小工资，平均工资
# 可以使用format函数，将平均工资变成两位小数，这里没找到，不知道人家怎么实现的
# 我的navicate实现不了，识别不出来，认为是语法错误
select department.dept_name,COUNT(instructor.ID) AS No_of_Instructors,budget,SUM(salary) AS Total_sal,MAX(salary) AS Highest_Sal,MIN(salary) AS Lowest_Sal,AVG(salary) AS Average_Sal
FROM department LEFT JOIN instructor
ON department.dept_name = instructor.dept_name
GROUP BY department.dept_name;# 第七题：定义搜索所有课程对应注册学生学号的视图。
CREATE VIEW course_enrolment(year,semester,course_id,sec_id,No_of_students,Instructor)
AS
SELECT section.year,section.semester,section.course_id,section.sec_id,COUNT(takes.ID),instructor.NAME
FROM section,takes,teaches,instructor
WHERE section.course_id=takes.course_id	
AND section.course_id=teaches.course_id
AND teaches.ID=instructor.ID
GROUP BY section.`year`,section.semester,section.course_id,section.sec_id;# 第八题：检索所有学生的成绩总和，在2019年FALL第一个section上了CS-347的学生的成绩列表
SELECT student.ID,student.`name`,grade
from student,section,takes,course
WHERE student.ID = takes.IDAND takes.course_id=section.course_idAND course.course_id=takes.course_idAND course.course_id='CS-347'AND section.sec_id='1'AND section.semester='Fall'AND section.`year`=2019;# 第九题，找到Comp.Sci系薪水最高的两个导师，
# limit子句的作用:
SELECT ID,`name`,salary
FROM instructor
WHERE dept_name='Comp. Sci.'
ORDER BY salary DESC
LIMIT 2;# 第十题，检索课程CS-319的时间表信息，主要是在section -2 spring semester提供的
# 2020年上课的
SELECT section.course_id,section.sec_id,section.semester,section.`year`,time_slot.`day`,time_slot.start_hr,time_slot.start_min,time_slot.end_hr,time_slot.end_min,classroom.building,classroom.room_number,classroom.capacity
FROM time_slot,section,classroom
WHERE time_slot.time_slot_id=section.time_slot_idAND classroom.room_number=section.room_numberAND classroom.building=section.buildingAND section.course_id='CS-319'AND section.sec_id='2'AND section.semester='Spring'AND section.`year`=2020;# 第十一题，你写sql语句，生成Emma Levy的所有在2020年上的课程
SELECT section.course_id,section.sec_id,section.semester,section.`year`,time_slot.`day`,time_slot.start_hr,time_slot.start_min,time_slot.end_hr,time_slot.end_min,section.building,section.room_number
FROM section,time_slot,course
WHERE section.time_slot_id=time_slot.time_slot_idAND course.course_id=section.course_idAND course.course_id IN(SELECT takes.course_idFROM student,takesWHERE student.ID=takes.IDAND student.`name`='Emma Levy');