Draw an EER diagram for each of the following scenarios using standard Chen's notation that we have discussed in class and that is described in the course textbook. You may complete the scenarios for option A and B using drawing software such as draw.io or Visio which both have templates for EER diagramming. You may, instead, complete them as hand-drawn diagrams. A hand drawn diagram must be complete and legible. It may be scanned and uploaded or it can be an easily legible photograph from a cell phone camera. If we cannot read it, we will not be able to grade it.

Scenario A - Fitness Club Extension

In your previous homework, you developed an ER diagram for the IM Building at Penn State. Campus Rec loves the idea and now determines they want to expand the database to be campus-wide and for fitness centers on other campuses. One of the aims for the project will be to record information about members that participate in group fitness sessions and personal training sessions and also which trainers supervised which sessions.

You can do this problem by building on your diagram from the last homework assignment. We have added a few things here around specializations and a few other more advanced concepts. The entire problem will be restated here though in the new context.

Each fitness CENTER is identified by a CenterID, a name (e.g. White Building) and campus. Every CENTER has one or more ROOMS that can be booked for sessions (either group or personal training). As in last time, rooms are noted by their maximum capacity. Within each center, the room number will be unique -- though this time, recognize that Room 116, for example, could exist in more than one center (Is this a strong or weak entity type?).

MEMBERS can sign up for personal or group sessions in different centers (but it is possible they won't sign up for any sessions). Each member has a member ID number.

Each SESSION (PERSONAL or GROUP) requires exactly one TRAINER. Even if there is another person co-teaching the class, the database will only store one official trainer of record for each class session. Sessions are identified by SessionID. A trainer is identified by his/her certification number. The system should accommodate trainers who are interns and may not have led any sessions yet.

For a session, the start time and room number will be recorded. For group sessions, the type should be recorded (i.e., aerobics, weight training, yoga, etc.). Sessions can start at the same time on the same day but in a different room of a center or in a different center. At a given start hour of a given day, at most one individual or group session may start in a given room of a given center.

Scenario B - Musicmatic Attracted by the success of Spotify, a group of students want to build their own music-streaming website called Musicmatic. Being economists, they are unaware of the specificities of databases and have therefore asked you to create an EER model.

A large number of songs will be made available through their website, and the following information on each song needs to be stored: title, year, length, and genre. Also, artist information will be added, including date of birth, name, and a URL to a website (e.g., Wikipedia page) with additional information on the artist. You can assume an artist is uniquely identified by her name, and that a song always belongs to exactly one artist. The Musicmatic students also point out that songs having the same title are possible, and only the combination of song and artist can be assumed to be unique.

The database will also have to store information on the people using Musicmatic. It was decided to only discriminate between two types of users: regular users who will be able to buy music(singles), and business users who will deliver the content (upload the music). The following information is recorded on each user: (unique) ID, name, and address. Business users will also have a tax id number.

The students want to offer a flexible service, and decided business users only can upload individual songs. These songs are classified either as singles or hits, and regular users can directly buy the singles. Users can compose a playlist consisting of multiple hits (no singles). An playlist should be uniquely identifiable by an playlist number. The position of each hit in the playlist is stored as a track number in the database.

Finally, a user can be a regular user on some occasions (e.g., when downloading a single or album), and a business user at other times (e.g., when uploading self-made songs to Musicmatic).

Capture as much of the semantics of this situation as you can in an EER diagram!