Database Systems

Scenario
London University is a prestigious institution educating students from all over
the world. It offers 20 programmes from five disciplines. The university currently
uses an old legacy system which is file-based. Due to various difficulties, the
management have decided to modernize the system by having an online
system with a backend database.
As a group of consultants, you are required to produce a database system to
upgrade a part of the system that handles the student registration and
timetabling of students.
On arriving at the college each student must be enrolled on a programme and
be allocated a personal tutor who would oversee their academic needs at the
college. The maximum duration of a programme is 6 years for part-time, and 4
years for full-time mode. The minimum and maximum number of modules that
make up a year of a programme is six to eight modules for full-time, and four to
six for part-time students. Each module weight 15 credits, students are required
to obtain a total of 360 credits to be eligible for an award.
Each programme must have a student representative. The student rep is the
first point of call for fellow students with issues related to the programme,
modules, and teachings.
Each student must take and pass each module in a given year before they are
allowed to move into the next year of the programme or to graduate. A student
is normally allowed two attempts to pass a module. A particular module can be
offered as part of one or more programmes.
The University has 6 departments, each of which offers a number of
programmes. Each department has a Head of Department (Dean) and each
programme has a Programme Leader. Each module is assigned to a Module
Leader who oversees the teaching and assessment of the module. Principal
Lecturers manages other teaching staff, which can also include Programme
Leaders.
Staff details, such as, contact telephone numbers, speciality, qualification and
awarding institutions and previous employment must be recorded.
In order to achieve the business needs and ensure consistency of data in the
database you are to develop integrity and referential integrity constraints that
will ensure the correctness of the data when it is entered. Below is a list of
checks you should implement.
1. For this study, student gender should be recorded as ‘M’ for male or ‘F’
for female.
2. The name of a student/staff should not contain any numeric characters.
3. The staff salary should be greater than £0 and not exceed £150,000.
4. Student email address should have an @ symbol present to be valid.
4
5. The classification of module results must only be one of ‘F’, ‘P’, ‘M’, ‘D’,
‘NS’.
6. Each student’s start-date of a programme must always have a value.
7. Each student’s end-date of a programme should not be earlier than
their start date.
8. The student degree graduation date should be greater than ‘1-Jan-
2016’.
9. Student numbers should be generated automatically using a sequence
and a trigger.
Assessment
a) A fully UML annotated E-R diagram 1 and 2 showing the entities, primary
and foreign keys, composite keys, relationships and optionality.
(30 marks)
c) Listings of the records in each table. There should be a minimum of ten
records in each table.
(10 marks)
d) Ensure consistency of data in the database by developing integrity and
referential integrity constraints that will ensure the correctness of the data
when it is entered.
(15 marks)
e) Produce SQL listings on the specific questions based on the Case Study.
(40 marks)
1. List all students’ details and order by student name. [3 marks]
2. List of student reps in all programmes [3 marks]
3. List the names of all students who are enrolled on the BSc Computing
Programme and are doing the Database module. [3 marks]
4. Find the student with the maximum marks in a specific module, giving
the name of the student and module. [4 marks]
5. Give a count of the number of student on a specific programme. [4
marks]
6. Give a count of the number of students on a specific course that started
on 1st October, 2014. [4 marks]
7. Give total credits acquired by each student. [4 marks]
8. Give the total number of student studying in the university. [4 marks]
9. Find the oldest and youngest students in the university. [4 marks]
10.List the academic’s name and salary and their Principle manager’s
name and salary for all academic staff who earn more than their
managers. [ 7 marks]
5
f) Documentation must be logically organised with clarity and completeness.
TEAM WORK: Documentation stating how each group member
participated in the development and completion of the assignment.
(5 marks)
Submission
Please upload ONLY 1 file:
A word document with answers to all the assessed tasks (a,b,c,d,e and f).
The file must be called your studentid.doc (i.e. 1234567.docx or 1234567.doc)
DEADLINE: Summer 2017
PLAGIARISM & COLLUSION
http://www.uel.ac.uk/aple/academic/avoidingplagiarism/
FEEDBACK TO STUDENTS
Feedback is central to learning and is provided to students to develop their
knowledge, understanding, skills and to help promote learning and facilitate
improvement.
• Feedback will be provided as soon as possible after the student has
completed the assessment task.
• Feedback will be in relation to the learning outcomes and assessment
criteria.
• It will be offered via Turnitin GradeMark and an Audio file where
appropriate.
As the feedback (including marks) is provided before Award & Field Board,
marks are:
• Provisional
• available for External Examiner scrutiny
• subject to change and approval by the Assessment Board
All students are actively encouraged to collect feedback, review and consider
its recommendations and implications, and seek further advice and guidance
from academic staff where required.
6
Assessment Criteria
Deliverable – ERD Grade
Band
There is a clear indication in the answer that students have fully
understood the problem domain and spent a considerable amount of
time iterating over different solutions. All relevant entities, data
attribute and relationships have been identified and many-to-many
relationships have been eliminated. Documentation is to a
professional standard along with complete list of references. 70-100%
Excellent
Most of the relevant entities, data attributes and relationships have
been identified. Many-to-many relationships have been eliminated.
Any assumptions have been justified in the context of the case study
and problem domain. Documentation is of a high standard and there
is an evidence of iteration and adequate referencing. 60-69%
Good to Very
Good
About half of the relevant entities, data attributes and relationships
have been identified. The standard of documentation is satisfactory.
Evidence of iteration will be patchy. 50-59%
Satisfactory to
Good
Very little or no iteration. Less than half of the relevant entities, data
attributes and relationships have been identified. The standard of
documentation is generally weak. 40-49%
Pass standard to
satisfactory
Significant errors and misunderstandings. Entities, data attributes
and relationships have not been identified correctly. Level of detail
is inappropriate. Documentation is poor. Task not attempted or
incomplete or fails to identify the most obvious points. 0-39
Fail