Case Description:

FAME (Forondo Artist Management Excellence) Inc. is an artist management company that represents classical music artists (only soloists) both nationally and internationally. FAME has more than 500 artists under its management, and wants to replace its spreadsheet-based system with a new state-of-the-art computerized information system.

Their core business idea is simple: FAME finds paid performance opportunities for the artists whom it represents and receives a 1030 percent royalty for all the fees the artists earn (the royalties vary by artist and are based on a contract between FAME and each artist). To accomplish this objective, FAME needs technology support for several tasks. For example, it needs to keep track of prospective artists. FAME receives information regarding possible new artists both from promising young artists themselves and as recommendations from current artists and a network of music critics. FAME employees collect information regarding promising prospects and maintain that information in the system. When FAME management decides to propose a contract to a prospect, it first sends the artist a tentative contract, and if the response is positive, a final contract is mailed to the prospect. New contracts are issued annually to all artists.

FAME markets its artists to opera houses and concert halls (customers); in this process, a customer normally requests a specific artist for a specific date. FAME maintains the artists calendars and responds back based on the requested artists availability. After the performance, FAME sends an invoice to the customer, who sends a payment to FAME (please note that FAME requires a security deposit, but you do not need to capture that aspect in your system). Finally, FAME pays the artist after deducting its own fee.

Currently, FAME has no IT staff. Its technology infrastructure consists of a variety of desktops, printers, laptops, tablets, and smartphones all connected with a simple wired and wireless network. A local company manages this infrastructure and provides the required support.

E-mail from Martin Forondo, Owner

Martin Forondo, the owner of FAME, has commissioned your team to design and develop a database application. In his e-mail soliciting your help he provides the following information:

My name is Martin Forondo, and I am the owner and founder of FAME. I have built this business over the past thirty years together with my wonderful staff and I am very proud of my company. We are in the business of creating bridges between the finest classical musicians and the best concert venues and opera houses of the world and finding the best possible opportunities for the musicians we represent. It is very important for us to provide the best possible service to the artists we represent.

It used to be possible to run our business without any technology, particularly when the number of the artists we represented was much smaller than it currently is. The situation is, however, changing, and we seem to have a need to get some technical help for us. At this moment we have about 500 different artists and every one of them is very special for us. We have about 20 artist managers who are responsible for different numbers of artists; some of them have only 10, but some manage as many as 30 artists. The artist managers really keep this business going, and each of them has the ultimate responsibility for the artists for whom they work. Every manager has an administrative assistant to help him or her with daily routine workthe managers are focusing on relationship building and finding new talent for our company. The managers report to me but they are very independent in their work, and I am very pleased that I only very seldom have to deal with operational issues related to the managers work. By the way, I also have my own artists (only a few but, of course, the very best within the company, if I may say so).

As I said, we find performance opportunities for the artists and, in practice, we organize their entire professional livesof course, in agreement with them. Our main source of revenue consists of the royalties we get when we are successful in finding a performance opportunity for an artist: We get up to 30 percent of the fee paid to an artist (this is agreed separately with every artist and is a central part of our contract with the artist). Of course, we get the money only after the artist has successfully completed the performance; thus, if an artist has to cancel the performance, for example, because of illness, we will not get anything. Within the company the policy is very clear: A manager gets 50 percent of the royalties we earn based on the work of the artists he or she manages, and the remaining 50 percent will be used to cover administrative costs (including the administrative assistants salaries), rent, electricity, computer systems, accounting services, and, of course, my modest profits. Each manager pays their own travel expenses from their 50 percent. Keeping track of the revenues by manager and by artist is one of the most important issues in running this business. Right now, we take care of it manually, which occasionally leads to unfortunate mistakes and a lot of extra work trying to figure out what the problem is. It is amazing how difficult simple things can sometimes become.

When thinking about the relationship between us and an artist whom we represent, it is important to remember that the artists are ultimately responsible for a lot of the direct expenses we pay when working for them, such as flyers, photos, prints of photos, advertisements, and publicity mailings. We dont, however, charge for phone calls made on behalf of a certain artist, but rather this is part of the general overhead. We would like to settle the accounts with each of the artists once per month so that either we pay them what we owe after our expenses are deducted from their portion of the fee or they pay us, if the expenses are higher than a particular months fees. The artists take care of their own travel expenses, meals, etc. From my perspective, the most important benefit of a new system would be an improved ability to know real-time how my managers are serving their artists. Are they finding opportunities for them and how good are the opportunities, what are the fees that their artists have earned and what are they projected to be, etc. Furthermore, the better the system could predict the future revenues of the company, the better for me. Whatever we could do with the system to better cultivate new relationships between promising young artists, it would be great. I am not very computer savvy; thus, it is essential that the system will be easy to use.

