Database Management Systems

CMPT 354

Simon Fraser University

Summer 2016

Inactive Links are Under Construction

Course Logistics

Instructor: Oliver Schulte
Office Location: TASC 1 9021.
Office Phone: 778-782-3390.
Office Hours: Thursday 10:30 am-12 pm.
E-mail Office Hour: Tuesday 11:30 am12:30 pm.

Teaching Assistant: Vivek Patel
E-mail: vgpatel@sfu.ca
Office Hours: Monday 12:00 PM - 1:00 PM
Office Location: ASB9808

Announcements

Extra Office Hour for final exam: Thursday, June 23, 10:30 am.

Course Resources

• Syllabus
• Schedule
• Database System Concepts website
• Submission Server
• SFU Medical Excuse Form.

Using SQL Server

• We can not promise support for all platforms. Support is available for the setup in the CSIL lab. The databases you will need for this assignment and others are preloaded on the CSIL server.
• Using SQL Server in CSIL. Here are some instructions for using SQL Server for those who are ready to start on this. The CSIL staff is reviewing our setup for the intersession, so these may be updated.
• If you want to install a personal copy of SQL Server on your machine, you can use this: Installation Guide for the SQL Server 2012 Express. This includes instructions for how to import the Adventure Works database from a .bak file. If this doesn't work for some reason, you can try importing the data by running an SQL script. The SQL script is in the .sql file.
  • AdventureWorks Schema and Data Files.

Assignments

• Assignment 1.
• Assignment 2.
  • We can not promise support for all platforms. Support is available for the setup in the CSIL lab. The databases you will need for this assignment and others are preloaded on the CSIL server. If you use your personal installation, you will need to import them.
  • Company Database: backup format.
  • StarWars Database: backup format.
  • Company Database: sql script.
  • Starwars Database: sql script.
• Assignment 3. Application Development.
  • To develop applications, you can use any programming language that works in with the CSIL database server Cypress in the CSIL environment. This includes Python, Visual Studio and Java for example. Most students have found Python by far the easiest to use for this assignment.
  • Visual Studio was developed to make this kind of application development easy. This applies to C# and Visual Basic. If you want to use Visual Studio, here are some more Visual Studio resources.
    • Flash Video for Database Connection.
    • Visual Studio Connection Example Code.
    • Overview of ASP Net.
  • CSIL has posted instructions for using Java. If you have trouble loading the driver, here is a possible solution for downloading it.
• Assignment4.

Midterm Resources

1. Our Midterm
   1. Midterm Information. You can use a cheat sheet, 1 (one) 8.5 x 11 sheet of notes for reference, both sides.
   2. Midterm Instructions.
   3. Midterm Appendix. Keywords, Sample Queries shown as part of the midterm.
2. Link to Previous Midterm 1
3. Link to Previous Midterm 2
4. Link to Previous Midterm 3

Final Exam Resources

• Final Exam Date: Thursday, June 23, 2016 from 2:30-4:20 pm.
• Final Exam Place: SSCC 9002

1. Our Final Exam
   1. Final Exam Info You can use a cheat sheet, 1 (one) 8.5 x 11 sheet of notes for reference, both sides.
   2. First Two Pages of Final Exam Instructions and grading breakdown.
   3. Appendix of Final Exam
2. Link1 to Previous Exam
3. Link2 to Previous Exam

External Learning Resources

• Changes in SQL:99 standards
• The 24x7 on-line book collection, for reference material. Log in to your library account, then search for books24x7 and "databases".
• Overview of ASP Net.
• Stanford Teaching Videos on Databases

Slides

• Introduction Updated May 10, 2016.
• Entity-Relationship Modelling. Updated May 18, 2016.
• The Relational Model. Updated May 18, 2016.
• Basic SQL. Set Operators, Joins. Updated May 24, 2016.
  • Script for creating example tables. It's a good exercise to recreate the example tables on your own system. Here's a script that you can run to do that. You need to edit it to point to your own database schema. Even better would be setting up the tables and instances yourself from scratch. This is purely optional for those who want to try it.
• Relational Algebra. Chapter 4 pdf for algebra symbols. Updated May 24, 2016.
• Advanced SQL. Updated May 24, 2016. Aggregation, Null Values, Access Control, Triggers. Youtube Video For the class on Tuesday, May 31.
• Application Development. Programming With Databases. Updated June 7, 2016.
• Chapter 7. Application Development ctd. Updated June 7, 2016.
• XML. Semi-Structured Data. Updated June 7, 2016.
• File Organization and Access Methods. Updated June 22, 2016.
• Query Processing.