COURSEPACK

SCHEME
The scheme is an overview of the learning pattern of the core subject of the Computer Science
domain and gets students into the real-world scenario of the problem domain.

Course Title Database Management System Course Type Comprehensive

Course Code E2UC302B Class B. Tech Core and

All specialization-
III Semesters
Activity Cred Week Total Number of Classes per Assessme
its ly
Semester nt in
Hour
Weightag
s
e
Instruction Lecture 3 3
delivery
Tutorial C S
0 0 Theor T Self-
Practic I E
y ut Learni
Practical 1 2 al E E
ng
Self- 0 7
Learning
Total 4 12 45 0 30 105 5 50
0 %
%
Course Lead: - Dr. Ashish Sharma
Names of Course Coordinator: - Mr. Akhilesh Tripathi
Course
Coordinato Theory Practical
rs and Dr. Gulshan Shrivastava Dr. Gulshan Shrivastava
Instructors Mr. A. Booblan Mr. A. Booblan
Mr. Amit Kumar Mr. Amit Kumar
Mr. Arunendra Mani Tripathi Mr. Arunendra Mani Tripathi
Mr. Arvindam M Mr. Arvindam M
Dr. Ashish Sharma Dr. Ashish Sharma
Mr. B. Thillaieswaran Mr. B. Thillaieswaran

Mr. C. Ramesh Kumar Mr. C. Ramesh Kumar

Ms. Garima Pandey Ms. Garima Pandey
Dr. Harshvardhan Choudhary Dr. Harshvardhan Choudhary
Ms. Indravati Ms. Indravati
Mr. K Bhaskar Mr. K Bhaskar
Mr. Kalemoor Rehman Mr. Kalemoor Rehman
Mr. Anas Khan Mr. Anas Khan
Ms. Nidhi Agarwal Ms. Nidhi Agarwal
Mr. Nitin Jain Mr. Nitin Jain
Ms. Pravesh Ms. Pravesh
Dr. S. Janarthanan Dr. S. Janarthanan
Dr. S. Premkumar Dr. S. Premkumar
Dr. Satheesh Kumar Dr. Satheesh Kumar
Ms. Shalakha Tyagi Ms. Shalakha Tyagi
Ms. Deepika Kataria Ms. Deepika Kataria
Mr. Ashwini K Pradhan Mr. Ashwini K Pradhan
Mr. Akhilesh K Singh Mr. Akhilesh K Singh
Mr. Akhilesh K Tripathi Mr. Akhilesh K Tripathi
Ms. Swati Sharma Ms. Swati Sharma
 COURSE OVERVIEW

This course introduces the core principles and techniques required to design and implement database systems.
This course focuses on relational database management systems, including database design theory: E-R
modeling, data definition, and manipulation languages, database security, and administration. It also covers
essential DBMS concepts such as Transaction Processing, Concurrency Control, and Recovery, various types of
databases like distributed databases and, Client/Server. Students undertake a semester mini project to design and
build a simple database system and demonstrate competence with the fundamental tasks involved with
modeling, designing, and implementing a DBMS. It also provides students with theoretical knowledge and
practical skills using MySQL tool in the use of databases and database management systems in information
technology applications.

PREREQUISITE COURSE
PREREQUISITE COURSE REQUIRED No Fundamental computer knowledge
required

If, yes please fill in the details

Prerequisite course code Prerequisite course name

COURSE OBJECTIVES

The objective of this course is to deliver concepts of database management systems using a practical approach,
with an emphasis on how to design, populate, organize, maintain, and retrieve information from RDBMS efficiently.

COURSE OUTCOMES (COs)

After the completion of the course, the student will be able to

CO No. Course Outcome
E2UC302B.1 Design database schema using ER diagrams and convert it into Relational tables.
E2UC302B.2 Implement the DDL, DML, and DCL queries.
E2UC302B.3 Apply database Normalization techniques up to BCNF for the removal of anomalies.
E2UC302B.4 Perform the serializability and recoverability tests on transactions.
BLOOM’S LEVEL OF THE COURSE OUTCOMES

Bloom's taxonomy is a set of hierarchical models used for the classification of educational learning objectives into
levels of complexity and specificity. The learning domains are cognitive, affective, and psychomotor.

