Database challenges

From Wikiversity
Jump to navigation Jump to search

The following database challenges are designed to flex your database skills, giving you a chance to apply what you learn. The idea is simple: use your favourite database application (Microsoft Access, or OpenOffice Base) to design and create the database described in each challenge. As each challenge will not necessarily give you step-by-step instructions, it will be useful to work together with others and discuss your solutions together.

Note: You can right-click on any image and open it up in a separate window to see a larger version. If you then want to print the image you can then click on the "Download high resolution version" link on that page.

Challenge 1: Recording your customer payments[edit]

A Customer table with a related Payments table (Note: this image was created with OpenOffice Base,so it may look slightly different in Access)

Your friend owns a small mail-order business and, up until this point, has used a simple spreadsheet to record her customer details. She's asked you if you could create a small database for her to allow her to:

  1. Enter customer details
  2. For each customer, add payment details when necessary.

Laura doesn't need you to create fancy forms or anything else (yet), but would just like the two related tables as shown in the diagram, with the following data. Note - it will be important to look at the data before you design your tables, as it will help you decide what type of fields to use.

Customers[edit]

CustomerID Firstname LastName Address City PostalCode EmailAddress MobileNumber
1 Elviss Presley 35 Rock Rd California 23457 elvis@presley.com 0474 234 234
2 John Lennon 19 Abbey Lane London ZN2454 john@beatles.com +0576 234 535

Payments[edit]

PaymentID CustomerID CreditCardNumber CreditCardExpDate PaymentAmount PaymentDate
1 1 5465 3455 2345 2345 1109 $2,000 2007-01-21
2 1 5465 3455 2345 2345 1109 $1,000 2007-01-23
3 2 1121 3213 7654 6565 0708 $11,000 2007-01-29
1 1 5465 3455 2345 2345 1109 $999 2007-02-04

Notes:

  • Make sure you understand from the data above which payment corresponds to which customer.
  • Experiment with the different types of fields for your data, and debate your choice with the people around you!
  • For help creating tables and relationships, check out the Roadmap to Access 2003 Training from Microsoft