Objective: 1. Consider Data Quality issues that can arise with working with databases and consider how they can be appropriately managed. 2. Apply data modelling skills. 1. Part A: Pharmacy Merger Problem The scenario: Over the past ten years, Scott's Pharmacy and Rees & Sons have been the main source of medicines in the town, supporting the local hospital, two nursing homes, four GP (General Practitioner/Doctor) surgeries and a number of district nurses and health visitors. MegaPharmacy has recently opened a store in the town, and the existing pharmacies have decided that to stay competitive they must merge. Each store has a database holding details of its day-to-day business, medicine stocks, customer details (for repeat prescriptions), and other stock details. Your task: You are requested to act as a Data Quality Consultant, to assist in the merging of the two databases. The first stage involved in the consultancy process is to explain to the pharmacy owners the problems they may face, and produce a set of guidelines to ensure their data is of high quality? Task 1: Create the outline of a short presentation that could be used to inform the pharmacy owners: 1. of the problems they may face when trying to merge their two databases 2. the potential data quality issues 3. the potential effect of poor quality data on their business 4. and provide guidelines (minimum of 5 points) to assist them in ensuring their database contains high quality data For this activity, identify the key points you would include in your presentation for each of the above 4 sections. 2. Part 2: Normalization and ERDs Below is some sample data: ProjectNo 1001 Project Description The construction of a new power plant to reduce the power failure problems Project Type Code P002 Project Type Des Power Plants Project Employees Employee Employee Name Union Union Description No Code 001 Andrew CBU Collective Bargaining Union Rutherford 002 Werner Olivier NTEU National Tertiary Education Union 003 Margaret Brittain CBU Collective Bargaining Union Equipment Equipment Description Equipment No Type Code 234 Ryobi Power surge ET001 Converter 321 Cement ETOO2 322 Number 2 Torch ET003 Project Equipment Equipment Type Description Level 1 Equipment Construction Material Light emitting device 1A) An employee can be assigned to more than one project at any one time. A piece of equipment can be assigned to different projects but not during the same time period. Employees belong to a single union no matter which projects they are assignment to 1B) A piece of equipment may be assigned to the same project more than once but not on the same time period (defined by the "Time-Period" attribute). Task 2: Create normalized tables to store the relevant information presented in the below reports. Task 3: Construct an ERD that accurately reflects the now normalized tables. Objective: 1. Consider Data Quality issues that can arise with working with databases and consider how they can be appropriately managed. 2. Apply data modelling skills. 1. Part A: Pharmacy Merger Problem The scenario: Over the past ten years, Scott's Pharmacy and Rees & Sons have been the main source of medicines in the town, supporting the local hospital, two nursing homes, four GP (General Practitioner/Doctor) surgeries and a number of district nurses and health visitors. MegaPharmacy has recently opened a store in the town, and the existing pharmacies have decided that to stay competitive they must merge. Each store has a database holding details of its day-to-day business, medicine stocks, customer details (for repeat prescriptions), and other stock details. Your task: You are requested to act as a Data Quality Consultant, to assist in the merging of the two databases. The first stage involved in the consultancy process is to explain to the pharmacy owners the problems they may face, and produce a set of guidelines to ensure their data is of high quality? Task 1: Create the outline of a short presentation that could be used to inform the pharmacy owners: 1. of the problems they may face when trying to merge their two databases 2. the potential data quality issues 3. the potential effect of poor quality data on their business 4. and provide guidelines (minimum of 5 points) to assist them in ensuring their database contains high quality data For this activity, identify the key points you would include in your presentation for each of the above 4 sections. 2. Part 2: Normalization and ERDs Below is some sample data: ProjectNo 1001 Project Description The construction of a new power plant to reduce the power failure problems Project Type Code P002 Project Type Des Power Plants Project Employees Employee Employee Name Union Union Description No Code 001 Andrew CBU Collective Bargaining Union Rutherford 002 Werner Olivier NTEU National Tertiary Education Union 003 Margaret Brittain CBU Collective Bargaining Union Equipment Equipment Description Equipment No Type Code 234 Ryobi Power surge ET001 Converter 321 Cement ETOO2 322 Number 2 Torch ET003 Project Equipment Equipment Type Description Level 1 Equipment Construction Material Light emitting device 1A) An employee can be assigned to more than one project at any one time. A piece of equipment can be assigned to different projects but not during the same time period. Employees belong to a single union no matter which projects they are assignment to 1B) A piece of equipment may be assigned to the same project more than once but not on the same time period (defined by the "Time-Period" attribute). Task 2: Create normalized tables to store the relevant information presented in the below reports. Task 3: Construct an ERD that accurately reflects the now normalized tables