Database task

GNVQ (Intermediate) Full Award - Unit 2

Handling Information

Part B - Database task

Please read the information below carefully. You must cover all the requirements for Unit 2 (databases) in your work based on the situation described.

To download a copy of this page as a Word document click here and save the file in your Unit 2 folder.

The Dvd Hire Shop

Julpar and Amina Patel own and run a corner shop in your local town. Recently Julpar bought 10 dvds so that they could be rented to their regular customers. A notice was put up inside the shop so that customers could see that they had the dvds for hire. In addition, for the first two weeks all the regulars were told about the dvds available - and they paid a hire fee of £2 per dvd per night to borrow them. This was OK as Julpar and Amina could remember who had borrowed each dvd. However, they decided that they must have a better system so that they would remember who had hired each dvd.

Julpar decided that only regular customers would be allowed to hire dvds. He decided to create a book with each film on a single page. The name of the person hiring the film, the date, and how much they had paid would be written when someone hired a dvd. One of the pages is shown here on the right.

At the start of the third week a new customer came into the shop and asked if he could borrow a dvd called “The Matrix”. This was the most popular title available – but Julpar agreed to loan it for one night at a cost of £2. The dvd was not returned the next day – and one of Julpar’s regular customers was upset that it was not available when he called to collect it. The new customer did not return the dvd.

This system continued to work quite well for a few weeks – until the day when Julpar decided to add 20 new dvd titles to the collection – to make the total number of dvds 30. This meant a big increase in the amount of data that must be stored and used.

Recently you met with Julpar and Amina to discuss the dvd hire system that they are using. You have found out that they have a computer in the shop that they use for writing letters and keeping their accounts using a spreadsheet program. You have also found out that they have a relational database system installed on the computer called “Microsoft Access”.

1. Describe how using a relational database could help Jalpur and Amina with their dvd hire system. Give details of the data that would need to be collected, stored and processed.

2. Plan (first on paper) a relational database with three tables (dvds, members and loans). Your plan must state the field names, field lengths, data types, primary keys and clearly show the relationships between the tables.

3. Create the database system using suitable software. Make the system as “user friendly” as possible by creating suitable data entry forms that make the layout of each screen as attractive and easy to use as possible. Create a data entry form that includes the loan data – plus details of the dvd and members for the loan displayed.

4. Check your database system is working correctly by entering data. You must enter at least 30 dvd titles (and these must be REAL dvds - not made up titles!!!!). In addition you must enter at least 30 “customer records”.

5. Run your database system over a period of (an imaginary) week. Loan dvd(s) to members; make sure some dvds are loaned several times during the week. Also make sure some customers hire several dvds whilst others do not loan any. Create at least 30 loans.

6. Carry out some queries (using the query facility in Access) listing information that may be useful for Jalpur and Amina. In each case get a screenshot of the query table (showing the structure of the query) AND a printout of the results shown after running the query. If you find the query doesn’t work (check carefully to see if you have done it correctly) then simply modify the query and try again until you get it to work correctly. Only print out the correct final versions! For example:

a. a list of all the dvds hired to one particular customer (simple search)

b. a list of customers who have hired dvds of one particular type (genre) – eg. science fiction or horror - sorted by more than one field (eg. member’s surname then first name) (sort on more than one field)

c. a list showing all dvds with a 15 certificate that are also science fiction or horror (complex search)

d. print screenshots of your data entry screens that you use to enter data and to process loans etc.

e. print out a screenshot of your relationships table and make sure you explain it (annotate it) correctly.

7. Create a report (using the Report facility in Access) showing all the dvds – with a list for each dvd of the members who have borrowed that dvd.

8. Make sure that your printouts show the data in a suitable layout – eg do not let some fields print on different pages of your printouts, as this will look less than professional.

9. Write on every one of your printouts (annotate) exactly what you have done and how you have obtained the results you have printed.

10. Create a short “user guide” booklet so that when Jalpur’s sister comes to help in the shop, she will know how to use it. Keep the guide very simple. Give clear instructions on how a new user should:

a. Add a new dvd to the dvds table

b. Add a new member to the members table

c. Enter the data correctly to loan a dvd to a particular member

11. Try entering some data in the wrong field – for example a dvd title in a date field – and see (and then explain) what happens. Make sure you use the term “validation” correctly in your explanation.

12. Evaluate your work on this database system. Include details of the following:

a. What went well and what went badly

b. How and why you decided to design the system as you did.

c. How you could improve the system further if you were to do the task again

13. FINALLY, organise your work – add a table of contents at the start with numbered pages so that it is possible to find any part quickly and efficiently. Make sure you show clearly by the way you have organised your work that you have gone from the design stage to completion and finally evaluation.

Additional notes:

You may be able to complete this work to an acceptable standard in about 15 hours. However, to show your skills and understanding of how databases work you will need to spend much longer than this – designing suitable data entry screens, writing the user guide, annotating your printouts, writing a detailed evaluation etc.

Please email your comments to mail@ictgnvq.org.uk Created by K C Parry - Please email your comments to kcparry@ictgnvq.org.uk