Database Administration
Comp 305-001/488-305
Spring 2018: Tuesdays, 4:15-6:45, Comm 013
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 13 (week 8); the final will be Tuesday, May 1, 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, 10.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 is recommended. Here's where to get MySQL
Community Server, an open-source database. The current version is
5.7.20, although any 5.7.x version 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
now owned by Oracle.
Postgres is, very roughly speaking, similar to Oracle. But if you wish to
gain experience with Oracle itself, here is where to get Oracle
Database 12c Release 1 (standard edition). I don't really recommend
this, but if you get it to install, let me know. I was not able to
install this on my linux system. A much easier (though not exactly smaller)
option, and the one I use, is to install an Oracle-provided virtual machine
that has Oracle installed within it (Oracle bought VirtualBox too, perhaps
for just this purpose). The link is here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html.
This takes care of all those installation "rough edges". The virtual disk I
downloaded for this is 8 GB (I don't know if that was its original size, or
if it has grown). Be aware that you might not be able to run VirtualBox and
VMWare at the same time (if you're already a VMWare user).
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 16
Week 2, Jan 23
Week 3, Jan 30
Week 4, Feb 6
Week 5, Feb 13
Week 6, Feb 20
Week 7, Feb 27
Spring break
Week 8, Mar 13: midterm exam
Week 9, Mar 20
Week 10, Mar 27
Week 11, Apr 3
Week 12, Apr 10
Week 13, Apr 17
Week 14, Apr 24
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
The university database (E&N p 8, Fig 1.2) is here: student.text.
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:
- Relational model
- normal forms
- advanced SQL
- Indexes and their role in query performance
- Query optimization
- Query algorithms
- Transactions and concurrency
- Access management
- Database configuration