Database System for a Blood Bank Organization

Consider a database system for a Blood Bank Organization. The data requirements are summarized as follows:

1. Donors:

• Donors are tracked by a unique donor ID.
• Personal information includes first name, last name, phone number, Date of Birth and sex.
• The combination of the Donors first and last name must be unique.
• Donor's sex is not a mandatory field.
• Donors must be at least 17 years of age.
• Donors can donate at any clinic any number of times.

2. Blood:

• Blood is tracked by a unique ID.
• BloodType is how the blood is categorized: (A+/A-, B+/B-, AB+/AB-, O+/O-, N/A).
• N/A refers to blood that has not been processed yet.
• Status refers to the state of the blood: (pure, N/A, impure).
• N/A refers to blood that has not been processed yet.
• Donor_id is a FK which references the ID of the Donor.
• Blood specimens must have a donor.
• Clinic_id is a FK which references the ID of the Clinic that stores the blood.
• Blood specimens must be stored at one clinic.
• DonateDate is the date the blood sample was received.

3. Clinic:

• Clinics are tracked by unique clinic ID.
• Each clinic has a name and location.
• The combination of the clinics name and location must be unique.

4. Employees:

• Employees are tracked by unique Employee ID.
• Clinic_id is a FK which references the Clinic ID which they work at.
• Employees must work at a single Clinic.
• Personal information includes Fname, Lname and sex.
• The combination of an employees first and last name must be unique.

SQL Queries:

• List the first and last name of all donors whose blood type is O+.
• List names and location of clinics that store a blood of type B+.
• Find the names of donors whose age is above 30 years and have donated since 1/1/2000.
• List the names of employees who are working in clinics which do not have a blood of type A-.