Case Study (Part TWO):

Case Description

Martin was very impressed with your project plan and has given you the go ahead for the project. He also indicates to you that he has e-mails from several key staff members that should help with the design of the system. The first is from Alex Martin (administrative assistant to Pat Smith, an artist manager). Pat is on vacation and Martin has promised that Pats perspective will be provided at a later date. The other two are from Dale Dylan, an artist that Pat manages, and Sandy Wallis, an event organizer. The text of these e-mails is provided below.

E-mail from Alex Martin, Administrative Assistant

My name is Alex Martin, and I am the administrative assistant to Pat Smith. While Pats role is to create and maintain relationships with our clients and the event organizers, I am responsible for running the show at the operational level. I take care of Pats phone calls while Pat is on the road, respond to inquiries and relay the urgent ones to Pat, write letters to organizers and artists, collect information on prospective artists, send bills to the event organizers and make sure that they pay their bills, take care of the artist accounts, and arrange Pats travel (and keep track of travel costs). Most of my work I manage with Word and simple Excel spreadsheets, but it would be very useful to be able to have a system that would help me to keep track of the event fees that have been agreed upon, the events that have been successfully completed, cancellations (in the current system, I sometimes dont get information about a cancellation and I end up sending an invoice for a cancelled concertpretty embarrassing), payments that need to be made to the artists, etc. Pat and other managers seem to think that it would be a good idea if they could better track their travel costs and the impact these costs have on their income.

We dont have a very good system for managing our artist accounts because we have separate spreadsheets for keeping track of a particular artists fees earned and the expenses incurred, and then at the end of each month we manually create a simple statement for each of the artists. This is a lot of work, and it would make much more sense to have a computer system that would allow us to be able to keep the books constantly up to date.

A big thing for me is to keep track of the artists whom Pat manages. We need to keep in our databases plenty of information on themtheir name, gender, address (including country, as they live all over the world), phone number(s), instrument(s), e-mail, etc. We also try to keep track of how they are doing in terms of the reviews they get, and thus we are subscribing to a clipping service that provides us articles on the artists whom we manage. For some of the artists, the amount of material we get is huge, and we would like to reduce it somehow. At any rate, we would at least like to be able to have a better idea of what we have in our archives on a particular artist, and thus we should probably start to maintain some kind of a list of the news items we have for a particular artist. I dont know if this is worth it but it would be very useful if we could get it done.

Scheduling is, of course, a major headache for me. Although Pat and the artists negotiate the final schedules, I do, in practice, at this point maintain a big schedule book for each artist whom we manage. You know, somebody has to have the central copy. This means that Pat, the artists, and the event organizers are calling me all the time to verify the current situation and make changes to the schedule. Sometimes things get mixed up and we dont get the latest changes to the central calendar (for example, an artist schedules a vacation and forgets to tell usas you can understand, this can lead to a pretty difficult situation). It would be so wonderful to get a centralized calendar which both Pat and the artists could access; it is probably, however, better if Pat (and the other managers for the other artists, of course) was the only person in addition to me who had the right to change the calendar. Hmmm . . . I guess it would be good if the artists could block time out if they decide that they need if for personal purposes (they are not, however, allowed to book any performances without discussing it first with us).

One more thing: I would need to have something that would remind me of the upcoming changes in artist contracts. Every artists contract has to be renewed annually, and sometimes I forget to remind Pat to do this with the artist. Normally this is not a big deal, but occasionally we have had a situation where the lack of a valid contract led to unfortunate and unnecessary problems. It seems that we would need to maintain some type of list of the contracts with their start dates, end dates, royalty percentages, and simple notes related to each of the contracts.

This is a pretty hectic job, and I have not had time to get as good computer training as I would have wanted. I think I am still doing pretty well. It is very important that whatever you develop for us, it has to be easy to use because we are in such a hurry all the time and we cannot spend much time learning complex commands.

E-mail from Dale Dylan, Established Artist

Hi! I am Dale Dylan, a pianist from Austin, TX. I have achieved reasonable success during my career and I am very thankful that I have been able to work with Pat Smith and Mr. Forondo during the past five years. They have been very good at finding suitable performance opportunities for me, particularly after I won an international piano competition in Amsterdam a few years ago. Compared to some other people with whom I have worked, Pat is very conscientious and works hard for me.

