Contents
Unit 1-INTRODUCTION 1-54
1.1 Data 1
1.1.1 Three-layer data architecture 1
1.2 Information 2
1.3 Data Warehous 2
1.4 Data Dictionary 2
1.5 Records 2
1.6 Files 3
1.7 Database 3
1.8 Database Management System 3
1.8.1 Application of database system 3
1.8.2 Functions and services of DBMS 4
1.8.3 Database vs. file systems 5
1.8.4 Advantages of file processing system 5
1.8.5 Disadvantages of files processing system 5
1.8.6 Advantages of DBMS 6
1.8.7 Disadvantages of DBMS 8
1.9 Data Abstraction 9
1.10 Instances and Schemas 9
1.11 Data Independence 10
1.12 Data Models 11
1.12.1 The entity-relationship model 11
1.12.2 Relational model 12
1.12.3 Object-oriented data model 12
1.12.4 The object-relational data model 13
1.12.5 Hierarchical model 14
1.12.6 The network data model 14
1.13 'JYpes of Database Systems 15
1.13.1 Centralised database system 15
1.13.2 Distributed database system 16
1.13.3 Parallel database system 17
1.13.4 Client/Server database system 19
1.14 Database Languages 20
1.14.1 Data definition language 20
1.14.2 Data-manipulation language 21
1.14.3 Data control language 21
1.14.4 Data query language 22
1.15 DBMS Interfaces 22
1.16 Database Users and Administrators
1.16.1 Database administrator
1.16.2 Database users
1.17 Overall Database Structure
1.17.1 Storage manager'
1.17.2 Querry processor
1.18 Fourth-Generation Language (4GL)
1.19 Metadata
1.19.1 lYpes of metadata
1.20 ER-Model Concepts
1.20.1 Entity
1.20.2 Attributes
1.21 Relationships and Relationship Sets
1.22 Constraints
1.22.1 Mapping cardinalities
1.22.2 Participation constraints
1.23 Existence Dependency
1.24 Keys
1.25 Association
1.26 Specialization
1.27 Generalization
1.28 Aggregation
1.29 Relationships of Higher Degree
1.30 Reduction of an E-R Diagram to Tables
1.30.1 Tabular representation of strong entity set
1.30.2 Tabular representation of weak entity set
1.30.3 Tabular representation of relationship sets
Solved Problems
Review Questions
Unit 2-RELATIONAL DATA MODEL CONCEPTS
2.1 Relational Data Model Concepts
2.2 Integrity Constraints
2.2.1 Entity integrity
2.2.2 Referential integrity
2.3 Domain Constraints
2.4 Relational Algebra
2.4.1 Select operation
2.4.2 Project operation
2.4.3 Union operation
2.4.4 Set-difference operation
2.4.5 Cartesian product operation
2.4.6 Division operation 60
2.4.7 Rename operation 61
2.4.8 Join 61
2.4.8.1 Natural join 61
2.4.8.2 Semi join 62
2.4.8.3 Anti join 62
2.4.8.4 Outer join 63
2.4.9 Projection 63
2.5 Relational Calculas 64
2.6 The Domain Relational Calculus 65
2.7 Introduction to SOL 66
2.7.1 Data types 67
2.7.2 lYpes of SOL commands 67
2.7.3 Insertion of Data into Tables 68
2.7.4 Select command 68
2.7.5 Elimination of duplicates from the select statement 69
2.7.6 Sorting data in a table 69
2.7.7 Creating a table from a table 69
2.7.8 Inserting data into a table from another table 69
2.7.9 Delete operations 70
2.7.10 Update command 70
2.7.11 Modifying the structure of tables 71
2.7.12 Renaming command 71
2.7.13 Destroying table 71
2.7.14 Logical operators 71
2.7.15 Range searching 72
2.7.16 Unique key 73
2.7.17 Primary key 73
2.7.18 Foreign key 73
2.7.19 Aggregate functions 73
2.7.20 Subqueries 76
2.7.21 Joins 77
2.7.22 Union clause 78
2.7.23 Intereset clause 79
2.7.24 Minus clause 80
2.8 Views 82
2.9 Indexes 82
2.10 Row Num in SOL Statement 83
2.11 Sequences 84
2.12 Cursor 85
2.13 Database Triggers 86
2.14 Oracle Packages 87
2.15 Assertions
Solved Problems
Review Questions
Unit 3-DATABASE DESIGN AND NORMALIZATION
3.1 Database Design
3.2 Decomposition
3.3 Universal Relation
3.4 Functional Dependency
3.5 Prime Attribute
3.5.1 Non-prime attribute
3:6 Armstrong's Axioms
3.7 Closure of Set of Functional Dependencies
3.8 Non-Redundant Covers
3.9 Canonical Cover or Minimal Set of FD's
3.10 Normalization
3.10.1 First normal form (INF)
3.10.2 Second normal form (2NF)
3.10.3 Third normal form (3NF)
3.10.4 Boyce-Codd normal form (BCNF)
3.10.5 Fourth normal form (4NF)
3.10.6 Fifth normal form (5NF)
3.10.7 Sixth normal form
3.10.8 Domain/key normal form
3.10.9 Conclusion of database normalization
3.11 Lossless-join Decomposition
Solved Problems
Review Questions
.
Unit 4-TRANSACTION PROCESSING CONCEPTS
4.1 Transaction Concept
4.2 Transaction Access Data
4.3 Transaction State
4.4 Concurrent Excutions
4.4.1 Schedules
4.5 Serializability
4.5.1 Conflict serializability
4.5.2 View serializability
4.5.3 Testing of serializability
4.6 Recoverability
4.6.1 Recoverable schedules
4.6.2 Cascadclcss schedules
4.7 Transaction Recovery 163
4.7.1 Failure classification 164
4.7.2 Types of transaction recovery 165
4.8 Log Based Recovery 167
4.9 Check Points 168
4.10 Deadlocks 169
4.10.1 Deadlock handling 169
4.10.1.1 Deadlock prevention 170
4.10.1.2 Deadlock detection and recovery 170
4.11 Concept of Phantom Deadlock 172
Solved Problems 173-177
Review Questions 177
Unit 5-CONCURRENCY CONTROL TECHNIQUES 180-214
5.1 Locking Techniques for Concurrency Control 180
5.1.1 Lock 180
5.1.2 The two-phase locking protocol 181
5.2 Concurency Co';trol Based on Timestamp Protocol 183
5.3 Validation (Optimistic)-Based Protocol 185
5.4 Multiple Granularity Locking 186
5.5 Multi-Version Schemes 188
5.6 Multi-Version 1Wo-Phase Locking 189
5.7 Recovery with Concurrent Transactions 190
5.8 Distributed Database 190
5.8.1 Classification of distributed database 191
5.8.2 Functions of distributed database 192
5.8.3 Advantages of distributed database 193
5.8.4 Disadvantages of distributed database 194
5.8.5 Architecture of distribtued database 194
5.8.6 Distributed database system design 196
5.8.7 Transaction processing in distributed system 197
5.8.7.1 System structure 197
5.8.7.2 System failure modes 198
5.8.8 Data fragmentation 198
5.8.9 Data replication and allocation 199
5.8.10 Data allocation 200
5.8.11 Overview of concurrency control 201
5.8.12 Distributed recov(!ry 202
5.8.13 1Wo-phase commit protocol 202
5.8.14 Handling of failures 203
Solved Problems 204-213
Review Questions 213
APPENDIX
Appendix A : Lab Assignment
Appendix B : Tick the Appropriate Answer
Appendix C : UPTU Question Paper
Appendix D : DBMS Interview Questions and Answers
INDEX
