ZeePedia

Table of Contents

Introduction to Databases and Traditional File Processing Systems >>
Database Management System
(CS403)
img
Database Management System (CS403)
VU
DATABASE MANAGEMENT SYSTEM ......................................................................................... 1
LECTURE NO. 01 ........................................................................................................................... 9
Reading Material..................................................................................................................... 9
Overview of Lecture ................................................................................................................ 9
Introduction to the course ................................................................................................... 9
Database definitions: ........................................................................................................ 10
Importance of the Databases ........................................................................................... 12
Databases and Traditional File Processing Systems ....................................................... 12
Advantages of Databases ................................................................................................ 15
LECTURE NO. 02 ......................................................................................................................... 17
Reading Material................................................................................................................... 17
Overview of Lecture .............................................................................................................. 17
Difference between Data and Information........................................................................ 17
Further Advantages of Database Systems:...................................................................... 19
Cost Involved: ................................................................................................................... 21
Importance of Data ........................................................................................................... 22
Levels of Data................................................................................................................... 22
Users of Database Systems: ............................................................................................ 24
LECTURE NO. 03 ......................................................................................................................... 31
Reading Material................................................................................................................... 31
Overview of Lecture .............................................................................................................. 31
Database Architecture: ..................................................................................................... 31
The Architecture: .............................................................................................................. 33
External View (Level, Schema or Model): ........................................................................ 35
Conceptual or Logical View: ............................................................................................. 37
LECTURE NO. 04 ......................................................................................................................... 40
Reading Material................................................................................................................... 40
O v e r v i e w of L e c t u r e ....................................................................................................... 40
In t e r n a l or P h y s i c a l V i e w / Sc h e m a ...................................................................... 40
Da t a I n d e p e n d e n c e : ................................................................................................... 43
F u n c t i o n s of D B M S .................................................................................................... 45
LECTURE NO. 05 ......................................................................................................................... 50
Reading Material................................................................................................................... 50
Overview of Lecture .............................................................................................................. 50
Database Development Process ...................................................................................... 51
Preliminary Study: ............................................................................................................ 51
Database Development Process: Approach 2 ................................................................. 54
Tools Used for Database System Development: ............................................................. 56
Data Flow Diagrams: ........................................................................................................ 56
Types of DFD.................................................................................................................... 60
LECTURE NO. 06 ......................................................................................................................... 63
Reading Material................................................................................................................... 63
Overview of Lecture .............................................................................................................. 63
Detailed Data Flow Diagram:............................................................................................ 63
Data Dictionary ................................................................................................................. 64
Database Design Phase................................................................................................... 67
2
img
Database Management System (CS403)
VU
Data Model ....................................................................................................................... 68
Types of Data Models....................................................................................................... 68
Types of Database Design ............................................................................................... 69
LECTURE NO. 07 ......................................................................................................................... 70
Reading Material................................................................................................................... 70
Overview of Lecture .............................................................................................................. 70
Entity-Relationship Data Model ........................................................................................ 70
The Entity.......................................................................................................................... 71
Classification of entity types ............................................................................................. 74
Attribute ............................................................................................................................ 75
Types of Attributes............................................................................................................ 77
Summary: ......................................................................................................................... 79
Exercises: ......................................................................................................................... 79
LECTURE NO. 08 ......................................................................................................................... 80
Reading Material................................................................................................................... 80
Overview of Lecture .............................................................................................................. 80
Attributes........................................................................................................................... 80
The Keys........................................................................................................................... 80
LECTURE NO. 09 ......................................................................................................................... 85
Reading Material................................................................................................................... 85
Overview of Lecture .............................................................................................................. 85
Relationships .................................................................................................................... 85
Types of Relationships ..................................................................................................... 87
LECTURE NO. 10 ......................................................................................................................... 91
Reading Material................................................................................................................... 91
Overview of Lecture .............................................................................................................. 91
Roles in Relationships ...................................................................................................... 95
Dependencies................................................................................................................... 97
Enhancements in E-R Data Model: .................................................................................. 98
Super-type and Subtypes ................................................................................................. 98
Summary: ......................................................................................................................... 99
LECTURE NO. 11 ....................................................................................................................... 100
Reading Material................................................................................................................. 100
Overview of Lecture ............................................................................................................ 100
Inheritance Is .................................................................................................................. 100
Super types and Subtypes ............................................................................................. 101
Specifying Constraints .................................................................................................... 103
Completeness Constraint ............................................................................................... 103
Disjointness Constraint................................................................................................... 104
Subtype Discriminator .................................................................................................... 108
LECTURE NO. 12 ....................................................................................................................... 110
Reading Material................................................................................................................. 110
Overview of Lecture ............................................................................................................ 110
Steps in the Study of system .......................................................................................... 110
LECTURE NO. 13 ....................................................................................................................... 118
Reading Material................................................................................................................. 118
Overview of Lecture ............................................................................................................ 118
3
img
Database Management System (CS403)
VU
Identification of Entity Types of the Examination System .............................................. 118
Relationships and Cardinalities in between Entities....................................................... 120
Conceptual Database Design......................................................................................... 122
Logical Database Design................................................................................................ 122
Conclusion ...................................................................................................................... 123
LECTURE NO. 14 ....................................................................................................................... 124
Reading Material................................................................................................................. 124
Overview of Lecture ............................................................................................................ 124
Relational Data Model .................................................................................................... 125
Introduction to the Relational Data model ...................................................................... 126
Mathematical Relations .................................................................................................. 129
Database Relations ........................................................................................................ 130
Summary ........................................................................................................................ 130
Exercise: ......................................................................................................................... 131
LECTURE NO. 15 ....................................................................................................................... 132
Reading Material................................................................................................................. 132
Overview of Lecture ............................................................................................................ 132
Database and Math Relations ........................................................................................ 132
Degree of a Relation....................................................................................................... 133
LECTURE NO. 16 ....................................................................................................................... 140
Reading Material................................................................................................................. 140
Overview of Lecture: ........................................................................................................... 140
Mapping Relationships ................................................................................................... 140
Binary Relationships ....................................................................................................... 140
Unary Relationship ......................................................................................................... 144
Data Manipulation Languages........................................................................................ 146
Relational Algebra .......................................................................................................... 147
Exercise: ......................................................................................................................... 147
LECTURE NO. 17 ....................................................................................................................... 148
Reading Material................................................................................................................. 148
Overview of Lecture: ........................................................................................................... 148
The Project Operator ...................................................................................................... 150
LECTURE NO. 18 ....................................................................................................................... 157
Reading Material................................................................................................................. 157
Overview of Lecture: ........................................................................................................... 157
Types of Joins................................................................................................................. 157
Theta Join: ...................................................................................................................... 157
Equi­Join: ....................................................................................................................... 159
Natural Join:.................................................................................................................... 159
Outer Join: ...................................................................................................................... 161
Semi Join: ....................................................................................................................... 161
Relational Calculus ......................................................................................................... 162
Tuple Oriented Relational Calculus: ............................................................................... 162
Domain Oriented Relational Calculus: ........................................................................... 162
Normalization.................................................................................................................. 162
LECTURE NO. 19 ....................................................................................................................... 164
Reading Material................................................................................................................. 164
Overview of Lecture: ........................................................................................................... 164
4
img
Database Management System (CS403)
VU
Functional Dependency.................................................................................................. 164
Inference Rules .............................................................................................................. 166
Normal Forms ................................................................................................................. 166
Summary ........................................................................................................................ 167
Exercise: ......................................................................................................................... 168
LECTURE NO. 20 ....................................................................................................................... 169
Reading Material................................................................................................................. 169
Overview of Lecture: ........................................................................................................... 169
Second Normal Form ..................................................................................................... 169
Third Normal Form ......................................................................................................... 171
Boyce - Codd Normal Form............................................................................................ 173
Higher Normal Forms ..................................................................................................... 175
Summary ........................................................................................................................ 175
Exercise: ......................................................................................................................... 175
LECTURE NO. 21 ....................................................................................................................... 176
Reading Material................................................................................................................. 176
Overview of Lecture: ........................................................................................................... 176
Normalization Summary ................................................................................................. 176
Normalization Example................................................................................................... 177
Physical Database Design.............................................................................................. 181
Summary ........................................................................................................................ 182
LECTURE NO. 22 ....................................................................................................................... 183
Overview of Lecture ............................................................................................................ 183
The Physical Database Design Considerations and Implementation ............................ 183
DESIGNING FIELDS...................................................................................................... 184
CODING AND COMPRESSION TECHNIQUES:........................................................... 185
LECTURE NO. 23 ....................................................................................................................... 187
Reading Material................................................................................................................. 187
Overview of Lecture ............................................................................................................ 187
Physical Record and De-normalization .......................................................................... 187
Partitioning...................................................................................................................... 187
Physical Record and Denormalization ........................................................................... 187
Denormalization Situation 1:........................................................................................... 188
Partitioning...................................................................................................................... 189
LECTURE NO. 24 ....................................................................................................................... 191
Reading Material................................................................................................................. 191
Overview of Lecture ............................................................................................................ 191
Vertical Partitioning......................................................................................................... 191
Replication ...................................................................................................................... 192
Reduced training cost..................................................................................................... 194
MS SQL Server .............................................................................................................. 194
LECTURE NO. 25 ....................................................................................................................... 196
Reading Material................................................................................................................. 196
Overview of Lecture ............................................................................................................ 196
Rules of SQL Format...................................................................................................... 196
Data Types in SQL Server.............................................................................................. 197
Summary: ....................................................................................................................... 200
Exercise: ......................................................................................................................... 200
5
img
Database Management System (CS403)
VU
LECTURE NO. 26 ....................................................................................................................... 201
Reading Material................................................................................................................. 201
Overview of Lecture ............................................................................................................ 201
Categories of SQL Commands....................................................................................... 201
Summary ........................................................................................................................ 205
Exercise: ......................................................................................................................... 205
LECTURE NO. 27 ....................................................................................................................... 206
Reading Material................................................................................................................. 206
Overview of Lecture ............................................................................................................ 206
Alter Table Statement..................................................................................................... 206
LECTURE NO. 28 ....................................................................................................................... 210
Reading Material................................................................................................................. 210
Select Statement ............................................................................................................ 211
Attribute Allias................................................................................................................. 213
LECTURE NO. 29 ....................................................................................................................... 215
Reading Material................................................................................................................. 215
Overview of Lecture ............................................................................................................ 215
Data Manipulation Language.......................................................................................... 215
LECTURE NO. 30 ....................................................................................................................... 220
Reading Material................................................................................................................. 220
Overview of Lecture ............................................................................................................ 220
ORDER BY Clause......................................................................................................... 220
Functions in SQL ............................................................................................................ 221
GROUP BY Clause ........................................................................................................ 222
HAVING Clause.............................................................................................................. 223
Cartesian Product ........................................................................................................... 224
Summary ........................................................................................................................ 225
LECTURE NO. 31 ....................................................................................................................... 226
Reading Material................................................................................................................. 226
Overview of Lecture ............................................................................................................ 226
Inner Join ........................................................................................................................ 226
Outer Join ....................................................................................................................... 228
Semi Join ........................................................................................................................ 230
Self Join .......................................................................................................................... 231
Subquery ........................................................................................................................ 232
Summary ........................................................................................................................ 236
Exercise: ......................................................................................................................... 237
LECTURE NO. 32 ....................................................................................................................... 238
Reading Material................................................................................................................. 238
Overview of Lecture ............................................................................................................ 238
Application Programs ..................................................................................................... 238
User Interface ................................................................................................................. 239
Forms.............................................................................................................................. 240
Tips for User Friendly Interface ...................................................................................... 243
LECTURE NO. 33 ....................................................................................................................... 246
Reading Material................................................................................................................. 246
Overview of Lecture.......................................................................................................... 246
6
img
Database Management System (CS403)
VU
LECTURE NO. 34 ....................................................................................................................... 255
Reading Material................................................................................................................. 255
Overview of Lecture ............................................................................................................ 255
LECTURE NO. 35 ....................................................................................................................... 260
Reading Material................................................................................................................. 260
Overview of Lecture ............................................................................................................ 260
File Organizations........................................................................................................... 260
LECTURE NO. 36 ....................................................................................................................... 265
Reading Material................................................................................................................. 265
Overview of Lecture ............................................................................................................ 265
Hashing........................................................................................................................... 265
Hash Functions............................................................................................................... 266
Hashed Access Characteristics...................................................................................... 266
Mapping functions .......................................................................................................... 266
Open addressing: ........................................................................................................... 269
LECTURE NO. 37 ....................................................................................................................... 270
Reading Material................................................................................................................. 270
Overview of Lecture: ........................................................................................................... 270
Index ............................................................................................................................... 270
Index Classification......................................................................................................... 272
Summary ........................................................................................................................ 274
LECTURE NO. 38 ....................................................................................................................... 275
Reading Material................................................................................................................. 275
Overview of Lecture ............................................................................................................ 275
Ordered Indices .............................................................................................................. 275
Clustered Indexes........................................................................................................... 275
Non-clustered Indexes.................................................................................................... 276
Dense and Sparse Indices ............................................................................................. 276
Multi-Level Indices .......................................................................................................... 277
LECTURE NO. 39 AND 40 ......................................................................................................... 280
Reading Material................................................................................................................. 280
Overview of Lecture ............................................................................................................ 280
Views .............................................................................................................................. 280
To Focus on Specific Data ............................................................................................. 280
Characteristics /Types of Views: .................................................................................... 283
Characteristics of Views ................................................................................................. 286
LECTURE NO. 41 ....................................................................................................................... 288
Reading Material................................................................................................................. 288
Overview of Lecture ............................................................................................................ 288
Updating Multiple Tables ................................................................................................ 288
Materialized Views.......................................................................................................... 289
Transaction Management............................................................................................... 291
LECTURE NO. 42 ....................................................................................................................... 293
Reading Material................................................................................................................. 293
Overview of Lecture ............................................................................................................ 293
The Concept of a Transaction ........................................................................................ 293
Transactions and Schedules .......................................................................................... 294
7
img
Database Management System (CS403)
VU
Concurrent Execution of Transactions ........................................................................... 295
Serializability................................................................................................................... 296
Lock-Based Concurrency Control................................................................................... 297
Deadlocks ....................................................................................................................... 299
LECTURE NO. 43 ....................................................................................................................... 302
Reading Material................................................................................................................. 302
Overview of Lecture ............................................................................................................ 302
Incremental Log with Deferred Updates ......................................................................... 302
Incremental Log with Immediate Updates ...................................................................... 305
Concurrency Control....................................................................................................... 307
Summary ........................................................................................................................ 308
LECTURE NO. 44 ....................................................................................................................... 310
Reading Material................................................................................................................. 310
Overview of Lecture ............................................................................................................ 310
Uncommitted Update Problem ....................................................................................... 310
Inconsistent Analysis ...................................................................................................... 311
Serial Execution.............................................................................................................. 312
Serializability................................................................................................................... 315
Locking ........................................................................................................................... 315
Summary ........................................................................................................................ 316
LECTURE NO. 45 ....................................................................................................................... 318
Reading Material................................................................................................................. 318
Overview of Lecture: ........................................................................................................... 318
Locking Idea ................................................................................................................... 319
DeadLock........................................................................................................................ 320
DeadLock Handling ........................................................................................................ 320
Wait ­ for Graph: ............................................................................................................ 320
Deadlock Resolution....................................................................................................... 323
Timestamping rules ........................................................................................................ 324
8
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules