Welcome to the

University of Saskatchewan - Department of Computer Science

CMPT 355 - Theory and Application of Data Bases

Web site for 2016

Class notes can be accessed by registered students via: moodle.cs.usask.ca

Class News (Last Updated: Nov 30) [It is very important to attend the Dec 1 Tutorial]

Course Syllabus CMPT 355

Catalog Description:

Lectures, assignments and projects dealing with the management, storage, and retrieval of large volumes of data. Concentrates on the relational data model, and relational data base management systems. Topics include: recovery and concurrency, integrity and security, query optimization, normalization, and semantic modeling. Additional topics include multimedia databases and other paradigms.


CMPT 270 and 260 [Please note: these prerequisites will not be waived for undergraduates without major industrial database experience]

Lectures: Monday, Wednesday, and Friday from 11:30 to 12:20 Thorv 124

(see Class Schedule below)

The lecture sections will focus on presenting information on the successful application of databases to a wide range of real world problems. While some of this information will be available from the text, a considerable amount of information in the lectures will go beyond that available from the text. Thus attendance at the lectures is important in order to gain an understanding of all class material.

Where noted in the lecture schedule (below), select lectures will answer student questions and summarize / discuss / illustrate a selection of major points taken from the text. Given the amount of time available in lectures for each chapter it will not be possible to deal with every aspect of every chapter. It is required that students read the assigned portions of the text prior to the class for which they are assigned. Students are strongly encouraged to identify questions they need answered during their reading of the text.

Where "---" appears as the text chapters in the lecture section (below), significant additional information beyond the text will be presented and discussed. Much of this material is essential for an understanding of the class assignments and project and provides an understanding of a range or real world issues that are missing in traditional database texts but that pose major challenges when trying to implement databases.

While lecture sections will introduce bahttp://userlab.usask.ca/CMPT%20479/c479home.htmlsic SQL issues common to all relational databases, they will not deal with the specific software used in the assignments or the project. Specifics related to the use of PostgreSQL will only be dealt with in the tutorials.

Tutorials: Thorvaldson, Spinks 320, Thursday afternoons starting Sept. 15

Due to the very high enrollment this year, we will have two tutorial times: 4:00-5:20 and 5:30-6:50. Both times will cover the same material. Unfortunately, limitations on the university's registration system was not able to deal with expansion of the class size in a way to register individual students into the second tutorial time once it was determined to be needed. Thus, all students are officially registered in the 4:00-5:20 section even though the room only has a capacity of 40 students. It is hoped that a significant number of students will voluntarily choose the 5:30-6:50 time, since computers in the two times will be available on a first come, first served basis.

PLEASE NOTE:  The first tutorial will be Thursday, September 15. Students should not miss this tutorial. It provides important information regarding accessing and using class specific software.

Software and Lab: 

The lectures and exams focus on standard SQL. Students will be using the PostgreSQL data management system for all assignments and their project.  PostgreSQL has various differences from standard SQL. 

Individual accounts will be established on the Department of Computer Science PostgreSQL server. You must have your completed assignments/project in your account for them to be marked. However this can be accomplished by using text files that have been developed and tested on your own system.

You can load and use a copy of PostgreSQL on your own home Windows or Linux system. Cygwin <http://sources.redhat.com/cygwin> is a Linux emulator for Windows that optionally includes a copy of PostgreSQL.

A good source for on-line information on PostgreSQL is  <http://www.postgresql.org/docs/9.3/interactive/index.html> .

Students are expected to use dbvisualizer as a tool to interact with PostgresSQL. It is available on the Computer Science Lab on both the Windows and Linux platforms and can be loaded to your own system from <http://www.dbvis.com/>.

The installation and initial use of both PostgresSQL and dbvisualizer will be discussed in the first tutorial on xxxxxxx.

Class Website: moodle.cs.usask.ca


Prof. Jim Carter, 280.3 Thorvaldson Bldg, 966-4893.

Office hours: Mon/Weds/Fri 12:30 - 1:20 or by appointment.

Course Objectives

A student successfully completing this course shall be able:
  1. To identify the important characteristics of data and information that need to be served by a well structured database
  2. To apply databases to meeting the differing needs of various users
  3. To apply sql-type databases to the storage and retrieval of data
  4. To apply entity-relationship diagrams to the design of well structured databases
  5. To apply normalization and the concepts of relational databases to the design of well structured databases
  6. To apply concepts of temporal data to the design of well structured databases
  7. To apply constraints and triggers to maintain the integrity of data within a database
  8. To successfully combine data and databases into a single database that better serves the combined interests involved
  9. To have an understanding of selected additional advanced concepts relating to databases

