FINAL
CS422, Summer 2004
Please turn in a hard copy of your solutions at the end of the exam,
and upload all your files using the turnin server.
Note
that both hard copy and electronic copy are required, and file
uploading will be disabled at 10:10PM.
Consider a database ClassInfo which consists of following
relations:
- students ( sid, name, dropped ) - student ID and name,
and
whether the student has dropped the class.
- assignments ( lname, sname, total, due ) - long name,
short name, and total points for an assignment, and the date and time
when the assignment is due.
- A long name is a descriptive name for an assignment, e.g.
"Homework 1" or "Midterm 2", which is usually used for display purpose.
- The short name of an assignment, such as "hw1" or "midterm2",
is for internal use.
- The name of an assignment, whether it's the long name or the
short name, is unique.
- Note that exams are considered as assignments, too. In
particular, the short name for a midterm always starts with "midterm",
and the
short name for the final exam is always "final".
- files ( name, size, sid, assignment ) - the name and
size of a file submitted for an
assignment, and the student who submitted the file. Note that a student
may submit multiple files for an assignment.
- sid is the ID of the student.
- assignment is the short name of the assignment.
- grades ( sid, assignment, points ) - the
points received by a student for an assignment.
[Schema Creation] (20pt) Create the relations in a DBMS using the
CREATE TABLE statement. Use proper data types and column/table
constraints.
[Triggers] (40pt) Write triggers to enforces the following constraints:
- To prevent a Denial-of-Service attack, the total size of the
files submitted for an assignment by each student is limited to 2MB.
- No turnin accepted after the due time.
[Functions] (30pt) Implement the following functions:
- grade_percentage returns the grade
percentage of a student for the course. The grade percentage is
calculated as
0.3*midterm_points/midterm_total +
0.3*final_points/final_total +
0.4*other_assignment_points/other_assignments_total
- course_grade returns the grade of a student
for the course. The course grade is calculated as follows:
'A', if grade_percentage >= 0.85
'B', if grade_percentage < 0.85 and grade_percentage >=
0.7
'C', if grade_percentage < 0.7 and grade_percentage >= 0.6
'D', if grade_percentage < 0.6 and grade_percentage >=
0.45
'F', if grade_percentage < 0.45
[Queries] (20pt) Write a query for each of the following problems:
- List the student IDs, names, and course grades, ordered by course
grade with the highest grade first.
- Find the highest, lowest, and average grade of hw4.
- Find the assignments for which at least one third of the students
have not turned in any files.
- Find the ID of the student whose hw1 grade is closest to
83.
[JDBC] (extra credit 30pt) Write a Java program TriggerTest.java
to test
your
triggers. In particular, the program perform the following operations:
- Inserts two assignments, Homework 1 (hw1) and Homework
2 (hw2), into the assignments table. Homework 1 is due at 11:59PM,
August 26, with 100 points total, and Homework 2 is due
at 11:59PM, September 1, with 90 points total.
- Read the tuples from tuples.txt and
insert them into the files table. For each tuple, prints out
whether it is
inserted
successfully, and if not, prints out the error message sent back by the
database server.
If you forgot how to do file I/O in Java (or don't know how to do it
in the first place), you may hard-code the tuples in your Java program
as an array, and lose 5pt for doing that.