I may also need to use the following output options:
- Screen – To view the database
- Printer – To print of reports (and maybe hand to students)
Data collection and input method
Before I can begin my database I must have a list of pupils and a list of books, this information will either be kept on paper or on notepad.
Data flow
Processes
* User name is made up like this:
Last two digits of year student joined the school
(1999=99), form they are in, first letter of their first name (John=J), first four letters of their second name (Harvey=Harv)
So mine would be: 99LJHARV
Fields required in student table
Username, first name, surname
Fields required in book table
Book id, book title, author, category
Fields required in loan table
Book id, username, date out, date in, returned
Reports needed
Individual student’s loan history, all late books, individual student’s late books
Database security
To keep my database secure I shall allow access only to the database by entering a correct password.
Relationships in my database
Main objectives for my database
The three main things I want my database to do:
- Show all late books
- Show history of student’s individual loans
- Show individual student’s late books
Backup strategy
The computer that has the final library system on it will be backed up either on a floppy disk or a CD-rom; this copy should be kept in a different location from the computer for safety.
Security
Upon entering the database, the user will be asked for a password, this password will be provided in the user guide.
Design
Now that I have decided to make a database, I will make two rough sketches of two different possible designs for my database. I will do these by hand, then I will decide the design I prefer and use it on my database.
Comments on designs
Ms Matthews thought my first design was very dark and not very user friendly, this lead me to chose design two as my final design as it was brighter and more user friendly. (Refer to the appendix at the end of the coursework to see these two designs)
Menu diagram
Form design diagrams
Report design diagrams
Menu structure diagram
Table design
There are four tables in this system, they are: category, tblbook, tblloans and tblstudent.
Note: category is a lookup table.
Here is how they are related:
The tables are defined like this:
tblbook
tblloans
tblstudent
category
Queries in my database
I am going to have three queries in my database; these will form the sources for my reports.
QryLate
QryStudentLate
QryStudentLoanHistory
Subtasks in my database
My database’s final design needs to be split into more manageable sub-tasks.
- Create my category table first then my three other tables, link tblbook to category with category as a lookup table, also define values, validations and input masks.
- Create the relationships between my tables
- Create the three forms and modify designs
- Create sub-menu for forms and modify design
- Create the three queries
- Create three reports from these queries and modify the report’s designs
- Create sub-menu for the reports and modify design
- Create the main menu linking to the two sub-menus and modify design
- Set password for library database system
Test plan
A test plan needs to be made in order to make sure that my library system is working properly and is doing what it is supposed to do. Valid, invalid and extreme data will has been used in my tests.
Implement
Finished design
Proof that the finished design has been implemented can be seen in the test results section.
Test results
Now that the book library system has been created, the test plan can be created up in the design section can be implemented. The test plan should be referred to when looking at the results below.
Test 1: Check if the password function works correctly.
Enter incorrect password:
Test 2: To check if close button on main menu works correctly.
Click on ‘exit’ on main menu:
Test 3: To check if links work on main menu.
Click on both links:
Test 4: To check if long names can be entered into frmstudent.
Enter Christina Macintyre-Simpson
Test 5: Test input mask validation on frmstudent.
Enter FF768987 as username:
Test 6: Test that frmbook will accept a new record.
Test 7: To see if frmloans will accept a book that isn’t in the database.
Enter non-existent book ID:
Test 8: To see rptlatebooks shows all late books.
Enter a late book into frmloans and check rptlatebooks:
Test 9: To see rptstudentloanhistory shows all late books of an individual student when username is entered.
Enter a loan for 99LJKHAW:
Test 10: To check if rptstudentlatebook shows an individual student’s late books.
Late books for 99LJKHAW:
Not late/returned books for 99LJKHAW:
As you see here, after entering 99LJKHAW’s username in rptstudentlatebook, only her two late books appear:
Test 11: To see if an invalid date in the ‘date in’ on frmloans can be an invalid date from 200 years ago and the return date 200 years before that:
Discussion of test results
All the tests gave the expected results except for test two and test eleven, here are my comments on where those tests went wrong;
Test two - the exit button did not exit the menu but opened the view reports menu. This happened because I had made a mistake when adding the button to the menu. I corrected this problem once I realised the button was wrong, after I corrected it I tested it again and it worked.
Test eleven – the frmloans form accepted a date from 200 years ago and a return date. To correct this problem I went to the design view of tblloans and added the following validations and default value for date in and date out:
Date in
New validation rule: =Date()
Default value: =Date()
This validation rule will now only allow the date of entry to be entered, which will automatically be entered by the default value.
Date out
New validation rule: <=DateAdd("d",14,Date())
Default Value: =DateAdd("d",14,Date())
This validation rule will only allow a date between the first fourteen days of the date in date; the default value automatically enters the maximum days that the book can be borrowed for.
Evaluate
Evaluation of initial objectives
When the database is opened, a password is requested. If the password is entered correctly the database then loads, displaying a list of options the user has to click on forms on the objects menu on the left, then on the right, there will be a list of options, they should click on ‘Main Menu’ to access the database. In my opinion I feel that it would be better for the library system’s main menu to appear automatically after entering the password.
After clicking on main menu, the system displays the menu, this is a very simple menu and has two main options: Add/Edit Data and View Reports, there is also an exit button.
It is easy for Ms Matthews to enter a new book, new student or a loan into the database with the help of basic input forms. There is also an option when entering new books to give it a category.
There are three reports; these show an individual student’s late books, an individual student’s loan history, and all of the books that are late in the system. All of my main objectives have been fully satisfied.
In my opinion, my database fulfilled it’s potential but on a very basic level. It was very useful and easy to use for someone like ms Matthews as she has very limited computer knowledge, but for a computer technician this database would be very easy and if someone with much more extensive computer knowledge needed this database then I would have made it more detailed and added much more features.
Overall, I think my database was pretty good for a basic library system.
User feedback on solution
Ms Matthews was given the system to try out for a day, after using it she gave me the following comments on the library system I had created:
“The library system is easy to use and fulfils all my needs, however, I did have trouble finding the main menu once I had entered the password on the database.”
“Sometimes when a book is not in the library, I need to see which student has the book to see if it has gone missing, it would be useful for me to be able to enter a book to see who is borrowing it instead of browsing through the records, I would like to see this in the form of a report.”
Ideas for further improvement
The system could be developed further into a system with more reports, for example: design a report searching for a book based on category, a report searching for students of a particular year, a report searching for students of a particular form group.
It could also be improved to open the main menu automatically after entering the password.
If I were to recreate this database from scratch I would make sure there were relevant validation rules for every field in all tables if needed, I would also create more reports as mentioned above, and make the main menu appear automatically after entering the password.
