ER Diagrams on five topics

A data model is used to design a data warehouse. It represents the underlying structure of a database. 

An ER Diagram in Database Management Systems is a high level data model used to construct a database in the form of tables. It is a collection of entities and relationships. An ER Diagram should be created in such a way that is understandable to even non-technical users.

Here, we will see 5 different ER Diagrams.

 

1. ER Diagram for a Nonprofit Membership Management System:

ER Diagram:

This ER diagram captures the basic structure and relationships for a Nonprofit Membership Management System.

Description:

Entities:

1. Member: Represents individuals who are members of the nonprofit organization.

   - Attributes: MemberID (Primary Key), Name, Email, Phone, Address, Profession, Date joined

2. Membership Type: Represents different types of memberships offered by the nonprofit organization.

   - Attributes: TypeID (Primary Key), Name, Description, Fee

3. Payment: Represents payments made by members for their membership.

   - Attributes: PaymentID (Primary Key), MemberID (Foreign Key), Amount, PaymentDate, PaymentType

4. Donations: Represents donations made by members for their membership. It is optional.

   - Attributes: DonationID (Primary Key), MemberID (Foreign Key), DAmount, DDate, DType

5. Registration: Represents members' registrations for events.

   - Attributes: RegistrationID (Primary Key), MemberID (Foreign Key), EventID (Foreign Key), RegistrationDate

6. Event: Represents events organized by the nonprofit organization.

   - Attributes: EventID (Primary Key),ManagerID(Foreign Key), Name, Description, EventDate, Location

7. Manager: Represents the managers managing events organized by the nonprofit organization.

   - Attributes: ManagerID (Primary Key),Manager Name, Contact Details

Relationships:

1. Subscribes to: Connects Member to Membership Type, indicating which membership type each member subscribes to. (Many-to-One)

2. Makes: Connects Member to Payment, indicating payments made by each member for their membership. (One-to-One)

3. Donates: Connects Member to Donations, indicating donations made by the members. It is optional. (One-to-One)

4. Makes: Connects Member to Registration, indicating registrations made by members. (Many-to-one)

5. For: Connects Registration to Events, indicating registrations made by members to attend events. (One-to-Many)

6. Managed by: Connects Events to Manager, indicating which manager manages which events. (One-to-Many)

2. ER Diagram for an Agricultural Supply Chain Management System:

ER Diagram:

This ER diagram captures the basic structure and relationships for an Agricultural Supply Chain Management System .

Description:

Entities:

1. Supplier: Represents entities or companies that supply agricultural products. 

-Attributes: SupplierID (Primary Key), Name, ContactInfo, Category (type of products supplied)

2. Producer: Represents individuals or entities engaged in agricultural production. 

Attributes: ProducerID (Primary Key), ProducerName, ContactInfo, ProducerType, FarmSize, CropType

3. Product: Represents agricultural products available in the supply chain. 

-Attributes: ProductID (Primary Key), Name, Description, Price

4. Warehouse: Represents storage facilities where agricultural products are stored. 

Attributes: WarehouseID (Primary Key), Name, Location, Capacity, Temperature, Humidity

5. Agro-Processing Unit: Represents the Facilities or operations where raw agricultural products are transformed into processed(cleaning, sorting, milling, grinding, canning, freezing, packaging) goods. 

Attributes: UnitID (Primary Key), UnitName, Location, ProcessType

6. Transport: Represents transportation involved in the agricultural supply chain. 

Attributes: TransportID (Primary Key), Vehicle, Driver, Capacity

7. Market: Represents wholesale or retail markets where agricultural products are sold. 

 Attributes: MarketID (Primary Key),MarketName, Location, MarketType.

8. Customer: Represents customers who purchase agricultural products. 

Attributes: CustomerID (Primary Key), Name, Order.

Relations:

1. Supplies: Connects Supplier to Product, indicating the association between suppliers and the products they supply. (One-to-Many)

2. Produces: Connects Producer to Product, indicating the relationship between producers and the products they produce. (One-to-Many)

3. Stored at: Connects Product to Warehouse, indicating how products are stored in warehouses. (Many-to-One)

4. Processed at: Connects Product to Agro-Processing Unit, indicating how products are processed in Agro-Processing Unit. (Many-to-One)

5. Loads: Connects Warehouse to Transport, indicating the loading of products from warehouses to transport system. (One-to-Many)

6. Delivers:Connects Transport to Market, indicating the transportation of products from suppliers to markets. (One-to-Many)

7. Buys from:Connects Customer to Market, indicating customers buying products from markets. (Many-to-One)

3. ER Diagram for a Pharmacy Inventory Management System:

ER Diagram:

This ER diagram captures the basic structure and relationships for a Pharmacy Inventory Management System System .

Description:

Entities:

1. Pharmacy: Represents the pharmacies using the inventory management system. 

-Attributes: PharmacyID (Primary Key), Location, Name, Open Hours

2. Medicine: Represents the medicines that the pharmacy buys, stocks and sells. 

-Attributes: Product_ID (Primary Key), Product_name, manufacturer, mfg date, exp date, Price, Quantity

3. Supplier: Represents entities or companies that supply medical products. 

-Attributes: SupplierID (Primary Key), Location (Composite attribute with components - State, City and Multivalued attribute), ContactInfo (multivalued attribute), Quoted price, Supplier Name

4. Patient: Represents the patients that buy medicine from the pharmacy. 

-Attributes: patient_ID (Primary Key), patient_name, contact details

5. Doctor: Represents the doctor that the patient may or may not have consulted. 

-Attributes: Doctor_ID (Primary Key), Hospital_ID (Foreign Key), Contact_no (multivalued attribute), doctor_name, Qualifications, Specialities, contact details 

6. Hospital: Represents the hospital that the employs the doctor consulted by the patient. 

-Attributes: hospital_ID (Primary Key), hospital_name, contact details, location

Relations:

1. Stocks: Connects Pharmacy to Product, indicating the association between the pharmacy and the products they sell. (One-to-Many)

2. Ordered from: Connects Product to Supplier, indicating the association between the products and the suppliers that manufacture them. (Many-to-One)

3. Bought by: Connects Product to Patient, indicating the association between the patient and the products they buy from the pharmacy. (Many-to-One)

4. Consults: Connects Patient to Doctor, indicating the association between the patient and the doctor that they may have consulted to buy medicines from the pharmacy. (Optional, One-to-One)

5. Linked with: Connects Pharmacy to Hospital, indicating that the pharmacy may or may not be affiliated with a hospital. (Optional, One-to-One)

6. Employed by: Connects Doctor to Hospital, indicating that the doctor may or may not be employed at a hospital. (Optional, Many-to-One)

7. Works at: Connects Employee to Pharmacy, indicating that the employee that work at the pharmacy. (Many-to-One)

-Attributes: work days and shift hours

4. ER Diagram for a Budget Tracking System:

ER Diagram:

This ER diagram captures the basic structure and relationships for a Budget Tracking System.

Description:

Entities:

1. User: Represents the individual/person using the budget tracking system. 

-Attributes: User_ID (Primary Key), Contact info (multivalued attribute), Email_ID, Name

2. Income: Represents the total income earned by the user. 

-Attributes: Income_No (Primary Key), Amount, Description

3. Notifications/Alerts: Represents the notifications that the user sets up and will receive if they ever went over budget or for other reasons. (weak entity)

-Attributes: Notification_type

4. Bank Account: Represents the bank accounts held by the user at various banks. 

-Attributes: Acc_No (Primary Key), Loan_ID (Foreign Key), Acc_Type, Acc_Balance 

5. Bank: Represents the bank where the user holds accounts. 

-Attributes: Bank_ID (Primary Key), Bank_name, Branch_name, Address 

6. Loan: Represents the loans that the user may have taken from the bank. 

-Attributes: Loan_ID (Primary Key), Loan_Amount, Interest_Rate, Purpose, Term_Period (Composite attribute with components - start date and end date)

7. Expenses: Represents the expenses that the user spends on. Every attribute of this entity is optional because expenses varies from one person to another.

