NPTEL Introduction to Database Systems Assignment 5 Answers 2023

Hello NPTEL Learners, In this article, 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, first try to solve the questions by yourself. If you find any difficulty, then look for the solutions.

NPTEL Introduction to Database Systems Assignment 6 Answers 2023 Join GroupšŸ‘‡
NPTEL Principles of Management Assignment 1 Answers 2023

Note: We are trying to give our best so please share with your friends also.

NPTEL Introduction to Database Systems Assignment 5 Answers 2023
NPTEL Introduction to Database Systems Assignment 5 Answers 2023

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šŸ‘‡
NPTEL Introduction to Database Systems Assignment 5 Answers 2023

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ā€™);
  • Answer: D
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: D

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šŸ‘‡
NPTEL Introduction to Database Systems Assignment 5 Answers 2023

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Ā Chase2learn. 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 Outcome:
  • 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/

Join Our Telegram Group:- CLICK HERE

Sharing Is Caring