and Information Technology
Institute Office: Room 3014, CCT Building
Telephone: 905-569-4398
| Institute of Communication, Culture and Information Technology Institute Office: Room 3014, CCT Building Telephone: 905-569-4398 | |
Click here to jump to the schedule. Lecture slides and exercises are posted under the corresponding weeks.
The final project handout is here.
The practice exam is here. The answers to the exam are here.
An example of a CGI script is here.
| Class Location & Time: | Wed, 01:00 PM - 04:00 PM CC 3160 |
| Instructor: | Yuri Takhteyev |
| Office Location: | CCT3018 |
| Office Hours: | 4–5 pm on Wednesdays or by appointment. (Appointments at the UTM campus will only be possible on Wednesdays, before 9 am, between noon and 1 pm, or after 5 pm.) |
| Email Address: | yuri.takhteyev -at- utoronto.ca. However, please see email policy. |
| Course Website: | http://takhteyev.org/courses/11F/cct395/ |
| The Q&A Site: | http://cct395.ischool.utoronto.ca |
Teaching assistant’s name and contact information will be announced later.
An in-depth examination of selected topics in communication, culture and information technology. Topics vary from year to year, and the content in any given year depends upon the instructor. [24L]
(This special topics course will introduce the students to database technologies, paying specific attention to analysis, design and use of relational database systems and SQL, but also looking more broadly at the different database technologies in use today.)
Prerequisite: Minimum of 8.0 credits (SSc)
Distribution Requirement: SSc
Only the Director of the CCIT program has the authority to give permission to waive course prerequisites. The UTM calendar states that students who lack the prerequisites for a course can be deregistered at any time.
Students will learn the main principles behind relational databases and information storage systems more broadly. In particular, they will acquire the following skills:
Required readings will be primarily from two books, which are both available in the UTM Bookstore. Students are encouraged to purchase paper copies. However, the University of Toronto library also provides access to electronic versions of both books.
Those two books are referred to in the readings as "SQL" and "RDD" respectively.
Students who choose to use the electronic copies are advised to download and open the required chapters ahead of time. Unavailability of the electronic resources at the necessary time or software problems related to their use will not be accepted as valid reasons for late assignments.
Some of the required readings will come from other sources. Those are all available over the Internet, either openly or through the University Library’s MyAccess service.
The students may find the following additional resources useful:
We will be using a MySQL 5.5 database server for examples, exercises, and the final project. MySQL’s implementation of SQL is sometimes different from from the official standard described by Harrington. MySQL 5.5 Reference Manual does a good job describing MySQL’s implementation. It is also sercheable. The students are strongly encouraged to learn how to make use of this resources.
SQL in a Nutshell provides a summary of SQL commands with notes on what is supported in different implementation.
We will be using a Linux database server in this course. Some familiarity with Unix commands will make your life easier.
We will be doing some very basic Python programming. You can use the following resources to supplement materials provided in class.
Hands-on use of database software is absolutely essential to your success in this class. Each student will get an account on a database server which will allow them to make queries against databases provided by the instructor. Each student will also have their own database on the database server in which they will be able to create their own tables and insert their own data into them.
Students are expected to spend substantial time interacting with the database server. There is simply no way to learn the material presented in the class without spending a lot of time interacting with the software. The amount of time a particular student would need to spend may vary depending on their background. However, for most students this time will be measured in hours rather than minutes per week and should at the very least include trying all the examples presented in class and doing all of the required exercies. This should really be considered the bare minimum, though. Successful students would likely spend a lot of additional time trying variations on the examples and exercises.
Some students might be surprised at the level of precision that is necessary for writing successful SQL queries and the time that may be required to get a query right. A missing quotation mark or comma can really make a difference and it can sometimes take you an hour (or even several hours) to figure out that your query was failing because of a misplaced comma. This, however, is an inherent part of SQL programming and the only way out of it is practice.
From the first week students will need to use a database or SSH "client" to connect to the database. The simplest method, which will be demoed during the first class, is to use Sequel Pro, a free / open source software database client for Mac OS X. Sequel Pro will be installed in the Mac Lab and students will be able to use it during or after class. Students who use Mac computers can install a copy of Sequel Pro on their computers. Students who use other operating systems or for some reason do not wish to use Sequel Pro can choose from among the following alternatives:
Database clients:
SSH clients:
Later in the semester, however, all students are expected to learn to use SSH and SCP (with one of the SSH clients listed above) to transfer files to the database server and / or modifying files on the server. This will be necessary for completing the final project.
Students will need to learn a simple programer's text editor for editing their files. Recommended options: TextWranger (Mac OS X), Notepad++ (Windows), gedit (Linux, built-in).
Finally, students will need diagraming software for drawing ER diagrams. It is recommended that students use OpenOffice.org's Draw and install this template that provides Crow's Feet arrows. This is the software that will be demoed in class and installed on the Lab computers. If you prefer, you can use any other software that allows you to draw the same diagrams. However, students should not use hand-drawn ER diagrams in their preliminary or final project submissions.
Students who have problems installing or using software should ask their questions on the Q&A site as described in the course communication policy.
| Type | Description | Due Date | Weight |
| Quiz | Five quizzes | 25% | |
| Assignment | Project Proposal | October 26, 2010 | 10% |
| Assignment | Final Project Report | November 23, 2010 | 25% |
| Final Exam | TBA | 40% |
25% of the grade will be based on in-class quizzes which will be given in weeks 2 through 12. (The timing of the quizzes will not be anounced beforehand.) There will be a total of 5 quizzes. Your grade will be based on your best four quiz results. In other words, your worst quiz result will be dropped. This also means you can miss one quiz without a penalty. (Your grade will be based on the four quizzes that you did take.)
To help you prepare for quizzes and the final exam, you will receive study problems and questions for each week of the semester. You are expected to work on those questions and problems. They are not an optional component of the course. However, you do not need to turn in your results, since you will not be graded on this component directly. (Rather, your work on the study problems will be graded through the quizzes.) The students are encouraged to work on study problems and question in groups. Students who want to get feedback on their solutions to study problems should bring them to office hours.
For the final project you will design and implement an information system with a MySQL database back-end and a simple web interface. The overall purpose and functionality of the system will be specified in a project handout that will be distributed later in the course. Students who want to develop a system serving a different purpose can do so with instructor’s permission, but must discuss this with the instructor early in the semester. (Yes, this does mean that you will need to discuss this before you know what the standard final project is going to be.) The final project will consist of two submissions. The first submission is an initial design, worth 10% of the grade. The second submission is the actual implementation and a report, worth 25% of the grade.
The projects can be done individually, in pairs, or in teams of three. The students who want to work in groups are responsible for forming the groups. The groups will need to be formed before the due date of the first submission.
The final exam, worth 40% of the grade, will test your understanding of the material from the whole semester. You will be asked to write SQL statements, draw ER diagrams, and answer conceptual questions about database design. The final exam questions will closely resemble study problems and questions and/or those used in the quizzes.
The class will be taught in the form of lectures, which will explain and expand on the material presented in the readings and will include live tutorials and demonstrations of database software. Assignments will generally stress practical skills.
Only student Utormail accounts should be used for course communication and all emails from students must include the course code in the subject line and should be signed with the full student name and student number.
Email should only be used for questions that cannot be asked on the Q&A site (see below) due to privacy concerns (e.g., questions about your grades or absences). Please expect a response within 2 business days. (If you do not hear back within 2 days, please resend your message.)
The instructor may send course-related announcements to the students by email. The students are responsible for making sure they receive those announcements and read them.
All questions about course material or software should be asked through the Q&A site at http://cct395.ischool.utoronto.ca/. Before asking a question, please check if the requested information is available in answers to earlier questions, in the course outline, in handouts, or in emails from the instructor. If you ask a question and then find an answer to it, please post the answer. Questions on the content of the course that are likely to require lengthy explanations should ideally be asked in class or brought to office hours.
Students are also encouraged to answer questions posted on the site. Students who consistently provide useful answers will receive extra credit (at the discretion of the instructor).
The content of the Q&A site will be visible to your fellow students as well as the world at large. If you do not want to reveal your identity to others, please pick a username that you do not use on other sites. Please use your Utormail address, however. (Your email will not be shown to other users but will be visible to the instructor.) Please note that if your email is enrolled with Gravatar, then the site will use your Gravatar icon, which may indirectly identify you. If you do not want this to happen, change your Gravatar icon to something else. If you have further privacy concerns in regards to the Q&A website, please discuss them with the instructor.
Students are expected to arrive to class before the beginning of class and to be seated and ready to start the discussion at 1:10 pm. Students who arrive late will be asked to wait outside until invited to come in. (This is a matter of respect for the time of students in the classroom.)
You are expected to complete assignments on time. There will be a penalty for lateness of 10% deducted per day and work that is not handed in one week after the due date will not be accepted.
To clarify:
As a general rule students will not be offered any opportunity to make up for missed quizzes. Students who miss a quiz will receive zero for that quiz. (Each student can drop their worst quiz score, however, which can include a zero for a missed quiz.) The cases of students who are not present in class at the time of a quiz for a truly valid reason will be considered on a one-off basis, provided that all of the following conditions are satisfied:
Students who arrive in class late at a time when a quiz is in progress will not be allowed to take the quiz. This includes students returning late from a break.
Students who cannot write a final examination due to illness or other serious causes must file an online petition within 72 hours of the missed examination. Original supporting documentation must also be submitted to the Office of the Registrar within 72 hours of the missed exam. Late petitions will NOT be considered. If illness is cited as the reason for a deferred exam request, a U of T Medical Certificate must show that you were examined and diagnosed at the time of illness and on the date of the exam, or by the day after at the latest. Students must also record their absence on ROSI on the day of the missed exam or by the day after at the latest. Upon approval of a deferred exam request, a non-refundable fee of $70 is required for each examination approved.
From the Code of Behaviour on Academic Matters:
It shall be an offence for a student knowingly: (d) to represent as one's own any idea or expression of an idea or work of another in any academic examination or term test or in connection with any other form of academic work, i.e. to commit plagiarism. Wherever in the Code an offence is described as depending on “knowing,” the offence shall likewise be deemed to have been committed if the person ought reasonably to have known.
From the U of T Mississauga Academic Calendar:
Honesty and fairness are considered fundamental to the University's mission, and, as a result, all those who violate those principles are dealt with as if they were damaging the integrity of the University itself. The University of Toronto treats academic offences very seriously. Students should note that copying, plagiarizing, or other forms of academic misconduct will not be tolerated. Any student caught engaging in such activities will be subject to academic discipline ranging from a mark of zero on the assignment, test or examination to dismissal from the University as outlined in the UTM calendar. Any student abetting or otherwise assisting in such misconduct will also be subject to academic penalties.
Students are assumed to be informed about plagiarism and are expected to read the handout, How Not to Plagiarize written by Margaret Procter. It is a valuable and succinct source of information on the topic. You are also supposed to be familiar, and considered as being familiar, with the Code of Behaviour on Academic Matters (see UTM Calendar: Codes and Policies or Code of Behavior on Academic Mattes and Code of Student Conduct, which spell out your rights, your duties and provide all the details on grading regulations and academic offences at the University of Toronto.
Students agree that by taking this course, they agree to adhere to the “ICCIT Expectations for Conduct in the Academic Setting.” See link for the Code: http://www.utm.utoronto.ca/iccit-code-of-conduct.
Information about the University’s Policy on Scheduling of Classes and Examinations and Other Accommodations for Religious Observances is at http://www.viceprovoststudents.utoronto.ca/publicationsandpolicies/guidelines/religiousobservances.htm
AccessAbility
The University accommodates students with disabilities who have registered with the AccessAbility Resource Centre. Please let me know in advance, preferable in the first week of class, if you will require any accommodation on these grounds. To schedule a registration appointment with a disability advisor, please call the centre at 905-569-4699 or e-mail access.utm@utoronto.ca.
Robert Gillespie Academic Skills Centre
Students can visit the Academic Skills Centre to consult with one of its strategists about understanding learning style, developing study plans for upcoming tests/exams, or discussing papers. Special Diagnostic Assessments are also offered and are designed to help you learn exactly where you stand with respect to critical academic skills.
UTM Library (Hazel McCallion Academic Learning Centre)
The University of Toronto boasts the biggest academic library in Canada and the second biggest in North America. Various services are available to students at the UTM Library and across the U of T library system. Services including borrowing, interlibrary loans, online references, laptop loans and the RBC Learning Commons. For more information, visit
This chart might help you visualize the order in which we will be covering the two books.
Overview of the course and ground rules. Databases in the context of modern information systems. Database servers and clients. SQL. Connecting to a database with a GUI client. Connecting to a database using SSH and a command-line client. The relational data model. Basic data types. Basic one-table SELECT. ORDER BY and LIMIT.
Complex conditions. Arithmetic, functions and system values. More data types. CREATE TABLE. INSERT and LOAD DATA. UPDATE and DELETE.
Requirements analysis and data modeling. The ER model. ER diagrams. Cardinality and optionality. Weak entities and belonging.
Exercises. No solutions are posted since you are asked to revisit this in Week 4.
Primary and foreign keys. Natural and surrogate keys. The converstion steps. Implementing PK-FK constraints in SQL. CASE tools.
Exercises. Partial solution: ODG, PDF.
Solution to last week's quiz: PDF.
Queries using multiple tables. Cartesian product of tables. JOIN. ON and USING. Inner and outer joins. Natural joins. “Traditional” joins.
Exercises and answers for part 1.
Normalization. Normal forms. Fixing tables with ALTER TABLE.
Solution to last week's quiz: PDF.
The final project handout is here.
No exercises this week.
Catching up in case we fall behind. Applying the material from the previous few weeks to examples.
Initial design for the final project due before the beginning of the class.
Using an SQL database from within a program. SQL with Python. SQL in a web application.
Solution to last week's quiz: PDF.
Subqueries. Aggregation with GROUP BY and HAVING. Variables. Casting values.
Full-text search. Markup languages. XML, YAML, JSON. Markup languages as input or output format. XML support in databases. Exporting and importing XML with --xml and LOAD XML. “Nosql” databases.
Users and access rights. Views. Firewalls. SQL injection attacks. Denial of service attacks.
Solution to last week's quiz: PDF.
A paper copy of the project report and the digital submission due before the beginning of the class.
Representing tables. Indexing. Storage Engines. Transactions. Backup. Replication.
Solution to last week's quiz: PDF.
The practice exam. The answers.
Every attempt will be made to follow this syllabus, but its contents are subject to change, according to the rules outlined in the UTM Instructor’s Handbook, section 3.2.2.