NPTEL Data Base Management System Week 1 Assignment Answers 2024 (July-October)

Introduction

The National Programme on Technology Enhanced Learning (NPTEL) offers a comprehensive course on Database Management Systems, which is a crucial subject in computer science and data management. This article provides the detailed answers and explanations for the Week 1 assignment of the NPTEL Database Management System course for the session July-October 2024.

Question 1:

Consider the SQL statement(s) below:

sql
CREATE table students(
  student_id number(5),
  student_name varchar2(30),
  address varchar2(100),
  email_id varchar2(20));

DELETE FROM students WHERE student_id = 1006;

Identify the correct statements: a) Both SQL and DDL Data Definition (DDL) Queries
b) Both SQL and Data Manipulation (DML) Queries
c) SQL is Data Definition (DDL) Query and Data Manipulation (DML) Query
d) SQL is Data Control Query and Data Manipulation (DML) Query

Answer: c) SQL is Data Definition (DDL) Query and Data Manipulation (DML) Query

Reason:

• The CREATE statement is a Data Definition Language (DDL) command used to create tables.
• The DELETE statement is a Data Manipulation Language (DML) command used to delete records.

Question 2:

Identify the valid primary key for the relation students.

Tables:

student_id	student_name	address	email_id
1001	RAHUL	PATNA	01rahul
1002	AMAR	CHENNAI	02amar
1003	JOHN	HYDERABAD	03john
1004	SONA	KERALA	04sona
1005	RANI	CHEMPAPET	05rani

a) student_id
b) student_name
c) address
d) student_name and address

Answer: a) student_id

Reason:

• The primary key uniquely identifies each record in the table. In this case, student_id is unique for each student.

Question 3:

Schema defines the overall logical structure of the database:

a) Logical schema defines the overall logical structure of the database.
b) Logical schema defines the overall physical structure of the database.
c) Physical schema defines the overall logical structure of the database.
d) View schema defines the interaction between end-user and database.

Answer: a) Logical schema defines the overall logical structure of the database.

Reason:

• A logical schema describes the structure of the database, such as tables, views, and indexes, without considering the physical implementation details.

Question 4:

Consider a relation MountainDetails(MountainName, Altitude, StateName) where the primary keys are (MountainName), (MountainName, Altitude), (MountainName, StateName), (MountainName, Altitude, StateName).

Select the possible candidate keys:

a) (MountainName, Altitude, StateName)
b) (MountainName, StateName)
c) (MountainName, Altitude)
d) (MountainName)

Answer: d) (MountainName)

Reason:

• A candidate key is a minimal set of attributes that can uniquely identify a tuple in the relation. Here, MountainName alone can uniquely identify each record, making it the candidate key.

Question 5:

Let students(student_id, student_name, address, email_id) and course(course_id, dept_name, duration, date) be two relations in a schema.

Which of the following statements hold for relational algebra:

a) πstudent_id (σdept_name = 'Science' (course))
b) σaddress = 'Hyderabad' (πstudent_name (students))
c) πstudent_id, student_name (σaddress = 'Hyderabad' (students))
d) σstudent_name = 'John' (πstudent_name, address (students))

Answer: c) πstudent_id, student_name (σaddress = 'Hyderabad' (students))

Reason:

• This statement correctly selects the student_id and student_name from the students relation where the address is 'Hyderabad'.

Question 6:

Which of the following statements is (are) correct?

a) View level abstraction describes how a record is stored.
b) Logical schema hides details of data types and focuses on the interaction with the database.
c) View level abstraction provides the details of data stored in a database and their relationships.
d) Physical level abstraction deals with how data is stored in files and databases.

Answer: d) Physical level abstraction deals with how data is stored in files and databases.

Reason:

• The physical level abstraction describes the physical storage of data in the database, which includes files and other storage mechanisms.

Question 7:

Consider the following instance of the StudentDetails(StudentID, StudentName) relation:

StudentID	StudentName
001	Ram
002	Shyam

If StudentID is the foreign key in the relation schema Enrollments(StudentID, CourseName, StudentID), which of the following is a valid instance of Enrollments?

a) Enrollments: StudentID, CourseName, StudentID
b) Enrollments: StudentID, CourseName
c) Enrollments: CourseName, StudentID
d) Enrollments: CourseName

Answer: c) Enrollments: CourseName, StudentID

Reason:

• The foreign key StudentID in the Enrollments table must match an existing StudentID in the StudentDetails table. This ensures referential integrity.

Question 8:

Consider the following tables:

StudentDetails:

StudentID	StudentName
001	Ram
002	Shyam

Enrollments:

StudentID	CourseName	Total_Marks
001	Math	100
002	Physics	102

Identify the correct operation(s) which will produce the following output from the above two relations:

Output:

StudentID	StudentName	CourseName	Total_Marks
001	Ram	Math	100
002	Shyam	Physics	102

a) StudentDetails ⨝ Enrollments
b) Enrollments ⨝ StudentDetails
c) πStudentID, StudentName (σCourseName = 'Math' (StudentDetails ⨝ Enrollments))
d) πStudentID, StudentName, CourseName (σTotal_Marks > 100 (Enrollments))

Answer: a) StudentDetails ⨝ Enrollments

Reason:

• The natural join (⨝) operation combines the two tables on the common attribute StudentID.

Question 9:

Consider the following tables:

CartDetails:

SNo	Total_Cost
8001	1000
8002	1500
8003	2000

Identify the correct operation(s) which will produce the following output from the above relation:

Output:

SNo	Total_Cost
8002	1500

a) πSNo, Total_Cost (σSNo = 8002 (CartDetails))
b) πSNo, Total_Cost (σTotal_Cost = 1500 (CartDetails))
c) πSNo (σTotal_Cost > 1000 (CartDetails))
d) πSNo (σTotal_Cost = 2000 (CartDetails))

Answer: b) πSNo, Total_Cost (σTotal_Cost = 1500 (CartDetails))

Reason:

• This operation selects the SNo and Total_Cost from the CartDetails table where Total_Cost is 1500.

Question 10:

Consider the following tables:

CartDetails:

SNo	Total_Cost
8001	1000
8002	1500
8003	2000

Identify the correct operation(s) which will produce the following output from the above relation:

Output:

SNo	Total_Cost
8003	2000

a) πSNo, Total_Cost (σSNo = 8003 (CartDetails))
b) πSNo, Total_Cost (σTotal_Cost = 2000 (CartDetails))
c) πSNo (σTotal_Cost > 1500 (CartDetails))
d) πSNo (σTotal_Cost = 1500 (CartDetails))

Answer: b) πSNo, Total_Cost (σTotal_Cost = 2000 (CartDetails))

Reason:

• This operation selects the SNo and Total_Cost from the CartDetails table where Total_Cost is 2000.