Database challenges
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
editYour 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:
- Enter customer details
- 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
editCustomerID | 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
editPaymentID | 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