CSL214 Database Management Systems

COURSE TEMPLATE
1. Department:
Department of Computer Science and Engineering
3. Course Code 4. L-T-P 5. Credits
2. Course Name: Database Management
Systems CSL214 3-0-2 4
6. Type of Course
(Check one): Programme Core ✔ Programme Elective Open Elective

7. Prerequisite(s), if any: None

8. Frequency of offering (check one):Odd Even ✔ Either semester Every semester

9. Focus: Employability ✔ Entrepreneurship Skill Development Basic Knowledge ✔

10. Brief Syllabus:

Databases form the core of all major applications – finance, social, administrative, education etc. Organizations work
on large volumes of data every day, introducing the need of database management systems to easily identify, extract,
store and transform details in the database. This course will explore concepts and principles of DBMS, database
design, data modeling, database implementation, and database management through various assignments and
experiments. By the end of this course, the student will be able to work as a database engineer by designing,
developing and maintaining the database for any project application.

Total Lecture, Tutorial and Practical Hours for this course (Take 15 teaching weeks per semester): 75

Practice

Lectures: 45 hours Tutorial: 0 hours Lab Work: 30 hours

11. Course Outcomes (COs)
Possible usefulness of this course after its completion i.e. how this course will be practically useful to him once it
is completed.

Identify contrast between traditional and modern Database Systems, thereby recognize their applications
CO 1
through case studies.
Develop conceptual database design for any real time project by defining the relationship, constraints
CO 2
etc. on entities.
Apply appropriate design techniques and design a good database that meets the user requirement and
CO 3
enhance back-end skill set.
Create a database and devise queries for extracting information from the database using Relational
CO 4
Algebra and SQL.
CO 5 Apply the concepts of DBMS for developing backend of a project using NoSQL.
CO 6 Improvise data fetching time and apply indexing concepts.
CSL214 Database Management Systems

CO 7 Illustrate the concepts of end-to-end transaction processing in a database.

12. UNIT WISE DETAILS No. of Units: 7

Unit Number: 1 Title: Introduction to Database Systems No. of hours: 4

Content Summary:
Overview of Database Management Systems, Advantages of DBMS over File Processing Systems, DBMS Vs.
RDBMS, DBA roles and responsibilities, Data Independence, Architecture of Database(3-Schema Architecture,
Complete architecture), Database Query Languages (DDL, DML, DCL), Relational Model Concepts: Primary Key,
Unique key, Foreign key, Super Key, Alternate key, Candidate key, Constraints used in Relational Data Model
including integrity constraints.

Unit Number: 2 Title: Conceptual Database Design No. of hours: 8

Content Summary:
Data Modeling Using the Entity Relationship (ER) Model, The Enhanced Entity-Relationship (EER) Model: Entity Set,
attributes and their types, Relationship Constraints (including Participation constraints and cardinality ratio), ER
Diagrams, constraints and design issues, Reduction of ER and EER diagram to relational schemas.

Unit Number: 3 Title: Relational Database Design No. of hours: 8

Content Summary:
Relational database design, Functional dependencies: Fully functional dependency, partial FD, trivial, non-trivial FD,
inference rules, canonical cover, lossless join, dependency preservation, multivalued dependency, Normal Forms:
1NF, 2NF, 3NF, BCNF, 4NF, 5NF, Normalization and denormalization process

Unit Number: 4 Title: Query Languages No. of hours: 8

