CS122 HW3

Homework 3
CS122, Summer 2007

Due: Tuesday, August 21

Please use a drawing software such as MS PowerPoint or OpenOffice Impress to draw the ER diagrams, and upload all your files to CSNS. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please turn in your work on time.

1. The Human Resource Database

(a) (10pt) Draw an ER diagram for the Human Resource database, excluding the Job_History table. For simplicity, you don't have to draw all the attributes for each entity set - just the key attribute(s). And make sure that you clearly indicate whether a relationship is many-to-many, many-to-one, or one-to-one.

(b) (5pt) Identify which entity set / relationship is converted to which table.

2. Design a database to keep track of the information about students, teachers, and classes. In particular:

Each student has a name and a unique student ID.
Each teacher has a name and a unique faculty ID.
Each class has a course code (e.g. CS122), name (e.g. Using Relational Database and SQL), quarter (e.g. Summer), year (e.g. 2007), and section number (e.g. there could be multiple sections of CS122 offered in the same quarter).
Each class is taught by one teacher.
Students take classes, and earn a letter grade of A, B, C, D, or F for each class they take.

(a) (10pt) Draw the ER diagram for this database. Don't forget to underline the key attribute(s) of each entity set, and clearly indicate whether a relationship is many-to-many, many-to-one, or one-to-one.

(b) (5pt) Convert the ER diagram to tables. For each table, simply give the table name and the column names, e.g. students(student_id,name).

(c) (+5pt) Suppose we need to write queries to calculate the GPA of the students. Make the necessary changes, if any, to your ER diagram to support these types of queries. Then write a query in SQL to calculate the GPA of a student with id=1000, using the tables converted from your ER diagram.