NPTEL Introduction to Database Systems Assignment 5 Answers 2023

Hello Learners, In this Post, you will find NPTEL Introduction to Database Systems Assignment 5 Week 5 Answers 2023. All the Answers are provided below to help the students as a reference don’t straight away look for the solutions.

NPTEL Introduction to Database Systems Assignment 6 Answers Join Group👇

CLICK HERE

Note: First try to solve the questions by yourself. If you find any difficulty, then look for the solutions.

NPTEL Introduction to Database Systems Assignment 5 Answers 2023:

We are updating answers soon Join Group for update: CLICK HERE

Use the following schema of the academic institution relational database in the following questions wherever required.

student(rollNo, name, degree, year, sex, deptNo, advisor)
department(deptId, name, hod, phone)
professor(empId, name, sex, startYear, deptNo, phone)
course(courseId, cname, credits, deptNo)
enrollment(rollNo, courseId, sem, year, grade)
teaching(empId, courseId, sem, year, classRoom)
preRequisite(preCourseId, courseId)

deptNo is a foreign key in the student, professor and course relations referring to deptId of department relation;

advisor is a foreign key in the student relation referring to empId of professor relation;

hod is a foreign key in the department relation referring to empId of professor relation;

rollNo is a foreign key in the enrollment relation referring to rollNo of student relation;

courseId is a foreign key in the enrollment, teaching relations referring to courseId of course relation;
empId is a foreign key of the teaching relation referring to empId of professor relation;

preCourseId and courseId are foreign keys in the preRequisite relation referring to courseId of the course relation.

Q.1. Which of the following SQL sub-language constructs are used to insert rows into tables?

• DDL
• DML
• Transaction control language
• None of the above

Q.2. Which of the following relational calculus operators does not have an equivalent keyword in SQL?

• exists (∃)
• for all (∀)
• and (∧)
• None of the above

Q.3. Suppose that in the given schema, we want to change the teaching table and add an extra column called ‘teachingAssistant’. A teaching assistant is a student who assists the professor of a course in clarifying student-doubts, setting up quizzes and evaluating students etc. Assume that each course is allotted at most one teaching assistant. Which of the following commands is suitable to enforce the above requirement?

• ALTER TABLE teaching ADD teachingAssistant VARCHAR(10);
• ALTER TABLE teaching ADD teachingAssistant VARCHAR(10) NOT NULL;
• ALTER TABLE teaching ADD teachingAssistant VARCHAR(10) REFERENCES professor(empId);
• ALTER TABLE teaching ADD teachingAssistant VARCHAR(10) REFERENCES student(rollNo);

NPTEL Introduction to Database Systems Assignment 6 Answers Join Group👇

Q.4. Which of the following are unique keys in the teaching table definition?

(i) empId, courseId, year (ii) empId, courseId, sem, year (iii) empId, courseId, sem, year, classRoom

• Only (i)
• Only (ii)
• Only (ii) and (iii)
• All (i), (ii) and (iii)

Q.5. Suppose that in the given schema, the PRIMARY KEY of preRequisite table is only “courseId”. Then, which of the following statement(s) is(are) TRUE in all data instances of the database?

• Every course in course table (specified in courseId column) has at least one prerequisite
• Every course in course table (specified in courseId column) has exactly one prerequisite
• Every course in preRequisite table (specified in courseId column) has exactly one prerequisite
• All of the above

Q.6. Suppose we need to find the roll numbers of students whose grades are neither ‘U’ nor ‘W’ in the course having id ‘CS123’. What is the correct SQL query?

• SELECT rollNo FROM enrollment where courseId = ‘CS123’ and grade != ‘U’ or grade != ‘W’
• SELECT rollNo FROM enrollment where courseId = ‘CS123’ and NOT (grade != ‘U’ and grade != ‘W’)
• SELECT rollNo FROM enrollment where courseId = ‘CS123’ and grade NOT EXISTS (‘U’, ‘W’);
• SELECT rollNo FROM enrollment where courseId = ‘CS123’ and grade NOT IN (‘U’, ‘W’);

Q.7. Which of the following queries would find the students who enrolled in a course twice (Note that a course is usually offered once in a year but some popular courses are offered in both the semesters of the same year)?

• Answer: C

NPTEL Introduction to Database Systems Week 5 Answers Join Group👇

CLICK HERE

Q.8. Consider the following query to retrieve the most senior professor (determined based on startYear) :

SELECT p1.name
FROM professor p1
WHERE p1.startYear ———- (SELECT p2.startYear
 FROM professor p2)

Which of the following options is the correct filler for the blank ?

• > ALL
• >= ALL
• >= ANY
• IN

Q.9. Which of the following queries would find the courses with at least two prerequisites?

• Answer: B

Q.10. Consider the following query

SELECT rollNo as identifier
FROM student
UNION
SELECT empId as identifier
FROM professor

Which of the following statements is correct regarding the above query?

• The query executes only if rollNo of student and empId of professor have same data types
• The query executes in all cases (irrespective of the data types of rollNo and empId)
• The query does not execute at all
• None of the above

NPTEL Introduction to Database Systems Assignment 5 Answers Join Group👇

Disclaimer: This answer is provided by us only for discussion purpose if any answer will be getting wrong don’t blame us. If any doubt or suggestions regarding any question kindly comment. The solution is provided by Brokenprogrammers. This tutorial is only for Discussion and Learning purpose.

About NPTEL Introduction to Database Systems Course:

Databases are the backbone of almost all the digital services and e-governance solutions. Modern businesses and financial systems heavily depend on databases systems and transaction processing for their successful operation. This course introduces the students to the various theoretical and practical principles involved in the design and use of databases systems with the help of database management systems (DBMS) and the SQL Standard

Course Layout:

• Week 1   : Introduction and part of E/R Model Module
• Week 2   : ER Model Module
• Week 3   : Relational Model Module
• Week 4   : Relational Model Module
• Week 5   : TRC Module and part of SQL Module
• Week 6   : SQL Module
• Week 7   : Indexes Module
• Week 8   : Indexes Module + Query Processing Module
• Week 9   : Normal Forms Module
• Week 10 : Normal Forms Module
• Week 11 : Transaction Processing Module
• Week 12 : Transaction Processing Module

CRITERIA TO GET A CERTIFICATE:

Average assignment score = 25% of average of best 8 assignments out of the total 12 assignments given in the course.
Exam score = 75% of the proctored certification exam score out of 100

Final score = Average assignment score + Exam score

YOU WILL BE ELIGIBLE FOR A CERTIFICATE ONLY IF AVERAGE ASSIGNMENT SCORE >=10/25 AND EXAM SCORE >= 30/75. If one of the 2 criteria is not met, you will not get the certificate even if the Final score >= 40/100.

If you have not registered for exam kindly register Through https://examform.nptel.ac.in/