-Attributes: Expense_ID (Primary Key), Fixed_Expenses (Composite attribute with components - loan repayment, rent/mortgage, Education, Tax (Composite attribute with components - Road_tax, Income_tax, Property_tax, Sales_tax and others) Insurance (Composite attribute with components - Health, Life, Auto, Home and others) Bills (Composite attribute with components - Phone, Gas, Water, Internet, Electricit and others ), Variable_Expenses (Composite attribute with components - Personal, Groceries, Petrol/Diesel, Credit Card payments, Entertainment and others)

8. Savings and Investments: Represents the savings and investments made by the user. 

-Attributes: Savings_ID (Primary Key), Emergency funds, Retirement Savings, Stocks / Bonds / Mutual Funds

9. Goals: Represents the goals that the user plans on achieving. (weak entity)

-Attributes: Vacation, Home / Land, Education, Debt Payoffs

Relations:

1. Earns: Connects User to Income, indicating the total income earned by the user. (One-to-Many)

2. Receives: Connects User to Notifications/Alerts, indicating the notifications that the user sets up and will receive if they ever went over budget or for other reasons. (One-to-Many) (Identifying relationship)

3. Holds: Connects User to Bank Accounts, indicating the bank accounts held by the user at various banks. (One-to-Many)

4. From: Connects Bank Accounts to Bank, indicating the bank where the user holds accounts . (Many-to-Many)

5. Maintains: Connects Bank Accounts to Loans, indicating the loans that the user may have taken from the bank using their accounts. (Optional, One-to-Many)

6. Spends on: Connects User to Expenses, indicating the expenses that the user spends on. (One-to-Many)

7. Saves: Connects User to Savings and Investments, indicating the savings and investments made by the user. (One-to-One)

8. For: Connects Savings and Investments to Goals, indicating the goals that the user plans on achieving. (One-to-One) (Optional and Identifying relationship)

5. ER Diagram for a Nutrition and Meal Planning System:

ER Diagram:

This ER diagram captures the basic structure and relationships for a Nutrition and Meal Planning System.

Description:

Entities:

1. User: Represents the individual/person using the Nutrition and Meal Planning system. 

-Attributes: User_ID (Primary Key), Gender , Email_ID, User_Name

2. Goal: Represents the goals set by the user. 

-Attributes: Goal_ID (Primary Key), Target_weight ,Target_Month, Calories needed, Nutrients needed

 3. Dietary info: Represents the dietary info offered by the user for better meal planning. 

-Attributes: Dietary_ID (Primary Key), age, weight, height, deficient nutrients, deficient calories, activity rate, Food Preferences - vegetarian or non-vegetarian, medical info (Composite attribute with components - allergies (multivalued attribute), health issues (Composite attribute with components - past (multivalued attribute), current(multivalued attribute)))

4. Dietitian: Represents the dietitian suggested to the user for consultations. 

-Attributes: Dietitian_ID (Primary Key), Dietitian_Name, Certifications, Contact_info

5. Meal Plan: Represents the meal plan suggested to the user to achieve their goals after taking into consideration the dietary info and dietitian consults. 

-Attributes: Meal_ID (Primary Key), Goal_ID (Foreign Key), User_ID (Foreign Key), Nutrition_ID (Foreign Key), Description, Calories

6. Mealtime: Represents the mealtimes suggested to the user. 

-Attributes: Mealtime_ID (Primary Key), Breakfast, Lunch, Tea, Dinner, In_Between (Optional, Multivalued)

7. Nutrient Content: Represents the nutrients and their content in the meals suggested to the user. 

-Attributes: Nutrient_ID (Primary Key), Vitamins, Proteins, Iron, Potassium, Fat, Calcium, Fibers, Sugars, Cholesterol, Carbohydrates

Relations:

1. Selects: Connects User to Goal, indicating what the goals set by the which user. (One-to-One)

2. Updates: Connects User to dietary info, indicating what dietary info is offered by the user. (Mandatory, One-to-One)

3. Can consult: Connects User to Dietitian, indicating what dietitian info is suggested to the user for consultation purposes. (Optional, One-to-One)

4. Is suggested: Connects User to Meal Plan, indicating what meal plans are suggested to the user. (One-to-Many)

5. Includes: Connects Meal Plan to Mealtimes, indicating what mealtimes are suggested for the meal plans suggested to the user. (One-to-One)

6. Contains: Connects Meal Plan to Nutrients, indicating what nutrient content is present in the meal plans suggested to the user. (One-to-One)