FINAL

Please put all the SQL statements in a script file final.sql and upload it to CSNS. The script must run on CS3 without any syntax errors; otherwise you will lose at least half of the credit. Note that file uploading will be disabled automatically after 10:30AM, and late submission will not be accepted.

Instructors at a dance studio give both private and group dancing lessons. A student would pay $80 for a private lesson, or $25 for a group lesson. Each private lesson is taught by only one instructor, but each group lesson will have at least two instructors and maybe more. The instructors keep 60% of the fees paid by the students, and the other 40% go to the studio. For a group lesson, the instructor's 60% is split evenly among the instructors. For example, for a group lesson of 12 students and 2 instructors, each instructor will get $90.

The following is an ER diagram for a database designed to keep track of the information about the instructors, the students, and the lessons. (click on the image to see a larger version):

[ER Diagram]

1. (12pt) Convert the ER diagram into tables, and use CREATE TABLE statements to create these tables in your database. Make sure that you use appropriate data types and data integrity constraints.

2. (5pt) Populate the tables with some test data so that each table has at least two rows.

3. (18pt)  Complete the following queries: