Syllabus - Data base Management System (CSIT 405)

Computer Science & Information Technology

Data base Management System (CSIT 405)

IV-Semester

Unit-I

Basic Concepts

Introduction to DBMS, File system vs DBMS, Advantages of database instances, Data systems, Database System architecture, Data models, Schemas and independence, Functions of DBA and designer, Entities and attributes, Entity types, Key attributes, Relationships, Defining the E-R diagram of database.

Unit-II

Relational Model

Structure of relational databases, Domains, Relations, Relational algebra – fundamental operators and syntax, relational algebra queries, Entity-Relationship model :Basic concepts, Design process, constraints, Keys, Design issues, E-R diagrams, weak entity sets, extended E-R features –generalization, specialization and aggregation

Unit-III

SQL

Data definition in SQL, update statements and views in SQL: Data storage and definitions, Data retrieval queries and update statements, Query Processing & Query Optimization: Overview, measures of query cost, selection operation, sorting, join, evaluation of expressions, transformation of relational expressions, estimating statistics of expression results, evaluation plans. Case Study of ORACLE and DB2.

Unit-IV

Relational Database design

Functional Dependency –definition, trivial and non-trivial FD, closure of FD set, closure of attributes, irreducible set of FD, Normalization –1NF, 2NF, 3NF, Decomposition using FD-dependency preservation, lossless join, BCNF, Multi-valued dependency, 4NF, Join dependency and 5NF

Unit-V

Introduction of transaction, transaction processing and recovery, Concurrency control

Lock management, specialized locking techniques, concurrency control without locking, Protection and Security Introduction to: Distributed databases, Basic concepts of object oriented data base system.

Course Objective

The main objectives of the course are 1. To understand fundamental knowledge of file system, database concepts and use of relational database. 2. To study of different data model and conceptual design using ER diagram. 3. Students can use SQL operations to manipulate the database and learn how to design and create a good database using functional dependencies and normalization. 4. The course provides an overview of transaction management, concurrency control, distributed database and Big Data.

Course Outcome

After successful completion of this course, the students would be able to: 1. Compare file system and DBMS and explain how DBMS is better than traditional File Processing Systems. 2. Analyze the physical and logical database designs, database modeling, relational, hierarchical, and network models 3. Analyze and renovate an information model into a relational database schema and to use a DDL, DML and DCL utilities to implement the schema using a DBMS. 4. Formulate data retrieval queries in SQL and Relational Algebra. 5. Demonstrate an understanding of functional dependencies, normalization theory and apply such knowledge to the design of a database. 6. Demonstrate and explain terms like Transaction Processing, Concurrency Control, distributed database and big data.

Practicals

  • To perform various SQL Commands of DDL, DML, DCL.

  • Write SQL Commands such as Insertion, deletion and updation for any schema.

  • To execute Nested Queries, Join Queries, order-by, having clause and string operation.

  • To perform set operators like Union, Intersect, Minus on a set of tables.

  • To execute various commands for GROUP functions (avg, count, max, min, Sum).

  • Write a PL/SQL block for transaction application using Triggers.

  • Write a DBMS program to prepare report for an application using function.

  • Designing of various Input screens/Forms.

  • Create reports using database connectivity of Front end with back end.

  • Create database Design with normalization and implementing in any application.

Reference Books

  • Korth, Silbertz, Sudarshan, “Database Concepts”, McGraw Hill.

  • Elmasri, Navathe, “Fundamentals of Database Systems”, Pearson.

  • Ivan Bayross, “SQL, PL/SQL the Programming Language of Oracle”, BPB publications.

  • S. Sharma, J. Agrawal, S. Agrawal, “Advanced Database Management System”, Dreamtech Press.

  • Leon & Leon, “Fundamental of Data Base Management System”, TMH