You operate a music school that specializes in piano lessons. You have a staff of piano teachers, for each of whom you want to store a first name, middle initial, last name, street address, city, state, zip, date hired, and hourly lesson rate (for example, $50 per hour). Your clients consist of households, each of which may have multiple students. For each student, you want to store a first name, middle initial, last name, date of birth, and current proficiency level (for example, beginner, intermediate, or advanced). All students from the same household have the same street address, city, state, zip, and credit card information consisting of a card number, expiration date, and security code. Lessons are given at your school in private rooms, each with its own piano. For each room, you want to store the room number, the type of piano (upright, grand, baby grand, and so on), the piano manufacturer, and the year the piano was made. Each lesson involves one student and one teacher meeting in one room; in addition to recording the student, teacher, and room, you also want to store the starting date/time and ending date/time of each lesson. Assume that you do have access to a suitable zip code table.

Design an appropriate database, drawing an ER diagram and writing a database design outline.

Assume that any address information specified is in US format, consisting of fields for street address, city, two-letter state code, and zip code. Unless stated otherwise in an individual problem, assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent.

1. An ER diagram contains all entities, relationships and cardinality constraints. No attribute is needed. 2. Design of all tables using outline notation. Must contain tables names, attributes, primary keys and necessary foreign keys. 3. Your ER diagram and table outline notation should be consistent with each other.