CSE3DMS: Physical database design - Data Analysis Assignment

Internal Code: MAS3412

Data Analysis Assignment:

Chemist Warehouse is the Australia's largest pharmacy retailer. It has over 1000 retail stores nationally and been growing at a rapid rate. Chemist Warehouse Group employs over 20,000 staff members including certified pharmacists and front selling clerks. Each store sells over 65,000 products of healthy related products coming from different suppliers. The products are managed by related departments such as Vitamins; Beauty; Fragrances; Baby Care; Dental; Household; Hair Care; Protein etc. The prescribed medicines must be prepared by registered pharmacists. Chemist Warehouse Group has got a distributed database systems to manage the operational business within the company. The database records all products, inventory, stores, suppliers, employees and sales information. To help the organization drives sales, marketing, loyalty, and service effectiveness, they also award customers with a VIP program. To ensure high customer numbers, management needs to carefully consider its pricing and VIP discounts. The VIP can be classified into 5 levels: 1: 2% discount for blue member; 2: 4% discount for silver member; 3: 6% discount for gold member; 4: 8% discount for diamond member; 5: 10% discount for platinum member. Questions: 1) To understand physical database design as a critical element in achieving overall database objectives. 2)  To have a sound understanding of the use of indexes and the trade-offs that must be considered in their use. 3) To understand the concepts of database query optimization. 4) To tune database queries using Oracle facilities such as hint and autotrace for better performance