Database Management System Notes | B.Sc. (Information Technology) Semester 1 | Mumbai University | munotes

Contents

Module I Databases and transactions, data models, database design and the ER diagram, the relational database model

1. What a Database Is, and What a Database System Is 1
2. The Purpose of a Database System 5
3. Setting Up MySQL and Your First Statements 10
4. The View of Data: Schemas and Instances 14
5. The Three Levels of Abstraction, and Data Independence 18
6. Degrees of Data Abstraction 22
7. Relational Databases: the Table as the Only Structure 26
8. Inside a DBMS: the Parts, and the People 30
9. Client Server, Two Tier and Three Tier Architecture 35
10. Transaction Management: What a Transaction Is 40
11. ACID: the Four Properties Every Transaction Must Have 44
12. Savepoints, and the Limits of Undo 49
13. Why a Data Model Matters 53
14. The Basic Building Blocks 57
15. Business Rules, and Where They Come From 61
16. Turning Business Rules Into a Design 66
17. Before Databases: the File System and Its Problems 71
18. The Hierarchical and Network Models 75
19. The Relational Model, and Why It Won 80
20. The Object, Object Relational and XML Models 85
21. NoSQL, and What It Gave Up 90
22. Database Design: the Whole Process 95
23. The ER Model: Entities, Entity Types and Entity Sets 100
24. Attributes and Their Kinds 105
25. Keys in the ER Model 110
26. Relationships, Relationship Sets, Degree and Roles 115
27. Key Constraints and Cardinality 119
28. Participation Constraints: Total and Partial 124
29. Weak Entities and Identifying Relationships 128
30. Generalization, Specialization and Inheritance 133
31. Aggregation 138
32. Drawing an ER Diagram: Chen Notation 142
33. Crow's Foot Notation, and Reading Someone Else's Diagram 146
34. A Complete ER Diagram, Built From Requirements 150
35. ERD Issue: an Entity or an Attribute 155
36. ERD Issue: an Entity or a Relationship 159
37. ERD Issue: Binary or Ternary 163
38. ERD Issue: the Fan Trap and the Chasm Trap 167
39. Codd's Rules: Rule Zero and Rules One to Four 172
40. Codd's Rules: Five to Eight 177
41. Codd's Rules: Nine to Twelve, and How MySQL Scores 181
42. Relational Schemas: Mapping Entities to Tables 186
43. Mapping Relationships to Tables 190
44. Mapping Multivalued Attributes, Specialization and N-ary Relationships 195
45. The Logical View of Data: What a Relation Is 200
46. Keys in the Relational Model 205
47. The Foreign Key 209
48. Integrity Rules: Entity and Referential Integrity 213
49. Domain Integrity: NOT NULL, DEFAULT and CHECK 217
50. Referential Actions: CASCADE, SET NULL and RESTRICT 221
51. The Elements of a Relational DBMS, in One Place 226

Module II Design theory and normalization, SQL and indexing, transaction management, concurrency control and recovery

