Design a schema and write SQL queries to support a university’s research…

To manage a university research publication database, a relational schema can be designed to store details about researchers, publications, authorship, and citations.

Step 1: Schema Design

Researcher table
Researcher(Researcher_ID, Name, Department)

Publication table
Publication(Pub_ID, Title, Year, Journal, Citation_Count)

Authorship table (to track co-authors)
Authorship(Researcher_ID, Pub_ID)

This structure allows multiple researchers to be linked with the same publication, which helps in tracking co-authors.

Step 2: Example SQL Queries

Query to list publications with their authors (using join):

SELECT P.Title, R.Name
FROM Publication P
JOIN Authorship A ON P.Pub_ID = A.Pub_ID
JOIN Researcher R ON A.Researcher_ID = R.Researcher_ID;

Query to find total publications per researcher (aggregate function):

SELECT R.Name, COUNT(A.Pub_ID) AS Total_Publications
FROM Researcher R
JOIN Authorship A ON R.Researcher_ID = A.Researcher_ID
GROUP BY R.Name;

Query to rank publications by citation count:

SELECT Title, Citation_Count
FROM Publication
ORDER BY Citation_Count DESC;

Example:
If publication “AI Research 2024” has 120 citations and is written by two researchers, both will appear through the Authorship table.

Thus, using joins, aggregation, and ranking queries, the system can efficiently track co-authors, citation counts, and publication performance.