For this assignment you will solve an exercise concerning Chapter 4 and an exercise from the end of Chapter 5 from our textbook.

Problem 1.) Professor Smith would like to assign m projects to n students in a database class. Each project can be described by its name, description, deadline and status (completed or in progress); each student has a student id and a name. Students can work in groups of several persons on one of the m projects. Different groups will take different projects, each group will take exactly one project (assume more projects than groups, so some projects will have no group assigned), and each student participates in exactly one group. After the project for a group is finished, a grade for the project is determined and given to all students in the group. Assume each group is identified by a unique group name.

• (a) Draw an E-R diagram for the system, in particular, use arrows or thick lines to represent constraints appropriately. Write down your assumptions if you need to make any. Your drawing should be done on a computer, not free hand on a piece of paper.
• (b) Translate the above E-R diagram to a relational model, in particular, specify your primary key and foreign key constraints clearly. That is, give SQL CREATE statements that define the tables you need.
• (c) Write an SQL statement to create a view that gives the project name and group name of completed projects.

Problem 2.) Do Exercise 5.10, parts (a) - (d), page 188-189.

Turn in a zip file containing the following files. For Problem 1, your E-R diagram document and an sql script that implements the database. For Problem 2, an sql script file that contains both the relational algebra expression and the SQL code for each part of the problem. Write the relational algebra expression in a comment (above the SQL code) and use operator names (like join, union, etc) instead of the special symbols used in the textbook. This assignment is due Tuesday, September 30.