Objectives
This course will help the students to acquire the basic knowledge and practical skills relating to Relational Database Management Systems. After going through this course a student should be able to:
• Describe the Database and Database Management System advantages
• Describe the file organization schemes for DBMS
• Describe various types of index mechanisms for DBMS
• Create small client server applications
• Write SQL queries, create forms and queries
• Draw E R diagrams and design of databases
• Describe and use features for Recovery, Concurrency and Security in DBMS.
Syllabus
BLOCK 1: The Database Management System Concepts
Unit 1: The Basic Concepts
• Need for a Database Management System
o The file based system
o Limitations of file based system
o The Database Approach
• The Logical DBMS Architecture
o Three level architecture of DBMS or logical DBMS architecture
o Mappings between levels and data independence
o The need for three level architecture
• Physical DBMS Architecture
o DML Precompiler
o DDL Compiler
o File Manager
o Database Manager
o Query Processor
o Database Administrator
o Data files indices and Data Dictionary
• Commercial Database Architecture
• Data Models
Unit 2: Relational And ER Models
• The Relational Model
o Domains, Attributes, Tuple and Relation
o Super keys Candidate keys and Primary keys for the Relations
• Relational Constraints
o Domain Constraint
o Key Constraint
o Integrity Constraint
o Update Operations and Dealing with Constraint Violations
• Relational Algebra
o Basic Set Operation
o Cartesian Product
o Relational Operations
• Entity Relationship (ER) Model
o Entities
o Attributes
o Relationships
o More about Entities and Relationships
o Defining Relationship for College Database
• E-R Diagram
• Conversion of E-R Diagram to Relational Database
Unit 3: Database Integrity and Normalisation
• Relational Database Integrity
o The Keys
o Referential Integrity
o Entity Integrity
• Redundancy and Associated Problems
• Single-Valued Dependencies
• Single-Valued Normalisation
o The First Normal Form
o The Second Normal Form
o The Third Normal Form
o Boyce Codd Normal Form
• Desirable Properties of Decomposition
o Attribute Preservation
o Lossless-join Decomposition
o Dependency Preservation
o Lack of redundancy
• Rules of Data Normalisation
o Eliminate Repeating Groups
o Eliminate Redundant Data
o Eliminate Columns Not Dependent on Key
Unit 4: ile Organisation in DBMS
• Physical Database Design Issues
• Storage of Database on Hard Disks
• File Organisation and Its Types
o Heap files (Unordered files)
o Sequential File Organisation
o Indexed (Indexed Sequential) File Organisation
o Hashed File Organisation
• Types of Indexes
• Index and Tree Structure
• Multi-key File Organisation99
o Need for Multiple Access Paths
o Multi-list File Organisation
o Inverted File Organisation
• Importance of File Organisation in Databases
BLOCK 2: Structured Query Language and Transaction Management
Unit 1: The Structures Query Language
• What is SQL?
• Data Definition Language
• Data Manipulation Language
• Data Control
• Database Objects: Views, Sequences, Indexes and Synonyms
o Views
o Sequences
o Indexes and Synonyms
• Table Handling
• Nested Queries
Unit 2: Transactions and Concurrency Management
• The Transactions
• The Concurrent Transactions
• The Locking Protocol
o Serialisable Schedules
o Locks
o Two Phase Locking (2PL)
• Deadlock and its Prevention
• Optimistic Concurrency Control
Unit 3: Database Recovery and Security
• What is Recovery?
o Kinds of failures
o Failure controlling methods
o Database errors
• Recovery Techniques
• Security & Integrity
o Relationship between Security and Integrity
o Difference between Operating System and Database Security
• Authorization
Unit 4: Distributed and Client Server Databases
• Need for Distributed Database Systems
• Structure of Distributed Database
• Advantages and Disadvantages of DDBMS
o Advantages of Data Distribution
o Disadvantages of Data Distribution
• Design of Distributed Databases
o Data Replication
o Data Fragmentation
• Client Server Databases
o Emergence of Client Server Architecture
o Need for Client Server Computing
o Structure of Client Server Systems
o Advantages of Client Server Systems
BLOCK 3: Application Development: Development of a Hospital Management System
• Need to Develop the Hospital Management System (An HMS)
• Creating a Database for HMS
• Developing Front End Forms
• Reports
• Using Queries and Record set
BLOCK 4: Study Centre Management System: A Case Study
• Software Development Process: Analysis
• System Designing
• Issues relating to Software Development, Testing and Maintenance
---------------------------------------------------------------------------------------------------------------------------
1st and 2nd block are sufficient for theory paper.
most common questions are:
1. Write query on given relation. (book examples are sufficient for its practice)
2. making ERD.
3. Deadlock
4. transctions
5. 2tier & 3tier client/server architecture.......etc
remaining questions u can see from previous year papers