Database Design is a foundational subject in the field of Computer Science, pivotal for anyone aspiring to excel in data management, analysis, and application development. This subject introduces students to the fundamental principles and practices that underpin the creation and management of efficient, reliable, and scalable database systems.
This textbook content has been developed as per the syllabus set forth by Osmania University. The textbook aims to provide a clear, accessible, and up-to-date introduction to the diverse and rapidly evolving field of Database Management System.
The primary objective of this book is to equip students with the knowledge and skills necessary to design, implement, and manage databases effectively. Starting with the basics of data modelling, students will learn to translate real-world requirements into a structured database design. Key topics include Entity-Relationship (ER) modelling, normalisation techniques, relational database theory, and the use of SQL for database manipulation.
Every unit of this book includes Objective type questions (Fill in the blanks and choose the best answer) with answers and also long and short answer type questions.
Contents –
UNIT I: DATABASE ENVIRONMENT
1.1 Database Environment ‒ Concepts and Definitions
1.2 Traditional File Processing Systems
1.3 Database Approach
1.3.1 Range of Database Applications
1.3.2 Range of Database Applications Advantages, Costs and Risks
1.3.3 Components
1.4 Database Development Process, IS Development
1.4.1 Three Schema Architecture of DBMS
1.4.2 Database Analysis
1.5 E-R Model – Entities, Attributes, Relationships, Degree and Cardinality – Case Studies
1.5.1 Entities, Attributes
1.5.2 Relationships, Degree and Cardinality ‒ Case Studies
UNIT II: ENHANCED E-R MODEL
2.1 Super Types and Subtypes
2.2 Specialization and Generalization
2.3 Constraints
2.4 Disjointness Constraints
2.5 Subtype Discriminator
2.6 Supertypes and Subtypes Hierarchies
2.7 Business Rules
2.8 Scope of Business Rule
2.9 Structural Constraints
2.10 Operational Constraints
2.11 Case Study
2.12 Relational Model
2.13 Integrity Constraints
2.14 Transforming EER Diagrams into Relations
2.15 Normalization
2.16 Merging Relations
2.17 Case Study
UNIT III: RELATIONAL ALGEBRA AND CALCULUS
3.1 Preliminaries, Relational Algebra, Relational Calculus
3.2 Expressive Power of Algebra and Calculus
3.3 SQL
3.3.1 Queries, Constraints, Triggers
3.4 The Form of Basic SQL
3.4.1 Set Operators, Nested Queries
3.4.2 Aggregate Operators
3.4.3 Procedures and Functions
3.4.4 Triggers
UNIT IV: OVERVIEW OF STORAGE AND INDEXING
4.1 Overview of Storage and Indexing
4.1.1 File Organizations
4.1.2 Indexing
4.1.3 Index Data Structures
4.1.4 Comparison of File Organizations
4.2 Tree-Structured Indexing
4.2.1 Indexed Sequential Access Method (ISAM)
4.2.2 B++ Trees
4.2.3 Search
4.2.4 Insert and Delete
4.2.5 B++ Trees in Practice
4.3 Hash-Based Indexing
4.3.1 Static Hashing
4.3.2 Extendible Hashing or Dynamic Hashing
4.3.3 Linear Hashing
4.3.4 Extendible versus Linear Hashing
UNIT V: TRANSACTION MANAGEMENT AND CURRENCY CONTROL
5.1 Transaction
5.1.1 Operations of Transaction
5.1.2 Transaction States
5.1.3 Acid Properties
5.2 Schedule
5.2.1 Types of Schedule
5.3 Concurrent Execution of Transaction
5.3.1 Problems with Concurrent Execution of Transaction
5.4 Concurrency Control
5.4.1 Lock Based Concurrency Control Protocol
5.4.2 Time Stamp Concurrency Control Protocol
5.4.3 Validation Based Concurrency Control Protocol
5.5 Serializability
5.5.1 Types of Serializability
5.6 Recoverability
5.6.1 Recoverable Schedules
5.6.2 Irrecoverable Schedules
5.6.3 Recoverable with Cascading Rollback
5.6.4 Cascadelesss Recoverable Rollback
5.6.5 Checkpoints
5.6.6 Shadow Paging
5.7 Deadlock
5.7.1 Deadlock Avoidance
5.7.2 Deadlock Detection
5.7.3 Deadlock Prevention
DATABASE DESIGN LAB
LAB WORK
