代写BISM7206 Information Retrieval and Management Assessment 2代做迭代

BISM7206 Assessment 2

Wine Company Case

INTRODUCTION

The wine company was founded in 1989 in the Hunter Valley region in New South Wales.  The company grows grapes on 27 acres of land and sells wine on the open market.  The first white wine was released in 1989 and their first red wine in 1992.  Each of these wines won silver at the Hunter Valley Winery Festival. This success enabled them to expand their operations in 1996 and they purchased another 14 acres to increase the quantity and variety of wines produced.

Over the last 16 years, the company has become one of the major boutique wine manufacturers in the Hunter Valley and has grown to around three times its original size.  The company wins annual gold medals for many of its wines and has developed a large following of wine connoisseurs.  Amy, one of the winery’s co-owners, has been keeping the winery's business records since the business started.  This has been accomplished using a variety of Excel Spreadsheets and Word Documents.  This had been successful for many years, but with the last expansion of the winery, Amy has been finding it hard to complete her record-keeping.  As a result, she recommended to the other owners that it was time to further automate the business’s record keeping.

Subsequently, the owners contacted a database consultant to analyse the Winery’s information requirements and to propose a solution to the problems Amy was having with the record keeping as a result of the Winery’s growth.  They have only partly completed the job by writing the descriptions that follow.

PERSONNEL

The wine company employs 32 permanent workers, who focus on all of the functions of the winery including clerks to grape farmers to wine makers. These functions are called roles. The role is to be given a unique role identifier and have its name and description stored. Over time employees can change roles and can be fulltime or parttime. Among the employees, supervisors have been appointed to oversee the work of other employees. Each supervised employee reports to only one supervisor.  Supervisors report directly to Mark, one of the owners of the Winery.   In addition to these permanent employees, the Winery also hires about  10 casual employees on a seasonal basis to help pick grapes. These  casual employees report to the grape farmer supervisor. Each employee, upon employment, is  assigned a unique employee identification number. In addition to the employee's name and position, the company also records each employee's tax file number, address, and phone number. An employee can have more than one phone number, so the primary contact is indicated in the record. It is important to store the history of roles, supervisors, addresses and phones. Therefore, the start date and end date are stored for each of these. The end date is left null when current.

Each role has a unique role identifier with its name and description recorded. Many of the roles can have more than one employee hold the role at one time. For example, there are multiple grape farmers. When recording an appointment to a role whether the employee is fulltime or parttime is recorded, with the starting date. Further, whether they are casual or permanent is also recorded as part of the role history.

VINEYARD HOLDINGS AND GRAPE VARIETIES

At the time of this report, the wine company owns many vineyards in the surrounds of Rutherglen.  The vineyards range in size from 2 hectares through to 42 hectares. Each vineyard has its own unique name, such as Valley, Hightop, etc. and each is managed by a grape farmer.  No employee manages more than one vineyard, although the grape farmers report to the grape farmer supervisor.

The location and size, measured in hectares, of each vineyard are recorded. Decimal sizes need to be able to be stored.  Each vineyard is dedicated to the growing of a single grape variety per vintage year.  However, over time a particular vineyard may be replanted to a different grape variety, depending on market demand for particular types of wine.  This is an expensive undertaking and only occurs infrequently!  The winery maintains a record of these plantings.

The wine company currently grows the major Hunter Valley varieties plus a number of minor varieties and some experimental varieties.  Information that is specific for each grape variety and must be recorded includes the juice conversion ratio, a measure of how much juice, on average, can be extracted from a given weight of a grape variety, stored as a percentage. The wine storage requirement, which is the type of storage container (typically either stainless steel tank or oak barrel) used to hold the juice while it ferments into wine, is also recorded for each grape variety, as is the wine aging requirement, which is the measure of how  long (in days) the wine produced from the juice should be stored before bottling. Certain measures related to the grapes that vary with the specific vintage year harvest are also recorded including the vineyard the grapes come from, the total amount (weight) of grapes harvested, and the ripeness of the grapes (expressed in % sugar).

WINE PRODUCTS AND BOTTLES

Information on the wines created from the grapes must, of course, be recorded.  Each wine produced is given a unique identification number in addition to its name. Other Information recorded for each wine is its vintage year, category (e.g., dry red, dessert, sparkling, etc.) and percent alcohol, a legal requirement.  Additionally, the wine company wishes to keep records of the medals won for each wine including: which medal, the year won, and the awarding organisation.  Also recorded is the employee in charge of making that wine.  Wine makers may be responsible for more than one wine at a time.

The composition of a wine may be entirely from a single grape variety or may be a blend of more than one variety.  The proportion of juice from each grape variety for each wine produced must be recorded.  Several of the grape varieties are used in more than one blended wine. None of the wine company’s wines are vineyard specified; that is, the wines are labelled by the grape varieties contained in the wines only, without reference to specific vineyard plots.

The wines are sold in case lots.  The winery refers to these case lots as products.  A product is a specific wine in a specific bottle size in a specific case quantity sold at a specific price.  Each product type is given a unique product identification number.  The wine company does not sell partial case, nor does it mix wines or bottle types in a single case.

