Database Systems and Structures

CMPT 354: Database Systems and Structures
Summer 1998

Chapter 1: Introduction	PDF and Slides
Database Management Systems Purpose of Database Systems Data Abstraction Data Models Object-based Logical Models The E-R Model The Object-Oriented Model Record-based Logical Models The Relational Model The Network Model The Hierarchical Model Physical Data Models Instances and Schemes Data Independence Data Definition Language (DDL) Data Manipulation Language (DML) Database Manager Database Administrator Database Users Overall System Structure
Chapter 2: Entity-Relationship Model	PDF and Slides
Entities and Entity Sets Relationships & Relationship Sets Attributes Mapping Constraints Keys Primary Keys for Relationship Sets The Entity Relationship Diagram Other Styles of E-R Diagram Reducing E-R Diagrams to Tables Representation of Strong Entity Sets Representation of Weak Entity Sets Representation of Relationship Sets Generalization Aggregation Design of an E-R Database Scheme Mapping Cardinalities Use of Entity or Relationship Sets Use of Extended E-R Features
Chapter 3: Relational Model	PDF and Slides
Structure of Relational Database Basic Structure Database Scheme Keys Query Languages The Relational Algebra Fundamental Operations Formal Definition of Relational Algebra Additional Operations The Tuple Relational Calculus Example Queries Formal Definitions Safety of Expressions Expressive Power of Languages The Domain Relational Calculus Formal Definitions Example Queries Safety of Expressions Expressive Power of Languages Modifying the Database Deletion Insertions Updating Views View Definition Updates Through Views and Null Values
Chapter 4: SQL	PDF and Slides
Background Basic Structure The select Clause The where Clause The from Clause The Rename Operation Tuple Variables String Operations Ordering the Display of Tuples Duplicate Tuples Set Operations Aggregate Functions Null Values Nested Subqueries Set Membership Set Comparison Test for Empty Relations Test for the Absence of Duplicate Tuples Derived Relations Views Modification of the Database Deletion Insertion Updates Update of a view Joined Relations Examples Join types and conditions Data-Definition Language Domain Types in SQL Schema definition in SQL Embedded SQL Other SQL Features
Chapter 5: Other Relational Languages	PDF and Slides
Query-by-Example (QBE) Basic Structure Simple Queries Queries on Several Relations The Condition Box The Result Relation Ordering the Display of Tuples Aggregate Operations Modifying the Database Insertion Updates Quel Converting Queries Easily Into Any Language
Chapter 6: Integrity Constraints	PDF and Slides
Domain Constraints Referential Integrity Basic Concepts Referential Integrity in the E-R Model Database Modification Referential Integrity in SQL Assertions Triggers Functional Dependencies Basic Concepts Closure of a Set of Functional Dependencies Closure of Attribute Sets Canonical Cover
Chapter 7: Relational Database Design	PDF and Slides
Pitfalls in Relational DB Design Representation of Information Decomposition Normalization Using Functional Dependencies Desirable Properties of Decomposition Lossless-Join Decomposition Dependency Preservation Repetition of Information Boyce-Codd Normal Form Third Normal Form Comparison of BCNF and 3NF Normalization Using Multivalued Dependencies (not to be covered) Multivalued Dependencies Theory of Multivalued Dependencies Fourth Normal Form (4NF) Normalization Using Join Dependencies (not to be covered) Domain-Key Normal Form (not to be covered) Alternative Approaches to Database Design (not to be covered)
Chapter 8: Object-Oriented Databases	PDF and Slides
New DB Applications The Object-Oriented Data Model Object Structure Object Classes Inheritance Multiple Inheritance Object Identity Object Containment Object-Oriented Languages Persistent Programming Languages Persistence of Objects Object Identity and Pointers Storage and Access of Persistent Objects Persistent C++ Systems The ODMG C++ Object Definition Language The ODMG C++ Object Manipulation Language References
Chapter 9: Object-Relational Databases	PDF and Slides
Nested Relations Complex Types and Object Orientation Structured and collection types Inheritance Reference Types Querying with Complex Types Relation-Valued Attributes Path Expressions Nesting and Unnesting Functions Creation of Complex Values and Objects Comparison of Object-Oriented and Object-Relational Databases References
Chapter 10: Storage and File Structure	PDF and Slides
Overview of Physical Storage Media Magnetic Disks Physical Characteristics of Disks Performance Measures of Disks Optimization of Disk-Block Access RAID: Redundant Arrays of Inexpensive Disks (Not covered) Tertiary Storage Optical Disks Magnetic Tapes Storage Access Buffer manager Buffer replacement policies File Organization Fixed-Length Records Variable-Length Records Byte string representation Fixed-length representation Organization of Records in Files Sequential File Organization Clustering File Organization Data Dictionary Storage Storage Structures for Object-Oriented Databases (Omitted)
Chapter 11: Indexing and Hashing	PDF and Slides
Basic Concepts Ordered Indices Primary Index Dense and Sparse Indices Multi-Level Indices Index Update Secondary Indices B⁺-Tree Index Files Structure of a B⁺-Tree Queries on B⁺-Trees Updates on B⁺-Trees B⁺-Tree File Organization B-Tree Index Files Static Hashing Hash File Organization Hash Functions Handling of bucket overflows Hash Indices Dynamic Hashing Comparison of Indexing and Hashing Index Definition in SQL Multiple-Key Access Grid File Partitioned Hashing
Chapter 12: Query Processing	PDF and Slides
Query Interpretation Equivalence of Expressions Selection Operation Projection Operation Natural Join Operation Other Operations Estimation of Query-Processing Cost Estimation of Access Costs Using Indices Join Strategies Simple Iteration Block-Oriented Iteration Merge-Join Use of an Index Hash Join Three-Way Join Join Strategies for Parallel Processors Parallel-Join Pipelined Multiway Join Physical Organization Structure of the Query Optimizer

Thank you for Dr. Jiawei Han, Dr. Veronica Dahl, and Dr. Li-Yan Yuan for their notes.

CMPT 354: Database Systems and StructuresSummer 1998

Table of Contents

CMPT 354: Database Systems and Structures
Summer 1998