GHADA_IT403

Project Instructions

You can work on this project as a group (minimum 3 and maximum 4 students). Each group member must submit the project individually with all group member names mentioned on the cover page.

This project worth 14 marks and will be distributed as in the following:

o Identify the entity types, attributes, keys. (1.5 marks)

o Identify the relationships and cardinalities. (1.5 marks)

o Design ER model. (3 marks)

o Schemas after Normalization (2 marks)

o Create the normalized tables. (1.5 marks)

o Populate your tables with at least 5 rows. (1.5 marks)

o Execute the requested sample queries. (3 marks)

Each student must submit one report about their chosen Project via the Blackboard (Email submission will not be accepted and will be awarded ZERO marks) containing the following:

a) ER Diagram.

b) All schemas before normalization.

c) All schemas after normalization.

d) All SQL statements of:

Creating tables.

Inserting data in tables.

e) All requested queries/results.

Screenshots from MySQL (or any other software you use) of all the tables after population and query results.

You are advised to make your work clear and well presented; marks may be reduced for poor presentation. This includes filling in your information on the cover page.

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 marks being awarded.

The work should be your own. Copying from students or other resources, including AI software, will result in ZERO marks.

Project Description

Global Research Collaboration Platform.

Consider a global digital platform is being developed to manage research partnerships between universities, research centers, funding agencies, and industry partners across different countries.

The system aims to centralize all critical information and make collaboration more transparent, efficient, and organized.

Universities and Research Centers Management

Each participating university in the system is identified by a University_ID. The platform stores information such as the universitys name, country, international ranking, accreditation status, and year of establishment. Each university contains multiple colleges, with each college uniquely identified by a College_ID, and the system stores its name, head of college, and location.

Colleges are divided into academic departments. Every department has a unique Department_ID, department name, specialization field, office location, and contact email. Within these departments, multiple research centers operate as specialized units focusing on particular fields of study. Each research center is assigned a Center_ID, center name, research focus area, annual budget, establishment date, and a director who is a registered researcher in the system.

Researcher Info Management

The platform maintains detailed profiles for all researchers. Each researcher has a unique Researcher_ID, full name, nationality, date of birth, gender, institutional email, ORCID number, academic rank, and areas of specialization.

Researchers are affiliated with a single department, connecting them to a specific college and university. However, they may work on multiple projects, possibly in collaboration with researchers from other departments or research centers.

The system also records researchers employment details, including start date, contract type, and experience. It calculates derived values such as total publications and total citations based on stored publication data, providing an overview of each researchers academic contributions.

Research Project Management

Research projects are central to the platform. Each project has a unique Project_ID, with attributes including project title, abstract, research domain, start and end dates, total budget, and status (e.g., Proposed, Active, Completed).

Each project is associated with a primary research center, linking it directly to a department, college, and university. Researchers from that research center, as well as other affiliated centers, may be assigned to the project. The system tracks each researchers role and contribution within the project.

Global Research Collaboration Platform.

a) Fill in the table below with all the Entities based on the given requirements:

Entity Type Attributes of the Entity Key

b) Fill in the table below with all the relationship type based on the given requirements.

Relationship Type Entities connected by the relationship type Cardinality Relationship attribute

c) Draw the ER Diagram

d) Provide the database schemas after the normalization

e) Create the normalized tables and populate them with at least 5 rows each (Provide screenshots of all the tables after filling in the data).

f) Write SQL queries to find the following (Provide screenshots of query results):

1. List all researchers specializing in Computer Science and display their details.

2. List all research centers established after 2020 and display their details.

3. List the Project_ID, project title, project domain, the full names of all researchers involved, for projects with status Active.

4. List all departments with the number of researchers in each, ordered by highest to lowest.

5. List Project_ID, Title, and the Name of the research center hosting each project.

6. Retrieve the full name of researchers involved in more than one project along with the number of projects they are part of.

WRITE MY PAPER