INFO90002 A1 2023s1 The University of Melbourne
INFO90002 2023 Semester 1 - ASSIGNMENT 1
Weighting: 20% of your total assessment
Group Assessment: Groups of four (4) students from the same tutorial
Assignment deadline: Check Canvas
Assignment submission: Canvas
CASE STUDY: The Psi Social Network Website
Psi is an online social network website for enthusiasts of philosophy. Users who are new to the site
can create an account, after which they can log in and do a variety of things as described below.
The details stored for user accounts are username, email address, password, first name, last name,
date of birth (DOB), date of account creation, and profile where users can write about themselves in
free text format. Furthermore, each time a user logs into the system, a login record is created
against that user account with the datetime and IP address of the login. The first time a user creates
their account, they are logged into the site.
The Psi system also stores a dynamic list of interests, which initially consists of some common
interests. Via a user options page, users have the option to select as many of these interests as they
like, and they can also add new interests to the list for selection. These user interest selections can
then be used by the Psi system for various purposes, particularly personalisation of the user
experience.
There are two components in Psi that enable social networking and communication. The first is a
Facebook-style newsfeed and the second is a traditional web forum.
For the newsfeed to work it must be possible to store posts as well as comments on those posts. As
well as text content, the user who created each post/comment and the datetime of the
post/comment is recorded. It is also possible to like a post/comment and for each ‘like’ we need to
store who did it. Furthermore, similar to Facebook and LinkedIn, there are different types of likes
that can be given, such as the usual Like, Celebrate, Support, etc. Psi keeps a list of these ‘like types’
stored, including the path where an image icon of the ‘like type’ is located.
The newsfeed posts that are shown to a user are determined by a basic Facebook-style social
network, where two existing users can become friends; if A is friends with B, then B is friends with A.
Thus, the database needs to be able to store information about user-user friendships.
As well as recording friendship connections, the Psi system periodically generates and needs to store
friendship suggestions, such that two users who are not already friends appear on each other’s
friendship suggestions list as determined by the system.
The forum is divided into a set of forum categories. For the forum to work, it must be possible for a
user to initiate a forum discussion thread under one of the various forum categories. Once an initial
discussion thread posting is made, users can then respond to the thread.
In order to keep track of what topics are popular in the Psi social content, the system periodically
runs a script that extracts all the keywords from the latest newsfeed and forum posts. A keyword
might already have appeared in a previous post, but each time a keyword is extracted from a post, a
INFO90002 A1 2023s1 ? The University of Melbourne
record needs to be inserted that records the fact that the keyword appears in the post, as well as a
count of how many times it appears in the post.
Apart from these social elements, users can also do bite-sized educational modules to familiarise
themselves with and learn more about various topics in philosophy. Each module can be done as a
one-off, standalone unit or as part of a sequential grouping of modules known as a collection, where
each collection falls under some philosophy theme.
For example, suppose there were three modules, Module 1, Module 2, and Module 3, and a
collection, Collection 1, which was categorised under the theme of Ethics. Furthermore, Collection 1
consists of Modules 1 to 3. A user could complete Module 1 by itself as a standalone activity, or they
could explicitly choose to do Module 1 as the first module in sequence as part of the three modules
that constitute Collection 1.
In order to set up this system of modules, collections and themes, the following pieces are required.
Firstly, site moderators will need to be able to create and manage a list of themes (e.g., Ethics, Logic,
Epistemology), as well as collections of modules. Collection entries need to store the title of the
collection, a possible description, and the theme of the collection.
Secondly, for each module, the database needs to store the module title, a brief description of the
module, and HTML content that is displayed when the module is loaded.
Thirdly, each module can fall under one or more collections, and when a module is connected to a
collection, there needs to be a way to indicate the position of that module within the sequence of
collection modules.
Finally, once a user has gone through the content of a module and reached the end of the page,
there will be a ‘Completed’ button that they can click to indicate they have completed the module.
Once they do so, the fact that the user has completed the module needs to be recorded, including
the datetime of the completion. Also, there needs to be a way to store whether the user completed
the module as part of a collection (and if so, which collection) or as a standalone module.
Philosophy enthusiasts are big on books, so the Psi network also offers a small online book
marketplace. In this marketplace, users can create entries for books that they wish to sell. The
details that are to be recorded for each book are the book title, ISBN, the primary philosophy theme
that categorises the book, the year of publication, the Psi user who is selling the book, the minimum
sale price, the datetime that the book sale entry was created, final auction deadline, and whether
the book has been sold or not. Book author information is to be stored in the format of “author1
name; author2 name, …”.
Once this book sale record has been created, other users can bid on the book, starting from the
lowest price nominated by the selling user. For each placed bid the system records the datetime at
which each bid was made, from which IP address, and by which user.
At the end of the auction period, the bidder with the highest amount on the book wins the auction;
the Psi system records the fact that the winning bid won and any other bid on the book is marked as
a lost bid. After that the system generates a notification to the seller and to the winning bidder
INFO90002 A1 2023s1 ? The University of Melbourne
containing the email addresses of both. The notification details are recorded separately including
recipients (seller and winner), the winning bid, and the datetime of when the notification was sent.
Psi also provides a simple note taking feature for users to add general text notes about any thoughts
or considerations they might have. For each note the system records written content and the
datetime when the note was added.
Finally, the Psi platform offers a general search tool, which is accessed via a search bar located at the
top of the site and which returns site elements related to the input search term. Each time a search
is made, a record needs to be stored, that records the user who did the search, the search term and
the datetime of the search.
TASK: Your group needs to provide:
I) The record of group members contribution (see Appendix).
II) A physical Entity Relationship model using Crows Foot notation suitable for a MySQL relational
database version 8.0 or higher. Export an image of the ER model (you can export as pdf).
III) Brief explanatory notes (optional). For example, if you are replacing a very complex PFK with a
surrogate key, you need to specify what would be PFK in that entity that you replaced.
IV) A copy of your final workbench file (format .mwb).
Assignment Submission:
ONE GROUP MEMBER should submit the assignment via the CANVAS LMS
https://lms.unimelb.edu.au
ONE PDF document named as your Group number id (e.g. Wed10-1.pdf) on or before the deadline,
containing:
Student name and Student Number of every student in your group
Work breakdown per team member (contribution listed and also measured 1-100% per
team member)
Legible image of your Physical ER Model in Crows foot notation
Explanatory notes (maximum 150 words) – optional since your model should be sufficiently
self-explanatory. For example, if you are replacing a very complex PFK with a surrogate key,
you need to specify what would be PFK in that entity that you replaced.
Your statements and design decisions must not contradict the case study and the discussions
on Ed Discussions Forum.
(Optional) You may provide some limited explanation behind your design decisions (e.g.
where you replace ‘too many’ natural keys with one surrogate key)
N.B. If you fail to submit legible models you will be penalised 10% of your total grade for this
assignment.
ONE COPY of your team's final MySQL Workbench modelling file (with an .mwb extension) of the
Physical ER model on or before the deadline.
INFO90002 A1 2023s1 ? The University of Melbourne
Late Submissions
Assignments that are late without a formal granted deadline extension from the subject coordinator
will attract a penalty of 10% for each Academic Day as per the School of Computing and Information
Systems policy.
Appendix
Assignment 1 group members contribution/ Work breakdown
Tute Group no.
Group members list (names and IDs and %% contributed); put the team leader as the first one.
The first draft of this table must be done at the first group meeting, then update the table as the
group progresses through the assignment. Keep notes of every member contribution or lack of
response.
Add rows to the table as needed.
Contribution to group work rules:
%% is reflection how much the member contribution to the group’s work improved the outcome so
only contribution to developing the solution counts. E.g. a member attending all meetings but not
providing any parts of the solution contributed 0%.
In case of equal contribution by all group members, everyone’s contribution is 100%; for members
with lower contribution, estimate their contribution in relation to members contributing 100%.
In case of an unresponsive group member, contact your tutor and subject coordinator immediately,
do not wait till closer to the deadline.