Case Study: Emerald Sharp

SCENARIO

ESSENCE OF THE CASE

• All new data that is entered in the main tables should automatically update pricing information in the foreign tables.

• Foreign pricing should be 10% less than domestic pricing.

• Like all organizations, they would like to have the prices synchronized with a minimum cost, effort, and time.

Emerald Sharp is a large manufacturer of high-quality cut-glassware products. It enjoys a national reputation for its products, but until now has not exported any goods. Emerald Sharp manufactures cut-glassware products in both mass production and careful customized production, for those willing to pay additional fees. Now Emerald Sharp is running at a profit with its 300 highly skilled employees. The company has 30 factories located around the nation, all in remote areas. Emerald Sharp has out-competed all its rivals who manufacture similar products, which are in great demand because of their quality. Emerald Sharp wants to reach out to the world with its quality products by dedicating ten more factories as export manufacturers to locations around the world. The company therefore needs to somewhat alter its existing data model to accommodate the needs of the factories abroad. After altering the data model, its employees will start to enter the new data into the database. Emerald Sharp has set the international price for its products as 10% less than their domestic price on all models created after May 15th, 2001. Emerald Sharp sells identical products both domestically and internationally, and creates almost 25 new models of glassware each month.

You work as a contractor and are hired to construct and implement this scenario Emerald Sharp is in.

ANALYSIS

You investigate the current scenario at Emerald Sharp and notice that Emerald Sharp sells to foreign countries at a discounted price of 10% less than the domestic price of goods on all models created after May 15, 2001. You have thought of a way to automate the process, so that Emerald Sharp will not have to manually create and populate a new table named ForeignPrice . You can create a trigger on the Product table and then write code to insert values into a new column or table named ForeignPrice . You decide to create an INSERT trigger on the Products table. The INSERT trigger fires when products are added into the table. The trigger verifies that the Manufacture_Date column of the record added is greater than May 15th, 2001. If it passes , it is entered into the table with an entry in the ForeignPrice column of 10% less than the domestic price. If the manufactured date of the product is not greater than May 15th, 2001, it is still entered into the table and has an entry in the ForeignPrice column that is equal to the domestic price.