Name: __________________________________________________________________
Version A
Part II: Problems (120 Points)
Problem 1 (50pts):
1. The dependency diagram in Figure 1 below indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.
Figure 1: Book royalty dependency diagram
Given the dependency diagram shown in Figure 1 above, answer questions 1a, 1b and 1c.
a. Identify each of the indicated dependencies.
Version A
b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.
c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.
Version A
Problem 2 (70pts):
Instructions:
Draw the Crow’s Foot ERD for the Cirque de la Lune application described below.
Cirque de la Lune
Cirque de la Lune is a high quality entertainment company and one of the largest theatrical producers in the world. Originally composed of a dozen street performers, it now employs many performers who bring wonder and delight to people through incredible circus performing acts. You have been hired by Cirque to create a database to manage and improve their businesses processes.
The foundation of Cirque are their amazing shows, which are carefully planned to thrill audiences with performances they’ve never seen before. For the database, a show will have a name, theme, the date it was established, and the date it will expire (or has expired.) Shows travel across the world, and have certain locations where they will be performed. A performance of a show is called an event. A show will have (or has had) many events at many locations. A location consists of the location name, address, phone, and capacity. A location may be the site of many events, but an event has one and only one location. It is also critical that the date of an event be recorded in the database.
The Cirque performers, who have one specialty, are divided into teams. A team has one or more performers, but a performer only belongs to one team. The team has a name and a theme. A specialty has a name, type, and usually a piece of equipment that is required. Specialties may have one or more performers able to perform that specialty. Performers will be performing at several different events of a show, and of course a show has many performers who can do the amazing acts.
Cirque also wishes to keep track of its customers’ information, such as their first and last name, phone number, and email. Customers may attend many events, and of course the events have so many customers in attendance, that they are usually sold out for every event.
The owners of Cirque are optimistic that a well-designed database system will substantially improve their business processes. They are depending on you to analyze their business to identify areas that can benefit from such a system, and to design and develop the system itself.
