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

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:

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

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

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