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.
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.
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.
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 (COVID19) 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
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-
• 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
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
• 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.