Pharmacy Management system is a database application that will cater the needs of storing, managing, querying and retrieving pharmacy data needs i.e. managing staff, customer, transaction, product and supplier records. The application is generated by a script and is further beautified manually through Alex app builder.

The scenario was opted because of the storage and management requirements of a pharmacy. If a manual storage system is considered, a pharmacy can be maintained through some file or .xlsx sheet but that wouldn’t be the ideal practice. Realizing this, a pharmacy which is a proper Entity made up of several components having their own attributes and operations later to be discussed in the schema, an attempt to make an almost fully-automated database application was brought into action. The application has automated the operation by adding rows or records manually through graphical user interface which is a utility itself, deleting or updating records, generate reports, view charts and performing calculations.

The schema consists of 6 entities and one bridge table. These tables are as follows:

• Staff – The staff table consists of the records of employees or simply staff in the pharmacy. The attributes include Staff ID, which is serving as a Primary key, name, salary, commission, hire date, and their job.
• Customer – The customer table consists of records of customers with attributes like customer ID (PK), name and phone number.
• Supplier – Supplier contains records of supplier supplying products to pharmacy. The attributes include Supplier ID, Company Name, supplier name, address and phone number.
• Product – Product table contains all the products the pharmacy sells or simply has as am inventory. It includes Product ID (PK), Product Name, Unit Price, Quantity, Supplier ID (FK) and Category ID (FK).
• Category – The category table contains the records of the categories the products belong to. Attributes are category ID (PK), category name and the description of category.
• Transaction – The transaction tables include records of the transactions made by customers. It’s attributes are Transaction ID (PK), Customer ID (FK), Staff ID (FK) and transaction Date.
• Transaction Details – This table serves as a bridge table between transaction and the products on which transactions are made. It’s attributes includes Transaction ID (FK), Product ID (FK), Unit Price, Quantity and Discount.

The below diagram depicts the entities (tables) and their relations. The diagram is designed using Draw.io , which is an online utility to design diagrams.

Following are the functional dependencies of attributes in each table:

The below diagram depicts how the data will flow throughout the database and which entity is doing what operation. It is to be noted that it is a level 0 DFD or simply a context diagram which would describe or give the overview of while application being modeled or analyzed.