The bottles used for the wines vary in capacity, shape, material (i.e. glass or plastic) and colour.  Each bottle type is assigned a unique bottle identification code.  The wine company maintains an inventory count of how many of each bottle type is currently on hand in their warehouse. The winery prefers to keep at least a month’s worth of bottle inventory on hand.  The usual cost per bottle is also recorded for each bottle type to aid in pricing the products and as a guide in calculating expected future bottle order costs.

The bottles  must  be  purchased  from  outside  suppliers.    Each  of  these  suppliers  is  assigned  a  unique identification number.  In addition to this number, each supplier’s name, address, and phone are recorded. Also recorded, for each supplier, is the name of the principal contact (i.e., account representative) at the supplier that handles the wine company account with their email address.

Bottles are acquired from the suppliers by placing orders.  Some bottle types may be ordered from more than one supplier.  Each order involves only a single supplier but may include more than one bottle type.  Usually, orders are filled completely by the suppliers, but occasionally an order must be filled with multiple shipments, due to a back-order condition at the supplier.  The wine company maintains careful records of what quantities are ordered and what quantities are received, as well as when the bottles are ordered, when they are received, and the actual price charged for the bottles.

CUSTOMERS

The wine company’s customers are mainly restaurants and wine shops, but the winery also sells to individuals via a mail-order newsletter.  All customers are assigned a unique customer identification number, and this number is recorded along with their address, email and phone number.  Individual customers also have their first and last names recorded along with their date of birth, in order to demonstrate legal age.   First and last names are also stored for business customers and are the details for the contact for the business. The wine company does not ship directly to overseas customers. They do sell to Australian based export companies. Restaurants, wine shops, export companies and other Australian based businesses have their company name and Australian Business Number (ABN) recorded.

All customers obtain their products from the wine company by placing orders. Each order is assigned a unique order number and the date the order is received is recorded along with the product or products ordered and the quantity or quantities desired.  A shipment is sent to the current customer address and never to a PO Box number or private bag. A shipment status of “pending” is assigned to an order until it is actually shipped , whereupon the status is then changed to “shipped”.  Customer orders are always filled in a single shipment as no back orders are possible.  Once the wine company is out ofa given wine for a particular year, no more can be produced.

ADDRESSES

It has been identified that the wine company stores addresses for multiple reasons. Customers, suppliers, employees and vineyards all must have a physical address. The customer, supplier or employee may change addresses. The wine company needs to know what address was valid on a certain date. The format of these addresses is a format that captures the majority of physical addresses. It follows the Australian standard.

The following address elements have been identified and for the purposes of the database a unique address identifier be given to each unique address:

•    Unit type and number (when required)

•    Level type and number (when required)

•    Building/property name (optional)

•    Place name (optional — used for acknowledging traditional custodians)

•    Address number (can include textual prefixes and suffixes)

•     Street/road name (or water feature or island name)

•    Locality/suburb name

•     State/territory name

•    Australia Post Postcode

Postal addresses are also stored but are optional. These may include post office boxes and private bags. These are used for accounts and mail outs ofthe mail-order newsletter. Orders are never shipped to this address. A history ofthe address is to be stored like the other addresses.

Additionally, the GPS location of a vineyard needs to be recorded. This does not change. A location is made up of a longitude and latitude point. For example, UQ has a GPS position of Latitude: -27.4920 and Longitude:

153.0077. This will be sufficient for the purposes of this assessment. There are other ways that vineyards are also located.

CONCLUSION

The preceding information was obtained by the database consultant through discussions with the winery owners, and some of the  supervisors and through observations of the operations of the winery.   Some additional points have been determined as follows:

•   The database system initially desired by the winery owners will not have to handle employee payroll, as this is handled by an external accounting firm.

•   Order payment details of how the customer paid are not stored in a separate accounting system. Only an indicator is stored to show that the customer has paid so that the shipment can proceed. Delivery costs are also handled by the accounting system’s payment component. A customer is charged what it costs the company to ship so there is no profit or loss to be considered by this system. Refunds when the desired number of products cannot be filled are handled by the accounting system.

•   No other inventory requirements exist for the system beyond those previously identified. Corks, labels, barrels and packing boxes are acquired in large annual lots and are not a concern for the winery owners to track with the database system unless your group has them as part ofthe sustainability initiative (see next point).

•   Production expenses apart from bottles are not part of this version of the database.

•   To demonstrate an understanding of how data changes over time without loss of information, it will be sufficient for this database to show how this can be accomplished for all addresses (apart from Vineyards as they do not move), all phone numbers and personnel has detailed previously. This can be accomplished through the use of start and end dates which include time.

•   The owners recognise the importance of being transparent with sustainability initiatives. They have requested that on facet of sustainability being recorded in the new database and example reports be provided. This is to make a start on better sustainability reporting.

The winery owners are hopeful that the software you will design for them will help take some of the record- keeping burdens off them so that they can spend more of their time involved with the grape growing and wine making activities that they love.

Useful resources

You may find the following resources useful for extra background and explanations.

Wine Australia’s website (Wine Australia - Empowering the success of Australian wine | Wine Australia) which has last year’s report available. Wine AustraliaVintageReport2024.pdf

Sustainable Wine Growing Australia (Sustainable Winegrowing Australia | Let's make a world of difference in wine). This site has an extensive list of resources.

And an interesting article from the Financial Times (2024) on cork versus screw top bottling for those groups looking at the Inventory and Supplier Management Perspective (Cork vs screwcap: which is better for the planet?).