CP4152 Database Practices Syllabus:

CP4152 Database Practices Syllabus – Anna University PG Syllabus Regulation 2021

COURSE OBJECTIVES

 Describe the fundamental elements of relational database management systems
 Explain the basic concepts of relational data model, entity-relationship model, relational database design, relational algebra and SQL.
 Understand query processing in a distributed database system
 Understand the basics of XML and create well-formed and valid XML documents.
 Distinguish the different types of NoSQL databases
 To understand the different models involved in database security and their applications in real time world to protect the database and information associated with them.

UNIT I RELATIONAL DATA MODEL

Entity Relationship Model – Relational Data Model – Mapping Entity Relationship Model to Relational Model – Relational Algebra – Structured Query Language – Database Normalization.

Suggested Activities:

Data Definition Language
 Create, Alter and Drop
 Enforce Primary Key, Foreign Key, Check, Unique and Not Null Constraints
 Creating Views
Data Manipulation Language
 Insert, Delete, Update
 Cartesian Product, Equi Join, Left Outer Join, Right Outer Join and Full Outer Join
 Aggregate Functions
 Set Operations
 Nested Queries
Transaction Control Language
 Commit, Rollback and Save Points

UNIT II DISTRIBUTED DATABASES, ACTIVE DATABASES AND OPEN DATABASE CONNECTIVITY

Distributed Database Architecture – Distributed Data Storage – Distributed Transactions – Distributed Query Processing – Distributed Transaction Management – Event Condition Action Model – Design and Implementation Issues for Active Databases – Open Database Connectivity.

Suggested Activities:

 Distributed Database Design and Implementation
 Row Level and Statement Level Triggers
 Accessing a Relational Database using PHP, Python and R

UNIT III XML DATABASES

Structured, Semi structured, and Unstructured Data – XML Hierarchical Data Model – XML Documents – Document Type Definition – XML Schema – XML Documents and Databases – XML Querying – XPath – XQuery

Suggested Activities:

 Creating XML Documents, Document Type Definition and XML Schema
 Using a Relational Database to store the XML documents as text
 Using a Relational Database to store the XML documents as data elements
 Creating or publishing customized XML documents from pre-existing relational databases
 Extracting XML Documents from Relational Databases
 XML Querying

UNIT IV NOSQL DATABASES AND BIG DATA STORAGE SYSTEMS

NoSQL – Categories of NoSQL Systems – CAP Theorem – Document-Based NoSQL Systems and MongoDB – MongoDB Data Model – MongoDB Distributed Systems Characteristics – NoSQL Key-Value Stores – DynamoDB Overview – Voldemort Key-Value Distributed Data Store – Wide Column NoSQL Systems – Hbase Data Model – Hbase Crud Operations – Hbase Storage and Distributed System Concepts – NoSQL Graph Databases and Neo4j – Cypher Query Language of Neo4j – Big Data – MapReduce – Hadoop – YARN.

Suggested Activities:

 Creating Databases using MongoDB, DynamoDB, Voldemort Key-Value Distributed Data Store Hbase and Neo4j.
 Writing simple queries to access databases created using MongoDB, DynamoDB, Voldemort Key-Value Distributed Data Store Hbase and Neo4j.

UNIT V DATABASE SECURITY

Database Security Issues – Discretionary Access Control Based on Granting and Revoking Privileges – Mandatory Access Control and Role-Based Access Control for Multilevel Security – SQL Injection – Statistical Database Security – Flow Control – Encryption and Public Key Infrastructures – Preserving Data Privacy – Challenges to Maintaining Database Security – Database Survivability – Oracle Label-Based Security.

Suggested Activities:

Implementing Access Control in Relational Databases

TOTAL : 75 PERIODS

COURSE OUTCOMES

At the end of the course, the students will be able to
CO1:Convert the ER-model to relational tables, populate relational databases and formulate SQL queries on data.
CO2:Understand and write well-formed XML documents
CO3:Be able to apply methods and techniques for distributed query processing.
CO4:Design and Implement secure database systems.
CO5:Use the data control, definition, and manipulation languages of the NoSQL databases

REFERENCES:

1. R. Elmasri, S.B. Navathe, “Fundamentals of Database Systems”, Seventh Edition, Pearson Education 2016.
2. Henry F. Korth, Abraham Silberschatz, S. Sudharshan, “Database System Concepts”, Seventh Edition, McGraw Hill, 2019.
3. C.J.Date, A.Kannan, S.Swamynathan, “An Introduction to Database Systems, Eighth Edition, Pearson Education, 2006
4. Raghu Ramakrishnan , Johannes Gehrke “Database Management Systems”, Fourth Edition, McGraw Hill Education, 2015.
5. Harrison, Guy, “Next Generation Databases, NoSQL and Big Data” , First Edition, Apress publishers, 2015
6. Thomas Cannolly and Carolyn Begg, “Database Systems, A Practical Approach to Design, Implementation and Management”, Sixth Edition, Pearson Education, 2015