数据库原理E/R图

---

绘制E/R模型图，包括实体集、属性和联系

  You need to design a database for the course selection system The database includes three entity sets described as following:
    • “Students”, its attributes involve Sno, Sname, Sclass;
    • “Courses”, its attributes involve Cno, Cname, credit(学分);
    • “Teachers”, its attributes involve Tno, Tname, title(职称).
  There is a “enrollment(登记)” relation between the Students and Courses, a student can enroll different courses, and each course can be enrolled by different students. This relationship also requires an attribute score .
  There is a “teaching” relation between the Teachers and Courses, a teacher can teach different courses, each course can only be taught by a teacher, each teacher have a classroom for each course.
  The requirements are as following:
    (1) Drawing an E/R diagram for the database, be sure to include the appropriate attributes and relation type, and give the primary key of each entity set.
Teachers是1，Coures是N

    (2) Converting the E/R diagram to a relational database schema, indicating the primary keys and foreign keys for the relations. You should combine your relations as possible.

     Students(Sno, Sname, Sclass)

     Courses(Cno, Cname, credit, Tno, classroom)
     Foreign key Tno references Teachers (Tno)

     Teachers(Tno, Tname, title)

     Enrollment(Sno, Cno, Score)
     Foreign key Sno references Students(Sno)
     Foreign key Cno references Courses(Cno)
    (3)Use SQL to find names of the students whose score of ‘c1’ is higher than that of all other students.
Select Sname From students
     Where Sno IN
     ( Select Sno From Enrollment Where Cno = ‘c1’ AND score >= ALL
     ( Select Score From Enrollment Where Cno = ‘c1’ )) ;

2.小结

     绘制E/R图记得标出主键，关系模式外键用Foreign key attribute references table(attribute)表示。根据E/R图写关系模式时，如果是1:N，则将1归于N的关系模式，如一种产品只能属于一个仓库，但一个仓库可以存放多种产品，构建联系时将仓库主键归于产品关系模式，如果是M:N，则要新建一个关系模式。ALL和in都是范围的，不是返回单个值
注：来自白老师的作业答案