Database Design – ITS113
Assignment – Social media
This assignment puts the students in a hypothetical, but realistic, scenario where they design a
database intended to support a web site with a social media platform as illustrated in Figure 1.
The assignment is divided into two parts which are intended to reinforce the iterative
methodology used in a typical project. Part A requires each student to use business rules to
develop the first draft of the design. Part B builds on part A to refine the model and deliver a
MySQL implementation of the database.
Page 1 of 3
Figure 1: Social media application
Anyone should be able to view the contents of the site. Users can create an account and this will
give them the ability to create a post with a picture, title and a description. Users with valid
accounts can add comments to any post. Valid users should also be able to rate posts and
comments, with positive, neutral (default) or negative sentiment. All posts and comments should
be time stamped. Deleting a post should also delete all comments associated with it.
Deliverables
Part A
Due: 21:00, 19 April 2020
This part of the assignment should be delivered as one component:
• Entity relationship model document – This component should be uploaded to Moodle as a
single file. The preferred format is PDF, however Libre Office or Microsoft Office files are
also acceptable.
Entity Relationship Model Document
This document should capture the business rules, based the provided information. Students will
need to identify the entities involved and create external model(s) that show their
relationships, then combine these into a unified conceptual model that represents the logical
view of the entire database. A combination of entity relationship diagrams and a data
dictionary should be used. Students should document the assumptions they make and
provide reasoning when faced with design choices that are not straight forward.
The information provided in the scenario is far from exhaustive and a process of discovery will be
required to gain a better understanding. Students will have the opportunity to ask questions
during tutorials and should also perform their own research to discover how real world
organisations operate. The documentation should capture this discovery process and provide
proper attribution for the sources of information in the form of references.
Part B
Due: 21:00, 24 May 2020
This part of the assignment should be delivered as two components:
• Entity relationship model document – This component should be uploaded to Moodle as
the first file. The preferred format is PDF, however Libre Office or Microsoft Office files are
also acceptable.
• MySQL database – This component should be uploaded to Moodle as the second file. It
should be a MySQL dump file, exported from your database.
Page 2 of 3
Entity Relationship Model Document
Based on the feedback received in assessment of part A, students will refine the conceptual
model and data dictionary produced in part A. The model should be normalised to at least 4NF
and the documentation should provide evidence of normalisation. The document should also
have examples of SQL queries that demonstrate the use of the database, in particular the
insertion and retrieval of data.
MySQL Database
Students will implement the revised model as a MySQL database. The database should be
populated with enough sample data to demonstrate how it works. All tables should have data –
approximately three to five rows per table should be sufficient.
The MySQL database should be exported into a MySQL dump file. This can be achieved from
MySQL Workbench, using the Server/Data Export menu item. Students should generate an SQL
dump with the following settings:
• Select: All objects in the schema
• Select: Dump Structure and Data
• Select: Export to Self-Contained File
• Enable: Create Dump in a Single Transaction
• Enable: Include Create Schema
• In advanced options:
◦ Enable: complete-insert
◦ Disable: extended-insert
Page 3 of 3
The post Database Design – ITS113 appeared first on My Assignment Online.