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

Table: STUDENT

*The values in Stream column can be either Science, Commerce, or Humanities.
* The values in Section column can be either I or II.
Ans:
CREATE TABLE STUDENT (
RollNo INT NOT NULL UNIQUE,
Name varchar(20) NOT NULL,
Stream varchar(20) CHECK (Stream IN (‘Science’, ‘Commerce’, ‘Humanities’)),
Section varchar(2) CHECK (Section IN (‘I’, ‘II’)),
RegistrationID int PRIMARY KEY);
Table: PROJECT_ASSIGNED

Ans:   
CREATE TABLE PROJECT_ASSIGNED (
RegistrationID INT,
ProjectID INT,
AssignDate DATE,
CONSTRAINT FK_STUDENT FOREIGN KEY (RegistrationID) REFERENCES STUDENT (RegistrationID),
CONSTRAINT FK_PROJECT FOREIGN KEY (ProjectID) REFERENCES PROJECT (ProjectID));
Table: PROJECT

Ans:   
CREATE TABLE PROJECT (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR (20) NOT NULL,
SubmissionDate DATE,
TeamSize INT,
GuideTeacher VARCHAR (30));
(a) Populate these tables with appropriate data.
Ans:
Use INSERT INTO statement.
(b) Write SQL queries for the following.
(c) Find the names of students in Science Stream.
Ans:
SELECT Name from STUDENT
WHERE Stream = “Science”;
(We can alse write WHERE Stream In(‘Scince’))
(d) What will be the primary keys of the three tables?
Ans:   
Following are the Primary keys of the three tables

(e) What are the foreign keys of the three relations?
Ans: RegistrationID of STUDENT relation and ProjectID of PROJECT relation are Foreign Key in PROJECT_ASSIGNED relation.
(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.
Ans:         
SELECT Name FROM STUDENT, PROJECT, PROJECT_ASSGINED
WHERE (STUDENT.RegistrationID = PROJECT_ASSIGNED.RegistrationID AND PROJECT.ProjectID = PROJECT_ASSIGNED.ProjectID) AND (Stream = “Commerce Stream” AND GuideTeacher = “teachername”);