Database Management Systems

CMPT 354

Simon Fraser University

Spring 2017

Inactive Links are Under Construction

Course Logistics

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

Teaching Assistant: Guiliang Liu
E-mail: gla68@sfu.ca
Office Hours: Tuesday 11:30 am - 12:30 pm.
Office Location: ASB9810

Announcements

Course Resources

• Syllabus
• Schedule. Updated Mar 06, 2017.
• Database Management Systems' 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

Currently under construction, we'll try to finalize as soon as possible. But we've got the links setup already so you have an idea of what it will look like in the end.

• Assignment 1. Updated Jan 12, 2017.
• Assignment 2. Updated Feb 1, 2017.
  • 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 (cmpt354_starwars and cmpt354_company). 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 3a. Updated March 6, 2017.
  • 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.
• Assignment 3b. Updated Mar 7, 2017.
• Assignment4. Updated Mar 22, 2017.

Exam Resources

Exam 1: updated Feb 25, 2017. Exam 2: updated March 15, 2017. Exam 3: updated March 31, 2017.

1. Our Exams. Exam Information. Break down of topics. You can use a cheat sheet, 1 (one) 8.5 x 11 sheet of notes for reference, both sides.
   1. Exam 1 Instructions.
   2. Exam 1 Appendix. Keywords, Sample Queries shown as part of the exam.
   3. Exam 2 Instructions.
   4. Exam 2 Appendix. Keywords, Sample Queries shown as part of the exam.
   5. Xpath exercises.
      1. Simple Xpath exercises using the running example from class You should be able to do these from what we covered in class today.
      2. Assignment4 contains XPath exercises. These also use no more than we covered already.
      3. More XPath exercises here. The on-line exercises are a good resource, but cover significantly more constructs than we need for the course.
   6. Exam 3 Instructions.

1. Previous Exams from other classes
   1. Link to Previous Midterm 1
   2. Link to Previous Midterm 2
   3. Link to Previous Midterm 3

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 Jan 11, 2017.
• Entity-Relationship Modelling. Updated Jan 11, 2017.
• The Relational Model. Updated Jan 11, 2017.
• Basic SQL. Set Operators, Joins. Updated Jan 23, 2017.
  • 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.
• Advanced SQL. Aggregation, Null Values, Access Control, Triggers.
• Application Development Programming With Databases.
• Internet Application Development Application Development ctd.
• XML. Semi-Structured Data.
• File Organization and Access Methods. Updated April 4, 2017.
• Query Processing. Updated April 5, 2017.