Blended Course

CO No. Rememb Understa Apply Analyze Evaluate Create

er nd KL3 KL4 KL5 KL6
KL1 KL2
E2UC302B. √ √
1
E2UC302B. √
2
E2UC302B. √
3
E2UC302B. √
4
PROGRAM OUTCOMES (POs): PO1 Computing Science knowledge: Apply the knowledge of
mathematics, statistics, computing science, and information science fundamentals to the solution of computer
application problems.

PO2 Problem analysis: Identify, formulate, review research literature, and analyze computing science problems
reaching substantiated conclusions using the first principles of mathematics, natural sciences, and computer sciences.

PO3 Design/development of solutions: Design solutions for complex computing problems and design system
components or processes that meet the specified needs with appropriate consideration for public health and safety,
and the cultural, societal, and environmental considerations.

PO4 Conduct investigations of complex problems: Use research-based knowledge and research methods including
design of experiments, analysis and interpretation of data, and synthesis of the information to provide valid
conclusions.

PO5 Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern computing science
and IT tools including prediction and modeling to complex computing activities with an understanding of the
limitations.

PO6 IT specialist and society: Apply reason informed by the contextual knowledge to assess societal, health,
safety, legal and cultural issues and the consequent responsibilities relevant to the professional computing science
and information science practice.

PO7 Environment and sustainability: Understand the impact of professional computing science solutions in
societal and environmental contexts, and demonstrate the knowledge of, and need for sustainable development.

PO8 Ethics: Apply ethical principles and commit to professional ethics and responsibilities and norms of the
computing science practice.

PO9 Individual and team work: Function effectively as an individual, as a member or leader in diverse teams and
multidisciplinary settings.

PO10 Communication: Communicate effectively on complex engineering activities with the IT analyst community
and with society at large, such as being able to comprehend and write effective reports and design documentation,
make effective presentations, and give and receive clear instructions.

PO11 Project management and finance: Demonstrate knowledge and understanding of computing science and
management principles and apply these to one’s work, as a member and leader in a team, to manage projects and in
multidisciplinary environments.

PO12 Life-long learning: Recognize the need for, and have the preparation and ability to engage in independent and
life-long learning in the broadest context of technological change.
Program Specific Outcomes (PSO’s)
PSO1: Have the ability to work with emerging technologies in computing requisite to Industry 4.0.
PSO2: Demonstrate Engineering Practice learned through industry internship and research projects to solve live problems
in various domains.

COURSE ARTICULATION MATRIX

The Course articulation matrix indicates the correlation between Course Outcomes and Program Outcomes and their
expected strength of mapping in three levels (low, medium, and high).

COs#/
P PO2 PO P PO PO PO PO PO PO PO PO PS PSO2
POs O1 3 O4 5 6 7 8 9 10 11 12 O1
2 2 1
E2UC302
1 2
B.1
2 1
E2UC302
1 2 2
B.2
2 2 1
E2UC302
1 2
B.3
E2UC302 2 1
2 2 2
B.4

Note: 1-Low, 2-Medium, 3-High

COURSE ASSESSMENT

The course assessment patterns are the assessment tools used both in formative and summative examinations.

* Assignment, Quiz, Class test

COURSE CONTENT

THEORY

Introduction

Introduction: An overview of the database management system, database system Vs file system, Database
system concept and architecture, data model schema and instances, data independence and database
language and interfaces, data definitions language, DML, and Overall Database Structure.
Relational Data Model and Language
Relational data model concepts, integrity constraints, entity integrity, referential integrity, Key
constraints, Domain constraints, relational algebra, relational calculus, tuple, and domain calculus.
Introduction to SQL: Characteristics of SQL, the advantage of SQL. SQL data type and literals. Types of
SQL commands. SQL operators and their procedures. Tables, views, and indexes. Queries and
subqueries. Aggregate functions. Insert, update, and delete operations, Joins, Unions, Intersection, Minus,
Cursors, Triggers, and Procedures in SQL/PL SQL
Database Design & Normalization
Functional dependencies, normal forms, first, second, and third normal forms, BCNF, inclusion
dependence, lossless join decompositions, normalization using FD, MVD, and JDs, and alternative
approaches to database design.
Transaction Processing Concept
Transaction system, testing of serializability, serializability of schedules, conflict & view serializable
schedule, recoverability, Recovery from transaction failures, log-based recovery, checkpoints, deadlock
handling. Distributed Database: distributed data storage, concurrency control, directory system.
Concurrency Control Techniques
Concurrency control, Locking Techniques for concurrency control, Time stamping protocols for
concurrency control, validation-based protocol, multiple granularities, multi-version schemes, Recovery
with concurrent transactions, MySQL databases, NoSQL databases, Recent trends in domain.
PRACTICAL

