Fundamentals of Database Systems Notes | B.Sc. (Computer Science) Semester 1 | Mumbai University | munotes

Contents

Module I Introduction to DBMS, data models, the ER model and ER to table, DDL and DML statements

1. What a Database Is, and What a DBMS Does 1
2. Why Not Just Files: the Advantages of a DBMS 6
3. The Three Levels of Abstraction 10
4. Data Independence 14
5. The Architecture of a DBMS 18
6. What a Data Model Is 22
7. Client/Server Architecture 26
8. Object Based Logical Models 30
9. Record Based Logical Models: Relational, Hierarchical and Network 34
10. The Entity Relationship Model: Entities, Attributes and Entity Sets 38
11. Relationships and Relationship Sets 42
12. Key Constraints and the Cardinality of a Relationship 46
13. Participation Constraints 50
14. Weak Entity Sets 54
15. Generalization, Specialization and Aggregation 58
16. Conceptual Design: Entity or Attribute 62
17. Conceptual Design: Entity or Relationship 66
18. Conceptual Design: Binary or Ternary 70
19. Constraints Beyond the ER Model 73
20. Turning an Entity Set Into a Table 77
21. Turning a Relationship Set Into Tables 81
22. Meeting MySQL: the Server, the Client and Your First Database 85
23. Data Types 89
24. Creating a Table 94
25. Integrity Constraints: PRIMARY KEY, NOT NULL, DEFAULT and CHECK 98
26. Altering a Table 102
27. Renaming, Dropping and Truncating Tables 106
28. Viewing the Structure of a Table 109
29. INSERT: Putting Rows In 112
30. UPDATE and DELETE: Changing and Removing Rows 116
31. SELECT: All Columns and Named Columns 120
32. DISTINCT: Unique Records 124
33. The WHERE Clause 127
34. IN and BETWEEN 131
35. ORDER BY and LIMIT 135
36. Aggregate Functions: COUNT, MIN, MAX, AVG and SUM 139
37. GROUP BY 142
38. HAVING 146

Module II The relational data model, functions, joins and subqueries, normal forms, database protection, views and DCL

1. The Relational Data Model: Domains, Attributes, Tuples and Relations 150
2. Relational Model Notation 154
3. Characteristics of Relations 157
4. Relational Constraints: the Primary Key 161
5. Referential Integrity and the Foreign Key 165
6. UNIQUE, NOT NULL and CHECK Constraints 170
7. String Functions 174
8. Math Functions 178
9. Date and Time Functions 181
10. The Inner Join 185
11. The Outer Joins, and What MySQL Does About FULL OUTER 190
12. Subqueries with IN and EXISTS 195
13. Nested Subqueries, and What a Subquery May Not Do 199
14. ANY and ALL 203
15. Correlated Subqueries 207
16. Functional Dependencies 211
17. First Normal Form 216
18. Second Normal Form 220
19. Third Normal Form 224
20. Boyce Codd Normal Form 228
21. Lossless Join Decomposition 233
22. Security Issues and Threats to Databases 238
23. Security Mechanisms 242
24. The Role of the Database Administrator 246
25. Discretionary Access Control 250
26. Backing Up and Restoring a Database 254
27. Views: Creating, Selecting and Dropping 258
28. Altering, Renaming and Updating Through a View 262
29. Creating and Dropping Users 267
30. Granting and Revoking Privileges 271
31. Transactions: COMMIT and ROLLBACK 275
32. Indexes, and the Part They Play in a Fast Search 279

Module P Computer Science Practical 1: the ten practicals set on this subject

1. Practical: Setting Up MySQL and Keeping the Journal 284
2. Practical 1: Conceptual Design With an ER Diagram 288
3. Practical 2: Database Management Operations and CRUD 292
4. Practical 3: Table Management, Backup and Restore 295
5. Practical 4: Simple Queries and Aggregate Functions 298
6. Practical 5: Date, String and Math Functions 300
7. Practical 6: Inner and Outer Join Queries 303
8. Practical 7: Subqueries With IN and EXISTS 306
9. Practical 8: ER Model to Relational Model, and Normalization 309
10. Practical 9: Views With and Without the Check Option 313
11. Practical 10: DCL Statements, COMMIT and ROLLBACK 316