Problem 1: Analyze and Create Database from Dependency Diagram ✓ Solved

```html

Problem 1 (50pts): 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.

a. Identify each of the indicated dependencies.

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.

Problem 2 (70pts): Instructions: Draw the Crow’s Foot ERD for the Cirque de la Lune application described below. Cirque de la Lune is a high quality entertainment company and one of the largest theatrical producers in the world.

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.

Paper For Above Instructions

The aim of this paper is to address the requirements of the final exam for the Business Database Applications course. Specifically, we will explore Problem 1 and Problem 2, creating and assessing databases that follow the principles of normalization, as well as design an Entity-Relationship Diagram (ERD) using the Crow's Foot notation.

Problem 1: Dependencies and Database Normalization

In the dependency diagram provided, it is indicated that the payment of royalties is dependent on several attributes: the author, the book, and the edition. Thus, the dependencies can be summarized as follows:

  • Royalty payment is dependent on Author ID, Book ID, and Edition.

Identifying these dependencies is crucial for normalization. The first step is to ensure that our database is in Second Normal Form (2NF). To achieve 2NF, a relation must be in First Normal Form (1NF) and all non-key attributes must be fully functionally dependent on the primary key.

Creating a 2NF Database

To create a 2NF compliant database, we can break down the components into different tables as follows:

  • Authors Table: AuthorID (Primary Key), AuthorName

  • Books Table: BookID (Primary Key), BookTitle, AuthorID (Foreign Key)

  • Editions Table: EditionID (Primary Key), BookID (Foreign Key), RoyaltyAmount

This setup ensures that all non-key fields are dependent solely on their respective primary keys and not on any other fields, thus satisfying 2NF.

Creating a 3NF Database

To achieve Third Normal Form (3NF), we need to eliminate any transitive dependencies. In our case, the edition table's reliance on BookID can be scrutinized. To keep this in 3NF:

  • Authors Table: AuthorID (Primary Key), AuthorName

  • Books Table: BookID (Primary Key), BookTitle, AuthorID (Foreign Key)

  • Editions Table: EditionID (Primary Key), BookID (Foreign Key), RoyaltyAmount, EditionName

Now each attribute is non-transitively dependent on the primary key, thus satisfying the conditions for 3NF.

Problem 2: Crow’s Foot ERD for Cirque de la Lune

The second part of the assignment is to create a Crow’s Foot ERD based on Cirque de la Lune's requirements. The following entities and relationships will be constructed:

  • Shows: ShowID (Primary Key), ShowName, Theme, EstablishedDate, ExpiryDate

  • Events: EventID (Primary Key), ShowID (Foreign Key), LocationID (Foreign Key), EventDate

  • Locations: LocationID (Primary Key), LocationName, Address, Phone, Capacity

  • Performers: PerformerID (Primary Key), PerformerName, SpecialtyID (Foreign Key), TeamID (Foreign Key)

  • Teams: TeamID (Primary Key), TeamName, Theme

  • Specialties: SpecialtyID (Primary Key), SpecialtyName, Type, Equipment

  • Customers: CustomerID (Primary Key), FirstName, LastName, PhoneNumber, Email

  • Attendances: AttendanceID (Primary Key), EventID (Foreign Key), CustomerID (Foreign Key)

In this design, each 'Show' can have multiple events, which are tied to specific locations. Each performer belongs to one team and can have a specialty. The 'Attendances' table links customers to the events they attend.

Conclusion

The exercises detailed in this paper include identifying dependencies from the provided diagrams and creating two databases—one in 2NF and another in 3NF—while also constructing a Crow's Foot ERD for the Cirque de la Lune entity. Through this process, we can ensure that the design is not only functional but also optimized for data efficiency and integrity. This framework lays a robust foundation for the upcoming management of the system.

References

  • Elmasri, R., & Navathe, S. (2015). Fundamentals of Database Systems. Pearson.

  • Rob, P., & Coronel, C. (2016). Database Systems: Design, Implementation, & Management. Cengage Learning.

  • Coronel, C., & Morris, S. (2016). Database Systems: Design and Implementation. Cengage Learning.

  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2017). Modern Database Management. Pearson.

  • Teorey, T. J., Lightstone, S., & Nadeau, T. (2011). Database Modeling & Design: Logical Design. Morgan Kaufmann.

  • Date, C. J. (2012). An Introduction to Database Systems. Pearson.

  • Korth, H. F., & Silberschatz, A. (2019). Database System Concepts. McGraw-Hill.

  • Connolly, T. M., & Begg, C. E. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.

  • Grover, V., & Malhotra, M. (2016). Business Databases: Applications and Management. Springer.

  • Roberts, J. (2018). Entity-Relationship Modeling: Foundations of Database Design. Academic Press.

```