Database Administration
Comp 305-001/488-305
Spring 2016: Tuesdays, 4:15-6:45, Corboy L08
Text: Elmasri & Navathe, Fundamentals
of Database Systems 7e, Pearson 2015, ISBN-13: 978-0133970777.
You may also use the 6th edition. I will try to find alternative sources
for content present only in the 7th edition.
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 30-35%, and the final about 50%. The midterm is set for
March 15; the final will be Tuesday, May 3, at our usual time.
My general course groundrules are
here.
For Spring 2016 we will use PostgreSQL
(commonly pronounced "postgres" after its initial name) as our "standard"
database. Binaries are
available at the site for Windows, Mac and Linux computers. The latest
version, 9.5, is preferred, though not absolutely mandatory. You
are expected to install PostgreSQL.
Some class examples will also use MySQL, though as of now I do not
anticipate requiring its installation. Here's where to get MySQL
Community Server, an open-source database. The current version is
5.7.10, although earlier versions will work for most things. If you want to
use MySQL, I also recommend downloading the reference manual.
Note that Oracle, through its purchase of Sun, now owns the commercial part
of MySQL. It appears that you do not
have to create an account to download MySQL, though they ask you to.
If you wish, 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
Midterm Study Guide
Homework 3, due April 22
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 19
Week 2, Jan 26
(Dr Channah Naiman; her notes)
Week 3, Feb 2
Week 4, Feb 9
Week 5, Feb 16
Week 6, Feb 23
Week 7, Mar 1
Spring break
Week 8, Mar 15: midterm exam
Week 9, Mar 22
Week 10, Mar 29
Week 11, Apr 5
Week 12, Apr 12
Week 13, Apr 19
Week 14, Apr 26
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