Consider the computer science bibliography domain described in Lab Assignment 1. Consider the schema in Figure 1 to model this domain. 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 paperID 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 papers by paperID. The pair authorld,paperID is 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 cach 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 paperlID cites another he title, year and venue are unique for every paper paper identified with citationll Authors PapersByAuthor authorlD (int) aperlD (int firstName (varchar(32)) lastName (varchar(32)) email (varchar(32)) Citations paperlD (int) citationlD (int) Papers Venues venuelD (int) name (varchar(100)) acronym (varchar(16)) type (char(10) er itle (varchar(100)) year (int) venuelD (int) Figure 1. Schema for the computer science bibliography database 1. Write the commands for creating the schema as described in Figure 1, 2. Use the information in the file populateDBLP.txt to populate the database with tuples. The 3. Write SQL queries that do the following together with the key constraints as described above. file has been uploaded on Piazza under Resources. the titles of papers published in 2012 Consider the computer science bibliography domain described in Lab Assignment 1. Consider the schema in Figure 1 to model this domain. 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 paperID 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 papers by paperID. The pair authorld,paperID is 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 cach 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 paperlID cites another he title, year and venue are unique for every paper paper identified with citationll Authors PapersByAuthor authorlD (int) aperlD (int firstName (varchar(32)) lastName (varchar(32)) email (varchar(32)) Citations paperlD (int) citationlD (int) Papers Venues venuelD (int) name (varchar(100)) acronym (varchar(16)) type (char(10) er itle (varchar(100)) year (int) venuelD (int) Figure 1. Schema for the computer science bibliography database 1. Write the commands for creating the schema as described in Figure 1, 2. Use the information in the file populateDBLP.txt to populate the database with tuples. The 3. Write SQL queries that do the following together with the key constraints as described above. file has been uploaded on Piazza under Resources. the titles of papers published in 2012
