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 176
41. Codd's Rules: Nine to Twelve, and How MySQL Scores 180
42. Relational Schemas: Mapping Entities to Tables 185
43. Mapping Relationships to Tables 189
44. Mapping Multivalued Attributes, Specialization and N-ary Relationships 194
45. The Logical View of Data: What a Relation Is 199
46. Keys in the Relational Model 204
47. The Foreign Key 208
48. Integrity Rules: Entity and Referential Integrity 212
49. Domain Integrity: NOT NULL, DEFAULT and CHECK 216
50. Referential Actions: CASCADE, SET NULL and RESTRICT 220
51. The Elements of a Relational DBMS, in One Place 225

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

1. Functional Dependencies 229
2. Finding the Functional Dependencies of a Table 233
3. Armstrong's Axioms and the Rules of Inference 237
4. Attribute Closure, and Finding Every Candidate Key 241
5. Equivalent FD Sets and the Minimal Cover 246
6. Why Normalize: the Three Anomalies 251
7. First Normal Form 255
8. Second Normal Form 259
9. Third Normal Form 263
10. Boyce Codd Normal Form 268
11. Lossless Join Decomposition 272
12. Dependency Preservation, and 3NF Synthesis 276
13. Decomposing Into BCNF, and What It Costs 281
14. Multivalued Dependencies and Fourth Normal Form 286
15. Join Dependencies and Fifth Normal Form 290
16. Inclusion Dependencies and Domain Key Normal Form 295
17. One Bad Table, Normalized All the Way 299
18. Introduction to SQL: Where It Came From, and What It Is Made Of 304
19. The Five Statement Families: DDL, DML, DQL, DCL and TCL 308
20. Data Types in MySQL 312
21. CREATE DATABASE, CREATE TABLE, and the Constraints That Go With Them 316
22. INSERT, UPDATE and DELETE 320
23. SELECT: Columns, Rows, and the Order They Come Back In 325
24. WHERE: the Operators, and What NULL Does to Them 329
25. Aggregate Functions, GROUP BY and HAVING 333
26. String, Numeric and Date Functions 338
27. Set Operations: UNION, INTERSECT and EXCEPT 342
28. Joining Database Tables: the Inner Join 346
29. Outer Joins, and the FULL OUTER JOIN MySQL Does Not Have 350
30. Self Joins, Cross Joins and Natural Joins 355
31. Complex Queries: Subqueries With IN, ANY and ALL 359
32. Complex Queries: EXISTS and the Correlated Subquery 364
33. Complex Queries: Derived Tables and Common Table Expressions 368
34. Complex Queries: Window Functions 373
35. Views: Creating, Using and Dropping 378
36. Updatable Views and WITH CHECK OPTION 385
37. Triggers: BEFORE, AFTER, and What They Are For 391
38. Writing a Trigger That Enforces a Business Rule 398
39. Schema Modification: ALTER TABLE 404
40. DROP, TRUNCATE and RENAME 410
41. Database Protection: What You Are Protecting Against 415
42. Users, Privileges, GRANT and REVOKE 421
43. Discretionary Access Control, Roles and Least Privilege 427
44. File Structure: the Storage Hierarchy and the Block 433
45. Records and Page Organization 437
46. File Organization: Heap, Sequential, Hashed and Clustered 443
47. The Buffer Manager 448
48. Hashing: Static Hashing and Bucket Overflow 453
49. Hashing: Extendible and Linear Hashing 458
50. Indexing: What an Index Is, Dense and Sparse 463
51. Multilevel Indexes and the B+ Tree 468
52. Indexes in MySQL: Primary, Secondary, Clustered and Covering 475
53. When an Index Does Not Help 481
54. Query Processing: From SQL Text to an Evaluation Plan 487
55. Relational Algebra: the Language a Plan Is Written In 491
56. How a Selection Is Actually Done 496
57. How a Join Is Actually Done 501
58. Sorting, Materialization and Pipelining 507
59. Query Optimization: Equivalence Rules and Heuristics 512
60. Cost Based Optimization and Statistics 517
61. Reading EXPLAIN on a Real Query 522
62. Transaction Processing Concepts: the Transaction and Its States 527
63. The Three Problems Concurrency Causes 531
64. Schedules and What Makes One Serializable 535
65. Testing Conflict Serializability with a Precedence Graph 540
66. View Serializability and Recoverable Schedules 544
67. Isolation Levels Seen on a Real Server 548
68. Concurrency Control: Locks and Two Phase Locking 552
69. Deadlock Prevention and Detection 558
70. Timestamp Ordering 563
71. Validation and Multiversion Concurrency Control 567
72. Granularity and Intention Locks 572
73. Recovery: What Can Fail, and the Log 576
74. Deferred and Immediate Update 580
75. Checkpoints and Recovering After a Crash 584
76. Shadow Paging and Backup Against Media Failure 589

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

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