A614 Week 6 homework -- Preparing database tables

The following are the tables that are needed to create your database.

Table Name	Primary Key	Foreign Keys	Other Attributes
Customer	Customer Number		Customer Name Shipping Address Billing Address Credit Limit Balance
Sales	Invoice number	Customer number	Date of sale
Sales-Inventory	Item number Invoice number		Quantity sold Price (actual sales price)
Inventory	Item Number		Cost (standard or list) Description Quantity on Hand Price (standard or list)

1.Open up Access. (File, New, Click Blank Database,then name your file, then click create table in design view)

2.Enter each of the fields for Inventory, starting with the primary key item number.

3.For Item Number, type ItemNumber directly underneath Field Name, leave its data type set to text, and right click your mouse (your cursor should be to the left of ItemNumber) to set up this attribute as the primary key. For field properties:

Field size: 3

Caption: Item Number

Validation Rule: Like # # #

Validation Text: Invalid entry. Item Number must be three digits long.

For validation rule, be sure to write the word Like. For all other properties, use the Access defaults

4.ItemCost is the next field, data type is text, caption is Standard Cost

5.ItemDescription is next, data type is text, field size is 25, caption is Description.

6.QuantityonHand is the next field, data type is numeric, field size is 3, caption is QOH

7.StamdardPrice is next, data type is numeric, caption is Standard Price

8.Now enter data directly into your table. Directly underneath the File there is a table icon; click this and then click datasheet view.

9. You can now enter data. Enter one line of data (you must be in datasheet view, you can get to datasheet view by clicking the button underneath File and doubleclicking datasheet):

ItemNumber: 101

Item Cost: $10.00

ItemDescription: 1/8 sheet metal

Quantity on Hand: 250

Standard Price: $11.00

Save your table as Inventory

10.Next, create a new table for sales. Make sure you are in design view, click tables, double click create table in design view and enter each of the fields for Sales., starting with the primary key Invoice Number

.

11.For Invoice Number, type InvoiceNumber directly underneath Field Name, leave its data type set to text, and right click your mouse (your cursor should be to the left of InvoiceNumber) to set this attribute up as the primary key. For field properties enter the following:

Field size: 3

Caption: Invoice Number

Validation Rule: Like # # #

Validation Text: Invalid entry. Invoice Number must be three digits long.

For all other properties, use the Access defaults

12.CustomerNumber is the next field -- type this underneath InvoiceNumber; The data type is text. For field properties enter the following:

Field size: 3

Caption: Customer Number

Validation rule: Like # # #

Validation text: Invalid Entry. Customer Number must be three digits long.

13.DateofSale is the next field, type this underneath CustomerNumber, the data type is date. For field properties enter the following:

Format: Short date

Caption: Date of Sale

For all other properties use the Access defaults. Go to datasheet view and enter the following record:

Invoice Number: 101

Customer Number: 101

Date of sale: January 5, 2007

14.Now build a table for Sales-Inventory. Make sure you are in design view, click tables and double click create table in design view. Then enter each of the fields for Sales-Inventory. This table has a composite primary key (both ItemNumber and InvoiceNumber are primary keys.

15. Enter a field name of ItemNumber, leave its data type set to text. For field properties:

Field size: 3

Caption: Item Number

Validation Rule: Like # # #

Validation Text: Invalid entry. Item Number must be three digits long.

Indexed Property: Yes (Duplicates OK)

For all other properties, use the Access default

16. For Invoice Number, type InvoiceNumber directly underneath Field Name, leave its data type set to text. For field properties enter the following:

Field size: 3

Caption: Invoice Number

Validation Rule: Like # # #

Validation Text: Invalid entry. Invoice Number must be three digits long.

Indexed Property: Yes (Duplicates OK)

For all other properties, use the Access defaults

17.Now we can set up the composite primary key. While pressing the Ctrl key, click the row selectors for ItemNumber and InvoiceNumber. With both fields selected, right click the Primary key toolbar button. The primary key should appear in the row selectors of both fields.

18. For the field QuantitySold, type QuantitySold directly underneath Field name, set its data type to text. Enter the following field properties:

Field Size: 3

Caption: Quantity Sold

Validation Rule: Like # # #

Validation Text: Quantity sold cannot exceed four digits.

For all other properties, use the Access defaults

19.ActualPrice is next, data type is currency, caption is Actual Price. For all other properties, use the Access defaults Save your table as SalesInventory.

20. Next, enter a row of data:

ItemNumber:101

InvoiceNumber: 101

QuantitySold: 10

Actual Price: $12.00

21.Now, create a new table for your customers. Make sure you are in design view, click tables, double click create table in design view and enter each of the fields for Customer, starting with the primary key CustomerNumber.

22.. For CustomerNumber, type CustomerNumber directly underneath Field Name, leave its data type set to text, and right click your mouse (your cursor should be to the left of ItemNumber) to set up this attribute as the primary key. For field properties:

Field size: 3

Caption: Customer Number

Validation Rule: Like # # #

Validation Text: Invalid entry. Customer Number must be three digits long.

23. Enter CustomerName below CustomerNumber, the data type is text, field size

is 40, caption is Customer Name.

23.Enter ShippingAddress below CustomerName The data type is text, the field size is 40 and the caption is Shipping Address

24. Enter BillingAddress below ShippingAddress. The data type is text, the field size is 40 and the caption is Billing Address

25. Enter CustomerCity. The data type is text, the field size is 40 and the caption is City.

26. Enter CustomerZip. The data type is text, the field size is 10, and the caption is Zip Code.

27. Type CreditLimit in the next row. And set the data type to currency. Change the decimal places property to 0.

28. Tyoe AccountBalance in the next row. And set the data type to currency. Change the decimal places property to 0.

29.Save your table as customer.

30.Click to datasheet view and enter one line of data (use your own name, address etc.)

31..Link your tables. This will enforce referential integrity. To do this, close all your open tables. Choose Tools, Relationships. When the tables dialog box appears, select all three tables. Connect the primary keys (by double clicking them) to their respective foreign keys.

Please take screen shots of each table along with how to link the relationships between the tables. Thank You!!

32. Send your file to your gradebook.