ABC Pharmacy (ABC) is a large, online retail pharmacy chain based in Ypsilanti, Michigan. With over 50 stores across the state, the company provides customers with prescription filling services. ABC keeps a listing of all customers' information in a centralized database that can be accessed from any ABC stores across Michigan. A unique id number is assigned to each customer as an identifier in the system. The information in the database includes customer Id#, name, address, phone number, and insurance type. Some of customers do not have medical insurance. For each customer his/her physician does not issue more than one prescription for the same medication on the same day. All prescriptions are electronically transmitted to ABC Pharmacy directly from the doctor's office for the customer (or patient) when prescribed by doctors. Each prescription has one or multiple medications, and is transmitted online by one physician to the pharmacy and filled by one pharmacist for one customer. ABC maintains a listing of all physicians who have written prescriptions for customers in the past, and have the ability to add or delete physicians from the record. All physicians are identified by the Drug Enforcement Administration (DEA) identification numbers assigned to them, when they were licensed as a physician. ABC also retains contact information for all physicians in the listing, including name, specialty, address and phone number. A unique drug identification number (DIN) is assigned to all medications dispensed by all ABC stores state-wide, and later identifies medication records for inventory control. ABC also keeps the medication name, cost, and supplier name of all the medications. Each medication can be supplied from more than one suppliers. If a supplier cannot provide the quantity needed, another supplier is located. The suppliers are listed by their license numbers, which are unique for each supplier. ABC has access to mailing addresses and phone numbers for all suppliers. Each prescription has a unique prescription number that identifies it when retrieved from the database. The DEA is part of the prescription number. A prescription can hold information for multiple medications with the date issued. The customer's name, address and doctor's e-signature are on the prescription as well as the date and dispensing store number. Each customer (or patient) will be informed to pick up his/her medications at the nearest ABC store after the prescription is filled by a pharmacist, The Pharmacy maintains their pharmacists' identifiers and names, addresses and employment starting dates in the centralized database. Requirements 1. Based the above ABC case prepare a comprehensive E-R diagram and business rules for the data model which will support a system to meet the needs described above about ABC Pharmacy. *** You don't need to assume any further business details such as a customer's doctor visit(s) and prescription pick-up from the pharmacy, which are not in the scope of your database development. 2. Design a relational schema for ABC Pharmacy with following sections: 2.1 Table Structure 2.2 Domains 2.3 Constraints 3. Create your documentation (an ERD, business rules and a relational schema) in MS Word. 4. A total of 30 points (10 for ERD, 5 for description/business rules about your ERD, and 20 for the Relational Schema)
