Consider the following University Database schema: (The key attribute(s) of each

Question

Consider the following University Database schema: (The key attribute(s) of each relation is highlighted. Assume all attributes except F_Name, S_Name are atomic.)

DEPARTMENT (D_CODE :string, D_Name :string, Chairman_SSN :integer) COURSE (D_CODE :string, C_NO :integer, Title :string, Units :integer)
PREREQ (D_CODE :string, C_NO :integer, P_CODE :string, P_NO :integer) CLASS (CR_NO :integer, D_CODE :string, C_NO :integer, Instructor_SSN :integer) ENROLLMENT (Student_SSN :integer,CR_NO :integer)

FACULTY (SSN :integer, F_Name :string, D_CODE :string, Rank :string)
STUDENT (SSN :integer, S_Name :string, Major :string, Status :string) TRANSCRIPT (Student_SSN :integer, D_CODE :string, C_NO :integer, GR :integer)

? Each department is described by the department code, department name, and the chairmans SSN. (FK: Chairman_SSN ? FACULTY.SSN)

? Each course is described by the code of the offering department, course code, course title, and number of credits.

? A prerequisite is described by the department code and course code of a course along with the department code and course code of one of its prerequisites.

FK: PREREQUISITE.D_CODE ? DEPARTMENT.D_CODE,

PREREQUISITE.P_CODE ? DEPARTMENT.D_CODE, PREREQUISITE.(D_CODE, C_NO)?COURSE(D_CODE,C_NO), PREREQUISITE.(P_CODE , P_NO)?COURSE(D_code,C_NO)

? A course may have several classes (sessions). Each class is described by the unique course reference number, the code of the offering department, course number (sessions of the same course have the same D_CODE and C_NO but different CR_NO), and instructors SSN. FK: CLASS.(D_CODE,C_NO) ? COURSE.(D_CODE,C_NO)

CLASS.Instructor_SSN ? FACULTY.SSN CLASS.D_CODE ? DEPARTMENT.D_CODE

? The Enrollment lists a students SSN and the CR_NO of a course he/she is currently taking. FK: ENROLLMENT.Student_SSN ? STUDENT.SSN,

ENROLLMENT.CR_NO? CLASS.CR_NO

? A faculty is identified by his/her SSN, name, department code, and rank.

? A student is identified by his/her SSN, name, major, and status.

? A transcript includes a students SSN, department code, course code, and grade. FK: TRANSCRIPT.Student_SSN ? STUDENT.SSN,

TRANSCRIPT.(D_CODE,C_NO) ? COURSE.(D_CODE,C_NO) TRANSCRIPT.D_CODE ? DEPARTMENT.D_CODE

In the relation PREREQ, a pair <P_CODE, P_NO> refers to <D_CODE, C_NO> in the COURSE relation. An example row in PREREQ relation is <IFSC, 614, IFSC, 501>; it means IFSC501 is a prerequisite course of IFSC614.

In the FACULTY relation, the domain of the attribute Rank is {Full, Associate, Assistant, Adjunct}, i.e., these are the permitted values for the attribute Rank.

In the TRANSCRIPT relation, the domain of the attribute GR is the set of integers between 0-100. Please specify the following queries in SQL

For the course IFSC614, list the D_Code, C_no, course title and offering department of each of its prerequisite courses.

List the D_code and C_no of the courses that dont require a prerequisite.

Retrieve the name of students majoring in IFSC along with the courses they have taken and their respective grades.

List the names and SSNs of students who are currently NOT enrolled in any class.

List the title and CR_no of the classes taught by Lindsay.

Find the SSNs of the students who have taken IFSC 614 (D_CODE=IFSC, C_NO=614) and CS501 (D_CODE=CS, C_NO=501) and received a grade of at least 80 in both courses.

List the SSNs and names of students who are currently enrolled in a class taught by the chair of the ISE department (i.e., D_CODE='ISE').

Retrieve the average grade for the course MATH321.

List the SSNs of the students who have received a grade of at least 90 in ALL the courses(s) he/she has taken.

Find the name of the student who has enrolled more classes than any other students.

Explanation / Answer

1)

SELECT PREREQ.D_CODE, PREREQ.C_NO, COURSE.TITLE,

DEPARTMENT_NAME FROM PREREQ, COURSE, DEPARTMENT

WHERE (PREREQ.P_CODE = IFSC AND PREREQ.P_NO = 614);

2)

SELECT COURSE.D_CODE, COURSE.C_NO FROM COURSE

WHERE PREREQ.D_CODE = NULL AND

               PREREQ.C_NO = NULL;

3)

SELECT STUDENT.S_NAME, TRANSCRIPT.C_NO

FROM STUDENT, TRANSCRIPT

WHERE STUDENT.MAJOR = IFSC AND

               STUDENT.SSN = TRANSCRIPT.STUDENT_SSN;

4)

SELECT SSN, S_NAME FROM STUDENT

WHERE TRANSCRIPT.STUDENT_SSN = NULL;

5)

SELECT COURSE.TITLE, CLASS.CR_NO FROM COURSE, CLASS

WHERE FACULTY.F_NAME = ‘LINDSAY’ AND

              (FACULTY.D_CODE = COURSE.D_CODE NAD

                FACULTY.D_CODE = CLASS.D_CODE);

6)

SELECT STUDEN_SSN FROM TRANSCRIPT

WHERE (TRANSCRIPT.D_CODE = ‘IFSC’ AND TRANSCRIPT.C_NO =614)

     AND (TRANSCRIPT.D_CODE = ‘CS’ AND TRANSCRIPT.C_NO = 501);

7)

SELECT SSN, S_NAME FROM STUDENT

WHERE STUDENT.SSN = TRANSCRIPT.STUDENT_SSN AND

               TRANSCRIPT.D_CODE = ‘ISE’;

8)

SELECT AVG (GR) FROM TRANSCRIPT

WHERE TRANSCRIPT.D_CODE = ‘MATH’ AND

                TRANSCRIPT.C_NO = 321;

9)

SELECT STUDENT_SSN FROM TRANSCRIPT

WHERE (TRANSCRIPT.D_CODE = COURSE.D_CODE AND

                TRANSCRIPT.C_NO = COURSE.C_NO) AND

                (TRANSCRIPT.GR => 90);

10)

SELECT STUDENT.S_NAME, TRANSCRIPT.COUNT (C_NO) AS MAX_COURSE

FROM STUDENT, TRANSCRIPT

WHERE MAX (MAX_COURSE);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.