During the recent months, FAME and its managers client base has grown quite a lot, and unfortunately I have seen this in the service they have been able to provide to me. I know that Pat and Alex dont mean any harm but it seems that they simply have too much to do, particularly in scheduling and getting my fees to me. Sometimes things seem to get lost pretty easily these days, and occasionally I have been waiting for my money for 23 months. This was never the case earlier but it has been pretty typical during the last year or so. Please dont say anything to Pat or Alex about this; I dont want to hurt their feelings, but it just simply seems that they have too much to do. Do you think your new system could help them?

What I would like to see in a new systemif you will develop one for themare just simple facilities that would help them do even better what they have always done pretty well (except very recently): collecting money from the concert organizers and getting it to me fast (they are, after all, taking 20 percent of my moneyat least they should get the rest of it to me quickly) and maintaining my schedule. I have either a laptop or at least my smartphone/iPad with me all the time while I am on the road, thus I certainly should be able to check my schedule on the Web. Now I always need to call Alex to get any last-minute changes. It seems pretty silly that Pat has to be in touch with Alex before any changes can be made to the calendar; I feel that I should be allowed to make my own changes. Naturally, I would always notify Pat about anything that changes (or maybe the system could do that for me). The calendar system should be able to give me at least a simple list of the coming events in the chronological order for any time period I want. Furthermore, I would like to be able to search for events using specific criteria (location, type, etc.).

In addition, we do, of course, get annual summaries from FAME regarding the fees we have earned, but it would be nice to have this information a bit more often. I dont need it on paper but if I could access that information on the Web, it would be very, very good. It seems to me that Alex is doing a lot of work with these reports by hand; if you could help her with any of the routine work she is doing, I am sure she would be quite happy. Maybe then she and Pat would have more time for getting everything done as they always did earlier.

E-mail from Sandy Wallis, Event Organizer

I am Sandy Wallis, the executive director of the Greater Tri-State Area Concert Halls, and it has been a pleasure to have a good

working relationship with Pat Smith at FAME for many years. Pat has provided me and my annual concert series several excellent artists per year, and I believe that our cooperation has a potential to continue into the foreseeable future. This does, however, require that Pat is able to continue to give me the best service in the industry during the years to come.

Our business is largely based on personal trust, and the most important aspect of our cooperation is that I can know that I can rely on the artists managed by Pat. I am not interested in the technology Pat is using, but it is important for us that practical matters such as billing and scheduling work smoothly and that technology does not prevent us from making decisions fast, if necessary. We dont want to be billed for events that were cancelled and never rescheduled, and we are quite unhappy if we need to spend our time on these types of technicalities.

At times, we need a replacement artist to substitute for a musician who becomes ill or cancels for some other reason, and the faster we can get information about the availability of world-class performers in these situations, the better it is for us. Yes, we work in these situations directly with Pat, but we have seen that occasionally all the information required for fast decision making is not readily available, and this is something that is difficult for us to understand. We would like to be able to assume that Pats able assistant Alex should be able to give us information regarding the availability of a certain artist on a certain date on the phone without any problems. Couldnt this information be available on the Web, too? Of course, we dont want anybody to know in advance whom we have booked before we announce our annual program; therefore, security is very important for us.

I hope you understand that we run multiple venues but we definitely still want to be treated as one customer. With some agencies we have seen silly problems that have forced them to send us invoices with several different names and customer numbers, which does not make any sense from our perspective and causes practical problems with our systems.

Case Study (Part THREE):

Case Description

Martin is encouraged by the progress you have made so far. As promised, he forwards you an e-mail from one of the key

members of his staff, Pat Smith (an artist manager). He also provides you with an e-mail from Shannon Howard, a prospective artist who might use FAMEs services.

E-mail from Pat Smith, Artist Manager

I am Pat Smith, and I am one of the 20 artist managers working for Mr. Forondo. I have worked for him for 15 years, and I am one of the most senior managers within the company. I enjoy working here because Mr. Forondo trusts me and knows that I will do my job. One area we have been lacking in during the last 1015 years is the use of computers to support our jobs, and it is great to hear and notice that something is happening in this area.

There are two areas that I find particularly important for me. First, I would like to have a system that would help me track prospective artists. There are so many talented musicians around the world that it is almost impossible to know who is doing what and where without keeping very good records and it seems that this would be an area where computers really could help. There are a lot of sources from which I get hints about young artists whose career I should start to follow. Sometimes my friends who are music critics call me and recommend a particular young artist they have heard; sometimes I myself hear a promising artist perform; sometimes we find a jewel among the unsolicited recordings that are sent or referred to us; and we also follow a large number of newspapers, magazines, and websites that review performances. Over the years we have learned to value the opinions of certain critics who write reviews, thus it is very important to know the source of a recommendation or an opinion. Sometimes I deal with dozens or even hundreds of recommendations per day and thus the lists that I maintain in a Word file on my laptop just are not very easy to use and organize.

