DATABASE MANAGEMENT SYSTEM (BCS403)

DATABASE MANAGEMENT SYSTEM

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

MODULE-1 

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 

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 

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 

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 

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

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

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.

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