Question

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

1. Populate these tables with appropriate data.
2. Write SQL queries for the following.
3. Find the names of students in Science Stream.
4. What will be the primary keys of the three tables?
5. What are the foreign keys of the three relations?
6. 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 1

The table ‘PROJECT_ASSIGNED’ has a composite primary key.

CREATE DATABASE STUDENT_PROJECT;
USE STUDENT_PROJECT;
CREATE TABLE STUDENT (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Stream VARCHAR(20) NOT NULL CHECK (Stream IN ('Science', 'Commerce', 'Humanities')),
    Section VARCHAR(2) NOT NULL CHECK (Section IN ('I', 'II')),
    RegistrationID VARCHAR(10) UNIQUE NOT NULL
);
CREATE TABLE PROJECT (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(50) NOT NULL,
    SubmissionDate DATE NOT NULL,
    TeamSize INT NOT NULL,
    GuideTeacher VARCHAR(50) NOT NULL
);
CREATE TABLE PROJECT_ASSIGNED (
    RegistrationID VARCHAR(10) NOT NULL,
    ProjectID INT NOT NULL,
    AssignDate DATE,
    PRIMARY KEY (RegistrationID, ProjectID),
    FOREIGN KEY (RegistrationID) REFERENCES STUDENT (RegistrationID),
    FOREIGN KEY (ProjectID) REFERENCES PROJECT (ProjectID)

SQL Statement to create the ‘PROJECT_ASSIGNED’ table:

CREATE TABLE PROJECT_ASSIGNED (
    RegistrationID VARCHAR(10) NOT NULL,
    ProjectID INT NOT NULL,
    AssignedDate DATE NOT NULL,
    PRIMARY KEY (RegistrationID, ProjectID)
);

SQL Statement to create add the ‘FOREIGN KEY‘ constraints to the ‘PROJECT_ASSIGNED’ table:

ALTER TABLE PROJECT_ASSIGNED
    ADD FOREIGN KEY (RegistrationID) REFERENCES STUDENT(RegistrationID),
    ADD FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID);

a) To populate the tables with appropriate data:

INSERT INTO STUDENT (RollNo, Name, Stream, Section, RegistrationID)
VALUES
(101, 'Rahul', 'Science', 'I', 'SCI_101'),
(102, 'Priya', 'Commerce', 'II', 'COM_102'),
(103, 'Nikhil', 'Humanities', 'I', 'HUM_103'),
(104, 'Kiran', 'Science', 'II', 'SCI_104'),
(105, 'Amit', 'Commerce', 'I', 'COM_105'),
(106, 'Anjali', 'Science', 'I', 'SCI_106'),
(107, 'Vivek', 'Humanities', 'II', 'HUM_107');
INSERT INTO PROJECT (ProjectID, ProjectName, SubmissionDate, TeamSize, GuideTeacher)
VALUES
(1, 'Data Analysis', '2023-06-30', 4, 'Dr. Rao'),
(2, 'Web Development', '2023-08-31', 3, 'Prof. Singh'),
(3, 'Marketing Research', '2023-07-31', 5, 'Dr. Gupta');
INSERT INTO PROJECT_ASSIGNED (RegistrationID, ProjectID, AssignDate)
VALUES
('SCI_101', 1, '2023-05-01'),
('COM_102', 2, '2023-05-02'),
('HUM_103', 3, '2023-05-01'),
('SCI_104', 1, '2023-05-03'),
('COM_105', 2, '2023-05-02'),
('SCI_106', 1, '2023-05-04'),
('HUM_107', 3, '2023-05-03');

b) This is just instruction and not an actual question

c) To write SQL query to find the names of students in Science Stream:

SELECT Name 
FROM STUDENT
WHERE Stream = 'Science';

d) The primary keys of the three tables

Table Name	Primary Key(s)
STUDENT	RollNo
PROJECT_ASSIGNED	(RegistrationID, ProjectID)
PROJECT	ProjectID

e) The foreign keys of the three relations:

Table Name	Foreign Key	REFERENCE TABLE
STUDENT	-	-
PROJECT	-	-
PROJECT_ASSIGNED	RegistrationID	STUDENT
PROJECT_ASSIGNED	ProjectID	PROJECT

f) 

SELECT Name 
FROM STUDENT, PROJECT_ASSIGNED, PROJECT
WHERE STUDENT.RegistrationID = PROJECT_ASSIGNED.RegistrationID
    AND PROJECT_ASSIGNED.ProjectID = PROJECT.ProjectID 
    AND Stream = 'Commerce'
    AND GuideTeacher = 'Prof. Singh';