The National Institute of Engineering, Mysuru 2023-24

Code: BCS403 Course: Database Management System

Credits: 4 L:T:P - 3:0:2
SEE: 100 Marks CIE: 100 Marks
SEE Hours: 3 Max. Marks: 100

Prerequisites if any NIL

Learning objectives 1. Understand the fundamental concepts of databases, including database
languages, architectures, and conceptual data modeling using entities and
relationships.
2. Gain proficiency in relational database management systems, including the
relational model, relational algebra, normalization, SQL, and transaction
processing.
Course Outcomes:

On the successful completion of the course, the student will be able to

COs Course Outcomes Bloom’s level

Describe fundamental concepts of database management systems, including architecture,
CO1 Understand
languages, and functionalities.
Design and implement database schemas using entities, relationships, and normalization
CO2 Apply
techniques.
CO3 Demonstrate proficiency in SQL for data manipulation, retrieval, and management tasks. Apply

Analyze and compare concurrency control mechanisms in relational databases and

CO4 Analyze
NoSQL databases, understanding their respective advantages and limitations.

Mapping with POs and PSOs:

COs PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2
CO1 2 - - - - - - - - - - - - -
CO2 3 3 2 - - - - - - - - - 2 2
CO3 2 2 2 - 3 - - - - - - 2 2 3
CO4 2 - - - 3 - - - - - - 3 2 2
Mapping Strength: Strong– 3 Medium – 2 Low – 1

Course Structure
No. of No. of No. of
Lecture Tutorial Practical
Hours Hours Hours
Module – 1: Introduction to Databases
Introduction to Databases: Introduction, Characteristics of database approach,
1.1 2 - 0
Advantages of using the DBMS approach, History of database applications.
Overview of Database Languages and Architectures: Data Models, Schemas, and
1.2 Instances. Three schema architecture and data independence. Database languages, and 3 - 0
interfaces, The Database System environment.
Conceptual Data Modelling using Entities and Relationships: Entity types, Entity sets
1.3 and structural constraints, Weak entity types, ER diagrams, Specialization and 3 - 1
Generalization
Module – 2: Relational Databases
Relational Model: Relational Model Concepts, Relational Model Constraints and
2.1 relational database schemas,Update operations, transactions, and dealing with constraint 3 - 0
violations.
Relational Algebra: Unary and Binary relational operations, additional relational
2.2 3 - 0
operations (aggregate,grouping, etc.) Examples of Queries in relational algebra.
Mapping Conceptual Design into a Logical Design: Relational Database Design using
2.3 2 - 0
ER-to-Relationalmapping
Module – 3: Normalization and SQL
B.E. Blown up Syllabus – II Year
 The National Institute of Engineering, Mysuru 2023-24

Normalization: Database Design Theory – Introduction to Normalization using

Functional and MultivaluedDependencies: Informal design guidelines for relation schema,
3.1 Functional Dependencies, Normal Forms basedon Primary Keys, Second and Third 5 - 1
Normal Forms, Boyce-Codd Normal Form, Multivalued Dependency andFourth Normal
Form, Join Dependencies and Fifth Normal Form.
SQL: SQL data definition and data types, Schema change statements in SQL, specifying
3.2 constraints in SQL,retrieval queries in SQL, INSERT, DELETE, and UPDATE statements 3 - 2
in SQL, Additional features of SQL.
Module – 4: SQL and Transactions
SQL: Advanced Queries: More complex SQL retrieval queries, Specifying constraints as
4.1 3 - 2
assertions andaction triggers, Views in SQL.
Transaction Processing: Introduction to Transaction Processing, Transaction and System
concepts, Desirableproperties of Transactions, Characterizing schedules based on
4.2 5 - 1
recoverability, Characterizing schedules basedon Serializability, Transaction support in
SQL.
Module – 5: Concurrency control and NoSQL Databases
Concurrency Control in Databases: Two-phase locking techniques for Concurrency
control, Concurrencycontrol based on Timestamp ordering, Multiversion Concurrency
5.1 4 - 1
control techniques, Validation Concurrencycontrol techniques, Granularity of Data items
and Multiple Granularity Locking.
NoSQL Databases and Big Data Storage Systems: Introduction to NOSQL Systems,
5.2 The CAP Theorem,Document-Based NOSQL Systems and MongoDB, NOSQL Key- 4 - 2
Value Stores, Column-Based or WideColumn NOSQL Systems.
Total No. of Lecture Hours 40 - -
Total No. of Tutorial Hours 00 -
Total No. of Practical Hours 10

PRACTICAL COMPONENT

Sl. No. Experiments COs

Create a table called Employee & execute the following.
Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)
1. Create a user and grant all permissions to the user.
2. Insert any three records in the employee table containing attributes.
1. CO3
EMPNO, ENAME JOB, MANAGER_NO, SAL, COMMISSION and
use rollback.Check the result.
3. Add primary key constraint and not null constraint to the employee table.
4. Insert NULL values to the employee table and verify the result.

Create a table called Employee that contains attributes EMPNO,ENAME,JOB,

MGR,SAL and execute the following.
1. Add a column commission with domain to the Employee table.
2. 2. Insert any five records into the table. CO3
3. Update the column details of job.
4. Rename the column of Employ table using alter command.
5. Delete the employee whose EMPNO is 105.
Queries using aggregate functions(COUNT,AVG,MIN,MAX,SUM),Group
by,Orderby.
Employee(E_id, E_name, Age, Salary)
1. Create Employee table containing all Records E_id, E_name, Age,
Salary.
3. CO3
2. Count number of employee names from employee table.
3. Find the Maximum age from the employee table.
4. Find the Minimum age from the employee table.
5. Find salaries of employees in Ascending Order.
6. Find grouped salaries of employees.

B.E. Blown up Syllabus – II Year

 The National Institute of Engineering, Mysuru 2023-24

Create a row level trigger for the customers table that would fire for INSERT or
UPDATE orDELETE operations performed on the CUSTOMERS table. This
4. CO4
trigger will display thesalary difference between the old & new Salary.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)
Create cursor for Employee table and extract the values from the table. Declare
the variables,Open the cursor, and extract the values from the cursor. Close the
5. CO4
cursor.
Employee(E_id, E_name, Age, Salary)
Install an Open-Source NoSQL Data base MongoDB & perform basic
6. CRUD(Create, Read,Update & Delete) operations. Execute MongoDB basic CO5
Queries using CRUD operations.
Project-based Experiment: CO1,
The project should use all the database concepts covered in theory and CO2,
7.
laboratory sessions. Students can integrate other relevant concepts/technologies CO3,
as required. CO4.

Textbook:
1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th
Edition, 2017, Pearson.
Reference Book:
1. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014, McGraw
Hill.
Online Resources:
1. MIT OpenCourseWareCourse Link: https://ocw.mit.edu/courses/6-830-database-systems-
fall-2010/
2. IIT Kharagpur Course Link: https://cse.iitkgp.ac.in/~pabitra/course/dbms/dbms_new.html
3. NPTEL Course Link: https://onlinecourses.nptel.ac.in/noc22_cs91/preview

B.E. Blown up Syllabus – II Year