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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*Date: 10/19/15 (Mon)*

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

*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.

*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.

*Date: 10/12/15 (Mon)*

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

*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.

*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.

*Date: 10/05/15 (Mon)*

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

*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.

*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.

*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.

*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.

*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.

*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.

*Date: 09/14/15 (Mon)*

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

*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.

*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.

*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.

*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.

*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.

*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.

*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.

*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.