Consider the following tables and give answers to the queries in Relational Algebra syntax. Queries are given at the end of the tables.

Note: Just write the queries on the paper and submit them. No need to use Relax software.

P-T-O1. Write Relational algebra queries to answer the following questions:

a. Which students have an ID number that is less than 50000?

b. What is the name of the faculty member whose ID is 4756?

c. What is the smallest section number used in the first semester of 2008?

2. Write Relational algebra queries to answer the following questions:

a. How many students are enrolled in Section 2714 in the first semester of 2008?

b. Which faculty members have qualified to teach a course since 1993? List the faculty ID, course,

and date of qualification.

3. Write SQL queries to answer the following questions:

a. Which students are enrolled in Database and Networking? (Hint: Use SectionNo for each class so

you can determine the answer from the Registration table by itself.)

b. Which instructors cannot teach both Syst Analysis and Syst Design?

4. Write Relational algebra queries to answer the following questions:

a. What are the courses included in the Section table? List each course only once.

b. List all students in alphabetical order by StudentName.

c. List the students who are enrolled in each course in Semester I, 2008. Group the students by the

sections in which they are enrolled.

d. List the courses available. Group them by course prefix. (ISM is the only prefix shown, but there

are many others throughout the university.)

Transcribed Image Text:

STUDENT (StudentID, StudentName) QUALIFIED (FacultylD, CourselD, DateQualified) StudentID StudentName FacultylD CourselD DateQuaified ISM 3112 ISM 3113 ISM 4212 38214 Letersky Altvater 2143 9/1988 54907 66324 2143 3467 9/1988 W1995 Aiken 70642 Marra 3467 4756 4758 ISM 4930 ISM 3113 ISM 3112 e/1996 W1991 e/1901 FACULTY (FacultylD, FacultyName) SECTION (SectionNo, Semester, CourselD) FacultylD FacultyName SectionNo Semester CourselD 2143 Brkin 2712 1-2008 ISM 3113 ISM 3113 ISM 4212 ISM 4930 Berndt 3467 4756 2713 2714 -2008 Collins 1-2008 2715 1-2008 COURSE (CourselD, CourseName) REGISTRATION (StudentID, SectionNo, Semester) CourcelD CourseName StudentID SectionNo Semester ISM 3113 ISM 3112 ISM 4212 ISM 4930 Syst Analysis Syst Design Database Networking 38214 2714 1-2000 1-2008 1-2008 1-2008 54907 2714 54907 2715 66324 2713 STUDENT (StudentID, StudentName) QUALIFIED (FacultylD, CourselD, DateQualified) StudentID StudentName FacultylD CourselD DateQuaified ISM 3112 ISM 3113 ISM 4212 38214 Letersky Altvater 2143 9/1988 54907 66324 2143 3467 9/1988 W1995 Aiken 70642 Marra 3467 4756 4758 ISM 4930 ISM 3113 ISM 3112 e/1996 W1991 e/1901 FACULTY (FacultylD, FacultyName) SECTION (SectionNo, Semester, CourselD) FacultylD FacultyName SectionNo Semester CourselD 2143 Brkin 2712 1-2008 ISM 3113 ISM 3113 ISM 4212 ISM 4930 Berndt 3467 4756 2713 2714 -2008 Collins 1-2008 2715 1-2008 COURSE (CourselD, CourseName) REGISTRATION (StudentID, SectionNo, Semester) CourcelD CourseName StudentID SectionNo Semester ISM 3113 ISM 3112 ISM 4212 ISM 4930 Syst Analysis Syst Design Database Networking 38214 2714 1-2000 1-2008 1-2008 1-2008 54907 2714 54907 2715 66324 2713