Student Evaluation:

 Marking Component  Marking Weight
 Part 1 - report
 Part 2 - report

 Midterm  20%
 Final  35%

Assignments and Project:

Attendance Expectations

It is expected that students will attend and participate in all lectures and tutorials.
Students will be responsible for all material presented in the lectures.

Midterm Exam

The midterm exam will take place on October 28 during class time. This will make the mid-term a very time constrained exam (students will have much more time to answer the final). In order to help students prepare for the mid-term, the instructor will post a large case study the day prior to the mid-term. Students can make use of this case study to prepare a good understanding of the case study and the possible questions in advance of the midterm. The mid-term will then make use of a small subset of the case study as the basis of testing student's skills with entity-relationship diagramming and sql. Students are not able to take any materials into the mid-term and must rely on their knowledge to answer its questions.

Final Exam Scheduling

The final exam will be a 3 hour closed book exam that will be held in the regularly scheduled examination period..
The Registrar schedules all final examinations, including deferred and supplemental examinations.
Students are advised not to make travel arrangements for the exam period until the exam schedule has been posted.

Note: All students must be properly registered in order to attend lectures and receive credit for this course.


Connolly and Begg, Database Systems, 6th Edition, 2015, Pearson

NOTE: The 5th edition is very close in content.


The instructor provides notes for many but not all lecture sessions that may be downloaded from the CS Moodle server and used by students registered in the class. All notes are copyright by the instructor and should not be shared outside the class. Students may wish to download the class notes prior to the class to use as a basis for their personal note taking, however minor changes to the notes may be made by the instructor  right up to class time. Lecture material will go beyond these notes and students are warned that reading the notes is not a substitute for attending and participating in class.

Lecture and Assignment Schedule for Fall 2016


Topics Assignments Text chapters
Sept 7
Sept 9
Welcome - Introduction to CMPT 355
Intro to Databases & Database Environment

Ch 1 - 2
Sept 12
Sept 14
Sept 16
The Relational Model
SQL Single Table Queries
SQL Multi-Table Queries  and Updates
A#0 Assigned - Due before Sept 19
Ch 4
Ch 6.2 - 6.3.6
Ch 6.3.7 - 6.3.10
Sept 19

Sept 21
Sept 23
Discussion of Assignment #1
SQL Standards
SQL Data Types and Constraints
SQL Tables, Views, Transactions and Access Control

A#0 Due before Sept 19
A#1 Assigned - Due Oct 5
Ch 7.1 - 7.2
Ch 7.3 - 7.6
Sept 26
Sept 28
Sept 30
SQL Example
SQL Example (cont.)
SQL Example (cont.)

Oct 3
Oct 5
Oct 7
Entity-Relationship Modeling
Enhanced Entity-Relationship Modeling
Discussion of Assignment #2

A#1 Due - Oct 5
A#2 Assigned - Due Oct 19
Ch 12
Ch 13
Oct 10
Oct 12
Oct 14
- Thanksgiving Day - no class today-
Advanced SQL (programmatic SQL)
Advanced SQL (triggers in SQL)

Ch 8.1
Ch 8.3
Oct 17
Oct 19
Oct 21

E-R Example
E-R Example (cont)
Discussing Assignment #3

A#2 Due - Oct 19
A#3 Assigned - Due Nov 2
Ch 14 - 15
Oct 24
Oct 26
Oct 28
Temporal Databases & Temporal Example
Discussion of a previous year's midterm
Midterm Exam

---, Ch 16-19
Oct 31
Nov 2
Nov 4
Issues in Combining Databases
Issues in Combining Databases (cont)
Return midterm /  Discuss class project requirements
Project Teams announced

A#3 Due - Nov 2
Project Assigned
Part 1 Due 11:00 Nov 21
Part 2 & Demo Due 8:00 am Dec 5
Nov 7
Nov 9
Nov 11
- University closed - Fall Mid-Term Break
Nov 14
Nov 16
Nov 18
Focusing on Information
Focusing on Information (cont)
Security & Privacy

Ch 20
Nov 21
Nov 23
Nov 25
Transaction Management
Discussion of Class Project Results & Requirements
Object-Oriented DBMSs
Project Part 1 Due Nov 21

Ch 22
Ch 27-28
Nov 28
Nov 30
Dec 2
Data Warehousing
OLAP and Data Mining
class wrap-up
Ch 31-32
Ch 33-34
Dec 5
Dec 6
Dec 7
class project presentations
class project presentations
 - no class today
Project Due 8:00 am Dec 5

Policies in this Class

Late Assignments

