Assignment 3.1 This assignment is worth 70 points

Assignment 3.1

This assignment is worth 70 points and the full rubric is on the bottom of the page.

It’s time to go to the opera!  You are going to use MySQL Workbench’s EER tool to create an Entity-Relationship diagram for the Civic Center Opera House.  You will then engineer that to produce MySQL script(s) which will create and load the database schema with the information below.  Finally, you will query your database to prove it is working.

Here is the relevant information.

·         Susie attended Madame Butterfly and spent $25 on the ticket for the 1/11 performance, and then went to see La Traviata on 10/3 for $100.  She also went to see Carmen for $30 on 10/10.

·         Lee attended Madame Butterfly and spent $25 on the ticket for the 10/21 performance.

·         Jackson attended Carmen and spent $30 on the ticket for the 5/12 performance.  He also went to see La Nozze di Figaro on 1/20, and paid $100 for the ticket.

·         Bobby attended Carmen and spent $30 on the ticket for the 1/26 performance, and then went to see La Boheme on 8/14 for $50.

·         Margaret attended La Nozze di Figaro and spent $100 on the ticket for the 2/22 performance, and then went (with Jackson) to see Carmen on 5/12 for $30.

·         Ulysses attended Rigoletto on 10/21 and spent $35 on the ticket.

·         Each ticket has an option to have a “Backstage Pass” option.  There is no additional cost associated with this, just a marking on the ticket that the backstage pass was selected.

·         Backstage passes were selected by Margaret at all of her operas, and by Jackson when he went with Margaret to Carmen on 5/12.   No other tickets chose a backstage pass.

Additional assumptions you should make:

·         Assume all tickets for the same opera cost the same.  For example, all tickets for Madame Butterfly are $25.

·         All operas occurred last year.  For example, if it is 2020 now, and the date says “November 11,” assume it was November 11, 2019.

Normalize this database and implement in MySQL Workbench using this information. 

Name your database schema “CivicCenter.”  Please try to use this exact name to make it easier to grade.  It is not case-sensitive so it’s OK to use “civiccenter” or CIVICcenter.”  Do not use “Civic_Center” or “civic-center.” 

You may use as many or as few tables as you see fit. 

Your deliverables are:   

1.       “XXX_Civiccenter_ERD.pdf” where XXX are your initials.  This is a pdf of an entity-relationship diagram made using MySQL Workbench’s modeling tool (you can find this under File Menu -> Export.) 

a.       Entity-relationship diagrams made using other tools will not earn full points.

b.       The diagram should show all entities you use (it’s OK to use more than one page if you need to, and you can cobble together screenshots if you need.)

c.       All entities should show all fields and keys; expand the boxes or rearrange them if you need to.

d.       All relationships and cardinalities should be easily visible

e.       All relationships should have text sentence(s) labeling them.  For example, if this were the pet database, you might write, “Each pet must have one and only one owner.  Each owner may have zero or more pets.”