Name:

Enrolment No:

Even Semester Mid-Term Examination, March 2024

Faculty of Engineering, SIT
Department of Data Science & Engineering
B.Tech. Data Science & Engineering
Course Code: DS2201 Course: Database Systems Semester: IV
Time: 1.5 hrs. Max. Marks: 30
Instructions: All questions are compulsory.
Missing data, if any, may be assumed suitably.
Calculator is not allowed.
SECTION A
S.No. Marks CO
Q A1 How Atomicity problems affect DBMS? 2 DS2201.1
Q A2 Define weak entity set in entity relationship model with example. 2 DS2201.2
Q A3 Distinguish between an entity, an entity type, and an entity set. 2 DS2201.3
SECTION B
Q B1 Explain the five responsibilities of a DBA with example. 4 DS2201.1
Suppose you have a database containing employee information, including employee
Q B2 ID, name, department, and salary. Explain how physical data independence might 4 DS2201.1
apply in this scenario.
Draw ER diagram for university database consisting of four entities Student,
Department, Class, and Faculty.
Q B3 Student has a unique id; the student can enroll for multiple classes. Faculty must 4 DS2201.2
belong to a department and faculty can teach multiple classes. Each class is taught
by only faculty. Every student will get grade for the class he/she has enrolled.
Consider the following two tables Customer and Order given below.

Customer Order
C_id C_Name
1 John O_id Amount C_id
2 Robert
1 200 10
3 David
2 500 3
4 John
5 Betty 3 300 6
Q B4 4 800 5 4 DS2201.3

5 150 8

Perform the following operations on both tables and show the result table for each,
1. Union
2. Full outer Join
3. Left outer Join
4. Right outer Join

SECTION C
 Write SQL or Relational algebra statements for following:
Student ( Enrno, name, courseId, emailId, cellno)
Course (courseId, course_nm, duration)
i) Add a column city in student table. DS2201.2
Q C1 ii) Find out list of students who have enrolled in “computer” course. 8 ,
iii) List name of all courses with their duration. DS2201.3
iv) List name of all students start with „a‟.
v) List email Id and cell no of all mechanical engineering students.
Q A1 How Atomicity problems affect DBMS?
Solution: A computer system, like any other mechanical or electrical device, is subject to failure. In many
applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to
the failure. Consider a program to transfer $50 from account A to account B. If a system failure occurs during the
execution of the program, it is possible that the $50 was removed from account A but was not credited to account
B, resulting in an inconsistent database state. Clearly, it is essential to database consistency that either both the
credit and debit occur, or that neither occur. That is, the funds transfer must be atomic—it must happen in its
entirety or not at all. It is difficult to ensure atomicity in a conventional file-processing system.
Q A2 Define weak entity set in entity relationship model with example.
Solution: In an Entity-Relationship (ER) model, a weak entity set is an entity set that cannot be uniquely identified
by its attributes alone. Instead, it relies on a relationship with another entity set, known as the identifying or owner
entity set, to establish its identity.
Consider a database model for a library system. We have two entity sets: Book and Copy.
• Book represents a type of book and has attributes such as ISBN, Title, and Author.
• Copy represents individual physical copies of books in the library and has attributes like Copy_ID and
Status.
In this scenario, Copy is a weak entity set because it cannot be uniquely identified by its attributes alone; multiple
copies of the same book may exist, and their identity depends on the relationship with the Book entity set.
Q A3 Distinguish between an entity, an entity type, and an entity set.
Solution:
1. Entity:
• An entity is a real-world object or concept with a distinct and independent existence, which can be
uniquely identified.
• Entities typically correspond to nouns in the problem domain.
• Examples of entities include a person, a book, a customer, or an employee.
2. Entity Type:
• An entity type represents a category or class of similar entities. It defines a set of entities that have
the same attributes or properties.
• Entity type specifies the common characteristics shared by all entities belonging to that category.
• It serves as a template for creating individual entity instances.
• For example, "Student" can be an entity type that encompasses all individual students, each having
attributes like name, student ID, and address.
3. Entity Set:
• An entity set is a collection of similar entities of a particular entity type that exists at a specific
point in time.
 • It is a set of instances of the same entity type.
• Entity sets are the actual instances or occurrences of entities in the database.
• For instance, if "Student" is an entity type, then the set of all students currently enrolled in a
university database would be an entity set.
Q B1 Explain the five responsibilities of a DBA with example.
Solution: The responsibilities of a DBA include:
• Schema definition. The DBA creates the original database schema by executing a set of data definition
statements in the DDL.
• Storage structure and access-method definition.
• Schema and physical-organization modification. The DBA carries out changes to the schema and physical
organization to reflect the changing needs of the organization, or to alter the physical organization to improve
performance.
• Granting of authorization for data access. By granting different types of authorization, the database administrator
can regulate which parts of the database various users can access. The authorization information is kept in a
special system structure that the database system consults whenever someone attempts to access the data in the
system.
• Routine maintenance. Examples of the database administrator’s routine maintenance activities are:
Q B2 Suppose you have a database containing employee information, including employee ID, name,
department, and salary. Explain how physical data independence might apply in this scenario.
Solution: Physical data independence refers to the ability to modify the physical storage structures or access
methods of a database system without affecting the conceptual or external schema. In the scenario of a database
containing employee information, including employee ID, name, department, and salary, physical data
independence might apply in the following ways:
1. Storage Structures: The database administrator might decide to change the underlying storage structures,
such as converting from a row-based storage format to a columnar storage format. With physical data
independence, this change can be implemented without requiring modifications to the logical or
conceptual schema. Applications and users interacting with the database will remain unaffected by this
change.
2. Indexing and Access Methods: The database administrator might optimize the database by implementing
different indexing or access methods to improve query performance. For example, they may choose to
create additional indexes on frequently queried columns like "employee ID" or "department". With
physical data independence, these changes can be made transparently without impacting the way users
interact with the database at the logical level.
3. Data Partitioning: In a large-scale database, the administrator may decide to partition the data across
multiple storage devices or servers for scalability and performance reasons. This partitioning can be
 achieved without changes to the external schema, ensuring that applications continue to function
