Create An ERD From A Business Problem 50 Marks

Create An ERD From A Business Problem 50 Marks

Create An ERD From A Business Problem 50 Marks

Create an ERD from a business problem. (50 marks) Read the following problem and complete the tasks listed below. THE ABS Blood Bank Australian Blood Services (ABS) is a new Non-Government Organisation (NGO) that has just been set up to collect blood and blood product donations from the public. ABS plans to have donation centres in each capital city and most major regional cities throughout Australia. You have been approached by ABS to design and create a database system to track these blood donations. ABS needs to record the details of each donor.

This needs to include the donor's full name and address details, contact information, gender, Medicare number, and other relevant details. ABS also needs to know about each donor's medical condition, including previous surgeries, existing medical conditions, and medications being taken. Additionally, ABS requires information about whether the donor has traveled overseas in the last three months, which countries they visited, how long they stayed, and if they contracted any diseases while abroad. Based on these details, ABS staff will decide whether it is safe to accept a blood donation from the prospective donor.

This decision can only be made by an ABS staff member who is a qualified Registered Nurse. Once the donation is accepted, ABS staff must record the donor details, staff member taking the donation, donation centre, and other relevant information to track each blood donation accurately.

Paper For Above instruction

Part A: Business Rules

The business rules for the ABS blood bank system are derived from the provided business scenario and include the following:

  1. Each donor must have unique full name, address, contact details, gender, and Medicare number to ensure accurate identification.
  2. A donor's medical history, including surgeries, conditions, and medications, must be recorded to assess eligibility for blood donation.
  3. Travel history in the last three months, including countries visited, duration, and disease contraction, must be documented for each donor to evaluate safety for donation.
  4. Only qualified Registered Nurses can make the decision to accept or reject a donor based on the medical and travel history.
  5. Every donation must be linked to a specific donor, donation centre, and staff member who collected the donation.
  6. Each donation must be uniquely identified and associated with the corresponding donor, staff, and centre details.
  7. The system must prevent donations from donors who have recent travel or medical conditions that contraindicate blood donation.
  8. Blood donations are recorded as individual events tied to the donor, staff, and centre, maintaining data integrity and traceability.
  9. If a donor undergoes multiple donations, each donation is individually recorded but linked to the same donor record.
  10. Donation centres should be associated with relevant location details and staffed by recognised personnel.
  11. The system should validate that all foreign keys (donor ID, staff ID, centre ID) refer to existing records to maintain referential integrity.
  12. Optional fields like overseas travel details should be allowed null if the donor did not travel recently.

Part B: ERD for ABS Staff and Donation Centres

The ERD to describe the ABS staff and donation centres includes the following key entities:

  • Staff: Attributes include StaffID (PK), Name, Role, Qualification, ContactDetails, and CentreID (FK).
  • DonationCentre: Attributes include CentreID (PK), Name, Location, City, State, and ContactDetails.

Relationships:

  • Staff are associated with one DonationCentre (Many-to-One), where each staff member works at a single centre, but each centre has many staff members.

Cardinality and optionality indicate that each Staff must belong to exactly one centre, and each centre can have zero or many staff members.

Part C: ERD for Donor, Medical Conditions, and Medications

The entities involved are:

  • Donor: DonorID (PK), FullName, Address, ContactDetails, Gender, MedicareNumber.
  • MedicalCondition: ConditionID (PK), Description, DateDiagnosed, DonorID (FK).
  • Medication: MedicationID (PK), Name, Dosage, StartDate, EndDate, DonorID (FK).

Relationships:

  • One donor can have multiple medical conditions and medications; thus, there are One-to-Many (1:M) relationships between Donor and MedicalCondition, and Donor and Medication.
  • Both MedicalCondition and Medication entities have foreign keys linking back to Donor, with optional associations depending on whether the donor has any medical conditions or medications recorded.

Part D: Adding Overseas Travel Details

The following additional entities and attributes are included:

  • TravelHistory: TravelID (PK), CountryVisited, DurationDays, DiseaseContracted, TravelDate, DonorID (FK).

Relationships:

  • Each donor may have zero or more travel history records; hence, a One-to-Many relationship between Donor and TravelHistory.
  • TravelHistory is optional; if the donor did not travel abroad recently, no records are needed.

