Consider the entertainment agency database schema, below. 

The diagram uses slightly different conventions than what I described in class.  In particular:

• It uses yellow key icons to represent primary keys (instead of underlining), and
• Foreign keys are represented with lines that start with an infinity sign and end with a "1".  My diagrams instead use an arrow that starts at the foreign key and points to the referenced column.

Following the conventions described in class, the diagram can be rewritten as follow:

Let's also assume that all of the columns are required (there can be no NULL values).

The questions below refer to the diagram above.

1. How do we know that this schema design requires exactly one customer per engagement?
2. How do we know that a single customer can belong to many different engagements?
3. If an entertainer has three different styles, how many rows for her would be present in the Entertainers table, and how many rows for her in the Entertainer_Styles table?
4. Which of these tables are highest-level parents?
5. Why is there a separate Musical_Preferences table?  In other words, why not just create a PreferredStyle column in the Customers table?
6. Why is a composite primary key used in the Entertainer_Styles table (instead of a single-column primary key)?
7. What are the many-to-one relationships in this schema (if any)?
8. What are the many-to-many relationships in this schema (if any)?
9. What are the one-to-one relationships in this schema (if any)?
10. How would we change this schema to prevent Entertainer Stage Names (EntStageName) from being repeated by different entertainers?
11. Let's say we wanted to remove the AgentID column from the Agents table and instead use AgtPhoneNumber as the primary key in this table.  What else would have to change in the schema?  What data would have to change if an Agent suddenly changed his/her phone number?
12. Let's change the schema as follows.  Assume that most engagements are free and therefore they have no ContractPrice, CustomerID, nor AgentID.  However, some engagements do have these three values.  What would be the best way to represent this?
    
    
13. Draw a schema diagram (like the hand-drawn one above) to represent a database for a medical office.  You should represent staff, patients, appointments, and diagnoses.  You should somehow associate:
    • diagnoses and patients
    • patients and appointments
    • staff and appointments
      
      
14. Draw a schema diagram to represent a movie database (like IMDB).  You should represent movies, actors, user reviews, and users.