The screen print below shows my complete table design for the books table which will include the fields that I will require for the books table to meet the end users requirements as it will store information about all the book records which the end user specified in the performance criteria to be included into the system.
The third table that I created was the payments table which will hold and deal with fines. By creating this table it also means that it meets the end users requirements as my end user requirement stated that it would be ideal if the new system would store fine details and deal with fines automatically meaning calculating the fine automatically, deducting the amount owed when students pay their fines. By creating this table it resolves one of her problems that she was facing with the current manual system where she stores fines in her personal diary. (See source document analysis for evidence and illustration).
Once I finished setting out my tables with all the fields I required to meet the end users requirements I started to validate each and every field present in all the tables.
Validations for tblborrower
Forename and Surname
The forename and the surname contains a field size of 20 that ensures that all the data inputted into either of these fields should be less than 20 anything data inputted into this fields which will be more than 20 it will automatically be rejected.
Gender
The gender field in this borrowers table has been validated. Which means no other values apart from “F” or “M” will be accepted in this field. And if any other value has been inputted an error message will appear automatically appear to inform the end user to either select “M” or “F
Date of Birth
The Date of birth has been given an input mask which means that all the dates that will need to be entered are in the format shown on the input mask “00/00/0000”. There is also a validation set for the Date of Birth field which means that anybody between the age of 11 and 16 will be a working data that will be accepted. And if the wrong date of birth is inputted into the system an error message will appear informing the user to enter a valid date of birth that is accepted within the criteria set.
Year Group
The year group has been validated so that it only allows any year to be entered from year 7 up to year 11. If any data under year 7 or above 11 is inputted it will be rejected and an error message will appear automatically informing the user to enter a valid year group between year 7 and year 11
Town
County
For the town and county a default value has been entered because everybody who attends Beardwood School lives in Blackburn. Therefore every time a new record needs to be made for a student the end user will not need to repetitive type every single time Blackburn in the town field which means Blackburn will be automatically present every time a new record is made. This means that it saves on input time and also limits the chances of error being inputted in to the system
Post Code
the post code was also given a precise format to follow by having an input mask which means that any post code inputted should have a letter followed by a letter and then a number followed by space and then a number and letter and letter. This is the format set for the post code. Any input that does not match the criteria set will be automatically rejected.
Telephone Number
Input mask was used to validate the telephone number which means that this field will accept the format that has been set. The 01254 will automatically appear as that is the code for Blackburn. And once again it reduces the time for the end user to enter the code again and again. The format after the code is that for the input data to be accepted into this field it has to be 5 or 6 digits long. If the value inputted is more than 6 digits it will be rejected. In the input mask the digit 9 means that the 6digit can be entered otherwise 5 digits are also accepted by this field.
Number on Loan
since according to my end user she wanted no more books to be loaned by one student in a week, a validation rule was set to meet the end users requirements which means that no more than 2 books will be allowed to be taken on loan and if more than 2 books are taken the system will reject this as iit has been set to only allow 2 books and less.
Date of Last Loan
Validation for tblbooks
Title, Author
The title and the author have a field size of 30 which means that no more than 30 characters will be allowed to be entered otherwise if more than 30 characters are entered the field will not allow this therefore it will be rejected.
Genre
For the Genre rather than the end user typing in the genre every time, a lookup field was created so save time. This lookup would store a list of genres so that the user can just select them rather than type them in every time.
To create a lookup firstly from the data type field the lookup wizard was selected as shown on the screen print just above
I want the to type in the Genre types therefore as shown on the screen print above the second bullet point was selected
Then all the genre types were typed in as shown on the screen print above. This typed in data will be displayed in the books table when book details are been entered into the system
Finally the lookup wizard was named as Genre and the click finish button was clicked.
The screen print below shows how it finally looks
ISBN
For the ISBN an input mask was used which would give the input format for this field allowing 10 digits to be entered into this field anything less than 10 digits or more than 10 digits will not be accepted into this field as it will be rejected.
Price of Book
For the price of book the format was changed to currency and a validation rule was set for the price of the book to be more than £0.00 as no more than cost less than
Number of Rentals
Validations for Payments
PaymentID
BorrowerID
Payment amount
After all the validation were set up for all the tables. The next step was to create a relationship between the tables and this was done by clicking the relationship button on the main screen which looks like as shown on the screen print
From the relationship above it shows 1 to many relationship between Borrower and Payments because one borrower can make many payments whereas a payment can be only be made by a specific borrower.
After I created my relationships I then started to create data entry forms because entering data into the tables does not look professional and can be confusing. And also by creating data entry forms I am meeting one of the end users requirements that the “system should be user friendly and easy to use”
The first form that was created was the borrower form and a wizard was used to create the forms as it much quicker to create forms from wizards rather than designing and making your own.
This how the borrower’s data entry form looks like
