C192 (Data Management for Programmers) This study guide provides a listing of the Course Objectives and links them to the Lessons. Data Storage Lesson 5: Section 5.1 - The Relational Algebra Lesson 12: Entity-Relationship Modeling Lesson 13: Enhanced Entity-Relationship Modeling Lesson 16: Methodology-Conceptual Database Design Lesson 17: Methodology-Logical Database Design for the Relational Model Lesson 18: Methodology-Physical Database Design for Relational Databases Objectives: Describe data models and conceptual modeling. Identify key relational model concepts that can be performed for a complex database. Describe an enhanced entity-relationship (EER) model that represents how data will be stored as part of the database application model. Identify the differences between entity-relationship (ER) and enhanced entity-relationship (EER) model. Describe a conceptual data model. Describe features of logical database design created from a conceptual data model. Explain storage requirements for a physical database design. Data Definition Lesson 1: Introduction to Databases Lesson 2: Database Environment Objectives: Describe the functions that a database management system (DBMS) supports. Compare database management systems. Identify data types that are used within a DBMS environment. Data Definition Language Lesson 7: Data Definition Objectives: Describe the benefits of using Structured Query Language (SQL). Identify DDL statements to create, drop, and alter tables. Identify DDL statements to create, drop, and alter views. Identify DDL statements to create, drop, and alter indexes. Identify DDL statements to create, drop, and alter constraints, including primary and foreign keys. Data Manipulation Language Lesson 6: Data Definition Objectives: Insert data in tables. Update data in tables. Delete data in tables. Write a select statement to extract data from tables. Aggregate data in tables. Sort and group data in tables. Perform multi-table queries. Procedural Language/ Strutured Query Language (PL/SQL) Lesson 8: Data Definition Objectives: Describe a simple stored procedure. Describe a package consisting of procedures and functions. Describe a trigger to help enforce referential integrity constraints. Data Architectures Lesson 24: Distributed DBMSs-Concepts and Design Lesson 32: Data Warehousing Design Lesson 33: OLAP Objectives: Identify main concepts associated with data warehousing. Identify main concepts associated with data marts. Identify main concepts and distribution requirements associated with distributed databases. Identify main concepts associated with online analytical processing (OLAP) using business intelligence technologies. Advanced Topics in Distributed Data Management Lesson 25: Distributed DBMSs-Advanced Concepts Objectives: Describe appropriate formats for representing multidimensional data. Determine when dimensionality modeling is appropriate for supporting high-performance access. Produce a star schema. Produce a snowflake schema. Produce a starflake schema. Data Presentation Lesson 29: Web Technology and DBMSs Lesson 30: Semi - structured Data and XML Lesson 34: Data Mining Objectives: Identify different web interfaces involved in presenting data to the DBMS using various programming languages. Describe how to use XML to present data stored in a DBMS or semi-structured data. Identify situations in which data mining is used. Data Security Implementation Lesson 4 – Section 4.4 Views Lesson 7 – Section 7.4 Views and Section 7.6 Discretionary Access Control Lesson 20 – From beginning through Section 20.2.6 Encryption Objectives: Develop access strategies to limit data access. Identify users and roles to limit data access using DCL. Describe how to create views to limit data access. Describe how keys and algorithms are used to create encryption. Transaction Management Lesson 22: Transaction Management Objectives: Describe the basic properties of a transaction. Identify which concurrency techniques will be used to support redundant systems. Describe the concept of serializability. Describe different locking methods. Describe how to prevent deadlocks. Describe how timestamping can be used to support concurrency control. Describe how optimistic techniques can be used to support concurrency control when conflict is rare. Administer Data Lesson 7: SQL-Data Definition Lesson 23: Query Processing Objectives: Identify database installation and administration considerations. Describe how to create, drop, and alter databases. Identify backup requirements for DBMS environment. Describe how to create a database disaster recovery plan that addresses system crashes, media failure, application errors, natural disasters, human errors, and sabotage. Identify data replication requirements including to mobile environments.