5.22. Data Management (Mandatory)

5.22. Data Management (Mandatory)

Figure 5.22: Connection Map. CS271 Data Management

5.22.1. Justification ↑ Back to top

This course introduces fundamental concepts and techniques in data management, focusing on relational database systems. Students will learn data modeling using entity-relationship diagrams, relational database design principles, normalization theory, and Structured Query Language (SQL). The course covers core database system components, transaction management, and basic query processing. Emphasis is placed on practical skills for designing, implementing, and querying databases, as well as understanding the role of data in organizations and the associated ethical considerations in data management.

5.22.2. Generales Goals ↑ Back to top

  1. Design and implement normalized relational databases.
  2. Formulate complex queries using SQL for data retrieval and manipulation.
  3. Apply data modeling techniques using entity-relationship diagrams.
  4. Understand database system architecture and transaction management.
  5. Recognize ethical and security considerations in data management.
  6. Utilize basic database administration tools and techniques.

5.22.3. Contribution to Outcomes ↑ Back to top

AG-C09) Design and Development of Solutions: Designs, implements, and evaluates solutions for complex computing problems. (Usage)
AG-C12) Applies computer science theory and software development fundamentals to produce computer-based solutions. (Usage)

5.22.4. Content ↑ Back to top

5.22.4.1. Core Database System Concepts (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

Bibliography: (Ramakrishnan and Gehrke, 2002; Silberschatz et al., 2019)

Topics

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

Learning Outcomes

  1. Identify at least four advantages that using a database system provides [Familiarity]
  2. Enumerate the components of a (relational) database system [Familiarity]
  3. Explain the advantages of database systems over file systems [Usage].
  4. Describe the three-schema architecture and data independence [Familiarity].
  5. Identify different types of database users and their roles [Familiarity].
5.22.4.2. Data Modeling (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

Bibliography: (Ramakrishnan and Gehrke, 2002; Elmasri and Navathe, 2015)

Topics

  1. Conceptual models (e.g., entity-relationship, UML diagrams)
  2. Entity sets, relationship sets, and attributes
  3. Cardinality constraints: one-to-one, one-to-many, many-to-many
  4. Weak entity sets and identifying relationships
  5. Extended E-R features: specialization, generalization, aggregation
  6. UML class diagrams for data modeling

Learning Outcomes

  1. Describe the components of the E-R (or some other non-relational) data model [Familiarity]
  2. Model a given environment using a conceptual data model [Usage]
  3. Design E-R diagrams for real-world scenarios [Usage].
  4. Convert E-R diagrams to relational schemas [Usage].
  5. Apply extended E-R features for complex modeling [Assessment].
5.22.4.3. Relational Databases (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

Bibliography: (Ramakrishnan and Gehrke, 2002; Elmasri and Navathe, 2015)

Topics

  1. Entity and referential integrity: Candidate key, superkeys
  2. Relational database design
  3. Relational algebra operations: select, project, join, union, etc.
  4. Integrity constraints: domain, key, entity, referential
  5. Relational calculus: tuple and domain calculus
  6. Mapping E-R diagrams to relational schemas

Learning Outcomes

  1. Describe the defining characteristics behind the relational data model [Familiarity]
  2. Comment on the difference between a foreign key and a superkey [Usage]
  3. Express queries using relational algebra [Usage].
  4. Design relational schemas with appropriate constraints [Usage].
  5. Map complex E-R diagrams to normalized relations [Assessment].
5.22.4.4. Query Construction (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

Bibliography: (Ramakrishnan and Gehrke, 2002; Beaulieu, 2009)

Topics

  1. SQL Query Formation:
    1. Interactive SQL execution
    2. Programmatic execution of an SQL query enumerate
    3. Data definition language: CREATE, ALTER, DROP
    4. Data types and domain constraints creation and management
    5. View definition and materialized views
    6. Database schema design and implementation

    Learning Outcomes

    1. Compose SQL queries that incorporate select, project, join, union, intersection, set difference, and set division [Usage]
    2. Define, in SQL, a relation schema, including all integrity constraints and delete/update triggers [Usage]
    3. Create and modify database schemas using SQL DDL [Usage].
    4. Implement integrity constraints at schema level [Usage].
    5. Design and create appropriate indexes for performance [Assessment].
    5.22.4.5. Query Construction (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

    Bibliography: (Ramakrishnan and Gehrke, 2002; Beaulieu, 2009)

    Topics

    1. Complex SQL queries: nested subqueries, correlated subqueries
    2. Aggregation functions and GROUP BY operations
    3. Set operations: UNION, INTERSECT, EXCEPT
    4. JOIN operations: inner, outer, natural, cross joins
    5. Data manipulation: INSERT, UPDATE, DELETE
    6. Transaction control: COMMIT, ROLLBACK, SAVEPOINT

    Learning Outcomes

    1. Determine when a nested SQL query is correlated or not [Usage]
    2. Compose an SQL query to update a tuple in a relation [Usage]
    3. Formulate complex queries with multiple joins and subqueries [Usage].
    4. Apply aggregation for data analysis and reporting [Usage].
    5. Implement data modification operations with transaction control [Assessment].
    5.22.4.6. Relational Databases (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

    Bibliography: (Ramakrishnan and Gehrke, 2002; Elmasri and Navathe, 2015)

    Topics

    1. Introduction to Functional dependency theory
    2. 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
      4. Functional dependencies and inference rules
      5. Normal forms: 1NF, 2NF, 3NF, BCNF
      6. Decomposition algorithms: lossless-join and dependency-preserving
      7. Practical considerations in normalization

      Learning Outcomes

      1. Describe how functional dependency theory generalizes the notion of key [Familiarity]
      2. Detect which normal form a given decomposition yields [Usage]
      3. Identify functional dependencies in relational schemas [Usage].
      4. Normalize relations to BCNF [Assessment].
      5. Evaluate decompositions for lossless-join and dependency preservation [Assessment].
      5.22.4.7. DBMS Internals (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

      Bibliography: (Ramakrishnan and Gehrke, 2002; Bernstein et al., 1987)

      Topics

      1. 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. ACID properties: Atomicity, Consistency, Isolation, Durability
          5. Serializability and conflict serializability
          6. Lock-based concurrency control: two-phase locking
          7. Deadlock detection and prevention
          8. Isolation levels and their trade-offs

          Learning Outcomes

          1. Describe the four properties for a correct transaction manager [Familiarity]
          2. Outline the principle of serializability [Familiarity]
          3. Explain ACID properties and their importance [Usage].
          4. Analyze transaction schedules for serializability [Assessment].
          5. Apply locking protocols for concurrency control [Usage].
          5.22.4.8. Data Security and Privacy (8 hours) [Skills AG-C09,AG-C12] ↑ Back to top

          Bibliography: (Ramakrishnan and Gehrke, 2002; Elmasri and Navathe, 2015)

          Topics

          1. Differences between data security and data privacy
          2. Personally identifying information (PII) and its protection
          3. Ethical considerations in ensuring the security and privacy of data
          4. Database security: authentication, authorization, encryption
          5. SQL injection attacks and prevention
          6. Introduction to NoSQL databases and big data
          7. Data governance and compliance

          Learning Outcomes

          1. Describe the differences in the goals for data security and data privacy [Familiarity]
          2. Identify and mitigate risks associated with different approaches to protecting data [Usage]
          3. Implement basic database security measures [Usage].
          4. Prevent SQL injection vulnerabilities [Assessment].
          5. Discuss ethical considerations in data collection and use [Assessment].

          5.22.5. Bibliography ↑ Back to top

          Ramakrishnan, R. and Gehrke, J. (2002). Database Management Systems. McGraw-Hill, 3rd edition.

          Silberschatz, A., Korth, H. F., and Sudarshan, S. (2019). Database System Concepts. McGraw-Hill, 7th edition.

          Elmasri, R. and Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson, 7th edition.

          Beaulieu, A. (2009). Learning SQL. O'Reilly Media, 2nd edition.

          Bernstein, P. A., Hadzilacos, V., and Goodman, N. (1987). Concurrency Control and Recovery in Database Systems. Addison-Wesley.

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

          Scan to open on your phone