Modeling and Normalisation

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 donors full name and address details, along with their contact details, gender, Medicare number and any other details that may be pertinent.

   ABS also needs to know about the medical condition of each donor or prospective donor. This includes the details of any surgery, existing medical conditions and any medication that is being taken.

   ABS also needs to know if the donor has been overseas in the last three months, which countries that they visited, how long they stayed in each county, whether they contracted any disease while in the country.

   Based on the answers to these questions, the ABS staff will decide if it is safe to accept a blood donation from the prospective donor. This is a decision can be made only by an ABS staff member who is a qualified Registered Nurse.

   Once the ABS staff member has decided to accept a blood donation from the donor, it is necessary to record the donor details, staff member taking the donation, ABS donation centre, and any other pertinent details in order to clearly identify each individual blood donation.

Tasks:

1. List all the business rules for the ABS ( Business rules are important because they define the conditions that the database must meet. For example, every Order must be associated with a valid Product.This prevents invalid Orders being entered into the database). (7 marks)

2. Create an ERD that describes the ABS staff and donation centres. This ERD should show:(7 marks)

    a. All entities with primary key and any foreign key attributes,
    b. The relationships between entities,
    c. The cardinality and optionality of each relationship.

3. Create an ERD that describes a blood donor, her medical condition and any medications that she takes. This ERD should show: (7 marks)
    a. All entities with primary key and any foreign key attributes,
    b. The relationships between entities,
    c. The cardinality and optionality of each relationship.

4. The donor also needs to state whether she has been overseas in the last 3 months and if so, to advise the details of that trip. Add this information to the Donor ERD created in Q3 above. This ERD should show:(9 marks)
    a. All entities with primary key and any foreign key attributes,
    b. The relationships between entities,
    c. The cardinality and optionality of each relationship.

5. Each donor can make a blood donation. Add this information to the Donor ERD created in Q3 and Q4 above. Donations must be made at an ABS Donation Centre and be taken by ABS staff. Add the ABS Staff ERD created in Q1 to the new Donor ERD. This combined ERD should show:(20 marks)

    a. All entities with all attributes identified including Primary key and any foreign key attributes,
    b. The relationships between entities,
    c. The cardinality and optionality of each relationship;
    d. Any M:N relationships are be resolved to 1:M relationships.  

 

Part B. Create an RDM (15 marks):

Tasks :

Using the ABS ERD created in Part A:

1. Identify all attributes required for each table shown in the ERD (10 marks); and

2. Create an RDM for each table in the ERD (5 marks);

 

 

Part C. Normalise the RDM to BCNF (35 marks):

 

Tasks :

Using the RDM created in Part B:

1. Create a set of Dependency Diagrams for the ABS database (15 marks);

2. Normalise the ABS tables to BCNF( 15 marks); and

3. Create a new RDM for the normalised tables (5 marks).