Late assignments will receive 0 marks. Students with a sufficiently serious reason for being late with an assignment that is acceptable to the instructor may be allowed an extension. The instructor will not accept notes from Student Health Services as support for the student's reason for being late with an assignment.

Missed Assignments

1. Each assignment builds on the previous assignment, thus any missed assignment will have to be completed as a basis for completing following assignments and the class project. However, this completion will not result in any marks being awarded for the missed assignment.

2. All students are required to complete all assignments as a prerequisite to being placed into a class project team. Any student who does not complete Assignment 3 will receive a mark of 0 on the class project.

Missed Examinations

1.    "Students who have missed an exam or assignment must contact their instructor as soon as possible. Arrangements to make up the exam may be arranged with the instructor. Missed exams throughout the year are left up to the discretion of the instructor if a student may make up the exam or write at a different time. If a student knows prior to the exam that she/he will not be able to attend, they should let the instructor know before the exam."

2.    "Final exams - a student who is absent from a final examination through no fault of his or her own, for medical or other valid reasons, may apply to the College of Arts and Science Dean's office. The application must be made within three days of the missed examination along with supporting documentary evidence. Deferred exams are written during the February mid-term break for Term 1 courses and in early June for Term 2 and full year courses." (2007/08. http://www.arts.usask.ca/students/transition/tips.php)

General Policies

Incomplete Course Work and Final Grades

"When a student has not completed the required course work, which includes any assignment or examination including the final examination, by the time of submission of the final grades, they may be granted an extension to permit completion of an assignment, or granted a deferred examination in the case of absence from a final examination. The student must apply to the instructor for such an extension and furnish satisfactory reasons for the deficiency. Deferred final examinations are granted as per College policy."

In the interim, the instructor will submit a computed percentile grade for the course which factors in the incomplete course work as a zero, along with a grade comment of INF (Incomplete Failure) if a failing grade.

If an extension is granted and the required assignment is submitted within the allotted time, or if a deferred examination is granted and written in the case of absence from the final examination, the instructor will submit a revised computed final percentage grade. The grade change will replace the previous grade and any grade comment of INF (Incomplete Failure) will be removed.

For provisions governing examinations and grading, students are referred to the University Council Regulations on Examinations section of the Calendar.

(2011 University of Saskatchewan Calendar/Academic Courses Policy)

Further details of the Academic Course Policy can be found online at: http://policies.usask.ca/policies/academic-affairs/academic-courses.php

Academic Honesty

The University of Saskatchewan is committed to the highest standards of academic integrity and honesty.  Students are expected to be familiar with these standards regarding academic honesty and to uphold the policies of the University in this respect.  Students are particularly urged to familiarize themselves with the provisions of the Student Conduct & Appeals section of the University Secretary Website and avoid any behavior that could potentially result in suspicions of cheating, plagiarism, misrepresentation of facts and/or participation in an offense.  Academic dishonesty is a serious offense and can result in suspension or expulsion from the University.

All students should read and be familiar with the Regulations on Academic Student Misconduct (http://www.usask.ca/secretariat/student-conduct-appeals/StudentAcademicMisconduct.pdf ) as well as the Standard of Student Conduct in

Non-Academic Matters and Procedures for Resolution of Complaints and Appeals (http://www.usask.ca/secretariat/student-conduct-appeals/StudentNon-AcademicMisconduct.pdf)

Academic honesty is also defined and described in the Department of Computer Science Statement on Academic Honesty (http://www.cs.usask.ca/undergrad/honesty.php ).

For more information on what academic integrity means for students see the Student Conduct & Appeals section of the University Secretary Website at: http://www.usask.ca/secretariat/student-conduct-appeals/forms/IntegrityDefined.pdf

Examinations with Disability Services for Students (DSS)

Students who have disabilities (learning, medical, physical, or mental health) are strongly encouraged to register with Disability Services for Students (DSS) if they have not already done so. Students who suspect they may have disabilities should contact DSS for advice and referrals. In order to access DSS programs and supports, students must follow DSS policy and procedures. For more information, check http://www.students .usask.ca/disability/ , or contact DSS at 966-7273 or dss@usask.ca .

Students registered with DSS may request alternative arrangements for mid-term and final examinations. Students must arrange such accommodations through DSS by the stated deadlines. Instructors shall provide the examinations for students who are being accommodated by the deadlines established by DSS.

Recording of Lectures

The instructor will not be recording lectures and will not provide approval to record lectures except if such recording is authorized by DSS. Any recordings of lectures are intended for the sole use of the person with permission to record them and shall not be shared or published.

Date of last revision: Sept. 7, 2016