Assignments/Self Learning Activity

No. of
Sr. No. Name of the activity hours
Assignment Writing (Numerical based assignment is
1 preferable) 15
2 Technical Video Based Learning Related to the Subject 15
3 Real World Case Studies-Based Learning 15

Activity-1. Assignment Writing (Numerical based assignment is

preferable)
Assignment 1: Fundamentals of Database System & Relational Model
Objective: To understand the basic concepts and components of a DBMS and the structure of
the relational model.
Total Questions: 3
Duration: 3 Hours
1. Scenario: A college database needs to store information about Students (StudentID,
Name, Program) and the Courses (CourseID, CourseName, Credits) they are enrolled
in.
o a) Identify the potential entities and their attributes.
o b) Define the following keys with a suitable example from the scenario: Super
Key, Candidate Key, and Primary Key.
o c) If we add a table "Enrollment" to record which student is enrolled in which
course, what would be its attributes? Identify its Primary Key and Foreign
Key(s).
2. Given a relation R (A, B, C, D, E) and a set of functional dependencies F = { A →
BC, CD → E, B → D, E → A }:
o a) Find the closure of the attribute set (AC).
o b) List all the candidate keys for the relation R.
3. Given two relations:
o Student: (Roll_No, Name, City)
o Course: (Course_ID, Course_Name, Credits)
o Write Relational Algebra expressions for the following queries:
▪ a) Find the names of all students who live in 'Bhavnagar'.
▪ b) Find the Roll Numbers and Names of students who have not taken
any course. (Hint: Assume a third relation Enrollment(Roll_No,
Course_ID) for this part).
▪ c) List all Course_Names along with the names of students enrolled in
them.

Assignment 2: SQL - Data Definition and Manipulation

Objective: To practice the basic structure of SQL queries, DDL, and DML commands.
Total Questions: 3
Duration: 3 Hours
 1. DDL Practice:
o a) Write an SQL statement to create a table Employee with the following
attributes: EmpID (integer, primary key), EmpName (varchar, max 50, not
null), Department (varchar, max
20), Salary (decimal(10,2)), JoiningDate (date).
o b) Add a new column PhoneNumber (varchar(15)) to the Employee table.
o c) Define a foreign key constraint on the Department column referencing
a DeptID in a Department table (assume the Department table exists).
2. DML Practice: Use the Employee table from Q1.
o a) Insert 3 sample records into the Employee table.
o b) Increase the salary of all employees in the 'IT' department by 10%.
o c) Delete the record of an employee named 'Rahul Sharma'.
o d) Update the department of an employee with EmpID = 101 to 'HR'.
3. Basic Queries:
o a) Write a query to display all employees whose salary is greater than 50000.
o b) Write a query to find the names of employees who joined after '2023-01-
01'.
o c) Write a query to count the number of employees in each department.

Assignment 3: SQL Functions and Set Operations

Objective: To gain proficiency in using built-in functions and set operations in SQL.
Total Questions: 3
Duration: 3 Hours
1. String and Date Functions: Consider an Employee table.
o a) Write a query to display employee names in uppercase.
o b) Write a query to extract the year of joining from the JoiningDate for all
employees.
o c) Write a query to list employee names and their salaries, displaying the
salary formatted with a comma (e.g., 50,000.00).
2. Aggregate Functions:
o a) Find the total salary expenditure of the company.
o b) Find the average, minimum, and maximum salary in the 'Sales' department.
o c) List departments that have more than 5 employees.
3. Set Operations: Consider two tables: Students_CSE and Students_IT. Both have the
same structure: (StudentID, Name).
o a) Write a query to find the names of students who are in either CSE or IT
(Union).
o b) Write a query to find the names of students who are common to both CSE
and IT (Intersect).
o c) Write a query to find the names of students who are in CSE but not in IT
(Minus).

Assignment 4: Entity-Relationship Modeling

Objective: To design a database using E-R diagrams and convert them into a relational
schema.
Total Questions: 2
Duration: 3 Hours
1. Scenario: A library system has the following requirements:
o Books are identified by an ISBN, and have a title, and publication year.
o Authors are identified by an AuthorID, and have a name.
o A book can have multiple authors, and an author can write multiple books.
o Members are identified by a MemberID, and have a name and phone number.
o A member can borrow multiple books, and a book can be borrowed by
different members at different times. The system must record the date
borrowed and the date returned.
o Draw an Entity-Relationship (E-R) diagram for this library management
system. Clearly indicate entities, attributes, primary keys, relationship sets,
and cardinality ratios (e.g., one-to-many, many-to-many).
2. Reduction to Relational Schema:
o Convert the E-R diagram from Question 1 into a set of relational schemas.
o Underline the primary key in each relation and use arrows to indicate foreign
key constraints.

