Consider the database schema of Figure 1 for modelling the computer science bibl
ID: 3702553 • Letter: C
Question
Consider the database schema of Figure 1 for modelling the computer science bibliography domain from Lab Assignment 2 Authors PapersByAuthor rlD (int aperID (int firstName (varchar(32)) lastName (varchar(32)) email (varchar(32)) Citations paperlD (int) citationID (int) Papers Venues title (varchar(100)) year (int) venuelD (int) name (varchar(100)) acronym (varchar (16)) type (char(10)) Figure 1. Schema for the computer science bibliography database 1. (10 points) Write two significantly different SQL queries that list the titles of papers that have never been cited. (The queries must be significantly different, in the sense that one must use some SQL construct that the other does not.) 2. (10 points) Write two significantly different queries that list the names of authors who have never published a paper in a journal. (The queries must be significantly different, in the sense that one must use some SQL construct that the other does not 3. (10 points) Write an SQL query that lists the names of authors who have only journal publications 4. (10 points) Consider the following queries which claim to list all authors who have published at least a paper in 2012 and at least a paper in 2013. For each query, say if it is correct or not. If you think the query is incorrect, then justify your answer in 2-3 sentences (a) SELECT distinct A.firstname FROM Authors A NATURAL JOIN PapersByAuthor PA1 NATURAL JOIN Papers P1 NATURAL JOIN PapersByAuthor PA2 NATURAL JOIN Papers P2 WHERE P1.year-2012 AND P2.year-2013 AND P1.paperIDP2.paperID;Explanation / Answer
If you have any doubts, please give me comment...
--1)
SELECT paperID, title
FROM Papers
WHERE paperID NOT IN (
SELECT PaperID
FROM Citations
);
--2)
SELECT firstName, lastName
FROM Authors
WHERE authorID NOT IN(
SELECT authorID
FROM PaperByAuthor
);
--3)
SELECT firstName, lastName
FROM Authors
WHERE authorID IN(
SELECT authorID
FROM (PaperByAuthor PA INNER JOIN Papers P ON PA.paperID = P.paperID) INNER JOIN Venues V ON P.venueID = V.venueID WHERE V.type='journal'
);
-- 4) Answer: B
Reason: An author need to publish atleast a paper in 2012 and atleast a paper in 2013, for that we need to check author must be publish in year of 2012 and 2013, so that we join papersByAuthor(PA1) and Papers(P1) for 2012 and as well as for 2013(PA2, P2).
--5)
SELECT P.venueID, COUNT(*) AS no_of_authors
FROM PaperByAuthor PA INNER JOIN Papers P ON P.paperID = PA.paperID
GROUP BY P.venueID;
--6)
SELECT firstName, lastName, COUNT(*) AS no_of_papers
FROM Author A INNER JOIN PaperByAuthor P ON A.authorID = P.authorID
GROUP BY A.AuthorID, firstName, lastName;
--7)
SELECT title
FROM Papers P INNER JOIN Citations C ON P.paperID = C.paperID
GROUP BY C.paperID
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM Citations C1
WHERE C.paperID = C1.paperID
);
--8)
SELECT distinct A.firstName
FROM Authors A
WHERE AuthorID IN(
SELECT AuthorID
FROM PaperByAuthor PA1 INNER JOIN Papers P1 ON PA1.paperID = P1.paperID
WHERE P1.year = 2012
GROUP BY P1.AuthorID
HAVING COUNT(*)>=2
) AND Author ID IN(
SELECT AuthorID
FROM PaperByAuthor PA2 INNER JOIN Papers P2 ON PA2.paperID = P2.paperID
WHERE P2.year = 2013
GROUP BY P2.AuthorID
HAVING COUNT(*)>=3
);