microsoft access

Page 1 of 5

IS 312: Fall 2020 Semester

Database Project Specifications Submitted by Due Date specified in CANVAS - ASSIGNMENT

CREATE A RELATIONAL DATABASE (30 points)

1. Use data provided in the tables on pages 3-4 to draw an Data Model for this database (in a Word document)

2. Make a Data Dictionary for this database (in a Word document). Pay attention to the data types and data sizes of the keys.

3. Use MS Access to create a database with the information given in the tables on pages 3-4.

a. Create Connectivity /Relationships among tables. b. Enforce Referential Integrity

• Save the database under the name “SalesAccounts” onto your local disk.

CREATE QUERIES (30 points) Create appropriate queries to answer the following questions (pay attention to the required fields and relevant tables):

1. List all info of customers living in Utah (UT). Sort the results by City. 2. List all info of customers living in Utah (UT) or Arizona (AZ). Sorted by State. 3. List all customers (Customer Name, City, State) have made purchases from

salesrep Mick? 4. Show Customer Number, Customer Name, Invoice Number, Date, Item Number,

Description, Unit Price, and Quantity Sold of each item on Invoice Number 103. 5. Show Customer Number, Customer Name, Invoice Number, Date, Item Number,

Description, Unit Price, and Quantity Sold of each item for sales made in November 2014. Sort results by Customer Number.

6. Show Item Number, Description, and Inventory Cost (UnitCost * QOH) for each item in inventory.

7. Show the Date, Item Number, Description, Unit Price, Unit Cost, Quantity Sold and Profit for Item Sold on Invoice Number 107.

• Save queries under the names: “qryQuestion_1”, “qryQuestion_2”, etc…

CREATE FORMS (20 points)

• Create the following Forms: 1. Create Inventory Form for table Inventory. Create a “Textbox” to calculate

Inventory Cost = [UnitCost] x [QOH] for each item 2. Create a Customer-Sales-Register Form in which Customer is in Main-Form

and Sales in the Sub-Form • Save all forms; use prefix frm (frmInventory, frmCustomer-Main, frnSales-

Sub)

Page 2 of 5

CREATE A REPORT (20 points)

• Create an appropriate query to gather necessary information for the attached report sample on page 5. Save this query as qrySalesReportData.

• Use this query to create a Sales Report following the attached layout. o The report has 2 level groupings (Salesperson and Customer). o For each line item, calculate Extension = [UnitPrice] x [Quantity] o Put your name on the header of the report.

• Save the Report as “Sales Report”. DELIVERABLES

• Print out of Data Model • Print out of Data Dictionary • Printout / Screenshot Access Relationship Window. • Printout / Screenshot Design Views all Tables • Printout / Screenshot Design Views and Data Views of all Queries. • Printout / Screenshot Design Views and Data Views of all Forms. • Printout / Screenshot Design View and Data View of “Sales Report ”.

Assemble all print-outs / screenshots in one single Word file then print / save it into PDF format. Save it under the file name “YourName_YourIDNo_DB312” Submit the final version of your work by the Due Date specified in CANVAS ASSIGNMENT. You have only ONE (1) attempt to submit your project and CAN’T resubmit again.

Page 3 of 5

Customer

CustNo Name City State CreditLimit

1000 Smith Tucson AZ $2,500.00 1001 Jones St.Louis MO $1,500.00 1002 Jeffries Atlanta GA $4,000.00 1003 Gilkey Phoenix AZ $5,000.00 1004 Lankford Tampe AZ $2,000.00 1005 Zeile Salt Lake UT $2,000.00 1006 Pagnozzi Emery UT $3,000.00 1007 Arocha Tooele UT $1,000.00

Inventory

ItemNo Description UnitCost UnitPrice QOH

1010 Blender $14.00 $29.95 200 1015 Toaster $12.00 $19.95 300 1020 Mixer $23.00 $33.95 250 1025 Television $499.00 $699.95 74 1030 Freezer $799.00 $999.95 32 1035 Refrigerator $699.00 $849.95 25 1040 Radio $45.00 $79.95 100 1045 Clock $79.00 $99.95 300

Sales InvoiceNo Date Salesperson CustNo

101 12/03/14 Wilson 1000 102 11/05/14 Mick 1003 103 10/05/14 Jackson 1002 104 11/15/14 Drezen 1000 105 10/15/14 Martinez 1005 106 10/16/14 Mick 1007 107 11/29/14 Mick 1002 108 11/03/14 Martinez 1000

Page 4 of 5

Sales-Inventory

InvoiceNo ItemNo Quantity

101 1025 1 101 1035 1 102 1045 3 103 1010 1 103 1015 1 103 1025 2 104 1025 1 104 1045 1 105 1035 1 106 1045 1 107 1030 1 107 1035 1 107 1040 2 107 1045 2 108 1025 1 108 1045 1

Page 5 of 5

Sales Report Prepared by YOUR NAME

Salesperson Customer Invoice Date Description Unit Price Quantity Extension Drezen Smith 104 11/15/14 Television $699.95 1 $699.95 104 11/15/14 Clock $99.95 1 $99.95 Jackson Jeffries 103 10/05/14 Television $699.95 2 $1,399.90 103 10/05/14 Toaster $19.95 1 $19.95 103 10/05/14 Blender $29.95 1 $29.95 Martinez Smith 108 11/03/14 Clock $99.95 1 $99.95 108 11/03/14 Television $699.95 1 $699.95 Zeile 105 10/15/14 Refrigerator $849.95 1 $849.95

(- - - - - - - - - - - - - -- - - - - - - - - - - - - - - - )