2.4. Data Management (DM)

2.4. Data Management (DM)

Since the mid-1970s, the study of Data Management (DM) has meant an almost exclusive study of relational database systems. Depending on institutional context, students have studied, in varying proportions, the following.

  • Data modeling and database design: for example, E-R Data model, relational model, normalization theory
  • Query construction: e.g., relational algebra, SQL
  • Query processing: e.g., indices (B+tree, hash), algorithms (e.g., external sorting, select, project, join), query optimization (transformations, index selection)
  • DBMS internals: e.g., concurrency/locking, transaction management, buffer management

Today's graduates are expected to possess DBMS user (rather than implementor) skills. These primarily include data modeling and query construction; ability to take an unorganized collection of data, organize it using a DBMS, and access/update the collection via queries.

Additionally, students need to study the following.

  • The role data plays in an organization. This includes the Data Life Cycle: Creation-Processing-Review/Reporting-Retention/Retrieval-Destruction.
  • The social/legal aspects of data collection: e.g., scale, data privacy, database privacy (compliance) by design, de-identification, ownership, reliability, database security, and intended and unintended applications.
  • Emerging and advanced technologies that are augmenting/replacing traditional relational systems, particularly those used to support (big) data analytics, including NoSQL (e.g., JSON, XML, key-value store databases), cloud databases, MapReduce, and dataframes.
  • The existing and emerging roles for those involved with data management, which include the following:
    • Product feature engineers: those who use both SQL and NoSQL operational databases.
    • Analytical engineers/data engineers: those who write analytical SQL, Python, and Scala code to build data assets for business groups.
    • Business analysts: those who build/manage data most frequently with Excel spreadsheets.
    • Data infrastructure engineers: those who implement a data management system in a variety of data applications (e.g., OLTP).
    • "Everyone" who produces or consumes data must understand the associated social, ethical, and professional issues. itemize

    One role that transcends all the above categories is that of data custodian. Previously, data were seen as a resource to be managed (Information Systems Management) just like other enterprise resources. Today, data are seen in a larger context. Data about customers can now be seen as belonging to (or in some national contexts, as owned by) those customers. There is now an accepted understanding that the safe and ethical storage, and use, of institutional data is part of being a responsible data custodian.

    Furthermore, we acknowledge the tension between a curricular focus on professional preparation versus the study of a knowledge area as a scientific endeavor. This is particularly true with Data Management. For example, proving (or at least knowing) the completeness of Armstrong's Axioms is fundamental in functional dependency theory. However, most computer science graduates will never utilize this concept during their professional careers. The same can be said for many other topics in the Data Management canon. Conversely, if our graduates can only normalize data into Boyce-Codd normal form (using an automated tool) and write SQL queries, without understanding the role that indices play in efficient query execution, we have done them and society a disservice.

    To this end, the number of CS Core hours is relatively small relative to the KA Core hours. This approach is designed to allow institutions with differing contexts to customize their curricula appropriately. An institution that focuses on OLTP implementation, for example, would prioritize efficient storage and data access, while an institution that focuses on product features would prioritize programmatic access to extant databases.

    However, an institution manages this tension, we wish to give voice to one of the ironies of computer science curricula. Students typically spend much of their educational life reading (and writing) data from a file or interactively, while outside of the academy the predominant data comes from databases accessed programmatically. Perhaps in the not-too-distant future students will learn programmatic database access early on and then continue this practice as they progress through their curriculum.

    Finally, we understand that while the Data Management KA may be orthogonal to the SEC (Security) and SEP (Society, Ethics, and the Profession) KAs, it is also ground zero for these (and other) knowledge areas. When designing persistent data stores, the question of what should be stored must be examined from both legal and ethical perspectives. Are there privacy concerns? And just as importantly, how well protected is the data?

    Knowledge Area (KA) CS
    Core    		KA
    Core
     2.4.1 The Role of Data and the Data Life Cycle 2 
     2.4.2 Core Database System Concepts 21
     2.4.3 Data Modeling 23
     2.4.4 Relational Databases 13
     2.4.5 Query Construction 24
     2.4.6 Query Processing  4
     2.4.7 DBMS Internals  4
     2.4.8 NoSQL Systems  2
     2.4.9 Data Security and Privacy 12
     2.4.10 Data Analytics  3
     2.4.11 Distributed Databases/Cloud Computing Elective
     2.4.12 Semi-structured and Unstructured Databases Elective
     2.4.13 Society, Ethics, and the Profession Elective
    Table 2.4: List of KUs in the Data Management area.

    2.4.1. DM/The Role of Data and the Data Life Cycle  (CS Core: 2 hrs) ↑ Back to top

    Topics:
    Core

    • The Data Life Cycle: Creation-Processing-Review/Reporting-Retention/Retrieval-Destruction

    Learning Outcomes:
    Core:

    1. Identify the five stages of the Data Life Cycle [Analyze]

    2.4.2. DM/Core Database System Concepts  (CS Core: 2 hrs, KA Core: 1 hr) ↑ Back to top

    Topics:
    Core

    • Purpose and advantages of database systems
    • Components of database systems
    • Design of core DBMS functions (e.g., query mechanisms, transaction management, buffer management, access methods)
    • Database architecture, data independence, and data abstraction
    • Transaction management
    • Normalization
    • Approaches for managing large volumes of data (e.g., NoSQL database systems, use of MapReduce)
    • How to support CRUD-only applications
    • Distributed databases/cloud-based systems
    • Structured, semi-structured, and unstructured data
    • Use of a declarative query language
    • Systems supporting structured and/or stream content

    Learning Outcomes:
    Core:

    1. Identify at least four advantages that using a database system provides [Analyze]
    2. Enumerate the components of a (relational) database system [Enumerate]
    3. Follow a query as it is processed by the components of a (relational) database system [Analyze]
    4. Defend the value of data independence [Defend]
    5. Compose a simple select-project-join query in SQL [Compose]
    6. Enumerate the four properties of a correct transaction manager [Enumerate]
    7. Describe the advantages for eliminating duplicate repeated data [Describe]
    8. Outline how MapReduce uses parallelism to process data efficiently [Outline]
    9. Evaluate the differences between structured and semi/unstructured databases [Evaluate]

    2.4.3. DM/Data Modeling  (CS Core: 2 hrs, KA Core: 3 hrs) ↑ Back to top

    Topics:
    Core

    • Data modeling
    • Relational data model
    • Conceptual models (e.g., entity-relationship, UML diagrams)
    • Semi-structured data models (expressed using DTD, XML, or JSON Schema, for example)

    Non Core

    • Spreadsheet models
    • Object-oriented models:
      1. GraphQL enumerate
      2. New features in SQL
      3. Specialized Data Modeling topics:
        1. Time series data (aggregation, join)
        2. Graph data (link traversal)
        3. Techniques for avoiding inefficient raw data access (e.g., "avg daily price"): materialized views and special data structures (e.g., Hyperloglog, bitmap)
        4. Geo-Spatial data (e.g., GIS databases) enumerate

        Learning Outcomes:
        Core:

        1. Describe the components of the relational data model [Describe]
        2. Model 1:1, 1:n, and n:m relationships using the relational data model [Use]
        3. Describe the components of the E-R (or some other non-relational) data model [Describe]
        4. Model a given environment using a conceptual data model [Analyze]
        5. Model a given environment using the document-based or key-value store-based data model [Apply]

        2.4.4. DM/Relational Databases  (CS Core: 1 hr, KA Core: 3 hrs) ↑ Back to top

        Topics:
        Core

        • Entity and referential integrity: Candidate key, superkeys
        • Relational database design
        • Mapping conceptual schema to a relational schema
        • Physical database design: file and storage structures
        • Introduction to Functional dependency theory
        • Normalization Theory:
          1. Decomposition of a schema; lossless-join, and dependency-preservation properties of a decomposition
          2. Normal forms (BCNF)
          3. Denormalization (for efficiency) enumerate

          Non Core

          • Functional dependency theory:
            1. Closure of a set of attributes
            2. Canonical Cover enumerate
            3. Normalization theory:
              1. Multi-valued dependency (4NF)
              2. Join dependency (PJNF, 5NF)
              3. Representation theory enumerate

              Learning Outcomes:
              Core:

              1. Describe the defining characteristics behind the relational data model [Describe]
              2. Comment on the difference between a foreign key and a superkey [Explain]
              3. Enumerate the different types of integrity constraints [Enumerate]
              4. Compose a relational schema from a conceptual schema which contains 1:1, 1:n, and n:m relationships [Compose]
              5. Map appropriate file structure to relations and indices [Map]
              6. Describe how functional dependency theory generalizes the notion of key [Describe]
              7. Defend a given decomposition as lossless and or dependency preserving [Defend]
              8. Detect which normal form a given decomposition yields [Detect]
              9. Comment on reasons for denormalizing a relation [Interpret]

              2.4.5. DM/Query Construction  (CS Core: 2 hrs, KA Core: 4 hrs) ↑ Back to top

              Topics:
              Core

              • SQL Query Formation:
                1. Interactive SQL execution
                2. Programmatic execution of an SQL query enumerate
                3. Relational Algebra
                4. SQL:
                  1. Data definition including integrity and other constraint specifications
                  2. Update sublanguage enumerate

                  Non Core

                  • Relational Calculus
                  • QBE and 4th-generation environments
                  • Different ways to invoke non-procedural queries in conventional languages
                  • Introduction to other major query languages (e.g., XPATH, SPARQL)
                  • Stored procedures

                  Learning Outcomes:
                  Core:

                  1. Compose SQL queries that incorporate select, project, join, union, intersection, set difference, and set division [Compose]
                  2. Determine when a nested SQL query is correlated or not [Determine]
                  3. Iterate over data retrieved programmatically from a database via an SQL query [Create]
                  4. Define, in SQL, a relation schema, including all integrity constraints and delete/update triggers [Define]
                  5. Compose an SQL query to update a tuple in a relation [Compose]

                  2.4.6. DM/Query Processing  (KA Core: 4 hrs) ↑ Back to top

                  Topics:
                  Core

                  • Page structures
                  • Index structures:
                    1. B+ trees
                    2. Hash indices: static and dynamic
                    3. Index creation in SQL enumerate
                    4. File structures:
                      1. Heap files
                      2. Hash files enumerate
                      3. Algorithms for query operators:
                        1. External Sorting
                        2. Selection
                        3. Projection; with and without duplicate elimination
                        4. Natural Joins: Nested loop, Sort-merge, Hash join
                        5. Analysis of algorithm efficiency enumerate
                        6. Query transformations
                        7. Query optimization:
                          1. Access paths
                          2. Query plan construction
                          3. Selectivity estimation
                          4. Index-only plans enumerate
                          5. Parallel Query Processing (e.g., parallel scan, parallel join, parallel aggregation)
                          6. Database tuning/performance:
                            1. Index selection
                            2. Impact of indices on query performance
                            3. Denormalization enumerate

                            Learning Outcomes:
                            Core:

                            1. Describe the purpose and organization of both B+ tree and hash index structures [Describe]
                            2. Compose an SQL command to create an index (any kind) [Compose]
                            3. Specify the steps for the various query operator algorithms: external sorting, projection with duplicate elimination, sort-merge join, hash-join, block nested-loop join [Analyze]
                            4. Derive the run-time (in I/O requests) for each of the above algorithms [Analyze]
                            5. Transform a query in relational algebra to its equivalent appropriate for a left-deep, pipelined execution [Redesign]
                            6. Compute selectivity estimates for a given selection and/or join operation [Compute]
                            7. Describe how to modify an index structure to facilitate an index-only operation for a given relation [Describe]
                            8. For a given scenario decide on which indices to support for the efficient execution of a set of queries [Assess]
                            9. Describe how DBMSs leverage parallelism to speed up query processing by dividing the work across multiple processors or nodes [Describe]

                            2.4.7. DM/DBMS Internals  (KA Core: 4 hrs) ↑ Back to top

                            Topics:
                            Core

                            • DB Buffer Management
                            • Transaction Management:
                              1. Isolation Levels
                              2. ACID
                              3. Serializability
                              4. Distributed Transactions enumerate
                              5. Concurrency Control:
                                1. 2-Phase Locking
                                2. Deadlocks handling strategies
                                3. Quorum-based consistency models enumerate
                                4. Recovery Manager:
                                  1. Relation with Buffer Manager enumerate

                                  Non Core

                                  • Concurrency Control:
                                    1. Optimistic concurrency control
                                    2. Timestamp concurrency control enumerate
                                    3. Recovery Manager:
                                      1. Write-Ahead logging
                                      2. ARIES recovery system (Analysis, REDO, UNDO) enumerate

                                      Learning Outcomes:
                                      Core:

                                      1. Describe how a DBMS manages its Buffer Pool [Describe]
                                      2. Describe the four properties for a correct transaction manager [Describe]
                                      3. Outline the principle of serializability [Outline]

                                      2.4.8. DM/NoSQL Systems  (KA Core: 2 hrs) ↑ Back to top

                                      Topics:
                                      Core

                                      • Why NoSQL? (e.g., Impedance mismatch between Application [CRUD] and RDBMS)
                                      • Key-Value and Document data model

                                      Non Core

                                      • Storage systems (e.g., Key-Value systems, Data Lakes)
                                      • Distribution Models (Sharding and Replication)
                                      • Graph Databases
                                      • Consistency Models (Update and Read, Quorum consistency, CAP theorem)
                                      • Processing model (e.g., Map-Reduce, multi-stage map-reduce, incremental map-reduce)
                                      • Case Studies: Cloud storage system (e.g., S3); Graph databases; "When not to use NoSQL"

                                      Learning Outcomes:
                                      Core:

                                      1. Develop a use case for the use of NoSQL over RDBMS [Create]
                                      2. Describe the defining characteristics behind Key-Value and Document-based data models [Describe]

                                      2.4.9. DM/Data Security and Privacy  (CS Core: 1 hr, KA Core: 2 hrs) ↑ Back to top

                                      Topics:
                                      Core

                                      • Differences between data security and data privacy
                                      • Protecting data and database systems from attacks, including injection attacks such as SQL injection
                                      • Personally identifying information (PII) and its protection
                                      • Ethical considerations in ensuring the security and privacy of data
                                      • Need for, and different approaches to securing data at rest, in transit, and during processing
                                      • Database auditing and its role in digital forensics
                                      • Data inferencing and preventing attacks
                                      • Laws and regulations governing data security and data privacy

                                      Non Core

                                      • Typical risk factors and prevention measures for ensuring data integrity
                                      • Ransomware and prevention of data loss and destruction

                                      Learning Outcomes:
                                      Core:

                                      1. Describe the differences in the goals for data security and data privacy [Describe]
                                      2. Identify and mitigate risks associated with different approaches to protecting data [Analyze]
                                      3. Describe legal and ethical considerations of end-to-end data security and privacy [Describe]
                                      4. Develop a database auditing system given risk considerations [Create]
                                      5. Apply several data exploration approaches to understanding unfamiliar datasets [Apply]

                                      2.4.10. DM/Data Analytics  (KA Core: 3 hrs) ↑ Back to top

                                      Topics:
                                      Core

                                      • Exploratory data techniques (motivation, representation, descriptive statistics, visualizations)
                                      • Data science lifecycle: business understanding, data understanding, data preparation, modeling, evaluation, deployment, and user acceptance
                                      • Data mining and machine learning algorithms: e.g., classification, clustering, association, regression
                                      • Data acquisition and governance
                                      • Data security and privacy considerations
                                      • Data fairness and bias
                                      • Data visualization techniques and their use in data analytics
                                      • Entity Resolution

                                      Learning Outcomes:
                                      Core:

                                      1. Describe several data exploration approaches, including visualization, to understanding unfamiliar datasets [Describe]
                                      2. Apply several data exploration approaches to understanding unfamiliar datasets [Apply]
                                      3. Describe basic machine learning/data mining algorithms and when they are appropriate for use [Describe]
                                      4. Apply several machine learning/data mining algorithms [Apply]
                                      5. Describe legal and ethical considerations in acquiring, using, and modifying datasets [Describe]
                                      6. Describe issues of fairness and bias in data collection and usage [Describe]

                                      2.4.11. DM/Distributed Databases/Cloud Computing ↑ Back to top

                                      Topics:
                                      Non Core

                                      • Distributed DBMS:
                                        1. Distributed data storage
                                        2. Distributed query processing
                                        3. Distributed transaction model
                                        4. Homogeneous and heterogeneous solutions
                                        5. Client-server distributed databases enumerate
                                        6. Parallel DBMS:
                                          1. Parallel DBMS architectures: shared memory, shared disk, shared nothing
                                          2. Speedup and scale-up, e.g., use of the MapReduce processing model
                                          3. Data replication and weak consistency models enumerate

                                          Learning Outcomes:
                                          NonCore:

                                          1. Describe the key components of a distributed DBMS, including distributed data storage, query processing, and transaction management [Describe]
                                          2. Analyze the tradeoffs between parallel DBMS architectures: shared memory, shared disk, and shared nothing [Analyze]
                                          3. Describe data replication strategies and weak consistency models in distributed database systems [Describe]

                                          2.4.12. DM/Semi-structured and Unstructured Databases ↑ Back to top

                                          Topics:
                                          Non Core

                                          • Vectorized unstructured data (text, video, audio, etc.) and vector storage:
                                            1. TF-IDF Vectorizer with ngram
                                            2. Word2Vec
                                            3. Array database or array data type handling enumerate
                                            4. Semi-structured databases (e.g., JSON):
                                              1. Storage:
                                                1. Encoding and compression of nested data types enumerate
                                                2. Indexing:
                                                  1. Btree, skip index, Bloom filter
                                                  2. Inverted index and bitmap compression
                                                  3. Space filling curve indexing for semi-structured geo-data enumerate
                                                  4. Query processing for OLTP and OLAP use cases:
                                                    1. Insert, Select, update/delete tradeoffs
                                                    2. Case studies on Postgres/JSON, MongoDB, and Snowflake/JSON enumerate enumerate

                                                    Learning Outcomes:
                                                    NonCore:

                                                    1. Describe vectorized representations of unstructured data (e.g., TF-IDF, Word2Vec) and their storage models [Describe]
                                                    2. Apply indexing techniques (e.g., B-tree, inverted index, Bloom filter) to semi-structured data stored in JSON format [Apply]
                                                    3. Analyze query processing tradeoffs for semi-structured databases in OLTP and OLAP use cases [Analyze]

                                                    2.4.13. DM/Society, Ethics, and the Profession ↑ Back to top

                                                    Topics:
                                                    Core

                                                    • Issues related to scale
                                                    • Data privacy overall:
                                                      1. Privacy compliance by design enumerate
                                                      2. Data anonymity
                                                      3. Data ownership/custodianship
                                                      4. Intended and unintended applications of stored data
                                                      5. Reliability of data
                                                      6. Provenance, data lineage, and metadata management
                                                      7. Data security

                                                      Learning Outcomes:
                                                      Core:

                                                      1. Enumerate three social and three legal issues related to large data collections [Enumerate]
                                                      2. Describe the value of data privacy [Describe]
                                                      3. Identify the competing stakeholders with respect to data ownership [Analyze]
                                                      4. Enumerate three negative unintended consequences from a given (well known) data-centric application (e.g., Facebook, LastPass, Ashley Madison) [Enumerate]
                                                      5. Describe the meaning of data provenance and lineage [Describe]
                                                      6. Identify how a database might contribute to data security as well as how it may introduce insecurities [Analyze]

                                                      Spotted a typo, an outdated course, a broken link, or have a suggestion? Let us know.

                                                      Scan to open on your phone