Identify and implement database models.
Examine and design a data model for a given scenario.
Describe and apply SQL to create tables and generate queries.
Design, develop and implement advanced database solutions using SQL statements.
Design, develop and implement advanced database solutions using procedural languages.
Demonstrate good time management as a team, in the application of problem solving techniques in relation to database management.
Database Systems
Using only ORACLE, design and implement a realistic database system and develop a range of SQL queries based on the case study below. For the deliverables c, d & e enclose the necessary screenshots without fail.
You are required to submit:
a) A fully UML annotated E-R diagram 1 and 2 showing the entities, primary and foreign keys, composite keys, relationships and optionality.
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.
2. List of student reps in all programmes
3. List the names of all students who are enrolled on the BSc Computer Science and are doing the Database module.
4. Find the student with the minimum marks in a specific module, giving the name of the student and module.
5. Give a count of the number of students on a specific module that started on 1st September, 2015.
6. Give total credits acquired by each student.
7. List the academic's name and salary and their Principal manager's name and salary for all academic staff who earn more than their managers.
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.
Case Study:
Wisdom 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 has 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.
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 £10,000 and not exceed £150,000.
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', ‘NS', ‘FX'.
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 and it should be minimum of 3 years.
8. The student degree graduation date should be greater than ‘27-Oct-2015'.
9. Staff and Student numbers should be generated automatically using a sequence and a trigger.