Assignment: Database System for a Blood Bank Organization
A. In this file, you will find a list of database project ideas. You must choose one of them to design and implement.
B. This project, will be distributed as in the following:
a. Design the database, following an ER model
b. Normalize the tables (each table should be in 3NF at least)
c. Use MySQL or any other database to create the normalized tables and populate your tables with at least 5 rows.
d. Execute the sample requested queries.
Containing the following:
e. ER Diagram.
f. All schemas before and after normalization.
g. All SQL statements of:
- creating tables
- inserting data in tables
- queries.
C. Screenshots from MySQL (or any other software you use) of all the tables after population and queries results.
D. You are advised to make your work clear and well-presented, marks may be reduced for poor presentation. This includes filling your information on the cover page.
E. You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
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:
1. Donors are tracked by a unique donor ID.
2. Personal information includes first name, last name, phone number, Date of Birth and sex.
3. The combination of the Donor's first and last name must be unique.
4. Donor's sex is not a mandatory field.
5. Donors must be at least 17 years of age.
6. Donors can donate at any clinic any number of times.
2. Blood:
1. Blood is tracked by a unique ID.
2. BloodType is how the blood is categorized: (A+/A-, B+/B-, AB+/AB-, O+/O-, N/A).
3. N/A refers to blood that has not been processed yet.
4. Status refers to the state of the blood: (pure, N/A, impure).
5. N/A refers to blood that has not been processed yet.
6. Donor_id is a FK which references the ID of the Donor.
7. Blood specimens must have a donor.
8. Clinic_id is a FK which references the ID of the Clinic that stores the blood.
9. Blood specimens must be stored at one clinic.
10. DonateDate is the date the blood sample was received.
3. Clinic:
1. Clinics are tracked by unique clinic ID.
2. Each clinic has a name and location.
3. The combination of the clinic's name and location must be unique.
4. Employees:
1. Employees are tracked by unique Employee ID.
2. Clinic_id is a FK which references the Clinic ID which they work at.
3. Employees must work at a single Clinic.
4. Personal information includes Fname, Lname and sex.
5. The combination of an employee's first and last name must be unique.
Requirement:
A. Design & ER Model
B. Normalize the Tables (in 3NF at least)
C. Create the Normalized Tables and Populate them with at least 5 Rows
D. Write the sample requested Queries & Execute them SQL Queries:
1. List the first and last name of all donors whose blood type is O+.
2. List names and location of clinics that store a blood of type B+.
3. Find the names of donors whose age is above 30 years and have donated since 1/1/2000.
4. List the names of employees who are working in clinics which do not have a blood of type A-.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.