solution

South-Eastern Ontario Collective (SEOC) is a performing arts organization that operates yearround and offers live in-person and on-line performances for the general public, but also special performances for students attending schools in South-Eastern Ontario. SEOC is revamping their information system and hired you to design a database that will hold information about SEOC past, current and future activities as well as information about the people involved in them. SEOC offers a creative platform for new and emerging artists and many of their productions need months of development and rehearsal work. Each of SEOC’s productions can be presented as “traditional” performances, in front of a paying audience, or as special presentations for local students. Each production has a title, an intended show format (e.g., in-person, on-line, etc.), a synopsis and a longer description destined to be included in the programs made available to audience members, a timeline, e.g., start and end date for its performance run, as well as a production development timeline, e.g., start and end date for its development. Each production has various tasks or roles, e.g., director, performer, composer, light designer, etc., that can be filled by artists and permanent staff, as appropriate. Each production involves at least one, and often more than one performer (artists who either full members of SEOC or hired sessional artists who work on a temporary basis), and at least one creator and one producer. The creator and the producer can be the same person, but often that is not the case. Each artist may be credited on a play as playing several different characters. Only the artists who are full members of SEOC can develop productions at SEOC. The sessional artists are all engaged for limited amounts of time. Note that an artist can work with SEOC on a temporary basis several times over the years, e.g., from March1 2019 to May 30 2019 and then again from May 1 2021 to October 31 2021, and work on multiple productions while under contract with SEOC. For each person involved in its productions, SEOC records the person’s SIN, first and last name, gender, address, one or more phone numbers and an email address and expertise. Some artists are actors, others are composers, and others have multiple areas of expertise, e.g., acting as well as directing. SEOC also employs permanent staff who work on various productions as sound engineers, light designers, costume designers, carpenters, etc. For each staff member SEOC needs to store their first and last name, address, SIN, email, phone number, permanent position (carpenter, costume designer, etc.) as well as their skills and qualifications. A permanent staff member may have a different role on each project they work on, as longs as their skills and qualifications match the requirements of the job role.

1.1 Draw an Entity-relationship diagram that models the information in the above description. Be sure to indicate all key and cardinality constraints and state in writing any assumptions that you make that are relevant for understanding your modeling decisions. Also identify in writing any constraints that you are unable to capture in the ER diagram. Use the style of modeling presented in class.

1.2 Translate your ER diagram into a relational model and identify the primary keys for each relation, as well as all foreign keys.

1.3 List 2 functional dependencies that should hold in any instance of your relational model.