Create a database name "DBLAB". Create the following tables.
Paper_details(paper_id (varchar), paper_title (varchar), paper_type* (varchar), publication(date))//*paper-type should be conference/journal
Paper_author(paper_id(varchar), author_id(varchar))
Author_details(author_id(varchar), author_type* (varchar))//*author_type should
be student/faculty
Student_details(student_id(varchar), student_name(varchar), student_institute(varchar),
department(varchar), DOB(date), research_area(varchar))
Faculty_details(faculty_id(varchar), faculty_name(varchar),
faculty_institute(varchar), department(varchar), DOB(date), research_area(varchar))
Supervisor(faculty_id(varchar), student_id(varchar))
Add atleast 10-15 relevant records in each of the above tables. Now write MySQL query to perform each of the followings-
Use ‘alter table’ command to add primary key constraint to the following tables-
In Paper_details, paper_id as the key attribute
In Author_details, author_id as the key attribute
In Student_details, student_id as the key attribute
In Faculty_details, faculty_id and research_area as the key attribute
In Paper_author, paper_id and author_id combination as the key attribute
In Supervisor, faculty_id and student_id combination as the key attribute
Use ‘alter table’ command to add the following foreign key constraints –
paper_id of Paper_author references to paper_id of Paper_details
author_id of Paper_author references to author_id of Author_details
faculty_id of Supervisor references to faculty_id of Faculty_details
student_id of Supervisor references to student_id of Student_details
List the titles of all conference papers.
Find the students whose research_area is “Big Data”.
Find the total number of journal papers in the database.
List the students whose DOB is between 1/4/1990 and 31/3/2000
List the faculties of IIT Patna and whose research area are “AI”
List the faculties who work in both “AI” and “Big Data”
List the students whose name end with “Kumar”
Show for each faculty, how many students are supervised under him/her.
List the paper_ids which have multiple authors
