Candidate Name: I limited the field length to 20 characters as this would be long enough to cater for any name but yet saves space as less space has to be set aside for each record.
Candidate Number: as candidate number is only 4 digits long there would be no way in which it would exceed the maximum length an integer would provide. Therefore I chose to decrease the field length to integer so as to save space.
Centre Number: I was not able to decrease the size of this field as Centre Number is a 5 digit number and therefore can exceed the maximum length of an integer of 32768.
Subject Reference Code: this field again used long integer as its data type due to it being a 5 digit code. To ensure that the field could start with a 0 I altered the properties of the field as below:
Original Grade: I decreased the length of this field to 1. The grade would always only be a 1 letter grade such as, A or B. Therefore I was able to decrease the length of this field to 1 at no consequence.
Re-mark Mark: I changed this to integer as it was only a 2 digit number.
Centre Address: I changed this to 200 characters as an address can be quite long. However I did not change the data type to memo which would have provided more space as it would make the system slower and the extra space would not have been needed
First Normal Form:
I realized that having the candidates and also the re-marks in the same table, that I would be unnecessarily duplicating data within the system. I therefore decided to split the entity up into two separate entities, candidate and re-mark. So my new entity list looked as below:
Candidate (Candidate Name, Candidate Number, Centre Number)
Re-mark (Subject Name, Subject Reference Code, Candidate Number, Centre Number, Original Mark, Original Grade, Re-mark mark, Re-mark Grade, Requested)
Subject (Subject Reference Code, A, B, C, D, E)
Centre (Centre Number, Centre Name, Centre Address)
The new entity remark has a composite primary key consisting of, subject reference code, candidate number, and centre number. This was the only combination of attributes within this entity which would ensure that it was always unique.
Also in this way I was able to ensure that all of my tables were in first normal form, satisfying the criteria of eliminating duplicative fields within the same table. By making the re-mark table a sub-form of candidate through the use of a one to many relationship between the two, I was able to have multiple re-marks for each candidate without having to break the first normal form which I need to adhere to.
Second Normal Form:
For a table to fulfill the requirements to be in second normal form, all of the non-key attributes within the entity must be functionally dependant upon the primary key of the table. All of my entities satisfied these criteria so therefore no changes needed to be made.
Third Normal Form:
For a table to fulfill the requirements to be in third normal form, it must first be in first and also second normal form. On top of the requirements for the first two forms, it must also be ensured that no non-key attributes are dependant upon other non-key attributes. My database already satisfies this requirement and can therefore be said to be in third normal form.
As my database fulfills all of the requirements to be in first, second and third normal form my database has now been normalized.
Relationships:
After designing the entities my table will use while considering the normalization of my tables I have now come up with the following relationships:
Above is a screenshot of my relationships that I have created in access. As can be noted there are no many to many relationships as I have eliminated them through the normalization process.
Software:
For this project I am going to be using Microsoft Access, as well as some coding within Visual Basic which I will implement into the database. I have chosen to use the following combination as it is an easy way to set up tables and the initial database, but also allows me to utilize the power of a programming language such as Visual Basic so I can fine tune and customize the user interface and other aspects of the database. I have chosen to use a package to create the initial database as opposed to programming the whole database as programming the whole database from the start is a larger task which I would not have been able to accomplish with my current understanding of programming. Also for this particular project the extra power that a programmed database could offer was not needed as the solution could be provided in a fairly simplistic form. My other main option for creating this database was to use Delphi, which is a form of Pascal. I have already covered most of the Pascal syntax and it would have been the best alternative choice, however I did not do this for the reasons above. On top of this I wanted to expand my knowledge of systems creation by experimenting with Visual Basic and also Access.
Hardware:
The hardware which I will be initially creating my solution on is my home PC. This PC was built by me and therefore I am very familiar with the hardware that I will be using. The hardware is of a medium to high specification:
- AMD Athlon XP 1800+ (clocked at 1533 MHz)
- 512 MB DDR PC2100 RAM
- 40 GB Hard drive
However I wanted to ensure that the software that I was creating would work on a PC of lower specification so that anyone that was using it would not be forced to upgrade their systems to use my database. I therefore did some testing on the PC’s at my College which were of a lower specification:
- Intel Pentium III 800 MHz
- 128 MB SD RAM
- 10 GB Hard drive
Design:
Tables:
Following are screen shots of the tables in my database:
Candidate Design View:
As can be seen in the properties of the candidate name attribute, I have changed the field size to 20. Also it can be seen that the primary key is a composite one made up of candidate number and centre number.
Candidate Table View:
Centre Design View:
In the above view, the focus is on the centre number attribute, as can be seen the field size is set to long integer, also for the format I have altered it to 00000. This represents the number of digits that I was to be shown at any time, this is a work around of the problem that if a centre number begins with a zero then it will still be kept. The primary key for this entity is Centre Number.
Centre Table View:
The test data included 3 centres. So as to show the full address of each centre I have had to increase the height of each record within the table.
Re-mark Design View:
As can be seen for this table the primary key is a composite one comprising of subject reference code, candidate number and centre number.
Re-mark Table View:
Here is all of the test data for my database, it may be noted that there are more than 15 re-marks (the corresponding number of candidates). This is because I have given some candidates a number of re-marks so as to be able to test the effects of more than one re-mark per candidate.
Subject Design View:
In the subject table I have given the primary key to the only unique attribute within the entity which is subject reference code.
Subject Table View:
As can be seen for each grade boundary I have just given the singular value as opposed to the actual bounds for each grade. This is so as to make coding for the user interface easier later on in the solution.
Validation Rules:
In the specification there were a few rules by which some of the data needed to adhere to, these were:
- Candidate Numbers are of 4 digits and will be unique within any centre, but not between centres. The solution should ensure that this is allowed for.
- Centre numbers are allocated within the range 10000 to 80000 and are unique.
- Subject Reference Codes are 5 numeric digits.
The following are the validation rules that I used to ensure that my data was correct.
- For the candidate numbers I used the following validation rule: (>0) And (<10000). This allowed for the numbers from 0001 up to 9999.
- For the centre numbers I used the following validation rule: (>9999) And (<80001). This allowed for the numbers 10000 to 80000.
- For the subject reference codes I used the following validation rule: (>0) And (<100000). This allowed for any 5 digit code.
Other validation codes that I used include:
- (>=0) And (<=100) : for the original mark and re-mark mark attributes. This ensured that every mark was expressed out of 100.
- (="A") Or (="B") Or (="C") Or (="D") Or (="E") Or (="U") : for original grade and re-mark grade, this ensure that each was a valid grade.
Queries:
The first query that I needed to create needed to show any outstanding re-marks. Outstanding was defined as 3 weeks after it was received. This led to me needing to change the design of the re-mark table slightly. I needed to add an attribute (date received) so as to keep record of the date that it was received. After this the re-mark entity looked as follows:
Re-mark (Subject Name, Subject Reference Code, Candidate Number, Centre Number, Original Mark, Original Grade, Re-mark mark, Re-mark Grade, Requested, Date Received)
Forms:
Main:
To make the design as user friendly as possible, I have split it into 2 sections of input and output. This way it is easy for the user to distinguish between the two uses of the system. For all of my forms I have included an exit button in the bottom right of each form. I have kept this constant to stay in line with the “element of least surprise” design.
New Centre:
Similarly to the new centre button, the new subject button opens at a new record. The screenshot is of the subject form.
New Re-marks:
The new re-mark button unlike the first two buttons does not open at a new record. This is because it is more likely that the user will be inputting a user from an existing centre and therefore it would make more sense for the user to scroll through the centres. A small feature that I have included in this form is to improve the user interface of the system. It will input a code from a subject name or a subject name from a code automatically. I used the following code to achieve this effect.
This code changes a subject name into code, it can be noted that this happens on exit of the subject_name field. For the opposite effect I used very similar code:
Above is a small clip of how this code works, it follows on similarly for each code. Again this code takes place on exit of the subject_reference_code field.
Re-mark Results:
For this particular form I have locked many of the fields, this is because it will be used to input the re-mark results from previously submitted papers. The locked fields are coloured in grey. Another feature which is used in this table is an auto-grading system, where the system calculates the grade from the mark entered. This feature also takes into consideration the different subjects as each has different grade boundaries. The code is as follows:
End Sub
Where the grade boundaries are actually on the table but are hidden as shown:
Each of these fields contains the grade boundaries from the subject table. They change in accordance to the subject reference code which is also on the table. This feature is put in place to increase the efficiency of the system for the end user. It may also be noted that when the re_mark_mark field is exited the re_mark_date field becomes equal to todays date. The field was like the grade boundary fields hidden on the form.
Reports:
The following are the reports which I included to provide hard copies for the end user. Beneath I have listed the names of each of these reports and have also printed out examples of each report. Each example is clipped onto the end of this report; the page number will be included next to the report name beneath:
- Grade Changes (pg )
- Outstanding Re-marks (pg )
- Subject Report (pg )
- Centre Report (pg )
- Candidate Report (pg )
Each of these reports simply used the queries that I had already constructed. However the candidate report posed a small problem, the specification stated that it would like to show the grade change or if no grade change had been made, then it would like a sentence stating that there was no change. This meant that I would need to create two separate reports and then create some code which could go to the necessary report when I had inputted the details for the report.
