数据库上机作业(英文)

Experiment

i) Identify the entities, relationships, and attributes required for constructing the entity-relationship models for the college database specified next.

ii)Construct for college database according the following entity-relationship model. The database keeps track of courses, textbooks, instructors, and students.

Fig.1 E-R model of college database

iii) Solve the following queries (which refer to the college database) in SQL.

(1)Find all students’ names of students who took cs210 and c s240.

(2)Find all students’ names of students who took cs210 but never took cs110.

(3)Find all pairs of student numbers such that both students studied with the same instructor.

(4)Find all triplets of instructors’ names of instructors who taught the same course.

(5)Find course numbers of courses taken by a student and taught by an instructor such that the student is advised by the instructor.

(6)Find all instructors who advise students who took a course from another instructor who is located in the same room.

(7)Find the names of instructors who taught every course taken by a student who lives in New Hampshire.

(8)Find all pairs of names of students and instructors such that the instructor advises the student but the student never took a course with the instructor.

(9)Find courses’ numbers such that two students are enrolled.

(10) Find courses’ numbers such that at least three students are enrolled.

(11) Find the names of instructors who taught every course taught by Prof. Evans.

(12) Find courses’ names of courses taught by every instructor who teaches cs240.

(13) Find students’ numbers, the lowest grade, the highest grade, and the average grade for students who took at least two courses, excluding cs110.

(14) Find instructors’ names for instructors who teach courses that enroll at least two students.

(15) List the names of instructors and the number of students advised by each instructor.