Question

You have the following information about opera lovers, their operas, and the attendance experiences:

•       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.

Construct a normalized database using this information.

You can assume all tickets for the same opera cost the same.  For example, all tickets for Madame Butterfly are $25.

Answer 1

The information in tabular form is (attributes are named as Person, Opera, Date, Ticket) :

Person	Opera	Date	Ticket
Susie	Madame Butterfly	1/11	25
	La Traviata	10/3	100
	Carmen	10/10	30
Lee	Madame Butterfly	10/21	25
Jackson	Carmen	5/12	30
	La Nozze di Figaro	1/20	100
Bobby	Carmen	1/26	30
	La Boheme	8/14	50
Margaret	La Nozze di Figaro	2/22	100
Margaret, Jackson	Carmen	5/12	30
Ulysses	Rigoletto	10/21	35

To convert it into 1NF, make all instances atomic (only one value per cell and no merged cells allowed). The resulting database is:

Person	Opera	Date	Ticket
Susie	Madame Butterfly	1/11	25
Susie	La Traviata	10/3	100
Susie	Carmen	10/10	30
Lee	Madame Butterfly	10/21	25
Jackson	Carmen	5/12	30
Jackson	La Nozze di Figaro	1/20	100
Bobby	Carmen	1/26	30
Bobby	La Boheme	8/14	50
Margaret	La Nozze di Figaro	2/22	100
Margaret	Carmen	5/12	30
Jackson	Carmen	5/12	30
Ulysses	Rigoletto	10/21	35

To convert in 2NF form, remove all dependencies. It can be seen that 'Ticket' is dependent on 'Opera'. The result is two tables:

Person	Opera	Date
Susie	Madame Butterfly	1/11
Susie	La Traviata	10/3
Susie	Carmen	10/10
Lee	Madame Butterfly	10/21
Jackson	Carmen	5/12
Jackson	La Nozze di Figaro	1/20
Bobby	Carmen	1/26
Bobby	La Boheme	8/14
Margaret	La Nozze di Figaro	2/22
Margaret	Carmen	5/12
Jackson	Carmen	5/12
Ulysses	Rigoletto	10/21

Opera	Ticket
Madame Butterfly	25
La Traviata	100
Carmen	30
La Nozze di Figaro	100
La Boheme	50
Rigoletto	35

Since there are no transitive functional dependencies, this is also 3NF form (normalized form).