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 113
30. UPDATE and DELETE: Changing and Removing Rows 117
31. SELECT: All Columns and Named Columns 121
32. DISTINCT: Unique Records 125
33. The WHERE Clause 128
34. IN and BETWEEN 132
35. ORDER BY and LIMIT 136
36. Aggregate Functions: COUNT, MIN, MAX, AVG and SUM 140
37. GROUP BY 143
38. HAVING 147

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 151
2. Relational Model Notation 155
3. Characteristics of Relations 158
4. Relational Constraints: the Primary Key 162
5. Referential Integrity and the Foreign Key 166
6. UNIQUE, NOT NULL and CHECK Constraints 171
7. String Functions 176
8. Math Functions 180
9. Date and Time Functions 183
10. The Inner Join 187
11. The Outer Joins, and What MySQL Does About FULL OUTER 192
12. Subqueries with IN and EXISTS 197
13. Nested Subqueries, and What a Subquery May Not Do 201
14. ANY and ALL 205
15. Correlated Subqueries 209
16. Functional Dependencies 213
17. First Normal Form 218
18. Second Normal Form 222
19. Third Normal Form 226
20. Boyce Codd Normal Form 230
21. Lossless Join Decomposition 235
22. Security Issues and Threats to Databases 240
23. Security Mechanisms 244
24. The Role of the Database Administrator 248
25. Discretionary Access Control 252
26. Backing Up and Restoring a Database 256
27. Views: Creating, Selecting and Dropping 260
28. Altering, Renaming and Updating Through a View 264
29. Creating and Dropping Users 269
30. Granting and Revoking Privileges 273
31. Transactions: COMMIT and ROLLBACK 277
32. Indexes, and the Part They Play in a Fast Search 281

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

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