CP0054E Database Design Project: ER Diagram, SQL, and System Proposals

This assignment showcases a database design project for a research department, encompassing an Entity-Relationship (ER) diagram, essential SQL queries, and system proposals. The solution includes SQL scripts, demonstrating the creation of views and the execution of queries to retrieve and manipulate data. The project also focuses on database security, outlining key measures such as isolating the database server, restricting access, and protecting against SQL injection. Furthermore, the assignment proposes future system enhancements, including a management support system and a hospital automation system, highlighting the potential for improved data management and operational efficiency. It also considers an expert information system. The assignment concludes with a bibliography of relevant sources.

System and
Database

Final ER Diagram for Research
Department
SICK TWIN
ILLNESS RECORD
RESEARCH PROJECTS
TEST
DEPARTMENT
SCHOLORLY JOURNALS RESEARCHERS
T_IDPK
AGE
SEX
CONTACT_DETAILS
AGE AND NATIONALITY OF
MOTHER AT DATE OF
BIRTH
PLACE_OF_BIRTH
NATIONALITY
Blood_TYPE
ILLNESS
AGE AND NATIONALITY OF
FATHER AT DATE OF BIRTH
T_IDFKPK
ILLNESS
DATE_OF_DIAGNOSIS
HOSPITAL_NAME
DURATION
RESEARCH_STUDIES_DESC
RIPTION
PROJECT_IDPK
T_IDFK
TEST_IDFK
TEST_IDPK
DATE
DESCRIPTION
TIME_REQUIRED
BUDGET
DEPT_IDPK
DEPT_NAME
LOCATION
JOURNAL_IDPK
PROJECT_IDFK
PUBLISH_DATE
RESEARCHERS_IDPK
NAME
DEPARTMENT_IDFK
RESEARCHERS_IDFK
RESEARCHERS_IDFK
SICK TWIN
ILLNESS RECORD
RESEARCH PROJECTS
TEST
DEPARTMENT
SCHOLORLY JOURNALS RESEARCHERS
T_IDPK
AGE
SEX
CONTACT_DETAILS
AGE AND NATIONALITY OF
MOTHER AT DATE OF
BIRTH
PLACE_OF_BIRTH
NATIONALITY
Blood_TYPE
ILLNESS
AGE AND NATIONALITY OF
FATHER AT DATE OF BIRTH
T_IDFKPK
ILLNESS
DATE_OF_DIAGNOSIS
HOSPITAL_NAME
DURATION
RESEARCH_STUDIES_DESC
RIPTION
PROJECT_IDPK
T_IDFK
TEST_IDFK
TEST_IDPK
DATE
DESCRIPTION
TIME_REQUIRED
BUDGET
DEPT_IDPK
DEPT_NAME
LOCATION
JOURNAL_IDPK
PROJECT_IDFK
PUBLISH_DATE
RESEARCHERS_IDPK
NAME
DEPARTMENT_IDFK
RESEARCHERS_IDFK
RESEARCHERS_IDFK

Important VIEWS for system.
create view VIEW1 AS
SELECT * from research_projects;
CREATE view VIEW2 AS
select * from scholarly_journals;

Important SQL queries for
system.
• select sj.journal_id, sj.PROJECT_ID,sj.publish_date, sj.RESEARCHERS_ID, rp.T_ID from
scholarly_journals sj
INNER JOIN research_projects rp ON
sj.RESEARCHERS_ID=rp.RESEARCHERS_ID;
• select r.RESEARCHER_ID, r.NAME, d.DEPT_NAME,d.DEPT_ID from
researchers r
inner JOIN
department d ON r.DEPARTMENT_ID = d.DEPT_ID;

Important SQL queries for
system.
SELECT * FROM test
INNER join research_projects ON
test.TEST_ID=research_projects.TEST_I
D
WHERE T_ID= 'tw02';
select * from illness_record
INNER JOIN sick_twin ON
illness_record.T_ID=sick_twin.T_ID WHERE sick_twin.T_ID='tw03'

Securing the Database
•Isolating the Database Server
•Tailor the DB Installation
•Keep it Updated
•Restrict the DB Processes
•Restrict SQL Traffic
•Use Least Privilege When Assigning
Permissions
•set a Strong Admin Password
•Audit DB Logins
•Secure Your Backups
•Protect Against SQL Injection

Proposals for additional
Information Systems or
Databases
• Management support systems: We can add this system in future. This system creates
timely, accurate and organized the information to allow lead researchers and hospital
research director to makes decisions and supervise every activities.
• Hospital automation system: We can add this system in the future, because this system
use software and networks to improve the workflow and enhance the communication
among every employees in Hospital.
• Expert Information System: This system can record every employee details in the
database. If hospital keep employee qualification and work experience details, authority
can easily assign an employee according to their expertise.

Bibliography
Christudas, B., 2019. MySQL. In Practical Microservices Architectural Patterns (pp.
877-884). Apress, Berkeley, CA.
Hogan, R., 2018. A practical guide to database design. CRC Press.
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology
Research, 6, p.1.

Thank You