Assignment 5: Functional Dependencies and Normalization

Objective: To understand and apply the concepts of functional dependencies and
normalization.
Total Questions: 3
Duration: 3 Hours
1. Consider a relation R (A, B, C, D, E) with the following set of functional
dependencies:
F = { AB → C, C → D, D → E }
o a) Find the closure of (AB).
o b) Is the dependency AB → E implied by F? Justify your answer using the
closure set.
o c) Identify all candidate keys for relation R.
2. **Consider a relation Student_Course (StudID, StudName, CourseID, CourseName,
Grade)`.
o a) Identify all the functional dependencies present in this relation.
o b) What is the highest Normal Form (1NF, 2NF, 3NF) that this relation is in?
Justify your answer by explaining the anomalies it suffers from.
o c) Decompose the relation into a set of relations that are in 3NF. Ensure the
decomposition is lossless and preserves dependencies.
3. Normalization Process:
o A relation Order_Details (OrderID, ProductID, ProductName, Category,
Quantity, Price) has the following FDs:
▪ OrderID, ProductID → Quantity
 ▪ ProductID → ProductName, Category, Price
o a) Is this relation in 2NF? If not, explain why and then decompose it into 2NF.
o b) Are the resulting relations from (a) in 3NF? If not, decompose further to
achieve 3NF.

Activity-2. Technical Video Based Learning Related to the Subject

Description:
Students will be required to watch curated technical video lectures from reputable online
platforms (like NPTEL, as mentioned in the syllabus) on specific, advanced DBMS topics.
Following the video series, they will prepare a comprehensive report and a presentation
summarizing their learning, demonstrating a deeper understanding of the subject matter.
Application in DBMS:
• Suggested Video Series: NPTEL's "Introduction to Database Systems" by Prof.
Sreenivasa Kumar, IIT Madras.
• Focus Topics: Students can choose a module that aligns with or goes beyond the
syllabus, such as:
o In-depth working of Query Optimization algorithms.
o Advanced Transaction Processing and Recovery techniques.
o Implementation details of Indexing and Hashing.
o Emerging trends like NoSQL Databases or Database Security in Cloud
Environments.
• Outcome: The activity bridges the gap between textbook theory and expert delivery,
providing different perspectives and deeper insights.
Implementation Plan:
• Video Duration: Approximately 5 hours of curated content.
• Report & Presentation: 10 hours for synthesizing information, preparing a report,
and creating presentation slides.
• Total Hours: 15 hours.
• Evaluation Criteria: Assessment will be based on:
o Report Quality: Depth of understanding, clarity of explanation, and structure.
o Presentation Skills: Ability to communicate key learnings effectively.
o Q&A Session: Understanding demonstrated during the question-and-answer
period after the presentation.
Activity-3. Real World Case Studies-Based Learning
Description:
This activity requires students to investigate and analyze how database management systems
are implemented and used in real-world organizations. They will select a business domain,
identify its data requirements, and study the corresponding database design, challenges faced
(e.g., scalability, security), and solutions implemented.
Application in DBMS:
• Potential Case Study Domains:
 o E-commerce: Analyzing database schema for product catalog, customer data,
transactions, and inventory management.
o Banking System: Studying the database design for handling accounts, loans,
transactions, and ensuring ACID properties.
o University Management System: Investigating tables for students, courses,
grades, and faculty, focusing on relationships and integrity constraints.
o Hospital Management System: Analyzing patient records, appointment
scheduling, and billing systems.
• Tasks: Students will collect information (through online research or hypothetical
modeling) on the Entity-Relationship model, schema, key tables, and specific
challenges like handling large volumes of data or ensuring data privacy.
Implementation Plan:
• Data Collection/Study: 5 hours for research and information gathering.
• Report Preparation: 10 hours for analyzing the data, connecting it to DBMS
concepts learned in class, and drafting a detailed case study report.
• Total Hours: 15 hours.
• Evaluation Criteria: The case study will be evaluated based on:
o Technical Depth: Application of relevant DBMS concepts (E-R Modeling,
Normalization, SQL, Transactions, etc.).
o Analysis and Insight: Quality of observations and the ability to link theory to
practical scenarios.
o Report Structure and Clarity: Organization, readability, and professionalism
of the submitted report.
o Fact-Finding: The extent and relevance of the information gathered.