CMPSC 431W Database Management Systems

Fall 2015

Department of Computer Science and Engineering

Penn State University


Find the lecture slides and lecture videos here. Note that the slides uploaded here have words taken out as "fill in the blank." You will only be provided with the filled in words by attending the lectures or watching the lecture videos.

Disclaimer: all the slides are originally created by McGraw Hill, first revised by Dr. Wang-Chien Lee, and second revised by Yu-San Lin.

Lecture 36: Index Selection and Query Evaluation

Date: 12/4/15 (Fri)

In this lecture, we continue the discussion on index selection. We then introduce the basic of query evaluation, including relational algebra trees and the algorithms for executing the operators in relational algebra.

[slides] [video]

Lecture 35: Cost Analysis Model and Index Selection

Date: 12/2/15 (Wed)

We finish up the discussion on cost analalysis model in this lecture. We then move on to talk about index selection, in which we consider among all the indexing strategies we have learned so far, which are the best under different situations.

[slides] [video]

Lecture 34: Cost Analysis Model

Date: 11/30/15 (Mon)

Welcome back from the break! In this lecture, we introduce how to analyze cost of different operations: scan, search, insertion, and deletion, under different file organizations and indexings: heap files, sorted files, clustered files, and unclustered files.

[slides] [video]

Lecture 33: Indexing: Tree-Based Indexing, ISAM

Date: 11/20/15 (Fri)

Continue the discussion on indexing, we introduce ISAM in this lecture by working through different operations in actions on such indexing approach.

[slides] [video]

Lecture 32: Indexing: Hash-Based Indexing

Date: 11/18/15 (Wed)

Continued the topics from last lecture, we discuss different approaches of indexings that work with the alternatives. To start the discussion on indexing, we introduce hash-based indexing.

[slides] [video]

Lecture 31: File Organizations

Date: 11/16/15 (Mon)

In this lecture, we introduce storage engines. In particular, we focus on three alternatives of file organization: Alternative 1, Alternative 2, and Alternative 3.

[slides] [video]

Lecture 30: Internet Applications - Part 3

Date: 11/6/15 (Fri)

To wrap up the internet applications, we present designing simple input forms in HTML, using Cascading Style Sheet (CSS) to change up the styles, and adding JavaScript for additional functionalities.

[slides] [video]

Lecture 29: Internet Applications - Part 2

Date: 11/4/15 (Wed)

In this lecture, we discuss Document Type Definitions. We also demonstrate how to scrape data from the web using BeautifulSoup in Python. Lastly, we introduce different system architectures: single tier, two tier, and three tier.

[slides] [video]

Lecture 28: Internet Applications - Part 1

Date: 11/2/15 (Mon)

Up until now, we have not yet discussed about applications of DBMS when considering the Internet. In this lecture, we started out by introducing some basics of Internet communication protocols, and discuss three well known web data formats: HTML, XML, and JSON.

[slides] [video]

Lecture 27: Database Application Development - Part 2

Date: 10/30/15 (Fri)

Continued the last lecture, we finish our discussion on JDBC in this lecture. We also introduce SQLJ and Stored Procedures.

[slides] [video]

Lecture 26: Database Application Development - Part 1

Date: 10/28/15 (Wed)

In this lecture, we cover the more traditional and well known tools for combining the frontend and backend, including: Embedded SQL, and JDBC.

[slides] [video]

Lecture 25: Introduction to NoSQL: MongoDB - Part 2

Date: 10/26/15 (Mon)

Wrapping up the lesson on NoSQL, we learn about the Update function in MongoDB by playing around with the unicorn dataset a bit more.

[slides] [video]

Lecture 24: Introduction to NoSQL: MongoDB - Part 1

Date: 10/23/15 (Fri)

Today we study a bit further on NoSQL by learning the basics of MongoDB. We also practice how to implement three out of the four CRUD semantics, Create, Read, and Delete, on the unicorn dataset.

[slides] [video]

Lecture 23: Introduction to NoSQL

Date: 10/21/15 (Wed)

We introduct NoSQL in this lecture. Contents include: What is NoSQL? Who is using NoSQL? What are the main types of NoSQL database? We also have a brief taste of how the four types of NoSQL database are like: Key-value database, column family database, graph database, and document database.

[slides] [video]

Lecture 22: SQL - Part 4

Date: 10/19/15 (Mon)

We finish the discussion on SQL in this lecture by introducing the grouping queries, and practicing more query implementations.

[slides] [video]

Lecture 21: SQL - Part 3

Date: 10/16/15 (Fri)

We continue the discussion of nested queries in SQL, and further present how we can create nested queries with correlations. Also, we introduce the aggregation operators in SQL.

[slides] [video]

Lecture 20: SQL - Part 2

Date: 10/14/15 (Wed)

Continue the last lecture, in this lecture, we discuss about how to implement set operations in SQL. We also learn about an important and powerful feature of SQL: the nested queries.

[slides] [video]

Lecture 19: SQL - Part 1

Date: 10/12/15 (Mon)

In this lecture, we learn about the basic SQL commands to implement queries practiced in Relation Algebra.

[slides] [video]

