CS122 HW3

Homework 3
CS122, Fall 2011

Due: Wednesday, November 30

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. 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 for a restaurant to keep track of the information about menu items, servers (i.e. waiters and waitresses), and orders. In particular:

Each menu item has a unique name (e.g. Kung Pao Chicken) and a price.
Each server has a unique id, a name, gender, and a hire date (i.e. the date when the server was hired).
Each order has a unique order number. An order is served by one server, and includes one or more menu items. For each order the database also keeps a timestamp which is the time when the total of the order is charged.

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

(b) (10pt) Convert the ER diagram to tables. For each table, simply give the table name and the column names.

(c) (+5pt) Assume a server receives 15% tip for each order he or she serves. Using the tables you created in (b), write a query to list the names of the servers and the amount of tip they received. The results should be ordered by the amount of tip in descending order. Note that you will only receive credit for this exercise if your table design in (b) is correct.