Database Administration

Comp 305-001/488-305

Spring 2020: Mondays, 5:30-8:00, Corboy L09

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 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 March 16 (week 8); the final will be Monday, April 27, at our usual time.

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, 12.1, 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 helpful. Here's where to get MySQL Community Server, an open-source database. The current version is 8.0.18, although any recent should work for our purposes. 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 13
 Jan 20: no class
Week 2: Jan 27 Week 3: Feb 3
Week 4: Feb 10 Week 5: Feb 17
Week 6: Feb 24 Mar 2: no class
Week 7: Mar 9 Week 8: Mar 16
Week 9: Mar 23 Week 10: Mar 30
Week 11: Apr 6 Week 12: Apr 13
Week 13: Apr 20
 Final: Apr 27


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: