SELECT name FROM Professor p JOIN Section s ON p.profNo = s.profNo NATURAL JOIN Roster r GROUP BY r.sectionNo, p.name HAVING count(*) > 30
SELECT courseNo, title FROM Catalog c WHERE department = 'Computer Science' AND courseNo IN (SELECT courseNo FROM Section);
SELECT c.courseNo, title FROM Catalog c NATURAL JOIN Section s WHERE department = 'Computer Science';
SELECT c.courseNo, title FROM Catalog c, Section s GROUP BY s.courseNo WHERE department = 'Computer Science';
SELECT c.courseNo, title FROM Section s, Course c WHERE department = 'Computer Science';
SELECT c.courseNo, title FROM Course c WHERE department = 'Computer Science' AND (SELECT courseNo FROM Section s) IN c;
ΠProfessor.name((σcourseNo='ECE351' Section) ⋈ Professor ⋈ (σbuilding='Min Kao'Room))
Branch 1..1 ⟶ 1..* Staff 0..1 ⟶ 0..* PropertyForRent
Rooms ------------------------⟶ Sections ----------------------------⟶ Exams 1 host 2 3 take 4The numbers shown above represent missing multiplicities for the relationships. You may assume that:
For each number, enter the letter associated with its multiplicity:
a. 1..1 1) (a) 1..1 b. 0..1 c. 1..0 d. 1..* 2) (f) 0..* e. *..* f. 0..* g. *..0 3) (d) 1..* h. *..1 i. 1..5 j. 5..1 4) (i) 1..5 k. *..5
(16 points) Normalization. Suppose you are given the following relation named Exam:
ExamId | Section | RoomId | RoomNo | Date | TimeFrom | Duration | AnswersDate | AnswersTime |
---|---|---|---|---|---|---|---|---|
271 | 1011 | R101 | MK524 | 2014-10-08 | 13:00 | 0:55:00 | 2014-10-15 | 08:00:00 |
271 | 4031 | R102 | MK404 | 2014-10-08 | 14:00 | 0:55:00 | 2014-10-15 | 08:00:00 |
271 | 5063 | R101 | MK524 | 2014-10-09 | 17:00 | 0:55:00 | 2014-10-15 | 08:00:00 |
273 | 4031 | R102 | MK404 | 2014-11-10 | 14:00 | 1:30:00 | 2014-11-01 | 12:00:00 |
... | ... | ... | ... | ... | ... | ... | ... | ... |
Assume that the table has the following functional dependencies:
ExamId, Section → Date ExamId → Duration, AnswersDate, AnswersTime Section → RoomId, TimeFrom RoomId → RoomNoAnswer the following questions about this relation.
Queries. Write the following queries in the requested language.
SELECT name, gpa FROM Student WHERE gpa > 3.5;
SELECT DISTINCT name FROM Student s, Transcript t WHERE s.studentNo = t.studentNo AND t.courseNo = 'CS102' AND t.grade IN ('A', 'B', 'C'); or SELECT DISTINCT name FROM Student s NATURAL JOIN Transcript t WHERE t.courseNo = 'CS102' AND t.grade IN ('A', 'B', 'C');
SELECT profNo, count(*) as numSections FROM Section GROUP BY profNo ORDER BY profNo;
{ S.name | Student(S) ∧ (∃R)(Roster(R) ∧ S.studentNo = R.studentNo ∧ R.sectionNo = '30189')}
Best Solution: Section is a teaching relationship among a professor, a course, and a room and a Roster is a relationship between a section and a student. So technically, the relationship is: A professor teaches students in a course in some room. Transcript is also a many-many relationship between courses and students.
Fine Solution: It's not easy to see that Section/Roster are part of the same relationship so I accepted a solution that showed Section as an entity and Section as being part of the "teaches" relationship.
Worst Solution: Showing Section as an entity that has three binary relationships with professor, catalog, and room obscures the relationship among the three entities.
Solution