Course Practical

Course Theory Practical Tutorial Theory Tutorial Total

Code /Oral
Name (Hrs.) (Hrs.) (Hrs.) (Credits) (Credits) (Credits)
(Credits)
Database
EC 210 Management 03 02 -- 03 01 -- 04
System

Examination Scheme
Course Theory Marks
Course Name Internal assessment End Term
Code Practical Oral Total
Sem. Work
1 2 Average
Exam

22
Database
EC 210 Management 40 40 40 60 25 25 -- 150
System

Prerequisite: Data Structures

1-
Course Objectives:
1. Develop entity relationship data model and its mapping to relational model
.2
2. Learn relational algebra and Formulate SQL queries
3. Apply normalization techniques to normalize the database
4. Understand the concept of transaction, concurrency control and recovery techniques.
.Y

Course Outcomes:
After successful completion of the course students will be able to
1. Recognize the need of database management system
2. Design ER and EER diagram for real life applications
m

3. Construct relational models and write relational algebra queries.

4. Formulate SQL queries.
5. Apply the concept of normalization to relational database design.
6. Describe the concept of transaction, concurrency and recovery.
Ad

DETAILED THEORY SYLLABUS:

Sr.
Module Detailed Content Hrs.
No.
Introduction, Characteristics of databases, File system v/s
Introduction
Database system, Data abstraction and data
1. Database 04
Independence, DBMS system architecture, Database
Concepts
Administrator
The Entity-Relationship (ER) Model: Entity types: Weak
Entity–Relationsh
2. and strong entity sets, Entity sets, Types of Attributes, 06
ip Data Model
Relationship constraints: Cardinality and Participation,

Department of Electronics & Computer Science - Syllabus for Undergraduate Programme

Relational Model Introduction to the Relational Model, relational schema
3. and relational and concept of keys. Relational Algebra-operators, 07
Algebra Relational Algebra Queries.
Overview of SQL, Data Definition Commands, Integrity
constraints: key constraints, Domain Constraints,
Referential integrity , check constraints, Data
Structured Query
4. Manipulation commands, Data Control commands, Set 08
Language (SQL)
and string operations, aggregate functions, group by,
having, Views in SQL, joins, Nested and complex
queries, Triggers.
Relational-Databa Concept of normalization, Function Dependencies, First
5. 06
se Design Normal Form, 2NF, 3NF, BCNF.
Transaction concept, Transaction states, ACID properties,
Transactions
Transaction Control Commands, Concurrent Executions,
Management and
6. Serializability-Conflict and View, Concurrency Control: 07

22
Concurrency and
Lock-based, Timestamp-based protocols, Recovery
Recovery
System: Log based recovery, Deadlock handling.

DETAILED LAB SYLLABUS:

1-
Hardware Requirements: 2GB RAM

Software Requirements: SQL server (Oracle/MySQL/PostGreSQL)

.2
Sr. No. Detailed Lab/Tutorial Description
Identify the case study and detail statement of the problem. Design an
.Y

1
Entity-Relationship(ER) / Extended Entity-Relationship (EER) Model.
2 Mapping ER/EER to Relational schema model.
Create a database using Data Definition Language (DDL) and apply integrity
3
constraints for the specified System.
m

4 Apply DML Commands for the specified system.

5 Perform Simple queries, string manipulation operations and aggregate functions.
6 Implement Views and Join operations.
Ad

7 Perform Nested and Complex queries

8 Perform DCL and TCL commands.
9 Implement function and trigger.
10 Demonstrate Database connectivity
Implementation and demonstration of Transaction and Concurrency control techniques
11
using locks.

Theory Assessments:

1. Internal Assessment: Two Internal assessments will be conducted for 40 marks each with
average marks of both assessments as final score.

2. End Sem Theory Examination:

Department of Electronics & Computer Science - Syllabus for Undergraduate Programme

Course Practical/
Theory Practical Tutorial Theory Tutorial Total
Code Course Name Oral
(Hrs.) (Hrs.) (Hrs.) (Credits) (Credits) (Credits)
(Credits)
Advance
Database
Management
EC 307 System + 03 02 -- 03 01 -- 04
Data
Warehousing
& Mining

Examination Scheme

22
Course Theory Marks
Course Name
Code Internal assessment End Term
Practical Oral Total
Sem. Work
1 2 Average
Exam

1-
Advance Database
Management System
EC 307 40 40 40 60 25 -- 25 150
+ Data Warehousing
& Mining
.2
Course Objectives:
1. To provide insights into distributed database designing.
2. To specify the various approaches used for using XML and JSON technologies.
.Y

3. To apply the concepts behind the various types of NoSQL databases and utilize it for
Mongodb.
4. To identify the significance of Data Warehousing and Mining.
5. To develop research interest towards advances in data mining.
m

Course Outcomes: Upon successful completion students will be able to

1. Design distributed database using the various techniques for query processing.
2. Organize the data using XML and JSON database for better interoperability.
Ad

3. Compare different types of NoSQL databases.

4. Formulate NoSQL queries using Mongodb.
5. Understand data warehouse fundamentals and design data warehouse with dimensional
modelling and apply OLAP operations.
6. Understand data mining principles and perform Data preprocessing and Visualization.

Prerequisite: Database Management System (DBMS)

DETAILED THEORY SYLLABUS:
Sr.
Module Detailed Content Hrs
No.
Introduction, Distributed DBMS Architecture, Data
Distributed
1. Fragmentation, Replication and Allocation Techniques for 08
Databases
Distributed Database Design, Distributed Transaction

