Create a database called STUDENT_PROJECT having the following tables.

Q. Create a database called STUDENT_PROJECT having the following tables. Choose appropriate data type and apply necessary constraints.

Table: STUDENT

 

 
	 
RollNo
 
	 
Name
 
	 
Stream
 
	 
Section
 
	 
RegistrationID
 
 

* The values in Stream column can be either Science, Commerce, or Humanities.
* The values in Section column can be either I or II.


Table: PROJECT_ASSIGNED

 

 
	 
RegistrationID
 
	 
ProjectID
 
	 
AssignDate
 
 

Table: PROJECT

 

 
	 
ProjectID
 
	 
ProjectName
 
	 
SubmissionDate
 
	 
TeamSize
 
	 
GuideTeacher
 
 

(a) Populate these tables with appropriate data.

(b) Write SQL queries for the following.

(c) Find the names of students in Science Stream.

(d) What will be the primary keys of the three tables?

(e) What are the foreign keys of the three relations?

(f) Finds  names  of  all  the  students  studying  in  class  ‘Commerce stream’ and are guided by same teacher, even if they are assigned different projects.

Answer :-

Create Database STUDENT_PROJECT ;

Create table STUDENT ( RollNo int primary key ,Name char(50),Stream char(10) , Section char(1) , RegistrationID char (10) FOREIGN KEY ( RegistrationID ) REFERENCES PROJECT_ASSIGNED ( RegistrationID ) ) ;

Create table PROJECT_ASSIGNED ( RegistrationID char(10) Primary key , ProjectID char(10) , AssignDate date FOREIGN KEY ( ProjectID ) REFERENCES Project ( ProjectID ) ) ;

Create table Project ( ProjectID char(10 ) Primary key , ProjectName char(50) , SubmissionDate date , TeamSize int , GuideTeacher char(50) );


(a) Insert the data of your choice.

(c) Select Name from STUDENT where stream = “Science” .

(d) RollNo in student table.

RegistrationID in PROJECT_ASSIGNED table .
ProjectID in project table.

(e) RegistrationID in student table.
ProjectID in PROJECT_ASSIGNED table.

(f) Select name from student ,  PROJECT_ASSIGNED ,  project group by GuideTeacher
having student.RegistrationID = PROJECT_ASSIGNED.RegistrationID and PROJECT_ASSIGNED. ProjectID = Project.ProjectID ;