seamlessly without knowledge of the underlying physical implementation.
4. Replication and Backup: Physical data independence also allows for the implementation of data
replication and backup strategies. For instance, the administrator might choose to replicate certain tables
or databases for fault tolerance or create backups using different technologies or storage mediums. These
actions can be performed without affecting the logical or external views of the data.
Q B3 Draw ER diagram for university database consisting of four entities Student, Department, Class, and
Faculty. Student has a unique id; the student can enroll for multiple classes. Faculty must belong to a department
and faculty can teach multiple classes. Each class is taught by only faculty. Every student will get a grade for the
class he/she has enrolled in.
Solution: Follow the steps given below to draw an Entity Relationship (ER) diagram for a University database
application −

Step 1 − Identifying the entity sets.

The entity set has multiple instances in each business scenario.
As per the given constraints the entity sets are as follows −
• Department
• Course i.e. Hole Class is mapped as a course
• Student
• Instructor
Step 2 − Identifying the attributes for the given entities.
Department − the relevant attributes are department Name and location.
Course − The relevant attributes are courseNo, course Name, Duration, and prerequisite.
Instructor − The relevant attributes are Instructor Name, Room No, and telephone number.
Student − The relevant attributes are Student No, Student Name, and date of birth.
Step 3 − Identifying the Key attributes
• Department Name is the key attribute for Department.
• CourseNo is the key attribute for Course entity.
• Instructor Name is the key attribute for the Instructor entity.
• StudentNo is the key attribute for Student entities.
Step 4 − Identifying the relationship between entity sets
• The department offers multiple courses and each course belongs to only one department, hence cardinality
between department and course if one to many.
• One course is enrolled by multiple students and one student for multiple courses. Hence, relationships are
many to many.
• One department has multiple instructors and one instructor belongs to one and only one department,
hence the relationship is one to many.
• One department has multiple instructors and one instructor belongs to one and only one department,
hence the relationship is one to many.
• One course is taught by only one instructor but one instructor teaches many courses hence the
relationship between course and instructor is many to one.

The relationship between instructor and student is not defined because of the following reasons −
There is no significance in the relationship.
We can always derive this relationship indirectly through course and instructors, and course and student.
Q B4 Consider the following two tables Customer and Order given below.

C_id C_Name O_id Amount C_id

1 John 1 200 10
2 Robert 2 500 3

3 David 3 300 6
4 800 5
4 John
5 150 8
5 Betty

Perform the following operations on both tables and show the result table for each,
1. Union
2. Full outer Join
3. Left outer Join
4. Right outer Join
Solution: 1. Union of Customer and Order is not possible because they are not Union Compatible i.e for a union
operation r ∪ s to be valid, we require that two conditions hold:
a. The relations r and s must be of the same arity. That is, they must have the same number of attributes.
b. The domains of the ith attribute of r and the ith attribute of s must be the same, for all i.
2. Full outer Join
The full outer join does both of those operations, padding tuples from the left relation that did not match any from
the right relation, as well as tuples from the right relation that did not match any from the left relation, and adding
them to the result of the join.
C_id C_Name O_id Amount
3 David 2 500
5 Betty 4 800
1 John Null Null
2 Robert Null Null
4 John Null Null
10 Null 1 200
6 Null 3 300
8 Null 5 150

3. Left outer Join

takes all tuples in the left relation that did not match with any tuple in the right relation, pads the tuples with null
values for all other attributes from the right relation, and adds them to the result of the natural join.
Customer Left Outer Join Order
C_id C_Name O_id Amount
3 David 2 500
5 Betty 4 800
1 John Null Null
2 Robert Null Null
4 John Null Null

4. Right outer Join

It pads tuples from the right relation that did not match any from the left relation with nulls and adds them to the
result of the natural join.
Customer Right Outer Join Order
C_id C_Name O_id Amount
3 David 2 500
5 Betty 4 800
10 Null 1 200
6 Null 3 300
8 Null 5 150

Q C1 Write SQL or Relational algebra statements for following:

Student ( Enrno, name, courseId, emailId, cellno)
Course (courseId, course_nm, duration)
i) Add a column city in student table.
ii) Find out list of students who have enrolled in “computer” course.
iii) List name of all courses with their duration.
iv) List name of all students start with „a‟.
v) List email Id and cell no of all mechanical engineering students.
Solution
i. Add a column city in the student table:
ALTER TABLE Student ADD COLUMN city VARCHAR (50);
ii. Find out the list of students who have enrolled in the "computer" course:
SELECT s.name
FROM Student s
JOIN Course c ON s.courseId = c.courseId
WHERE c.course_nm = 'computer';
OR
π_name(σ_course_nm='computer'(Student ⨝ Course))
iii. List the name of all courses with their duration:
SELECT course_nm, duration FROM Course;
OR
π_course_nm, duration(Course)
iv. List the names of all students starting with 'a':
SELECT name FROM Student WHERE name LIKE 'a%';
v. List the email ID and cell number of all mechanical engineering students:
SELECT s.emailId, s.cellno FROM Student s JOIN Course c ON s.courseId = c.courseId WHERE c.course_nm
= 'mechanical engineering';
OR
π_emailId, cellno(σ_course_nm='mechanical engineering'(Student ⨝ Course)