+1(978)310-4246 credencewriters@gmail.com
  

Description

College of Computing and Informatics
Project
Deadline: Wednesday14/04/2021@
23:59
[Total Mark for this Case Study is 5]
Student Details:
Name: ###
ID: ###
CRN: ###
Instructions:
•
•
•
•
•
•
•
•
•
•
You must submit two separate copies (one Word file and one PDF file) using this Template on Blackboard
via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets or languages other than English or any kind of
manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.
Project Scenario
Pg. 01
Project Scenario
Database System for a Blood Bank Organization
This project is about designing a database system for a Blood Bank Organization. In this
system, donors can donate blood many numbers of times. The donated bloods are stored
at clinics at different locations. Each clinic is administered by a specific employee.
The data requirements are characterized 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 Gender.
3.
Donor’s Gender is not a mandatory field.
4.
Donors must be at least 17 years of age.
5.
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.
Project Scenario
Pg. 02
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.
Note: You can make any additional reasonable assumptions for any missing details for
this system.
Question One
Pg. 03
Learning
Outcome(s):
LO 5
Develop a data
model for a
business
application.
Question One
2.5 Marks
Design an Entity-Relationship diagram for the database of Blood Bank Organization.
Question Two
Pg. 04
Learning
Outcome(s):
LO 4
Design a database
for a business
application.
Question Two
2.5 Marks
Convert your ERD into relational schemas and then apply normalization rules (1NF,
2NF, 3NF, BCNF, and 4NF) against these relational schemas.
Question Three
Pg. 05
Learning
Outcome(s):
LO 6
Create a database
application to store
and retrieve data.
Question Three
Write All SQL statements for:

creating tables with necessary constraints.
inserting data into the created tables.
Using any database (MySQL, Oracle…, etc.) and include screenshots
2.5 Marks
Question Four
Pg. 06
Learning
Outcome(s):
LO 4
Question Four
2.5 Marks
Write SQL Queries for the following:
1.
List the first and last name of all donors whose blood type is A+.
Use Structured
2.
List names and location of clinics that store a blood of type AB+.
Query Language to
3.
Find the names of donors whose age is above 30 years and have donated since
perform queries
and to perform
relational
operations.
1/1/2000.
4.
List the names of employees who are working in clinics which do not have a
blood of type A-.
Include screenshots to show all the queries statement and results for the above.

Purchase answer to see full
attachment

  
error: Content is protected !!