INFO20003 S2 2020 Assignment 1 INFO20003 Semester 2, 2020 Assignment 1: ER Modelling Due: 6:00pm 4th September 2020 Submission: Via LMS https://canvas.lms.unimelb.edu.au/ The Royal Society The Royal Society was founded in 1660 and is the oldest continuous scientific organisation in the world. At any time, there are only 2000 members who have all achieved some scientific achievement such as a Nobel prize, Fields medal (mathematics) or Turing award (computer science). You are requested to design a database to be used by The Royal Society. Membership The Royal Society has two categories of membership: 'fellowship' and 'honoured fellowship'. Within each category of membership there are several types of members. For example, in the fellowship there are: 'Provisional', 'Restricted' and 'Full' Membership. In the honoured fellowship category, there are 'Esteemed Royal fellow', 'Overseas fellow', '50 Year fellow', and 'Life fellow'. You must first attain full membership as fellow of the Royal Society to be considered for an honoured fellowship category. We need to store the membership type history for all members tracking how they progressed through various memberships. For example, the dates they were a provisional member, then a restricted member and finally a full member of the Royal Society need to be captured. About each member of the Royal Society we store their title e.g. ("Sir", "Lord", "Duchess", "Earl", "Barron", "Professor", "Dr", "Mr" and many more), their first name, middle names (if any), last name, current residential address, email and phone number(s). One of the most important details is the date they signed the 'Charter' book which signifies the date they formerly joined the Royal Society. The number in the charter book is their Royal Society membership number. Royal Society also stores the date of death for deceased members. Application To become a member of The Royal Society, an application is necessary. The application must have the support of two Royal Society members who hold full membership or honoured fellow category status. Applicants must provide their first, middle names, last name, date of birth, home address, marital status, and the application must have two written references provided INFO20003 S2 2020 Assignment 1 by the nominating members. The written references are stored in the database and must be no longer than 2,500 words. The member who nominates an applicant first serves as his primary referee and must provide a statement of achievement of no more than 300 words in support of the application for membership. This statement is also stored with the application. The membership committee of the Royal Society approves or rejects applications. The database needs to store the date the applicant applied and the date and time the applicant was approved. If a membership application is rejected, the database must record the date of the rejection and the reason for the rejection. There are currently 173 rejection categories and the rejection types are frequently updated, but never deleted. For example, no member has been rejected for a 'pistol dual' since 3-August-1773. Once rejected, an applicant can reapply for membership after five years have elapsed. Subsequent re-applications are treated as new applications but must be linked to the original application. Event Members of the Royal society may organise different events. Events can be conducted in one of the 12 rooms in the central London office of the Royal Society. In that case, members need to book a venue for their event as detailed below. Nowadays given the Coronavirus (COVID- 19) pandemic situation, events such as summer science school are happening virtually. For virtual (online) events, there is no need of venue booking. Details about events such as event type, date, venue (if applicable), capacity limit, status (“pending approval”, “upcoming”, “happening now”, “postponed”, “cancelled”), online webinar link (if applicable) and registration link should be recorded. The Royal Society database stores only the registration link without any further details of the registration process. In case of cancelled events, the database updates the status of the event as “cancelled” but still keeps the event in the database. Events can be public (such as conferences, lectures, workshops, training sessions and seminars), or private (such as scientific meetings and dinners). One or more members can be involved in organising a given event and be responsible in different roles such as president, keynote speaker, chair, coordinator etc. For past public events such as lectures and trainings, one or more video or audio recordings can be recorded and stored in the database to be made available to public. Venue Booking With 12 rooms available for venue hire, the central London location of the Royal Society is ideal for hosting events. Each room has different capacity depending on the room organisation/layout. For example, a conference room in theatre seating layout can host 60 people but in boardroom layout the same room can host only 24 people. It is therefore crucial to record these details so organisers can make the right choice depending on the nature of INFO20003 S2 2020 Assignment 1 the event they are booking the venue for. Different layout settings include but are not limited to theatre, board room, hollow square, U shaped, Round tables/dinners, cabaret style and reception. For each room we need to capture its dimensions (width and length) and the room floor. Any booking request can either be approved or rejected depending on the availability of venue. Also, each booking has a booking rate that depends on the capacity of the room (basic fee) and the event type. For example, education-related sessions have 50% discount over the basic fee, while all private events are double the basic room fee. Business Requirements Your database design needs to be able to meet the business' need to answer questions such as: • How long was Stephen Hawking a member of The Royal Society? • How many members joined more than 50 years before they passed away? • How many rejected applicants had Sir Isaac Newton as the Primary referee? • Who applied the maximum number of times to be accepted by The Royal Society? • What was the most common rejection reason for members rejected in the years 1700- 1800? • Which member has been involved in the most ‘Gala’ events as keynote speaker? • How many events received 50% discount in 2019? • How many onsite public events have recorded videos of the event? • Which room has the largest difference between its maximum and minimum capacities for different configurations? • How many scientific meetings are hosted in “The Royal room” using U shaped layout? • What is the rate of conference room used for a python training workshop organised for college students? INFO20003 S2 2020 Assignment 1 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 on a separate page. There is a 400-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. • As part of your submission you must submit your final MySQL Workbench .mwb file of your Physical ER Model. NOTE: Items 1 (Conceptual Model & Physical ER model) and 2 (Assumptions) must be submitted as a single PDF. The title of the PDF document must be your student id (e.g. 987654.pdf). Similarly, the title of your .mwb file should be your studentid.mwb (e.g. 987654.mwb). Assignment Submission: You are to submit the assignment under the Assignments tab on Canvas LMS. Assignment 1 has two submission links for the following files: 1. ONE PDF document named as your student id (e.g. 987654.pdf) containing a legible (hand drawn) picture of conceptual model in Chen’s notation, a legible picture of your Physical ER Model and your assumptions (if you stated any). Submit this single PDF document under the “Assignment 1 PDF file” link. NOTE: If you fail to submit legible models you will be penalised 10% of the mark for this assignment. 2. Your final .mwb MySQL Workbench file of the Physical ER model. Submit your .mwb file under the “Assignment 1 mwb file” link. INFO20003 S2 2020 Assignment 1 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, 3rd of September. Medical certificates need to be at least two days in length. To request an extension: • Email Farah Zaib Khan (
[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 Therefore, it is our recommendation to students that you attempt every assignment and every question in the exam. GOOD LUCK!
