IMAT5103 Database Design and Implementation: EER Modeling & SQL

This document presents a comprehensive solution for a database design and implementation assignment, focusing on a blood donation system. The solution begins with a scenario description, outlining the system's functionalities for administrators and volunteers, including data management for donors, blood samples, testing, and storage. It then provides an Enhanced Entity Relationship (EER) diagram illustrating the tables, attributes, and relationships. The logical design, table creation, and indexing are detailed, along with data population examples. Finally, the solution includes various SQL queries for data retrieval and manipulation, such as selecting male donors, joining tables, counting test results, ordering data, and using the LIKE operator. References to relevant database systems literature are also provided. This document serves as a valuable resource for students studying database design, particularly those working on similar assignments involving EER modeling and SQL implementation.

Running head: DATABASE
DATABASE SYSTEM AND DESIGN
Name of the Student
Name of the University
Author Note

1DATABASE
Table of Contents
Solution 1.1......................................................................................................................................2
Solution 1.2......................................................................................................................................3
Solution 2.1......................................................................................................................................4
Logical Design.............................................................................................................................4
Solution 2.2......................................................................................................................................4
Table Creation.............................................................................................................................4
Solution 2.3......................................................................................................................................5
Index............................................................................................................................................5
Solution 2.4......................................................................................................................................6
Data Population...........................................................................................................................6
Solution 2.5......................................................................................................................................8
SQL Query...................................................................................................................................8
References......................................................................................................................................11

2DATABASE
Solution 1.1
A scenario of a blood donation system: The hospital management decided to develop a
digital system for their blood donation system. The new system mainly performs the following
functionality.
There are two users who are going to use the new system. There are two sides of blood
donation system. They are admin and the volunteers who will donate blood. The system will
maintain the volunteers accounts with their personal details such as name, phone no, email id,
address and obviously their blood group.
After collecting the blood they are packed and sent for testing. Sometime blood samples
have some diseases, infections, viruses. Therefore they are sent for testing. In testing center the
blood samples are tested properly. After that the positive samples are sent to blood bank. The
sample that has some diseases or viruses, they are rejected and not sent to the blood bank.
After the collection of blood the packets of bloods are sent to the blood bank they are
stored according to their blood groups and their characteristics.
As per requirements hospitals requests blood from blood bank with respected blood
group. If possible then the blood bank sent blood packets to the hospitals. After that patients are
provided the blood.

3DATABASE
Solution 1.2
Enhanced Entity Relationship Diagram is the conceptual design of the design. It represents the
tables and their attributes. The relationships among the tables are also discussed. Donor donates
blood after that the blood sample are sent for testing. After positive testing, the blood is sent to
blood bank. Blood bank stores the blood. Hospitals requests blood from blood bank and after that
they are sent to the hospitals.

4DATABASE
Solution 2.1
Logical Design
Logical data model represents the logical view of the database. It represents the foreign key and
relation among the tables.
Solution 2.2
Table Creation
Create Donor Table
create table P2512359Donor (DonorId int, Name varchar(20), Address varchar(20), Phone
varchar2(20), Sex varchar(20), Age int, primary key(DonorId));
Create Blood Table

5DATABASE
create table P2512359Blood (Id int, DonorId int, BloodGroup varchar(20), primary key(Id),
foreign key(DonorId) references P2512359Donor(DonorId));
Create BloodBank Table
create table P2512359BloodBank(BankId int, Id int, Bloodtype varchar(20), primary
key(BankId), foreign key(Id) references P2512359Blood(Id));
Create Test Table
create table P2512359Test(TestId int, Id int, Status varchar(20), primary key(TestId),Foreign
Key(Id) references P2512359Blood(Id));
Create Hospital Table
Create table P2512359Hospital (HospitalId number constraint hospital_pk primary key, BankId
number, HospitalName varchar2(20), HospitalPhone varchar2(20), HospitalAddress
varchar2(20), constraint P2512359hospital_fk foreign key(BankId) references
P2512359bloodbank(bankid));
Solution 2.3
Index
Index is used to search data quickly without searching every row in database. It is used to
increase the speed of data retrieval from the database table every time a database is
accessed. Two types of indexes are there in database. They are clustered and non-
clustered. For the database the following created indexes are Name in P2512359Donor
table, Hospital on P2512359Hospital table, Phone number in P2512359Donor table and
BloodGroup on P2512359Blood table.
There are some reasons behind the making the keys as index. As it is known,
index can be used to retrieve data more smoothly and fast from a table.

6DATABASE
 create index Name on P2512359Donor(Name);
Name in customer table can be treated as index because in most of the cases donor names
are unique and it will be easy to retrieve data from the table.
 create index HospitalName on P2512359Hospital(HospitalName);
HospitalName is also much unique and can help to retrieve data from table quite
smoothly.
 create index Phone on P2512359Donor(Phone);
As well as name Donor’s phone number is also unique in mosgt of the cases. It is also
considered as index though it also can help to find data from table
 create index BloodGroup on P2512359Blood(BloodGroup);
BloodGroup is not same in all cases but though can be treated as index. Data can be
retrieved for such cases where hospital need the record of the Donors. The process can be
smooth enough with using BloodGroup as index.
Solution 2.4
Data Population
1. Select * from P2512359Donor;

7DATABASE
2. Select * from P2512359Blood;
3. Select * from P2512359BloodBank;
4. Select * from P2512359Test;
5. Select * from P2512359Hospital;

8DATABASE
Solution 2.5
SQL Query
1. Select name from P2512359Donor where sex='Male';
2. SELECT * FROM P2512359Blood JOIN P2512359Donor ON
P2512359Blood.DonorId = P2512359Blood.DonorId;

9DATABASE
3. SELECT Name FROM P2512359Donor Full outer JOIN P2512359Blood ON
P2512359Donor.DonorId = P2512359Blood.DonorId;
4. SELECT COUNT(TestId)FROM P2512359Test WHERE (status='Positive');
5. SELECT name,phone,sex,age FROM P2512359Donor ORDER BY Age;

10DATABASE
6. select * from P2512359Donor where Name LIKE 'A%';

11DATABASE
References
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage
Learning.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Bailis, P., Fekete, A., Franklin, M.J., Ghodsi, A., Hellerstein, J.M. and Stoica, I., 2014. Coordination
avoidance in database systems. Proceedings of the VLDB Endowment, 8(3), pp.185-196.
Cellary, W., Morzy, T. and Gelenbe, E., 2014. Concurrency control in distributed database systems (Vol.
3). Elsevier.
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Zhang, L., Shi, L., Zhang, B., Zhao, L., Dong, Y., Liu, J., Lian, Z., Liang, L., Chen, W., Luo, X. and Pei, S.,
2017. Probabilistic Entity-Relationship Diagram: A correlation between functional connectivity and
spontaneous brain activity during resting state in major depressive disorder. PloS one, 12(6), p.e0178386.
Dennis, A., Wixom, B.H. and Roth, R.M., 2018. Systems analysis and design. John wiley & sons.
Oktafianto, M.R., Al Akbar, Y.F., Zulkifli, S. and Wulandari, A.M., 2018. Dismissal working relationship
using analytic hierarchy process method. International Journal of Pure and Applied Mathematics, 118(7),
pp.177-184.