CREATE ER DIAGRAM WITH YOUR HAND ON PAPAER BASED ON INTERVIEW NOTED
AANSWER MUST BE SIMILAR LIKE THIS WORK.
What you need to do: 1. Please prepare an ER diagram based on the following information gathered during interviews conducted with users. Please indicate the entities, attributes, primary keys, and cardinalities on your diagram. Also, be sure to include an appropriate title (with name, date, version number) for your diagram. 2. Please include all of the listed attributes for each entity. You may assume that all attributes listed in the narrative are required. 3. Be aware that in the following problem narrative, an appropriate primary key attribute may not be specified for every entity. If an appropriate primary key for an entity is NOT indicated in the description below, then please CREATE an appropriate primary key for the entity. 4. By convention, entity labels should be of the singular form (e.g., "Customer" instead of "Customers"). 5. Include a diagram label (e.g., ER Diagram for ACME Sales System, Prepared by [Name], [Date], Version \#). 6. To receive full credit, please prepare your work very neatly. The Rogue Valley Commerce Bureau (RVCB) is an organization located in southwestern Oregon that is responsible for many types of functions aimed at promoting commerce and business activity in the Rogue Valley region. The Rogue Valley is an especially beautiful area in the state of Oregon and become a popular conference site for many organizations (e.g., corporations, business coalition groups, trade groups, academic organizations). One the most important types of functions addressed by the RVCB is the coordination of the conferences held in the Rogue Valley area. RVCB coordinates virtually all conferences in the Rogue Valley. There are numerous fine conference facilities in the Rogue Valley (e.g., hotels, municipal buildings, park facilities, etc.), and the RVCB helps organizations to arrange their conference activities in the appropriate facilities. As the RVCB has gotten more and more involved with the coordination of conference events, the management of RVCB has come to realize that they will need to develop a database system to support their conference-related work. RVCB will want to store data regarding its clients (i.e., the organizations that want to hold a conference in the Rogue Valley), conference request applications, RVCB employees, specific conference activity events, conference activity locations, and facilities at each location. (Hint: Each of the bolded words in the previous sentence will correspond to an entity on the ER diagram.) Following is a summary of notes gathered during interviews with personnel from RVCB. Please use the following information to develop an entity relationship model for the propes. Interview Notes: - Information that is stored about each client includes: client organization name, client organization address, client organization phone number. The client entity is a "supertype." RVCB has found that some of the clients may be categorized as non-profit clients (e.g., state universities, regional government agencies, scientific associations), while other clients may be classified as for-profit clients (e.g., corporations). RVCB would like to model the following subtypes for the client entity: "Non-Profit Client" and "For-Profit Client." Attributes of interest for a non-profit client include the "non-profit tax code number" and the "non-profit category." Attributes that are recorded for a for-profit client include ownership status (i.e., public or privately held) and organizational rating (i.e., Is the organization listed as a "Fortune 1000" company or not?). RVCB requires that a client be fully specified as one of these two subtypes. A client must be either a non-profit or a for-profit organization (but can not be both). - Hint: As suggested above, you will be representing "subtype" entities for the client entity. While you will want to show a primary key for the client "super type" entity, it will not be necessary to show a primary key for the "subtype" entities associated with the entity. For an example, see Figure 8 -19 on p. 277 of the textbook. - When a client organization wishes to hold a conference in the Rogue Valley, the client will submit a conference request application to RVCB. The conference request application will include a variety of information including: the name of the proposed conference (e.g., 2022 Summer Theater Arts Festival), the purpose of the conference, client contact person, client contact person phone number, the requested starting date and ending date for the conference the expected, and the specific types of activities desired (e.g., large group meeting, presentation, cross country skiing). (Note: There could be more than one type of desired activity listed in the request.) Each organization that is considered to be a "client" must have submitted at least one conference request to RVCB. Some client organizations return to the Rogue Valley year after year and have submitted many conference request applications over the years. While most conference request applications are associated with only one client, it is possible that a conference application request may be submitted jointly by more than one client (e.g., it is not unusual for a conference to be sponsored by more than one client organization). An application is not accepted unless it is associated with at sponsoring client organization. - When a conference request application arrives at RVCB's office, the application is immediately assigned to at least one of RVCB's employees. There are some large conferences that are assigned to more than one employee. Some -- but not all -- of RVCB's employees were hired especially for their conference planningrelated expertise. These employees are the only RVCB employees that handle the applications. Other RVCB employees do not handle the applications. The RVCB employees with planning-related expertise have typically handled numerous conference requests. - As part of the system, RVCB would like to keep track of which its employees are supervised by other employees. There are several management-level employees at RVCB who supervise one or more other RVCB employees. However, some employees do not supervise anyone. Each employee is supervised by a maximum of one other employee. Ms. Serena Harlan, the Director of RVCB, is not supervised by anyone. Information stored for each employee at RVCB includes: social security number, name, home address, home phone number, title, and work phone number. - Hint: The ER diagram you develop should include a unary relationship involving the employee entity. - Each conference request application generates one or more conference activity events. Each conference activity event is described by a unique activity event code, activity date, activity starting time, activity ending time, and activity description. (Note: For purposes of record keeping, each "activity event" is limited to one date - if an ongoing conference event spreads out over several dates then the "event" is split into different instances in the database corresponding to each date (e.g., a three-day group meeting would be represented as three instances of activity events - one activity event would correspond to each of the three meeting days).) A given activity event is associated with one and only one conference request application. - Each conference activity event is assigned to a specific conference activity location in the Rogue Valley (e.g., the Siskiyou Hotel and Meeting Center, the Medford Conference Hall, the Shakespeare Learning Center). Each "activity event" is assigned to one and only one conference activity location. Each conference activity location is identified by a conference location identification code, along with location name and location address. So that all relevant location me consige for conference planning, the list of locations stored in RVCB's database should include all possible locations in the Rogue Valley where a conference event might take place. While some locations have been assigned to be the site for many conference events, there may be some locations that have noe beiated with any events. - Each conference activity location is associated with one or more specific instances of a conference facility. For example, the facilities associated with the Siskiyou Hotel and Meeting Center includes several meeting rooms, presentation rooms, banquet rooms, and two large areas for outdoor events. RVCB would like to keep track of each of the instances of facilities at each location. Information to store about each facility includes the facility code, facility type (e.g., meeting room, presentation room, etc.), facility capacity, and comments (to provide additional information about each facility). An instance of a facility is associated with only one conference location
