BTM311 Asgn C: Building a Database using Microsoft Access: ValueFresh Stores Limited (ValueFresh)

ValueFresh Stores Ltd has been serving the retail shopping needs of rural and small-town communities in British Columbia and Alberta since 1968.  Originally, they provided dry goods to farmers and ranchers, but they expanded to carry a full array of groceries and other items for residential (and some commercial) customers.  Although not as large as “bigbox” stores, they have developed a strong and growing base (20 stores) in small towns by hiring residents to run their stores with a local, community-centered focus. 

“Once we put data into our database, we need to get it out again”, said Miranda Bellows.  “To do this, we need help in converting our business requirements into commands that the database understands.  We need a form to help us capture data about producers and their products, a query that calculates order-stats for selected stores, and a report on discounts-given for non-tax-exempt products.”   

REQUIREMENTS:   Working individually, deliver the following:

A) Build an Access 2016 (or compatible) database of TABLES that match the “ValueFresh_Asgn_C_2022_01_Data_Model” handed out in the Assignments folder on your BTM311 eClass web-site (do NOT use your personal data model that you built in the previous assignment).  In the database, install LOOKUPS on all foreign key fields, so that the database operator can always see a list of correct choices when inputting values.  For all lookups, un-check the “hide key column” box in the lookup wizard. Ensure all relationships from the data model are registered and ensure your relationships still include referential integrity enforcement.  Ensure lookups are functional and helpful (ie. show more than 1 field but no more than four fields).

B) Using Access’s “Get External Data” function, populate your Access tables with all appropriate data from the Excel spreadsheet (“ValueFresh_Asgn_C_2022_01_Data”) handed out in the eClass Assignments folder.  Initially, you may have to reformat and “clean up” the tables by cutting and re-pasting them.  Suggestion: first import rows to the tables that only act as “1” tables in 1-M relationships – then add rows to their “Child” tables, and so on.     

C) Using Access wizards, build a “FORM that contains a sub-form” (based on the Producer and Product tables).  This form should allow staff to enter new Producers, one Producer at a time.  When entering a new Producer, the operator should also be able to enter multiple Products for that Producer.  For each table involved in this form, all attributes should be displayed, and should be operator-enterable.  Ensure your form is fully functional and well designed/laid-out.

D) Use your new “Producer form” to insert at least 2 new Producers, with 3 Products for each (invent your own values).  

E) Build a QUERY that gathers Order-Items for Orders sold from Stores with loading-dock size > 42000.  For each Order, display: its store-id (& store-municipality), member-id (& member-last-name), order-placed-date, cashier number and order id.  Include Orders even if they have no Member (hint: use outer join).  Prepare an extracolumn which calculates “unit price paid * quantity-ordered” (to be summarized later).  Group rows so there is one row per order.  For each order, display: Sum of your above calculated-column (use heading “Total”), Count of the Order-Items (use heading “Order-Lines”), Sum of the quantity-ordered (use heading “Items_Carried”) and Max of the unit-price-paid (use heading “Biggest_Ticket”).  Sort the query by store ID, then by member-id, then by order placed-date, then by order-id (first two ascending & last two descending).  The left-to-right order of columns in query should be appropriate, given the required sort order.  Ensure the query functions correctly, always extracting the correct data in the right format.

F) Build a REPORT (based on a newly created QUERY, not query ‘E’ above) that retrieves all Order-Items for each Product that is not GST-exempt.  Each row of output should include the product-line description, producer-name along with the Product’s id-number, descriptive name and regular-price.  As well, each row should also show the Order-Item’s unit-price-paid, along with an extra column which calculates regular_price – unit_price_paid (use heading “Discount-given”).  Include Product-Lines even if they have no Product, and include Products even if they have no Producer or no Order-Item (hint: use outer joins).  The report’s grouping-levels should be by product-line-description and by producer-name.  The report should be further sorted by product-descriptive-name (ascending).  The left-to-right order of columns in the report should be appropriate, given the required sort order.  Ensure the report is fully functioning, well designed and laid-out, and always reports the correct data.  HINT: do most of this work in your query – then use the query in a report.

G) Deliver the database in one accdb file using the filename stated in the Assignment submission instructions (see eClass Assignments page).  Keep the total size of your database file to less than 25 Mb.  Databases over the limit will be penalized.  Save extra backup copies of your database often.  Submission instructions are provided on the eClass Asgn C page.

Cheating and Plagiarism:

All work is to be done individually.  Do not copy, in whole or in part, the work of others, including paper printouts, electronic files or computer programs.  Do not use the work of others as a starting point and then modify it.  All work submitted under your name must be yours and yours alone. 

The University of Alberta is committed to the highest standards of academic integrity and honesty. Students are expected to be familiar with these standards regarding academic honesty and to uphold the policies of the University in this respect. Students are particularly urged to familiarize themselves with the provisions of the Code of Student Behavior (online at www.ualberta.ca/secretariat/appeals.htm) and avoid any behavior that could potentially result in suspicions of cheating, plagiarism, misrepresentation of facts and/or participation in an offence. Academic dishonesty is a serious offence and can result in suspension or expulsion from the University.

Get expert help for BTM311 Asgn C: Building a Database using Microsoft Access and many more. 24X7 help, plag free solution. Order online now!