Announcements

• December 7: Problem Set #3 has been graded and Problem Set #1 re-graded. I will do my best finish grading Problem Set #2 tonight.
• December 4: I will hold a final study session for Comp 521 on Saturday from 4pm to 6pm on Saturday 12/6. It will be held in SN011.
• November 18: As I announced in class today, the second midterm has been delayed until 11/25 due to popular demand. If that time will not work for you make sure that you send me an email to arrange another time to take the test. I will also hold a study session to prepare for the 2nd midterm on Wednesday night, 11/19. During the study session I will go over a midterm from a previous semester.
• November 11: Problem Set 3 is now on-line. It is due in only one week (11/18). I will hold extra office hours on Monday 11/17 from 3pm-5pm. I will also hold a study session in SN-014 from 6pm-8pm that night.
• November 3: I have postponed the second mid-term until 11/20. More news to come soon.
• October 8: The due date of the second problem set has been moved to Oct 14. Make good use of the extra weekend, I suggest that you read the online manual for the sqlite3 Python package (and the URL is fixed now... thanks for the bug reports). Leonard McMillan will hold extended office hours from 5pm-7pm on Monday, Oct 13.
• September 30: The first midterm will be given in class on 10/2. It will be open notes, but closed books, closed computer, mobile phone, and tablet or any other device with internet access. The format of the test will be multiple choice (~18-20 questions) and short answer (~2-4 questions). There will be a midterm study session held tomorrow night in SN014 from 6pm-8pm.   
• September 26: The second problem set is now posted, and its new due date is October 9, 2014. Stay tuned for an announcement concerning the first mid-term.
• September 10: Due to the number of bugs in the problem set found during office hours, I have decided to extend the deadline until noon on Monday, September 15.
• September 6: A minor error was found in the "Gene" schema of Problem 3 in the first problem set. It has been fixed. I also clarified the queries in question 4, by replacing the phrases "Superman" movie, "Batman" movie, and "Spider-Man" movie with the movie entitled "Superman", etc. Please download it again.
• September 5: The sailing club database (right click or command-click for menu, then "save link as...") for sqlite3 is available for exploration.
• September 4: All problems are now posted for the first Problem Set. It is due one week from today in class.
• August 26: Create a course login at this link. Use Comp521 as your "Institute" and either your Onyen or CS login as "Email".
• August 19: First day of class and the course syllabus.

Course Description

Databases are an indispensable tool for managing information, and a course on the principles and practice of database systems is now an integral part of any computer science curricula. This course covers the fundamentals of modern database management systems, in particular relational database systems.

The material covered in Comp 521 can be broken into three areas of emphasis. The first area includes database foundation material such as the relational model, relational algebra, relational calculus, and normal forms. The second area of emphasis is database application programming and includes among others the topics of Structured Query Language (SQL), eXtensible Markup Language (XML), integrating databases into programs, web-based database usage, and triggers and active databases. The third area of emphasis is the systems side of databases, which includes database indexing, efficient query evaluation, the transaction-based model, concurrency, and security.

This course is suitable for computer science majors at both undergraduate and graduate levels. Students who wish to take this course should have some programming experience in a modern language and knowledge of data structures. There will be five homeworks, two mid-terms, and a final exam.

Book, Course Information, and Prerequisites

Course Instructors

	Instructor:	Leonard McMillan		Co-Instructor:	Erik Scott
	Office:	SN311		Office:	SN127
	email:	mcmillan@unc.edu		email:	escott@renci.org

Office Hours:

Leonard McMillan: Wednesdays 3pm-5pm
Erik Scott: Thursdays 11:15am-1:15pm
Chad Armstrong: SN364 Mondays 3:30pm-5:00pm

Schedule

Resources

SQLlite3

SQLlite is a open-source light-weight serverless database management system that uses ordinary disk files as its repository. Despite its simplicity, sqlite3 is one of the most widely used databases in the world. For example, nearly every smartphone uses sqllite to track the settings and configurations of their local apps. It is widely supported via libraries by nearly every modern programming language (Java, C#, Python, etc). Therefore, Sqllite is a DBMS well worth learning.

If you are using either OSX or Linux, sqlite is already installed on your system. You can access it by typing "sqlite3 " at a shell command prompt. If you use Windows you will need to install sqlite3 from http://www.sqlite.org/download.html. I recommend installing the precompiled windows binary.

IMDb Movie database

IMDb provides one of the most extensive and, according to their website, most authoritative movie databases on the planet. They also provide dumps as a means for taking snapshots. Each fall when Comp 521 is taught, I take a snapshot in early August of IMDb with the following simplified sqlite schema:

CREATE TABLE Actor (
    aid INTEGER PRIMARY KEY AUTOINCREMENT,
    first TEXT DEFAULT '',
    last TEXT DEFAULT '',
    dob DATE DEFAULT '',
    gender TEXT DEFAULT '');

CREATE TABLE Movie (
    mid INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT DEFAULT '',
    year INTEGER DEFAULT 0,
    rating TEXT DEFAULT '');

CREATE TABLE Role (
    aid INTEGER REFERENCES Actor(aid),
    mid INTEGER REFERENCES Movie(mid),
    role TEXT DEFAULT '',
    billing INTEGER DEFAULT 0);

which I like to use as an example database for querying, indexing, and examining query execution plans. You can download a "zip" compressed copy here (149 Mb). (You might find it useful for various homework problems.)