[SOLVED] CS 350 Database Management Systems Fall 2024

CS 350—Database Management Systems

Fall 2024

leeanne vineyards, inc.

In 2009, Lee and Anne Woods left behind careers in law and accounting, respectively, to pursue their dream of growing grapes and making wine. They moved to Mendocino County in California and purchased 25 acres of grape-growing land and a small winery and house. They called their new venture LeeAnne Vineyards and released their first wine for sale in 2012. They have been successful enough to gradually expand their land holdings and to increase the quantity and variety of the wines they sell. In fact, the expansion of their business has led the Woods to consider automating the record keeping of their business with a computer database. They have invited several database consulting companies, including yours, to send in teams of analysts and developers to evaluate their business needs and to propose an automated solution to the record keeping problems brought on by the company’s continued growth. The results of the preliminary investigation recently conducted by an analyst from your company are presented below.

LeeAnne Vineyards, Inc. (LVI) currently employees over two dozen full-time employees, with positions ranging from clerks to grape farmers to wine makers. Among the employees, supervisors have been appointed that oversee the work of other employees. Each supervised employee reports to only one supervisor. Each employee, upon employment, is assigned a unique employee identification number. In addition to the employee’s name, position, and ID number, the company also records each employee’s social security number, address, and phone.

LVI’s vineyard holdings now include ten plots of land, in separate locations, ranging in size from two acres to twenty acres. Each plot of land is a vineyard. Each vineyard has its own unique name, such as Hilltop, Westside, etc. and each is managed by a single employee. No employee manages more than one vineyard. The location and size of each vineyard is recorded. 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. The winery maintains a record of these plantings.) LVI currently grows six different grape varieties: Cabernet Sauvignon, Merlot, Zinfandel, Pinot Noir, Chardonnay, and Sauvignon Blanc. Information that is specific for each grape variety and that is necessary to record includes the juice conversion ratio (a measure of how much juice, on average, that can be extracted from a given weight of that grape variety), wine storage requirement (the type of storage container, typically either stainless steel tank or oak barrel, that is used to hold the juice while it ferments into wine), and wine aging requirement (a measure of how long 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 percent sugar).

Information on the wines created from the grapes is, of course, of significant interest to record. 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, etc.), and percent alcohol (a legal requirement). 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 100 percent from a single grape variety or may be a blend of more than one variety. A requirement is to record how much juice from each grape variety makes up each wine produced. Several of the grape varieties are used in more than one blended wine.

The wines are sold in case lots. These case lots are what the winery refers to 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. LVI does not sell partial cases, nor does it mix wines or bottle types in a single case. The bottles used for the wines vary in capacity, shape, and glass color. Each bottle type is assigned a unique bottle identification code. LVI 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 glass vendors. Each of these vendors is assigned a unique identification number. In addition to this number, each vendor’s name, address, and phone are recorded. Also recorded, for each vendor, is the name of the principal contact (i.e., account representative) at the vendor that handles the LVI account. Bottles are acquired from the vendors by placing orders. (Some bottle types may be ordered from more than one vendor.) Each order only involves a single vendor, but it may include more than one bottle type. Usually orders are filled completely by the vendors, but occasionally an order must be filled with multiple shipments, due to a back-order condition at the vendor. LVI maintains careful records of what quantities are ordered and what quantities are received, as well as the date the bottles were ordered and when they are received, and the actual price charged for the bottles.

The products are, of course, sold to customers. Customers of LVI 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 and phone. Individual customers also have their first and last names recorded along with their date of birth (to demonstrate legal age). Company customers have their company name and tax identification number recorded. All customers obtain their products from LVI by placing orders. Each such 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. Customer orders are always filled in a single shipment as no back orders are possible. (Once LVI is out of a given wine for a particular year, no more can be produced.)

The preceding information was obtained by an analyst representing your consulting firm through early discussions with Lee and Anne Woods, and observations of the operations of the company. Further discussions with the Woods, as well as interviews with other LVI employees, have since been conducted along with a thorough analysis of the written records of the company. A few additional pertinent points have been determined, as follows. The database system initially desired by the Woods will not have to handle employee payroll, as this is handled by an external accounting firm. Also, no other inventory requirements exist for the system beyond those previously identified. (Corks, labels, and packing boxes are acquired in large annual lots and are not a concern for the Woods to track with the database system.) Additionally, the Woods desire to have certain information that the system will produce (particularly total sales revenues) available to only themselves and certain other supervisors. You have assured them that a system can be designed with passwords that determine what areas of the system can be accessed.