The Overall Task – An Academic Recording and Management Database System
You are to design (logical and physical) and implement a data base system to accurately record and manage all aspects of student enrolments at The NoTreal University. Your system must provide for efficient and effective storage of all data related to Programs, Courses, Students and Enrolments and it must also have in-built integrity checking and enforcing capabilities.
The task will be completed progressively in three sections – Conceptual and Logical Design (Assignment 1), Physical Design and Implementation (Assignment 2) and finally you will create a prototype Management Reporting System and some Stored Procedures to track and manage some of the business rules of NoTreal (Assignment 3).
Assignment 1 – Conceptual / Logical Design
The first part of your work will be to understand and document the “business rules” in place at NoTreal. The output of this will be an Entity Relationship Diagram showing the data storage requirements, a State Transition Diagram showing the various stages through which students pass as they move through the University and a list of the major business rules.
The NoTreal requirements are described in the attached narrative but it is unlikely that this will contain all of the information you will need. You will also need to ask for any further information you need.
The Assessment Process
This assignment is due end of Week 4. Specifically you should submit by midnight on Saturday 18th June. BUT I will allow two submissions. One before the due-date and one after you have received some feedback on your first effort.
Following your first submission we will meet and I I will mark your work and provide you feedback on what you have done well and what could be improved. If the mark you achieve on the first submission is greater than or equal to 60% of the total marks available for the assignment I will also release the marks you have achieved and you may either accept that mark or if you wish you may address the issues I have raised and re-submit your updated assignment which I will remark and update your marks to reflect the added value in the answers.
If the mark you achieve on the first submission is less than 60% of the total marks available for the assignment you will still receive feedback but it may not be particularly specific and you will NOT receive any marks until the submission period ends. Of course you will still be able to (and be strongly encouraged to) make an improved second submission which I will remark and update your marks to reflect the added value in the answers.
No marks will ever be reduced based on a second submission however the marking of any improvements will be much more rigorous than for the first submission.
This process is designed to allow student flexibility in organising their work, the opportunity to receive feedback and improve their work based on their feedback. Note however that simply submitting a very poor attempt and expecting that you will get detailed instructions on how to fix it will not be successful, I am happy to help those who have made a real effort but I cannot do the work for you.
A more detailed marking scheme will be provided as a part of the feedback for your first submission but in broad terms the following mark breakup will be used.
- ER diagram(s) will be worth 70% of the assignment marks.
- Student State Transition Diagram will be worth 15%.
- Your Listing of the essential Business Rules 15%
Note your mark will be based on not just what you submit. Your ability to explain what you did within the interview will also affect your mark.
The NoTreal Student Requirements
The University offers degree Programs. Student are admitted to a Program and then enrol in a number Courses within that Program. By successfully completing the necessary Courses a Student is able to satisfy the requirements of the Program.
- To complete a PROGRAM a student must successfully complete a number of UNITS. Most programs will consist of 320, 240, 160 or 80 units.
- Students earn these units by completing Courses. Each course is worth a number of units generally 5, 10 or 20.
- If it is to count toward the program the student is completing a Course must be on the List of Approved Courses for that program. Typically a Course will be on more than one List of approved courses, although some course will not be on any!
- The list of approved courses for programs are subdivide into three groups – Compulsory, Directed and Elective. Each program must have a single Compulsory course group, may have one or more Directed elective groups and may have an elective group. Directed elective groups typically contain courses covering specialist areas within the program (for example in an IT degree there might be a direct group for specialist programming courses and another with advanced database courses.
- Students must complete all of the courses in the program’s Compulsory Group. Each Directed group has a minimum and advanced number of units that must be completed from the group specified. If electives are available there will be a maximum number of units which a student can complete from that group. If a course is compulsory then it will only be listed in the compulsory group, a course may be in more than one directed elective group. Courses which are compulsory in the program, or part of a directed elective group cannot be taken as an elective.
- Students must complete the minimum number of units in each directed elective group (this can be 0). Those who complete the specified advanced number of unitsin a directed elective group qualify for a SPECIALISATION in that area. (Not all directed elective groups lead to specialisations – for these the advanced number of units is set to 999999.
- Courses may have ASSUMED KNOWLEDGE specified – this assumed knowledge will be one or more other courses. Students are NOT allowed to enrol in a course without having completed the assumed knowledge.
- A student may not enrol in more than 40 units each Semester. There are two semesters each teaching Year.
- Courses are offered by Schools, Academics teach in courses offered by their School. The system must record details of the courses which an academic is able to teach.
- Not all courses are offered every semester. Students may only enrol in a course if it is being offered in that semester. When a course is offered there are a maximum number of students who may enrol in that course.
- Students may only enrol in courses that will count toward their program of study.
- Students who have outstanding fines or fees may NOT enrol in any courses until those debts are paid.
- Both grades and percentage marks are awarded for each course. The grades awarded are RP, FF, WF, WW, P, TP, C, D and HD. Grade may contribute to the students grade-point-average (GPA) as follows;
RP (recognition of prior learning) not included
FF grade points 0
WF grade points 0
WW not included
P grade points 4
C grade points 5
D grade points 6
HD grade points 7
- GPA is the sum of the grade points the student has received divided by the number of courses attempts excluding RPL and WW’s. Any student with a GPA under 2 (who has attempted at least 60 units becomes “at-risk”. An at-risk student may not enrol in more than 20 units of courses in each semester until such time as there GPA reaches 3.
- The system must be able to record RP (recognition of prior learning.
- An at-risk student who does not return to good-standing within two semesters will be excluded (taken out of the program.
- A student may apply for Leave of Absence for one or more semesters – but not if they are at-risk – unless they have approval from the Program Convenor. The program convenor is an Academic from the Faculty which controls the Program.
- A student becomes a Graduate after satisfying all of the requirements of the program.
Your design must be able to accurately and efficiently store all of the data related to the activities of NoTreal.
NOTE: the details provided above are certainly not ALL that you need to know and indeed they may not be accurate. It is your job to find what you need to know and to identify any problems on the data above. YOU NEED TO THINK AND ASK QUESTIONS