Database Management Systems

CMPT 354

Simon Fraser University

Fall 2016

Inactive Links are Under Construction

Course Logistics

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

Teaching Assistant: Rohit Ranjan
E-mail: rranjan@sfu.ca
Office Hours: Wednesday 4:00-5:00 Pm
Office Location: ASB9810

Announcements

• Third In-Class Exam coming up on *Monday*.
• Office hour on Monday Dec 5 is now scheduled for *10 am* (not 4 pm).

Course Resources

• Syllabus
• Schedule
• 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.
• 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 3a.
  • 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.
• Assignment4.

Exam Resources

1. Our Exams
   1. 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.
   2. Exam 1 Instructions.
   3. Exam 1 Appendix. Keywords, Sample Queries shown as part of the exam.
   4. Exam 2 Instructions.
   5. Exam 2 Appendix. Keywords, Sample Queries shown as part of the exam.
   6. Exam 3 Instructions.
   7. Assignment4 contains XPath exercises. More XPath exercises here. The on-line exercises are a good resources, but cover significantly more constructs than we need for the course.
2. 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 Aug 31, 2016.
• Entity-Relationship Modelling. Updated Sep 28, 2016.
• The Relational Model. Updated Sep 28, 2016.
• Basic SQL. Set Operators, Joins.
  • 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. Updated June 7, 2016.
• File Organization and Access Methods. Updated Dec 1, 2016.
• Query Processing. Updated Dec 1, 2016.