Data and Knowledge Management CHAPTER OUTLINE 5.1 Managing Data 5.2 The Database Approach 5.3 Big Data 5.4 Data Warehouses and Data Marts 5.5 Knowledge Management 5.6 Appendix: Fundamentals of Relational Database Operations LEARNING OBJECTIVES 5.1 Discuss ways that common challenges in managing data can be addressed using data governance. 5.2 Discuss the advantages and disadvantages of relational databases. 5.3 Define Big Data and its basic characteristics. 5.4 Explain the elements necessary to successfully implement and maintain data warehouses. 5.5 Describe the benefits and challenges of implementing knowledge management systems in organizations. 5.6 Understand the processes of querying a relational database, entity-relationship modeling, and normalization and joins. Opening Case Tracking Pot from Seed to Customer The Problem POM When Colorado's Amendment 64 legalized recreational mari- juana in 2012, most of its support came from voters in Denver and its surrounding metropolitan area, and not from the state's more rural ar- eas. As a result, Colorado remains divided over the drug, which is still illegal under federal law (as of March 2017). At least partially in response to this ongoing debate, Colorado has placed strict regulations on all aspects of the marijuana production process. These regulations mandate that producers and distributors track each plant throughout its life cycle. The problem was how to im- plement these regulations in an efficient, accountable, cost-effective manner. The IT Solution tuana growers and distributors are having to adjust to eas. As a result, Colorado remains divided illegal under federal law (as of March 2017). In fact, of 321 municipalities in Colorado, 228 opted out of allow- ing marijuana..Local officials and law enforcement throughout the state, unconvinced of the benefits of legalizing marijuana, have tried to push back against legalization, citing the federal government's ban on the drug. Meanwhile, investors, business owners, tourists, and many Colorado residents argue that legal recreational marijuana has benefited the state. The IT Solution Colorado's marijuana growers and distributors are having to adjust to regulations concerning their newly' legitimate businesses. Under the state's rules, growers must put a microchip on each plant so it can be recorded and monitored in Colorado's Marijuana Enforcement Track- ing Reporting Compliance (METRC; www.metrc.com) system. Franwell (www.franwell.com), a Florida-based technology com pany, developed METRC. The system provides end-to-end tracking and 118 Introduction 119 tracing of marijuana plants and products. Franwell developed the sys- tem specifically for government agencies in charge of legalized mari- juana enforcement. As of November 2016, METRC had registered over 20,000 users and tracked over 5 million plants and 4 million packages. Here is how METRC works. A yellow radio-frequency identification (RFID) tag placed on each plant enables officials consulting a statewide database to track crops from "bud to blunt." The tags contain 24-digit identification codes, and they are staked into the soil or wrapped around individual plants while they grow. The tags travel with the plants through the grow house, to harvest, and as the pot flowers are dried and cured for flavor. (A grow house is a property that is primarily used for the production of mar- ijuana.) For shipment to stores, each strain of marijuana is grouped into a batch that receives its own RFID tag, which remains on the pack- ages until the batch is sold. During scheduled and unscheduled visits to dispensaries or grow houses, state officials use RFID scanners and electronic inventories to ensure that none of the plants goes missing. Each plant tag costs 45 cents, and the batch package tag costs 25 cents. Growers contend that the tags are expensive. For many grow- ers, however, their biggest frustration is having to enter the data twice: once into their own accounting databases and again into the MITS sys- tem, which is not compatible with most other accounting programs. The penalties for mistakes in entering the data include loss of license and criminal charges. Given that the potential for mistakes is high, growers feel that these penalties are excessively harsh. For example, when a plant dies, growers must follow a lengthy ticketing procedure Other growers are installing their own RFID scanners to electronically track each plant's location in their warehouses. The Results As of November 2016, the state of Colorado had issued some 3,000 li- censes for marijuana cultivation, production, and testing facilities and retail stores in both the medical and recreational markets. Marijuana brought in nearly $80 million for the state in taxes and fees during the 2015 fiscal year (based on $996.5 million in total licensed marijuana sales). The revenue comes from a 2.9 percent retail and medical mar- ijuana sales tax, a 10 percent retail marijuana special sales tax, and a 15 percent marijuana excise tax, plus application and license fees for retail and medical marijuana. Those figures did not keep track of the economic contributions of thousands of tourists who come to Colorado partly for its marijuana. In fact, according to Colorado's Marijuana Enforcement Division, up to 90 percent of recreational marijuana sales in some areas, particularly mountain resort towns, are made to tourists. By May 2017, Alaska, Washington, Oregon, California, Massachu- setts, Nevada, Maine, Colorado, and the District of Columbia had le- galized nonmedical cultivation and uses of marijuana. Most of these states were planning to use an RFID system similar to Colorado's to monitor marijuana production and distribution. Sources: Compiled from T. Fuller, "Californians Legalize Marijuana in Vote that Could Echo Nationally," New York Times, November 9, 2016; J. Alsever, "Is Pot Losing Its Buzz in Colorado?" Fortune, June 29, 2016; R. Grenoble, "Revenue tem specifically for government agencies in charge of legalized mari- juana enforcement. As of November 2016, METRC had registered over 20,000 users and tracked over 5 million plants and 4 million packages. Here is how.METRC works. A yellow radio-frequency identification (RFID) tag placed on each plant enables officials consulting a statewide database to track crops from "bud to blunt." The tags contain 24-digit identification codes, and they are staked into the soil or wrapped around individual plants while they grow. The tags travel with the plants through the grow house, to harvest, and as the pot flowers are dried and cured for flavor. (A grow house is a property that is primarily used for the production of mar- ijuana.) For shipment to stores, each strain of marijuana is grouped into a batch that receives its own RFID tag, which remains on the pack- ages until the batch is sold. During scheduled and unscheduled visits to dispensaries or grow houses, state officials use RFID scanners and electronic inventories to ensure that none of the plants goes missing. Each plant tag costs 45 cents, and the batch package tag costs 25 cents. Growers contend that the tags are expensive. For many grow- ers, however, their biggest frustration is having to enter the data twice: once into their own accounting databases and again into the MITS sys- tem, which is not compatible with most other accounting programs. The penalties for mistakes in entering the data include loss of license and criminal charges. Given that the potential for mistakes is high, growers feel that these penalties are excessively harsh. For example, when a plant dies, growers must follow a lengthy ticketing procedure to document and delete that plant from the system or face severe pen- alties for a "missing" plant. Franwell is working to develop a system that integrates with grower databases. The company notes that the water- and chemical- resistant tags are relatively cheap given that orders are usually small. A spokeswoman for Colorado's marijuana enforcement division maintains that red tape and fees are "part of being regulated." Com- plaints notwithstanding, one grower stated that RFID tagging and capturing the data in the MITS database is necessary to legitimize the industry. The plant tags do provide some benefits to growers. Specifically, they offer knowledgeable growers a closer look at which strains are selling well or where a troublesome batch shipped. For example, when a batch of pot-infused gummy bears shipped from Native Roots (http://nativeroots303.com, the largest marijuana dispensary in Colorado) became moldy, the store manager used the data recorded on the METRC database to locate and recall only the rotten gummies. track each plant's location in their warehouses. The Results As of November 2016, the state of Colorado had issued some 3,000 li- censes for marijuana cultivation, production, and testing facilities and retail stores in both the medical and recreational markets. Marijuana brought in nearly $80 million for the state in taxes and fees during the 2015 fiscal year (based on $996.5 million in total licensed marijuana sales). The revenue comes from a 2.9 percent retail and medical mar- ijuana sales tax, a 10 percent retail marijuana special sales tax, and a 15 percent marijuana excise tax, plus application and license fees for retail and medical marijuana. Those figures did not keep track of the economic contributions of thousands of tourists who come to Colorado partly for its marijuana. In fact, according to Colorado's Marijuana Enforcement Division, up to 90 percent of recreational marijuana sales in some areas, particularly mountain resort towns, are made to tourists. By May 2017, Alaska, Washington, Oregon, California, Massachu- setts, Nevada, Maine, Colorado, and the District of Columbia had le- galized nonmedical cultivation and uses of marijuana. Most of these states were planning to use an RFID system similar to Colorado's to monitor marijuana production and distribution. Sources: Compiled from T. Fuller, "Californians Legalize Marijuana in Vote that Could Echo Nationally," New York Times, November 9, 2016; J. Alsever, "Is Pot Losing Its Buzz in Colorado?" Fortune, June 29, 2016; R. Grenoble, "Revenue from Colorado Marijuana Tax Expected to Double in 2015," The Huffington Post, September 21, 2015; K. Wyatt, "Colorado Pot Growers Save Big Bucks on Tax Holiday Caused by Quirk in State Law," US News and World Report, September 16, 2015; J. Frank, "Ahead of 2015 Vote, Campaign Pushes Marijuana Tax Question in Colorado," Denver Post, September 13, 2015; C. Cooper, "Colorado Profits, But Still Divided on Legal Weed," The Center for Public Integrity, August 16, 2015; R. Baca, "Colorado Pot Sales Spike in June, Top $50 Million for First Time," The Cannabist, August 13, 2015; H. Borrud, "Oregon Pot Tracking System Will Use RFID Technology," East Oregonian, June 14, 2015; S. Lohmeyer, "States Turn to Tech for Tracking Marijuana," GCN.com, May 26, 2015; K. Weise, "Tracking Colorado's Legal Pot, Plant by Plant," Bloomberg BusinessWeek, March 17-23, 2014; R. Hiscott, "RFID Tags Track Marijuana from Seed to Sale in Colorado," Mashable, February 11, 2014. Questions 1. Describe how database technology plays an important role en- abling Colorado to closely monitor the marijuana production and distribution process. 2. Describe potential disadvantages in using RFID tags to monitor marijuana production and distribution.