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 chairman

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.