Content

Draw an E-R diagram and convert entities and relationships to a relation table for a given scenario.
(Two assignments shall be carried out i.e., consider two different scenarios (e.g., bank, college)
.
Perform the following on MySQL:
a. Viewing all databases, Creating a Database,
b. Viewing all Tables in a Database, Creating simple tables
c. Inserting/Updating/Deleting Records in Table
d. Saving (Commit and Undoing (rollback)
Perform the following:
a. Altering a Table, Dropping/Truncating/Renaming Tables.
b. Adding a column, Changing column data type, size
c. Dropping a column
d. Creating Tables (With and Without Constraints (Key/Domain)
e. Creating Tables (With Referential Integrity Constraints)
For a given set of relation schemes, create tables and perform the following Queries:
a. Simple Queries with Aggregate Functions
b. Queries with Aggregate functions (Max/Min/Sum/Avg/Count)
c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions
For a given set of relation schemes, create tables and perform the following Queries:
a. Inner Join
b. Outer Join Subqueries- With IN clause, With EXISTS clause
For a given set of related tables perform the following: -
a. Creating Views
b. Dropping views
c. Selecting from a view

Write a Pl/SQL program using a FOR loop to insert ten rows into a database table.

Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a cursor to select the five highest-paid
employees from the table.

For a given set of related tables perform the following:

a. Begin Transactions
b. End Transaction
For a given set of related tables perform the following:
a. Create roles
b. Assign Privileges
c. Revoke Privileges

Demonstrate the working of different types of Triggers.

Using the Xampp server package, create usernames/passwords & connect it to MySQL community edition.
 LESSON PLAN FOR THEORY COURSES (15 weeks * 3 Hours = 45 Classes)

SN T Topics for Delivery Skills Competency

/
1 L Overview of the database Identify the CO1
management system elementary concepts
T of
2 T Database system Vs file File Management
system systems, DBMS and
3 T Database system concept and Data Models.
architecture
4 T Data model schema and
instances
5 T Data model schema and
instances
6 L Draw an E-R diagram and
convert
entities and relationships to a
relation table for a given
scenario.
7 T Data independence Ability to gain CO1/CO2
RDBMS knowledge
8 T Database language and and SQL queries.
interfaces,
9 L Installing MySQL & Xampp on
the windows machine and
creating the first Database.
10 T DDL, DML, DCL

11 L Viewing all databases, Creating a

Database, Viewing all Tables in
a
Database, Creating simple tables

12 T Overall Database Structure

13 T RDBMS concepts, Integrity

constraints, entity integrity,
referential integrity
14 L Inserting/Updating/Deleting
Records
in Table Saving (Commit,),
Undoing
(rollback)
15 T Key constraints, Domain
constraints
16 T Relational algebra Competence to CO1
formulate Relational
17 T Relational Calculus Algebra & Relational
 Calculus queries

18 T SQL data type and literals. Describe the CO2

Types of SQL commands working of SQL with
different clauses &
designing keys &
19 T Characteristics of SQL, constraints.
advantages of SQL

20 T Creating Tables (Key/Domain)

Creating Tables with
Referential Integrity

21 L Dropping/Truncating/Renaming
Table, Adding a column,
Changing, column data type,
size,
Dropping column
22 T SQL operators and their Writing & CO2
procedure Evaluating SQL
23 T Tables, views, and indexes. queries based on
24 T Aggregate functions tables, views,
Indexes, Aggregate
25 L Queries with Aggregate Functions and joins
functions
Queries with group by and
having
Queries involving- Date, String,
26 T Insert, update, and delete
operations
27 T Joins, Unions, Intersection,
Minus
28 L Inner & Outer Join With
IN/EXISTS clause

29 T Cursors, Triggers Ability to CO2/CO3

30 T Procedures in SQL/PL SQL formulate
Advanced SQL
31 T Functional dependencies queries and
32 L Creating, Dropping & Selecting understand the
data from a view basics of DB
33 T Normal forms, first & design.
second
34 T Normal forms, Third &
BCNF
35 T Dependency Preservations Applying DB design CO3
36 T Lossless join decompositions concepts using
Dependencies,
37 L Pl/SQL program using FOR Lossless design, and
loop to insert ten rows into a understanding the
database table.
38 T Normalization using FD, MVD, basics of Transaction
and JDs processing
39 T Transaction system, ACID
properties
40 T Testing of serializability,
serializability of schedules
41 T Conflict & view serializable Classify and performing CO4
schedule Recoverability &
42 L For a given set of related tables deadlocks tests in
performs transactions
a. Begin Transactions
b. End Transaction

43 T Recoverability, Recovery from

transaction failures
44 T Log-based recovery and
checkpoints
45 T Deadlock handling in
Transaction processing
46 T Distributed Databases,
distributed data storage &
Directory system
47 T Concurrency control, Locking Developing CO4
Techniques for concurrency knowledge of
control different
48 L For a given set of related tables Concurrency
performs protocols.
a. Create roles
b. Assign Privileges
c. Revoke Privileges
49 T Time stamping protocols
for concurrency control
50 T Validation-based protocol,
multiple granularities
51 T Multi-version schemes,
Recovery with the concurrent
transaction
52 L Write Trigger to insert the
deleted rows of table1 to table2.
53 T Case study of Oracle & Capturing the CO1/CO2
MySQL understanding about
54 T Case study NoSQL (MongoDB) recent tools and
technologies of the
55 L Given the table EMPLOYEE domain
(EmpNo,
Name, Salary, Designation,
DeptID) write a
Cursor to select the five highest-
paid
Employees from the table.

56 T Discussion on some of the latest

DB products available in the
market
57 T Discussion of some latest papers
published in IEEE Transactions
and ACM transactions
58 T Discussion of some latest papers
published in Web of Science and
SCOPUS-indexed journals
59 L Using the Xampp server, create
usernames/passwords & connect it
to
MySQL.
60 T Study of advances, the latest
trends in the course, and the latest
applications of the domain.

LAB PLAN FOR THEORY COURSES (15 weeks * 2 Hours = 30 Classes)

1 L Draw an E-R diagram and convert entities and relationships to a relation table for a
given scenario.
(Two assignments shall be carried out i.e., consider two different scenarios (e.g. bank,
College)

2 L Installing MySQL & Xampp on the windows machine and creating the first Database.
3 L Perform the following on MySQL:
a. Viewing all databases, Creating a Database,
b. Viewing all Tables in a Database, Creating simple tables

4 L Perform the following on MySQL:

a. Inserting/Updating/Deleting Records in Table
b. Saving (Commit,) and Undoing (rollback)

5 L Perform the following:

a. Altering a Table, Dropping/Truncating/Renaming Tables.
b. Adding a column, Changing column data type, size
 c. Dropping a column

6 L Perform the following:

a. Creating Tables (With and Without Constraints (Key/Domain)
b. Creating Tables (With Referential Integrity Constraints)

7 L For a given set of relation schemes, create tables and perform the following Queries:
a. Simple Queries
b. Queries with Aggregate functions (Max/Min/Sum/Avg/Count)
c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions

8 L For a given set of relation schemes, create tables and perform the following Queries:
a. Inner Join
b. Outer Join Subqueries- With IN clause, With EXISTS clause

9 L . For a given set of related tables perform the following: -

a. Creating Views
b. Dropping views
c. Selecting from a view
10 L Write a Pl/SQL program using FOR loop to insert ten rows into a database table.

11 L Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a
cursor to select the five highest-paid employees from the table.

12 L For a given set of related tables perform the following:

a. Begin Transactions
b. End Transaction

13 L For a given set of related tables perform the following:

a. Create roles
b. Assign Privileges
c. Revoke Privileges
14 L Write Trigger to insert the deleted rows of table1 to table2.

15 L Using Xampp server, create usernames/passwords & connect it to MySQL community

Edition
 BIBLIOGRAPHY

Text Book

1. Henry F Korth, Abraham Silberschatz, S. Sudarshan, “Database system concepts”, McGraw- Hill

2. Date C J, “An Introduction to Database Systems”, Addison Wesley

Reference Books

1. Elmasri, Navathe, “Fundamentals of Database Systems”, Addison Wesley

2. O’Neil, Databases, Elsevier Pub.
3. Leon & Leon,” Database Management Systems”, Vikas Publishing House
4. Bipin C. Desai, “An Introduction to Database Systems”, Gagotia Publications
5. Majumdar & Bhattacharya, “Database Management System”, TMH (14)
6. Ramakrishnan, Gehrke, “Database Management System”, McGraw Hill

Journals/Magazines/Govt. Reports/Gazette/Industry Trends

1. Journal of Database Management, Published by IGI Global, Online ISSN: 1533-8010

2. https://dl.acm.org/journal/jdbm
3. https://computers.stmjournals.com/index.php?journal=JoADMS

SWAYAM/NPTEL/MOOCs Certification

1. Database Management System, By Prof. Partha Pratim Das & Prof. Samiran Chattopadhyay, IIT Kharagpur
https://onlinecourses.nptel.ac.in/noc20_cs60/preview

2. DML Statements and SQL Server Administration | Online | Alison

https://alison.com/topic/learn/71105/using-dml-statements-learning-outcomes

3. SQLCourse: Beginner & Advanced Interactive SQL Tutorials

4. https://academy.oracle.com/
PRACTICE PROBLEMS-

S. Problem
No
1 Draw an ER diagram for the Hospital database. Be sure to indicate the various attributes of each entity
and relationship set; also specify the key and participation constraints for each relationship set. Specify
any necessary overlap and cover constraints as well.
2 Consider the instance of the sample Students' relation.
1. Give an example of an attribute (or set of attributes) that you can deduce is not a candidate key,
based on this instance being legal.
2. Is there any example of an attribute (or set of attributes) that you can deduce is a candidate key,
based on this instance being legal?
3 Consider the relations between Students, Faculty, Courses, Rooms, Enrolled, Teaches, and Meets.
1. List all the foreign key constraints among these relations.
2. Give an example of a constraint involving one or more of these relations that is not a primary key or
foreign key constraint.
4 Write SQL for the following relational schema:
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)
5 Suppose that we have a ternary relationship R between entities sets A, B, and C such that A has a key
constraint and total participation and B has a key constraint; these are the only constraints. A has
attributes a1 and a2, with a1 being the key; B and C are similar. R has no descriptive attributes. Write
SQL statements that create tables corresponding to this information to capture as many of the
constraints as possible. If you cannot capture some constraint, explain why?
6 Consider the following relations containing airline flight information:
Flights (flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time)
Aircraft (aid: integer, aname: string, cruisingrange: integer) Certified(eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: integer)
1. Find the eids of pilots certified for some Boeing aircraft.
2. Find the names of pilots certified for some Boeing aircraft.
3. Find the aids of all aircraft that can be used on non-stop flights from Bonn to Madras. 4. Identify the
flights that can be piloted by every pilot whose salary is more than $100,000.
5. Find the names of pilots who can operate planes with a range greater than 3,000 miles but are not
certified on any Boeing aircraft.
6. Find the eids of employees who make the highest salary.
7. Find the eids of employees who make the second highest salary.
8. Find the eids of employees who are certified for the largest number of aircraft.
9. Find the eids of employees who are certified for exactly three aircraft.
10. Find the total amount paid to employees as salaries.
7 Consider the following relational schema.
An employee can work in more than one department; the pct time field of the Works relation shows
the percentage of time that a given employee works in a given department.
Emp (eid: integer, ename: string, age: integer, salary: real)
Works (eid: integer, did: integer, pct time: integer)
 Dept (did: integer, budget: real, managerid: integer)
Write the following queries in SQL:
1. Print the names and ages of each employee who works in both the Hardware department and the
Software department.
2. For each department with more than 20 full-time-equivalent employees (i.e., where the part-time
and full-time employees add up to at least that many full-time employees), print the did together with
the number of employees that work in that department.
3. Print the name of each employee whose salary exceeds the budget of all of the departments that he
or she works in.
4. Find the managerids of managers who manage only departments with budgets greater than
$1,000,000.
5. Find the enames of managers who manage the departments with the largest budget.
8 Consider the instance of Sailors
Let us define instance S1 of Sailors to consist of the first two tuples, instance S2 to be the last two
tuples, and S to be the given instance.
(a) Show the left outer join of S with itself, with the join condition being sid=sid.
(b) Show the right outer join of S with itself, with the join condition being sid=sid.
(c) Show the full outer join of S with itself, with the join condition being sid=sid.
(d) Show the left outer join of S1 with S2, with the join condition being sid=sid.
(e) Show the right outer join of S1 with S2, with the join condition being sid=sid.
(f) Show the full outer join of S1 with S2, with the join condition being sid=sid.
9 Draw E-R diagram of following pair of entities (i)Customer & Account (ii)
Customer & Loan (iii) Doctor & Patient

10 Draw an ER diagram of the Hospital Management System.

(i) Take only 2 entities

(ii) Use all types of attributes
(iii) Use proper relationship

11 Find the minimum number of tables required for the following ER diagram in relational
model-

12 Find the minimum number of tables required to represent the given ER diagram in
relational model-
13 Write the following queries in SQL, using the university schema. (We suggest
you actually run these queries on a database, using the sample data
that we provide on the Web site of the book, db-book.com. Instructions for
setting up a database, and loading sample data, are provided on the above
Web site.)
a. Find the titles of courses in the Comp. Sci. department that have 3
credits.
b. Find the IDs of all students who were taught by an instructor named
Einstein;make sure there are no duplicates in the result.
c. Find the highest salary of any instructor.
d. Find all instructors earning the highest salary (there may be more
than one with the same salary).
e. Find the enrollment of each section that was offered in Autumn 2009.
f. Find the maximum enrollment, across all sections, in Autumn 2009.
g. Find the sections that had the maximum enrollment in Autumn 2009.
14 Write Queries for the following requirements:
(i) Find the customer names and their loan numbers for all customers having a loan at
some branches.
(ii) Find the names of all branches that have greater assets than some branch located
in Lucknow.
(iii) Find the names of all customers whose street includes the substring “main”.
(iv) List in alphabetic order the names of all customers having a loan in “Knit
Campus” branch.
(v) Find all customers who have a loan, an account, or both.
(vi) Find all customers who have both a loan and an account.
(vii) Find all customers who have an account but no loan.
(viii) Find all customers who have both an account and a loan at the same branch.

15 Write Queries for the following requirements:

(i) Find all customers who have either an account or a loan (but not both) at the bank.
(ii) Find all branches where the total account deposit is greater than the average of the
total account deposits at all branches.
(iii) Find the average account balance of those branches where the average account
balance is greater than 100000.
(iv) Account number and name of customer whose account balance is highest.
(v) Find the name of the customer with the total balance, which is most valuable.
(vi) Find all customers who have a loan at the bank but do not have an account at the
bank
(vii) Find the name of customers who have an account at all the branches located in
sultanpur.
(viii) Find the name of all customer who have an account in all branches of NEW YORK
city.
(ix) Name of branches where total customers are more than 5.
(x) Name of customers whose branch city and city of living is the same.

16 Create a Trigger on the banking database on update, insert operation.

17 Create a Trigger on the banking database on modify, delete operation.

18 Draw an ER diagram of the Airlines Management System. Demonstrate keys, Constraints, and
 relationships. Be sure to indicate the various attributes of each entity and relationship set; also specify
the key and participation constraints for each relationship set. Specify any necessary overlap and cover
constraints as well.
19 Draw an ER diagram of the Blood Bank Management System. Demonstrate keys, Constraints, and
relationships. Be sure to indicate the various attributes of each entity and relationship set; also specify
the key and participation constraints for each relationship set. Specify any necessary overlap and cover
constraints as well.
20 Suppose a relational schema R(w x y z), and set of functional dependency as followings
F : { wx yz, y w, z x }
Find the candidate keys in above relation
21 Suppose a relational schema R(a, b, c, d, e), and set of functional dependency as follows
F : { ab cd, d a, bc de }
Find the candidate keys in the above relation.
22 Suppose that we have a relation marks (ID, score) and we wish to assign
grades to students based on the score as follows: grade F if score < 40,
grade C if 40 ≤ score < 60, grade B if 60 ≤ score < 80, and grade A if 80 ≤
score. Write SQL queries to do the following:
a. Display the grade for each student, based on the mark’s relation.
b. Find the number of students with each grade
23

Consider the bank database of Figure 2, where the primary keys are underlined. Construct the
following SQL queries for this relational database.

a. Find all customers of the bank who have an account but not a loan.
b. Find the names of all customers who live on the same street and in the same city as “Smith”.
c. Find the names of all branches with customers who have an account in the bank and who live in
“Harrison”
24 Let R = (A, B, C, D, E, F) be a relation scheme with the following dependencies-
C→F
E→A
EC → D
A→B
Find Candidate keys.
25 Consider the given schedule S and check if it is view serializable or not. If yes, then give the serial
Schedule: S : S1: R1(X) R1(Y) R2(X) R2(Y) W2(Y) W1(X).
26 Write SQL TRIGGER FOR database triggers to enforce data integrity and implement business rules.
27

a. Find the total number of people who owned cars that were involved in accidents in 2009.
b. Add a new accident to the database; assume any values for required attributes.
28 Consider the database available in the previous question & perform following queries.
a. Add a column to the table car.
b. Drop the column date from table accident
c. Modify the column location as location_Info
d. Rename table owns to owner
29

Consider the library database. Write the following queries in SQL.

a) Print the names of members who have borrowed any book published by “McGraw-Hill”.
b) Print the names of members who have borrowed all books published by “McGraw-Hill”.
c) For each publisher, print the names of members who have borrowed more than five books of
that publisher.
d) Print the average number of books borrowed per member. Take into account that if an
30 Create the following Database of a Bank with following relations
Branch(Branch_name , Branch_city , Assets)
Customer(Customer_name,Customer_street,Customer_city)
Account(account_number, Branch_name, Balance)
Loan( Loan_number, Branch_name, Amount)
Depositor(account_number,Customer_name)
Borrower(Loan_number, Customer_name)
Foreign keys:
(i) In Account table Branch name is referring to Branch table
(ii) In Loan table Branch name is referring to Branch table
(iii) In the Depositor table, the account number is referring to the account table and the
customer name is referring to the customer table.
(iv)In Borrower table Loan number is referring to Loan table and customer name is referring
to customer table.
Insert data in different tables.
STUDENT-CENTERED LEARNING (SELF-LEARNING TOWARDS LIFE-
LONG- LEARNING)

