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:

(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.