London University is a prestigious institution educating students from all over
the world. It offers 40 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 university 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 5 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
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’
2. The name of a student/staff should not contain any numeric characters.
3. The staff salary should be greater than £10,000 and not exceed
4. Student email address should have an @ symbol present to be valid.
5. The classification of module results must only be one of ‘F’, ‘P’, ‘M’, ‘D’,
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 startdate and it should be minimum of 3 years.
8. The student degree graduation date should be greater than ‘27-Oct-
9. Staff and Student numbers should be generated automatically using a
sequence and a trigger.
a) A fully UML annotated E-R diagram 1 and 2 showing the entities, primary
and foreign keys, composite keys, relationships and optionality. (15 marks)
b) A relational schema of your database in 3NF, clearly indicating attributes, the
data type of each attribute, primary and foreign keys and which attributes are
nullable, giving reasons. List any assumptions you need to make.
c) Listings of the records in each table. There should be a minimum of ten
records in each table.
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.
e) Produce SQL listings on the specific questions based on the Case Study.
1. List all students’ details and order by student Id. [3 marks]
2. List of student reps in all programmes [4 marks]
3. List the names of all students who are enrolled on the BSc Computer
Science and are doing the Database module. [4 marks]
4. Find the student with the minimum marks in a specific module, giving
the name of the student and module. [4 marks]
5. Give a count of the number of students on a specific module that
started on 1st September, 2015. [4 marks]
6. Give total credits acquired by each student. [4 marks]
7. 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]
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.
g) Group presentation – presenting the design and implementation of the
solution in Oracle.
Place your order now to enjoy great discounts on this or a similar topic.
People choose us because we provide:
Essays written from scratch, 100% original,
Delivery within deadlines,
Competitive prices and excellent quality,
24/7 customer support,
Priority on their privacy,
Unlimited free revisions upon request, and
Plagiarism free work,