Reassessment Task 1: E-R Modelling
Case Study: Music Talent Management (MTM)
Music Talent Management (MTM) is an artist management company that represents classical music artists (only soloists) both nationally and internationally. MTM has more than 500 artists under its management, and wants to replace its spreadsheet-based system with a contemporary computerised information system.
Their core business idea is simple: MTM finds paid performance opportunities for the artists whom it represents and receives a 10–20 percent royalty for all the fees the artists earn (the royalties vary by artist and are based on a contract between MTM and each artist).
To accomplish this objective, MTM needs technology support for several tasks. For example, it needs to keep track of prospective artists. MTM receives information regarding possible new artists both from promising young artists themselves and as recommendations from current artists and a network of music critics. MTM employees collect information regarding promising prospects and maintain that information in the system. When MTM 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.
MTM markets its artists to opera houses and concert halls (customers); in this process, a customer normally requests a specific artist for a specific date. MTM maintains the artists’ calendars and responds back based on the requested artist’s availability. After the performance, the MTM sends an invoice to the customer and, in turn, the customer sends a payment to MTM (note that the company requires a security deposit, but you do not need to capture that aspect in your system). Finally, MTM pays the artist after deducting its own fee.
Currently, MTM 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.
The needs and expectation of a new system
E-mail from Martin Jones, owner
Martin Jones, the owner of MTM, has commissioned you to design and develop a database application. In his e-mail requesting your help he provides the following information:
“My name is Martin Jones, and I am the owner and founder of MTM. 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. However, the situation is 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 work -the managers are focusing on relationship building and finding new talent for our company. 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).
As I said, we find performance opportunities for the artists and, in practice, we organise their entire professional lives -of 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 20 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.
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 don’t, 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 month’s 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 improve 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.”
E-mail from Alex Martin, administrative assistant
A few other e-mails have been sent from the company to assist you in the Database design of the system. The first is from Alex Martin (administrative assistant to Pat Smith, an artist manager). Pat is on holidays and Martin has promised that Pat’s 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 organiser.
“My name is Alex Martin, and I am the administrative assistant to Pat Smith. While Pat’s role is to create and maintain relationships with our clients and the event organisers, I am responsible for running the show at the operational level. I take care of Pat’s phone calls while Pat is on the road, respond to inquiries and relay the urgent ones to Pat, write letters to organisers and artists, collect information on prospective artists, send bills to the event organisers and make sure that they pay their bills, take care of the artist accounts, and arrange Pat’s travel (and keep track of travel costs). Most of my work I manage with a word processor and simple 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 don’t get information about a cancellation and I end up sending invoices for cancelled event -pretty 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 don’t have a very good system for managing our artist accounts because we have separate spreadsheets for keeping track of a particular artist’s 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 them—their 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 don’t 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 organisers are calling me all the time to verify the current situation and make changes to the schedule. Sometimes things get mixed up and we don’t get the latest changes to the central calendar (for example, an artist schedules a vacation and forgets to tell us -as you can understand, this can lead to a pretty difficult situation). It would be so wonderful to get a centralised 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. 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 with us first).
One more thing: I would need to have something that would remind me of the upcoming changes in artist contracts. Every artist’s 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 Glasgow. 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. Jones 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, MTM 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 don’t 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 2–3 months. This was never the case earlier but it has been pretty typical during the last year or so. Please don’t say anything to Pat or Alex about this; I don’t 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 system -if you will develop one for them, are 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 organisers and getting it to me fast (they are, after all, taking 20 percent of my money -at 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 -at the moment, 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 MTM regarding the fees we have earned, but it would be nice to have this information a bit more often. I don’t need it on paper but if I could access that information on the Web, it would be 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 Organiser
“I am Sandy Wallis, the executive director of the Britannia Concert Halls, and it has been a pleasure to have a good working relationship with Pat Smith at MTM 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 don’t 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 Pat’s 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. Couldn’t this information be available on the Web, too? Of course, we don’t 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.”
This assignment aims to facilitate analytical and design practice using case studies. Using a standard notation, you are required to produce an Entity-Relational data model for MTM and their needs as described in the case study above.
To produce a data model that satisfies the needs of MTM you are expected to:
- Analyse the case study in detail and identify what the new system will accomplish; what functions it will perform, and what organisational goals it will support.
- Create an initial E-R diagram for MTM. Clearly state any assumptions you made in developing the diagram.
- Based on your initial E-R diagram above, identify all entities and attributes and proceed to normalise them up to the 3rd Normal form; clearly identify primary and foreign keys as well as any other constraints. Provide the rationale for any changes made.
- Based on the normalisation of your tables, primary keys, foreign keys, and constraints identified above, finalise your data model by making any necessary changes to the initial E-R diagram. Provide your rationale for any changes made.
- Discuss to what extent the database suggested in your final E-R model will address the organisational needs discussed in task 1 above.
You are expected to produce a report outlining the process followed including the outputs obtained at each stage. Assumptions should be clearly stated.
This is an individual assignment. Your report should be submitted electronically through the module’s Blackboard site. You will find a link under Reassessment for this submission.
- One document should be produced with all the above. The maximum length of the document is 2,000 words plus images and tables. Appendices do not count against this maximum length.
- Make sure that the assignment submitted clearly indicates your name – No name, no marks!
The normal regulations for the submission of work apply, for further details see: http://students.shu.ac.uk/rightsrules/regs.html
Deadline: 19th May 2022 (3:00pm)
|Initial Analysis of the case study.
|Functionality and benefits for a new information system for MTM are not identified or fail to address the needs of the company.||The functionality and benefits identified for a new information system for MTM are limited or irrelevant. Consideration of business goals is minimal.||The functionality required by a new information system for MTM is identified and is relevant; it relates to the business goals. Potential benefits for the company are outlined.||Comprehensive analysis of the company needs and requirements, which allows the identification of functionality, benefits and potential outcomes of a new information system for MTM.||+ Analysis is wide-ranging while concise. Different stakeholders and their needs are clearly identified.|
|Create an initial E-R diagram for the MTM.
|Entity-Relationship modelling has not been understood. Essential Entities, Attributes and/or Relationships are not represented in the model. Standard notation is not properly used.||Entity-Relationship modelling is understood to some extent. However, essential Entities, Attributes and/or Relationships have not been identified or represented in the model. Standard notation is used.||Derived from analysing the case study requirements, essential Entities, Attributes and Relationships are correctly identified and represented in the model. Standard notation is used. Assumptions are stated.||Demonstrates a comprehensive understanding of Entity-Relationship modelling. A model is proposed within the context and constraints of the case study. Sensible assumptions are clearly stated. Some alternatives considered. Standard notation is used.||+ A comprehensive evaluation which recognises the validity of alternative perspectives and solutions.|
|Normalise the tables identified up to 3NF.
|Initial Entities from the case study have not been considered. Normalisation has not been conducted or has not been understood.||Initial Entities from the case study have been considered, but normalisation process has not been properly followed or has not been understood. Changes are not justified.||Entities are mapped into tables and normalised up to 3NF. Primary and foreign keys, as well as other constraints are identified. Rationale for changes is presented.||Demonstrates a comprehensive understanding of normalisation process. Data has been properly normalised to 3NF. Clear rationale presented.||+ The data model produced considers alternatives, as well as context and constraints of the case study.|
|Final E-R Diagram.
|The final E-R diagram evidences a lack of understanding of Relational data modelling.||The final solution has serious notational or semantic errors -one or many of the following are present: essential Entities or Attributes are missing; Relationships are not valid, or names irrelevant, Cardinality or Participation is incorrect; Primary and Foreign Keys or constraints are not identified.||Identifies key Entities and their Attributes within the system. Valid relationships are identified between Entities and appropriate names are allocated to the relationships, indicating their correct Cardinality and Participation. Primary and Foreign Keys are identified, as are other constraints.||+ Demonstrates a comprehensive understanding of relational data modelling. The model produced is complete and implementable.||+ Extensive consideration of the context and constraints of the case study. The model produced is exemplar.|
|Critical reflection on model developed.
|Analysis of the final E-R model suggested is not present; or is irrelevant for the organisational needs of MTM.||The final E-R model is analysed to some extent, but does not acknowledges its limitations; or fails to articulate how the model addresses the needs of MTM; or its limitations.||Critical analysis of the final E-R model is presented. The extent to which such model addresses the organisational needs discussed in task 1 above is addressed. Limitations in the model are acknowledged.||+ Comprehensive understanding of the role of the database modelled for the new Information system.||+ Exceptional consideration of the context and constraints of the case study.|