Example: The Talent Agency

Suppose you have been asked to build a database for a talent agency that books musical bands into clubs/Restaurants. The agent needs a database to keep track of the agency’s transactions and to answer day-to-day questions. For example, a club manager often wants to know which bands are available on a certain date at a certain time, or wants to know the agent’s fee for a certain band. The agent may want to see a list of all band members and the instrument each person plays, or a list of all bands that have three members.
Suppose that you have talked to the agent and have observed the agency’s business operation. You conclude that your database needs to reflect the following facts:

1. A booking is an event in which a certain band plays in a particular club on a particular date, starting and ending at certain times, and performing for a specific fee. A band can play more than once a day. The Heartbreakers, for example, could play at the East End Cafe in the afternoon and then at the West End Cafe on the same night. For each booking, the club pays the talent agent. The agent keeps a five per cent fee and then gives the remainder of the payment to the band.

2. Each band has at least two members and an unlimited maximum number of members. The agent notes a telephone number of just one band member, which is used as the band’s contact number. No two bands have the same name or telephone number.

3. Band member names are not unique. For example, two bands could each have a member named Sally Smith.

4. The agent keeps track of just one instrument that each band member plays. For the purpose of this database, “vocals” are considered an instrument.

5. Each band has a desired fee. For example, the Light metal band might want $700 per booking, and would expect the agent to try to get at least that amount.

6. Each nightclub has a name, an address, and a contact person. The contact person has a telephone number that the agent uses to call the club. No two clubs have the same name, contact person, or telephone number. Each club has a target fee. The contact person will try to get the agent to accept that fee for a band’s appearance.

7. Some clubs feed the band members for free; others do not.

Before continuing with this tutorial, you might try to design the agency’s database on your own. Ask yourself: What are the entities? Recall that business databases usually have Customer, Employee, and Inventory entities, as well as an entity for the event that generates revenue transactions. Each entity becomes a table in the database. What are the relationships among the entities? For each entity, what are its attributes? For each table, what is the primary key?