PHS badge

PHS CyberSchool

Computer Studies SG 

Modified 08 June 2004

School Supporter's Database - Basic table 

The Supporter's Club at PHS raises money by selling merchandise imprinted with the school logo to past learners.  The Supporter's Club purchases products from suppliers that deal in school speciality items.  The database consists of two tables.  The Item table contains information on items available for sale.  The Suppliers table contains information on the suppliers.

  1. Create a new database in which to store all the objects relating to the merchandising data.  Call the database School Stock YourSurname.
  2. Create the Item table using the structure shown below.  
    1. Use the name Item YourInitials for the table. 
    2. Add the data shown below to the Item table. 
    3. Print the Item table on one page.

Structure of the Item table

Field name Data type Field size Primary key Description
Item Id Text 4 Yes Item Id number (Primary key)
Description Text 25   Description of items
On Hand Number Long integer   Number of units on hand
Cost Currency     Cost of item
Selling Price Currency     Selling price of item
Supplier Code Text 2   Code of item supplier

Data for Item table

Item Id Description On Hand Cost Selling Price Supplier Code
BA02 Cricket hat 15 R12.50 R15.00 AG
CM12 Coffee mug 20 R3.75 R5.00 EP
DM05 Doormat 6 R14.25 R17.00 YourInitial
OR01 Ornament 25 R2.75 R4.00 EP
PL05 Pillow 8 R13.50 R15.00 YourInitial
PN21 Pennant 22 R5.65 R7.00 YourInitial
PP20 Pen and pencil set 12 R16.00 R20.00 EP
SC11 Scarf 12 R8.40 R12.00 AG
TT12 Tie 10 R8.90 R12.00 AG
WA34 Wastebasket 3 R14.00 R15.00 EP
  1. Create the Supplier table using the structure below.  Use the name Supplier YourInitials for the table. 
    1. Ensure that all Telephone Numbers are entered with 999 999-9999 spacing.
    2. Add the data shown below to the Supplier table. 
    3. Print the Supplier table on ONE page.

Structure of the Supplier table

Field name Data type Field size Primary key Additional formatting Description
Supplier Code Text 2 Yes   Supplier code (Primary key)
Name Text 30     Name of supplier
Address1 Text 20     Street address1
Address2 Text 20     Street address2
City Text 20     City
Province Text 2     Province (Two-character abbreviation)
Postal Code Text 4     Postal code
Telephone Number Text 20   000 000-0000  as Input mask Telephone number 

Data for Supplier table

Supplier Code Name Address1 Address2 City Province Postal Code Telephone Number
AG Ahead Gear Berkley Square Melch Street Ndabeni WP 7405 021 511-6611
EP Executive Promotional Gifts 32 Milner Road Metro Industria Paarden Eiland WP 7405 021 511-0959
YourInitials YourSurname Agencies Your address1 Your address2 Your town Your province Your postal code 021 Your-phone number 
  1. Create a form for the Item table illustrated below.  Use the name Item Form YourInitials for the form.  (Use the tip sheet for assistance.)
    1. Make the labels bold.
    2. Size each label box to a suitable size (fit).
    3. Make the label boxes a school colour.
    4. Add a text tip to Cost and Selling Price.  
    5. Add the school logo to the form.  
    6. Give the form a background colour.
    7. Print the form of JUST the 2nd record. 

    Form of the Item table

     

  2. Create a report called Inventory Report YourInitials for the Item table.  (Use the tip sheet for assistance.)
    1. The report must have the data on the Item Id, Description, On Hand and Cost.  
    2. Add the school logo to the report in the report header area.
    3. Print the report on ONE page.
  3. Save the database for future tasks.
  4. Consult the Access Help pages on Datatype and Fieldsize Property.
  5. Know and understand the concepts of Redundancy and Primary key  in database design.

(based on Access 2000 by Shelly, G.B., Cashman, T.J. and Pratt, P.J. published by Course Technology in 2000 www.scsite.com )

Return to Computer Studies lesson plan index. This work may be freely copied for non-commercial use only. Copyright © Pam Miller.