Self-Learning (it’s a typical course-based project to be carried out by a whole class in groups of four students
each; they should exhibit higher level BTLs)

The students, in a group, are expected to conceive an idea based on the content (objectives/outcomes) and apply
the suitable knowledge to demonstrate their learning.

A) COURSE-BASED PROJECT

To enhance their skill set in the integrated course, the students are advised to execute course-based design
projects. Some sample projects are given below:

SN Suggested Projects
o
1 Create a Database for registering a new user for the generation of electricity bills for a customer.

2 Design a Database for checking daily items sold by the Online Retail Application.

3 Design a database for the issue and return of items in Inventory Control Management.

4 Design a database to calculate fines on pending books in a Library Management System.

5 Design a database to check student’s fee details in Student Database Management.

6 Create a database for providing leave to employees in a Payroll Management System.

7 Design a database to check transport availability in a Voice-based Transport Enquiry System.

8 SMS-based Remote Server Monitoring System.

9 Design a database to book doctor appointments in a Hospital Management System.

10 Create a database to check the total of blood availability for a Blood Donation Management
System.
B) SELF-LEARNING THROUGH MOOCs (Cognitive Skills):

1. Database Management System, By Prof. Partha Pratim Das & Prof. Samiran Chattopadhyay, IIT Kharagpur
https://onlinecourses.nptel.ac.in/noc20_cs60/preview

2. DML Statements and SQL Server Administration | Online | Alison

https://alison.com/topic/learn/71105/using-dml-statements-learning-outcomes

3. SQLCourse: Beginner & Advanced Interactive SQL Tutorials

4. https://academy.oracle.com/