Introduction
Pharmacies are an essential component of healthcare and handle the function of selling medicines. Even though the pharmacies do not seem different than any other shop, their functioning is very different due to various laws regarding medicines. For example, most of the medicines available in a pharmacy cannot be purchased without a prescription.
Even with a signed prescription, there is a limit on the quantity that can be purchased. Additionally, pharmacists can do a background check on customers’ medical history to ensure that they are not involved in medical abuse. In addition, there are other laws on the operations of pharmacies like a requirement for the safe disposal of expired medicine and the requirement of license for employees that mix/prepare the medicines.
Thus, preparing a Database Management System for a pharmacy not only requires a study of how things are handled from a customer or employee point of view but also the relevant rules and regulations.
With this Pharmacy Management System DBMS Project, we aimed to develop a comprehensive system that could deal with challenges faced in day to day operation of a modern pharmacy.
ER Modelling
The final ER diagram and UML diagram are shown below with explanations.
ER Diagram
UML Diagram:
1. A single customer can have multiple prescriptions. Thus, the relationship between them is one too many.
2. A prescription consists of multiple medicines, so the relation is one too many. In the case of refills, a prescription can generate multiple orders. So, this relation is one too many as well.
3. A single order can contain multiple medicines, thus the relationship is one too many. One order, however, can generate only one bill. Thus, the relation between bill and order is one-to-one.
4. A customer can make multiple purchases and hence, the relationship between the customer and the bill is one too many. This is because every bill has only one customer.
5. In the medicine table (stock), the medicine’s name and batch numbers can uniquely identify every medicine we have in inventory. The batch number is assumed to be unique among manufacturers.
6. Disposed medicines are weak entities and use foreign key medicine’s Name and Batch Number as their primary key.
7. One employee can receive multiple notifications and one notification can be sent to multiple employees, thus the relationship is many to many.
8. Multiple employees can dispose of the same medicines. Similarly, one employee can dispose of multiple medicines. Hence, the relationship is many to many.
9. One employee can prepare multiple orders. However, a specific order can only be prepared by one employee. Thus, the relationship is one too many. Relations and Normalization
Conclusion
The Pharmacy Management System DBMS Project was a good learning experience for implementing a real-world DBMS and helped us understand the nuances of full implementation. The most interesting part was the experience of starting from the real world and then translating the concepts into the terms of a DBMS. Paired with a capable application front end, it can handle day-to-day operations for a pharmacy.
Download the Complete Pharmacy Management System DBMS Project Code, Report, and deployment help for free.