Database Administration

Comp 305-001/405-001

Spring 2024: MWF 1:40-2:30. Completely online Wednesdays and Fridays. On Mondays I will lecture in Life Science Building 412, and also stream on Zoom. If it works for you to attend in person those days, wonderful! If not, you can still attend via Zoom.

Text: We will use a variety of online sources for this course, including online documentation for MySQL and Postgres.

The following text is optional, though recommended for students who have not taken a database course previously: Elmasri & Navathe, Fundamentals of Database Systems 7e, Pearson 2015, ISBN-13: 978-0133970777. The 6th edition is almost as complete. Course notes will refer to this text, but the notes will provide nearly equivalent coverage.

There will be some homework assignments during the semester, plus a midterm and final. The assignments will be worth about 15-20% of your grade, the midterm 35-40%, and the final about 45%. The midterm is set for the week of March 18 (week 9); the final will be Thursday, May 2, 1:00-3:00.

My general course groundrules are here.


Our primary databse will be PostgreSQL (commonly pronounced "postgres" after its original name). Binaries are available at the site for Windows, Mac and Linux computers. The latest version, 16.x, is preferred, though not absolutely mandatory. You are expected to install PostgreSQL.

Some class examples will also use MySQL. While not required, installation of MySQL might be of interest. Here's where to get MySQL Community Server, an open-source database. If you want to use MySQL, I also recommend downloading the reference manual. It appears that you do not have to create an account to download MySQL, though the site asks you to. Note that MySQL is owned by Oracle.


Study Guides


Class notes and readings:

My primary notes have now been divided into topic-specific files:

There are also brief notes for each class meeting:

Week 1: Jan 17-
 Week 2: Jan 22-
Week 3: Jan 29-
 Week 4: Feb 5-
Week 5: Feb 12- Week 6: Feb 19-
Week 7: Feb 26- Spring break: Mar 4-
Week 8: Mar 11-
 Week 9: Mar 18-
Week 10: Mar 25- Week 11: Apr 3-
Week 12: Apr 8- Week 13: Apr 15-
Week 14: Apr 22-
 Final: May 2


Examples


The company database (spreadsheet, files). To load this into Postgres, take the contents of table definitions and just paste it in to a postgres command window. Or start postgres (with psql) in the directory containing company.alter.text, and run
    \i company.alter.text

(On Windows systems this is unnecessarily difficult. The best approach is to make sure psql is in your %PATH%; an alternative is to copy the file into the appropriate system directory that contains psql.exe.)


The university database (E&N p 8, Fig 1.2) is here: student.text. A diagram is here. I added three additional students, and their grades. You should be able to paste this in to Postgres, or load it with the \i command as above.


We will consider some of the following topics: