Solved by verified expert:HiI have small project as per the attached description.NOTE: There is an example for similar project, it same idea and requirment.Thank you.
20180326155637project_2_edited.docx
project_example.docx
poject_requirments.pdf
Unformatted Attachment Preview
Project 2: Blood Bank Organization
ER Diagram:
Schema before/after Normalization:
I have developed a normalized form of database from the start by using reference
tables to store repeated varchar values. I have added primary key constraint and
foreign key constraint to make it normalized and provide consistency.
You can see the detailed schema in above ER Diagram.
Complete SQL Script:
drop table if exists blood_activity;
drop table if exists employees;
drop table if exists clinic;
drop table if exists donor;
drop table if exists blood_ref;
drop table if exists status_ref;
drop table if exists sex_ref;
#drop table status_ref;
create
table
status_ref(
status_id numeric,
status_desription varchar(20),
primary key(status_id)
);
#drop table if exists sex_ref;
create
table
sex_ref(
sex_id numeric,
sex_description varchar(10),
primary key(sex_id)
);
insert
into
sex_ref
values(
1,
‘Male’
),
(
2,
‘Female’
);
insert
into
status_ref
values(
1,
‘pure’
),
(
2,
‘N/A’
),
(
3,
‘impure’
);
#drop table if exists blood_ref;
create
table
blood_ref(
blood_type_id numeric,
blood_type_descrption varchar(5),
primary key(blood_type_id)
);
insert
into
blood_ref
values(
1,
‘A+’
),
(
2,
‘A-‘
),
(
3,
‘B+’
),
(
4,
‘B-‘
),
(
5,
‘O+’
),
(
6,
‘O-‘
),
(
7,
‘AB+’
),
(
8,
‘AB-‘
),
(
9,
‘N/A’
);
#drop table if exists donor;
create
table
donor(
donor_id numeric,
first_name varchar(20),
last_name varchar(20),
phone_number varchar(20),
blood_type_id numeric,
date_of_birth date,
sex_id numeric,
primary key(donor_id),
foreign key(blood_type_id) references blood_ref(blood_type_id),
foreign key(sex_id) references sex_ref(sex_id),
unique key(
first_name,
last_name
)
);
#drop trigger check_donor_age;
CREATE TRIGGER check_donor_age BEFORE INSERT
ON donor
FOR EACH row
begin
DECLARE dummy, age_flag INT;
IF Datediff(Curdate(), new.date_of_birth) / 365.25 < 17 THEN
SET age_flag = 1;
end IF;
IF age_flag = 1 THEN
SELECT Concat(
'Cannot Insert This Donor Entry because his age is less than 17'
)
INTO
dummy
FROM
information_schema.tables;
end IF;
end;
insert
into
donor
values(
1,
'Ahmad',
'Ali',
'0987654321',
1,
'1992-01-01',
1
),
(
2,
'Fatimah',
'Hamad',
'0987654321',
5,
'1994-01-01',
2
),
(
3,
'Khaled',
'Hassan',
'0987654321',
3,
'1974-01-01',
1
),
(
4,
'Hind',
'Saleh',
'0987654321',
5,
'1988-01-01',
2
),
(
5,
'Faisal',
'Badr',
'0987654321',
3,
'1995-01-01',
1
),
(
6,
'Aishah',
'Salem',
'0987654321',
1,
'1965-01-01',
2
),
(
7,
'Turki',
'Waleed',
'0987654321',
2,
'1995-01-01',
1
);
#drop table if exists clinic;
create
table
clinic(
clinic_id numeric,
name varchar(50),
location varchar(100),
primary key(clinic_id),
unique key(
name,
location
)
);
insert
into
clinic
values(
1,
'Main Clinic',
'The Blood Bank'
),
(
2,
'Secondary Clinic',
'The main hospital'
),
(
3,
'ER Clinic',
'Emergency Building'
);
#drop table if exists employees;
create
table
employees(
employee_id numeric,
clinic_id numeric,
first_name varchar(20),
last_name varchar(20),
sex_id numeric,
primary key(employee_id),
unique key(
first_name,
last_name
),
foreign key(clinic_id) references clinic(clinic_id),
foreign key(sex_id) references sex_ref(sex_id)
);
insert
into
employees
values(
1,
1,
'Sultan',
'Fahad',
1
),
(
2,
1,
'Norah',
'Ahmad',
2
),
(
3,
1,
'Majed',
'Fahad',
1
),
(
4,
2,
'Sarah',
'Faisal',
2
),
(
5,
2,
'Ibrahim',
'Saif',
1
),
(
6,
2,
'Hanaa',
'Mohamad',
2
),
(
7,
3,
'Omar',
'Saleh',
1
),
(
8,
3,
'Alaa',
'Omar',
2
);
#drop table blood_activity;
create
table
blood_activity(
activity_id numeric,
status_id numeric,
donor_id numeric,
clinic_id numeric,
donate_date date,
primary key(activity_id),
foreign key(donor_id) references donor(donor_id),
foreign key(clinic_id) references clinic(clinic_id),
foreign key(status_id) references status_ref(status_id)
);
insert
into
blood_activity
values(
1,
1,
1,
1,
'2011-01-01'
),
(
2,
2,
2,
2,
'1980-01-01'
),
(
3,
3,
3,
3,
'2007-01-01'
),
(
4,
1,
4,
1,
'2001-01-01'
),
(
5,
2,
5,
2,
'1990-01-01'
),
(
6,
1,
6,
1,
'2017-01-01'
),
(
7,
1,
7,
1,
'2017-01-01'
);
select
d.first_name,
d.last_name
from
donor d inner join blood_ref f on
d.blood_type_id = f.blood_type_id
where
f.blood_type_descrption = 'O+';
select
c.name,
c.location
from
clinic c inner join blood_activity b on
c.clinic_id = b.clinic_id inner join donor f on
b.donor_id = f.donor_id inner join blood_ref e on
e.blood_type_id = f.blood_type_id
where
e.blood_type_descrption = 'B+';
select
d.first_name,
d.last_name
from
blood_activity b inner join donor d on
b.donor_id = d.donor_id
where
datediff(
curdate(),
d.date_of_birth
)/ 365.25 > 30
and b.donate_date > ‘2000-01-01’;
select
e.first_name,
e.last_name
from
employees e
where
clinic_id not in(
select
b.clinic_id
from
blood_activity b inner join donor d on
d.donor_id = b.donor_id inner join blood_ref f on
f.blood_type_id = d.blood_type_id
where
f.blood_type_descrption = ‘A-‘
);
Each table’s data after this script:
Queries Results:
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 who their 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-.
Project 2: Blood Bank Organization
ER Diagram:
Schema before/after Normalization:
I have developed a normalized form of database from the start by using reference
tables to store repeated varchar values. I have added primary key constraint and
foreign key constraint to make it normalized and provide consistency.
You can see the detailed schema in above ER Diagram.
Complete SQL Script:
drop table if exists blood_activity;
drop table if exists employees;
drop table if exists clinic;
drop table if exists donor;
drop table if exists blood_ref;
drop table if exists status_ref;
drop table if exists sex_ref;
#drop table status_ref;
create
table
status_ref(
status_id numeric,
status_desription varchar(20),
primary key(status_id)
);
#drop table if exists sex_ref;
create
table
sex_ref(
sex_id numeric,
sex_description varchar(10),
primary key(sex_id)
);
insert
into
sex_ref
values(
1,
‘Male’
),
(
2,
‘Female’
);
insert
into
status_ref
values(
1,
‘pure’
),
(
2,
‘N/A’
),
(
3,
‘impure’
);
#drop table if exists blood_ref;
create
table
blood_ref(
blood_type_id numeric,
blood_type_descrption varchar(5),
primary key(blood_type_id)
);
insert
into
blood_ref
values(
1,
‘A+’
),
(
2,
‘A-‘
),
(
3,
‘B+’
),
(
4,
‘B-‘
),
(
5,
‘O+’
),
(
6,
‘O-‘
),
(
7,
‘AB+’
),
(
8,
‘AB-‘
),
(
9,
‘N/A’
);
#drop table if exists donor;
create
table
donor(
donor_id numeric,
first_name varchar(20),
last_name varchar(20),
phone_number varchar(20),
blood_type_id numeric,
date_of_birth date,
sex_id numeric,
primary key(donor_id),
foreign key(blood_type_id) references blood_ref(blood_type_id),
foreign key(sex_id) references sex_ref(sex_id),
unique key(
first_name,
last_name
)
);
#drop trigger check_donor_age;
CREATE TRIGGER check_donor_age BEFORE INSERT
ON donor
FOR EACH row
begin
DECLARE dummy, age_flag INT;
IF Datediff(Curdate(), new.date_of_birth) / 365.25 < 17 THEN
SET age_flag = 1;
end IF;
IF age_flag = 1 THEN
SELECT Concat(
'Cannot Insert This Donor Entry because his age is less than 17'
)
INTO
dummy
FROM
information_schema.tables;
end IF;
end;
insert
into
donor
values(
1,
'Ahmad',
'Ali',
'0987654321',
1,
'1992-01-01',
1
),
(
2,
'Fatimah',
'Hamad',
'0987654321',
5,
'1994-01-01',
2
),
(
3,
'Khaled',
'Hassan',
'0987654321',
3,
'1974-01-01',
1
),
(
4,
'Hind',
'Saleh',
'0987654321',
5,
'1988-01-01',
2
),
(
5,
'Faisal',
'Badr',
'0987654321',
3,
'1995-01-01',
1
),
(
6,
'Aishah',
'Salem',
'0987654321',
1,
'1965-01-01',
2
),
(
7,
'Turki',
'Waleed',
'0987654321',
2,
'1995-01-01',
1
);
#drop table if exists clinic;
create
table
clinic(
clinic_id numeric,
name varchar(50),
location varchar(100),
primary key(clinic_id),
unique key(
name,
location
)
);
insert
into
clinic
values(
1,
'Main Clinic',
'The Blood Bank'
),
(
2,
'Secondary Clinic',
'The main hospital'
),
(
3,
'ER Clinic',
'Emergency Building'
);
#drop table if exists employees;
create
table
employees(
employee_id numeric,
clinic_id numeric,
first_name varchar(20),
last_name varchar(20),
sex_id numeric,
primary key(employee_id),
unique key(
first_name,
last_name
),
foreign key(clinic_id) references clinic(clinic_id),
foreign key(sex_id) references sex_ref(sex_id)
);
insert
into
employees
values(
1,
1,
'Sultan',
'Fahad',
1
),
(
2,
1,
'Norah',
'Ahmad',
2
),
(
3,
1,
'Majed',
'Fahad',
1
),
(
4,
2,
'Sarah',
'Faisal',
2
),
(
5,
2,
'Ibrahim',
'Saif',
1
),
(
6,
2,
'Hanaa',
'Mohamad',
2
),
(
7,
3,
'Omar',
'Saleh',
1
),
(
8,
3,
'Alaa',
'Omar',
2
);
#drop table blood_activity;
create
table
blood_activity(
activity_id numeric,
status_id numeric,
donor_id numeric,
clinic_id numeric,
donate_date date,
primary key(activity_id),
foreign key(donor_id) references donor(donor_id),
foreign key(clinic_id) references clinic(clinic_id),
foreign key(status_id) references status_ref(status_id)
);
insert
into
blood_activity
values(
1,
1,
1,
1,
'2011-01-01'
),
(
2,
2,
2,
2,
'1980-01-01'
),
(
3,
3,
3,
3,
'2007-01-01'
),
(
4,
1,
4,
1,
'2001-01-01'
),
(
5,
2,
5,
2,
'1990-01-01'
),
(
6,
1,
6,
1,
'2017-01-01'
),
(
7,
1,
7,
1,
'2017-01-01'
);
select
d.first_name,
d.last_name
from
donor d inner join blood_ref f on
d.blood_type_id = f.blood_type_id
where
f.blood_type_descrption = 'O+';
select
c.name,
c.location
from
clinic c inner join blood_activity b on
c.clinic_id = b.clinic_id inner join donor f on
b.donor_id = f.donor_id inner join blood_ref e on
e.blood_type_id = f.blood_type_id
where
e.blood_type_descrption = 'B+';
select
d.first_name,
d.last_name
from
blood_activity b inner join donor d on
b.donor_id = d.donor_id
where
datediff(
curdate(),
d.date_of_birth
)/ 365.25 > 30
and b.donate_date > ‘2000-01-01’;
select
e.first_name,
e.last_name
from
employees e
where
clinic_id not in(
select
b.clinic_id
from
blood_activity b inner join donor d on
d.donor_id = b.donor_id inner join blood_ref f on
f.blood_type_id = d.blood_type_id
where
f.blood_type_descrption = ‘A-‘
);
Each table’s data after this script:
Queries Results:
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 who their 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-.
I. I. Instructions:
1.This project will evaluated 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.
2. Each student has to submit one report about his/her chosen Project containing the following:
a. ER Diagram.
b. All schemas before and after normalization.
c. All SQL statements of:
creating tables
inserting data in tables
queries.
3. Screenshots from MySQL (or any other software you use) of all the tables after population and
queries results.
4. Use Times New Roman font for all your answers.
Football championship calendar
This project supposed to develop a database system for football championship calendar.
The system contains information about teams, stadiums, referees, and matches. A match is
composed by local team, visitor team, date of match, referee and stadium and the score.
You should design the system to contain entities and attributes to store data that makes the
system able manage the championship and to organize the different matches. Note that the
winner has 3 points and the loser 0 point. In draw case, 1 point for each team. The referee is
identified by his ID. The team is identified by his name.
SQL Queries:
1.
List the matches of the current day.
2.
List the matches with score of the last journey.
3.
List the winners for each journey.
4.
Insert the matches of the next journey and the date.
5.
List the available stadium with the capacity.
6.
List the championship standings.
…
Purchase answer to see full
attachment
