Archivist’s Beer Vault Using MS Access

PROJECT

The Archivist’s Beer Vault (ABV) is a database that I have worked on since my second semester at UNT. Initially it was just a simple beer database built using a fairly low tech software. For my term project in SLIS 5707 Data Modeling for Information Professionals we were tasked to devise a multiple entity database and create it using Microsoft Access. Converting the ABV into a multi entity database was a bit perplexing but once I figured out the entity-relationship diagram it was smooth sailing. This was my first forray into Access and I was impressed with the flexibility and power of the program. Knowing some SQL commands that I used for my Team ASPCA Questionnaire project definitely helped with the query and reports aspects of it. I was able to finish with enough time to implement a great looking UI for the database. Here is my ABV built by Access.

Main Menu
Main Menu

 

OVERVIEW

The Archivist’s Beer Company (ABC), located in Pasadena, California, is the largest craft beer specialty store in the state. The store is stocked with well over two thousand individual craft beers from across the United States. The store sees 300 customers in any given day, of which 15-25 are new customers. A new soccer stadium down the street is set to open within a year and business is expected to boom. The database, Archivist’s Beer Vault (ABV), grew out of the need to keep track of the large collection or beers, as well as a rapidly expanding user base. The ABV houses not only beer and customer information but also distributor profiles and customer purchase history.


 

USER REQUIREMENTS

With the high amount of beers in stock, the store is not easy to browse. Instead of having to navigate aimlessly down endless rows of bottles and cans with only labels to go off of, customers use the ABV, through the ABC website and in-store kiosks, to smartly drill down by important beer attributes, such as the International Bitterness Units (IBU) or seasonal availability, which are not necessarily found directly on the bottle. Browsing beers that share beer style allows customers to see what other beers the store has that fits their preferences. In effect, the database exists to aid customers find the beer they need and as a discovery tool.

On the staff side, the ABV fills multiple needs that can only be achieved with a database. For example, the store’s layout is built around beer style instead of the usual brewery name organization scheme found in rival stores. That is to say American Pale Ales are right next to American Wheat Pale Ales but far away from Doppelbock. Staff must know what style a beer is set to and find the appropriate stocking location. The store has a policy of awarding contracts of one brewery to one distributor, the ABV allows management to ensure that the policy is enforced. The ABV also allows marketing staff to monitor customer purchase trends to adjust marketing campaigns to highlight underperforming beers.


 

BUSINESS RULES

  • A customer may have zero or many orders.
  • A customer has only one favorite style.
  • A style may have zero or many beers.
  • A style may be the favorite of zero or many customers.
  • A purchase order is submitted by one customer.
  • A purchase order has only one customer.
  • A purchase order contains one or many beers.
  • A beer is listed only once per purchase.
  • A beer has only one brewery.
  • A brewery has only one distributor.
  • A brewery may have zero or many beers.
  • A distributor may distribute zero or many breweries.

MAJOR ENTITIES

CUSTOMER

This table contains information related to ABC customers. It contains the customer’s rewards program unique ID, contact information and favorite beer style.

BEER

This table contains individual beer details. It stores the beer Universal Product Code (UPC), name, brewery, assigned style, and vitals.

BREWERY

This table contains information regarding a brewery company. It stores the brewery ID, name, location and distributor.

STYLE

This table covers beer style details. It stores the style name, country of origin, suggested serving temperature, recommended glassware, best food pairing and shelving location.

PURCHASE

This table maintains records of purchases. It stores the purchase ID, customer ID, date of transaction and total.

DISTRIBUTOR

This table contains information related to ABC distributors. It contains the distributor ID, location and contact information.


ENTITY-RELATIONSHIP DIAGRAM

ERD
Entity Relationship Diagram

 


 

SAMPLE QUERIES

  • How many fall seasonal beers does the store carry?

SELECT COUNT(BEER_Availability)  AS [Number of beers AVAILABLE]

FROM BEER

WHERE (BEER_Availability = “Fall”)

GROUP BY BEER_Availability

ORDER BY COUNT (BEER_Availability) DESC;

  • Which Oregon breweries are represented in the store?

SELECT Brewery_name

FROM Brewery

WHERE Brewery_AState = “OR”

ORDER BY Brewery_AState ASC;

  • Which distributors supply beers from Texas?

SELECT Distributor.Distributor_ID, Distributor.Distributor_CompanyName, Distributor.Distributor_Contact, Brewery.Brewery_AState

FROM Distributor, Brewery

WHERE Brewery_AState = “TX”

AND Distributor.Distributor_ID = Brewery.Distributor_ID

ORDER BY Brewery_AState ASC;

  • How many customers love Stouts (Style_ID =2)?

SELECT COUNT(Style_ID) AS [Number of Stout Lovers]

FROM Customer

WHERE (Style_ID = 2)

GROUP BY Style_ID

ORDER BY ‘Number of Stout Lovers’ DESC;


 

SCREENSHOTS

Using various options in MS Access I was able to create an interface for the ABV.

This slideshow requires JavaScript.