Data Modeling Problem Fall 2003 -- EXAM #1

Brian’s Gemini Music (BGM)

Brian’s Gemini Music

Brian’s Gemini Music (BGM) offers music and entertainment services for a wide variety of occasions, like weddings, company parties, birthday parties, anniversaries, class reunions, etc. BGM always supplies one or more disk jockeys (DJs) who play recorded music and emcee the entertainment, but it often provides other entertainment services as well such as special dance acts, special singing, special musical instrument playing, etc. The people who provide DJ service and other entertainment are called players, and BGM hires them on a job-by-job basis. BGM’s own employees do the job arranging, booking of reservations, cash handling, and advertising booking, and these people are called agents. Both players and agents are identified by social-security-number (SSAN).

Each job that BGM books may have multiple players, but a player corresponds to just one role on a job (that is, someone like BOB can be both a DJ and a dancer, but not on the same job). Jobs may have many special acts (like a vocal solo or an energizing dance number) added on, but each special act occurs only once on a job. Most jobs have a booked reservation, but there are some that are done on the fly. Reservations correspond to just one job and to just one job type.

When an agent books a reservation, he or she notes the single job type and then makes decisions about other types. Job types have recommended equipment types and recommended player types. Job types also have recommended sets of special acts, and these special acts correspond both to multiple player types (or roles) and to multiple equipment types. Therefore, when agents realize that they are booking a reservation for a job of a certain type, they can use the database to see what its needs could be (in terms of special acts, equipment, and players) and then negotiate those with the customer. All reservations note the planned set of equipment types, the planned set of special acts, and the planned set of player types (as determined by the customer-agent dialogue). Recommended sets of players and equipment may not correspond to the planned set because of customer wishes and desires. The actual jobs note the actual equipment used and the actual set of players engaged and their roles, but they do not track whether or not the set of planned special acts took place as scheduled. All specific instances of equipment are identified by a combination of equipment-type and equipment-number.

When a reservation is made, the customer pays one third of the fee, and the rest is due 14 days prior to the engagement. Each reservation corresponds to two remittance advices used for these two payments. All payments note the customer check# and they are deposited in one cash account by the agent. When an on-the-fly job is done, it is paid immediately with one remittance advice. Jobs are not put into the database until just after they happen or slightly thereafter. When a job goes overtime, there is a surcharge (determined by the job type) that is paid immediately with a separate remittance advice and check by the customer.

Advertising services are booked and paid for by BGM agents. All advertising vouchers are targeted toward either a single job type or toward multiple customer categories.

Brian’s Gemini Music

On the pages that follow, two different parts of BGM’s operations are described. Each part has a more detailed narrative description and a list of attributes to be used in solving that particular part of the problem. Try to do an entity-relationship diagram and a set of relational tables for part (a) first. Then, go on to attempt the same for part (b). In both cases, limit yourself to the attributes given on just that part.

SIMPLIFYING ASSUMPTIONS:

In order to simplify the solution of this modeling problem, you may make the following assumptions:

  • You may assume that all “economic agents,” all “economic resources,” and all types are put into the database before any relationships with other entities are instantiated.
  • All relationships in the problem are binary (you may have to use a key attribute of one entity as a non-key attribute of another in the problem to prevent a ternary relationship).
  • Don’t worry about modeling the acquisition of player time and paying them for working on jobs. Assume these are done in a separate payroll process.
  • You may assume that all players correspond to one or more player types (roles), that all jobs correspond to only one job type, that all equipment corresponds to only one equipment type, that job types may correspond to multiple special act types and vice-versa, that job types correspond to many player types and vice-versa, that special act types correspond to multiple player types and vice versa, and that player types may correspond to many players. All customers are assigned to a single category.
  • Cash receipts occur in other cycles of BGM, but 80% of them are linked directly to jobs. Cash disbursements occur in many other cycles besides advertising acquisition. Agents pay for all advertising vouchers incurred in a month on the 15th of the following month.

Brian’s Gemini Music (a)

REQUIRED: Using the data items listed on this page, construct an E-R diagram (entities, relationships, participation cardinalities) plus a minimal relational database for BGM (a). By “minimal,” it is meant that the option to post a key into an existing table because of either required participation or a discernible high load should be exercised where appropriate. Make sure you have good reasons for putting model components in or for keeping model components out. With the exception of posted keys, please do not add or delete items from this list. Some data items on the list may be used more than once in building the E-R model and the relational database.

-customer-for-reservation-agent-for-reservation-cash-account#

-receipt-$-amount-remittance-advice#-agent-SSAN

-customer#-customer-namecash-receipt-date

-agent-name-reservation#-reservation-planned-length

-equipment-number-actual-earnings-of-player-on-job-player-type-name

-player-Date-of-birth-number-of-a-player-type-needed-for-special-act

-reservation-date- number-of-a-player-type-needed-for-a-job

-reservation-time-slot- number-of-an-equipment-type-needed-for-a-job

-equipment-type-expected-life-of-equipment-type

-count-of-this-equipment-type-on-reservation-job-expected-length

-job#-job-actual-duration-job-type-special-act-name

-job-total-charge--count-of-this-player-type-on-reservation-job-overtime-surcharge

-typical-special-act-duration-player-type-hourly-wage-player-SSAN

-date-player-qualfied-for-role-cash-account-balance -customer-for-job

-agent-for-receipt-agent-for-job

Brian’sGemini Music (b)

REQUIRED: Using the data items listed on this page, construct an E-R diagram (entities, relationships, participation cardinalities) plus a minimal relational database for BGM (b). By “minimal,” it is meant that the option to post a key into an existing table because of either required participation or a discernible high load should be exercised where appropriate. Make sure you have good reasons for putting model components in or for keeping model components out. With the exception of posted keys, please do not add or delete items from this list. Some data items on the list may be used more than once in building the E-R model and the relational database.

-disbursement-timestamp-agent-SSAN-agent-name

-customer-category-name-advertising-service-voucher#-customer#

-$-amount-of-disbursement-vendor#-individual-accounts-payable

-job-type-$-amount-of-service-voucher-vendor-phone

-YTD-$-sales-to-customer-category-YTD-$-advertising-for-job-type

-advertising-vendor-for-disbursement-disbursement-for-this-voucher