Annexure VIII

IC CSC 401: Database Management System for IV semester BTech ICE

A. SYLLABUS

Course No. Title of the Course Credits Course Pre-Requisite

Structure

IC CSC 401 Database Management 4 3L-0T-2P None

System

Unit Topics
No.
Unit 1 Introduction to database systems: Overview, File Systems Vs. DBMS,
Advantages of DBMS, Levels of Abstraction, Data Independence, Data
Models and their comparison (Hierarchical, Network, Relational Model).
Unit 2 Relational Data models: Structure of Relational Database, Integrity
Constraints over relations (Primary key, foreign key, unique, not null,
check), Enforcing Integrity Constraints, Relational Algebra and
Calculus, Introduction to SQL (DDL, DML, DCL), aggregate functions,
sub-queries, correlated sub-queries, Use of group by, having, order by,
join and its types, triggers, introduction to NoSQL database, difference
between SQL and NoSQL databases
Unit 3 Database Design: Top down approach (ER Model), Participation
Constraints, Specialization, Generalization and Aggregation,
Relationship Types, Relationship Sets, Roles, and Structural
Constraints, Weak Entity Types, Transformation of ER Schema to
relational tables
Unit 4 Normalization: Functional Dependencies, axioms, closure, Normal
forms based on Primary Keys, First, Second and Third Normal Forms,
BoyceCodd Normal Form, lossless and dependency preserving,
Converting relation into required Normal Form
Unit 5 Transactions: Introduction, ACID property, types of schedule,
concurrent versus parallel execution, Typical concurrency problems
(The Lost Update Problem, Dirty Read Problem, Non-repeatable Read,
Phantom Read), conflict serializability, Concurrency Control (Lock based
protocols: 2PL, strict 2PL, rigorous 2PL) and Database Recovery,
Database Security

Approved in BOS held on 19-11-24

Suggested Readings:
1. Abraham Silberschatz, Henry F. Korth, S. Sudharshan, “Database System
Concepts”, Tata McGraw Hill.
2. Ramez Elmasri, Shamkant B. Navathe, “Fundamentals of Database
Systems”, Pearson / Addision wesley.
3. C.J. Date, A. Kannan, S. Swamynathan, “An Introduction to Database
Systems”, Pearson Education.
4. Raghu Ramakrishnan, “Database Management Systems”, McGraw Hill.
5. S.K. Singh, “Database Systems Concepts, Design and Applications”,
Pearson Education.

CO-PO MAPPING TABLE

P PO2 PO PO PO PO P PO PO PO10 PO PO12

O 3 4 5 6 O7 8 9 11
1
CO1 1 2 1 1 1 0 1 0 2 0 2 1
CO2 2 1 1 2 3 1 0 0 1 2 1 1
CO3 1 0 2 1 2 0 0 1 1 1 1 2
CO4 1 1 1 2 1 0 1 0 1 2 1 1
CO5 2 0 0 1 1 1 0 0 2 1 2 3

Approved in BOS held on 19-11-24

C. THEORY LECTURE PLAN
S.No CONTENT NUMBER Unit
.
OF

LECTURE
S
1 Introduction to database systems: Overview 1 UnitI

2 File Systems Vs. DBMS, Advantages of DBMS 1 (4)

3 Levels of Abstraction, Data Independence 1

4 Data Models and their comparison (Hierarchical, 1
Network, Relational Model)
CLASS TEST-I
5 Relational Data models: Structure of Relational 2 UnitII
Database, Integrity Constraints over relations
(Primary key, foreign key, unique, not null, (13)
check), Enforcing Integrity Constraints
6 Relational Algebra and Calculus 2
7 Introduction to SQL (DDL, DML, DCL) 1
8 Aggregate functions 1
9 sub-queries, correlated sub-queries 1
10 Use of group by, having, order by 1
11 Join and its types, triggers 3
12 Introduction to NoSQL database, difference 2
between SQL and NoSQL databases
MID SEMESTER EXAMS
13 Database Design: Top down approach (ER 2 UnitIII
Model), Participation Constraints, and
Structural Constraints, Weak Entity Types (6)

14 Specialization, Generalization and Aggregation 1

15 Relationship Types, Relationship Sets, Roles 1
16 Transformation of ER Schema to relational 2
tables
CLASS TEST-II
17 Nor malization: Functional Dependencies, 2 UnitI
axioms, closure V

(9)

Approved in BOS held on 19-11-24

18 Normal forms based on Primary Keys, First, 2 (9)
Second Normal form
19 Third Normal Forms, BoyceCodd Normal Form 2

