Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INFO20003
Assignment 1: ER Modelling
Academic conferences
An academic conference is an event in which researchers present their findings and hear about the latest work
within their research area. Researchers can submit their research papers to conferences, where reviewers check
the quality of each submitted research paper, and then accept high-quality papers for presentation. Your team is
going to be helping to create the technical infrastructure for the academic conferences, papers, and researchers.
As part of this, you will be creating a MySQL database to store this information. The following specifications have
been provided to you to assist in your design.
Conference event
For each conference event, the system records its details, that are – name (e.g., International Conference on Data
Engineering), occurrence number (e.g., 22, which means it’s the 22nd occurrence of the International Conference
on Data Engineering), location (e.g., Melbourne, Australia) of the conference, the start date, the end date of the
conference, and the URL to the conference event website. The location, dates, and the website of different
occurrences of a conference are different. Each conference has at least one ‘track’, where each track has different
requirements on the papers that can be submitted there. Each track is associated with the following information:
a unique track type (e.g., long paper, short paper, demonstration paper, etc.), paper submission guideline (as a text
description of maximum 500 characters), and page limit (e.g., 10 pages) of the submission. Each track is associated
with exactly one conference. The information of a track (i.e., the track type, paper submission guideline, and page
limit of the submission) can be different for different occurrences of the conference that the track is associated
with.
Each track has at least one ‘session’. Each session has a session name (e.g., Session on indexing), start date and
time, end date and time, and the number of papers to be presented in that session. Each session has exactly one
chairperson, who is a researcher. A researcher can be the chairperson for any number of sessions.
Researchers
For each researcher, the system records the researcher’s details such as first name, last name, a unique email
address, and the name of the institution(s) the researcher is currently affiliated with. A researcher can be currently
affiliated with multiple institutions. The system also stores the research supervision relations of the researchers.
Each researcher can have any number of supervisors, where each supervisor is also a researcher. A researcher can
supervise any number of other researchers. For each supervision relation, the system stores the start and end date
of the supervision (e.g. “Farhana Choudhury and Renata Borovica-Gajic supervised Timothy Hermanto between
01/01/2020 and 31/12/2022”). If the supervision is ongoing, the end date is unpopulated.
INFO20003 S1 2023 A1 Page 2 of 4
Papers
The system maintains the information of the papers that are submitted to the conferences. Each submitted paper
is associated with the following information: unique ID, title, abstract, and the authors. There can be one or more
authors of a paper, where each author is a researcher. A paper is submitted (only once) to a particular track of a
conference. Each track of a conference can have any number of papers submitted to it. Each submission of a paper
is assigned to reviewers, where each reviewer is a researcher. A paper can have any number of reviewers assigned
to it. A researcher can review any number of papers submitted to any of the tracks. Some submitted papers may
not be assigned any reviewer and get rejected if they do not follow the submission guideline of that track.
After the reviewing process, a paper can be either rejected or accepted to that track. For each rejected paper, the
following information are stored: date of rejection, reasons for rejection (as a text description of maximum 100
characters). Each accepted paper is assigned a start page number and an end page number (a note as the
explanation of the page numbers - conferences publish a booklet called conference proceedings, where the start
and end page numbers of each accepted paper stored in the system correspond to the page numbers in that
conference proceeding). The accepted papers are assigned to sessions (at least one session), where the paper can
be presented and discussed. Each session has at least one paper assigned to it. Each paper assigned to a particular
session will be presented by a researcher. A researcher can present any number of papers on the same session or
different sessions. For the given paper presentation, the system captures the presentation date and time, the
presenter, the paper and the session.
Conference committee and registration
For each conference, there is an organisation committee consisting of at least one researcher who help organise
the conference. A researcher can be in the organisation committee of multiple conferences.
Researchers need to register to attend a conference. Each conference offers one or more types of registration
options. The system stores the registration information. Each registration option/type is associated with the
following information: type of the registration (e.g., student registration, member registration, etc.), registration
fee, and the final date to register for that option. When a researcher registers to a conference, the system stores a
unique registration ID and the date of registration. A researcher can make multiple registrations.
Business Requirements
Your database design needs to be able to meet the business's needs to answer questions as:
1. How many researchers have organised at least 5 conferences?
2. How many times the researcher named “Farhana Choudhury” has presented in any conference?
3. Which conference has the highest number of accepted papers?
4. Which researchers supervised by “Renata Borovica-Gajic” have not reviewed any paper in any
conference?
5. Which researchers have more than one paper accepted in the same conference, but in different tracks?
6. How many researchers are associated with the institute “The University of Melbourne”?
7. In which year did the researcher “Farhana Choudhury” and “Renata Borovica-Gajic” both registered for
the type “member registration” in the same conference for the first time?
8. Which researcher has the highest number of papers accepted in total in any conference?
9. Who presented an accepted research paper ID 551, at session “Indexing and query processing” at 10am
on 22nd of May 2022?
10. Who are the session chairpersons of the conference named “International Conference on Data
Engineering” and conference occurrence 22, but have not registered to the conference yet?
INFO20003 S1 2023 A1 Page 3 of 4
Instructions
Assignment 1 is worth 10% of your final mark. The assignment will be graded out of 100 marks as described in the
table below:
ER Physical Model with assumptions 80%
Conceptual Model in Chen’s Notation 20%
.mwb Physical Model File submitted Assignment Hurdle
You are to analyse this business case and design a Conceptual ER Model in Chen’s notation (can be hand drawn)
as taught in class and a Physical ER Model for a MySQL Relational Database in Crow’s foot notation (modelled
with MySQL Workbench).
You may list any assumptions you have made about the model. There is a 200-word limit for assumptions.
Assumptions must not be used to simplify the assignment, but only to justify your decision about any ambiguity in
the study.
Assignment Submission
You are to submit the assignment under the Assignments tab on Canvas LMS. The submission will require you to
submit two files:
1. A SINGLE PDF document containing:
i. a legible (hand drawn, or otherwise modelled) picture of a conceptual model in Chen’s notation
ii. a screenshot/export of your Physical ER Model done in MySQL workbench. Ensure that all attributes
are readable, and tables are fully expanded.
iii. any assumptions you made (limit this to a maximum of 200 words)
Note: You can use an online tool like https://smallpdf.com/merge-pdf to merge multiple PDFs together.
2. A copy of your final .mwb MySQL Workbench file of your Physical ER model.
Note: This model will only be used by staff in circumstances where the screenshot/export of your model in the
PDF is unreadable (i.e. if we have to open this file, a penalty of 10% will occur), so remember to include the
export of your physical model in the PDF!
If you fail to submit clear and legible models your assignment will be penalised.
INFO20003 S1 2023 A1 Page 4 of 4
Unless you have an approved extension (see below), you will be penalised -10% of the total number of marks in
the assignment per day that your submission is late. For instance, if you received a 78% raw score, but submitted
2 days late, you'd receive a 58% for the assignment.
Requesting a Submission Deadline Extension
If you need an extension due to a valid (medical) reason, you will need to provide evidence to support your
request by 5pm, 30th of March. Medical certificates need to be at least two days in length.
To request an extension:
• Email Timothy Hermanto ([email protected]) from your university email address,
supplying your student ID, the extension request and supporting evidence.
• If your submission deadline extension is granted you will receive an email reply granting the new
submission date. Do not lose this email!
Reminder: INFO20003 Hurdle Requirements
To pass INFO20003, you must pass two hurdles:
• Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%)
• Hurdle 2: Obtain at least 50% (35/70) or higher for the combination of quizzes and end of semester exam
It is our recommendation to students that you attempt every assignment and every question in the exam.