Database Lab Assignment-1 Solution

Description

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-

1. Use ‘alter table’ command to add primary key constraint to the following tables-

1. 1. In Paper_details, paper_id as the key attribute

   2. In Author_details, author_id as the key attribute

   3. In Student_details, student_id as the key attribute

   4. In Faculty_details, faculty_id and research_area as the key attribute

1. 5. In Paper_author, paper_id and author_id combination as the key attribute

   6. In Supervisor, faculty_id and student_id combination as the key attribute

2. Use ‘alter table’ command to add the following foreign key constraints –

1. 1. paper_id of Paper_author references to paper_id of Paper_details

   2. author_id of Paper_author references to author_id of Author_details

   3. faculty_id of Supervisor references to faculty_id of Faculty_details

   4. student_id of Supervisor references to student_id of Student_details

3. List the titles of all conference papers.

4. Find the students whose research_area is “Big Data”.

5. Find the total number of journal papers in the database.

6. List the students whose DOB is between 1/4/1990 and 31/3/2000

7. List the faculties of IIT Patna and whose research area are “AI”

8. List the faculties who work in both “AI” and “Big Data”

9. List the students whose name end with “Kumar”

10. Show for each faculty, how many students are supervised under him/her.

11. List the paper_ids which have multiple authors