Database Management Systems Syllabus - BCIS (PU)

Course Objectives

The main objective of this course is to introduce students to fundamentals of data management  technology by studying databases from three viewpoints: those of the database user, the database designer, and the database administrator.

Course Description

This course will concentrate on the principles, design, implementation and applications of database management systems. Topics include database management systems, relational database management system, structured query language, database design and the E-R Model, relational database design, transaction concurrency control, recovery system.

Course Outcomes

On completion of the course, students should be able to

• understand the different issues involved in the design and implementation of a database system.
• study the physical and logical database designs, database modeling, relational, hierarchical, and network models.
• understand and use data manipulation language to query, update, and manage a database
• develop an understanding of essential DBMS concepts such as: database security, integrity, concurrency, distributed database, and intelligent database, Client/Server (Database Server),
• design and build a simple database system and demonstrate competence with the fundamental tasks involved with modeling, designing, and implementing a DBMS. 

Course Contents

Unit One: Introduction                                                                                             3 hours

Database management systems and its applications, Purpose of Database systems, View of Data, Data models, Database  Language, Instances and schemes, Database users and administrators, Application architecture(one tier, two tier, n-tier)

Unit Two: Database Design and the E-R model                                                    7 hours

 E-R model, Entity sets, Relationship sets, Attributes, Constraints, Mapping Cardinalities, Participation constraints, Keys (Super key, Candidate key, Primary key), Entity Relationship Diagram, Basic Structure, Mapping Cardinality, mapping cardinalities in E-R DIAGRAM, Complex attributes, Roles, non-binary relationship sets, Weak, strong entity sets, E-R Diagram (case study), Extended E-R Features, Specialization, Generalization, Constraints on Generation, Aggregation, E-R Diagram with Aggregation

Unit Three: Introduction to the Relational model                                                 6 hours

Structure of Rational database, Database Schemes (case study), Keys, Schema Diagram, Rational Query Language, Relational operations.

Unit Four: Introduction to SQL                                                                              13 hours

Overview of SQL Query language, SQL Data Definition, Basic types, Basic Schema Definition, Basic Structure of SQL Queries (single, multiple), Natural join, Additional Basic Operations, Set operational (union, intersect, except), Null Values, Aggregate function (Basic, Grouping, Having, Nested sub-queries (comparison, empty relating from clause) Scalar sub-queries), Modification of the database.(insert, update, delete), Join Expression, join conditions, outer joins,  view, materialized view, Transaction (commit, Rollback), Integrity constraints (not null, unique, check, referential integrity), Authorization (Grant, Revoke), Roles, view, transfer of privileges, revoking of privileges, Functions and Procedures, Declaring and Revoking SQL functions and Procedures, Language constraints for procedures and functions, Triggers, indexes

Unit Five: Relational Database Design                                                                   7 hours

Database anomalies, Functional Dependencies, Basic Concepts, Closure of set of Functional Dependencies, closure of attribute set, Decomposition, Lossless Join Decompositions, Dependency preservation, Normalization, First Normal Form, Second Normal Form, Third Normal Form, Boyce -Codd Normal Form, Comparison of BCNF and 3NF

Unit Six: Transactions                                                                                              4 hours

ACID Properties, Simple transactions models, Storage structure, transaction atomicity of durability, Transaction Isolation, Serializibility, transaction isolation and atomicity, Locking, timestamp

Unit Seven: Concurrency Control                                                                          4 hours

Lock-Based protocols, Dead-lock handling, Multiple Granularities, Time-stamp Based Protocols, Validation based protocols

Unit Eight: Recovery System                                                                                   4 hours

Failure classification, Storage, Recovery and atomicity, Recovery Algorithm, Buffer management, Failure with loss of non-volatile storage, Early lock release and logical undo operations, Remote Backup systems

Note: Students are required to complete the project. Project should be done on group at most 4-5 number of students. Format of project report is given below:

• Project Description
• Description of entities or object consideration in the project
• Algorithm or Diagram showing description of project
• Conclusion of the project.

Basic Texts

1. Silberschatz,, Abraham , Henry F. Korth, S. Sudarshan Database system concepts:  McGraw Hill 6^th Edition, New Delhi

References

1. Post, Gerald V.:  Database Management Systems, McGraw Hill International Edition, New Delhi
2. Ramezelmasri, B.  Navate, Fundamentals of Database Systems, Pearson Education Asia