This report presents the design, implementation, and administration of a comprehensive pharmacy database management system, focused on effectively managing data related to suppliers, medications, customers, staff, prescriptions, purchases, and activity logs. Developed using MySQL Workbench, the system employs advanced SQL programming to offer valuable reporting and insights.
Throughout the project's development, our team utilized GitHub for version control and collaboration. Each team member committed their work to a shared repository, allowing for seamless collaboration and tracking of changes made to the database and associated files. This approach ensured that the project maintained a consistent history of modifications, offering a platform for addressing potential issues efficiently. By combining a well-structured database design with effective collaboration, our team has delivered a pharmacy database management system that meets the organization's current and future needs.
To access the GitHub repository for this project, please click the following link: Project Repository
In the fast-paced and highly regulated pharmaceutical industry, effective data management is essential for maintaining accuracy, compliance, and profitability. A pharmacy database management system is an invaluable tool for pharmacists, enabling them to track inventory, sales, and customer records with ease. By leveraging the latest technologies and best practices in database design, a well-structured pharmacy database management system can streamline workflows, reduce errors, and improve patient care. The aim of this project is to create a robust, scalable, and user-friendly pharmacy database management system that can meet the diverse needs of pharmacists and pharmacy owners. Whether it's tracking prescription orders, storing customer records, or allowing access to supplier information, this system will provide a comprehensive solution to optimize operations and drive success.
We identified the key features that a pharmacy database should have effective inventory management, user friendly interaction and enhanced customer care. We also brainstormed various queries that would enhance the functionality of the database, allowing pharmacists to manage stock, prescriptions, and customer details. Through effective communication and collaboration, we were able to pool our knowledge and ideas to come up with a comprehensive proposal that meets the requirements of the college project. The process was challenging, but also rewarding, as we developed our skills in teamwork, research, and critical thinking.
The development of the ERD diagram for our pharmacy database management system involved a collaborative approach to ensure a simple yet effective design.
Idea Generation:
Team Meeting:
ERD Diagram Creation:
Modifications:
Addition:
By following this collaborative process, we successfully designed an ERD diagram that captures the necessary entities, relationships, and attributes for our pharmacy database management system. The addition of the Activity_Log table provides an extra layer of security, ensuring that the database meets the organization's requirements for data management and security.
To provide a visual representation of our progress in developing the ERD diagram, we have included links to the initial and final versions of the diagram. Click on the links below to view the evolution of our ERD diagram throughout the development process.
The ERD defines relationships between the entities with cardinality and participation constraints. Primary keys and foreign keys are outlined to ensure data integrity and referential integrity.
The pharmacy database was implemented using MySQL Workbench, with tables created based on the entities identified in the ERD. Each table includes primary and foreign key constraints to ensure data integrity. Throughout the implementation process, proper naming conventions and adherence to database normalization rules were followed.
The table creation process began with a scheduled team meeting to discuss the assignment of tables to each team member and determine the appropriate data types for the attributes. The tables were evenly distributed among the team:
After finalizing the database tables script, the team moved on to data population. Initially, we anticipated this step to be relatively simple, as it involved creating an INSERT statement for each table and adding values. The team agreed on 50 entries per table to ensure sufficient data for running queries later in the project.
However, when executing the data population script, we encountered various issues with the INSERT statements, such as misaligned foreign key constraints, primary key errors, and NOT NULL value errors. These challenges were compounded by the fact that each team member had their own style of inserting data, leading to inconsistencies.
To resolve these issues, we reduced the number of entries per table to 20 and then adjusted to 50 and focused on refining the INSERT statements. In hindsight, assigning the data population task to a single team member would have been more efficient, as it would have ensured a consistent approach throughout the process.
With the revised data population script, we successfully populated the tables, eliminating errors related to foreign key constraints, primary keys, and NOT NULL values. This experience highlighted the importance of clear communication and consistent formatting when working on a collaborative project.
Our team developed a total of 12 complex queries using WHERE, GROUP BY, and JOIN clauses etc. as outlined in the project description. Each team member contributed four queries, providing valuable insights and information for the organization's current and future needs.
The development of these queries was discussed during a team meeting where all members shared their ideas and research from lecture slides. During the meeting, we decided to work on a few queries together and then evenly distribute the remaining queries among team members for further development and upload to our GitHub repository.
select al.transaction_id, al.date_time, al.action, s.Name as Staff_Name
from activity_log al
inner join Staff s on al.user_id = s.Staff_ID
where al.date_time >= date_sub(now(), interval 6 month)
order by al.date_time asc;
This query retrieves the names and email addresses of the staff members who were involved in processing purchases on a specific date.
select distinct s.Name, s.Email
from Staff s
join Purchase p on s.Staff_ID = p.Staff_ID
where p.Date_Purchased = '2023-04-15';