Question

Consider the computer science bibliography domain described in Lab Assignment 1. Consider the schema in Figure to model this domai Each author in table Authors is identified by a unique authorID which is autogenerated. Each author has a first name, a last name and an email. The email is unique, while the first name and last name may not be unique. Each paper is identified by a unique papeD which is autogenerated. For each paper, we keep in table Papers the title, a short abstract, the year it was published, and the venue where it was published The table PapersByAuthors maintains the relationships between papers and authors of a paper, identifying authors by authorID, and unique. Each paper is published at a venue The table Venues holds data about venues. Each venue is identified by a unique venuelD which is autogenerated. The venue can be of type conference, or of type journal. Therefore, attribute type takes two possible values: conference or journal. For each venue, we keep the name and the abbreviation. Finally, every paper cites other papers. The citations are saved in table Citations, where each tuple (paperlD,citationID) means that a paper identified with paperID cites another The title, year and venue are unique for every paper erlD. The pair authorld,paperID is paper identified with citationID firstName (varchar(32)) lastName (varchar(32) email (varchar(32)) paperlD (int) citationlD (int) title (varchar(100) year (int) name (varchar(100) acronym (varchar(16)) type (char(10)) Figure 1. Schema for the computer science bibliography database 1. Write the commands for creating the schema as described in Figure 1, together with the key constraints as described above. 2. Use the information in the file populateDBLP.txt to populate the database with tuples. The file has been uploaded on Piazza under Resources.

Answer 1

Solution:

The query for creating the table is given below:

TableName: Authors

CREATE TABLE Authors(
authorID int NOT NULL,
firstName varchar(32),
lastName varchar(32),
email varchar(32)

    PRIMARY KEY (authorID),
);

TableName: Venues

CREATE TABLE Venues(
venueID int NOT NULL,
name varchar(100),
acronym varchar(16),
type char(10)

    PRIMARY KEY (venueID),

);

TableName: Papers

CREATE TABLE Papers(
authorID int NOT NULL,
title varchar(100),
year int,
venueID int,

    PRIMARY KEY (PaperID),

venueID int FOREIGN KEY REFERENCES Venues(venueID)
);

TableName: Citations

CREATE TABLE Citations(
citationID int NOT NULL,
paperID int,

paperID FOREIGN KEY REFERENCES Venues(paperID)
);

TableName: PapersByAuthors

CREATE TABLE PapersByAuthors(
authorID int NOT NULL,
paperID int

    PRIMARY KEY (authorID, paperID),

paperID int FOREIGN KEY REFERENCES Papers(paperID),

authorID int FOREIGN KEY REFERENCES Authors(authorID)
);

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)