Virtual Campus (VC) is a social media firm that specializes in creating virtual meeting places for students, faculty, staff, and others associated with different college campuses. VC was started as a student project in a database class at Cyber University, an online polytechnic college, with headquarters in a research park in Dayton, Ohio. The following parts of this exercise relate to different phases in the development of the database VC now provides to client institutions to support a threaded discussion application. Your assignment is to draw an ERD to represent each phase of the development of the VC database and to answer questions that clients raised about the capabilities (business rules) of the database in each phase. The description of each phase will state specific requirements as seen by clients, but other requirements may be implied or possibly should be implemented in the design slightly differently than the clients might see them, so be careful to not limit yourself to only the specifics provided.

a. The first phase was fairly simplistic. Draw an ERD to represent this initial phase, described by the following:

• A client may maintain several social media sites (e.g., for intercollegiate sports, academics, local food and beverage outlets, or a specific student organization). Each site has attributes of Site Identifier, Site Name, Site Purpose, Site Administrator, and Site Creation Date.

• Any person may become a participant in any public site. Persons need to register with the client's social media presence to participate in any site, and when they do the person is assigned a Person Identifier; the person provides his or her Nickname and Status (e.g., student, faculty, staff, or friend, or possibly several such values); the Date Joined the site is automatically generated. A person may also include other information, which is available to other persons on the site; this information includes Name, Twitter Handle, Facebook Page link, and SMS Contact Number. Anyone may register (no official association with the client is necessary).

• An account is created each time a person registers to use a particular site. An account is described by an Account ID, User Name, Password, Date Created, Date Terminated, and Date/Time the person most recently used that account.

• Using an account, a person creates a posting, or message, for others to read. A posting has a Posting Date/Time and Content. The person posting the message may also add a Date when the posting should be made invisible to other users.

• A person is permitted to have multiple accounts, each of which is for only one site.

• A person, over time, may create multiple postings from an account.

b. After the first phase, a representative from one of the initial clients asked if it were possible for a person to have multiple accounts on the same site. Answer this question based on your ERD from part a of this exercise. If your answer is yes, could you enforce via the ERD a business rule of only one account per site per person, or would other than a data modeling requirement be necessary? If your answer is no, justify how your ERD enforces this rule.

c. The database for the first phase certainly provided only the basics. VC quickly determined that two additional features needed to be added to the database design, as follows (draw a revised ERD to represent the expanded second phase database):

• From their accounts, persons might respond to postings with an additional posting. Thus, postings may form threads, or networks of response postings, which then may have other response postings, and so forth.

• It also became important to track not only postings but also when persons from their accounts read a posting. This requirement is needed to produce site usage reports concerning when postings are made, when they are read and by whom, frequency of reading, etc.

d. Clients liked the improvements to the social media application supported by the database from the second phase. How useful the social media application is depends, in part, on questions administrators at a client organization might be able to answer from inquiries against the database using reports or online queries. For each of the example client inquiries that follow, justify for your answer to part c whether your database could provide answers to that inquiry (if you already know SQL, you could provide justification by showing the appropriate SQL query; otherwise, explain the entities, attributes, and relationships from your ERD in part c that would be necessary to produce the desired result):

• How many postings has each person created for each site?

• Which postings appear under multiple sites?

• Has any person created a posting and then responded to his or her own posting before any other person has read the original posting?

• Which sites, if any, have no associated postings?

e. The third phase of database development by VC dealt with one of the hazards of social media sites— irresponsible, objectionable, or harmful postings (e.g., bullying or inappropriate language). So for phase three, draw a revised ERD to the ERD you drew for the second phase to represent the following:

• Any person from one of their accounts may file a complaint about any posting. Most postings, of course, are legitimate and not offensive, but some postings generate lots of complaints. Each complaint has a Complaint ID, Date/Time the complaint is posted, the Content of the complaint, and a Resolution Code. Complaints and the status of resolution are visible to only the person making the complaint and to the site administrator.

• The administrator for the site about which a complaint has been submitted (not necessarily a person in the database, and each site may have a different administrator) reviews complaints. If a complaint is worthy, the associated offensive posting is marked as removed from the site; however, the posting stays in the database so that special reports can be produced to summarize complaints in various ways, such as by person, so that persons who make repeated objectionable postings can be dealt with. In any case, the site administrator after his or her review fills in the date of resolution and the Resolution Code value for the complaint. As stated, only the site administrator and the complaining person, not other persons with accounts on the site, see complaints for postings on the associated site. Postings marked as removed as well as responses to these postings are then no longer seen by the other persons.

f. You may see various additional capabilities for the VC database. However, in the final phase you will consider in this exercise, you are to create an expansion of the ERD you drew for phase three to handle the following:

• Not all sites are public; that is, open for anyone to create an account. A person may create one or more sites as well as groups, and then invite other persons in a group to be part of a site he or she has created. A group has a Group ID, Group Name, Date Created, Date Terminated, Purpose, and Number of Members.

• The person creating a “private” site is then, by default, the site administrator for that site.

• Only the members of a group associated with a private site may then create accounts for that site, post to that site, and perform any other activities for that site.