In groups of 5-6 students, you are required to design and implement a database to
address the requirements of a scenario provided by your tutor. Note that you will be
grouped with other students in the same seminar session.
As the scenario contains only a brief outline of a business case study, you will need to
state any business rules and assumptions that you have made in order to fill any gaps or
to address any ambiguities when putting together your database design. All the required
tables need to be implemented using Oracle APEX.
SUBMISSION A: Group Project Report
Each group member needs to submit a copy of their group’s report using the ‘ISDB
Group Project’ submission link set up on Blackboard. Any group member who fails to
submit a copy will be awarded zero marks. Any report that is submitted late will capped
at 40%. The report must use the assignment report template provided, containing the
following elements, in this order:
1. A header page which includes the name and ID of your team members and the
username and password of the APEX account(s) containing your group’s
implemented tables and reports/queries.
2. A list of any assumptions/clarifications you have made that have affected the
design of your database.
3. An ER diagram (using ‘Crow’s foot’ notation) providing the final design of your
database, which shows entities, attributes, primary and foreign keys, and named
relationships.
4. Entity specification forms documenting all your entities (using the template
provided).
5. The SQL scripts you used to create your tables (including all constraints) which
should match your entity specification forms.
6. A list of the SQL scripts (INSERT INTO ..commands) used to insert test data into
each of your tables, annotated to show that there is sufficient breadth and
volume of data to adequately test your queries/reports (see below).
2
7. 8 functional requirements generating reports/output that effectively
demonstrate the scope and functionality of the system, and for each: a) the SQL
script used to implement the requirement, and b) a screenshot of the run query
and output it produces. The queries you choose should critical queries illustrating
the breadth and potential of the implemented database, as well as your mastery
of SQL. Each script should be numbered and saved in your database.
8. A brief overview of the database features you would implement to ensure your
data is adequately secured.
9. A suggestion for one other database technology/information system that you
think might benefit the company, either now or in the future, providing a
rationale for this.
10. Evidence to show effective and timely project management using Scrum, the
contribution of each member to the project, namely attendance at team
meetings held in (and outside) your seminar sessions, sprint backlogs, tasks
uploaded to the group’s file Blackboard area and SQL scripts saved in members’
APEX account(s). Case study for Information Systems and Databases – February 2023
South London University’s Computing school wishes to set up a database to manage its external
speaker and placements processes. This is all part of developing links with local businesses and
giving students the opportunity to get some industry experience.
At present, individual members of the teaching team organise guest lectures, but this is all done in a
rather random fashion. They might use their personal contacts or meet an interesting speaker at an
external event and invite him/her to give a lecture or run a workshop for their students. If the
speaker agrees, then the lecturer will contact the timetabling unit to arrange a suitable date, time
and room for the lecture and then announce details of the event through the Blackboard
announcement system.
With the increasing emphasis on giving students ‘real world’ experiences, more and more of these
events are being run and the school wishes to make the most of the contacts they have and which
topics these contacts have expressed an interest in talking about. Unfortunately, these contacts and
speakers are not stored centrally, which leads to problems, for example:
• There have been occasions where multiple guest lectures have been arranged in one week
for a particular cohort of students, as each of the teaching staff concerned had no idea of
the other lecturers’ arrangements.
• A number of guest lectures were arranged on the same topic, thereby duplicating the
content of a previous lecture.
• Staff have independently invited speakers from the same company.
• Staff members have contacted an organisation to ask if it would consider offering
placement/workshop/ guest lecture etc, to find that the contact has already been
approached and has turned down the request - so is annoyed that he/she is being targeted
again.
• Staff members wanted to re-run specific guest lectures in a subsequent year for a new
cohort of students, but the staff member who had arranged the original lecture had left the
university without leaving details of the contact.
• Students have turned up to the staff room wanting to know where a lecture was being held.
etc.. etc..
The school is very keen to set up a database as it feels that if everything is centrally stored it will be
able eradicate such problems and to use the contacts to greater advantage, for example to see if a
speaker (or someone within their organisation) would be interested in providing placements/job
experience or live projects for students, as well as guest lectures. The Head of School hopes that a
sensible timetable can be put together showing the guest lectures for the whole of a semester which
students and teaching staff could access. He would also like to monitor attendance at each of the
lectures/workshops and also to collect any positive or negative feedback from students to see how
popular the talk has been, which might indicate whether to re-run a particular talk the following
year.
5
The Head of Computing has suggested that, with a new system, teaching staff could put in requests
for a guest lecture on a particular topic, and the system administrator might then be able to suggest
a suitable external speaker for that topic from information held in the database.
A small number of companies are already supplying ‘live’ projects to the department and the Head
of School hopes that a lot more can be found. The details of these also need to be stored centrally,
together with the member of staff who is running the live project. At present nobody knows who is
doing what. The marketing manager for the school would also like to know about these live projects
and their outcomes – if completed successfully, they could raise some good publicity for the
university. The new system should have some mechanism for informing the marketing manager that
the project has completed so that timely publicity materials can be produced.
The Head of School would also like there to be a designated staff member for each organisation,
responsible for nurturing and developing links with the organisation. Anyone wishing to contact a
company would need to liaise with the designated staff contact person. Members of staff should
have easy access to this information. The system should also be able to store a history of the
contacts made with each company and the reason for that contact, which teaching staff could refer
to.
The placement officer for the school also wants to maintain a database of placement or job
experience opportunities provided by organisations, and which students have been successful in
getting these placements, so that these students could be invited to talk to the student cohort on
their return from placement – again this is all part of getting students ready for the world of work.
School administrators will also benefit from this as currently they often unaware of which students
are doing placements. The new system will be able to clearly show which students are on placement
and who their designated supervisor is at their placement company (which may be different for each
student).
The Head of School and administrator think that useful reports could be generated from the
proposed database, for example to show how the school is interacting with businesses and the local
community, which lecturer has made the most effort at developing links with industry (and who
hasn’t bothered at all…), and many other outputs.
All in all, the current system is a mess and you have been called in to sort it out. The above
description is just a basic outline of some of the issues and problems. You can make any sensible
assumptions to fill in any gaps. Good luck!