Q1. Design an ER diagram for a Pharmacy. The pharmacy stocks various medicines. Each medicine is uniquely identified by a Batch Number. The system maintains information about the medicine's name, price, quantity in stock, date of manufacture, and date of expiry. The pharmacy sells these medicines only to the patients who are registered with it. It stores the Patient ID, Name, Address and Phone Number for each the patients. All sales transactions are duly recorded along with the date of the transaction. A single transaction may consist of multiple medicines. Design an ER diagram for the scenario above. You should clearly list the following: Make and state suitable assumptions, if any.
- (i)) Entities and Attributes. (300 words)
- (ii)) Relationships and their Cardinality (300 words)
- (iii)) Key Constraints (Primary/Foreign Keys). (300 words)
Key Points:
- MEDICINE, PATIENT, TRANSACTION, and TRANSACTION_DETAIL are the core entities.
- Batch_Number uniquely identifies MEDICINE, Patient_ID for PATIENT, Transaction_ID for TRANSACTION.
- TRANSACTION_DETAIL is an associative entity resolving M:N between TRANSACTION and MEDICINE.
- Relationships: PATIENT (1:N) TRANSACTION, TRANSACTION (1:N) TRANSACTION_DETAIL, MEDICINE (1:N) TRANSACTION_DETAIL.
Answer: Designing an ER diagram for a pharmacy system involves identifying the core entities, their attributes, and the relationships that exist between them, along with their cardinalities and key constraints. This process is fundamental in database design, as discussed in BCS-092, to create a logical blueprint for the database. Our design considers the pharmacy's operational requirements: managing medicine stock, registering patients, and recording sales transactions. The ER model presented below pro...