Unit I
DBMS Concepts and architecture Introduction, Database approach vs. Traditional file accessing approach, Advantages of database systems, Data models, Schemas and instances, Data independence, Database Language and interfaces, Overall Database Structure, Functions of DBA and designer, ER data model: Entities and attributes, Entity types, Defining the E-R diagram, Concept of Generalization, Aggregation, and Specialization. Transforming ER diagram into the tables. Various other data models object-oriented data Model, Network data model, and Relational data model, Comparison between the three types of models.
Unit II
Relational Data models: Domains, Tuples, Attributes, Relations, Characteristics of relations, Keys, Key attributes of relation, Relational database, Schemas, Integrity constraints. Referential integrity, Intension and Extension, Relational Query languages: SQL-DDL, DML, integrity constraints, Complex queries, various joins, indexing, triggers, assertions, Relational algebra and relational calculus, Relational algebra operations like select, Project, Join, Division, outer union. Types of relational calculus i.e. Tuple-oriented and domain-oriented relational calculus and its operations.
Unit III
Database Design: Introduction to normalization, Normal forms, Functional dependency, Decomposition, Dependency preservation and lossless join, problems with null valued and dangling tuples, multivalued dependencies. Query Optimization: Introduction, steps of optimization, various algorithms to implement select, project and join operations of relational algebra, optimization methods: heuristic-based, cost estimation-based.
Unit IV
Transaction Processing Concepts: -Transaction System, Testing of Serializability, Serializability of schedules, conflict & view serializable schedule, recoverability, Recovery from transaction failures. Log-based recovery. Checkpoints deadlock handling. Concurrency Control Techniques: Concurrency Control, locking Techniques for concurrency control, time-stamping protocols for concurrency control, validation-based protocol, multiple granularity. Multi-version schemes, Recovery with concurrent transaction. Introduction to Distributed databases, data mining, data warehousing, Object Technology and DBMS, Comparative study of OODBMS Vs DBMS. Temporal, Deductive, Multimedia, Web & Mobile database.
Unit V
Study of Relational Database Management Systems through Oracle/PL SQL MySQL: Architecture, physical files, memory structures, background process. Concept of tablespaces, segments, extents, and block. Dedicated server, multi-threaded server. Distributed database, database links, and snapshot. Data dictionary, dynamic performance view. Security, role management, privilege management, profiles, invoker-defined security model. SQL queries, Data extraction from single, multiple tables equi-join, non equi-join, self-join, outer join. Usage of like, any, all, exists, in Special operators. Hierarchical queries, inline queries, flashback queries. Introduction of ANSI SQL, anonymous block, nested anonymous block, branching and looping constructs in ANSI SQL. Cursor management: nested and parameterized cursors, Oracle exception handling mechanism. Stored procedures, in, out, in-out type parameters, usage of parameters in procedures. User-defined functions their limitations. Triggers, mutating errors, instead of triggers.