Database Systems (CIS-3010) Home Page

This is the home page for Peter Chapin's Database Systems course notes for the Spring 2023 semester. Here you will find electronic versions of class handouts, homework assignments, lecture slides, and links to other references of interest. If you are a student taking Database Systems you should bookmark this page.

The Zoom meeting URL gives you access to the live lectures.
The homework submission area and grade book are on Canvas but all other course resources are here.
The course syllabus gives an overview of the course and its content, lists course resources, and describes the grading policy and related issues.
We will be using PostgreSQL as the database management system in the lab and for assignments. We will use the server running on Lemuria, one of the CIS department's Linux systems. You do not need to set up the PostgreSQL database server on your own computer, although you can if you would like.
I've prepared some general information on submitting assignments.
My home page contains other resources of potential interest.

Lecture Topics

The lectures for this course are on Zoom. The labs for the course will be face-to-face.

2023-01-18. Course introduction and overview. Discussed Lab #1 and introduced the variable star database.
2023-01-23. Introduced DBeaver. Introduced Lab #2, and started talking about the SQL SELECT slides.
2023-01-25. Gave a short astronomy lesson via Stellarium. Continued discussing the SELECT statement.
2023-01-30. Described the more complete definition of the VariableStars database showing various features of how to create tables, specify their constraints, and insert data into tables.
2023-02-01. Discussed Lab #3 and the PeakBaggers database. Introduced jOINs.
2023-02-06. More discussion of JOINs. Discussed INSERT, UPDATE, and DELETE in more detail. Introduced sub-queries.
2023-02-08. Discussed aggregate types (arrays, etc.), and their use and limitations. Introduced 1st normal form.
2023-02-13. Introduced Lab #5. Introduced ER diagrams using Chen's notation, crow's foot notation, and IDEF1X notation.
2023-02-15. Discussed 2nd and 3rd normal form.
2023-02-20. No class (Vacation).
2023-02-22. No class (Vacation).
2023-02-27. Discussed Lab #6. Started discussing column (aggregate) functions and row functions.
2023-03-01. Introduced XML. Demonstrated CEML and COML.
2023-03-06. Continued discussing XML using AOML as an example.
2023-03-08. Discussed stored functions and introduced views.
2023-03-13. Introduced Lab #7 on stored functions. Introduced stored procedures. Described how to back up lab databases.
2023-03-15. Described the security model of PostgreSQL and introduced the GRANT and REVOKE commands.
2023-03-20. Introduced the FoodTracker lab.
2023-03-22. Introduced JDBC and PHP Data Objects.
2023-03-27. More on Lab #8. Discussed some specifics on how to import the USDA data. Discussed triggers.
2023-03-29. Introduced the concept of deductive databases and gave a short overview of the Datalog query language.
2023-04-03. No class (Vacation).
2023-04-05. No class (Vacation).
2023-04-10. Introduced MongoDB.
2023-04-12. Demonstrated the Mongo shell, and discussed making queries against MongoDB.
2023-04-17. More discussion about MongoDB. Introduced Lab #9.
2023-04-19. Discussed ACID, BASE, and the CAP theorem.

Slides

These slides are by Joan Spasyk. They are being used with permission. They have been lightly edited by me.

Lab Assignments

The lab set is summarized in the lab summary document, along with links to specific lab handouts and supporting code samples.

Samples

