Homework I Instructions The goal of this homework is to draft an EER diagram for a business problem that reflects the reallife projects of a data architect. The homework is individual. Any form of collaboration is not allowed. After reading the description of the business problem (see below the client's requirements and attached additional information), prepare a list of entities and a short description/explanation of each entity. Then, design the EER diagram representing your proposed solution to this database project. In case something is not clearly specified, you have 2 options: 1. Prepare a list of questions for the client (your instructor). Be mindful that the client will require time to answer, so ask the questions at least 2 days before the deadline. 2. Make assumptions and design the diagram (remember to list all the assumptions in your deliverable) I will evaluate each submission following these criteria. Outline of the evaluation criteria: 1. The EER Diagram ( 120 points) must: Be in the EER language/format that we use in this course Follow the EER logic and rules Include the entities that are necessary to answer all the client's requirements Include a list of all the entities with a brief description of each entity Show all the necessary relationships between entities Specify the cardinality (both directions!) and degree of each relationship Include a short list of the assumptions that were made to draft the diagram Prepare one word/pdf document with all the above information. For the diagram, you can use any tool you want, including drafting it on a paper and taking a picture of it. But remember, the submission must include one file only. When you're ready, upload your file on Blackboard. Client's Project Requirements EZMusic Inc. is an artist management company that connects music artists with performing venues. We work at a national level. EZMUSIC has more than 500 artists/bands in its portfolio and more than 2000 venues. We want to replace the spreadsheet-based system with a new stateof-the-art computerized information system. The core business idea is simple: EZMUSIC finds paid performance opportunities for the artists whom it represents at venues that are connected with EZMUSIC and receives a 5 to 15 percent royalty for all the fees the artists earn (the royalties vary by artist and are based on a contract between EZMUSIC and each artist). In addition, EZMUSIC receives a fixed fee from the venues (the fee varies by venue and is based on an annual agreement between EZMUSIC and each venue). To accomplish this objective, EZMUSIC needs technology support for several tasks. For example, it needs to keep track of prospective artists. EZMUSIC receives information regarding possible new artists both from promising young artists themselves and as recommendations from current artists and a network of music critics. Each venue is classified for the preferred music genres, and EZMUSIC keeps the venue's reviews of the artists that played in each venue. These reviews are used to keep a list of favorite artists for each venue. EZMUSIC employees collect information regarding promising prospects and maintain that information in the system. When EZMUSIC 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. EZMUSIC markets its artists to the venues; in this process, a venue normally requests a specific artist for a specific date. EZMUSIC maintains the artists' calendars and responds back based on the requested artist's availability. After the performance, EZMUSIC sends an invoice to the venue, who sends a payment to EZMUSIC (note that EZMUSIC requires a security deposit, but you do not need to capture that aspect in your system). Finally, EZMUSIC pays the artist after deducting its own fees. Currently, EZMUSIC 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 wire-less network. A local company manages this infrastructure and provides the required support. EZMUSIC has commissioned your team to design and develop a database application. In his e-mail soliciting your help, he provides the following information: Additional Information: E-mail from EZMUSIC's CEO EZMUSIC manages artists and venues for over 30 years thanks to the wonderful staff. We are in the business of creating bridges between the finest musicians and the best venues of the US 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 work - the managers are focusing on relationship building IS3063 - Database Management for Information Systems and finding new talent for our company. The managers report to the CEO, but they are very independent in their work, and we are very pleased that very seldom we have to deal with operational issues related to the managers' work. The CEO also have his/her own artists (only a few). As we said, our main source of revenue consists of the royalties we get when we are successful in finding a performance opportunity for an artist and the fees from the venues. We get up to 15 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) and a fixed fee from the venue (which is negotiated every other year in a separate agreement with each venue). 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 (plus the venue's fees) 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 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 track current revenues and 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. Additional Information: E-mail from an artist manager 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 don't get information about a cancellation and I end up sending an invoice for a cancelled concert-pretty embarrassing), payments that need to be made to the artists, etc. Each venue gives us a list of events and for each performance we negotiate with the venue for a fee. IS3063 - Database Management for Information Systems This fee includes the artist's royalty and the venue's fixed fee. 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. Also, we need to keep in our databases plenty of information on the artists-their name, gender, address (including the state, as they live all over the US), phone number(s), instrument(s), e-mail, genre, 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. 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. In addition to that, we keep track of the venue's rating of the artists after each performance. Each rating is numeric on a scale from 1 to 10 and has also a short description attached. The system for managing prospective artists should keep track of the artists (including their name, gender, year of birth, instrument(s), genres, 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 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). Scheduling is, of course, a major headache for me. I maintain a big schedule book for each artist whom we manage. It would be so wonderful to get a centralized calendar which both myself and the artists could access. 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 artist's contract has to be renewed annually, and sometimes I forget 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. From an artist perspective, it would be nice to have annual summaries regarding the fees they have earned, but it would be nice to have this information a bit more often. It is also crucial to keep track of the fees that have already been paid to the artists and the fees that still need to be paid. Our policy is to pay the artist the week after the performance, which is when the venues pay us