lOMoARcPSD|57299707

BCS403 - DBMS

Database management system (Visvesvaraya Technological University)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by sunil lamani (sunillamani0770@gmail.com)
 lOMoARcPSD|57299707

IPCC (26.04.2022)

DATABASE MANAGEMENT SYSTEM Semester 4

Course Code BCS403 CIE Marks 50
Teaching Hours/Week (L:T:P: S) 3:0:2:0 SEE Marks 50
Total Hours of Pedagogy 40 hours Theory + 8-10 Lab slots Total Marks 100
Credits 04 Exam Hours
Examination nature (SEE) Theory

Course objectives:
● To Provide a strong foundation in database concepts, technology, and practice.
● To Practice SQL programming through a variety of database problems.
● To Understand the relational database design principles.
● To Demonstrate the use of concurrency and transactions in database.
● To Design and build database applications for real world problems.
● To become familiar with database storage structures and access techniques.

Teaching-Learning Process
These are sample Strategies, which teachers can use to accelerate the attainment of the various
course outcomes.
1. Lecturer method (L) needs not to be only a traditional lecture method, but alternative effective
teaching methods could be adopted to attain the outcomes.
2. Use of Video/Animation to explain functioning of various concepts.
3. Encourage collaborative (Group Learning) Learning in the class.
4. Ask at least three HOT (Higher order Thinking) questions in the class, which promotes critical
thinking.
5. Adopt Problem Based Learning (PBL), which fosters students’ Analytical skills, develop design
thinking skills such as the ability to design, evaluate, generalize, and analyze information rather than
simply recall it.
6. Introduce Topics in manifold representations.
7. Show the different ways to solve the same problem with different circuits/logic and encourage the
students to come up with their own creative ways to solve them.
8. Discuss how every concept can be applied to the real world - and when that's possible, it helps
improve the students' understanding
9. Use any of these methods: Chalk and board, Active Learning, Case Studies

MODULE-1 No. of Hours: 8

Introduction to Databases: Introduction, Characteristics of database approach, Advantages of using the
DBMS approach, History of database applications.
Overview of Database Languages and Architectures: Data Models, Schemas, and Instances. Three schema
architecture and data independence, database languages, and interfaces, The Database System environment.
Conceptual Data Modelling using Entities and Relationships: Entity types, Entity sets and structural
constraints, Weak entity types, ER diagrams,Specialization and Generalization.

Textbook 1:Ch 1.1 to 1.8, 2.1 to 2.6, 3.1 to 3.10

RBT: L1, L2, L3

MODULE-2 No. of Hours: 8

@# 16032024 1
Downloaded by sunil lamani (sunillamani0770@gmail.com)
 lOMoARcPSD|57299707

IPCC (26.04.2022)

Relational Model: Relational Model Concepts, Relational Model Constraints and relational database schemas,
Update operations, transactions, and dealing with constraint violations.
Relational Algebra: Unary and Binary relational operations, additional relational operations (aggregate,
grouping, etc.) Examples of Queries in relational algebra.
Mapping Conceptual Design into a Logical Design: Relational Database Design using ER-to-Relational
mapping.

Textbook 1: Ch 5.1 to 5.3, Ch 8.1 to 8.5; Ch 9.1 to 9.2 Textbook 2: 3.5

RBT: L1, L2, L3

MODULE-3 No. of Hours:8

Normalization: Database Design Theory – Introduction to Normalization using Functional and Multivalued
Dependencies: Informal design guidelines for relation schema, Functional Dependencies, Normal Forms based
on Primary Keys, Second and Third Normal Forms, Boyce-Codd Normal Form, Multivalued Dependency and
Fourth Normal Form, Join Dependencies and Fifth Normal Form.
SQL: SQL data definition and data types, Schema change statements in SQL, specifying constraints in SQL,
retrieval queries in SQL, INSERT, DELETE, and UPDATE statements in SQL, Additional features of SQL
Textbook 1: Ch 14.1 to 14.7, Ch 6.1 to 6.5
RBT: L1, L2, L3

MODULE-4 No. of Hours:8

SQL: Advanced Queries: More complex SQL retrieval queries, Specifying constraints as assertions and
action triggers, Views in SQL.

Transaction Processing: Introduction to Transaction Processing, Transaction and System concepts, Desirable
properties of Transactions, Characterizing schedules based on recoverability, Characterizing schedules based
on Serializability, Transaction support in SQL.

Textbook 1: Ch 7.1 to 7.3, Ch 20.1 to 20.6

RBT: L1, L2, L3
MODULE-5 No. of Hours:08
Concurrency Control in Databases: Two-phase locking techniques for Concurrency control, Concurrency
control based on Timestamp ordering, Multiversion Concurrency control techniques, Validation Concurrency
control techniques, Granularity of Data items and Multiple Granularity Locking.
NOSQL Databases and Big Data Storage Systems: Introduction to NOSQL Systems, The CAP Theorem,
Document-Based NOSQL Systems and MongoDB, NOSQL Key-Value Stores, Column-Based or Wide
Column NOSQL Systems, NOSQL Graph Databases and Neo4j

Textbook 1:Chapter 21.1 to 21.5, Chapter 24.1 to 24.6

RBT: L1, L2, L3