Part E: Complete Combined ERD for Donors and Donations

The comprehensive ERD combines all previous entities: Donor, MedicalCondition, Medication, TravelHistory, Staff, DonationCentre, and Donation.

  • Donation: DonationID (PK), DonationDate, BloodType, Volume, DonorID (FK), StaffID (FK), CentreID (FK).

Relationships include:

  • Each Donation is made by one Donor (Many-to-One).
  • Each Donation is collected by one Staff member (Many-to-One).
  • Each Donation occurs at one DonationCentre (Many-to-One).
  • All entities are connected with the appropriate foreign keys, with relationships ensuring data integrity and proper linkage.
  • Any M:N relationships are resolved into 1:M associations; for example, if a donor could serve multiple centres, that would be modeled accordingly, but in this scenario, most relationships are 1:M.

Part B: Relational Data Model (RDM)

1. Attributes Required for Each Table

  • Staff: StaffID (PK), Name, Role, Qualification, ContactDetails, CentreID (FK).
  • DonationCentre: CentreID (PK), Name, Location, City, State, ContactDetails.
  • Donor: DonorID (PK), FullName, Address, ContactDetails, Gender, MedicareNumber.
  • MedicalCondition: ConditionID (PK), Description, DateDiagnosed, DonorID (FK).
  • Medication: MedicationID (PK), Name, Dosage, StartDate, EndDate, DonorID (FK).
  • TravelHistory: TravelID (PK), CountryVisited, DurationDays, DiseaseContracted, TravelDate, DonorID (FK).
  • Donation: DonationID (PK), DonationDate, BloodType, Volume, DonorID (FK), StaffID (FK), CentreID (FK).

2. RDM for Each Table

Staff(StaffID: INT, Name: VARCHAR, Role: VARCHAR, Qualification: VARCHAR, ContactDetails: VARCHAR, CentreID: INT)


DonationCentre(CentreID: INT, Name: VARCHAR, Location: VARCHAR, City: VARCHAR, State: VARCHAR, ContactDetails: VARCHAR)


Donor(DonorID: INT, FullName: VARCHAR, Address: VARCHAR, ContactDetails: VARCHAR, Gender: VARCHAR, MedicareNumber: VARCHAR)


MedicalCondition(ConditionID: INT, Description: VARCHAR, DateDiagnosed: DATE, DonorID: INT)


Medication(MedicationID: INT, Name: VARCHAR, Dosage: VARCHAR, StartDate: DATE, EndDate: DATE, DonorID: INT)


TravelHistory(TravelID: INT, CountryVisited: VARCHAR, DurationDays: INT, DiseaseContracted: VARCHAR, TravelDate: DATE, DonorID: INT)


Donation(DonationID: INT, DonationDate: DATE, BloodType: VARCHAR, Volume: DECIMAL, DonorID: INT, StaffID: INT, CentreID: INT)

Part C: Normalisation to BCNF

1. Dependency Diagrams

Given the attributes and keys, dependencies primarily involve primary keys determining all other attributes. For example, in the Donor table, DonorID determines FullName, Address, etc. No partial dependencies exist, so the tables are in 2NF; further analysis confirms that all determinants are candidate keys, satisfying BCNF conditions.

2. Normalising Tables to BCNF

Each table already adheres to BCNF because all functional dependencies are based on primary keys. For example, in the MedicalCondition and Medication tables, ConditionID and MedicationID are candidate keys determining all other attributes, with no violating dependencies.

3. Revised RDM after BCNF

The final RDM remains as the attribute structure listed earlier, with all functional dependencies satisfying BCNF norms.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Modern Database Management (12th ed.). Pearson.
    • Database System Concepts (6th ed.). McGraw-Hill. Business Database Systems. Pearson. Journal of Biomedical Informatics, 113, 103608. International Journal of Data Science and Analytics, 6, 57–68. Communications of the ACM, 13(6), 377–387. Database System Concepts (7th ed.). McGraw-Hill. Health Informatics Journal, 26(2), 995–1010. Database: Principles, Programming, and Performance. Morgan Kaufmann.

Related Assignments