The system for managing prospective artists should keep track of the artists (including their name, gender, year of birth, instrument(s), university degrees, address, phone number, e-mail, honors, etc.) and all the situations in which we have heard of them (including the source, a brief summary, a brief quality evaluation, and space for storing the original story or a reference to it if it was a review either in a newspaper or on the Web). It is essential that I can get this data reported quickly and in an easy-to-read format. It would be fantastic if I could query the database from my personal tablet and smartphone; I definitely need access to my laptop while on the road. This info would be maintained by any of the managers in the company or their administrative assistants (the assistants take care of most of the work with the reviews). I dont know if Mr. Forondo told you but he makes the final decisions regarding who becomes the artist manager for a new artist if there is any question about the contributions in recruiting the artist.

The second system I would find very helpful would be an application that reports the revenues my artists have earned in the past (we should be able to choose the period freely) and are predicted to earn in the future based on the contracts we have signed for them with our clients. This way, I would know how much I am earning and going to earn in the future. Somehow, it would be great if the system could also tell how much money I have spent on travel; as you might have learned, we managers pay our own travel costs from the 60 percent of royalties we receive. I am really happy we dont need to pay the assistants salaries, too.

E-mail from Shannon Howard, Prospective Artist

I am Shannon Howard, a soprano from Bloomington, Indiana, and I have had some initial discussions with Pat Smith at FAME regarding the possibility that they might take me under management. I feel that having a good manager would be very important for my career, and I believe that FAME would provide excellent service for me.

One area where FAME is not yet very strong is marketing their artists on the Internet, and maybe your project could have some impact in this area. I think it would be an excellent idea if prospective concert organizers could see an artists information on the Web and also hear samples of his/her music. In addition, information about an artists availability should be available on the Internet. By the way, has anybody remembered to tell you that an artist may be prevented from performing somewhere not only because of an earlier commitment to perform but also because of rehearsals or time needed for travel? Sometimes large productions need long practice times and transcontinental travel also can take several days away from an artists schedule. For me it is very important that I can personally negotiate with my manager what I will perform and what I wont, and I think it would be great if my manager would know what repertoire I have already prepared and what I am not willing or able to perform at this time. Also, it would be great if I could block time away from my calendar in different priority groups so that I could say that certain days I am definitely not available, certain days are not very good but I can perform if Pat can find an excellent opportunity for me, and on certain days I can take any work. I dont know if this is realistic technologically and whether or not Pat would accept the idea, but it sure would be nice from my perspective.

The smoother all types of practical issues go, the better I can focus on my actual work, i.e., singing. Therefore, I feel that it is very important that FAME has a good computer system to help them in their work for me (assuming I can sign up with themwish me luck!). I would find it very helpful if they could tell me at the end of the year how much money I have made and from whom I received itit wont be a long list in the beginning but hopefully it will become much more extensive over time.

I dont know if you have thought about it but just in case I have gigs around the world it would be very nice if the system could also tell how much (if anything) each of the governments withheld from my pay at the source before it was forwarded to FAME and if the payments could be sorted and subtotalled by country. If you wonder what this could mean in practice, let me give you an example. Lets say I am performing in Finland and Finland has an at-the-source tax for artists of 15 percent. If my fee there is $2,000, my employer in Finland has to withdraw 15 percent of my fee and pay it to the Finnish government; therefore, FAME will receive only $1,700, and if their royalty is 30 percent, I will receive only $1,190. At the end of the year, FAME

should give me a report including four columns: my original fee (i.e., $2,000 in this case), tax-at-source ($300), FAMEs share ($510), and finally my share ($1,190). The math is simple but it is essential that this is done correctly so that I wont be in trouble with the tax authorities either in foreign countries or here in the United States.

1. Create an enterprise data model that captures the data needs of FAME. (Hint: use case study part one).

2. Create an E-R diagram for FAME based on the enterprise data model you developed in question 1. (Hint: use case study part two).

3. Create an EER diagram for FAME that extends the E-R diagram you developed in question 2. (Hint: use case study part three).

4. Convert the EER diagram you developed in question 3, to a relational schema using the techniques described in chapter 5. Be sure to appropriately identify the primary and foreign keys as well as clearly state referential integrity constraints.

5. Use the Logical data model you created to guide you in writing the SQL statements for creating the FAME database for the relational schema you created.

a) Write the SQL statements for creating the tables, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing other constraints you identified.

b) Select a portion of your database and populate it with sample data. Be prepared to defend the sample test data that you insert into your database.