CSCI100 - Introduction to Software Packages and Programming

Computer Assignment #2: Company Database Guide

Customer Table: Data Dictionary

-------------------------------

Field Name Data Type

CustomerID Autonumber

CustomerName Text (Properties=20)

PhoneNo Text (Properties=13)

City Text (Properties=15)

Product Table: Data Dictionary

------------------------------

Field Name Data Type

ProductID Autonumber

Product Name Text (Properties=15)

Price Currency (Properties=Standard)

ExpiryDate Date/Time (Properites=Medium Date)

Transaction Table: Data Dictionary

----------------------------------

Field Name Data Type

TransactionID Autonumber

CustomerID Lookup Wizard

ProductID Lookup Wizard

Quantity Number (Properties=Integer)

TransactionDate Date/Time (Properties=Medium Date)

Section I: Tables

Task 1: Open MS Access

From the Start menu choose All Programs, MS Office, MS Access 2007.

Task 2: Blank Database

Under the heading New Blank Database click on Blank Database.

Task 3: Choose Folder and File Name

On the right side of the screen you can choose which folder that you want to save the database in. You can also choose the File Name of the database. Here the filename is Company.accdb. The file extension for MS Access 2007 database files is .accdb. Then click Create.

Task 4: Create the Customer Table

When you first create a table in MS Access 2007 the table will be in Datasheet View. We want to create the table in Design View. Click on the down arrow under View on the left side of the screen and choose Design View.

The Save As dialog box will pop up and you can type the name of the table which is the Customer table. Click OK.

Enter the field names and data types for each of the Customer table fields as shown below. Notice that CustomerID has a key to the left of it. We do not have to enter the key manually because we created the table using Design View.

For each field data type you may need to change the property. Check with the tables listed at the beginning of this guide to determine the field properties. In the example below the Field Properties for CustomerName was Field Size=20.

You will need to make sure the table is saved by either clicking on the save icon or using the shortcut key Ctrl+S.

Finally, you can close the table by click on the x on the left side of the table. It is a good idea to keep the table closed because some tasks cannot be completed with the tables open.

Task 5: Create the Product Table

Select the Create tab and click on Table.

Follow the same steps used to create the Customer table. Click on the down arrow under View and select Design View.

For the Table Name type Product and click OK to save the table.

Enter the field names and data types for each of the Customer table fields as shown. Refer to the Product table listed at the beginning of this guide. Note that the properties for the Currency data type is Standard and the properties for the ExpiryDate is Medium Date. Save the table.

Task 6: Create the Transaction Table

Create the transaction table using the same method as the previous table. In order to create a realationship between the three tables you will need to use CustomerID and ProductID as foreign keys. Remember that CustomerID is a primary key of the customer table and ProductID is a primary key of the product table. The primary key for the transaction table is TransactionID. When you enter the first foreign key, CustomerID, you must select Lookup Wizard as the Data Type.

The following screen will pop up. You don’t need to change anything just press Next.

Next you must choose the corresponding table. Since we are using the Lookup Wizard with the CustomerID, you should choose the customer table which is already selected. Click Next.

Next you need to select which field to use. You should choose CustomerName only as the selected field. This will allow you to view the actual customer names in the transaction table. Click Next.

You can skip this screen. Click Next.

There are no further steps required here. Clcik Next.

The last screen asks you to name the column. You can change the name if you like or just leave it as it is. Click Finish.

Now you need to save the table to establish the relationship.

Repeat the same steps for the ProductID as a foreign key. Enter the Field Name as ProductID. Choose Lookup Wizard as the Data Type

The window below will pop up and you can click Next.

Choose the Product table and click Next

Select the ProductName field and move it into the Selected Fields box.

Finally, you can click Next and then Finish.

You will need to add the final two Field Names which are Quantity and Transaction Date. The Data Type for Quantity is Number and you can use the Integer Property. The Data Type for TransactionDate is Date/Time and you can choose the Medium Date Property. Then save and close the table.

Task 7 - Relationships

Now we need to look at the Relationships between the three tables. So click on the Database Tools tab and select Relationships.

If you see the following Relationships then you have created the tables correctly.

We discussed Integrity Constraints in the textbook and now we will apply them to the relationships. You will need to either double-click on the relationship line between the tables or right-click and select Edit Relationships. Then you will need to click on the box beside Enforce Referential Integrity.

The result will be that there is now a 1 and an infinity symbol on the relationship line between the Customer table and the Transaction table. This will ensure the integrity or quality of the data. Specifically it will ensure that each CustomerID in the Transaction table will have a matching CustomerID in the Customer table. Now repeat the same process for the relationship between the Transaction table and the Product table.

When you have completed the integrity constraints the relationships should look as below.

Note about records

You will need to enter the records for each table using Forms (Views). The Customer table will need five records. The Products table will also need five records. Finally, the Transaction table will need fifteen records. Below is a sample of the data required.

Note: You cannot change the number for the CustomerID in the Customer table as it is chosen by the database. The same goes for the ProductID in the Product table and the TransactionID in the Transaction table.

Customer Table

CustomerName must be the name of a company that is your customer. You can choose your own customer names. For example, Costco, Walmart, and Superstore.

PhoneNo does not have to be the real phone number of the company.

City should include at least two customers from Vancouver, two from Burnaby, and one from Coquitlam.

Product Table

ProductName must be products from the same type. If you choose be a company that sells food then all your products must be food. For example, Siu Mai, Hah Gow, and Pai Gwat.

Price should be carefully thought out to match the type of product you are selling.

