CS 338 Computer Applications in Business: Database
Assignment #1 – Relational Algebra
Introduction
Assignments are essential in learning the course material and preparing for exams. You can ask for help via the course message board or by contacting the TAs and instructor, but make sure you write the answers in your own words. We will check for copies. Please DO NOT post assignments (in part or in whole) to third party web sites (other than Learn). Such act constitutes copy right infringement and academic misconduct. You should submit your work to the drop box on Learn, by 1pm on Oct 2nd, 2024. Late submission will NOT be accepted.
In order for Learn to properly render and display your file, when you submit your assignment (or exam) file, please ONLY upload PDF file or image (JPG, JPEG, BMP, PNG) file.
When writing RA expression. try to follow the formatting style. in the lectures. If you write your answers without proper formatting in exams and in assignments, it will be difficult to understand your answers and increase the chance of being misinterpreted.
Questions
The questions here are based on the Enrollment database whose schema is listed on the last page. Write relational algebra expressions to answer the following questions.
1. Find students who never took more than one course from the same instructor. That is, every course that they took (including repeated courses if any) had a different instructor. The result should list student names and ID.
2. Find course(s) offered in W24 where all the students were from the same major or the course used textbook(s) published by “Pearson Publishing” . The result should display course name and course number.
3. In the math faculty, a student can try the same course no more than three times. We need to give a friendly reminder to students who are trying the same course for the third time this term (F24). Write a query to find these students. The result should display their name, SID and major. Assume that the policy is now adopted by all faculties and applied to all students.
4. Find the publisher(s) who has at least one book adopted by each department except the AMATH department (ZERO book adopted by courses offered by AMATH department).
5. Find out which of the following majors has the most students: C&O, AMATH or ITM. The result should be displayed in the same row like below (first row is the attribute names). That is, find the number of students in each program. Hint: cross-product.
6. Find the publisher(s) who has at least 10 different books adopted by courses offered by the CS department but NONE adopted by the STAT department.
7. Find students who have earned at least 5 credits (in 2A or beyond) but yet to pass the first communication requirement. Assume each course is 0.5 credit and the only courses in the first communication requirement are ENGL109 and SPCOM100. Note that a student might have taken the same course twice and passed both time. This counts as 0.5 credit, not 1 credit. Say John took MATH135 twice and got 50 and 65.John earned 0.5 credit from MATH135. Jane took MATH135 and got 55 and is currently taking it again in F24. Jane has also earned 0.5 credit from MATH135. The result should display student name, SID and E-mail.
8. For students who are in the same major, find the pair(s) who took classes together (same course in the same term) the most frequently (across all majors). The result should display their SIDs. Note that (A, B) and (B, A) is the same pair and only one should be displayed. The result should show their SIDs.
9. Find the Math/BA student(s) with the second highest average. The result should display their name, SID and major.
10. Find the most difficult course(s) in S24. The difficulty of a course is estimated by a weighted factoring formula: (Number of fail * 0.6 + Number of WD * 0.2 – Class Average * 0.2). The higher this number, the more difficulty it is. You can use arithmetic in the projection operator like: Say we have R(AB), R1(c) ← πA−B(R). This produces a new relation R1 with a column C. Each row in C is the difference between A and B in R. The result should display the course#.
Marking (30 points)
Each question is worth 3 points and is marked according to the following scheme:
• Good (no mistake or very small one): 3 points;
• Ok (mostly correct): 2 points;
• Poor (mostly wrong but has some merit): 1 point;
• Wrong (totally off or no answer): 0 point.
Submission
The assignment file format should be PDF or JPG/JPEG (picture). It should be uploaded to the drop box on Learn: Submit -> Dropbox -> Assignment #1. The submission deadline is Oct 2nd, 1pm, Waterloo local time.
The Enrollment Database
The following relations for a database keep track of student enrollment in courses and the books adopted for each course.
Assumptions:
• Examples of Course#: CS338, MATH135;
• Examples of Dept: CS, ACTSC, AMATH, ENGL;
• Examples of Major: CS, Math/BA, SE, STAT;
• Examples of term: F24, W24;
• For students who are taking a course but yet to receive a grade, the grade will be recorded as NA;
• For students who enroll into a course but drop it midway through, the grade will be recorded as WD;
• A grade below 50 is a fail;
• Publisher is unique.
Note that in some question(s), you might consider using the Count-Distinct(a) aggregation function instead of Count(a). The former counts the number of distinct values in attribute a.
Student(SID, Name, Major, E-mail, Bdate)
Course(Course#, Cname, Dept)
Enroll(SID, Course#, Term, Grade, InstructorID)
Textbook(Course#, Term, ISBN)
Book(ISBN, Title, Publisher, Author)