@# 16032024 2
Downloaded by sunil lamani (sunillamani0770@gmail.com)
 lOMoARcPSD|57299707

IPCC (26.04.2022)

PRACTICAL COMPONENT OF IPCC (May cover all / major modules)

Sl.NO Experiments
1 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 theuser.
2. Insert the any three records in the employee table contains attributes
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.
2 Create a table called Employee that contain attributes EMPNO,ENAME,JOB, MGR,SAL &
execute the following.
1. Add a column commission with domain to the Employeetable.
2. Insert any five records into the table.
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.
3 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.
2. Count number of employee names from employeetable
3. Find the Maximum age from employee table.
4. Find the Minimum age from employeetable.
5. Find salaries of employee in Ascending Order.
6. Find grouped salaries of employees.
4 Create a row level trigger for the customers table that would fire for INSERT or UPDATE or
DELETE operations performed on the CUSTOMERS table. This trigger will display the
salary difference between the old & new Salary.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)
5 Create cursor for Employee table & extract the values from the table. Declare the variables
,Open the cursor & extrct the values from the cursor. Close the cursor.
Employee(E_id, E_name, Age, Salary)
6 Write a PL/SQL block of code using parameterized Cursor, that will merge the data available
in the newly created table N_RollCall with the data available in the table O_RollCall. If the
data in the first table already exist in the second table then that data should be skipped.
7 Install an Open Source NoSQL Data base MangoDB & perform basic CRUD(Create, Read,
Update & Delete) operations. Execute MangoDB basic Queries using CRUD operations.
Course outcomes (Course Skill Set):
At the end of the course, the student will be able to:
● Describe the basic elements of a relational database management system
● Design entity relationship for the given scenario.
● Apply various Structured Query Language (SQL) statements for database manipulation.
● Analyse various normalization forms for the given application.
● Develop database applications for the given real world problem.
● Understand the concepts related to NoSQL databases.
Assessment Details (both CIE and SEE)
The weightage of Continuous Internal Evaluation (CIE) is 50% and for Semester End Exam (SEE) is 50%. The minimum

@# 16032024 3
Downloaded by sunil lamani (sunillamani0770@gmail.com)
 lOMoARcPSD|57299707

IPCC (26.04.2022)

passing mark for the CIE is 40% of the maximum marks (20 marks out of 50) and for the SEE minimum passing mark is
35% of the maximum marks (18 out of 50 marks). A student shall be deemed to have satisfied the academic requirements
and earned the credits allotted to each subject/ course if the student secures a minimum of 40% (40 marks out of 100) in
the sum total of the CIE (Continuous Internal Evaluation) and SEE (Semester End Examination) taken together.
CIE for the theory component of the IPCC (maximum marks 50)
● IPCC means practical portion integrated with the theory of the course.
● CIE marks for the theory component are 25 marks and that for the practical component is 25 marks.
● 25 marks for the theory component are split into 15 marks for two Internal Assessment Tests (Two Tests, each of 15
Marks with 01-hour duration, are to be conducted) and 10 marks for other assessment methods mentioned in
22OB4.2. The first test at the end of 40-50% coverage of the syllabus and the second test after covering 85-90% of
the syllabus.
● Scaled-down marks of the sum of two tests and other assessment methods will be CIE marks for the theory
component of IPCC (that is for 25 marks).
● The student has to secure 40% of 25 marks to qualify in the CIE of the theory component of IPCC.
CIE for the practical component of the IPCC
● 15 marks for the conduction of the experiment and preparation of laboratory record, and 10 marks for the test to be
conducted after the completion of all the laboratory sessions.
● On completion of every experiment/program in the laboratory, the students shall be evaluated including viva-voce
and marks shall be awarded on the same day.
● The CIE marks awarded in the case of the Practical component shall be based on the continuous evaluation of the
laboratory report. Each experiment report can be evaluated for 10 marks. Marks of all experiments’ write-ups are
added and scaled down to 15 marks.
● The laboratory test (duration 02/03 hours) after completion of all the experiments shall be conducted for 50 marks
and scaled down to 10 marks.
● Scaled-down marks of write-up evaluations and tests added will be CIE marks for the laboratory component of IPCC
for 25 marks.
● The student has to secure 40% of 25 marks to qualify in the CIE of the practical component of the IPCC.
SEE for IPCC
Theory SEE will be conducted by University as per the scheduled timetable, with common question papers for the course
(duration 03 hours)
1. The question paper will have ten questions. Each question is set for 20 marks.
2. There will be 2 questions from each module. Each of the two questions under a module (with a maximum of 3 sub-
questions), should have a mix of topics under that module.
3. The students have to answer 5 full questions, selecting one full question from each module.
4. Marks scoredby the student shall be proportionally scaled down to 50 Marks
The theory portion of the IPCC shall be for both CIE and SEE, whereas the practical portion will have a CIE
component only. Questions mentioned in the SEE paper may include questions from the practical component.
Suggested Learning Resources:
Text Books:
1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th Edition, 2017, Pearson.
2. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014, McGraw Hill
Activity Based Learning (Suggested Activities in Class)/ Practical Based learning
Mini Project:
• Project Based Learning

@# 16032024 4
Downloaded by sunil lamani (sunillamani0770@gmail.com)