Lecture 18: Decompostition into BCNF and 3NF

Date: 10/09/15 (Fri)

After learning about normal forms, you must be curious to know how to decompose schemas that are not satisfactory into proper normal forms. In this lecture, we introduce algorithms that can guide you to decompose schemas into BCNF and 3NF that are lossless-join and dependency-preserving.

[slides] [video]

Lecture 17: Boyce-Codd Normal Form (BCNF) & Decompositions

Date: 10/07/15 (Wed)

In this lecture, we discuss the last normal form, Boyce-Codd Normal Form (BCNF). We then talk about the properties of decompositions, which are covered by two types: lossless-join decomposition and dependency-preserving decomposition.

[slides] [video]

Lecture 16: Third Normal Form (3NF)

Date: 10/05/15 (Mon)

Continue the last lecture, we finish examples of checking whether a schema is in 3NF.

[slides] [video]

Lecture 15: Armstrong's Axioms & Normal Forms

Date: 10/02/15 (Fri)

Today we discuss the soundness and completeness of Armstrong's Axioms, the concept of Attribute Closure, and introduce three of the normal forms: first normal form, second normal form, and third normal form.

[slides] [video]

Lecture 14: Functional Dependency & Armstrong's Axioms

Date: 09/30/15 (Wed)

After learning conceptual design, let's think about whether the designs are "efficient" by learning a new concept, Functional Dependency (FD). You will learn how to identify functional dependencies from a given schema, and to use Armstrong's Axioms to derive the closure of functional dependencies.

[slides] [video]

Lecture 13: Relational Calculus - Part 2

Date: 09/25/15 (Fri)

Continue the Relational Calculus we introduced at the end of last lecture, in this lecture, we work on some query examples to practice Tuple Relational Calculus (TRC). We also compare the concept between Relational Calculus and Relational Algebra we learned in the past few lectures.

[slides] [video]

Lecture 12: Relational Algebra - Part 4 & Relational Calculus - Part 1

Date: 09/23/15 (Wed)

In this lecture, we continue working through the translation from queries to relational algebra expressions. Besides the query exercises, we also discuss about the aggregation & grouping operator. We then introduce the concept of relational calculus, which is a declarative langauge, in contrast the procedural relational algebra. In particular, we focus on Tuple Relational Calculus (TRC) today.

[slides] [video]

Lecture 11: Relational Algebra - Part 3

Date: 09/21/15 (Mon)

To provide a better sense of how to express various queries in relational algebra, in this lecture, we will be working on ten relational algebra exercises by using the knowledge we learned in lecture 9 and 10.

[slides] [video]

Lecture 10: Relational Algebra - Part 2

Date: 09/16/15 (Wed)

Continue the last lecture, we talk about some additional operations used in relational algebra: renaming, joints (condition joint, equijoint, and natural joint), and division.

[slides] [video]

Lecture 9: Relational Algebra - Part 1

Date: 09/14/15 (Mon)

In this lecture, we introduce the concept of Relational Algebra, starting with basic operators and set operations.

[slides] [video]

Lecture 8: Introduction to SQL Programming (lab) - Part 3

Date: 09/11/15 (Fri)

Continue the in-class lab on SQL programmin in this lecture, you will be learning how to implement foreign keys, key constraints, participation constraints, and weak entities in SQL by translating ER diagrams into SQL commands.

[slides] [video]

Lecture 7: Introduction to SQL Programming (lab) - Part 2

Date: 09/09/15 (Wed)

We continue the in-class lab on SQL programmin in this lecture. You will be implementing integrity constraints with SQL, and querying from the relational data.

[slides] [video]

Lecture 6: Relational Model (cont.) & Introduction to SQL Programming (lab) - Part 1

Date: 09/04/15 (Fri)

In this lecture, we start by reviewing some confusions we had from the last few ER model lectures. We then have an in-class lab on SQL programmin to help us learn the basics for Relational Model.

[slides] [video]

Lecture 5: ER Model (cont.) & Relational Model

Date: 09/02/15 (Wed)

We wrap up the discussion on ER model in this lecture. We also move on to learn about relational model, with the help of some basic SQL examples.

[slides] [video]

Lecture 4: ER Model (cont.)

Date: 08/31/15 (Mon)

Continue from the last lecture. In this lecture, we introduce more details about ER model design. The concepts covered include: participation constraints, weak entities, class (ISA) hierarchies, and aggregation.

[slides] [video]

Lecture 3: ER Model

Date: 08/28/15 (Fri)

We introduce the basics of Entity-Relationship Model (ER Model) in this lecture, which includes the concepts of entity, relationship, attribute, and key constraints. We also briefly discuss what is expected in phase 1 of the project.

[slides] [video]

Lecture 2: Introduction (cont.)

Date: 08/26/15 (Wed)

This lecture covers the concepts of relational data model, levels of abstraction, data independence, queries, transactions, and structure of DBMS.

[slides] [video]

Lecture 1: Introduction

Date: 08/24/15 (Mon)

In this lecture, we will give you the overview/expectation of this course, the overview of Database Management Systems, and a brief introduction of data models.

[slides] [video]