Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

INFO20003 Semester 1, 2023

Assignment 1: ER Modelling

Due: Friday 31st of March 2023, 5:59pm

Submission: Via LMS https://canvas.lms.unimelb.edu.au/

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 22^nd 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.

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 22^nd 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?

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:

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.

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, 30^th 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.