Question

Mr. Bob Dillon is a collector of china pieces. He needs a database is to store data about his collection. Each piece in this collection is to be identified by a unique ID value. Bob knows of several agents that work on getting these pieces shown in prestigious exhibitions. Each exhibition has an ID, a name, a venue, a start date and an end date. For all items shown, there is a deadline for returning them to the item owners. If an exhibitor would like to showcase a particular piece in a particular exhibition, he/she works with an agent and pays the item owner a flat fee for “borrowing” that piece to show in that exhibition. Each agent has a commission rate and charges the owner for the commission. Since these are expensive pieces, such a deal is by the piece. In other words, each deal is for one piece to be shown in one exhibition. If there are two pieces in the same show, they need to make two deals. Once such a deal is confirmed, Bob would like to assign it a unique Showing ID for tracking it.    Bob wants to make a database to keep track of the collection, the exhibitions in which he is interested, the agents that can handle these deals, and the showcase deals. He would like the database to store data for two years, that is, from January 1 of the previous calendar year to date.

1.) Identify all of the Entities and the attributes of each entity

2.) Draw the ERD to represent the conceptual database design Use the Crow’s foot model for the ERD. Be sure to include all of the business rules.

3.) Define all of the relationships types that are necessary to clearly identify the necessary queries that Bob, the end user wants.

4.) Be sure to show any necessary cardinalities and constraints.