Module Code and Title:       CAP102          Database Management Systems

Programme:                          BCA

Credit Value:                         12

Module Tutor:                       Vijay Gurung

General Objective: This module introduces students to the needs for and uses of database management systems in organizations. Students will learn about the components of a computerized database information system and its applications. The module aims to groom an ability in students to correctly use the techniques, components and tools of a typical database management system to build comprehensive information systems.

Learning Outcomes – On completion of the module, learners will be able to:

1. Explain the advantages of using database approaches in managing large volumes of data.
2. Describe a typical database system and its architecture.
3. Apply Entity-Relationship model concepts to draw ER diagrams that represent a conceptual database.
4. Use relational model concepts to design a relational database.
5. Use SQL statements to create and manipulate a database stored in RDBMS.
6. Write SQL queries to retrieve information from databases.
7. Apply normalization & denormalization concepts to achieve high quality database design.

Learning and Teaching Approach:

Assessment Approach:

A. Individual Assignment: Portion of Final Mark: 15%

Students should submit two assignments related to E-R Diagram and database normalization to obtain this 15%. The first one will be before the midterm and it constitutes half of the total 15% allocated. The students need to submit an E-R diagram based on the data given. The next assignment that is for the other half will be done after the midterm, will be on topics normalization concepts. The sample data will be given to students to normalize the table. The students need to submit a report of 750 words. 40% will be awarded for the explanation of the topic, 20% for content and 40% for illustrating with applications.

B. Class Test: Portion of Final Mark: 15%

This is a written test conducted within the class for duration of 30-40 minutes. There will two such tests, one before midterm comprising of topics from the beginning to the quarter point of the subject matter and the other after the midterm comprising of topics from after the midterm to quarter pointer after midterm. Two written tests will be conducted, each of which (worth 7.5%) and cover 2-3 weeks of material.

C. Lab Practical Exam: Portion of Final Mark: 15%

These are practical exams conducted in computer labs for duration of 1 hour. There will be two such tests, first one will be a week or two before midterm and the next will be a week or two before the semester end exam. Each will carry 50% of the total 15% allocated. The first lab test will be based on the first half of the subject matter and the next test will be based on the second half. The students will be tested to write database queries for creating database and tables, and to apply manipulation functions.

Students will be assessed on their program designing skills, maintaining syntax, use of functions, testing and debugging a code. 2-3 database queries programs will be assigned to individual student. They have to solve it within predefined examination duration.

35% will be awarded sub tasks completed, 35% Techniques used for each sub task, 10% for timing and 30% for output.

D. Midterm Exam: Portion of Final Marks: 15%

This a college wide examination conducted at the half-way into the semester. This examination is conducted for 1 hour and 30 Minutes and it includes all topics till the half-way point in the subject matter.

Prerequisites: CPR101

Subject Matter:

1. Introduction to Database Management Systems
   • Data and Database meaning
   • Database Management Systems
   • Characteristics of the database approach
   • Role of Database administrators
   • Role of Database Designers
   • End Users
   • Advantages of Using a DBMS
   • Database system Vs file system
2. DBMS Architecture
   • Data Models – Categories of data models, Schemas, Instances and Database state.
   • DBMS Architecture and Data Independence
   • The Three schema architecture, Data independence.
   • DBMS Languages and Interfaces.
   • Classifications of Database Management Systems.
3. ER Modelling
   • Some advantages to good database design
   • Conceptual Database Design
   • Logical database design
   • Physical database design
   • Entity-Relationship Model
     • Entity types, entity set, attribute and key, relationships, relation types, roles and structural constraints, weak entities, enhanced E-R and object modelling
     • Sub classes; super classes, inheritance, specialization and generalization.
   • ER to Relational Mapping Algorithm
   • Mapping EER Model Constructs to relations
4. Database Design and Relational Theory
   • Functional dependencies, boyce/codd normal form
   • Normalization: some generalities
   • Normalization purposes
   • Update anomalies
   • The normal form hierarchy
   • Normalization and constraints
   • Normalization concepts, first, second, third normal form
   • Denormalization Meaning
   • Denormalize for performance
5. SQL-Introductory Part
   • Overview, Characteristics of SQL.
   • Advantage of SQL, SQL data types and literals.
   • Types of SQL commands: DDL, DML, DCL.
   • Basic SQL Queries.
6. SQL-Intermediate Programming
   • Logical operators: BETWEEN, IN, AND, OR and NOT
   • Null Values: Disallowing Null Values, Comparisons Using Null Values
   • Integrity constraints: Primary Key, Not NULL, Unique, Check, Referential key
   • Privileges
   • Grant and Revoke
   • Creating a New User
7. Introduction to Nested Queries
   • Correlated Nested Queries
   • Set -Comparison Operators
   • Aggregate Operators: The GROUP BY and HAVING Clauses
8. Relational Algebra & Basic Relational calculus
   • Relational Algebra
   • Relational Calculus Languages
   • Tuple Calculus
   • Domain Calculus
   • Relational Languages Equivalence
9. Joins
   • Inner joins
   • Outer Joins
   • Left outer
   • Right outer
   • Full outer joins.
10. Overview of views and indexes
11. Relational Data Model
    • Relation, Integrity constraints - domain, entity and
    • Referential integrity constraints
    • Basic Relational Algebra operations, select, project and join operations.
12. Introduction to Oracle SQL and PL/SQL
    • Basics of PL/SQL, PL/SQL structures 
    • Iteration in PL/SQL, PL/SQL tables
    • Dynamic SQL in PL/SQL
    • Nested blocks in PL/SQL
    • Triggers in PL/SQL
    • Stored procedures
    • Functions and Packages
13. Distributed Databases
    • Introduction to Multi-user systems, its advantages, issues related to it.
    • Concurrency control. Basic SQL Injection (including demonstration).
    • Distributed database concepts,
    • Data fragmentation, Replication, and Allocation Techniques for Distributed database design,
    • Types of Distributed database systems.
14. Practical Components
    • Create database
    • Create table
    • Create New user
    • Add a record in the database
    • Delete a record in the database
    • Modify the record in the database
    • Generate queries
    • Generate the report
    • List all the record of database in the different orders
    • Writing nested queries programs using different kinds of joins and operators.
    • Grant and Privileges
    • Changing credential for Admin
    • Backup and restore
    • A simple pl/sql code block
    • Code block components and block labels
    • Declaring variables and assigning values
    • Numeric definitions
    • constrained definitions
    • The if-then-else structure
    • Declaring the cursor
    • Using aggregate variables with cursors

Reading List:

1. Essential Reading
   • Elmasri & Navathe (2002). Fundamentals of Database Systems, Latest Edition. Addison Wesley.
   • Fenerstein,S., Pribyl, B.(2005). ORACLE PL/SQL Programming. 4th Edition. O’Reilly Media Inc.
   • Loney, K. & Koch,G. (2002). ORACLE: The Complete Reference. 1st Edition. McGraw Hill Osborne Media.
   • KEDAR, S. (2011). DATABASE MANAGEMENT SYSTEM (2nd ed.). Technical Publications.
2. Additional Reading
   • Date, C. J. (2008). The Relational Database Dictionary. Extended Edition. Apress.
   • ORACLE Reference Manual.
   • Favre, Liliana. (2003).UML and the unified process. IRM Press
   • Pressman, R.S. (2005). Software Engineering. McGraw-Hill, Delhi Iyar J.

Date: May 30, 2015