ExpiryDate is the suggested fourth field but it may not be suitable for your products. You can easily change the field by right-clicking on the table and choosing Design View. Then simply change ExpiryDate to something else and be sure to update the Data Type to match.

Transaction Table

The CustomerID and the ProductID in the Transaction table have a drop-down arrow that allows you to choose from a list of CustomerNames or ProductNames. This is because we used the Lookup Wizard. Be sure to have the customer transactions in random order.

Quantity is a suitable number of products purchased for each record.

TransactionDate must be in chronological order. For example, if the first record has a date of 3 June 14, then the next record might also be 3 Jun 14. The following records might be 4 Jun 14 and so on.

Section II – Forms

Forms are more practical for entering records because you can view only one record at a time. Forms also allow you to place more fields on the screen.

Task 1 – Create a form for the Customer table

Select the Customer table and choose the Create tab. Then click on Form.

When the Form first appears it will initially be in Layout View. You cannot enter records right away. If you choose to you may select a coloured of the Autoformat Wizard.

Once you have chosen from the Autoformat Wizard then you can save the form. You can just accept the form name given and press OK. Then you can close the form and reopen it and start to enter records. Or you can select Form View from the View drop down menu.

Now you are ready to enter the five Customer records. You only need to enter records for the CustomerName field, the PhoneNo field, and the City field. You cannot enter a number in the CustomerID field. The number in CustomerID field is decided by the database. Press the Enter key to jump to the next field and to the next record. You can also use the Page Down and Page Up keys to jump between records. I am not yet sure why the fields from the transaction table appear below. Maybe it is there to show that the Customer table is in a relationship with the Transaction table. DO NOT enter the ProductID, Quantity, or TransactionDate. Close the Customer form by clicking on the black x to the right.

To create a form for the Product table you must select the Product table with your mouse. Click the Create tab. Then click Form. Select an AutoFormat style if you like. Then enter five Product records. Save the Form. Then either close the Form and reopen it or select Form View from the View drop-down menu on the top left side.

Finally, you will need to create a form for the Transaction table. Repeat the same process as you did for the Product form. You will need to create fifteen records. Be sure to mix up CustomerName so they are random. Also be sure to have the TransactionDate in chronological order. Note that you do not have to type in the CusomerName and ProductName. You only have to choose them from a drop-down menu.

Section III Queries

Task 1 Create a Query in Design View

To create a query you will need to click on the Create tab and then select Query Design

The Show Table dialog box will pop-up and you will need to select all three tables. You can make a query with only one table but we will always sue three tables. Simply select the table and click Add. When all three tables have been Added then click Close. You may want to Add the Transaction table second in order to have the relationship lines uncrossed.

This is what the query window now looks like. You will have to select the appropriate fields from each table to create the query. You will also need to have correct criteria entered in order to query the required records.

To add fields to the query you either need to double click on the appropriate field or drag the field to the query or select the field within the query drop-down menu. In this particular query we want to find all the records where a customer purchased a product called “Siu Mai”. You may choose any fields to add to your query. In this query we have chosen the ProductName, CustomerName, Price, Quantity, and TransactionDate. Once you have completed this query you will need to create four more queries. Each query you create should use all three tables and include the criteria. Be sure each query is good and unique.

The criteria is a very important part of the query. In this example the ProductName is text data type so after you type in the product’s name, Siu Mai in my example, then the database will add the quotations as shown here: "Siu Mai". Other fields may not need the quotations.

After you have created the query then you will need to click on the View menu button and select Datasheet View to see your query.

Your query will appear as shown below. Although, you may not have the same record content. If you want to hide the ProductName from appearing then you simply have to check the box as shown below

Uncheck the ProductName Show box and ProductName will not appear in the query.

Note: If you want to use TransactionDate in the criteria then you have to use the # symbol as shown below. The criteria will display records between June 4 and June 15. You can also create a criteria based on a number ex) >300.

>=#6/4/2014# And <=#6/15/2014

Section IV Reports

Task 1 Create a Report

A report allows you to create a printable view of you tables or queries. A report also allows you to group your records by CustomerName or by ProductName or by any other field. A report also allows you to subtotal any field with a number data type. To create a report click on the Create tab and select Report Wizard. It is much easier to create a report from a Wizard.

I have created a query using all fifteen records in order to create a more interesting report. The Report Wizard will then ask you to choose from a list of Tables and Queries that you have created. I am selecting the AllRecords query that I created. This query did not include any criteria to allow maximum records.

Next you will need to move all the fields from the Available fields window to the Selected fields window by click in the >> button. Click Next.

The next window allows you to select which field to group your report. For example if you leave the selection as by customer then the Report will be grouped by CustomerName. An example is shown below. Click Next.

Costco

Pai Gwat 100 2.84 14-Jun-14

Dan Ta 200 1.95 12-Jun-14

In the next window you are asked for any additional grouping. You can skip this step. Click Next.

In the next window you can select to sort fields in the report. For example, I have selected to sort by ProductName. This will change the appearance of the report such that the ProductName will be sorted as shown below. Now click the Summary Options. We will need to find the sum total of the Quantity field.

Costco

Dan Ta 200 1.95 12-Jun-14

Pai Gwat 100 2.84 14-Jun-14

Click the check box under Sum for Quantity and click OK. That will return you to the previous window and you can click Next.

Continue to click Next until you get to the final window. You can change the title for the report if you like otherwise just click Finish.

This is the Report completed. You only need to create one report. Your report may not look exactly the same as my report. I used a query without any criteria in order to show more records. You can see the the report is grouped by CustomerName. The records for each customer are displayed below and are sorted. The Sum of the quantity for each customer is also displayed. Your assignment is complete.