ER & EER Diagram Design: Step-by-Step Practical Case Studies

How to Solve ER Diagram Questions in DAVV BCA Exams

In every semester examination at Devi Ahilya Vishwavidyalaya (DAVV), a 10-Mark or 14-Mark question asks students to design a complete ER diagram from a descriptive business paragraph.

Follow this proven 4-Step Formula to score full marks:

1. Identify the Entities (Nouns): Scan the problem statement for tangible things or concepts (e.g., Student, Doctor, Account).
2. Identify the Attributes (Properties): Look for details describing each noun (e.g., Roll_No, Name, Balance). Identify which attribute is the Primary Key (underline it).
3. Identify the Relationships (Verbs): Look for actions linking the nouns together (e.g., Enrolls, Treats, Deposits).
4. Determine Cardinality and Participation: Decide if the link is 1:1, 1:N, or M:N, and whether participation is Total (double line) or Partial.

Case Study 1: University Management System (DAVV Model)

Problem Statement:

“A university has multiple departments. Each department offers multiple courses. Students enroll in courses. A faculty member belongs to one department and teaches several courses. Students may participate in multiple hobby clubs.”

Step-by-Step Breakdown:

Relationships and Cardinality:

1. DEPARTMENT to COURSE (OFFERS):
   • Cardinality: 1:N (One department offers many courses; a course belongs to only 1 department).
2. STUDENT to COURSE (ENROLLS_IN):
   • Cardinality: M:N (A student takes multiple courses; a course has multiple enrolled students).
   • Descriptive Attribute: Grade or Semester is attached to the ENROLLS_IN diamond!
3. PROFESSOR to DEPARTMENT (BELONGS_TO):
   • Cardinality: N:1 (Many professors belong to 1 department). Total participation on Professor.
4. PROFESSOR to COURSE (TEACHES):
   • Cardinality: 1:N (A professor can teach several courses; each course is taught by 1 professor).

Case Study 2: Hospital Management System

Problem Statement:

“A hospital consists of doctors, patients, and rooms. Doctors examine patients and prescribe medications. Each patient is assigned to at most one room during admission.”

Breakdown:

• Entities:
  • DOCTOR (Doc_ID, Name, Specialization, Contact)
  • PATIENT (Patient_ID, Name, Age, Disease, Admission_Date)
  • ROOM (Room_No, Room_Type, Daily_Charge)
  • MEDICINE (Med_ID, Med_Name, Manufacturer, Price)
• Relationships:
  • TREATS: DOCTOR (1) ──<TREATS>── (N) PATIENT.
  • PRESCRIBES: Ternary or binary relationship with attribute Dosage and Date.
  • ASSIGNED_TO: PATIENT (N) ──<ASSIGNED_TO>── (1) ROOM.

Case Study 3: Banking Database System (with EER Specialization)

Problem Statement:

“A bank maintains customer details and multiple accounts. Accounts are divided into Savings Accounts (with interest rate) and Current Accounts (with overdraft limit). Customers can take loans.”

• Entities:
  • CUSTOMER (Customer_ID, Name, Phone, Aadhar_No)
  • ACCOUNT (Superclass: Account_No, Balance)
    • SAVINGS_ACCOUNT (Subclass: Interest_Rate)
    • CURRENT_ACCOUNT (Subclass: Overdraft_Limit)
  • LOAN (Loan_No, Amount, Loan_Type)
• Relationships:
  • CUSTOMER (M) ──<HOLDS>── (N) ACCOUNT (A joint account can have 2 customers; 1 customer can hold savings & current accounts).
  • CUSTOMER (1) ──<APPLIES_FOR>── (N) LOAN.

Summary Checklist for University Exam ER Diagrams

To ensure maximum marks from the evaluator:

1. Always underline the primary key attributes (e.g., <u>Roll_No</u>).
2. Clearly label cardinality ratios (1, N, M) next to the connecting lines.
3. Use a double rectangle for weak entities and a double diamond for their identifying relationships.
4. If an attribute can have multiple values (like mobile numbers), use a double ellipse.
5. If an attribute is derived (like age calculated from DOB), use a dashed ellipse.
6. Write a brief 4-line textual description explaining your entities and relationships alongside the diagram!

💡 DAVV Exam Preparation Tips

10-Mark / 14-Mark Question: “Design a complete Entity-Relationship (ER) diagram for a University Management System. Clearly specify all entities, attributes, primary keys, relationships, and cardinality ratios.”

• Follow the 4-step framework outlined above.
• Identify at least 4 strong entities (STUDENT, PROFESSOR, DEPARTMENT, COURSE).
• Show at least one M:N relationship with a descriptive attribute (Grade in ENROLLS_IN).
• State the cardinalities explicitly in your written explanation.