| Database Systems (CC-215) | ||
| Carlos Coronel, Steven Morris, Database Systems: Design, Implementation & Management, 13th Edition | ||
| WEEK 01 | Lecture 01 | 1-1 Why Databases? 3 |
| 1-2 Data versus Information 4 | ||
| 1-3 Introducing the Database 6 | ||
| 1-3a Role and Advantages of the DBMS 6 | ||
| 1-3b Types of Databases 8 | ||
| 1-4 Why Database Design is Important 11 | ||
| 1-5 Evolution of File System Data Processing 14 | ||
| 1-5a Manual File Systems 14 | ||
| Lecture 02 | 1-5b Computerized File Systems 15 | |
| 1-5c File System Redux: Modern End-User Productivity Tools 17 | ||
| 1-6 Problems with File System Data Processing 18 | ||
| 1-6a Structural and Data Dependence 19 | ||
| 1-6b Data Redundancy 20 | ||
| 1-6c Data Anomalies 21 | ||
| 1-7 Database Systems 21 | ||
| 1-7a The Database System Environment 22 | ||
| Lecture 03 | 1-7b DBMS Functions 24 | |
| 1-7c Managing the Database System: A Shift in Focus 28 | ||
| 1-8 Preparing for Your Database Professional Career | ||
| TEST 01 | ||
| 2-1 Data Modeling and Data Models 36 | ||
| 2-2 The Importance of Data Models 37 | ||
| 2-3 Data Model Basic Building Blocks 37 | ||
| 2-4 Business Rules 39 | ||
| WEEK 02 | Lecture 04 | 2-4a Discovering Business Rules 39 |
| 2-4b Translating Business Rules into Data Model Components 40 | ||
| 2-4c Naming Conventions 41 | ||
| 2-5 The Evolution of Data Models 41 | ||
| 2-5a Hierarchical and Network Models 41 | ||
| 2-5b The Relational Model 43 | ||
| 2-5c The Entity Relationship Model 45 | ||
| 2-5d The Object-Oriented (OO) Model 48 | ||
| Lecture 05 | 2-5e Object/Relational and XML 49 | |
| 2-5f Emerging Data Models: Big Data and NoSQL 50 | ||
| 2-5g Data Models: A Summary 56 | ||
| 2-6 Degrees of Data Abstraction 57 | ||
| 2-6a The External Model 60 | ||
| 2-6b The Conceptual Model 61 | ||
| 2-6c The Internal Model 62 | ||
| 2-6d The Physical Model 63 | ||
| Lecture 06 | TEST 02 | |
| 3-1 A Logical View of Data 73 | ||
| 3-1a Tables and Their Characteristics 73 | ||
| 3-2 Keys 76 | ||
| 3-2a Dependencies 76 | ||
| 3-2b Types of Keys 77 | ||
| 3-3 Integrity Rules 80 | ||
| 3-4 Relational Algebra 82 | ||
| WEEK 03 | Lecture 07 | 3-4a Formal Definitions and Terminology 82 |
| 3-4b Relational Set Operators 83 | ||
| 3-5 The Data Dictionary and the System Catalog 91 | ||
| 3-6 Relationships within the Relational Database 93 | ||
| 3-6a The 1:M Relationship 93 | ||
| 3-6b The 1:1 Relationship 95 | ||
| 3-6c The M:N Relationship 97 | ||
| 3-7 Data Redundancy Revisited 101 | ||
| Lecture 08 | 3-8 Indexes 103 | |
| 3-9 Codd’s Relational Database Rules 104 | ||
| TEST 03 | ||
| 4-1 The Entity Relationship Model (ERM) 118 | ||
| 4-1a Entities 118 | ||
| 4-1b Attributes 118 | ||
| 4-1c Relationships 124 | ||
| 4-1d Connectivity and Cardinality 125 | ||
| Lecture 09 | 4-1e Existence Dependence 126 | |
| 4-1f Relationship Strength 126 | ||
| 4-1g Weak Entities 129 | ||
| 4-1h Relationship Participation 131 | ||
| 4-1i Relationship Degree 134 | ||
| 4-1j Recursive Relationships 136 | ||
| 4-1k Associative (Composite) Entities 138 | ||
| 4-2 Developing an ER Diagram 140 | ||
| WEEK 04 | Lecture 10 | 4-3 Database Design Challenges: Conflicting Goals 147 |
| TEST 04 | ||
| 5-1 The Extended Entity Relationship Model 170 | ||
| 5-1a Entity Supertypes and Subtypes 170 | ||
| 5-1b Specialization Hierarchy 171 | ||
| 5-1c Inheritance 172 | ||
| 5-1d Subtype Discriminator 174 | ||
| 5-1e Disjoint and Overlapping Constraints 174 | ||
| Lecture 11 | 5-1f Completeness Constraint 175 | |
| 5-1g Specialization and Generalization 176 | ||
| 5-2 Entity Clustering 176 | ||
| 5-3 Entity Integrity: Selecting Primary Keys 177 | ||
| 5-3a Natural Keys and Primary Keys 178 | ||
| 5-3b Primary Key Guidelines 178 | ||
| 5-3c When To Use Composite Primary Keys 178 | ||
| 5-3d When To Use Surrogate Primary Keys 180 | ||
| Lecture 12 | 5-4 Design Cases: Learning Flexible Database Design 182 | |
| 5-4a Design Case 1: Implementing 1:1 Relationships 182 | ||
| 5-4b Design Case 2: Maintaining History of Time-Variant Data 183 | ||
| 5-4c Design Case 3: Fan Traps 186 | ||
| 5-4d Design Case 4: Redundant Relationships 187 | ||
| TEST 05 | ||
| 6-1 Database Tables and Normalization 202 | ||
| 6-2 The Need For Normalization 202 | ||
| WEEK 05 | Lecture 13 | 6-3 The Normalization Process 206 |
| 6-3a Conversion To First Normal Form 208 | ||
| 6-3b Conversion To Second Normal Form 211 | ||
| 6-3c Conversion To Third Normal Form 213 | ||
| 6-4 Improving the Design 215 | ||
| 6-5 Surrogate Key Considerations 219 | ||
| 6-6 Higher-Level Normal Forms 220 | ||
| 6-6a The Boyce-Codd Normal Form 221 | ||
| Lecture 14 | 6-6b Fourth Normal Form (4NF) 224 | |
| 6-7 Normalization and Database Design 226 | ||
| 6-8 Denormalization 229 | ||
| 6-9 Data-Modeling Checklist 232 | ||
| TEST 06 | ||
| 7-1 Introduction to SQL 247 | ||
| 7-2 Data Definition Commands 249 | ||
| 7-2a The Database Model 249 | ||
| Lecture 15 | 7-2b Creating The Database 251 | |
| 7-2c The Database Schema 251 | ||
| 7-2d Data Types 252 | ||
| 7-2e Creating Table Structures 255 | ||
| 7-2f SQL Constraints 259 | ||
| 7-2g SQL Indexes 263 | ||
| 7-3 Data Manipulation Commands 264 | ||
| 7-3a Adding Table Rows 264 | ||
| WEEK 06 | Lecture 16 | 7-3b Saving Table Changes 266 |
| 7-3c Listing Table Rows 266 | ||
| 7-3d Updating Table Rows 268 | ||
| 7-3e Restoring Table Contents 269 | ||
| 7-3f Deleting Table Rows 269 | ||
| 7-3g Inserting Table Rows with a Select Subquery 270 | ||
| 7.4 SELECT Queries 271 | ||
| 7-4a Selecting Rows with Conditional Restrictions 271 | ||
| Lecture 17 | 7-4b Arithmetic Operators: The Rule of Precedence 276 | |
| 7-4c Logical Operators: AND, OR, and NOT 277 | ||
| 7-4d Special Operators 279 | ||
| 7-5 Additional Data Definition Commands 283 | ||
| 7-5a Changing a Column’s Data Type 284 | ||
| 7-5b Changing a Column’s Data Characteristics 284 | ||
| 7-5c Adding a Column 284 | ||
| 7-5d Dropping a Column 285 | ||
| Lecture 18 | 7-5e Advanced Data Updates 285 | |
| 7-5f Copying Parts of Tables 287 | ||
| 7-5g Adding Primary and Foreign Key Designations 289 | ||
| 7-5h Deleting a Table from the Database 290 | ||
| 7-6 Additional SELECT Query Keywords 290 | ||
| 7-6a Ordering a Listing 290 | ||
| 7-6b Listing Unique Values 292 | ||
| 7-6c Aggregate Functions 292 | ||
| WEEK 07 | Lecture 19 | 7-6d Grouping Data 297 |
| 7-7 Joining Database Tables 300 | ||
| 7-7a Joining Tables with an Alias 303 | ||
| 7-7b Recursive Joins 303 | ||
| TEST 07 | ||
| 8-1 SQL Join Operators 341 | ||
| 8-1a Cross Join 342 | ||
| 8-1b Natural Join 343 | ||
| Lecture 20 | 8-1c JOIN USING Clause 344 | |
| 8-1d JOIN ON Clause 345 | ||
| 8-1e Outer Joins 347 | ||
| 8-2 Subqueries and Correlated Queries 349 | ||
| 8-2a WHERE Subqueries 351 | ||
| 8-2b IN Subqueries 352 | ||
| 8-2c HAVING Subqueries 353 | ||
| 8-2d Multirow Subquery Operators: ANY and ALL 353 | ||
| Lecture 21 | 8-2e FROM Subqueries 355 | |
| 8-2f Attribute List Subqueries 356 | ||
| 8-2g Correlated Subqueries 358 | ||
| 8-3 SQL Functions 361 | ||
| 8-3a Date and Time Functions 361 | ||
| 8-3b Numeric Functions 366 | ||
| 8-3c String Functions 366 | ||
| 8-3d Conversion Functions 368 | ||
| WEEK 08 | Lecture 22 | 8-4 Relational Set Operators 371 |
| 8-4a UNION 371 | ||
| 8-4b UNION ALL 373 | ||
| 8-4c INTERSECT 373 | ||
| 8-4d EXCEPT (MINUS) 375 | ||
| 8-4e Syntax Alternatives 377 | ||
| 8-5 Virtual Tables: Creating a View 377 | ||
| 8-5a Updatable Views 379 | ||
| Lecture 23 | 8-6 Sequences 382 | |
| 8-7 Procedural SQL 387 | ||
| 8-7a Triggers 392 | ||
| 8-7b Stored Procedures 401 | ||
| 8-7c PL/SQL Processing with Cursors 407 | ||
| 8-7d PL/SQL Stored Functions 409 | ||
| 8-8 Embedded SQL 410 | ||
| TEST 08 | ||
| Lecture 24 | 9-1 The Information System 440 | |
| 9-2 The Systems Development Life Cycle 442 | ||
| 9-2a Planning 442 | ||
| 9-2b Analysis 443 | ||
| 9-2c Detailed Systems Design 444 | ||
| 9-2d Implementation 444 | ||
| 9-2e Maintenance 445 | ||
| 9-3 The Database Life Cycle 445 | ||
| WEEK 09 | Lecture 25 | 9-3a The Database Initial Study 445 |
| 9-3b Database Design 450 | ||
| 9-3c Implementation and Loading 451 | ||
| 9-3d Testing and Evaluation 454 | ||
| 9-3e Operation 456 | ||
| 9-3f Maintenance and Evolution 457 | ||
| 9-4 Conceptual Design 457 | ||
| 9-4a Data Analysis and Requirements 459 | ||
| Lecture 26 | 9-4b Entity Relationship Modeling and Normalization 461 | |
| 9-4c Data Model Verification 464 | ||
| 9-4d Distributed Database Design 467 | ||
| 9-5 DBMS Software Selection 467 | ||
| 9-6 Logical Design 468 | ||
| 9-6a Map the Conceptual Model to the Logical Model 468 | ||
| 9-6b Validate the Logical Model Using Normalization 470 | ||
| 9-6c Validate Logical Model Integrity Constraints 470 | ||
| Lecture 27 | 9-6d Validate the Logical Model Against User Requirements 471 | |
| 9-7 Physical Design 471 | ||
| 9-7a Define Data Storage Organization 472 | ||
| 9-7b Define Integrity and Security Measures 472 | ||
| 9-7c Determine Performance Measures 473 | ||
| 9-8 Database Design Strategies 473 | ||
| 9-9 Centralized Versus Decentralized Design 474 | ||
| TEST 09 | ||
| WEEK 10 | Lecture 28 | 10-1 What Is a Transaction? 483 |
| 10-1a Evaluating Transaction Results 484 | ||
| 10-1b Transaction Properties 487 | ||
| 10-1c Transaction Management with SQL 488 | ||
| 10-1d The Transaction Log 489 | ||
| 10-2 Concurrency Control 490 | ||
| 10-2a Lost Updates 490 | ||
| 10-2b Uncommitted Data 491 | ||
| Lecture 29 | 10-2c Inconsistent Retrievals 492 | |
| 10-2d The Scheduler 493 | ||
| 10-3 Concurrency Control with Locking Methods 495 | ||
| 10-3a Lock Granularity 496 | ||
| 10-3b Lock Types 498 | ||
| 10-3c Two-Phase Locking to Ensure Serializability 500 | ||
| 10-3d Deadlocks 500 | ||
| 10-4 Concurrency Control with Time Stamping Methods 502 | ||
| Lecture 30 | 10-4a Wait/Die and Wound/Wait Schemes 502 | |
| 10-5 Concurrency Control with Optimistic Methods 503 | ||
| 10-6 ANSI Levels of Transaction Isolation 504 | ||
| 10-7 Database Recovery Management 506 | ||
| 10-7a Transaction Recovery 506 | ||
| TEST 10 | ||
| 11-1 Database Performance-Tuning Concepts 516 | ||
| 11-1a Performance Tuning: Client and Server 517 | ||
| WEEK 11 | Lecture 31 | 11-1b DBMS Architecture 518 |
| 11-1c Database Query Optimization Modes 520 | ||
| 11-1d Database Statistics 521 | ||
| 11-2 Query Processing 522 | ||
| 11-2a SQL Parsing Phase 523 | ||
| 11-2b SQL Execution Phase 524 | ||
| 11-2c SQL Fetching Phase 525 | ||
| 11-2d Query Processing Bottlenecks 525 | ||
| Lecture 32 | 11-3 Indexes and Query Optimization 526 | |
| 11-4 Optimizer Choices 528 | ||
| 11-4a Using Hints to Affect Optimizer Choices 530 | ||
| 11-5 SQL Performance Tuning 531 | ||
| 11-5a Index Selectivity 531 | ||
| 11-5b Conditional Expressions 533 | ||
| 11-6 Query Formulation 534 | ||
| 11-7 DBMS Performance Tuning 536 | ||
| Lecture 33 | 11-8 Query Optimization Example 538 | |
| TEST 11 | ||
| 12-1 The Evolution of Distributed Database Management Systems 554 | ||
| 12-2 DDBMS Advantages and Disadvantages 556 | ||
| 12-3 Distributed Processing and Distributed Databases 556 | ||
| 12-4 Characteristics of Distributed Database Management Systems 559 | ||
| 12-5 DDBMS Components 560 | ||
| 12-6 Levels of Data and Process Distribution 561 | ||
| WEEK 12 | Lecture 34 | 12-6a Single-Site Processing, Single-Site Data 561 |
| 12-6b Multiple-Site Processing, Single-Site Data 562 | ||
| 12-6c Multiple-Site Processing, Multiple-Site Data 563 | ||
| 12-7 Distributed Database Transparency Features 564 | ||
| 12-8 Distribution Transparency 565 | ||
| 12-9 Transaction Transparency 568 | ||
| 12-9a Distributed Requests and Distributed Transactions 568 | ||
| 12-9b Distributed Concurrency Control 571 | ||
| Lecture 35 | 12-9c Two-Phase Commit Protocol 571 | |
| 12-10 Performance and Failure Transparency 573 | ||
| 12-11 Distributed Database Design 575 | ||
| 12-11a Data Fragmentation 575 | ||
| 12-11b Data Replication 578 | ||
| 12-11c Data Allocation 580 | ||
| 12-12 The CAP Theorem 581 | ||
| 12-13 C. J. Date’s 12 Commandments for Distributed Databases 583 | ||
| Lecture 36 | TEST 12 | |
| 13-1 The Need for Data Analysis 590 | ||
| 13-2 Business Intelligence 590 | ||
| 13-2a Business Intelligence Architecture 592 | ||
| 13-2b Business Intelligence Benefits 598 | ||
| 13-2c Business Intelligence Evolution 598 | ||
| 13-2d Business Intelligence Technology Trends 601 | ||
| 13-3 Decision Support Data 602 | ||
| WEEK 13 | Lecture 37 | 13-3a Operational Data Versus Decision Support Data 602 |
| 13-3b Decision Support Database Requirements 605 | ||
| 13-4 The Data Warehouse 607 | ||
| 13-4a Data Marts 610 | ||
| 13-4b Twelve Rules That Define a Data Warehouse 610 | ||
| 13-5 Star Schemas 610 | ||
| 13-5a Facts 611 | ||
| 13-5b Dimensions 611 | ||
| Lecture 38 | 13-5c Attributes 612 | |
| 13-5d Attribute Hierarchies 614 | ||
| 13-5e Star Schema Representation 616 | ||
| 13-5f Performance-Improving Techniques for the Star Schema 617 | ||
| 13-6 Online Analytical Processing 621 | ||
| 13-6a Multidimensional Data Analysis Techniques 621 | ||
| 13-6b Advanced Database Support 623 | ||
| 13-6c Easy-to-Use End-User Interfaces 623 | ||
| Lecture 39 | 13-6d OLAP Architecture 623 | |
| 13-6e Relational OLAP 626 | ||
| 13-6f Multidimensional OLAP 628 | ||
| 13-6g Relational versus Multidimensional OLAP 628 | ||
| 13-7 SQL Extensions for OLAP 629 | ||
| 13-7a The ROLLUP Extension 630 | ||
| 13-7b The CUBE Extension 631 | ||
| 13-7c Materialized Views 633 | ||
| WEEK 14 | Lecture 40 | TEST 13 |
| 14-1 Big Data 649 | ||
| 14-1a Volume 651 | ||
| 14-1b Velocity 652 | ||
| 14-1c Variety 653 | ||
| 14-1d Other Characteristics 654 | ||
| 14-2 Hadoop 655 | ||
| 14-2a HDFS 655 | ||
| Lecture 41 | 14-2b MapReduce 658 | |
| 14-2c Hadoop Ecosystem 660 | ||
| 14-3 NoSQL 662 | ||
| 14-3a Key-Value Databases 663 | ||
| 14-3b Document Databases 664 | ||
| 14-3c Column-Oriented Databases 665 | ||
| 14-3d Graph Databases 668 | ||
| 14-3e NewSQL Databases 669 | ||
| Lecture 42 | 14-4 Data Analytics 670 | |
| 14-4a Data Mining 671 | ||
| 14-4b Predictive Analytics 673 | ||
| TEST 14 | ||
| 15-1 Database Connectivity 681 | ||
| 15-1a Native SQL Connectivity 682 | ||
| 15-1b ODBC, DAO, and RDO 683 | ||
| 15-1c OLE-DB 685 | ||
| WEEK 15 | Lecture 43 | 15-1d ADO.NET 687 |
| 15-1e Java Database Connectivity (JDBC) 691 | ||
| 15-2 Database Internet Connectivity 692 | ||
| 15-2a Web-to-Database Middleware: Server-Side Extensions 693 | ||
| 15-2b Web Server Interfaces 695 | ||
| 15-2c The Web Browser 696 | ||
| 15-2d Client-Side Extensions 697 | ||
| 15-2e Web Application Servers 698 | ||
| Lecture 44 | 15-2f Web Database Development 699 | |
| 15-3 Extensible Markup Language (XML) 702 | ||
| 15-3a Document Type Definitions (DTD) and XML Schemas 704 | ||
| 15-3b XML Presentation 706 | ||
| 15-3c XML Applications 708 | ||
| 15-4 Cloud Computing Services 709 | ||
| 15-4a Cloud Implementation Types 712 | ||
| 15-4b Characteristics of Cloud Services 712 | ||
| Lecture 45 | 15-4c Types of Cloud Services 713 | |
| 15-4d Cloud Services: Advantages and Disadvantages 714 | ||
| 15-4e SQL Data Services 716 | ||
| TEST 15 | ||
| 16-1 Data as a Corporate Asset 723 | ||
| 16-2 The Need for a Database and its Role in an Organization 724 | ||
| 16-3 Introduction of a Database: Special Considerations 726 | ||
| 16-4 The Evolution of Database Administration 727 | ||
| WEEK 16 | Lecture 46 | 16-5 The Database Environment’s Human Component 731 |
| 16-5a The DBA’s Managerial Role 733 | ||
| 16-5b The DBA’s Technical Role 738 | ||
| 16-6 Security 745 | ||
| 16-6a Security Policies 746 | ||
| 16-6b Security Vulnerabilities 746 | ||
| 16-6c Database Security 748 | ||
| 16-7 Database Administration Tools 749 | ||
| Lecture 47 | 16-7a The Data Dictionary 750 | |
| 16-7b Case Tools 752 | ||
| 16-8 Developing a Data Administration Strategy 755 | ||
| 16-9 The DBA’s Role in the Cloud 756 | ||
| 16-10 The DBA at Work: Using Oracle for Database Administration 757 | ||
| 16-10a Oracle Database Administration Tools 758 | ||
| 16-10b Ensuring that the RDBMS Starts Automatically 758 | ||
| 16-10c Creating Tablespaces and Datafiles 760 | ||
| Lecture 48 | 16-10d Managing Users and Establishing Security 762 | |
| 16-10e Customizing the Database Initialization Parameters 763 | ||
| TEST 16 | ||
Database Systems (CC-215) Tutorials for PU BS Students