Department of Electronics & Computer Science - Syllabus for Undergraduate Programme

Management – Definition, properties, types, architecture.
Distributed Query Processing - Characterization of Query
Processors, Layers/ phases of query processing. Distributed
Concurrency Control- Taxonomy, Locking based, Basic TO
algorithm, Recovery in Distributed Databases: Failures in
distributed database, 2PC and 3PC protocol.
XML Databases: Document Type Definition, XML Schema,
Querying and Transformation: XPath and XQuery. Basic JSON
Data
syntax, (Java Script Object Notation),JSON data types,
2. interoperability – 05
Stringifying and parsing the JSON for sending & receiving,
XML and JSON
JSON Object retrieval using key-value pair and JQuery, XML Vs
JSON.
NoSQL database concepts: NoSQL data modeling, Benefits of
NoSQL, comparison between SQL and NoSQL database system.
Replication and sharding, Distribution Models Consistency in

22
NoSQL
distributed data, CAP theorem, Notion of ACID Vs BASE,
3. Distribution 07
handling Transactions, consistency and eventual consistency.
Model
Types of NoSQL databases: Key-value data store, Document
database and Column Family Data store, Comparison of NoSQL

1-
databases w.r.t CAP theorem and ACID properties.
NoSQL using MongoDB: Introduction to MongoDB Shell,
Running the MongoDB shell, MongoDB client, Basic operations
with MongoDB shell, Basic Data Types, Arrays, Embedded
.2
NoSQL using Documents. Querying MongoDB using find() functions,
4. 05
MongoDB advanced queries using logical operators and sorting, simple
aggregate functions, saving and updating document. MongoDB
Distributed environment: Concepts of replication and horizontal
scaling through sharding in MongoDB.
.Y

Introduction to Data Warehouse, Data warehouse architecture,

Data warehouse versus Data Marts, E-R Modeling versus
Data Dimensional Modeling, Information Package Diagram, Data
5. Warehousing Warehouse Schemas; Star Schema, Snowflake Schema, Factless 07
Fundamentals Fact Table, Fact Constellation Schema. Update to the dimension
m

tables. Major steps in ETL process, OLTP versus OLAP, OLAP

operations: Slice, Dice, Rollup, Drilldown and Pivot.
Data Mining Task Primitives, Architecture, KDD process, Issues
Introduction to
in Data Mining, Applications of Data Mining, Data Exploration:
Ad

Data Mining,
Types of Attributes, Statistical Description of Data, Data
6. Data Exploration 07
Visualization, Data Preprocessing: Descriptive data
and Data
summarization, Cleaning, Integration & transformation, Data
Pre-processing
reduction, Data Discretization and Concept hierarchy generation.

DETAILED LAB SYLLABUS:

Software Requirements: SQL, Java/Python, WEKA
Sr.
Detailed Lab Description
No.
1 Design of a distributed database for a real life application - Fragmentation, Query Processing.

2 Simulation of Recovery methods in distributed DB.

3 Design XML schema for real life application and write queries using XPath & XQuery.

Department of Electronics & Computer Science - Syllabus for Undergraduate Programme

4 Implement data transfer using JSON.

5 Design a database using NoSQL model and query it.

6 Implement different operations in MongoDB.

7 One case study on building Data warehouse/Data Mart -Write Detailed Problem statement and
design dimensional modelling (creation of star and snowflake schema).
8 Implementation of all dimension table and fact table based on the case study.

9 Implementation of OLAP operations: Slice, Dice, Rollup, Drilldown and Pivot based on the
case study.
10 Perform data Pre-processing task and demonstrate Classification, Clustering, Association
algorithm on data sets using data mining tool (WEKA/R tool).

22
Theory Assessments:
1. Internal Assessment: Two Internal assessments will be conducted for 40 marks each with
average marks of both assessments as final score.

1-
2. End Sem Theory Examination:
● Question paper will consist of 4 questions, each carrying 20 marks.
● Total 3 questions need to be solved.
● Q.1 will be compulsory, based on the entire syllabus.
.2
● Remaining questions will be randomly selected from all the modules.
● Weightage of marks should be proportional to number of hours assigned to each
module.
.Y

Lab Assessments:
1.Term work Assessment:
Term work should consist of minimum 8-10 experiments. Journal must include at least 2
assignments on content of theory and practical. The final certification and acceptance of term
work ensures satisfactory performance of laboratory work and minimum passing marks in
m

term work. Total 25 Marks (Experiments: 15-marks, Attendance (Theory & Practical):
05-marks, Assignments: 05-marks).
Ad

2. Oral/Viva Assessment: Oral exam to be conducted by Internal & External examiners.

Text Books:
1. Korth, Siberchatz,Sudarshan, “Database System Concepts”, 6thEdition, McGraw Hill.
2. Elmasri and Navathe, “Fundamentals of Database Systems”, 5thEdition, Pearson
Education.
3. Ozsu, M. Tamer, Valduriez, Patrick, “Principles of distributed database systems”,3rd
Edition, Pearson Education, Inc.
4. PramodSadalge, Martin Fowler, NoSQL Distilled: A Brief Guide to the Emerging World
of Polyglot Persistence, Addison Wesely/ Pearson.
5. Jeff Friesen , Java XML and JSON,Second Edition, 2019, après Inc.
6. Paulraj Ponniah, “ Data Warehousing: Fundamentals for IT Professionals”, Wiley India.
7. Han, Kamber, “Data Mining Concepts and Techniques”, Morgan Kaufmann 2nd edition.

Department of Electronics & Computer Science - Syllabus for Undergraduate Programme