Introduction to Database Management Systems, 1e

✍ Scribed by Kahate, Atul

• Publisher: Pearson Education (Singapore)
• Year: 2004
• Tongue: English
• Leaves: 535
• Series: Always learning
• Category: Library

✦ Synopsis

Description Designed specifically for a single semester, first course onDatabase Systems, there are 4 aspects that differentiate our bookfrom the rest. Simplicity ? Normally, the technology of database systems can be quite difficult to understand. There are so many terms, acronyms and buzzwords associated with the technology that people find highly complicated. This book explains each of these with very simple examples, lucid language and a lot of illustrations. Coverage ? The book covers all the essential aspects of database systems, and also covers the areas of RDBMS. There are very few books, which cover all the theory and practice of database systems. This book covers both these aspects. Illustrative approach ? The book contains over 400 diagrams ? about one per page. This makes this book unique in terms of its visual approach. With this, even the most difficult concepts become a lot easier to understand. Modern topics ? The book covers all the modern topics, such as OODBMS, database systems and the Internet, Data warehousing, Mobile databases, Multimedia databases, Deductive databases. Detailed coverage of Database models, theory and architectures Focus on Transaction management and Concurrency issues A separate chapter on Database security Coverage of Object technology and OODBMS Distributed databases explained in depth Explanation of Data warehousing, Data mining, and OLAP Liberal use and explanation of SQL Unique appendices on Data Structures, Sorting and Searching, Database Management with Access and Case Studies with real life programs in COBOL, DB2 and C. File Systems An Introduction to Database Systems The Relational Model Database Design Transaction Processing and Management Database Security Query Execution and Optimisation Distributed Databases Decision Support Systems, Data Warehousing and Data Mining Object Technology and DBMS Advanced Topics in DBMS

✦ Table of Contents

Cover......Page 1
Foreword......Page 8
Preface......Page 10
Acknowledgements......Page 14
Contents in Brief......Page 16
Contents......Page 18
Chapter 1: File Systems......Page 26
1.2.1 Sample File......Page 27
1.2.2 Records and Fields......Page 28
1.2.3 Master and Transaction Data......Page 29
1.3 Computer Files......Page 30
1.4.1 Record Keys......Page 33
1.4.2 Searching Records......Page 35
1.5.1 What is Sequential Organisation?......Page 36
1.5.3 Problems with Sequential Organisation......Page 37
1.6 Pointers and Chains......Page 40
1.6.1 Problems with One-way Chains......Page 43
1.6.2 Two-way Chains......Page 44
1.6.3 Queries Based on Other Fields......Page 46
1.7.1 Using Indexes......Page 47
1.7.2 Improvements to Index-chain Method......Page 48
1.7.3 Maintaining a List of All Items in the Index......Page 49
1.7.4 Keeping a Count of Records......Page 50
1.7.5 Complex Queries and Query Optimisation......Page 51
1.7.6 Indexed Organisation in Computer Files......Page 52
1.8.3 Hashed Files......Page 57
Chapter Summary......Page 60
Practice Set......Page 61
Chapter 2: Introduction to Database Systems......Page 64
2.1 What is DBMS?......Page 65
2.2 File Management Systems (FMS)......Page 66
2.3 Database Management Systems (DBMS)......Page 68
2.4 FMS versus DBMS......Page 70
2.5 .1 DBMS Basics......Page 77
2.5.2 Internal Process......Page 79
2.5.3 Tables, Rows and Columns......Page 80
2.5.4 SQL and its Power......Page 82
2.6 Brief Introduction to SQL......Page 84
2.6.1 Data Definition Language (DDL)......Page 85
2.6.3 Select, Insert, Update and Delete......Page 86
2.6.4 Multiple Tables and Joins......Page 93
2.6.5 Nested Queries......Page 94
2.6.6 Data Control Language......Page 95
2.7.1 Embedding SQL Statements inside 3GL......Page 96
2.7.2 Embedded SQL Program Lifecycle......Page 97
2.7.3 Cursors......Page 98
2.8 Dynamic SQL......Page 100
2.9.1 The Hierarchical Model......Page 102
2.9.1.1 Retrieval......Page 103
2.9.2 Network Model......Page 104
2.9.2.1 Retrieval......Page 105
2.9.3 Relational Model......Page 106
2.9.3.1 Retrieval......Page 107
2.10 Database System Architecture......Page 108
Key Terms and Concepts......Page 110
Chapter Summary......Page 111
Practice Set......Page 112
Chapter 3: The Relational Model......Page 116
3.1.2 Some Terminology......Page 117
3.1.3 Domains......Page 118
3.2 Relational Database Characteristics......Page 119
3.3.1 Relational Algebra Operators......Page 121
3.3.1.1 Restrict......Page 123
3.3.1.3 Product......Page 124
3.3.1.4 Union......Page 125
3.3.1.5 Intersection......Page 127
3.3.1.6 Difference......Page 129
3.3.1.7 Join......Page 130
3.3.2 Grouping......Page 132
3.4 Relational Calculus......Page 133
3.5.1 Constraints......Page 135
3.5.2 Declarative and Procedural Constraints......Page 136
3.5.2.2 Attribute constraints......Page 137
3.5.2.4 Database constraints......Page 138
3.6 Keys......Page 139
3.6.1 Superkey and Key......Page 140
3.6.2 Composite Key......Page 141
3.6.4 Primary Key......Page 142
3.6.5 Alternate Key or Secondary Key......Page 143
3.6.6 Foreign Key......Page 144
3.6.7.1 Defining primary keys in SQL......Page 147
3.6.7.2 Defining foreign keys in SQL......Page 148
3.7.1 Entity Integrity......Page 151
3.8.1 What is a View?......Page 152
3.8.2 Updating Data through Views......Page 154
Key Terms and Concepts......Page 155
Chapter Summary......Page 156
Practice Set......Page 157
Chapter 4: Database Design......Page 160
4.2 Functional Dependency......Page 161
4.3.1 Decomposition......Page 163
4.3.2 What is Normalisation?......Page 167
4.3.3 First Normal Form (1NF)......Page 168
4.3.4 Second Normal Form (2NF)......Page 170
4.3.5 Third Normal Form (3NF)......Page 172
4.3.6 Boyce-Codd Normal Form (BCNF)......Page 174
4.3.7 Fourth Normal Form (4NF)......Page 177
4.3.8 Fifth Normal Form (5NF)......Page 178
4.3.10 Denormalisation......Page 181
4.4.1 Aspects of E/R Modelling......Page 183
4.4.2 Types of Relationships......Page 185
Chapter Summary......Page 186
Practice Set......Page 187
Chapter 5: Transaction Processing and Management......Page 191
5.1.1 Transactions - Need and Mechanisms......Page 192
5.1.2 Transaction Processing (TP) Monitor......Page 195
5.1.3 Transaction Properties......Page 198
5.2.1 Classification of Recovery......Page 199
5.2.2.1 Failure recovery......Page 200
5.2.2.2 Media recovery......Page 204
5.3.1 Flat Transactions......Page 205
5.3.2 Chained Transactions......Page 206
5.4 Two-Phase Commit......Page 207
5.5 Concurrency Problems......Page 209
5.5.1 Lost Update Problem......Page 210
5.5.2 Dirty (Uncommitted) Read Problem......Page 213
5.5.3 Non-Repeatable Read Problem......Page 216
5.5.4 Phantom Read Problem......Page 220
5.6 Locking......Page 221
5.7.1 Lost Update Problem Revisited......Page 223
5.7.2 Dirty (Uncommitted) Read Problem Revisited......Page 224
5.7.3 Non-repeatable Read Problem Revisited......Page 225
5.7.4 Phantom Read Problem Revisited......Page 227
5.8 Deadlocks......Page 228
5.9 Transaction Serialisability......Page 230
5.10 Two-Phase Locking......Page 231
5.11 Isolation Levels......Page 232
Key Terms and Concepts......Page 234
Chapter Summary......Page 235
Practice Set......Page 237
Chapter 6: Database Security......Page 240
6.1.2 Private Organisations versus Military Classifications......Page 241
6.2.1 Confidentiality......Page 246
6.2.2 Authentication......Page 247
6.2.4 Non-repudiation......Page 248
6.3.1 Types of Cryptography......Page 249
6.3.1.1 Symmetric key cryptography......Page 250
6.3.1.2 Asymmetric key cryptography......Page 251
6.4 Digital Signature......Page 254
6.5.1 Discretionary Control......Page 256
6.6 Users and Database Privileges......Page 257
6.7 Types of Privileges......Page 258
6.8.1 Operations and Privileges......Page 259
6.8.2 Granting Object Privileges......Page 260
6.8.3 Restricting Object Privileges to Certain Columns......Page 263
6.8.4 Granting All Privileges at the Same Time......Page 265
6.8.5 Allowing Others to Grant Privileges......Page 266
6.9 Taking Away Privileges......Page 268
6.10 Filtering Table Privileges......Page 269
6.11 Statistical Databases......Page 271
Chapter Summary......Page 274
Practice Set......Page 275
Chapter 7: Query Execution and Optimisation......Page 278
7.1 Query Processing......Page 279
7.2 Using Indexes......Page 282
7.3 Optimiser Functionality......Page 283
7.3.2 List Merge......Page 284
7.4.1 Simple SELECT......Page 285
7.4.3 JOIN Implementation......Page 290
7.4.5 SET Operator Implementation......Page 291
7.5 Optimisation Recommendations......Page 292
7.6 Database Statistics......Page 297
Chapter Summary......Page 298
Practice Set......Page 299
Chapter 8: Distributed Databases......Page 301
8.1 Distributed Database Concepts......Page 302
8.1.2 Distributed Databases......Page 303
8.2 Distributed Database Architectures......Page 305
8.3 Advantages of Distributed Databases......Page 307
8.4 Distributed Database Requirements......Page 312
8.5.1 Data Fragmentation......Page 315
8.5.2 Data Replication......Page 318
8.6.1 Costs......Page 319
8.6.2 Semi-join......Page 322
8.6.3 Distributed Query Decomposition......Page 324
8.7.1 Concurrency and Recovery Problems......Page 325
8.7.2 Distinguished Copy......Page 326
8.7.2.1 Primary site technique......Page 327
8.7.2.2 Primary site with backup site technique......Page 328
8.7.3 Dealing with Coordinator Failures......Page 329
8.7.4 Voting Method......Page 331
8.7.5 Distributed Recovery......Page 332
8.8.1 Prevent a Deadlock......Page 333
8.8.3 Detect a Deadlock......Page 334
8.9.1 Client/server Computing......Page 335
8.9.2 Client/server Computing and DDBMS......Page 336
8.10 Date’s 12 Rules......Page 338
Key Terms and Concepts......Page 339
Chapter Summary......Page 340
Practice Set......Page 341
Chapter 9: Decision Support Systems, Data Warehousing and Data Mining......Page 344
9.1.2 Need for Information......Page 345
9.1.3 Quality of Information......Page 347
9.1.5 Historical Data......Page 348
9.2 What is a Data Warehouse?......Page 350
9.3 Data Warehousing Concepts......Page 352
9.4.2 Data Marts......Page 357
9.4.2.1 Dependent data mart......Page 358
9.5 Online Analytical Processing (OLAP)......Page 359
9.5.1 Desktop OLAP......Page 360
9.5.3 Multidimensional OLAP (MOLAP)......Page 361
Chapter Summary......Page 363
Practice Set......Page 364
Chapter 10: Object Technology and DBMS......Page 367
10.1 An Introduction to Object Technology......Page 368
10.1.1 Attributes and Methods......Page 369
10.1.2 Messages......Page 370
10.1.3 What is Modelling?......Page 373
10.1.4 Practical Example of an Object......Page 374
10.1.5 Classes......Page 375
10.1.6 The Essence of Classes and Objects......Page 377
10.2 Abstraction......Page 380
10.3 Encapsulation......Page 383
10.4 Inheritance......Page 386
10.5.1 Identifying a Record Uniquely......Page 389
10.5.2 Mapping Classes to Tables......Page 391
10.5.3 Mapping Binary Associations to Tables......Page 394
10.5.4 Modelling Generalisations to Tables......Page 398
10.6.1 Basic Concepts......Page 403
10.6.3 Advantages of OODBMS......Page 406
10.6.4 Examples of ODL, OQL and OML......Page 408
Key Terms and Concepts......Page 411
Chapter Summary......Page 412
Practice Set......Page 413
Chapter 11: Advanced Topics in DBMS......Page 416
11.1.1 Features of Deductive Databases......Page 417
11.1.2 An Overview of Logic......Page 418
11.1.3 Knowledge Representation......Page 419
11.2.3 Hyper Text Markup Language (HTML)......Page 421
11.2.4 Dynamic Web Pages......Page 422
11.2.5 Issues in Web Databases......Page 424
11.3.1 What is Multimedia?......Page 425
11.3.2 Sampling and Quantising......Page 426
11.3.3 Issues in Multimedia Databases......Page 428
11.5.1 What is Mobile Computing?......Page 429
11.5.2 Case Study - WAP......Page 430
11.5.4 Mobile Databases: Problem Areas......Page 432
Key Terms and Concepts......Page 433
Practice Set......Page 434
A.1 Linked Lists......Page 437
A.2 Doubly Linked Lists......Page 442
A.2.1.1 Inserting an item......Page 443
A.2.1.2 Deleting an item......Page 445
A.3 Queues......Page 453
A.3.1 Circular Lists/queues......Page 457
A.4 Binary Trees......Page 458
B.1 Sorting......Page 464
B.1.1 Exchange Sort......Page 465
B.1.2 Selection Sort......Page 467
B.1.3 Quick Sort......Page 469
B.1.4 Tree Sort......Page 471
B.2.1 Sequential Search......Page 473
B.3 Binary Search......Page 475
C.1 Basic Table Design......Page 478
C.2 Inserting Data in a Table......Page 483
C.3 Table Relationships......Page 485
C.4.1 Filtering Records......Page 489
C.5 Creating Reports......Page 491
C.6 Forms......Page 494
C.7 Queries......Page 495
D.2.1 Credit Card Database......Page 498
D.2.2 Employee Database......Page 500
D.2.3 Weather Database......Page 502
D.2.4 Cricket Database......Page 503
D.3 Programming Examples......Page 504
D.3.1 Program for SELECT Operation......Page 505
D.3.2 Program for SELECT with File Operations......Page 507
D.3.3 Program for Basic Cursor Operations......Page 513
D.3.4 Program for Advanced Cursor Operations......Page 519
D.3.5 Program in C for SELECT Operation......Page 527
Index......Page 529