1. Functional Dependencies 230
2. Finding the Functional Dependencies of a Table 234
3. Armstrong's Axioms and the Rules of Inference 238
4. Attribute Closure, and Finding Every Candidate Key 242
5. Equivalent FD Sets and the Minimal Cover 247
6. Why Normalize: the Three Anomalies 252
7. First Normal Form 256
8. Second Normal Form 260
9. Third Normal Form 264
10. Boyce Codd Normal Form 269
11. Lossless Join Decomposition 273
12. Dependency Preservation, and 3NF Synthesis 278
13. Decomposing Into BCNF, and What It Costs 283
14. Multivalued Dependencies and Fourth Normal Form 288
15. Join Dependencies and Fifth Normal Form 292
16. Inclusion Dependencies and Domain Key Normal Form 297
17. One Bad Table, Normalized All the Way 302
18. Introduction to SQL: Where It Came From, and What It Is Made Of 307
19. The Five Statement Families: DDL, DML, DQL, DCL and TCL 311
20. Data Types in MySQL 315
21. CREATE DATABASE, CREATE TABLE, and the Constraints That Go With Them 319
22. INSERT, UPDATE and DELETE 323
23. SELECT: Columns, Rows, and the Order They Come Back In 328
24. WHERE: the Operators, and What NULL Does to Them 332
25. Aggregate Functions, GROUP BY and HAVING 336
26. String, Numeric and Date Functions 341
27. Set Operations: UNION, INTERSECT and EXCEPT 346
28. Joining Database Tables: the Inner Join 350
29. Outer Joins, and the FULL OUTER JOIN MySQL Does Not Have 354
30. Self Joins, Cross Joins and Natural Joins 359
31. Complex Queries: Subqueries With IN, ANY and ALL 363
32. Complex Queries: EXISTS and the Correlated Subquery 368
33. Complex Queries: Derived Tables and Common Table Expressions 372
34. Complex Queries: Window Functions 377
35. Views: Creating, Using and Dropping 382
36. Updatable Views and WITH CHECK OPTION 389
37. Triggers: BEFORE, AFTER, and What They Are For 395
38. Writing a Trigger That Enforces a Business Rule 402
39. Schema Modification: ALTER TABLE 408
40. DROP, TRUNCATE and RENAME 414
41. Database Protection: What You Are Protecting Against 419
42. Users, Privileges, GRANT and REVOKE 425
43. Discretionary Access Control, Roles and Least Privilege 431
44. File Structure: the Storage Hierarchy and the Block 437
45. Records and Page Organization 441
46. File Organization: Heap, Sequential, Hashed and Clustered 447
47. The Buffer Manager 452
48. Hashing: Static Hashing and Bucket Overflow 457
49. Hashing: Extendible and Linear Hashing 462
50. Indexing: What an Index Is, Dense and Sparse 467
51. Multilevel Indexes and the B+ Tree 472
52. Indexes in MySQL: Primary, Secondary, Clustered and Covering 479
53. When an Index Does Not Help 485
54. Query Processing: From SQL Text to an Evaluation Plan 491
55. Relational Algebra: the Language a Plan Is Written In 495
56. How a Selection Is Actually Done 500
57. How a Join Is Actually Done 505
58. Sorting, Materialization and Pipelining 511
59. Query Optimization: Equivalence Rules and Heuristics 516
60. Cost Based Optimization and Statistics 521
61. Reading EXPLAIN on a Real Query 526
62. Transaction Processing Concepts: the Transaction and Its States 531
63. The Three Problems Concurrency Causes 535
64. Schedules and What Makes One Serializable 539
65. Testing Conflict Serializability with a Precedence Graph 544
66. View Serializability and Recoverable Schedules 548
67. Isolation Levels Seen on a Real Server 552
68. Concurrency Control: Locks and Two Phase Locking 556
69. Deadlock Prevention and Detection 562
70. Timestamp Ordering 567
71. Validation and Multiversion Concurrency Control 571
72. Granularity and Intention Locks 576
73. Recovery: What Can Fail, and the Log 580
74. Deferred and Immediate Update 584
75. Checkpoints and Recovering After a Crash 588
76. Shadow Paging and Backup Against Media Failure 593

Module P Major Practical 1, Module 2: the ten practicals set on this subject

1. Practical: Keeping the Journal, and the Viva 597
2. Practical 1: Conceptual Design With an ER Diagram 601
3. Practical 2: Databases, Tables and CRUD 604
4. Practical 3: Altering, Dropping, Truncating and Backing Up 608
5. Practical 4: Simple Queries and Aggregate Functions 612
6. Practical 5: Date, String and Math Functions 616
7. Practical 6: Inner and Outer Join Queries 620
8. Practical 7: Subqueries With IN and With EXISTS 624
9. Practical 8: ER Model to Relational Model, and Normalization 627
10. Practical 9: Views, With and Without the Check Option 632
11. Practical 10: DCL Statements, COMMIT and ROLLBACK 636