Content Summary:
Relational Algebra: relational operators (projection, selection, union, intersection, set difference, division, rename,
Cartesian product, generalized relational algebra operators, cross product, join operators: inner vs. outer join, theta
join, outer join, natural join, equijoin, self-join, complete set of relational algebra operations.
SQL- Queries, Constraints, Form of SQL query, UNION, INTERSECT and EXCEPT, Nested queries, Aggregate
Operators, Null values.

Unit Number: 5 Title: Introduction to NoSQL (MongoDB) No. of hours: 4

Content Summary:
Introduction to MongoDB, Data Types, Document Data Model-Creating, Inserting, Updating and Deleting
Documents, MongoDB Query Language, Sorting, Join Operations.

Unit Number: 6 Title: File Organization & Indexing No. of hours: 6

Content Summary:
Disk Storage, Basic File Structures and Hashing: Unordered, ordered and hashed files of records, Single and
multilevel indexes: primary index, secondary index, clustered, multilevel indexes.

Unit Number: 7 Title: Transaction Management & Concurrency Control No. of hours: 7
CSL214 Database Management Systems

Content Summary:
Introduction to transaction processing, ACID Properties, Concurrency control mechanisms: serializability, two phase
locking protocol, basic concept of deadlock, deadlock handling, timestamp-based protocols, precedence graph to
ensure serializability, different protocols in concurrency control.

13. Brief Description of Self-learning components by students (through books/resource material etc.):
● Aggregation and Pagination in MongoDB

● Books Recommended:

Textbooks:
● Elmasri R. and Navathe S.B., Fundamentals of Database Management Systems. 6th ed. Pearson, 2010.
● Silberschatz A., Korth H.F. and Sudarshan S., Database System Concepts. 6th ed. Mc.Graw Hill, 2010.
● Chodorow K., MongoDB: The Definitive Guide. 2nd ed. O'Reilly Media, 2013.

Reference Books:
● Ramakrishnan R. and Gehrke J., Database Management Systems. 3rd ed. McGraw-Hill Education, 2003.
● Suehring S., My SQL Bible. Wiley Publishing, 2002.

Reference Websites: (nptel, swayam, coursera, edx, udemy, lms, official documentation weblink)
● https://nptel.ac.in/courses/106106220
● https://docs.mongodb.com/

Practical Content

Software/
Unit Time
Sr. No. Title of the Experiment Hardware
Covered Required
Based
1 Design an ER diagram for the COMPANY database for the
erdplus.com 2 3 hours
following set of requirements.
2 Design a Relational Database Design for the COMPANY
erdplus.com 2 2 hours
database from the ER/EER diagram.
3 To apply SQL integrity constraints as per the DDL statements
MySQL 4 3 hours
given below for COMPANY database.
4 To familiarize with SELECT-FROM-WHERE SQL simple
MySQL 4 3 hours
queries on the COMPANY database.
5 To familiarize with different JOIN operations in SQL on the
MySQL 4 3 hours
COMPANY database.
6 To understand Aggregate functions and Group by Clause
MySQL 4 3 hours
using SQL queries on the COMPANY database.
7 To familiarize with nested SQL queries on the COMPANY
MySQL 4 3 hours
database.
8 Identifying contrast between Relational Databases and mongodb.co
5 2 hours
NoSQL, thereby recognizing their applications. m
9 Create a COMPANY database using NoSQL database - MongodbSh
5 3 hours
MongoDB. ell
CSL214 Database Management Systems

10 Mongodb
Retrieve data from NoSQL database - MongoDB. 5 3 hours
Shell

Value Added Experiments

1 Mongodb
Sorting and Indexing of Data in COMPANY Database 5 2 hours
Shell

Project (To be done as individual/in group): No

Evaluation Scheme

TYPE OF ALLOTTED RANGE

PARTICULAR PASS CRITERIA
COURSE OF MARKS

Minor Test 15%

Major Test 35%

Continuous Evaluation Must Secure 30% Marks Out of Combined
Theory+
Through Class 10% Marks of Major Test Plus Minor Test with
Practical
Tests/Practice/Assignments/
(L-T-P/L-0-P) Overall 40% Marks in Total.
Presentation/Quiz
Online Quiz 5%

Lab Work 35%

Mapping of PO’s and CO’s

PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO3
CO1 2 2 1 1 1 - 1 1 1 2 1 3 2 1 1
CO2 2 3 3 3 3 2 - 2 2 3 3 3 2 2 3
CO3 2 2 3 2 3 2 1 2 2 3 2 3 3 2 3
CO4 3 2 3 3 3 3 1 2 2 3 3 3 3 2 3
CO5 3 2 3 3 3 3 1 2 2 3 3 2 3 2 3
CO6 2 2 2 2 3 2 - - 1 1 2 2 2 2 1
CO7 2 2 2 3 2 2 1 1 1 2 3 3 3 2 2