CPSC 368:
Databases in Data Science
It's Nice To Meet You
Tell Me More

About

This class will explore the role of databases in data science. It will discuss both relational and non-relational databases and will delve into the process of working with each of these types of databases. This is not a course focused on the design of databases. If this is what you are interested in, please refer to CPSC 304.

The Head of the Computer Science Department has determined that this course will provide an in-person and not a hybrid, online, or multi-access mode of instruction.

The logo for CPSC 368

Prerequisites

Prerequisite: One of CPSC 203, CPSC 210, CPEN 221.

The CPSC department enforces prerequisites. Students without appropriate prerequisites will get a “missing prerequisite” letter near the start of the course. Watch for it in your e-mail if you fall into this category and follow the instructions in the letter to resolve it. If you do not have the prerequisites, you will be dropped from the course.

Learning Outcomes

These are the overall learning goals for each topic.

Topic Learning Goals
Introduction to a DBMS
  • Define the term database and explain the purpose of having a database.
  • Explain the high-level objectives of a database management system (DBMS), and explain how a DBMS relates to a database. List benefits that result from the usage of a DBMS.
Data Modeling (E-R Diagrams)
  • Explain the purpose of an E-R diagram, and explain the differences between entities, attributes, domains, and relationships.
  • Describe the different types of keys: search, candidate, primary, alternate, foreign, and super(key).
  • Explain the types of questions that you would ask a knowledgeable user in order to create a data model (E-R diagram) about a given business process.
  • Given a problem description, create an E-R diagram with the correct entities and relationships that accurately model the problem’s data. Justify the decisions you make for entities, relationships, keys, key constraints, participation constraints, aggregations, etc.
  • Given a problem domain/description that contains entities that: depend on other entities, inherit from other entities, or have n-ary relationships with other entities, create an E-R diagram that accurately represents them. Justify your decisions.
  • Given a problem description, identify alternative representations of the problem, and evaluate the choices.
  • For a given domain/problem, compare alternative E/R diagrams or techniques, and identify their strengths and weaknesses.
Relational Model & DDL
  • Compare and contrast logical and physical data independence.
  • Define the components (and synonyms) of the relational model: tables, rows, columns, keys, associations, etc.
  • Create and modify tables, including attributes, keys, field lengths, etc., using Data Definition Language (DDL).
  • Create a view for a table. Explain the purpose of a view.
  • Explain and differentiate the kinds of integrity constraints in a database (e.g., primary key constraints).
  • Explain the purpose of referential integrity.
  • Enforce referential integrity in a database using DML. Determine which delete, insert, or update policy to use when coding rules/defaults for referential integrity. Analyze the impact that a poor choice has.
  • Map E-R diagrams to the relational model (i.e., DDL), including constraints, weak entity sets, etc.
SQL
  • Justify the use of SQL as a standard. In particular, explain why SQL was developed, and compare it to programming-based approaches to extract data from a database (e.g., data structure traversal via pointers).
  • Given the schemas of a relation, create a simple query in SQL that qualifies the search using WHERE-clause arguments.
  • Create more complex queries in SQL that join multiple tables.
  • Create SQL queries that appropriately use the keywords: SELECT, FROM, WHERE, ORDER BY, EXISTS, NOT EXISTS, UNIQUE, NOT UNIQUE, ANY, ALL, and DISTINCT.
  • Create SQL queries that aggregate data, using the GROUP BY and HAVING clauses.
  • Show that there are alternative ways of coding SQL queries to yield the same result. Determine whether or not two SQL queries are equivalent.
  • Given an SQL query, table schemas, and instances, compute the result of the query.
  • Create SQL statements that appropriately use the keywords: INSERT, UPDATE, and DELETE.
  • Debate the use of NULLs and 3-valued logic to appropriately represent missing attribute values (“unknowns”). Argue that NULLs can be ambiguous in certain contexts. Suggest alternate ways of handling “unknowns” as an alternative to NULLs.
  • Identify the pros and cons of using of general table constraints (e.g., CONSTRAINT, CHECK), stored procedures, and triggers in databases.
  • Explain the role and advantages of embedding SQL in application programs.
  • Write embedded SQL that requires database access using Python.
  • Write embedded SQL in Python that requires database access and cursors.
Data Warehousing and OLAP
  • Compare and contrast OLAP and OLTP processing (e.g., focus, clients, amount of data, abstraction levels, concurrency, and accuracy).
  • Explain the ETL tasks (i.e., extract, transform, load) for data warehouses.
  • Explain the differences between a star schema design and a snowflake design for a data warehouse, including potential tradeoffs in performance.
  • Argue for the value of a data cube in terms of:
    • The type of data in the cube (numeric, categorical, temporal, counts, sums)
    • The goals of OLAP (e.g., summarization, abstractions), and
    • The operations that can be performed (drill-down, roll-up, slicing/dicing).
  • Write SQL statements to perform operations (e.g., drill-down, roll-up, slicing/dicing) in a data cube.
Data Mining
  • Define the term knowledge discovery.
  • Explain the general steps involved in the knowledge discovery in databases (KDD) process
  • Comment on the benefits and challenges that data mining has when dealing with imperfect data quality, especially in large datasets (e.g., data mining can point out anomalies (outliers), optimize the use of human time, detect patterns in data (including patterns that are there just by chance).
  • Explain the value of finding frequent itemsets and association rules. Provide some real-world examples of their use (e.g., retailing, biology).
  • Explain the purpose of association rules.
  • Apply the Apriori Algorithm and compute frequent itemsets and association rules (by hand, for a small dataset).
  • Explain how k-clustering works and apply an algorithm to find n clusters.
  • Critique issues surrounding the collection and mining of data.
NoSQL
  • Explain the differences between the relational and NoSQL model and state considerations that would help you decide which type of database would be more suitable for a given situation.
  • Identify situations where you would want to use a NoSQL database over a relational database.
  • Compare and constrast the different types of NoSQL databases.
  • Explain the benefits of using one type of NoSQL database over another.
  • Summarize the difference between Cassandra vs. MongoDB and list considerations that would impact whether you would use one over another.
  • Use Python to access a NoSQL database (MongoDB) to access and manipulate data.
  • Write MQL queries to solve a given problem.
Query Evaluation
  • Explain the need for indexing
  • Explain how B+-trees can be used to search for, insert, update, and delete entries.
  • Perform an insert/update/delete an entry on a B+ tree.
  • Explain how extendible and linear hashing work.
  • Perform an insert/update/delete operation using a linear hash index.
Miscellaneous
  • List and explain the importance of ACID properties.
  • Compare ACID and BASE properties.
  • Explain how the ARIES algorithm works.
  • Explain what MapReduce is and how it can be used to perform data analysis.
  • Recognize situations where MapReduce should be used.

FAQ

Frequently Asked Questions

Registration

  1. I want to register for this class but SSC says that there are no more seats available.
    Registration is centrally controlled by the Computer Science (CS) department and individual instructors have no say over who gets admitted to the course. The best advice we can give is to sign up for the waitlist.

    Curious about the waitlist? The UBC Computer Science department has a FAQ page about waitlists.

  2. I am on the waitlist and SSC says that there are spaces in the class. When will I be moved into the class?
    The department will periodically process students from the waitlist into the course so please be patient.

    Some of the seats in the class are reserved for students with a particular major (or minor) so you might see that the course has space but the waitlist has not moved.

  3. I am not registered in the class but can I sit in?
    Due to COVID safety protocols, we cannot allow for non-registered students to sit in on the class.

  4. I would like to audit the class. Who should I talk to get this process started?
    The conditions for auditing the class are:
    • You have to already be registered in the class
    • You have to pass all the course components except for the final exam (which you will not have to take).

    If you are willing to accept these conditions, follow the instructions listed here to get the process started.

  5. I don't have the listed prerequisites but I have previously completed courses that are similar to the listed prerequisites. Am I still allowed to take this course?
    The CS department adheres to a strict prerequisite checking policy. If you do not have the listed prerequisites, you will receive a letter at the beginning of the semester that will tell you what to do next. Please keep an eye out for that letter - if you do not respond to the letter, you may be deregistered from the course.

Course

  1. How is this course different from CPSC 304? Which course should I take?
    CPSC 368 is focused on the use of databases and not how to design databases. It approaches databases from the perspective of "I already have a database that has been set up so how do I get what I need out of it?" whereas 304 approaches databases from the viewpoint of "I need to do something with a database from scratch so how do I start?". Topics such as normalization (how to make the design of your database less redundant) and relational algebra (which talks about the underlying concepts for other query language such as SQL and are necessary to understand how to efficiently execute queries) are not taught in 368.

    CPSC 368 is designed for data scientists and will use Python instead of Java or PHP. It also doesn't have a term project - it has a series of assignments over the term which can be extended to different small data science projects.

    CPSC 368 is not a prerequisite for 404. If you take 368 and decide that you love databases so much you want to take another course in it, you will have to take 304 before going to 404. (This is a decision by the UBC Senate and not the course instructors so there is nothing we can do to change this requirement.) If you take 304 after 368 (or vice versa), the second course will not count towards your degree requirements (i.e, you have to pay for it but you can't count it towards your "x credits of an upper year Science course" type of requirement).

    Ultimately, which course to take will depend on what you want to do for the next 2-3 years. Do you want a software development job? If so, the 304 term project allows you to showcase a larger range of technical abilities. Do you want to go into data science instead? 368 will have assignments that give you the opportunity to develop small projects to present (but the technical portion is not nearly as complex as 304).

  2. What websites/platforms will we be using in class?
    We will be using:
    • Canvas to host our course materials (lecture slides, assignments, etc.)
    • Piazza as a place for Q&A
    • Syzygy/Jupyter as a place to complete some of the tutorials/assignments
    • Gradescope for retrieving your graded exams

  3. Will we need a textbook?
    The textbook for our class will be Database Management Systems 3/E by RAMAKRISHNAN (ISB: 9780072465631). There are copies of this textbook in the reading room.

    We will not be assigning specific questions from this textbook but many of our course topics will list associated chapters from the textbook.

  4. When is the final?
    The final exam will be held at a time and place determined by the university. Please do not book travel plans until after the final exam schedule has been announced.