The university is organized into colleges (COLLEGE), and each college has a unique name (CName), a main office (COffice) and phone (CPhone), and a particular faculty member who is dean of the college. Each college administers a number of academic departments (DEPT). Each department has a unique name (DName), a unique code number (DCode), a main office (DOffice) and phone (DPhone), and particular faculty member who chairs the department. We keep track of the start date (CStartDate) when that faculty member began chairing the department.

A department offers a number of courses (COURSE), each of which has a unique course name (CoName), a unique code number (CCode), a course level (Level: this can be coded as 1 for freshman level, 2 for sophomore, 3 for junior, 4 for senior, 5 for MS level, and 6 for PhD level), a course credit hours (Credits), and a course description (CDesc). The database also keeps track of instructors (INSTRUCTOR); and each instructor has a unique identifier (Id), name (IName), office (IOffice), phone (IPhone), and rank (Rank); in addition, each instructor works for one primary academic department.

The database will keep student data (STUDENT) and stores each students name (SName, composed of first name (FName), middle name (MName), last name (LName)), student id (Sid, unique for every student), address (Addr), phone (Phone), major code (Major), and date of birth (DoB). A student is assigned to one primary academic department. It is required to keep track of the student's grades in each section the student has completed.

Courses are offered as sections (SECTION). Each section is related to a single course and a single instructor and has a unique section identifier (Secid). A section also has a section number (SecNo: this is coded as l, 2, 3, ... for multiple sections offered during the same semester/year), semester (Sem), year (Year), classroom (CRoom: this is coded as a combination of building code (Bldg) and room number (RoomNo) within the building), and days/times (DaysTime: for example, MWF 9am-9.50am or TR 3.30pm-5.20pm restricted to only allowed days/time values). (Note: The database will keep track of all the sections offered for the past several years, in addition to the current offerings. The SecId is unique for all sections, not just the sections for a particular semester.) The database keeps track of the students in each section, and the grade is recorded when available (this is a many-to-many relationship between students and sections). A section must have at least five students.

List of few queries

1. Retrieve all the students id number, name, the number of courses selected, and total grade.
2. Total number of students in any particular course who has successfully registered to the course.
3. Retrieve the Name, Selected Course, birth date, Phone Number and address of the Student(s).
4. For every Department, list the course along with the credit, fee and Faculty details.
5. To retrieve complete list of courses in a selected department.
6. To retrieve details of Faculty along with the course subjects being taught.
7. Total fee for a student based on the course or credits selected.
8. For each student, retrieve the students first and last name and the first and last name of his or her immediate supervisor.
9. Retrieve the grades of every student and all distinct credits passed.
10. Retrieve all students in a department (CS) who credits are above 12.
11. Retrieve list of students along with department name who does not have supervisor assigned.
12. Find the list of students who have maximum and minimum credits.
13. To find average GPA of all Students.
14. Give the name of the student with highest GPA.
15. Show the list of students with the GPA grades in each major ordered by GPA in descending order.
16. Query to find out with how many students does a specific faculty work with.
17. Add .05 to all of the Marketing major's GPA's
18. Change Vijays faculty from Robert to Charles.
19. For any student who is currently majoring in CIS and who has a GPA of less than 3.0, change their major to Marketing.
20. Retrieve the students id number that get same grade in different courses, and display their id number, course id number, and the grade that are the same.
21. Retrieve all the students id number and average score whose average score is greater than 60.
22. Delete all the records from Student table where the student is Jack;
23. Retrieve the max and min score and display them with the format: Course Id, Max Score, Min Score;
24. Retrieve all the courses and the number of students who select that course;
25. Retrieve the students id number, name, and average grade whose average is greater than 85;

Draw the relational algebra for the above queries using relational calculus