University Case study - design, develop and document a prototype Database system

Prototype Database system

  • Design and implement a database system in Oracle based on the case study below. You will be expected to identify the management needs of the organisation and present how they are solved and dealt with by your database design and implementation.

  • ScenarioLondon 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 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.

  • For this study, student gender should be recorded as ‘M’ for male or ‘F’ for female.

  • The name of a student/staff should not contain any numeric characters.

  • The staff salary should be greater than £10,000 and not exceed£150,000.

  • Student email address should have an @ symbol present to be valid.

  • The classification of module results must only be one of ‘F’, ‘P’, ‘M’, ‘D’, ‘NS’, ‘FX’.

  • Each student’s start-date of a programme must always have a value.

  • Each student’s end-date of a programme should not be earlier than their startdate and it should be minimum of 3 years.

  • The student degree graduation date should be greater than ‘27-Oct- 2015’.

Powered by