IFB130 Assignment 1 Solution

Project - part A

Project overview
The IFB130 project gives you an opportunity to apply all the concepts and skills you acquire in the unit to a realistic database design scenario and reflect on the data requirements of an organisation.

A. Design of a database
B. Creation and use of a database


The task for part A
You are to design a solution for a database management system that addresses the needs of an interactive visualization tool.

In this part you will design a database by:
1. Creating an ER model for the database based on a use case;
2. Creating a relational schema for the database; and,
3. Assessing an alternative relational schema for the database using normalisation.

Weighting
Part A is worth 15 marks, for 15% of the unit.

Groups
This assignment can be done individually or in pairs.

If you choose to work in a pair, only one student should submit the assignment. Please provide the name and student number of the person you worked with on the front page. No consideration will be given to students who claim they did more work in their pair than the other student because this assignment can be done individually.



Submission
For this assessment you should submit a professionally presented Word document containing your solution to each task. ER models should be created using a computer drawing program, such as draw.io or Lucid Chart. Partial or fully handwritten submissions will not be accepted. This needs to be uploaded to the submission link on Blackboard.


Please note: extensions will not be granted for group submissions, regardless of the reason.










1
Background
You have been hired by the Brisbane based company Pulse Learning. Pulse Learning specialises in data visualisation to help educators better understand their student’s health and improve the workload within their units. The product they would like you to design will display correlations between sleep duration and assignments. The idea is that students will be provided with smart watches that will record their daily sleeping patterns throughout the semester.

After hours of meetings and a lot of emails the company have agreed on the screenshots of what educators should be able to see. It is now your task to design the database to allow the data to be stored so that it can ultimately be displayed.

The scenario – data visualisation


• basic information about students (their student id, first and last name, email address, full mailing address and phone number. Each student could have up to three phone numbers recorded.)
• smart watch information about students (which type of watch they were given, and what is its serial number).
• basic information about the available units (the unit code, the year and semester the unit is available, the name of the unit)
• which unit(s) each student is enrolled in
• the grades of each student for each assignment
• list of student pairs in the buddying system
A dynamic, overall grade point average (GPA) should feature in each student’s file and is calculated from all grades in their assignments.




The second interactive plot they would like to display is, within a selected unit and for a selected assignment, of each student per the number of hours they slept and their grade for this particular assignment, as shown below.

Some students can have a buddy if they wish: a buddy is another student who has done the course before. A student can only have one buddy throughout their studies. However, a student can serve as buddy to as many students they wish to, or none. The third interactive plot should show for each buddy, how well the students they are partnering with are sleeping within 3 periods of the semester, on average. By clicking on a buddy’s line, the name of the students they are supporting should appear, as well as their student number and their GPA (grade point average, calculated from their grades in all assignments across all their units) .





Part A tasks

Part A requires you to complete a number of sequential tasks to fulfill the requirements of the scenario. In this part you will design a database by:
1. Creating an ER model for the database;
2. Deriving the relational schema for the new database;
3. Demonstrating the constraints of the relational schema through examples; and
4. Verifying a previous (different) relational schema for the database using normalization;


Task 1 [5 marks]

• the entity types (0.5 marks),
• the binary relationship types (1 mark), • the complex relationship type (1 mark),
• the multiplicity of relationship types (1 mark),
• the attributes (and if applicable their domains) of entity types (0.5 marks),
• the attributes (and if applicable their domains) of relationship types (0.5 marks), and
• the primary key of each entity type (0.5 marks).

State any assumptions you make.


Task 2 [5 marks]

Derive a Relational Model from the conceptual schema you provided in Task 1 (it should match exactly).
1. List all the relations derived from the entity types of the ERD, and their initial attributes. (1 mark)
2. For each relationship type in the ERD justify whether it leads to a new relation, a new attribute in a relation derived from an entity type, or to a merger of two relations derived from two entity types. (1 marks)
3. List all the relations in the final relational model, and all their attributes (1 mark)
4. List all the constraints included in the conceptual schema in addition to the relevant primary keys and foreign keys. (2 marks)


Task 3 [2 marks]

Create four rows of sample data for each of the relations you identified in Task 2.

Based on one of the relations of your relational model and its sample data, specify an example of each of the following, and explain why you selected it:
• A delete operation that would run successfully
• An update operation that would run successfully
• An update operation that would not run successfully
• An insert operation that would not run successfully


Task 4 [3 marks]

Pulse Learning had an intern who prototyped a former version of the database, but they could never implement it, and they have already been told this is really poor design. This is what it looks like, with some sample data:

Student
ID Units
3345 IA33 FR12
QA25
3279 FR54
QA25
8754 IA33
4579 QA45


Student ID Student
Name Unit
3467 Adele X IA33 3345 Nikki
4666 John Snow FR54 3345 Nikki
2357 Malcom
Turnbull FR54 5798
Jack
8097 Mickey Mouse FR54 5798
Jack


Coordinator
Unit code Unit Name Coordinator ID Coordinator Name
FR54 French Studies 2556 Emma Macron
IA33 Data Mining 7643 Mark Zuck
QA25 Natural Language Processing 2234 Laurianne Sitbon
QA54 Search Engines 2234 Laurianne Sitbon




BONUS MARKS (2 marks, but your overall grade for this assignment will be capped to 15)

Write a scenario and the corresponding ERD that would display at least 5 entity types, at least one relationship type of each multiplicity (one-to-one, one-to-many, many-to-many), at least one complex relationship type (degree 3 or more), and at least one recursive relationship type. The model should also feature at least one derived attribute and one composite attribute.