The variable stars database consists of the Lab #1 starter files together with a more full-featured description of the tables (provided later). This information comes from the Sternberg Astronomical Institute at Moscow State University.
A more complete definition of the VariableStars database includes tables for observers, locations, and observations. See star-tables.sql. Here also is some raw data to populate the additional tables.
The aggregates.sql script shows some issues with creating "multivalued" attributes in the context of the PeakBaggers example.
To support the Temperature database you create in Lab #5 (and use again in Lab #6), here is some raw temperature data and the deployment map for the VTC SensorNet.
Course Evaluation Markup Language (CEML) is a custom XML vocabulary for marking up data associated with course evaluations. Sample, Schema definition.
Course Outline Markup Language (COML) is a custom XML vocabulary for marking up course outlines. Several years ago it was being considered for use at VTC.
Astronomical Observation Markup Language (AOML) is a custom XML vocabulary for marking up amateur astronomical observations.
A sample of stored functions used with the variable star database.
A sample of a stored procedure used with the variable star database.
USDA data on foods that is used in Lab #8.
A simple demonstration program that illustrates JDBC (including an IntelliJ project).
A PHP script that connects to the PostgreSQL database server on Lemuria.
A sample of using triggers with PostgreSQL.
Some examples of Datalog, a non-SQL query language that is sometimes used with deductive databases. Unlike SQL based relational databases, deductive databases can "deduce" information about your data given rules.

Resources/Articles

Database Server Systems

PostgreSQL

The PostgreSQL Database. This is the database management system we will be using in this class.
Documentation for PostgreSQL version 14. This is the version on Lemuria.
The documentation for how to back up a PostgreSQL database is useful for saving your work in this class.
Here is a nice tutorial description of how PostgreSQL's GENERATED AS IDENTITY feature works.

MongoDB

MongoDB is a database server for "document oriented" databases.
Compass is a tool for interacting with the data on a MongoDB instance (think: "Dbeaver for Mongo"). Here is the download page for Compass. Windows, macOS, and Linux are all supported.
JavaScript is used as a database query language by MongoDB.
JSON is used by MongoDB as a language for representing data.
Mongo uses Perl-style regular expressions in its query language.
There are various tutorials for MongoDB:
- A Getting Started tutorial on the MongoDB website.
- A list of tutorials (basic and advanced) on the MongoDB website.
- A MongoDB tutorial on TutorialsPoint.
- O'Reilly has many MongoDB resources. The O'Reilly collection contains, among other things: MongoDB: The Definitive Guide, 3rd Edition. Log in using your VTC credentials.

MSSQL Server

Microsoft SQL Server Documentation. This is the official documentation site for Microsoft's SQL Server.
Reference Manual for T-SQL, Microsoft's dialect of SQL. Microsoft, like many database engine vendors, implements a number of extensions to standard SQL that are specific to their product.
Documentation for sqlcmd. This is Microsoft's command line tool for interacting with the MSSQL database engine in a text-mode environment.

SQLite

SQLite is not a database server. In fact, it is the opposite: it is a C library that allows applications to store data in local files, but access that data using SQL statements. It has a reputation for being very reliable and efficient.

SQL Information and Tutorials

This SQL style guide seems useful. Many people have their own style, but if you aren't sure, start with this.
Wikipedia's page on SQL has a nice discussion of the scope, nature, and history of SQL.
A description of the various SQL standards and their evolution.
An SQL tutorial from W3Schools
An SQL tutorial from TutorialsPoint
An SQL tutorial from Code Academy
An SQL tutorial from Khan Academy
An SQL tutorial from SQL Zoo

Database Graphical Tools

The DBeaver tool can also be used for diagramming databases. It supports Windows, macOS, and Linux.
Wikipedia's article on Entity-Relationship diagrams gives a good overview of the concepts and shows Chen's notation and crow's foot notation.
This article on crow's foot notation for ER diagrams is fairly nice. Note that the text uses Chen's notation for ER diagrams which looks different but still endeavors to capture much the same information.
A cheat sheet for IDEF1X notation that is used be DBeaver in its ER diagrams.
One online tool for drawing ER diagrams is Lucid Chart.
Another online tool for drawing ER diagrams is diagrams.net
Yet another online tool for drawing ER diagrams, specializing in database diagrams, is dbdiagram.io

Database Theory

Here is a nice description about why 1st normal form is desirable. Note that the first and second answers are good, but the third answer is wrong and the fourth answer is muddled.
The Wikipedia page on ACID.
The Wikipedia page on BASE.
The Wikipedia page on the CAP theorem.

JDBC

This tutorial on JDBC from TutorialsPoint looks reasonable.
Information about the PostgreSQL JDBC driver
Download the JDBC driver for PostgreSQL.