DBMS__BCA_SEM_II

BCA202: DATABASE MANAGEMENT SYSTEM

Course Details

  • Credits: 3(Lecture) + 1(Tutorial) = 4

1.1 Course Description

  • Aiming to provide fundamental knowledge of and practical experience with database concepts.

  • Includes study of information concepts realized using the relational data model.

  • Provides practical exposure to SQL (universal query language) and PL/SQL.

  • Prerequisites: Basic knowledge of computer programming.

1.2 Learning Targets/Outcomes

  • Differentiation: Distinguish database systems from file systems by listing their features and their functions and benefits.

  • Terminology: Define terminology, features, classifications, and characteristics of database systems.

  • Information Modelling: Analyze an information storage problem and derive models using entity relation diagrams and data dictionaries.

  • Understanding Relational Model: Demonstrate an understanding of the relational data model.

  • Database Schema: Transform an information model into a relational database schema using a data definition language and DBMS utilities.

1.3 Course Organization

  • Schedule: Three 1-hour lectures and one 1-hour tutorial session per week.

  • Participation in class discussions is expected.

Course Resources

Text Books

  • Silberschatz, H.F. Korth, S. Sudarshan, Database System Concepts 6th Edition, McGraw Hill, 2010.

  • R. Elmasri, S.B. Navathe, Fundamentals of Database Systems 6th Edition, Pearson Education, 2010.

Reference Books

  • R. Ramakrishanan, J. Gehrke, Database Management Systems 3rd Edition, McGraw-Hill, 2002.

  • R. Elmasri, S.B. Navathe, Database Systems: Models, Languages, Design, and Application Programming 6th Edition, Pearson Education, 2013.

  • Hansen and Hansen: DBM and Design, PHI.

Course Content

UNIT-I

  • Introductory Concepts of DBMS: Overview and applications, purpose of databases, data independence, architecture levels, mappings, users, and DBA roles.

  • Relational Model Introduction: Structure of relational databases, domains, schema, relations, keys, schema diagrams, relational algebra fundamentals, operators, queries, and tuple relational calculus.

UNIT-II

  • Entity Relationship Model: Basic concepts, design process, constraints, redundant attributes removal, keys, design issues, E-R diagrams, and extended E-R features (generalization, specialization, aggregation).

UNIT-III

  • Functional Dependency: Definitions (trivial and non-trivial), closures of FD set, normalization (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), and decomposition.

  • Transactions: Transaction concepts, properties, serializability, system recovery, two-phase commit protocol, and concurrency issues (locking mechanisms, deadlock, isolation).

UNIT-IV

  • SQL: Basics covering DDL, DML, DCL, structure creation, alterations, defining constraints (primary key, foreign key, unique, not null, check), aggregate and built-in functions, sub-queries, joins, and views.

UNIT-V

  • Advanced SQL & PL/SQL: Transaction control commands (commit, rollback, savepoint), PL/SQL concepts (cursors, stored procedures, stored functions, database triggers).

BCA202P: DATABASE MANAGEMENT SYSTEM PRACTICAL

Course Details

  • Credits: 2(Practical) = 2

1.1 Course Description

  • Same objectives and structure as BCA202, focusing on practical application.

1.3 Learning Targets/Outcomes

  • Same learning outcomes as BCA202, emphasizing practical experience in database concepts.

1.3 Course Organization

  • Schedule: Two 2-hour practical sessions each week.

Course Resources

  • Same textbook and reference book information as BCA202.

Practical List

  1. Creating Tables: Example schema for DEPOSIT, BRANCH, CUSTOMERS, and BORROW tables.

  2. Inserting Data: Example data for each table.

  3. Performing Queries: Listing data from tables, describe operations, and filtering queries.

  4. Additional Table Creation: Commands to create Job and Employee tables.

  5. Data Insertion for Employees and Jobs: Inserting sample data.

  6. Data Manipulation Commands: Perform various SQL queries to modify and retrieve data.

  7. Joining Tables: Advanced exercises involving the joining of multiple tables and applying aggregate functions.

  8. Subquery Utilization: Writing queries that involve subqueries to retrieve specific dataset.

  9. Data Manipulation: Inserting, updating, and deleting data based on specific conditions.

  10. PL/SQL Blocks and Functions: Developing PL/SQL blocks to manage and manipulate database entries.