MIDTERM
CS422, Fall 2012
[Part I] Please write your answers on a piece of paper and submit it at the end of the exam.
1. (35pt) Design a database for a website like stackoverflow.com. Specifically:
- Users can post questions on the website. Each question can be tagged with some keywords.
- Users can give answers to the questions posted on the website, and one of the answers to a question can be selected as the correct answer for the question.
- Each user can give a positive vote (i.e. 1) or a negative vote (i.e. -1) to a question (or an answer) depending on whether the user thinks the question (or the answer) is helpful.
(a) Draw an ER diagram for this database.
(b) Convert your ER diagram to a relational schema.
[Part II] This part is to be completed on a computer using the sample database University (university-create.sql, university-drop.sql). Please put all the queries, stored procedures, and triggers in an SQL script file midterm.sql and upload it CSNS at the end of the exam. Note that file uploading will be disabled automatically after 11:30AM, so please turn in your work on time.
2. (15pt) Complete the following queries:
(a) List the average section size in each year. The results should be ordered by average section size in descending order.
(b) List the names of the professors who have taught Computer Science courses for at least two consecutive years. The results should not contain duplicates.
(c) Find the GPA of the student Joe. Note that GPA is calculated as sum(grade_point_value*units)/sum(units). For example, if a student got an A in a 4-unit class and a B in a 2-unit class, the GPA of the student should be (4.0*4+3.0*2)/(4+2)=3.67.
3 (30pt)
(a) Write a stored function round_to_grade() that takes a number between 0 and 4, and returns the letter grade whose grade point value is closest to the number. For example, round_to_grade(3.75) should return 'A-', while round_to_grade(3.9) should return 'A'. If a number is at the exact middle of two grades, we round it up to the higher grade, e.g. round_to_grade(3.85) should return 'A'. For any number outside the range of 0 to 4, the function should raise an exception.
(b) Use the function round_to_grade() to write a query that lists the names of the professors and the average grade they gave in their classes.
4. (20pt) Write a trigger enrollment_check to enforce the constraint that only students who major in Computer Science can take the class Compilers.