VC 4
Building Tables in Access to Record Business Transactions

(Transaction Processing Systems)

Note: VC4 is UNGRADED, and you are not required to turn anything in or build the Access database. However, you are encouraged to read the case and even build the Access database to help prepare you for quizzes and VC6.

Background

A snowboard company, EDS snowboards, has requested that your group help them develop a database application to keep track of their business transactions (orders of snowboards). They have been tracking snowboard orders with paper and Excel spreadsheets and have found that they are not able to effectively use their transaction information to manage their business.

Assignment

After viewing the catalog of snowboards on the EDS Web site, a customer can place an order for a custom-made snowboard using an online form. After the customer completes the form and clicks the submit button, the information is then electronically transmitted to the EDS server where it will reside in a database management system. However, an order received over the WWW is only one way EDS can receive an order for a snowboard. Customers may also purchase a snowboard by phone or through sales representatives at trade shows and other exhibition events. In the case of all three types of orders, your Access database will be used to store and query the information.

Using a database management system (DBMS) (Microsoft Access), you are to build a prototype transaction processing system to support the processing of customer orders.

• Create Tables - Using a DBMS, create a product table, a customer table, and an order table according to the following specifications:

Product Table

Attribute Name	Data Type	Description
prodid	Text	Primary key – uniquely identifies each product
prod	Text	Produce description / name
cost	Currency	Cost of manufacturing product
onhand	Number	Number of units on hand
sellpr	Currency	Current price of product

Customer Table

Attribute Name	Data Type	Description
custid	Text	Primary key – uniquely identifies each customer; obtain from Customer ID Database
custlname	Text	Customer’s last name
custfname	Text	Customer’s first name
maddr	Text	Mailing address
city	Text	City
stateprov	Text	State or province (2-character abbreviation)
zippc	Text	Zip or postal code (9-character version)
country	Text	Country name (2-character abbreviation)
wphone	Text	Work phone (area code + number)
hphone	Text	Home phone (area code + number)
em	Text	Customer’s email address
bal	Currency	Amount owed by customer

Order Table

Attribute Name	Data Type	Description
ordnumb	Number	Primary key – uniquely identifies each order
custid	Text	Foreign key – custid is the primary key in customer table; links order table with the customer table.
prodid	Text	Foreign key – prodid is the primary key in product table; links order table with the product table.
saleamt	Currency	Price of snowboard purchased by customer; includes tax and shipping (only one board per transaction)
ordate	Date	Date the order was placed
ordtype	Text	Type of sale: Internet, Phone, Sales Rep
pay	Text	Type of payment: Check, Credit or Debit card (Card), COD
ctype	Text	Customer’s credit or debit card: Misery Card, Vista, Uncover, American Indebtedness
cnumb	Text	Card number
expdate	Text	Card expiration date

• Enter Data Into Tables - After you create the three tables using a DBMS package, you will need to enter data into these tables. The IS Department captured data so far for you, and you can get it here.

• Create Relationships Between Tables – You can do this in Access under “Tools” / “Relationships”.

• Create Online Forms - Since other EDS employees will be entering data into the tables, you need to create input forms for entering and editing the data in the tables. Since non-technical employees will be entering data, you need to make these three data input forms as intuitive as possible.