Feis DB

Create an ER Schema for the following scenario. The schema must include:

• The ER diagram itself, complete with attributes and cardinality constraints.
• An entity attribute list briefly describing, when necessary, the attributes for each entity. Try to have at least 5 attributes per entity, although this will not be reasonable for some entities.
• A list of relationship attributes and their descriptions. (NOTE: NOT all relationships will have attributes.)
• A list of assumptions you have made in order to make the diagram.

Shamrock Steps is a company that specializes in organizing Irish dance competitions. The company wants to create a database to help keep track of the details associated with running a competition.

An Irish dance competition is known as a feis (pronounced fesh). In Irish Gaelic, it means a gathering. The plural of feis is feisana.

The main groups of users of the database are: 1) Participants, 2) Officials, and 3) Managers. Participants are primarily the dancers in the competitions. A dancer's teacher is also a participant even though they may not dance. Officials are the people who are in charge of a particular feis. They make the assignments of who is dancing where, at what time, and with whom. Managers are employees of Shamrock Steps that will need to maintains certain aspects of the database. An important point is that managers are not database administrators.

Draw an ER model that most accurately and concisely represents the following scenario.

• Irish dance is organized into different geographical regions. There may be several regions in a country, depending on size and strength of interest. Different regions may do things slightly differently, but are largely consistent.
• A feis is held at a particular location on a particular date. Each feis has a name. The name sometimes comes from the school or schools hosting the feis.
• When a feis is held at a hotel or close to a hotel, it is common to have multiple feisana on subsequent days, e.g., one feis on Saturday and one on Sunday.
• The primary component of a feis doesn't have a fixed name. Depending on who you talk to, it may be called a competition, a set, or a dance, among other things. But they all refer to the same thing, a small group of people performing the same dance in front of a judge. For the sake of discussion here, it will be called a competition.
• A typical feis may have over two hundred different competitions.
• Each competition consists of several dancers who have been grouped according to several criteria including age, gender, skill level, and type of dance.
• There are several types of dance. Dances will usually have an abbreviation, which is usually fairly standard across regions. The dances are: light jig (LJ), slip jig (SJ), single jig (SN), reel (RL), hornpipe (HP), treble jig (TJ), traditional set (TS), non- traditional set (SD), treble reel (TR), figure dance (FD), and St. Patrick's Day (PD), which is actually a subset of the traditional set.
• There are several skill levels, each with a label. The labels may vary by region, and are really only used to help organize the feis. The skill levels are (with a proposed numeric label) : First Feis (1), Beginner 1 (2), Beginner 2 (3), Novice (4), Prize Winner (5), Preliminary Championship (6), and Open Championship(7).
• Each competition group has a label associated with it that usually indicates the skill level, age, gender, and type of dance for that competition. Some examples of labels include "311LJ" for 11 year old girls dancing a light jig at the Beginner 2 level, or 515RL for 15 year old girls dancing a reel at the Prize Winner level. Boys are often indicated by adding a number to the age. To go along with the above examples, 361LJ would be 11 year old boys dancing a light jig at the Beginner 2 level, and 565RL would be 15 year old boys dancing a reel at the Prize Winner level.
• Each competition has anywhere from 1 to over 20 dancers. As needed, feis officials will combine different groups into one competition. For example, one competition may consist of 413TJ / 463TJ, combining both 13 year old girls and boys dancing the treble jig at the novice level. If the number of dancers in a particular competition group becomes too large, feis officials may split that into two groups, e.g., 410HP-A, 410HP-B, each with 15 people in it.
• The groups in a competition and the number of people in each competition are key pieces of information to be tracked.
• A feis has several stages (8 is not uncommon), often with competitions going on each stage at the same time. (It can get very noisy.) Competitions are assigned to stages in advance.
• The dancers in each competition will line up at the back of the stage, and then in groups of three or less, will go out and dance for a fixed number of measures of a song that is playing. As they finish, they return to the edge of the stage and others take their place.
• Each competition has at least one judge, who is considered to be a participant.
• Competitions below the championship skill levels have one judge. Competitions at the championship levels have three judges.
• Each judge rates each dancer in the competition on a scale from 0-100 and gives comments
• Dancers are ranked within their competitions based on the judge's scores and receive prizes accordingly. Lists of ranked dancers for each competition are a key data output.
• Each competition has an artist who provides music for the competition. Occasionally, there will be more than one artist per competition, but this is rare. Artists will play either a violin, a penny whistle, or an accordion.
• The competitions on a given stage do not have specific time slots. But they do have a specific order, assigned by the officials. Typically, the lower skill levels will compete before the higher skill levels.
• Often the non-championship levels will compete on one set of stages and the champi- onship levels will compete on a different set of stages.
• A given dancer may compete at different levels. It would be possible for a dancer to be at a Beginner 2 level in the reel and slip jig, Novice level in the hornpipe, and Beginner 1 in the treble jig.
• Judges are not fixed to certain stages. They will often rotate once or twice throughout the feis.
• Artists are usually assigned to a particular stage, but they can change, if need be.
• Sometime after the feis, and often online, dancers can receive their score, ranking, and comments for each of the dances (competitions) in which they participated. It will also be noted who the judge was. If the competition had more than one judge, then the scores and comments will be available from each judge.
• A copy of the competition score, ranking, and comments will be sent to each dancer's dance school.
• When a dancer registers for their first feis, they are asked for pertinent identifying information and contact information. In subsequent registrations, they are not asked for full information.
• Dancers are asked for their dance school affiliation.
• Dancers are not asked for school contact information unless the school does not exist in the database.
• When a dancer registers for a particular feis (many may be available), they choose the skill level and types of dance in which they wish to participate. Later, they will be given assignments as to the groups they belong to.
• Each dancer is assigned a participant number which is unique to the feis.
• A feis will often have music competitions and cooking competitions. Prizes are awarded. But these competitions are currently not tracked.
• Managers are responsible for creating the initial framework for a feis which the feis officials will fill in.
• Officials can be associated with multiple feisana simultaneously.

Hints: If you blindly take the above sentences and turn nouns into entities, verbs into relationships, and adjectives into attributes, you will likely do very poorly. Think about the data and how it interacts.

Try different alternatives. Some things will work better than others. Not every piece of data or interaction is listed.

Some of the information above will not be represented directly in the diagram. Think about what is needed to support functionality required outside of the database.