First Year MCA (Semester–I)
Course Code 01MCL103 Course Name Database Design and Management System
Teaching Scheme Evaluation Scheme
L T P Credits SE-I SE-II SEE
Marks Marks Marks
3 1 0 4 25 25 50
Pre-requisites:
Course Objectives:
01 Understand the use of Structured Query Language (SQL) and learn SQL syntax
02 Understanding of database management concepts, including data integrity, security, and
concurrency control.
03 Understand the needs of database processing and learn techniques for controlling the
consequences of concurrent data access.
Course Outcomes:
At the end of the course, students will be able to
01 Query databases using SQL and other query languages
02 Design and implement databases using various data models and DBMS software.
03 Analyze and solve database-related problems.
Course Contents
Unit I Introduction to DBMS 6 Hours
Introduction, Traditional file system v/s DBMS, views of data, instance and schema, Data
Models – Relational and ER model, Keys, Database design process, Schema diagram, Extended
E-R Features- Specialization, Generalization and Aggregation, Database system structure,
Database users. Relational algebra, Tuple relational calculus, Domain relational calculus.
Unit II Structured Query Language 6 Hours
Introduction to SQL, data types.
DDL Statements – Create, Alter, Drop, Rename, Truncate.,DML Statements- Select, Insert,
Update, Delete,DCL Statements – Commit, Rollback.
Aggregate functions, Group by clause, having clause, order by clause, set operations, Joins,
Nested Queries, Views.
Unit III Functional Dependency and Normalization 7 Hours
Integrity constraints – domain constraints, referential integrity, Pitfalls in Relational-Database
Design, Functional dependency, types of functional dependency, closure of set of functional
dependency, Closure of Attribute Sets, canonical cover.
Normalization – Purpose of normalization, First Normal Form (1NF), Second Normal Form (2NF),
Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), Fifth
Normal Form (5NF)
Unit IV Introduction to PL /SQL 6 Hours
Introduction to PL /SQL: Introduction, Difference between SQL AND PL/SQL, Block definition
structure and Data types, Block Functions, cursor, trigger, procedures, exception handling. No
SQL Database - Introduction, Need& Advantages ,Types of No SQL Database , No SQL database
vs RDBMS
Unit V Data Storage and Indexing 6 Hours
File organization, Organization of records in file, Buffer Management.
Indexing – Ordered indices – primary indices, secondary indices, dense and sparse indices,
multilevel indexing, B tree indexing, B+ tree indexing and multiple key access.
Hashing – static hashing – open hashing, closed hashing, dynamic hashing.
Bitmap indices.
Unit VI Transaction Processing and Concurrency Control 7 Hours
Transaction Processing – Concept, ACID properties, Transaction model, Schedule, Serializability –
conflict and view Serializability, Recoverable schedule.
Concurrency Control Mechanisms – Lock based protocols, Multiple Granularity, Timestamp
based protocols, Thomas’s Write Rule, Validation based protocols
Texts Books:
Database System Concepts, Abraham Silberschatz, Henry F. Korth, S. Sudarshan, 6th
1
edition, McGraw- Hill.
Database Systems - A Practical Approach to Design, Implementation and Management,
2
Thomas Connolly, Carolyn Begg, 4th Edition, Addison Wesley
3 “MySQL Cookbook”, Paul DuBois, 3rd edition, O’REILLY.
Reference Books:
Fundamentals of Database Systems, Ramez, Elmasri, Shamkant B. Navathe, 6th Edition,
1
Addison Wesley.
“Database Systems – Design, Implementation and Management”, Rob & Coronel, 5th
2
Edition, Thomson Course Technology.
Supplementary Readings:
1.https://www.w3school.com
