Physical Design and Implementation
Our output from Assignment 1 was a Conceptual Model describing the NoTreal student system requirements. Based on that model we will now complete and implement a physical design for a database to implement this model. The design and implementation will be carried out in 2 stages:
Stage 1 (worth 40% of the marks for assignment 2):
Will involve design and implementation of data structures (tables and indexes) capable of recording all necessary information relating to Programs, Courses, Students, Academic Staff and Departments. This stage will be primarily based on the ER diagram produced for assignment 1. So that we are all working on a similar problem I suggest that you use the partial ER I have provided - with all attributes and the extra entities for Departments and Academics added, and any other modifications you see as worthwhile. Note that this ER is NOT complete and implies at least one significantly different business rule to those shown for Assignment 1 – this is that, while a student may be completing several programs concurrently, they may only enrol in courses for a single program in each teaching period. This simplifies the stage 2 tasks.
Of course if you would prefer to use your ER model that is fine, just remember you will need to implement the operations described in Stage 2 within whatever structure you create. You are to create all necessary tables, including fields (attributes), indexes, keys and foreign keys and check constraints where appropriate. The tables must be normalised to 3NF or BCNF where this is appropriate and where it is not you should document this and explain why you chose not to do so.
For stage 1 you need not not implement the entities shown in green on my example ER diagram – after all unless we are able to populate these tables automatically they are actually of little use. However I would still suggest that you think about creating them now, because in stage 2 you will be creating stored procedures to create these tables so you will need them at that time.
You should also create a first set of “test data” and use it to populate theses tables, we will need this for testing later. This test data should be sufficiently detailed to allow full testing of the database. If you have created the “green” entities populate them with the correct data manually.
Stage 2 (worth 60% of the marks for assignment 2):
The tables you have created in Stage 1 should already allow NoTreal to enter all of the data needed to manage their student teaching (even if you did not implement the “green” tables). They will even enforce some of the business rules that you have identified, for example I would expect that a student would not be able to be enrolled in a course unless they are enrolled in a program - and similarly I would expect that a student could not be deleted from a program if they have enrolment records for courses within that program.
BUT there are other business rules that we identified which tables and referential integrity constraints cannot monitor/enforce – examples would be ensuring that a student enrols only in courses for which they have completed all assumed knowledge courses. So while the system in its present form allows all valid data to be stored it also allows INVALID data to be entered. In this stage we will address some of these issues. We will NOT be trying to implement solutions for every rule since that would just involve too much work, so instead we will select enough to demonstrate most of the techniques needed. Here is a list of things to create:
- A stored procedure to maintain assumed knowledge requirements. The procedure should be able to delete existing rows from the table and add new rows. The procedure should take three arguments, the operation type (insert or delete), the crs_id of the course and the crs_id of its assumed knowledge course.
For insertions the procedure should check that the row does not already exist. If there are no problems the stored procedure will insert the row and return a value of 1, otherwise it returns -99
When a deletion is requested the procedure should check to make sure that the row to be deleted does exist. If the row exists the procedure deletes it and returns a value of 1, otherwise it returns -98
- A stored procedure to populate and maintain the Program Approved Courses and Program Available Courses tables. This procedure should be able to accept input parameters for a single program, or optionally a parameter specifying that all programs are to be updated. Since this procedure takes all of it’s inputs from pre-existing data there should be no errors. AND when updating the tables the stored procedure should first delete all of the pre-existing rows and then re-insert them.
As in the first stored procedure this one should accept an argument of either a prog_id or the words “all programs” (and either update a single program or all programs).
- A stored procedure to populate and maintain the Program Approved Courses and Program Available Courses tables. This procedure should be able to accept input parameters for a single program, or optionally a parameter specifying that all programs are to be updated. Since this procedure takes all of it’s inputs from pre-existing data there should be no errors. AND when updating the tables the stored procedure should first delete all of the pre-existing rows and then re-insert them.
As in the first stored procedure this one should accept an argument of either a prog_id or the words “all programs” (and either update a single program or all programs).
- A stored procedure to add and delete records to/from the Student _Enrolment table. Arguments are to be st_id, crs_id, period_id. Only enrolments which do match records in the table Student_Program_Available_Courses are to be allowed. Students with outstanding fines are not allowed to enrol in anything. Students may enrol in no more than 4 courses each teaching period and At RISK students must have their restricted load enforced.
You are to design the arguments and return processes/values. The procedure should only allow operations relating to “current” teaching periods (this is a new concept, we haven’t talked about it before).
- A stored procedure to “review” a student’s academic record and provide a report showing whether they have satisfied the requirements for the program and if not what they still need to complete in order to qualify. (This is much harder).
The Assessment Process
As for assignment 1 rather than having a single due-date there will be a “submission period”.
For Assignment 2 the submission period will commence on Sunday 10th July and finish twoweeks after that date. Submissions can be made at any time throughout that period.
You may make two submissions. When you submit the first time I will provide you feedback (within 5 days) 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 resubmit 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.
This process is designed to allow student flexibility in organising their work, the opportunity to receive feedback and improve their work based on that 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. Finally note that the later you make your first submission the less time you will have to work on the changes for the second submission.
