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

Table: STUDENT

RollNoNameStreamSectionRegistrationID

*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

RegistrationIDProjectIDAssignDate

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

ProjectIDPreojectNameSubmissionDateTeamSizeGuideTeacher

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

TablePrimaryLey
STUDENTRegistrationID
PROJECTProjectID
PROJECT_ASSIGNED

(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”);

Leave a Comment

Your email address will not be published. Required fields are marked *

error: Content is protected !!