Consider the following University Database schema: (The key attribute(s) of each
ID: 3568137 • Letter: C
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
1. For the course IFSC614, list the D_Code, C_no, course title and offering department of
each of its prerequisite courses.
2. List the D_code and C_no of the courses that dont require a prerequisite.
3. Retrieve the name of students majoring in IFSC along with the courses they have taken and
their respective grades.
4. List the names and SSNs of students who are currently NOT enrolled in any class.
5. List the title and CR_no of the classes taught by Lindsay.
6. 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.
7. 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').
8. Retrieve the average grade for the course MATH321.
9. List the SSNs of the students who have received a grade of at least 90 in ALL the courses(s)
he/she has taken.
10. 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
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.