20 Lossless and dependency preserving 1

21 Converting relation into required Normal Form 2

22 Transactions: Introduction, ACID property 1 UnitV

23 Types of schedule, concurrent versus parallel 1 (9)

execution
24 Typical concurrency problems (The Lost Update 2
Problem, Dirty Read Problem, Non-repeatable
Read, Phantom Read)
25 Conflict serializability 1
26 Concurrency Control (Lock based protocols: 2
2PL, strict 2PL)
27 Database Recovery, Database Security 2

Approved in BOS held on 19-11-24

D. PRACTICAL CLASS PLAN
Lab Name of the Experiment
Class
No.
1 NOTE: Ensure all the integrity constraints are applied while creating
the relations. Also write relational algebra queries, wherever possible.

For the given database, write SQL queries and corresponding relational
algebra queries (wherever possible) :

Author (aid, authorname, citizenship, birthyear),Book(aid, isbn, title,

authorname),Topic(isbn, subject),Branch(libname, city),Instock(isbn,
libname, quantity)

a. Give all authors born after 1940.

b. Give the names of libraries in Sydney.

c. Give the cities where each book is held.

2 For the database in question 1, write SQL queries and relational algebra
queries (wherever possible):

a. Give the title of each book on the topic of either alcohol or drugs.

b. Give the title and author of each book of which at least two copies
are held in a branch located in Melbourne.

c. Give the name of each Italian author who wrote an autobiography.

3 For the database in question 1, write SQL queries :

a. Give the total number of books in stock in the branch called Fisher.

b. This could mean the number of different titles, or the number of

physical copies.

c. Give the total number of books in stock in the branches located in

Sydney.

Approved in BOS held on 19-11-24

4 Create the following relations in SQL. Add a constraint in the ‘SALES’
relation that payment mode can only be ‘COD’ and ‘PAYPAL’.
(10)

CUSTOMER (CUST_ID, CUST_NAME, CUST_ADD)

SALES (#CUSTOMER_ID, ITEM_ID, PAYMENT_MODE)

For the relations created, write a query to insert data into the relations.
Insert 5 rows in each relation. Write a query to alter the data type of
CUST_ADD from varchar to char. Show the use of DDL triggers in
response to DDL events.
5 NOTE: Ensure all the integrity constraints are applied while creating
the relations. Also write relational algebra queries, wherever possible.

For the given database, write SQL queries to create the following
database:

Flights(flno, from, to, distance, departs),Aircraft(aid, aname,

range),Certified(eid, aid),Employees(eid, ename, salary)

By definition, pilots are those employees who are certified on at least

one aircraft. An aircraft can be used for any flight provided it has
sufficient range. Pilots can pilot any flight provided they are certified on
an aircraft with sufficient range.

a.Find eid’s of pilots who are certified on some Boeing.

b.Find names of pilots who are certified on some Boeing.

c. Find aid’s of aircraft that can fly non-stop from LA to NY. Assume
you don’t already know the distance.
6 For the database created in ques 5, perform the following queries:

a.Find names of pilots who can operate planes with a range greater than
3,000 miles, but are not certified on any Boeing.

b.Find eid of employee(s) with the highest salary.

c. Write a query to change the size of the column, ‘salary’.

Approved in BOS held on 19-11-24

 7 For the database created in ques 5, perform the following queries:

a.Find eid of employee(s) with the second highest salary.

b.Find eid’s of employees certified on exactly three aircraft.

MID SEMESTER EVALUATION
8 Create a concurrent schedule and show if it is conflict serializable.
9 Create a concurrent schedule and change it into an equivalent serial
schedule. Write statements to check if the two schedules are equivalent
or not.
10 Create a transaction to demonstrate the use of locks in all the 4 modes.
11 Create a project in group of 3 to show the functioning of any of the real
life software systems, for example, blood donation system, inventory
management system, hospital management system etc. Create a detailed
ER model to show the design of the system, followed by relations to
store data. The relations should be in requisite normal forms. Write the
highest normal form of each relation. Implement the system using
NoSQL database (Mongo Database).
END SEMESTER EVALUATION

E. SELF STUDY

Sr. Topic
No.

1. To study different types of databases in SQL such as ORACLE, Unit I, II,III

MySQL, SQLite, MongoDB.

2. Study the use and applicability of higher normal forms and Unit IV
multivalued dependencies. Convert a set of relations to 4th Normal
Form.

3 Study different types of indexing and hashing functions. Unit V

Create and use an index inside a transaction.

Approved in BOS held on 19-11-24